PL/SQL - CURSOR

CURSOR


A cursor is a private SQL work area. Whenever we issue a SQL statement the Oracle server opens an area of memory in which the command is parsed and executed. This area is called a cursor.

When the executable part of a block issues a SQL statement PL/SQL creates an implicit cursor which PL/SQL manages automatically.


There are two types of cursors.

- Implicit cursors.
- Explicit cursors.

The Oracle server uses implicit cursors to parse and execute SQL statements. Implicit cursor is declared for all DML and PL/SQL select statements., including queries that return only one row.

Explicit cursors are explicitly declared by the programmer. It is created for the queries that return more than one row and are declared and named by the programmer and manipulated through specific statements in the block’s executable section.

SQL Cursor Attributes :
SQL cursor attributes allow us to evaluate what happened when an implicit cursor was last used. We can use these attributes in the exception section of a block to gather information about the execution of a DML statement.

SQL%ROWCOUNT :

Number of rows affected by the most recent SQL statement.

SQL%FOUND :

Boolean attribute that evaluates to TRUE if the most recent SQL statement affects one or more rows.

SQL%NOTFOUND :

Boolean attribute that evaluates to TRUE if the most recent SQL statement does not affect rows.

SQL%ISOPEN :
Always evaluates to FALSE because PL/SQL closes implicit cursors immediately after they are executed.

Note : PL/SQL does not return an error if a DML statement does not affect any rows in the underlying table. However, if a select statement does not retrieve any rows PL/SQL returns an exception.


Eg: Deleting rows that have the specified empno and print the no of rows deleted.

Variable rows_deleted varchar2(30)
Declare
V_empno emp.empno%type :=7900;
Begin
Delete from emp where empno = v_empno;
:rows_deleted := (SQL%ROWCOUNT ‘rows deleted.’);
End;
/
Print rows_deleted;



Explicit cursor :

Use explicit cursor to individually process each row returned by a multiple row select statement. The set or rows returned by a multiple row query is called the active set.

Declaring the cursor:

CURSOR cursor_name IS select_statement;

Eg:
Declare
Cursor emp_cursor is
Select ename, empno from emp;
Begin
----


Opening the cursor:

Open cursor_name;

--> Open the cursor to execute the query and identify the active set.
If the query returns no rows no exception is raised.

--> Open is an executable statement that performs the following operations.

--> Dynamically allocates memory for a context area that eventually contains crucial processing information.
--> Parses the select statement.
--> Binds the input variables – sets the value for the input variable.
--> Identifies the active set – the set of rows that satisfy the search criteria. Rows in the active set are not retrieved into variables when the open statement is executed. Rather fetch statement retrieve the rows.
--> Positions the pointer just before the first row in the active set.


Fetching data from the cursor :

Fetch cursor_name into [variable,…..] / record_name ] ;

--> Retrieve the current row values into variables.
--> Match each variable to correspond to the columns positionally.
--> Test to see whether cursor contains rows.

Eg:

Loop
Fetch emp_cursor into v_empno, v_ename;
Exit when ….;
----
Process the retrieved data.
---
End loop;


Retrieve the first 10 employees one by one
Set serveroutput on

Declare
V_empno emp.empno%type;
V_ename emp.ename%type;

Cursor emp_cursor is
Select empno, ename from emp;
Begin
Open emp_cursor;
For I in 1..10 loop
Fetch emp_cursor into v_empno, v_ename;
Dbms_output.put_line(to_char(v_empno) ’ –‘ v_ename);
End loop;
End;



Closing the cursor:

Close cursor_name;

--> The close statement disables the cursor, and the active set becomes undefined.
--> It releases the context area.

Note: The maximum limit to the number of open cursors per user is 50.


Explicit cursor attributes :

%ISOPEN : Evaluates to TRUE if the cursor is open.

Eg:

If not emp_cursor%ISOPEN then
Open emp_cursor;
End if;
Loop
Fetch emp_cursor ….

%NOTFOUND : Evaluates to TRUE if the most recent fetch does not return a row.
Eg:
Loop
Fetch c1 into my_ename, my_sal;
Exit when c1%NOTFOUND; (or c1%NOTFOUND is NULL)
-----
End loop;


%FOUND : Evaluates to TRUE if the most recent fetch returns a row.

%ROWCOUNT : Evaluates to the total number of rows returned so far.

Eg:
Loop
Fetch c1 into my_ename, my_sal;
If c1%ROWCOUNT >10 then
----
End if;
----
End loop;


