A cursor is tied to a specific query. A cursor variable is not tied to a specific query. It is also known as a REF CURSOR. A REF CURSOR is considered strong if it includes a return type. A strong cursor variable type declaration is usually followed by the declaration of the cursor itself.
It is considered weak if it does not include a return type.
Strong REF CURSOR types are safer (less error prone) because you are limited to associating them with queries that return a specified set of columns. This is somewhat analogous to coding in a strongly typed language where variable types are clearly defined and casting is very controlled. Weak REF CURSOR types are more flexible because this limitation is not enforced. Any query can be referenced - there is no type checking. This flexibility can lead to errors if an unexpected query is referenced by the cursor and the results contain unexpected values.
It is common to see legacy Oracle PL/SQL packages with REF CURSOR type declarations. Because a weak REF CURSOR has no declared structure, recent versions of Oracle include a the predefined type SYS_REFCURSOR that can be used wherever a weak ref cursor is needed. You can use this in recent Oracle versions instead of declaring your own REF CURSOR type.
declare rc sys_refcursor; rec employees%rowtype; begin open rc for select * from employees; loop fetch rc into rec; exit when rc%notfound; dbms_output.put_line(rec.first_name || ' ' || rec.last_name); end loop; close rc; open rc for select * from departments; dbms_output.put_line('Using cursor again.'); close rc; end; /
This is a simple example showing the use of %ROWTYPE and SYS_REFCURSOR.
declare rc sys_refcursor; v_string_data varchar2(400); v_switch NUMBER := 2; begin IF v_switch=1 THEN open rc for select first_name from employees; ELSE open rc for select department_name from departments; END IF; loop fetch rc into v_string_data; exit when rc%notfound; dbms_output.put_line(v_string_data); end loop; close rc; end;
This demonstration shows the flexibility available through the use of a weak REF CURSOR. A SYS_REFCURSOR is used to open one of two possible queries with different rows as results. Depending upon the value of v_switch, a particular cursor is opened and the value fetched and displayed.