Examples of Oracle PL/SQL Cursors

Examples of Oracle PL/SQL Cursors:

1)

Declare

cursor c_1 is

select * from emp;

BEGIN

for emp_rec in c_1 loop

dbms_output.put_line(emp_rec.ename);

end loop;

END;

2)

create or replace procedure sum_of_salary IS

cursor c_1 is

select * from emp;

salary_sum integer;

BEGIN

salary_sum := 0;

for emp_rec in c_1 loop

salary_sum := salary_sum+ emp_rec.sal;

end loop;

dbms_output.put_line('Salary sum: ' || salary_sum);

END;

3)

Retrieving the records from the emp table and displaying them one by one using cursors:

CREATE OR REPLACE PROCEDURE my_proc IS

var_empno emp.empno%type;

var_ename emp.ename%type;

var_sal emp.sal%type;

//declaring a cursor//


CURSOR EMP_CURSOR IS

select empno, ename, sal from emp;

BEGIN

//opening a cursor//

open EMP_CURSOR;

LOOP

//fetching records from a cursor//

fetch EMP_CURSOR into var_empno, var_ename, var_sal;

//testing exit conditions//

EXIT when EMP_CURSOR%NOTFOUND;

IF (var_sal > 1000) then

DBMS_OUTPUT.put_line(var_empno || ' ' || var_ename || ' ' || var_sal);

ELSE

DBMS_OUTPUT.put_line(var_ename || ' sal is less then 1000');

END IF;

END LOOP;

//closing the cursor//

close EMP_CURSOR;

DBMS_OUTPUT.put_line('DONE');

END;

4)

Declare

CURSOR EMP_CURSOR IS

select * from emp;

variable_cur EMP%ROWTYPE;

BEGIN

open EMP_CURSOR;

LOOP

fetch EMP_CURSOR into variable_cur;

EXIT when EMP_CURSOR%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(variable_cur.empno || ' ' || variable_cur.ename ||' ' ||

variable_cur.job || ' ' ||variable_cur.mgr || ' ' ||

variable_cur.hiredate|| ' ' ||variable_cur.sal || ' ' ||

variable_cur.deptno);

END LOOP;

close EMP_CURSOR;

DBMS_OUTPUT.put_line('DONE');

END;

/

5)

Declare

CURSOR MY_CUR IS

select * from emp;

v_cur EMP%ROWTYPE;

BEGIN

open my_cur;

LOOP

fetch my_cur into v_cur;

EXIT when my_cur%ROWCOUNT = 5;

DBMS_OUTPUT.PUT_LINE(v_cur.empno || ' ' || v_cur.ename ||' ' ||

v_cur.job || ' ' ||v_cur.mgr || ' ' ||

v_cur.hiredate|| ' ' ||v_cur.sal || ' ' ||

v_cur.deptno);

END LOOP;

close my_cur;

DBMS_OUTPUT.put_line('DONE');

END;