Cursors in oracle for updating all records examples
Question: I have a ref cursor defined as an OUT parameter and I am trying to get a receiving PL/SQL to accept the ref cursor.Can I see some examples of working ref cursor examples, especially passing a ref cursor between two programs?
By strongly typing the cursor variable, you define what the cursor can return.
The cursor variable can also be returned by a function and assigned to another variable.
The REF CURSOR variable is not a cursor, but a variable that points to a cursor.
declare2 type auth_cursor is ref cursor return author%rowtype;3 c2 auth_cursor;4 r_c2 c2%rowtype;56 function get_auth return auth_cursor7 is8 c1 auth_cursor; 9 begin10 open c1 for select * from author;11 return c1; 12 end;1314 begin15 c2 := get_auth;16 loop17 fetch c2 into r_c2;18 exit when c2%notfound;19 dbms_output.put_line(initcap(r_c2.author_last_name));20 end loop;21 close c2;22 end;23 / Line 2 defines the cursor type.
Lines 3 and 4 define the cursor and return record used in the body.declare2 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 begin8 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.