Recent Posts

Saturday, 23 July 2016

Cursors Tutorial


* Cursor is a temporary SQL work area which is used to fetch more than one record from existing table.

* Cursors are classified into two types those are
1. Implicit Cursors
      The cursor mechanism which is performed by the system internally those cursors can be called as implicit cursors.
2. Explicit Cursors
     The Cursor mechanism which is performed by the user manually those cursors can be called as explicit cursors.
     Whenever we are working with cursors with explicit cursors we need to perform following operations.
1. Declare Cursor
2. Open the cursor
3. Close the cursor
4. Fetch the records from the cursor.

Syntax to Create a Cursor
CURSOR<Cursor Name> IS SELECT * FROM <Table Name> WHERE <Condition>
E.g
Cursor C is select * from emp where deptno=20;
Open the cursor
Syntax
Open <Cursor name>
E.g
Open c;
Fetch the records from the Cursor Syntax
FETCH <Cursor Name> INTO <List of variables>
E.g
Fetch C into x,y,z;
Syntax to Close the Cursor Syntax
CLOSE <Cursor Name>
E.g
Close C;
A program which have no cursor.
declare
   a emp%rowtype;
begin
   select * into a from emp where deptno=&deptno;
   dbms_output.put_line(a.ename||' '||a.sal||' '||a.deptno);
end;
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 4
To overcome this program 
P) Write a PL/SQL Block to display employee details such as employee no, ename, salary when department no is passed as in value.
declare
   a emp%rowtype;
   cursor c1 is select * from emp where deptno=&deptno;
begin
   open c1;
   fetch c1 into a;
   dbms_output.put_line(a.ename||' '||a.sal||' '||a.deptno);
   close c1;
end;
     The above example fetches only one record because the statements are not lies within the loop. Whenever we are working with loops we need to use the following attributes.
1. %Found
2. %Not Found
3. %isOpen
4. %RowCount

1. %Found
* This attribute is used to check whether the record is found or not in the memory it always returns Boolean values that is either true or false.

* If the record is found then it is true if the records is not found then it is false
P) Write a PL/SQL Block display the employee details who are working under Sales Department.
declare
   a emp%rowtype;
   cursor c is select * from emp where deptno=&deptno;
begin
   open c;
   loop
   fetch c into a;
   if c% found then
      dbms_output.put_line(a.empno||' '||a.sal||' '||a.deptno);
   else
      exit;
   end if;
   end loop;
end;
Note 
     We can use Fetch command for empno also but it will display only one value.

2. %Isopen 
    This attribute is used to check whether the cursor is opened or not in memory.
E.g
declare
   a emp%rowtype;
   cursor c1 is select * from emp;
begin
   if c1% isopen then
       dbms_output.put_line('Cursor not opened');
   else
       open c1;
       dbms_output.put_line('Cursor  Opened');
   end if;
end;
3. %Not Found
     This attribute is used to check whether the record is found or not in the cursor. This attribute always say us Boolean value that is true or false if the record is found then it is false. If the record is not found then it is true.

4. %Row Count
     This attribute is used to count the number of records with in the cursor.
Reference Cursor
     It is special type of cursor. We can assign more than one select statement to same cursor.
declare
   type curs_bhar is ref cursor;
   a emp%rowtype;
   b dept%rowtype;
   c curs_bhar;
begin
   open c for select * from emp;
loop
   fetch c into a;
   if c%  found then
      dbms_output.put_line(a.empno||' '||a.ename||a.sal);
   else
      exit;
   end if;
end loop;
   open c for select * from dept;
loop
   fetch c into b;
   if c%  found then
      dbms_output.put_line(b.dname);
   else
      exit;
   end if;
end loop;
end;
Advantages of for loops using in the Cursors
1. No need to open the Cursor.
2. Fetch the records automatically.
3. It automatically check the end of the rows.
4. It automatically close the Cursor.
5. No need to declare the variables.
6. Code will be decreased.
7. Execution will be faster.
8. It is collection of information from cursor to variable (That is i).

For loop using the Cursor
E.g
declare
   cursor c is select * from emp;
begin
   for i in c loop
      dbms_output.put_line(i.deptno||' '||i.sal||' '||i.ename);
   end loop;
end;
CURSOR_ALREADY_OPEN
     This exception will be raised if the user trying to open the cursor within the for loop program.
declare
   cursor c is select * from emp where deptno =(select deptno from dept where loc ='NEW YORK') and ename like '%A%';
begin
   for i in c loop
      open c;
      dbms_output.put_line(i.empno||' '||i.ename||' '||i.sal);
   end loop;
exception
   when cursor_already_open then
      dbms_output.put_line('It is not necessary to open cursor in for loop');
end;
Cursors using joins
declare
   cursor c_emp is
      select ename,sal*12 ANNSAL,dname from emp,dept where emp.deptno = dept.deptno;
begin
   for i in c_emp
   loop
      dbms_output.put_line(i.ename || ' - ' || i.annsal || ' - ' || i.dname);
   end loop;
end;
Parameterized Cursors
declare
   cursor c(dno number) is select * from dept where deptno=dno;
begin
   for i in c(10) loop
      dbms_output.put_line(i.deptno||' '||i.dname);
   end loop;
end;

Next Tutorial  Sub Programs in PL/SQL

Previous Tutorial  Exception Handling in PL/SQL

No comments:

Post a Comment