Updating inside an oracle function

25-Jan-2018 12:05

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.

There's no problem understanding this logic, but depending on the quantity of data involved, this could be a very inefficient implementation.

We'd really love to take advantage of the recent (Oracle 8i) addition of the with records prior to Oracle 9i Release 2, however, we'd need to select each element in the select list into its own collection; this technique is shown in Example 2.

, and before you increment it, you need to get its current value.

This current value will be used as an ID for some operation, so concurrent sessions must not get the same value.

In summary, before Oracle 9i Release 2, records offered significant advantages for developers, but also left us frustrated because of the limitations on their usage.

Oracle 9i Release 2 goes a long way in relieving (but not completely curing us of) our frustrations.

updating inside an oracle function-51

of mandating the

If we change the structure of the books table, all we have to do is recompile the preceding code and bestseller will take on the new structure of that table.DECLARE TYPE employee_ids_t IS TABLE OF employees.employee_id%TYPE INDEX BY BINARY_INTEGER; ... v_emprecs emp_util.emprec_tab_t; CURSOR cur IS SELECT employee_id, ... LAST LOOP v_emprecs (j).employee_id := v_employee_ids (j); ...FROM employees WHERE hire_date = TO_DATE( '25-JUN-1997', 'DD-MON-YYYY'); BEGIN OPEN cur; FETCH cur BULK COLLECT INTO v_employee_ids, ... Suppose, for example, that we'd like to retrieve all employees hired before June 25, 1997, and then give them all big, fat raises.

A very straightforward way to write the logic for this is shown in Example 1.And if you're using to declare the argument, the "shape" of the record (numbers and types of fields) will adjust automatically with changes to the underlying cursor or table.

Here is our Free Online Chat Rooms and another chat room, Chatting and Uk Chatter Box alternative.… continue reading »

Read more