PL/SQL Cursors
 

Oracle SQL 

SQL Introduction    SQL Introduction 

SQL Insert    SQL Insert

SQL Update    SQL Update

SQL Delete    SQL Delete

SQL Joins   SQL Joins 

SQL Subqueries    SQL Subqueries

SQL Views    SQL Views

Oracle PL/SQL

PL/SQL Introduction    PL/SQL Introduction

PL/SQL Cursors    PL/SQL Cursors

PL/SQL Triggers    PL/SQL Triggers

PL/SQL Interview Questions    PL/SQL Questions

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 cursors:

Begin
Update emp 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 cursor 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.