Examples of Oracle PL/SQL Cursors

Examples of Oracle PL/SQL Cursors:

1)
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;

2)
Create a PL/SQL block to increase salary of employees in department 17.

a) The salary increase is 20% for the employees making less than $90,000 and 12% for the employees making $90,000 or more.
b) Use a cursor with a FOR UPDATE clause.
c) Update the salary with a WHERE CURRENT OF clause in a cursor FOR loop (cursor FOR loop problem).



PL/SQL procedure successfully completed.

3)
An example of an Implicit cursor



Oracle automatically associates an implicit cursor with the SELECT INTO statement and fetches the values for the variables var_firstname and var_lastname. After the SELECT INTO statement completes, Oracle closes the implicit cursor.