AIM: Implementing and usage of Cursors:

Cursors:

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

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


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>;
 

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 termi- nated. 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; 
/

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 at- tribute 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 evalu- ate %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 fo
llowing 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; 
/

Automated Explicit Cursors (Cursor For Loop):

The previous section illustrated the basic mechanism of declaring and using cursors. This also applies to PL/SQL cursors; there are opportunities to streamline or simplify the coding and usage of them. 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;

Aim: To write a Cursor to display the list of Male and Female Passengers:

DECLARE 
cursor c(jb varchar2) is select Name from Passenger where Sex=m; 
pr Passenger.Sex%type; 
BEGIN 
open c('m'); 
dbms_RESULT.put_line(' Name of Male Passenger are:'); 
loop 
fetch c into pr; 
exit when c%notfound; 
dbms_RESULT.put_line(pr); 
end loop; 
close c; 
open c('f'); 

dbms_RESULT.put_line(' Name of female Passengers are:'); 
loop 
fetch c into em; 
exit when c%notfound; 
dbms_RESULT.put_line(em); 
end loop; 
close c; 
END;

RESULT: 

Name of Male Passenger are: 
SACHIN 
rahul 
rafi 
salim 
riyaz 
Name of female Passengers are: 
swetha 
neha 
PL/SQL procedure successfully completed.