On the Old，the New――sql文で文を生成する例

回転:http://www.oracle.com/technology/oramag/oracle/07-may/o37plsql.html
 
TECHNOLOGY：PL/SQL Practices
On the Old，the New，and ORA-2260 By Steven Feuerstein
Best practices for mangaging old and new information and preventing FOREALL errors
I want to auuudit udates to a table、including the before and afftervalues of each column n in mmmytable.To do this、I would love to pass：NEW and：OLD asargments to procedurerereres、but that doest doesn'seseseeemmmmmmmharerererererereaaaaaaaaaaaaaaaaaaaaaaaaaaaat t t t t thethethethetherererererererereree e e e e e e e e e e e e e e e e e e e e e e e e lumns are added、dropped、or renamed.Is this possible？This is how I am currently dong things:
 

 

CREATE TABLE load_a ( 

a1 VARCHAR2 (10), a2 VARCHAR2 (10) ) 

/



CREATE OR REPLACE TRIGGER ins_load_a

   AFTER UPDATE

   ON scott.load_a

   FOR EACH ROW

DECLARE

   a_rec   scott.load_a%ROWTYPE;

BEGIN

   a_rec.a1 := :OLD.a1;

   a_rec.a2 := :OLD.a2;

   save_old_values (a_rec);

END;

/

The bad news is that up through Oracle Database 10 g、you cannot pass：OLD and：NEW as argments to procedures.The good news that least you don't have to write all the coded needtcoded cored coded coded coded coded coded coded coded codettcocococoress.codents.coress.com。
Let's tare a step back:you want to pass the old and new values available inside a trigger to a stored procedure to process the data(write the information to a log table，execute validation lognic，and so)You Conninstead、You must copy the individual fields of those pspeudecords to a real recorded that can be passed to the procedure.
It certainly is tedious and time-consuming to write that code yourself，especially for tables with many columns.Fortunate，it is quite ease to generate the code you desire.
I offer you the gen_audit_トリガー_text procedure shown in Listing 1 to help you achieve your goal.I ran this program for the employes table and、after some formating、had the reulting code shown Listing 2.
Code Listing 1:gen_audit_トリガー_テキスト
 

CREATE OR REPLACE PROCEDURE gen_audit_trigger_text (

   table_in IN VARCHAR2

 , owner_in IN VARCHAR2 := USER

 , program_name_in IN VARCHAR2 := 'process_data'

)

IS

   c_rowtype           CONSTANT VARCHAR2 (100)     := table_in || '%ROWTYPE';

   l_columns            DBMS_SQL.varchar2s;



   PROCEDURE gen_copy_proc (old_or_new_in IN VARCHAR2)

   IS

   BEGIN

      DBMS_OUTPUT.put_line (   'FUNCTION copy_'

                            || old_or_new_in

                            || ' RETURN '

                            || c_rowtype

                            || ' IS l_return '

                            || c_rowtype

                            || '; BEGIN '

                           );



      FOR indx IN 1 .. l_columns.COUNT

      LOOP

         DBMS_OUTPUT.put_line (   '   l_return.'

                               || l_columns (indx)

                               || ' := '

                               || ':'

                               || old_or_new_in

                               || '.'

                               || l_columns (indx)

                               || ';'

                              );

      END LOOP;



      DBMS_OUTPUT.put_line ('RETURN l_return;');

      DBMS_OUTPUT.put_line ('END copy_' || old_or_new_in || ';');

   END gen_copy_proc;

BEGIN

   SELECT LOWER (column_name) column_name

   BULK COLLECT INTO l_columns

     FROM all_tab_columns

    WHERE owner = UPPER (owner_in) AND table_name = UPPER (table_in);



   DBMS_OUTPUT.put_line ('DECLARE');

   DBMS_OUTPUT.put_line ('   my_Old ' || table_in || '%ROWTYPE;');

   DBMS_OUTPUT.put_line ('   my_New ' || table_in || '%ROWTYPE;');

   gen_copy_proc ('old');

   gen_copy_proc ('new');

   DBMS_OUTPUT.put_line ('BEGIN');

   DBMS_OUTPUT.put_line ('   my_Old := copy_Old ();');

   DBMS_OUTPUT.put_line ('   my_New := copy_New ();');

   DBMS_OUTPUT.put_line ('   ' || program_name_in || '(my_Old, my_new);');

   DBMS_OUTPUT.put_line ('END;');

END gen_audit_trigger_text;

/

Code Listing 2:Result of gen_audit_トリガー_text procedure on employes table
 

DECLARE

   my_old    employees%ROWTYPE;

   my_new   employees%ROWTYPE;



   FUNCTION copy_old

      RETURN employees%ROWTYPE

   IS

      l_return employees%ROWTYPE;

   BEGIN

      l_return.employee_id := :OLD.employee_id;

      l_return.first_name := :OLD.first_name;

      l_return.last_name := :OLD.last_name;

      l_return.email := :OLD.email;

      l_return.phone_number := :OLD.phone_number;

      l_return.hire_date := :OLD.hire_date;

      l_return.job_id := :OLD.job_id;

      l_return.salary := :OLD.salary;

      l_return.commission_pct := :OLD.commission_pct;

      l_return.manager_id := :OLD.manager_id;

      l_return.department_id := :OLD.department_id;

      RETURN l_return;

   END copy_old;



   FUNCTION copy_new

      RETURN employees%ROWTYPE

   IS

      l_return employees%ROWTYPE;

   BEGIN

      l_return.employee_id := :NEW.employee_id;

      l_return.first_name := :NEW.first_name;

      l_return.last_name := :NEW.last_name;

      l_return.email := :NEW.email;

      l_return.phone_number := :NEW.phone_number;

      l_return.hire_date := :NEW.hire_date;

      l_return.job_id := :NEW.job_id;

      l_return.salary := :NEW.salary;

      l_return.commission_pct := :NEW.commission_pct;

      l_return.manager_id := :NEW.manager_id;

      l_return.department_id := :NEW.department_id;

      RETURN l_return;

   END copy_new;

BEGIN

   my_old := copy_old ();

   my_new := copy_new ();

   process_data (my_old, my_new);

END;