Cursor

Ref Cursor with example

Ref Cursor:-

Ref Cursor is a dynamic cursor in Oracle. We can change cursor definition as run time but  it define and declare once.

Two type of Ref Cursor

Week Ref Cursor:- Week ref cursor never return a value.

Strong Ref Cursor:- Strong ref cursor must return a value.

Example of week    ref cursor
Example 1-

DECLARE
TYPE XXC_REF_CUR IS REF CURSOR;
XXC_REF_CUR_C1 XXC_REF_CUR;
EMP_ID EMPLOYEES.EMPLOYEE_ID%TYPE;
L_NAME EMPLOYEES.LAST_NAME%TYPE;
BEGIN
OPEN XXC_REF_CUR_C1 FOR
SELECT EMPLOYEE_ID,LAST_NAME
FROM EMPLOYEES;
LOOP
FETCH XXC_REF_CUR_C1 INTO EMP_ID,L_NAME;
EXIT WHEN XXC_REF_CUR_C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(‘EMPLOYEE_ID IS  ‘||EMP_ID||’ and ‘||’LAST_NAME IS  ‘||L_NAME);
END LOOP;
CLOSE XXC_REF_CUR_C1;
OPEN XXC_REF_CUR_C1 FOR
SELECT DEPARTMENT_ID,DEPARTMENT_NAME
FROM DEPARTMENTS;
LOOP
FETCH XXC_REF_CUR_C1 INTO DEPT_ID,DEPT_NAME;
EXIT WHEN XXC_REF_CUR_C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(‘DEPARTMENT ID IS  ‘||DEPT_ID||’ and ‘||’DEPARTMENT NAME IS  ‘||DEPT_NAME);
END LOOP;
CLOSE XXC_REF_CUR_C1;
END;

Example 2-

DECLARE
TYPE XXC_REF_CUR IS REF CURSOR;
XXC_REF_CUR_C1 XXC_REF_CUR;
EMP_REC EMPLOYEES%ROWTYPE;
DEPT_REC DEPARTMENTS%ROWTYPE;
BEGIN
OPEN XXC_REF_CUR_C1 FOR
SELECT *
FROM EMPLOYEES;
LOOP
FETCH XXC_REF_CUR_C1 INTO EMP_REC;
EXIT WHEN XXC_REF_CUR_C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(‘EMPLOYEE_ID IS  ‘||EMP_REC.EMPLOYEE_ID||’ and ‘||’LAST_NAME IS  ‘||EMP_REC.LAST_NAME);
END LOOP;
CLOSE XXC_REF_CUR_C1;
OPEN XXC_REF_CUR_C1 FOR
SELECT *
FROM DEPARTMENTS;
LOOP
FETCH XXC_REF_CUR_C1 INTO DEPT_REC;
EXIT WHEN XXC_REF_CUR_C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(‘DEPARTMENT ID IS  ‘||DEPT_REC.DEPARTMENT_ID||’ and ‘||’DEPARTMENT NAME IS  ‘||DEPT_REC.DEPARTMENT_NAME);
END LOOP;
CLOSE XXC_REF_CUR_C1;
END;

Example of Strong ref cursor

DECLARE
TYPE XXC_EMP_REF_CUR IS REF CURSOR RETURN EMPLOYEES%ROWTYPE;
XXC_EMP_REF_CUR_C1 XXC_EMP_REF_CUR;
TYPE XXC_DEPT_REF_CUR IS REF CURSOR RETURN DEPARTMENTS%ROWTYPE;
XXC_DEPT_REF_CUR_C1 XXC_DEPT_REF_CUR;
EMP_REC EMPLOYEES%ROWTYPE;
DEPT_REC DEPARTMENTS%ROWTYPE;
BEGIN
OPEN XXC_EMP_REF_CUR_C1 FOR
SELECT *
FROM EMPLOYEES;
LOOP
FETCH XXC_EMP_REF_CUR_C1 INTO EMP_REC;
EXIT WHEN XXC_EMP_REF_CUR_C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(‘EMPLOYEE_ID IS  ‘||EMP_REC.EMPLOYEE_ID||’ and ‘||’LAST_NAME IS  ‘||EMP_REC.LAST_NAME);
END LOOP;
CLOSE XXC_EMP_REF_CUR_C1;
OPEN XXC_DEPT_REF_CUR_C1 FOR
SELECT *
FROM DEPARTMENTS;
LOOP
FETCH XXC_DEPT_REF_CUR_C1 INTO DEPT_REC;
EXIT WHEN XXC_DEPT_REF_CUR_C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(‘DEPARTMENT ID IS  ‘||DEPT_REC.DEPARTMENT_ID||’ and ‘||’DEPARTMENT NAME IS  ‘||DEPT_REC.DEPARTMENT_NAME);
END LOOP;
CLOSE XXC_DEPT_REF_CUR_C1;
END;

SYS Ref Cursor:-

DECLARE
XXC_EMP_REF_CUR SYS_REFCURSOR;
EMP_REC EMPLOYEES%ROWTYPE;
BEGIN
OPEN XXC_EMP_REF_CUR FOR SELECT * FROM EMPLOYEES;
LOOP
FETCH XXC_EMP_REF_CUR INTO EMP_REC;
EXIT WHEN XXC_EMP_REF_CUR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(EMP_REC.EMPLOYEE_ID||CHR(9)||EMP_REC.LAST_NAME||CHR(9)||EMP_REC.HIRE_DATE);
END LOOP;
END;

 

