More

    Cursors in Oracle PL/SQL

    You can retrieve the rows into the cursor using a query and then fetch the rows one at a time from the cursor.

    General steps when using a cursor with a simple LOOP:

    1. Declare variables of compatible types to store the column values for a row.
    2. Declare the cursor with a name and the query you want to execute.
    3. Open the cursor from execute block.
    4. Fetch the column values for rows from the cursor one row at a time, into the variables declared in Step 1, and do any processing required.
    5. Close the cursor.

    Steps 3-5 are combined into one step if you are using a FOR loop.

    In step 1, while declaring variables, %TYPE may be used to get the type of a column, so that your variables will automatically be of the correct type:

    DECLARE

    emp_id_var emp.emp_id%TYPE;

    emp_name_var emp.emp_name%TYPE;

    You can declare a cursor as:

    CURSOR emp_cursor IS

    SELECT * FROM emp;

    The above query is executed when you open the cursor from the execute block (BEGIN) as:

    OPEN emp_cursor;

    You can fetch rows one by one within a simple LOOP and may use %NOTFOUND to exit from the loop as:

    LOOP

      FETCH emp_cursor

      INTO emp_id_var, emp_name_var;

      EXIT WHEN emp_cursor%NOTFOUND;

      — any processing

    END LOOP

    You can close a cursor as:

    CLOSE emp_cursor;

    COMPLE CURSOR CODE

    Using a simple loop

    DECLARE

    emp_id_var emp.emp_id%TYPE;

    emp_name_var emp.emp_name%TYPE;

    CURSOR emp_cursor IS

    SELECT * FROM emp;

    BEGIN

    OPEN emp_cursor;

    LOOP

      FETCH emp_cursor

      INTO emp_id_var, emp_name_var;

      EXIT WHEN emp_cursor%NOTFOUND;

      — any processing

    END LOOP;

    CLOSE emp_cursor;

    END;

    Using FOR LOOP

    When using a FOR LOOP, you don’t have to explicitly open or close the cursor, nor declare variables as with simple LOOP:

    DECLARE

    CURSOR emp_cursor IS

    SELECT * FROM emp;

    BEGIN

    FOR empVar IN emp_cursor LOOP

    DBMS_OUTPUT.PUT_LINE(

    ’emp id = ‘ || empVar.emp_id || ‘wmp name = ‘ || empVar.emp_name

    );

    END LOOP;

    END;

    REF CURSOR TYPE AND OPEN-FOR STATEMENT

    You can also declare a REF CURSOR type and then use OPEN-FOR statement to assign the cursor to a different query every time you open:

    DECLARE

      TYPE emp_cursor_type IS

        REF CURSOR RETURN emp%ROWTYPE;

      emp_cursor emp_cursor_type;

      emp_var emp%ROWTYPE;

    BEGIN

      OPEN emp_cursor FOR

      SELECT * FROM emp;

      LOOP

      FETCH emp_cursor INTO emp_var;

    EXIT WHEN emp_cursor%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(

    ’emp id = ‘ || emp_var.emp_id || ’emp name = ‘ || emp_var.emp_name

    );

      END LOOP;

      CLOSE emp_cursor;

    END;

    Below statement declares a REF CUSRSOR and returns a row containing the various columns of the emp table:

    TYPE emp_cursor_type IS

     REF CURSOR RETURN emp%ROWTYPE;

    After this we use this user defined type (emp_cursor_type) to define an actual object(emp_cursor).  Then we declared an object to store columns from the products table (emp_var) which will be later used in the loop to hold a row. Then, within the BEGIN block, we assign a query to the cursor and open it using the OPEN-FOR statement.  

    UNCONSTRAINED CURSORS

    The return type for a constrained cursor must match the columns in the query that is run by the cursor.

    An unconstrained cursor has no return type, and can therefore run any query.

    DECLARE

      TYPE u_cursor_type IS

    REF CURSOR;

      u_cursor u_cursor_type;

      emp_var emp%ROWTYPE;

      student_var student%ROWTYPE;

    BEGIN

      OPEN u_cursor FOR

      SELECT * FROM emp;

      LOOP

    FETCH u_cursor INTO emp_var;

    EXIT WHEN u_cursor%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(

    ’emp id = ‘ || emp_var.emp_id || ’emp name = ‘ || emp_var.emp_name

    );

      END LOOP;

      OPEN u_cursor FOR

      SELECT * FROM student;

      LOOP

    FETCH u_cursor INTO student_var;

    EXIT WHEN u_cursor%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(

    ‘student id = ‘ || student_var.stud_id || ‘student name = ‘ || student_var.stud_name

    );

      END LOOP;

      CLOSE u_cursor;

    END;

    Recent Articles

    OAUTH – FREQUENTLY ASKED QUESTIONS FOR INTERVIEWS AND SELF EVALUATION

    Why is refresh token needed when you have access token? Access tokens are usually short-lived and refresh tokens are...

    SUMO LOGIC VIDEOS AND TUTORIALS

    Sumo Logic Basics - Part 1 of 2 (link is external) (Sep 29, 2016)Sumo Logic Basics - Part 2 of 2...

    GIT – USEFUL COMMANDS

    Discard all local changes, but save them for possible re-use later:  git stash Discarding local changes...

    DISTRIBUTED COMPUTING – RECORDED LECTURES (BITS)

    Module 1 - INTRODUCTION Recorded Lecture - 1.1 Introduction Part I – Definition

    BOOK REVIEW GUIDELINES FOR COOKBOOKS

    Whenever you add reviews for the book, please follow below rules. Write issues in an excel.Create an excel...

    Related Stories

    Leave A Reply

    Please enter your comment!
    Please enter your name here

    Stay on op - Ge the daily news in your inbox