So, we'll start with a table to update: [email protected]
now, we can define a record that represents a row in our table.In this case, it really represents an ARRAY of rows in our table. COM create or replace package body do_update 2 as 3 type rowid Array is table of rowid index by binary_integer; 4 5 type emp Rec is record 6 ( 7 empno dbms_sql.number_table, 8 ename dbms_sql.varchar2_table, 9 job dbms_sql.varchar2_table, 10 mgr dbms_sql.number_table, 11 hiredate dbms_sql.date_table, 12 sal dbms_sql.number_table, 13 comm dbms_sql.number_table, 14 deptno dbms_sql.number_table, 15 rowid rowid Array, 16 changed dbms_sql.varchar2_table 17 ); 18 19 this little routine just mimicks your setup.For more information on WHERE clauses and SQL statements, see Building a query expression and Specifying a query in Python.(The default value is None).An SQL prefix clause is positioned in the first position and will be inserted between the SELECT keyword and the SELECT COLUMN LIST.The Update is dependent on results of 19 procedures which are quite complex.I can not do a one hit update outside of the cursor loop.Note that we are fetching out the ROWID for updating (faster) and we are saving a changed flag as well [email protected]
We update any empno with an ODD empno 20 procedure Quo To Pur Days( p_empno in number, p_ename in out NOCOPY varchar2, p_changed in out NOCOPY varchar2 ) 21 is 22 begin 23 if (mod(p_empno,2) = 1 ) 24 then 25 p_ename := initcap( p_ename ); 26 p_changed := 'Y'; 27 end if; 28 end; 29 now for the meat of the routine.