Updating cursor


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.



However, for faster performance and reliable field order, it is recommended that the list of fields be narrowed to only those that are actually needed. Additional information can be accessed using tokens (such as An optional expression that limits the records returned.

Use numbers between say 1 to see what works best for you. ) 30 procedure do_it 31 is 32 l_record emp Rec; 33 l_array_size number default 5; 34 l_done boolean; 35 l_cnt number default 1; 36 37 cursor c is select emp.*, rowid, 'N' from emp; 38 begin 39 40 open c; 41 loop you might want to use dbms_application_info. One would use a record of arrays in 8i to facilitate bulk collects whilst using a record.