santosh-asp tutorial

Cursor in Oracle:-

A cursor is a pointer to a private SQL area that stores information about the processing of a SELECT or DML statements like INSERT, UPDATE, DELETE or MERGE. Cursor is a mechanism which facilitates you to assign a name to a SELECT statement and manipulate the information within that SQL statement.

How to declare cursor

Syntax:-

  
 CURSOR cursor_name  IS  SELECT_statement;

Let's see how to define a cursor called c1. We are using a table name "course" having columns "course_id" and "course_name".

Example (1):-

  
 CURSOR c1  
    IS  
      SELECT course_id  
      FROM courses  
      WHERE course_name = name_in;

In the above example, the result set of this cursor is all course_id whose course_name matches the variable called name_in.
How to use cursor in a function:-

Example (2):-

  
    CREATE OR REPLACE Function FindCourse  
       ( name_in IN varchar2 )  
       RETURN number  
    IS  
     cnumber number;  
    CURSOR c1  
       IS  
         SELECT course_id  
         FROM courses  
         WHERE course_name = name_in;  
    BEGIN  
     OPEN c1;  
       FETCH c1 INTO cnumber;  
     if c1%notfound then  
          cnumber := 9999;  
       end if;  
     CLOSE c1;  
    RETURN cnumber;  
    END;

Output

  
Function created.
0.09 seconds

How to open a cursor:- After the declaration of the cursor, you have to use the open statement to open the cursor.
Syntax:-

  
   OPEN cursor_name;  

Example

    OPEN c1;

Example (3):-

 
       OPEN c1;

How to use open cursor in a function:- This function specifies how to use the open statement.
Example (4):-

  

    CREATE OR REPLACE Function FindCourse  
      ( name_in IN varchar2 )  
      RETURN number  
    IS  
       cnumber number;  
    CURSOR c1  
       IS  
         SELECT course_id  
         FROM courses  
       WHERE course_name = name_in;  
    BEGIN  
    OPEN c1;  
       FETCH c1 INTO cnumber;  
    if c1%notfound then  
          cnumber := 9999;  
     end if;  
    CLOSE c1;  
    RETURN cnumber;  
    END;

Output

 
Function created.
0.09 seconds

How to fetch rows from cursor:- This statement is used after declaring and opening your cursor. It is used to fetch rows from cursor.

Syntax:-

  

FETCH cursor_name INTO variable_list;

Parameters:-
1) cursor_name: It specifies the name of the cursor that you wish to fetch rows.
2) variable_list: It specifies the list of variables that you wish to store the cursor result set in.

Example (5): Consider a cursor defined as

  

       CURSOR c1  
       IS  
       SELECT course_id  
       FROM courses  
       WHERE course_name = name_in;

Statement used for fetching data

  

     FETCH c1 into cnumber;

Let's take an example to fetch course_id into the variable called cnumber.

  

    CREATE OR REPLACE Function FindCourse  
       ( name_in IN varchar2 )  
       RETURN number  
    IS  
       cnumber number;  
     CURSOR c1  
       IS  
         SELECT course_id  
         FROM courses  
         WHERE course_name = name_in;  
    BEGIN  
     OPEN c1;  
       FETCH c1 INTO cnumber;  
    if c1%notfound then  
      cnumber := 9999;  
       end if;  
     CLOSE c1;  
    RETURN cnumber;  
    END;

How to close cursor:- CLOSE statement is a final step and it is used to close the cursor once you have finished using it.
Syntax:-

  

 CLOSE cursor_name;

Statement for closing cursor:-

  

     CLOSE c1;

Example (6):- The following example specifies how to close the cursor.

  

    CREATE OR REPLACE Function FindCourse  
       ( name_in IN varchar2 )  
       RETURN number  
    IS  
    cnumber number;  
    CURSOR c1  
       IS  
         SELECT course_id  
         FROM courses  
         WHERE course_name = name_in;  
    BEGIN  
    OPEN c1;  
       FETCH c1 INTO cnumber;  
    if c1%notfound then  
          cnumber := 9999;  
       end if;  
    CLOSE c1;  
    RETURN cnumber;  
    END;

Cursor within cursor:- It is also possible to declare a cursor within a cursor. the following example specifies how to declare a cursor within a cursor. In this example, there is a cursor named get_tables that retrieves the owner and table_name values. These values are then used in a second cursor called get_columns.

Example:-

  

    CREATE OR REPLACE PROCEDURE MULTIPLE_CURSORS_PROC is  
       v_owner varchar2(40);  
       v_table_name varchar2(40);  
       v_column_name varchar2(100);  
         
       /* First cursor */  
       CURSOR get_tables IS  
         SELECT DISTINCT tbl.owner, tbl.table_name  
         FROM all_tables tbl  
         WHERE tbl.owner = 'SYSTEM';  
           
       /* Second cursor */  
       CURSOR get_columns IS  
         SELECT DISTINCT col.column_name  
         FROM all_tab_columns col  
         WHERE col.owner = v_owner  
         AND col.table_name = v_table_name;  
           
       BEGIN  
         
       -- Open first cursor  
       OPEN get_tables;  
       LOOP  
          FETCH get_tables INTO v_owner, v_table_name;  
            
          -- Open second cursor  
          OPEN get_columns;  
          LOOP  
             FETCH get_columns INTO v_column_name;  
          END LOOP;  
         CLOSE get_columns;  
        END LOOP;  
       CLOSE get_tables;  
      EXCEPTION  
       WHEN OTHERS THEN  
     raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);  
    end MULTIPLE_CURSORS_PROC;

Output:-

  

Procedure created.
0.16 seconds