Oracle Cursor - oracle tutorial - sql tutorial



What is 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

	CURSOR cursor_name  
	IS  
	SELECT_statement;   
click below button to copy the code. By - oracle tutorial - team
  • 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

	CURSOR c1  
	IS  
	SELECT course_id  
	FROM courses  
	WHERE course_name = name_in;  
click below button to copy the code. By - oracle tutorial - team
  • 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

	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;  
click below button to copy the code. By - oracle tutorial - team

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;  
click below button to copy the code. By - oracle tutorial - team

Example

OPEN c1;  
click below button to copy the code. By - oracle tutorial - team
oracle tutorial , sql tutorial , sql , pl sql tutorial , oracle , pl sql , plsql

How to use open cursor in a function

  • This function specifies how to use the open statement.

Example

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;  
click below button to copy the code. By - oracle tutorial - team

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;   
click below button to copy the code. By - oracle tutorial - team

Parameters

cursor_name:

  • It specifies the name of the cursor that you wish to fetch rows.

variable_list:

  • It specifies the list of variables that you wish to store the cursor result set in.

Example:

  • Consider a cursor defined as
	CURSOR c1  
	IS  
	SELECT course_id  
	FROM courses  
	WHERE course_name = name_in;  
click below button to copy the code. By - oracle tutorial - team

Statement used for fetching data

	FETCH c1 into cnumber;  

click below button to copy the code. By - oracle tutorial - team
  • 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;   
click below button to copy the code. By - oracle tutorial - team

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;  
click below button to copy the code. By - oracle tutorial - team

Statement for closing cursor

CLOSE c1;  

click below button to copy the code. By - oracle tutorial - team

Example

  • 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;  

click below button to copy the code. By - oracle tutorial - team

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;  
click below button to copy the code. By - oracle tutorial - team

Output

Procedure created.
0.16 seconds

This tutorial provides an indepth knowledge on the following items such as oracle tutorial , sql tutorial , sql , pl sql tutorial , oracle , pl sql , mysql tutorial , sql tutorial for beginners , learn sql , oracle database tutorial , sql query tutorial , oracle dba tutorial , plsql tutorial , oracle tutorial pdf , oracle pl sql tutorial , oracle sql tutorial , sql tutorial point , oracle tutorial for beginners , learn oracle online free , learn oracle online , learning pl sql programming , learn sql online for free , sql learning online , dba oracle tutorial , oracle sql tutorial advanced , oracle 11g dba tutorial with examples , oracle online learning , oracle learning online , how to learn pl sql , sql coding tutorial , sql learning websites , sql basic learning

Related Searches to Oracle Cursor