Translate

Cursors and Cursor Types

Why we need Cursors? What are the advantages of using different types of Cursors?

  •  Cursor-Is a memory object that will be stored inside your RAM.
  •  Whenever Cursor executed inside the PL/SQL block memory allocated/deallocated automatically based on     the type of cursor.
  •  Cursor is used to access the result set present in the memory. This result set contains the records returned  on execution of a query.
  • The data that is stored in the cursor is called as Active Data Set so can also defined as a pointer to an Active Result set.
  • The TOO_MANY_ROWS Exception (ORA-01422) occurs when a SELECT INTO statement returns more than one row. Cursor helps us to avoid this error.
  • Opening the cursor executes the query and identifies the result set, which consists of all rows that meet the query search criteria. For cursors declared using the FOR UPDATE clause, the OPEN statement also locks those rows.
By default, a SELECT INTO statement must return only one row. Otherwise, PL/SQL raises the predefined exception TOO_MANY_ROWS and the values of the variables in the INTO clause are undefined. Make sure Your WHERE clause is specific enough to only match one row.

To know how Cursor works internally- Click here.

Types of Cursors-
  1.         Implicit Cursor
  2.         Explicit Cursor (Simple, Parameterized and Ref Cursor)
  3.         Package Cursor
Implicit Cursor (Predefined)-

Opened - Fetched and closed automatically.
Implicit Cursor is created whenever we execute SQL within a PL/SQL Block and we don’t have any control over it as it is controlled by Oracle itself and also it doesn’t have any name.

Attributes of Implicit Cursor-
  • SQL%OPEN- Will always return FALSE
  • SQL%FOUND- Will return TRUE whenever previous SQL statement is executed successfully.
  • SQL%NOTFOUND- Opposite to SQL%FOUND- Will return FALSE if previous SQL statement executed successfully.
  • SQL%ROWCOUNT- Will return no of rows affected by the previously executed statement.
Example- Have a look on below example - we are deleting records from table and then checking total number of rows removed using Cursor attribute.

Begin
Delete from EMP_B where Dept_No=10;
If (SQL%FOUND) Then
DBMS_OUTPUT.PUT_LINE (‘Number of records Deleted- ‘|| SQL%ROWCOUNT);
Else
DBMS_OUTPUT.PUT_LINE (‘No records to be deleted !!!’);
End If;
END;

SQL> SELECT * From EMP_B Order By DEPT_NO;

        ID NAME          DEPT_NO
---------- ---------- ----------
       101 Ravi               10
       110 Aman               10
       104 Manu               10
       107 Akhil              20
       111 Nik                20
       109 Shiva              20
       106 Subho              20
       113 Shaan              30
       102 Rajan              30
       105 Gaurav             30
       103 Pritesh            30
       108 Neeraj             40
       112 Sri                40

13 rows selected.


SQL> Begin

  2  Delete from EMP_B where Dept_No=10;
  3  If (SQL%FOUND) Then
  4  DBMS_OUTPUT.PUT_LINE ('Number of records Deleted- '|| SQL%ROWCOUNT);
  5  Else
  6  DBMS_OUTPUT.PUT_LINE ('No records to be deleted !!!');
  7  End If;
  8  End;
  9  /
Number of records Deleted- 3

PL/SQL procedure successfully completed.

SQL> SELECT * From EMP_B Order By DEPT_NO;

        ID NAME          DEPT_NO
---------- ---------- ----------
       107 Akhil              20
       111 Nik                20
       109 Shiva              20
       106 Subho              20
       113 Shaan              30
       105 Gaurav             30
       103 Pritesh            30
       102 Rajan              30
       108 Neeraj             40
       112 Sri                40

10 rows selected.


Explicit Cursor (User Defined)-

Created by users for processing multiple records to avoid too many rows error. Explicit cursor will have a name associated with it. An explicit cursor is defined in the declaration section of the PL/SQL Block
  • DECLARE the cursor in the declaration section.
  • OPEN the cursor in the Execution Section.
  • FETCH the data from cursor into PL/SQL variables or records in the Execution Section.
  • CLOSE the cursor in the Execution Section before you end the PL/SQL Block.
Attributes of Explicit Cursor-
  • Cur_Name%Open- Will return True if cursor is Open Else False.
  • Cur_Name%Found- Will return TRUE whenever previous SQL statement is executed successfully.
  • Cur_Name%NotFound- Opposite to SQL%FOUND- Will return FALSE if previous SQL statement executed successfully.
  • Cur_ Name%ROWCOUNT- Will return no of rows affected by the previously executed statement.
