1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Cursor in Oracle

Discussion in 'SQL PL/SQL' started by SBH, Dec 1, 2010.

  1. SBH

    SBH Forum Expert

    Messages:
    99
    Likes Received:
    6
    Trophy Points:
    160
    Location:
    Bangalore
    1. Overview

    A cursor is any SQL query executed by Oracle server. For every cursor, Oracle server follows similar execution cycle. We shall discuss the cursor execution cycle and different types of cursors available.

    2. Introduction

    Cursor would be best defined as a memory pointer, which points to a location or context area. Context area is a chunk of memory where all SQL related processing is performed. SQL query result (active set) is also retained by the context area until it is fetched for operation. Once the complete result set is fetched, the context area is flushed out to release back the memory.

    There are two types of cursors namely, implicit cursor and Explicit cursor.

    Implicit cursor – All SQL queries executed by Oracle server in SQL engine are implicit cursor. They are independently executed (not in PL/SQL block). Complete cursor execution cycle is followed internally by the Oracle server.
    Example:
    Code (SQL):

    SELECT ENAME, DEPTNO, SALARY
    FROM EMPLOYEE
    Above query is an implicit cursor, whose complete execution cycle is followed internally by the Oracle server.

    Explicit cursor is an explicitly declared pointer in PL/SQL with whom an SQL query is associated. Here the execution cycle is handled by the developer with commands.
    Explicit cursors are logically useful where the SELECT query retrieves multi row result. The result set can be iterated to process each row individually.

    3. Cursor Execution cycle

    The screehot below demonstrates the Stages of Cursor Execution Cycle

    [​IMG]

    4. Cursor Attributes

    Cursors attributes are the property of the cursors. Oracle provides four attributes to derive the cursor properties which are set during in course of execution cycle.

    [TABLE]Name Description
    %FOUND Returns TRUE if record was fetched successfully, FALSE otherwise. It is set for the most recent FETCH.
    %NOTFOUND Returns TRUE if record was not fetched successfully, FALSE otherwise. It is set for the most recent FETCH.
    %ROWCOUNT Returns number of records fetched from cursor at that point in time. It is set (increased by 1) as we move forth in the active set.
    %ISOPEN Returns TRUE if cursor is open, FALSE otherwise.[/TABLE]

    Note that except %ROWCOUNT, all other attributes return Boolean outputs.

    Example

    The PL/SQL block declares a cursor which selects an employee details for the employee id 100. It is opened, fetched and result set is operated.

    Code (SQL):
    DECLARE
     CURSOR C1 IS
    SELECT ENAME, SALARY, DEPTNO
    FROM EMPLOYEE
    WHERE EMPNO = 100;

     L_ENAME EMPLOYEE.ENAME%TYPE;
     L_SALARY EMPLOYEE.SALARY%TYPE;
     L_DEPTID EMPLOYEE.DEPTNO%TYPE;
    BEGIN

    OPEN C1;
    FETCH C1 INTO L_ENAME, L_SALARY, L_DEPTID;


    CLOSE C1;

    END;
    The PL/SQL block below declares a cursor with multi row result. Result set of a multi row cursor can be iterated through a loop and each row can be processed separately.

    Code (SQL):
    DECLARE

     CURSOR C1 IS
    SELECT UPPER(ENAME)
    FROM EMPLOYEE;

     L_ENAME EMPLOYEE.ENAME%TYPE;
    BEGIN

     IF C1%ISOPEN THEN
        CLOSE C1;
     END IF;

     OPEN C1;
     LOOP
      FETCH C1 INTO L_ENAME;
      EXIT WHEN C1%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(CHR(10));
      DBMS_OUTPUT.PUT_LINE(‘Employee No ’||TO_CHAR(C1%ROWCOUNT)||’:’||L_ENAME);
     END LOOP;
     CLOSE C1;
    END;

    Employee No 1:JOHN
    Employee No 2:MILLER
    Employee No 3:KATE

    PL/SQL PROCEDURE successfully completed.
    5. Parameterized cursors

    Oracle allows passing parameters to a cursor which allows it to open for multiple times for different values of a column in the query.

    Syntax,
    Code (Text):

    CURSOR [CURSOR NAME]
      [(PARAMETER DATATYPE, ...)]
    IS
      [SELECT QUERY]
    To be noted, parameter declaration should only hold the data type with no precision. Example,

    Code (SQL):
    DECLARE
    CURSOR C1 (P_EMPID NUMBER) IS
     SELECT SALARY
     FROM EMPLOYEE
     WHERE EMPNO=P_EMPID;
    CUR_REC C1%ROWTYPE;
    BEGIN
    OPEN C1(100);
    FETCH C1 INTO CUR_REC;

    CLOSE C1;

    OPEN C1(110);
    FETCH C1 INTO CUR_REC;

    CLOSE C1;

    OPEN C1(120);
    FETCH C1 INTO CUR_REC;

    CLOSE C1;

    END;
     

    Attached Files:

    • cur.JPG
      cur.JPG
      File size:
      23.5 KB
      Views:
      7,170