How to define the cursor type
Type ref_type_name is ref cursor [return return_type];
Declare cursor variable
Cursor_name ref_type_name;
From the technical bottom layer, the two are the same. Common PLSQL cursor is "static" in definition. While ref cursors can be opened dynamically.
For example:
Declare
Type RC is ref cursor;
Cursor C is select * from dual;
Rochelle cursor RC;
Begin
If (to_char (sysdate, 'dd') = 30) then
Open l_cursor for 'select * From emp ';
Elsif (to_char (sysdate, 'dd') = 29) then
Open l_cursor for select * from Dept;
Else
Open l_cursor for select * from dual;
End if;
Open C;
End;
/
L RC is opened dynamically based on logic, and the cursor C is defined and cannot be modified.
L ref cursor can be returned to the client, but cursor cannot.
L cursor can be global, and ref cursor must be defined in a process or function.
L ref cursor can be passed between subprograms, but cursor cannot.
L The static SQL defined in cursor is more efficient than ref cursor, so ref cursor is usually used to return the result set to the client.