Explicit Cursor is categorized as –
  •          Simple Cursor
  •          Parameterized Cursor
  •           Ref Cursor
Simple Cursor-

 Query remain constant only where condition changes based on the requirement. In the below example value is hard coded.

Declare
Cursor Cur_Emp Is
Select Name,Salary from EMP_TEST Where DeptNO=10;
Cursor Cur_Emp2 Is
Select Name,Salary from EMP_TEST where DeptNo=20;
Vnm Emp_TEST.NAME%Type;
Vsal Emp_TEST.Salary%Type;
Begin
                  Open Cur_Emp;
                  Loop
                  Fetch Cur_Emp Into Vnm, Vsal;
                  Exit When Cur_Emp%NOTFOUND;
                  DBMS_OUTPUT.PUT_LINE(Vnm || '-'|| Vsal);
                  End Loop;
                  DBMS_OUTPUT.PUT_LINE('Cursor1 Row Count : ' ||Cur_Emp%ROWCOUNT);
                  Close Cur_Emp;

                  Open Cur_Emp2;
                  Loop
                  Fetch Cur_Emp2 Into Vnm, Vsal;
                  Exit When Cur_Emp2%NOTFOUND;
                  DBMS_OUTPUT.PUT_LINE(Vnm || '-'|| Vsal);
                  End Loop;
                  DBMS_OUTPUT.PUT_LINE('Cursor2 Row Count : ' ||Cur_Emp2%ROWCOUNT);
                  Close Cur_Emp2;

End;

Let's execute above PL/SQL Block :

 
SQL> SELECT * From EMP_TEST;

        ID NAME           SALARY STATUS         DEPTNO  MANAGERID
---------- ---------- ---------- ---------- ---------- ----------
       102 Ankit            8000 Active             20        101
       104 Nikhil          69000 Active             10        101
       105 Rajan           18000 Active             20        112
       107 Karan          101000 Active             20        112
       110 Sajal           88000 Active             10        101

SQL> Declare
  2  Cursor Cur_Emp Is
  3  Select Name,Salary from EMP_TEST Where DeptNO=10;
  4  Cursor Cur_Emp2 Is
  5  Select Name,Salary from EMP_TEST where DeptNo=20;
  6  Vnm Emp_TEST.NAME%Type;
  7  Vsal Emp_TEST.Salary%Type;
  8  Begin
  9  Open Cur_Emp;
 10  Loop
 11  Fetch Cur_Emp Into Vnm, Vsal;
 12  Exit When Cur_Emp%NOTFOUND;
 13  DBMS_OUTPUT.PUT_LINE(Vnm || '-'|| Vsal);
 14  End Loop;
 15  DBMS_OUTPUT.PUT_LINE('Cursor1 Row Count : ' ||Cur_Emp%ROWCOUNT);
 16  Close Cur_Emp;
 17
 18  Open Cur_Emp2;
 19  Loop
 20  Fetch Cur_Emp2 Into Vnm, Vsal;
 21  Exit When Cur_Emp2%NOTFOUND;
 22  DBMS_OUTPUT.PUT_LINE(Vnm || '-'|| Vsal);
 23  End Loop;
 24  DBMS_OUTPUT.PUT_LINE('Cursor2 Row Count : ' ||Cur_Emp2%ROWCOUNT);
 25  Close Cur_Emp2;
 26  End;
 27  /
Nikhil-69000
Sajal-88000
Cursor1 Row Count : 2

Ankit-8000
Rajan-18000
Karan-101000
Cursor2 Row Count : 3

PL/SQL procedure successfully completed.


Parameterized Cursor- (Reusability and Maintainability)

Limitation of above cursor is resolved using Parameterized cursor. It has ability to manipulate conditions at run time.

Declare
Cursor C1(V_deptno NUMBER) IS
Select Name, Salary From Emp_Test Where Deptno=V_deptno;
Begin
For rec in C1(10)
Loop
DBMS_OUTPUT.PUT_LINE('Employee – ' || rec.Name || ' Has Salary – ' || rec.Salary);
End Loop;

End;

SQL> SELECT * From EMP_TEST;

        ID NAME           SALARY STATUS         DEPTNO  MANAGERID