————————- normal corsor ———————
declare
cursor c1 is select * from employees;
v1 employees%rowtype;
begin
open c1;
for i in 1..3
loop
fetch c1 into v1;
dbms_output.put_line(v1.employee_id);
end loop;
close c1;
end;

—————– week ref cursor ——————–
declare
type week_ref_cursor is ref cursor;
wrf week_ref_cursor;
v1 employees%rowtype;
v2 departments%rowtype;
begin
open wrf for select * from employees;
loop
fetch wrf into v1;
exit when wrf%notfound;
dbms_output.put_line(v1.employee_id||chr(9)||v1.last_name||chr(9)||v1.salary);
end loop;
close wrf;
open wrf for select * from departments;
loop
fetch wrf into v2;
exit when wrf%notfound;
dbms_output.put_line(v2.department_id||chr(9)||v2.department_name);
end loop;
close wrf;
end;

——————- strong ref cursor ————–

declare
type strong_ref_cursor is ref cursor return employees%rowtype;
src strong_ref_cursor;
v1 employees%rowtype;
begin
open src for select * from employees;
loop
fetch src into v1;
exit when src%notfound;
dbms_output.put_line(v1.employee_id);
end loop;
close src;
open src for select * from employees;
loop
fetch src into v1;
exit when src%notfound;
dbms_output.put_line(v1.employee_id||’    ‘||v1.last_name);
end loop;
close src;
end;

 

***************************************REF CURSOR *******************************

declare
emp_id number;
emp_name varchar2(20);
type test_cur is ref cursor;
v_ref test_cur;
begin
if(no=1)then
open v_ref for select employee_id from employees where rownum<9;
for i in 1..9
loop
fetch v_ref into emp_id;
dbms_output.put_line((emp_id));
end loop;
close v_ref;
elsif(no=2)then
open v_ref for select department_id from employees where rownum<9;
for i in 1..9
loop
fetch v_ref into emp_id;
dbms_output.put_line((emp_id));
end loop;
close v_ref;
else
open v_ref for select last_name from employees where rownum<9;
for i in 1..9
loop
fetch v_ref into emp_name;
dbms_output.put_line((emp_name));
end loop;
close v_ref;
end;
/

***************************************************** PARAMETERIZED CURSOR *************************************

declare
cursor c1(no number)is select employee_id from employees where employee_id<no;
id number;
begin
open c1(198);
for i in 1..10
loop
fetch c1 into id;
dbms_output.put_line(id);
end loop;
close c1;
end;

declare
type p1 is ref cursor;
t1 p1;
v1 varchar2(10);
begin
open t1 for select employee_id from employees;
for i in 1..10
loop
fetch t1 into v1;
dbms_output.put_line(v1);
end loop;
open t1 for select last_name from employees;
for i in 1..10
loop
fetch t1 into v1;
dbms_output.put_line(v1);
end loop;
end;
/

create or replace procedure p1 is
type ref_cursor is ref cursor;
ref1 ref_cursor;
v1 employees%rowtype;
begin
open ref1 for select * from employees;
for i in 1..10
loop
fetch ref1 into v1;
dbms_output.put_line(v1.employee_id||chr(9)||v1.last_name);
end loop;
end;
/

create or replace procedure p1(no number) is
type ref_cursor is ref cursor;
ref1 ref_cursor;
v1 employees%rowtype;
begin
if no=10 then
open ref1 for select * from employees;
for i in 1..10
loop
fetch ref1 into v1;
dbms_output.put_line(v1.employee_id||chr(9)||v1.last_name||chr(9)||v1.salary);
end loop;
elsif no=20 then
open ref1 for select * from employees;
for i in 1..10
loop
fetch ref1 into v1;
dbms_output.put_line(v1.salary||chr(9)||v1.manager_id);
end loop;
else
open ref1 for select * from employees;
for i in 1..10
loop
fetch ref1 into v1;
dbms_output.put_line(v1.manager_id);
end loop;
end if;
end;
/

create or replace procedure p1(no number) is
type ref_cursor is ref cursor;
ref1 ref_cursor;
type v1_table is table of employees%rowtype;
v1 v1_table;
begin
if no=10 then
open ref1 for select * from employees;
for i in 1..10
loop
fetch ref1 bulk collect into v1 limit 10;
for i in v1.first..v1.last
loop
dbms_output.put_line(v1(i).employee_id||chr(9)||v1(i).last_name||chr(9)||v1(i).salary);
end loop;
dbms_output.put_line(chr(10)||v1.count||chr(10));
end loop;
elsif no=20 then
open ref1 for select * from employees;
for i in 1..10
loop
fetch ref1 bulk collect into v1 limit 10;
for i in v1.first..v1.last
loop
dbms_output.put_line(v1(i).manager_id||chr(9)||v1(i).first_name||chr(9)||v1(i).salary);
end loop;
dbms_output.put_line(chr(10)||v1.count||chr(10));
end loop;
else
open ref1 for select * from employees;
for i in 1..10
loop
fetch ref1 bulk collect into v1 limit 10;
for i in v1.first..v1.last
loop
dbms_output.put_line(v1(i).manager_id);
end loop;
dbms_output.put_line(chr(10)||v1.count||chr(10));
end loop;
end if;
end;
/

 

Thanks

Sajal Agarwal

Advertisements