Ref cursor and cursor

Question

Can any one share the difference between ref cursor and cursor?

Answer 1

Cursor is associated with single SQL query.
It is not flexible that is why use dynamic cursor called as ref cursor to use same cursor for different queries.
--- ------------
Example,

Static cursor I want to get the results from a table :

DECLARE

Cursor get_info_cur
select col1, col2
from table1;

BEGIN
FOR REC IN get_info_cur LOOP
DBMS_OUTPUT.PUT_LINE('col1:' || REC.col1 || ' col2:' || REC.col2);
END LOOP;
END;
/

Maybe you want if exists a value to add it as a condition.

DECLARE

p_year varchar2(100);

TYPE cursor_type IS REF CURSOR;
v_cur cursor_type;
v_cursor_stmt varchar2(4000);

v_col1 table1.col1%type;
v_col2 table1.col2%type;

BEGIN

IF P_YEAR is not null then
v_cursor_stmt := 'SELECT col1, col2 FROM TABLE1 WHERE YEAR=' || p_year;
else
v_cursor_stmt := 'SELECT col1, col2 FROM TABLE1';
end;

OPEN v_cur FOR v_cursor_stmt;
LOOP
FETCH v_cur INTO v_col1, v_col2
EXIT WHEN v_cur%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('col1:' || v_col1 || ' col2:' || v_col2);

END LOOP;
CLOSE v_cur;

END;
/