---------- ---------- ---------- ---------- ---------- ----------
       102 Ankit            8000 Active             20        101
       104 Nikhil          69000 Active             10        101
       105 Rajan           18000 Active             20        112
       107 Karan          101000 Active             20        112
       110 Sajal           88000 Active             10        101

SQL> Declare
  2  Cursor C1(V_deptno NUMBER) IS
  3  Select Name, Salary From Emp_Test Where Deptno=V_deptno;
  4  Begin
  5  For rec in C1(10)
  6  Loop
  7  DBMS_OUTPUT.PUT_LINE('Employee - ' || rec.Name || ' Has Salary - ' || rec.Salary);
  8  End Loop;
  9  End;
 10  /
Employee - Nikhil Has Salary - 69000
Employee - Sajal Has Salary - 88000

PL/SQL procedure successfully completed.


Example – Print Department Wise Employee names and their Salary.

Declare
Cursor C_Dept IS
Select Distinct DeptNo From EMP_TEST Order By DeptNO;
Cursor C1(V_Deptno NUMBER) IS
Select Name, Salary From Emp_Test Where Deptno=V_Deptno;
Begin
For rec IN C_Dept Loop
                  DBMS_OUTPUT.PUT_LINE('DeptNo – ' || rec.Deptno);
For rec1 IN C1(rec.Deptno) Loop
                  DBMS_OUTPUT.PUT_LINE('Employee ' || rec1.Name || ' Has Salary – ' || rec1.Salary);
End Loop;
End Loop;

End;


SQL> SELECT * From EMP_TEST;

        ID NAME           SALARY STATUS         DEPTNO  MANAGERID
---------- ---------- ---------- ---------- ---------- ----------
       102 Ankit            8000 Active             20        101
       104 Nikhil          69000 Active             10        101
       105 Rajan           18000 Active             20        112
       107 Karan          101000 Active             20        112
       110 Sajal           88000 Active             10        101

SQL> Declare
  2  Cursor C_Dept IS
  3  Select Distinct DeptNo From EMP_TEST Order By DeptNO;
  4  Cursor C1(V_Deptno NUMBER) IS
  5  Select Name, Salary From Emp_Test Where Deptno=V_Deptno;
  6  Begin
  7  For rec IN C_Dept Loop
  8  DBMS_OUTPUT.PUT_LINE('DeptNo - ' || rec.Deptno);
  9  For rec1 IN C1(rec.Deptno) Loop
 10  DBMS_OUTPUT.PUT_LINE('Employee ' || rec1.Name || ' Has Salary - ' || rec1.Salary);
 11  End Loop;
 12  End Loop;
 13  End;
 14  /
DeptNo - 10
Employee Nikhil Has Salary - 69000
Employee Sajal Has Salary - 88000
DeptNo - 20
Employee Ankit Has Salary - 8000
Employee Rajan Has Salary - 18000
Employee Karan Has Salary - 101000

PL/SQL procedure successfully completed.


Ref Cursor- (Type of Explicit Cursor)

Reference cursor works on the concept of Dynamic SQL.
It can store different result set at different point of time.

Ref cursor is user defined data type- By using this we can create multiple number of variable(s) - With a single variable we can retrieve different result set and very much useful to return multiple records from SP or Function.

Example-
Declare
Type test1 is Ref Cursor;
Var test1;
Vrec EMP_TEST%ROWTYPE;
Vrec2 DEPT_TEST%ROWTYPE;
Begin
DBMS_OUTPUT.PUT_LINE('----------------EMPLOYEES-----------------------');
Open Var for 'Select * from EMP_TEST';
Loop
Fetch Var into Vrec;
Exit When Var%NotFound;
DBMS_OUTPUT.PUT_LINE(Vrec.Name);
End Loop;
DBMS_OUTPUT.PUT_LINE('----------------DEPARTMENTS-----------------------');
Open Var for 'Select * from DEPT_TEST';
Loop
Fetch Var into Vrec2;
Exit When Var%NotFound;
DBMS_OUTPUT.PUT_LINE(Vrec2.DEPARTMENT);
End Loop;
Close Var;
End;


SQL> SELECT * From EMP_TEST;

        ID NAME           SALARY STATUS         DEPTNO  MANAGERID
---------- ---------- ---------- ---------- ---------- ----------
       102 Ankit            8000 Active             20        101
       104 Nikhil          69000 Active             10        101
       105 Rajan           18000 Active             20        112
       107 Karan          101000 Active             20        112
       110 Sajal           88000 Active             10        101

