Oracle PL/SQL Explicit Cursors

Explicit cursors are created by the programmer, and with these you can do operations on a set of rows, which can be processed one by one. You use explicit cursors when you are sure that the SQL statement will return more than one row. You have to declare an explicit cursor in the declare section at the beginning of the PL/SQL block. Once you declare your cursor, the explicit cursor will go through these steps:

Declare This clause initializes the cursor into memory.

Open The previously declared cursor is now open and memory is allotted.

Fetch The previously declared and opened cursor can now access data;

Close The previously declared, opened, and fetched cursor is closed, which also releases memory allocation.

SQL> set serveroutput on;
SQL> Declare
2 Cursor empcursor(empn in number)
3 Is select * from emp where empno=empn;
4
5 Empvar emp%rowtype;
6 Begin
7 Dbms_output.put_line('Getting records for employee no. 7521');
8 Open empcursor(7521);
9 Loop
10 Fetch empcursor into empvar;
11 Exit when empcursor%notfound;
12 Dbms_output.put_line('emp name : ' || empvar.ename);
13 Dbms_output.put_line('emp salary : ' || empvar.sal);
14 End loop;
15 Close empcursor;
16 End;
17 /
Getting records for employee no. 7521
emp name : WARD
emp salary : 1250

PL/SQL procedure successfully completed.

Cursor Attributes

Here are the main cursor attributes:

%ISOPEN It returns TRUE if cursor is open, and FALSE if it is not.

%FOUND It returns TRUE if the previous FETCH returned a row and FALSE if it did not

%NOTFOUND It returns TRUE if the previous FETCH did not return a row and FALSE if it did.

%ROWCOUNT It gives you the number of rows the cursor fetched so far.

Some more examples of Explicit Cursors:

Declare

CURSOR cur_emp IS

select empno, ename from emp;

var_empno emp.empno%type;

var_ename emp.ename%type;

Begin

OPEN cur_emp;

FETCH cur_emp into var_empno,var_ename;

WHILE cur_emp%FOUND

Loop

Dbms_output.put_line(var_empno || ', ' || var_ename);

FETCH cur_emp into var_empno,var_ename;

End loop;

CLOSE cur_emp;

End;

Another approach:

Declare

CURSOR cur1_emp IS

select empno, ename from emp;

Begin

FOR r1_emp in cur1_emp

Loop

Dbms_output.put_line(r1_emp.empno || ', ' || r1_emp.ename);

End loop;

End;