Declare
V_empno emp.empno%TYPE;
V_ename emp.ename%TYPE;
CURSOR emp_cursor IS
SELECT empno, ename from emp;
Begin
OPEN emp_cursor;
Loop
Fetch emp_cursor into v_empno, v_ename;
Exit when emp_cursor%ROWCOUNT > 10 or
Emp_cursor%NOTFOUND;
Dbms_ouput.put_line(to_char(v_empno’-‘ to_char(v_ename) ;
End loop;
Close emp_cursor;
End;



Cursors and Records :

Process rows of the active set by fetching values into PL/SQL RECORD.

Declare
Cursor emp_cursor is
Select empno, ename from emp;
Emp_record emp_cursor%ROWTYPE;
Begin
Open emp_cursor;
Loop
Fetch emp_cursor into emp_record;
----
declare
cursor emp_cursor is
select empno, ename from emp;
emp_record emp_cursor%rowtype;
begin
open emp_cursor;
loop
fetch emp_cursor into emp_record;
exit when emp_cursor%notfound;
inser into temp_emp (empid, empname) values (emp_record.empno, emp_record.ename);
end loop;
commit;
close emp_cursor;
end;



Cursor FOR loops :

A cursor FOR loop processes rows in an explicit cursor. It is a shortcut because the cursor is opened and rows are fetched once for each iteration in the loop the loop exists when the last row is processed and the cursor is closed automatically.


FOR record_name IN cursor_name LOOP
Statement1;
Statement2;
-----
End loop;

--> The cursor FOR loop is a shortcut to process explicit cursors.
--> Implicit open, fetch, exit and close cursor,
--> The record is implicitly declared.

Eg: Print a list of the employees who work for the sales department.

set serveroutput on
declare
cursor emp_cursor is
select empno, deptno from emp;
begin
for emp_record in emp_cursor
loop
if emp_record.deptno = 10 then
dbms_output.put_line(to_char(emp_record.empno));
end if;
end loop;
end;
/

Cursor FOR loops using subqueries :

No need to declare a cursor.

Set serveroutput on
Begin
For emp_record in (select empno, deptno from emp)
Loop
If emp_record.deptno = 10 then
Dbms_output.put_line(to_char(emp_record.empno));
End if;
End loop;
End;
/



Cursors with parameters :

We can pass parameters to the cursor in a cursor FOR loop. It means that we can open and close an explicit cursor several times in a block; returning a active set on each occasion. For each execution the previous cursor is closed and reopened with a new set of Each
parameters.formal parameter in the declaration section must have a corresponding actual parameter in the OPEN statement.

Cursor cursor_name
[(parameter_name datatype, …)]
Is
Select statement;


Open cursor_name(parameter, ----);

Eg:

Pass the deptno, job to the where clause in the cursor select statement.

Declare
Cursor emp_cursor
(p_deptno number, p_job varchar2)
Is
Select empno, ename from emp where deptno = p_deptno and job = p_job;
Begin
Open emp_cursor(10, ‘ACCOUNT’);
----
Close emp_cursor;
Open emp_cursor(20, ‘CLERK’);
--
End;

--------
Declare
Cursor emp_cursor( p_deptno number, p_job varchar2) is
Select empno, ename from emp where deptno = p_deptno and job = p_job;
Begin
For emp_record in emp_cursor(10, ‘SOFTWARE’)
Loop
--

The FOR UPDATE clause :

Locks the rows before update or delete.
Add the FOR UPDATE clause in the cursor query to lock the affected rows when the cursor is opened. Because the Oracle server releases locks at the end of the transaction we should not commit across fetches from an explicit cursor if FOR UPDATE is used.


Select --- from ---
FOR UPDATE [OF column_reference] [NOWAIT];

Column_reference : is a column in the table against which the query is performed.

NOWAIT : Returns an oracle error if the rows are locked by another session.

Eg:
Retrieve the employees who work in dept 10 and update their salary.

Declare
Cursor emp_cursor is
Select empno, ename, deptname from emp, dept where emp.deptno = dept.deptno
And emp.deptno = 10 FOR UPDATE OF sal NOWAIT;


Cursor with subqueries :

Declare
Cursor my_cursor is
Select t1.deptno, t1.deptname, t2.staff from dept t1, (select deptno, count(*) as staff from emp
Group by deptno) t2
Where t1.deptno = t2.deptno and t2.staff >=3;
-----

Subquery : A subquery is a query that appears within another SQL DML statement. When evaluated the subquery provides a value or set of values to the outer query. Subqueries are often used in the WHERE clause of a select statement. They can also used in the FROM clause creating a temporary data source for that query.