Microsoft.NET

……………………………………………….Expertise in .NET Technologies

PL/SQL Cursors – Part 2

Posted by Ravi Varma Thumati on November 11, 2009

In SQL when you submit a query, it returns number of rows depends on query. It may be zero or may be hundreds. While in PL/SQL if your select statement returns multiple rows then oracle must return Too_many_rows error message (Exception).

In Such circumstances it is necessary to manipulate multiple rows through PL/SQL Block without raising Exception. The resource that Oracle provides to accomplish this job is the Cursor. PL/SQL cursors provide a way for your program to select multiple rows of data from the database and then to process each row individually. Specifically, a cursor is a name assigned by Oracle to every SQL statement processed. This is done in order to provide Oracle a means to direct and control all phases of the SQL processing Two kinds of cursor are used by Oracle: Implicit and Explicit. PL/SQL implicitly declares a cursor for every SQL statement. Implicit cursors are declared by Oracle for each UPDATE, DELETE, and INSERT SQL command. Explicit cursors are declared and used by the user to process multiple rows returned by a SELECT statement. Explicitly defined cursors are constructs that enable the user to name an area of memory to hold a specific statement for access at a later time.

A SELECT statements can return Zero, One, or many rows of data. When a PL/SQL cursor query returns multiple rows of data, the resulting group of rows is called the active set. This active set is stored by Oracle in the explicitly defined and named cursor that you create. The Oracle cursor is a mechanism used to easily process and manage each individual row that is selected by the cursor query. Another feature of the cursor is that it contains a pointer that keeps track of the current row being accessed, which enables your program to process the rows one at a time.

Following figure illustrates an Oracle cursor: It shows the active set, consisting of the rows returned by the cursor’s SELECT statement, and the pointer indicating the latest row fetched from the active set.

Explicit Cursor

User define cursor are known as Explicit cursor. Explicit cursor is one in which the cursor explicitly assigned to the select statement. Processing of explicit cursor involves four steps.

1)      Declare the cursor

2)      Open the cursor

3)      Fetch data from cursor

4)      Close the cursor

Declaring the cursor

The first step is to declare cursor in order for PL/SQL to reference the returned data. This must be done in the declaration portion of your PL/SQL block. Declaring a cursor accomplishes two goals:

  • It names the cursor
  • It associates a query with a cursor

The name you assign to a cursor is an undeclared identifier, not a PL/SQL variable. You cannot assign values to a cursor name or use it in an expression. This name is used in the PL/SQL block to reference the cursor query.

Cursor <cursor_name> is <select statement>;

Where, cursor_name is the name you assign to the cursor. SELECT statement is the query that returns row to the cursor active set.

In the following example, the cursor named cur_emp is defined with a SELECT statement that queries the employee table.

cursor cur_emp is select * from emp where ename like ‘A%’;

The only constraint that can limit the number of cursors is the availability of memory to manage the cursors.

Opening the Cursor

Opening the cursor activates the query and identifies the active set. When the OPEN command is executed, the cursor identifies only the rows that satisfy the query used with cursor definition. The rows are not actually retrieved until the cursor fetch is issued. OPEN also initializes the cursor pointer to just before the first row of the active set. Syntax to opening a cursor is:

Open <cursor_name>;

In this syntax, cursor_name is the name of the cursor that you have previously defined.

  • After a cursor is opened, until the moment you close it, all fetched data in the active set will remain static.

This means that the cursor will ignore all SQL DML commands (INSERT, UPDATE, DELETE and SELECT) performed on that data after the cursor was opened. Hence, you should open the cursor only when you need it.

Fetching Data from the Cursor

Getting data form the Activ Set is accomplished with FETCH command. The FETCH command retrieves the rows from the active set one row at a time. The FETCH command is usually used in conjunction with some type of iterative process. The first FETCH statement sorts the active set as necessary. In the iterative process, the cursor advances to the next row in the active set each time the FETCH command is executed. The FETCH command is the only means to navigate through the active set. Syntax for Fetching Data from the Cursor is:

Fetch <cursor_name> into <record_list>;

In this syntax, cursor_name is the name of the previously defined cursor from which you are now retrieving rows-one at a time. record_list is a list of variables that will receive the columns from the active set. The FETCH command places the results of the active set into these variables.