SQL> Declare
  2  Type test1 is Ref Cursor;
  3  Var test1;
  4  Vrec EMP_TEST%ROWTYPE;
  5  Vrec2 DEPT_TEST%ROWTYPE;
  6  Begin
  7  DBMS_OUTPUT.PUT_LINE('----------------EMPLOYEES-----------------------');
  8  Open Var for 'Select * from EMP_TEST';
  9  Loop
 10  Fetch Var into Vrec;
 11  Exit When Var%NotFound;
 12  DBMS_OUTPUT.PUT_LINE(Vrec.Name);
 13  End Loop;
 14  DBMS_OUTPUT.PUT_LINE('----------------DEPARTMENTS-----------------------');
 15  Open Var for 'Select * from DEPT_TEST';
 16  Loop
 17  Fetch Var into Vrec2;
 18  Exit When Var%NotFound;
 19  DBMS_OUTPUT.PUT_LINE(Vrec2.DEPARTMENT);
 20  End Loop;
 21  Close Var;
 22  End;
 23  /

----------------EMPLOYEES-----------------------
Ankit
Nikhil
Rajan
Karan
Sajal
----------------DEPARTMENTS-----------------------
Security
ENU
Insurance
Retail
BFSI
CISCO
Accounts
Finance

PL/SQL procedure successfully completed.


Ref cursors are of two types -

1) Strong ref cursor: Which returns value.
When return type included then it is called strong or static structure type. Static ref cursor supports different type of select statement but all of same structure, but not necessary that the table should be same.

2) Weak ref cursor: Which doesn't return value.
Weak ref cursor allows us to any type of select statement irrespective of data structure.

Strong cursor example 

Declare
 Type Emprefcur is Ref Cursor Return EMP_TEST%rowtype;
 ec Emprefcur;
 v_ec ec%rowtype;
Begin
 Open ec For Select * From EMP_TEST;
 Loop
      Fetch ec into v_ec;
      Exit when ec%notfound;
      DBMS_OUTPUT.PUT_LINE(v_Ec.ID||' - '||v_ec.Name);
 End loop;
Close ec;
DBMS_OUTPUT.PUT_LINE('-------------------------------------------------------------------------');
 Open ec For Select * From EMP_TEST;
 Loop
      Fetch ec into v_ec;
      Exit when ec%notfound;
      DBMS_OUTPUT.PUT_LINE(v_Ec.ID||' - '||v_ec.Name);
 End loop;
Close ec;
End;


Weak cursor example 

Declare
Type refcur is Ref cursor;
xc refcur;
v_Ec EMP_TEST%rowtype;
v_dc DEPT_TEST%rowtype;
Begin
  Open xc For Select * From EMP_TEST;
Loop
  Fetch xc into v_ec;
  Exit when xc%notfound;
  DBMS_OUTPUT.PUT_LINE(v_ec.Name ||'-'||v_Ec.ID);
End loop;
Close xc;
DBMS_OUTPUT.PUT_LINE('--------------------------------------');
Open xc For Select * From DEPT_TEST;
Loop
     Fetch xc into v_dc;
     Exit  when xc%notfound;
  DBMS_OUTPUT.PUT_LINE(v_dc.Deptno||'-'||v_dc.Department);

End loop;
Close xc;
End;
**Package Cursor**

A Package that returns a Cursor type is a package cursor.
In a package cursor, the SQL statement for the cursor is attached dynamically at runtime from calling procedures.

Example-
Create or Replace Package Pkg_Util is
        Cursor c_emp is Select * from EMP_TEST;
        r_emp c_emp%ROWTYPE;
End;
  

/*Another package using this package*/
Create or Replace Package Body pkg_aDifferentUtil is
Procedure p_printEmps is
    Begin
          Open pkg_Util.c_emp;
          Loop
                 Fetch pkg_Util.c_emp into pkg_Util.r_emp;
                 Exit when pkg_Util.c_emp%NOTFOUND;
                 DBMS_OUTPUT.Put_Line(pkg_Util.r_emp.NAME);
          End loop;
          Close pkg_Util.c_emp;
    End;
End;

You can use any of the above Cursor types as per your requirement.


Get involved and leave your Comments in the Box Below. The more people get involved, the more we all benefit. So, leave your thoughts before you leave the page. 

4 comments:

Subscribe to: Post Comments (Atom)