Oracle if updating

The IF statement associates a condition with a sequence of statements enclosed by the keywords THEN and END IF.

If the condition is TRUE, the statements get executed, and if the condition is FALSE or NULL, then the IF statement does nothing.

For example, assume that the following update changed 75% of the table rows: shows a case where the developer forgot to include the SET condition in the UPDATE WHERE clause, causing high redo waits (log file parallel write waits, log file sync waits).

Simply including the existing state of the SET clause can result in a huger performance improvement for UPDATE statements: As more people adopt 64-bit servers with giant data buffers, we see a delay caused by the database writer process having to scan through giant data buffers seeking dirty blocks.

CREATE or REPLACE TRIGGER emp_after_update AFTER UPDATE OF empid ON emp FOR EACH ROW DECLARE BEGIN update emp_backup set empid = :new.empid where empid = :old.empid; DBMS_OUTPUT.

PUT_LINE('empid successfully updated into emp_backup table'); END; The above trigger named ’emp_after_update’ will be initiated whenever ’empid’ column in ’emp’ table gets updated.

Now before updating ’empid’ column in ’emp’ table see the ’emp’ and ’emp_backup’ table records.

Vishwanath Dalvi is a gifted engineer and tech enthusiast. When not hacking around or supporting the open source community, he is trying to overcome his phobia of dogs.

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.

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.

In this case, if it contain zero, it means the update failed to find any rows to update and therefore the record needs to be inserted instead. If the record exists in the master table, it should be updated with the new values in the staging table, otherwise insert the record from the staging table.

create or replace PACKAGE test_write_scalability IS TYPE piped_output IS RECORD ( idxes NUMBER , cmnd VARCHAR2(255) , seconds NUMBER , id1 NUMBER); TYPE piped_output_table IS TABLE OF piped_output; FUNCTION run(n IN number) RETURN test_write_scalability.piped_output_table PIPELINED; END; create or replace PACKAGE BODY test_write_scalability IS TYPE tmp IS TABLE OF piped_output INDEX BY PLS_INTEGER; FUNCTION run_insert(tbl IN NUMBER, d1 IN NUMBER) RETURN VARCHAR2 AS r2 NUMBER := CEIL(DBMS_RANDOM.

VALUE(1000000,9999999)); r3 NUMBER := CEIL(DBMS_RANDOM. VALUE(1000000,9999999)); r4 NUMBER := CEIL(DBMS_RANDOM. VALUE(1000000,9999999)); r5 NUMBER := CEIL(DBMS_RANDOM. VALUE(1000000,9999999)); BEGIN CASE tbl WHEN 0 THEN INSERT INTO scale_write_0 (id1, id2, id3, id4, id5) VALUES ( d1, r2, r3, r4, r5); WHEN 1 THEN INSERT INTO scale_write_1 (id1, id2, id3, id4, id5) VALUES ( d1, r2, r3, r4, r5); WHEN 2 THEN INSERT INTO scale_write_2 (id1, id2, id3, id4, id5) VALUES ( d1, r2, r3, r4, r5); WHEN 3 THEN INSERT INTO scale_write_3 (id1, id2, id3, id4, id5) VALUES ( d1, r2, r3, r4, r5); WHEN 4 THEN INSERT INTO scale_write_4 (id1, id2, id3, id4, id5) VALUES ( d1, r2, r3, r4, r5); WHEN 5 THEN INSERT INTO scale_write_5 (id1, id2, id3, id4, id5) VALUES ( d1, r2, r3, r4, r5); END CASE; RETURN 'insert'; END; FUNCTION run_delete(tbl IN NUMBER, d1 IN NUMBER) RETURN VARCHAR2 AS BEGIN CASE tbl WHEN 1 THEN DELETE FROM scale_write_1 WHERE id1 = d1; WHEN 2 THEN DELETE FROM scale_write_2 WHERE id1 = d1; WHEN 3 THEN DELETE FROM scale_write_3 WHERE id1 = d1; WHEN 4 THEN DELETE FROM scale_write_4 WHERE id1 = d1; WHEN 5 THEN DELETE FROM scale_write_5 WHERE id1 = d1; ELSE NULL; END CASE; IF SQL%ROWCOUNT 0 THEN RETURN 'delete'; ELSE RETURN NULL; END IF; END; FUNCTION run_update_all(tbl IN NUMBER, d1 IN NUMBER) RETURN VARCHAR2 AS r2 NUMBER := CEIL(DBMS_RANDOM.