The record_list or Variable list is the structure that receives the data of fetched row. For each column value retrieved by the cursors’ query must have corresponding variable in the INTO list.

Additionally, their datatypes must be compatible. If you want to revisit a previously fetched row, you must close and reopen the cursor and then fetch each row in turn. If you want to change the active set, you must assign new values to the input variables in the cursor query and reopen the cursor. This re-creates the active set with the results of the revised query statement.

Closing the Cursor

The CLOSE statement closes or deactivates the previously opened cursor and makes the active set undefined. Oracle will implicitly close a cursor when the user’s program or session is terminated. After closing the cursor, you cannot perform any operation on it or you will receive and invalid_cursor exception. Syntax for closing a cursor:

Close <cursor_name>;

Where, cursor_name is the name of the previously opened cursor

Example

Declare

cursor cur_emp is select ename,Salary from emp;

nm emp.ename%type;

sal emp.salary%type;

Begin

open cur_emp;

fetch cur_emp into nm,sal;

dbms_output.put_line(‘Name : ‘ || nm);

dbms_output.put_line(‘Salary : ‘ || sal);

close cur_emp;

End;

/

Above cursor will store all the records of emp table into active set but display only first employee details. Because no iterative control is used with fetch statement.

To display all the employee details you should aware little about Cursor Attribute.

Explicit Cursor Attributes

Each cursor, whether it is explicitly or implicitly defined, carries with it attributes that provide useful data of the cursor. The four cursor attributes are %isopen, %rowcount, %found and %notfound.

These attributes can be used in any PL/SQL statement. Cursor attributes cannot be used against closed cursors, an invalid_cursor exception will be raised if you attempt this.

The %isopen Attribute

The %isopen attribute indicates whether the cursor is open. If the named cursor is open, then this attribute equates to true; otherwise, it will be false. The following example uses the %isopen attribute to open a cursor if it is not already open:

Example

Declare

cursor c1 is select * from emp;

Begin

open c1;

if c1%isopen then

dbms_output.put_line(‘cursor already open’);

else

open c1;

end if;

close c1;

End;

/

The %notfound Attribute

The %notfound attribute is useful in telling you whether a cursor has any rows left in it to be fetched. The %notfound Attribute equates to true when last fetch statement return no row (there are no more rows remaining in Active Set), while it equates to false if last fetch statement retuns row. Prior to the first fetch, this attribute will equate to null. An error will be returned if you evaluate %notfound on a cursor that is not opened. Following example illustrates use of %notfound attribute.

Example

Declare

cursor cur_emp is select * from emp where ename like ‘A%’;

emp_rec emp%rowtype;

Begin

open cur_emp;

loop

fetch cur_emp into emp_rec;

exit when cur_emp%notfound;

dbms_output.put_line(‘Name : ‘ || emp_rec.ename);

