Pl sql inserting updating

Luckily enough, we can fake a staging table using DUAL: create table employees ( employee_id integer not null, employee_name varchar2(100) not null); alter table employees add constraint employee_pk primary key (employee_id); create or replace procedure merge_employee( i_emp_id in integer, i_emp_name in varchar2 ) is begin merge into employees e using (select i_emp_id id, i_emp_name name from dual) s on (e.employee_id = s.id) when matched then update set employee_name = when not matched then insert (employee_id, employee_name) values (s.id, s.name); end; / With the merge statement, we now have a single more complex query instead of the 3 we started with, or the two of the refined approach.

create table Employee( 2 ID VARCHAR2(4 BYTE) NOT NULL, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 / Table created.

If the more likely case is that existing records will be updated, the code below is better: begin update employees set ....

where employee_id = i_employee_id; if sql%rowcount = 0 then -- no rows were updated, so the record does not exist insert into employees ( ... ); end if; end; When any SQL statement is executed in PLSQL, the SQL%ROWCOUNT variable will contain the number of rows affected (in this case updated) by the most recent query. The MERGE statement takes a list of records which are usually in a staging table, and adds them to a master table.

1) Row level trigger - An event is triggered for each row upated, inserted or deleted.

2) Statement level trigger - An event is triggered for each sql statement executed.

Trigger names do not need to be unique with respect to other schema objects, such as tables, views, and procedures.

Triggers can be: Trigger names must be unique with respect to other triggers in the same schema.

TABLEA and TABLEB consists of a DATE_T column which has a date and AMOUNT column, which has totals.

I'd like for the script to read TABLEA and either insert or update TABLEB based on date.

We can create a trigger to update the 'product_price_history' table when the price of the product is updated in the 'product' table.

1) Create the 'product' table and 'product_price_history' table Once the above update query is executed, the trigger fires and updates the 'product_price_history' table.

Leave a Reply