Orcale SQL Tutorial


Cursor

Oracle Cursor

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

  1. CURSOR cursor_name  
  2. IS  
  3.   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  
  2. IS  
  3.   SELECT course_id  
  4.   FROM courses  
  5.   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

  1. CREATE OR REPLACE Function FindCourse  
  2.    ( name_in IN varchar2 )  
  3.    RETURN number  
  4. IS  
  5.  cnumber number;  
  6. CURSOR c1  
  7.    IS  
  8.      SELECT course_id  
  9.      FROM courses  
  10.      WHERE course_name = name_in;  
  11. BEGIN  
  12.  OPEN c1;  
  13.    FETCH c1 INTO cnumber;  
  14.  if c1%notfound then  
  15.       cnumber := 9999;  
  16.    end if;  
  17.  CLOSE c1;  
  18. RETURN cnumber;  
  19. 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

  1. OPEN cursor_name;  

 

Example

  1. OPEN c1;  

 

How to use open cursor in a function

This function specifies how to use the open statement.

Example

  1. CREATE OR REPLACE Function FindCourse  
  2.   ( name_in IN varchar2 )  
  3.   RETURN number  
  4. IS  
  5.    cnumber number;  
  6. CURSOR c1  
  7.    IS  
  8.      SELECT course_id  
  9.      FROM courses  
  10.    WHERE course_name = name_in;  
  11. BEGIN  
  12. OPEN c1;  
  13.    FETCH c1 INTO cnumber;  
  14. if c1%notfound then  
  15.       cnumber := 9999;  
  16.  end if;  
  17. CLOSE c1;  
  18. RETURN cnumber;  
  19. 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

  1. 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:

Consider a cursor defined as

  1. CURSOR c1  
  2. IS  
  3.    SELECT course_id  
  4.    FROM courses  
  5.    WHERE course_name = name_in;  

 

Statement used for fetching data

  1. FETCH c1 into cnumber;  

 

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

  1. CREATE OR REPLACE Function FindCourse  
  2.    ( name_in IN varchar2 )  
  3.    RETURN number  
  4. IS  
  5.    cnumber number;  
  6.  CURSOR c1  
  7.    IS  
  8.      SELECT course_id  
  9.      FROM courses  
  10.      WHERE course_name = name_in;  
  11. BEGIN  
  12.  OPEN c1;  
  13.    FETCH c1 INTO cnumber;  
  14. if c1%notfound then  
  15.   cnumber := 9999;  
  16.    end if;  
  17.  CLOSE c1;  
  18. RETURN cnumber;  
  19. 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

  1. CLOSE cursor_name;  

 

Statement for closing cursor

  1. CLOSE c1;  

 

Example

The following example specifies how to close the cursor.

  1. CREATE OR REPLACE Function FindCourse  
  2.    ( name_in IN varchar2 )  
  3.    RETURN number  
  4. IS  
  5. cnumber number;  
  6. CURSOR c1  
  7.    IS  
  8.      SELECT course_id  
  9.      FROM courses  
  10.      WHERE course_name = name_in;  
  11. BEGIN  
  12. OPEN c1;  
  13.    FETCH c1 INTO cnumber;  
  14. if c1%notfound then  
  15.       cnumber := 9999;  
  16.    end if;  
  17. CLOSE c1;  
  18. RETURN cnumber;  
  19. 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

  1. CREATE OR REPLACE PROCEDURE MULTIPLE_CURSORS_PROC is  
  2.    v_owner varchar2(40);  
  3.    v_table_name varchar2(40);  
  4.    v_column_name varchar2(100);  
  5.      
  6.    /* First cursor */  
  7.    CURSOR get_tables IS  
  8.      SELECT DISTINCT tbl.owner, tbl.table_name  
  9.      FROM all_tables tbl  
  10.      WHERE tbl.owner = 'SYSTEM';  
  11.        
  12.    /* Second cursor */  
  13.    CURSOR get_columns IS  
  14.      SELECT DISTINCT col.column_name  
  15.      FROM all_tab_columns col  
  16.      WHERE col.owner = v_owner  
  17.      AND col.table_name = v_table_name;  
  18.        
  19.    BEGIN  
  20.      
  21.    -- Open first cursor  
  22.    OPEN get_tables;  
  23.    LOOP  
  24.       FETCH get_tables INTO v_owner, v_table_name;  
  25.         
  26.       -- Open second cursor  
  27.       OPEN get_columns;  
  28.       LOOP  
  29.          FETCH get_columns INTO v_column_name;  
  30.       END LOOP;  
  31.      CLOSE get_columns;  
  32.     END LOOP;  
  33.    CLOSE get_tables;  
  34.   EXCEPTION  
  35.    WHEN OTHERS THEN  
  36.  raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);  
  37. end MULTIPLE_CURSORS_PROC;  

 

Output

Procedure created.
0.16 seconds

Note: You have to continuously open and close the second cursor each time a new record is retrieved from the first cursor. That way, the second cursor will use the new variable values from the first cursor.

Quick Links
Get Started

Get access to your full Free Training

Login
Contact Us

info@dba-arabic.com

Copyright DBA-ARABIC © 2023. All rights reserved.