Updating records using cursors

02-Oct-2019 17:16

c1%rowcount); 12 close c1; 13 if not c1%isopen then 14 dbms_output.put_line('The Cursor is closed.'); 15 end if; 16 end; 17 / The Cursor is open. Here some of the cursor attributes are used to process the cursor.

Notice that the record used to hold a fetched cursor row is defined as an author table "%rowtype".

The ref cursor can be assigned to other ref cursor variables.

This is a powerful capability in that the cursor can be opened, then passed to another block for processing, then returned to the original block to be closed.

Once the cursor type is defined, the actual variable can be defined as the cursor type.If a strongly typed cursor returns something other that its return type, a "ROWTYPE_MISMATCH" exception is raised.A strongly typed cursor type is less flexible but less prone to programming errors.In the example below, the cursor variable c1 is defined as three different statements.SQL declare 2 type auth_cursor is ref cursor; 3 c1 auth_cursor; 4 r_c1 author%rowtype; 5 r2_c1 book%rowtype; 6 r3_c1 number; 7 begin 8 open c1 for select * from author; 9 fetch c1 into r_c1; 10 close c1; 11 open c1 for select * from book; 12 fetch c1 into r2_c1; 13 close c1; 14 open c1 for select sum(quantity) 15 from store join sales using (store_key) 16 group by store_name; 17 fetch c1 into r3_c1; 18 close c1; 19 end; 20 / Although the block does not do anything but open and close the cursor variable, it does demonstrate that weakly typed variables can be defined differently each time the variable is opened.

Once the cursor type is defined, the actual variable can be defined as the cursor type.If a strongly typed cursor returns something other that its return type, a "ROWTYPE_MISMATCH" exception is raised.A strongly typed cursor type is less flexible but less prone to programming errors.In the example below, the cursor variable c1 is defined as three different statements.SQL declare 2 type auth_cursor is ref cursor; 3 c1 auth_cursor; 4 r_c1 author%rowtype; 5 r2_c1 book%rowtype; 6 r3_c1 number; 7 begin 8 open c1 for select * from author; 9 fetch c1 into r_c1; 10 close c1; 11 open c1 for select * from book; 12 fetch c1 into r2_c1; 13 close c1; 14 open c1 for select sum(quantity) 15 from store join sales using (store_key) 16 group by store_name; 17 fetch c1 into r3_c1; 18 close c1; 19 end; 20 / Although the block does not do anything but open and close the cursor variable, it does demonstrate that weakly typed variables can be defined differently each time the variable is opened.It allows a user to pass this "reference to the same cursor" among all the programs that need access to the cursor.