PL/SQL Cursors

Implicit Cursors

Implicit cursors are automatically created and used by Oracle every time you issue a select statement in PL/SQL. If you use an Implicit cursors, Oracle will perform the open, fetches, and close for you automatically. Implicit cursors are used in statements that return only one row. If the SQL statement returns more than one row, an error will occur.

In the following PL/SQL code block, the select statement makes use of an implicit cursor:

Begin

Update emp

Set empno=empno

Where 1=2;

Dbms_output.put_line(sql%rowcount ||' '|| ' rows are affected by the update statement');

End;

SELECT SUM (sal) INTO TOTAL

FROM emp

WHERE depno = 10;

Cursor For loops

Normally when you use explicit cursors, cursors have to be opened, the data has to be fetched, and finally the cursor needs to be closed. A cursor for loop automatically performs the open, fetch, and close procedures, which simplifies your job.

Declare

Cursor emp_cursor is

Select empno, ename,sal

From emp;

V_emp emp%RowType;

Begin

For emp_info IN emp_cursor LOOP

Dbms_output.put_line ('Employee id : '||emp_info.empno||'Employee

name : '|| emp_info.ename||'Employee salary :'||emp_info.sal);

End LOOP;

End;

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 cursors in the declare section at the beginning of the PL/SQL block. Once you declare your cursor, the explicit cursors will go through these steps:

1. Declare. This clause initializes the cursor into memory.


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


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


4. 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.

PL/SQL Sitemap     PL/SQL Resources