dbms_output.put_line(‘Age : ‘ || round((sysdateemp_

rec.bdate)/30, 0);

end loop;

close cur_emp;

End;

/

Above PL/SQL block of cursor will store all the records of employee into active set whose name start with A and display their name with the age.

The %found Attribute:

The %found attribute equates to true if the last FETCH statement returns row. Therefore, the %found attribute is a logical opposite of the %notfound attribute. The %found attribute equates to false when no rows are fetched. Like the %notfound, this attribute also equates to null prior to the first fetch.

The following example illustrates practical use of %found attribute

Example

Declare

cursor cur_emp is select ename,salary from emp;

nm emp.ename%type;

sal emp.salary%type;

Begin

open cur_emp;

loop

fetch cur_emp into nm,sal;

if cur_emp%found then

dbms_output.put_line(‘Name : ’|| nm);

dbms_output.put_line(‘Salary : ’|| sal);

else

exit;

end if;

end loop;

close cur_emp;

End;

/

The %rowCount Attribute:

The %rowCount attribute returns the number of rows fetched so far for the cursor. Prior to the first fetch, %rowcount is zero. There are many practical applications of the %rowcount attribute. The following example will perform a commit after the first 250 employees’ salaries are processed.

Example

Declare

cursor c1 is select * from emp where salary > 4000;

emp_rec emp%rowtype;

Begin

open c1;

loop

fetch c1 into emp_rec;

exit when c1%rowcount > 5;

dbms_output.put_line(emp_rec.ename);

end loop;

close c1;

End;

/

Above PL/SQL block of cursor will display only first five records whose salary is greater than 4000.

Automated Explicit Cursors (Cursor For Loop)

The previous section illustrated the basic mechanism of declaring and using cursors. In many programming situations, there is more than one way to code your logic. This also applies to PL/SQL cursors; there are opportunities to streamline or simplify the coding and usage of them. An alternate way to open, fetch and close the cursor Oracle furnishes another approach to place the cursor within a FOR Loop. This is known as a CURSOR FOR loop. A CURSOR FOR loop will implicitly.

  • Declare the Loop Index
  • Open the Cursor
  • Fetch the next row from the cursor for each loop iteration
  • Close the cursor when all rows are processed or when the loop exits

The Syntax for cursor for loop :

For <record_list> in <cursor_name>

Loop

Statements;

End loop;

CURSOR FOR loops are ideal when you want all the records returned by the cursor. With CURSOR FOR loops, you should not declare the record that controls the loop.

Example

Declare

cursor emp_cursor is select * from emp where deptno in (10,30);

emp_rec emp%rowtype;

Begin

for emp_rec in emp_cursor

loop

update emp set salary = salary + (salary * 0.10) where

empno = emp_rec.empno;

end loop;

End;

/

Implicit Cursor

As mentioned earlier, Oracle creates and opens a cursor for every SQL statement that is not part of an explicitly declared cursor. PL/SQL implicitly declares cursors for all SQL data manipulation statements, including queries that return one row. The most recent implicit cursor can be referred to as the SQL cursor. You cannot use the OPEN, CLOSE, and FETCH statements with the implicit cursor. However, you can use the cursor attributes to access information about the most recently executed SQL statement through the SQL cursor.

In the following example, PL/SQL creates an implicit cursor to identify the set of rows that are affected by the UPDATE command.

Example

Begin

update emp set comm = salary * 0.15 where deptno = 10;

if sql%notfound then

dbms_output.put_line(‘No record is updated’);

else

dbms_output.put_line(sql%rowcount|| ‘rows updated’);

end if;

End;

/

Implicit Cursors Attributes

Like explicit cursors, implicit cursors use attributes. The implicit cursor attributes are %isopen, %found, %notfound and %rowcount. Implicit cursors have no name; you must add a prefix SQL to the attributes. The implicit cursor contains information concerning the processing of the last SQL statement (INSERT,UPDATE,DELETE, and SELECT INTO). Implicit cursor attributes can be used only in PL/SQL statements and not in SQL Statements. The following section briefly describe each of these

The %isopen Attribute:

After the execution of the SQL statement, the associated SQL cursor is always closed automatically by Oracle. Hence the %isopen attribute always evaluate to false.

The %found Attribute:

This attribute will equate to true if an INSERT,UPDATE or DELETE affected one or more rows or a SELECT INTO returns one or more rows. Otherwise, it evaluates to false. %found will equate to null until a SQL DML statement is executed. The following is an example using the implicit %found attribute:

Example

Begin

Update emp set sal=3000 where ename like ‘A%’;

If sql%found then

commit;

Else

dbms_output.put_line(‘no such employee found’);

End if;

End;

/

The %notfound Attribute:

The %notfound attribute evaluates to true if the most recent SQL statement does not affect any rows. Otherwise, it will evaluate to false. The following example illustrates the implicit %notfound attribute.

Example

Begin

Update emp set sal=3000 where ename like ‘Himani’;

If sql%notfound then

dbms_output.put_line(‘no such employee found’);

Else

commit;

End if;

End;

/

Careful code design must be exercised when using the SELECT INTO statements. The %notfound attribute cannot be used immediately after the SELECT INTO statement when no records are retrieved. This is because the No_Data_Found exception will be raised and before the %notfound attribute is set. The following example illustrates this point:

Example

SQL>ED TRY

Declare

mename emp.ename%type;

Begin

select ename into mename from emp where empno=79;

if sql%notfound then

dbms_output.put_line(‘no such employee found’);

end if;

exception

when No_Data_Found then

dbms_output.put_line(‘no such employee found [handled in

exception]’);

End;

/

Output

SQL> @try2

No Such Employee Found [Handled in Exception]

PL/SQL procedure successfully completed.

The %rowcount Attribute:

This attribute equates to the total number of rows affected by the most recent SQL statement. An

example of the %rowcount follows:

Example

SQL>ED try

Begin

delete emp set sal = sal + (sal * 0.05)where ename like ‘S%’;

dbms_output.put_line(‘Total ‘||sql%rowcount||’ row(s) deleted’);

End;

/

Output

SQL> @try3

Total 2 row(s) deleted.

PL/SQL procedure successfully completed.

Parameterized Cursors

So far we have used cursors querying all the records from a table. For that the cursor must be declared in such a way that it recognizes that it will receive the requested value(s) at the time of opening the cursor. Such a cursor is known as Parameterized Cursor. Syntax for parameterized cursor is:

cursor cur_name(variable_name1 datatype, variable_name2 datatype …) is select statement…

The scope of cursor parameters is local to that cursor, which means that they can be referenced only within the query declared in the cursor declaration. The values of cursor parameters are used by the associated query when the cursor is opened.

For Example,

cursor c1(msal number) is select * from emp where salary > msal;

The parameters to a cursor can be passed in the open statement. They can either be constant values or the contents of a memory variable. Remember that the parameters of a cursor cannot return values like subprograms (discussed Later).

  • Each parameter in the declaration must have a corresponding value in the open statement.

For Example,

open cur_emp(5000);

OR

open cur_emp(sal);

where sal is a memory variable of the block.

  • Memory variable should be declared and the value should be assigned before using it in parameterized cursor.

Ref Cursor and Cursor Variable

A PL/SQL cursor is a named area in the database. The cursor variable, by definition, is a reference to that named area. Cursor variables point to a query’s work area in which the query’s resultset is stored. A cursor variable is also dynamic in nature because it is not tied to a specific query. Oracle retains this work area as long as a cursor pointer is pointing to it. You can use a cursor variable for any type-compatible query. This type of cursor is known as REF cursor. There are two type of REF cursors – Strong and Weak.

One of the most significant features of the cursor variable is that Oracle allows you to pass a cursor variable as an argument to a procedure or function call. The cursor variable cannot accept variables to itself.

After the cursor variable is declared there, you can pass it as a bind variable to your PL/SQL block. Likewise, these variables can be declared in other Oracle products such as Forms. Additionally, you can pass cursor variables back and forth between servers and applications through Oracle’s remote procedure calls.

The Cursor Variable Declaration

To create a cursor variable, you must first create a referenced cursor type and then declare a cursor variable on that type. Syntax for Creating the Cursor Type:

Type <cur_type_name> is ref cursor [return <return_type>];

In this syntax, ref stands for reference, cur_type_name is the name of the type of cursor, and return_type (Optional) is the data specification for the return cursor type.

Strong and Weak cursor is based on their return type. If return type is specified then it is Strong cursor while Weak cursor is a cursor whose return type is not specified.

The following example illustrates this declaration

Declare

—creation of Strong cursor type

Type emp_scur_type is ref cursor return emp%rowtype;

—creation of Weak cursor type

Type emp_wcur_type is ref cursor;

—creation of cursor variable based on cursor type

es emp_scur_type;

ew emp_wcur_type;

Begin

…..

End;

/

Remember, the cursor variable declaration does not create a cursor object, but rather a pointer to a cursor object. As such, you cannot substitute a cursor variable where a proper cursor is expected and required.

Cursor Usage with Cursor Variables

After the cursor variable is declared, you can use the variable in three different statements: OPEN…FOR, FETCH, and CLOSE.

You can assign a value to it through the OPEN…FOR cursor statement. Syntax for the OPEN…FOR

Cursor Statement:

open <cur_variable_name> for <select_statement>;

In this syntax, cur_variable_name is the name for the cursor, the cursor variable, or the host cursor variable, and select_statement is the appropriate SQL statement.

The OPEN…FOR statement executes the multirow query associated with the declared cursor variable.  The OPEN…FOR statement also identifies the resultset, which consists of all rows that meet the query search criteria.

The OPEN…FOR statements can open the same cursor variable for different queries as needed. After the cursor is opened, you can perform a typical FETCH using the cursor variable. The syntax for the FETCH statement using a cursor variable is the same as for a normal explicit static cursor, so we shall not discuss the same.

PL/SQL will make sure the return type of the cursor variable is compatible with the INTO clause of the FETCH statement. For each column value returned by the query associated with the cursor variable, there must be a corresponding variable in the INTO clause. Also, the number of fields or variables must equal the number of column values.

Strong Cursor

The following example puts together the concepts of declaring, opening, and fetching data from

Strong cursors:

Example

SQL>ED try

Declare

type emp_cur_type is ref cursor return emp%rowtype;

C1 emp_cur_type;

emprec emp%rowtype;

Begin

open C1 for select * from emp where sal > 3000;

dbms_output.put_line(‘list of employees getting salary more

than 3000’);

Loop

fetch C1 into emprec;

exit when C1%notfound;

dbms_output.put_line(emprec.empno || ‘ ‘ ||

emprec.ename || ‘ ‘ || emprec.salary);

End loop;

close C1;

open C1 for select * from emp where sal < 2000;

dbms_output.put_line(‘list of employees getting salary less

than 2000’);

Loop

fetch C1 into emprec;

exit when C1%notfound;

dbms_output.put_line(emprec.empno || ‘ ‘ || emprec.ename

|| ‘ ‘ || emprec.salary);

End loop;

close C1;

End;

/

Output

List of Employees getting Salary more than 3000

7566 JONES 4998

7782 CLARK 4527.6

7839 KING 6875

7902 FORD 5400

7934 MILLER 5000

List of Employees getting Salary less than 2000

7369 SMITH 1344

7499 ALLEN 1680

7521 WARD 1250

7654 MARTIN 1250

7698 BLAKE 1000

7844 TURNER 1500

7876 ADAMS 1848

7900 JAMES 1425

PL/SQL procedure successfully completed.

Check out this example

Declare

cursor cur_ref is ref cursor return emp%rowtype;

cur_var cur_ref;

Begin

open cur_var for select ename from emp;

End;

/

This select statement is not valid for this return type. If you want to select only ename from emp table then declaration must be as follows:

cursor cur_ref is ref cursor return emp.ename%type;

Weak Cursor

The following example puts together the concepts of declaring, opening, and fetching data from Weak cursors:

Example

Declare

type weak_type is ref cursor;

weak_cur weak_type;

eno emp.empno%type;

enm emp.ename%type;

sal emp.salary%type;

drec dept%rowtype;

Begin

open weak_cur for select empno, ename, salary from emp;

Loop

fetch weak_cur into eno, enm, sal;

exit when weak_cur%notfound;

dbms_output.put_line(eno|| ‘ ‘ || enm || ‘ ‘ || sal);

End loop;

Close weak_cur;

open weak_cur for select * from dept;

Loop

fetch weak_cur into drec;

exit when weak_cur%notfound;

dbms_output.put_line(drec.deptno || ‘ ‘ || drec.dname);

End loop;

Close weak_cur;

End;

/

Example

Declare

type weak_type is ref cursor;

weak_cur weak_type;

eno emp.empno%type;

enm emp.ename%type;

Sal emp.salary%type;

dno dept.deptno%type;

dnm dept.dname%type;

Begin

open weak_cur for select empno, ename, emp.deptno, salary,

dname from emp, dept where emp.deptno = dept.deptno;

Loop

fetch weak_cur into eno, enm, dno, sal, dnm;

exit when weak_cur%notfound;

dbms_output.put_line(eno|| ‘ ‘ || enm || ‘ ‘ ||dno || ‘ ‘

|| sal || ‘ ‘ || dnm);

End loop;

Close weak_cur;

End;

/

Output

7782 CLARK 10 4527.6 ACCOUNTING

7839 KING 10 6875 ACCOUNTING

7934 MILLER 10 5000 ACCOUNTING

7782 CLARK 10 4527.6 AD

7839 KING 10 6875 AD

7934 MILLER 10 5000 AD

7369 SMITH 20 1344 RESEARCH

7902 FORD 20 5400 RESEARCH

7876 ADAMS 20 1848 RESEARCH

PL/SQL procedure successfully completed.

Advertisements

One Response to “PL/SQL Cursors – Part 2”

  1. Hi, i think that i saw you visited my site thus i came to “return the prefer”.I’m trying to find issues to improve my web site!I assume its adequate to make use of a few of your ideas!!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: