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

Adblocker detected! Please consider reading this notice.

We've detected that you are using AdBlock Plus or some other adblocking software which is preventing the page from fully loading.

We don't have any banner, Flash, animation, obnoxious sound, or popup ad. We do not implement these annoying types of ads!

We need money to operate the site, and almost all of it comes from our online advertising.

Please add wikitechy.com to your ad blocking whitelist or disable your adblocking software.

×