Skip to content

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;

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;  

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 Find Course  

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

FETCHc1 INTO cnumber;  

if c1%not found 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;

How to use open cursor in a function

This function specifies how to use the open statement.

Example

CREATE OR REPLACE Function Find Course  

( name_in IN varchar2 )  

RETURN number  

IS

cnumber number;  

CURSORc1  

IS  

SELECT course_id  

FROM courses  

WHERE course_name = name_in;  

BEGIN

OPENc1;  

FETCHc1 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:

Consider a cursor defined as

CURSORc1  

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 Find Course  

( name_in IN varchar2 )  

RETURN number  

IS

cnumber number;  

CURSOR c1  

IS  

SELECT course_id  

FROM courses  

WHERE course_name = name_in;  

BEGIN

OPENc1;  

FETCHc1 INTO cnumber;  

if c1%notfound then

cnumber := 9999;  

endif;  

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

CLOSEc1;  

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  

WHEREcourse_name = name_in;  

BEGIN

OPENc1;  

FETCH c1 INTO cnumber;  

if c1%notfound then

cnumber := 9999;  

end if;  

CLOSEc1;  

RETURNcnumber;  

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

CREATEOR 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 DISTINCTowner, tbl.table_name  

FROM all_tables tbl  

WHEREowner = ‘SYSTEM’;  

/* Secondcursor */  

CURSORget_columns IS  

SELECTDISTINCTcolumn_name  

FROMall_tab_columns col  

WHEREowner = v_owner  

AND col.table_name = v_table_name;  

BEGIN

— Open first cursor  

OPENget_tables;  

LOOP  

FETCHget_tables INTO v_owner, v_table_name;  

— Open second cursor  

OPENget_columns;  

LOOP  

FETCHget_columns INTO v_column_name;  

ENDLOOP;  

CLOSEget_columns;  

ENDLOOP;  

CLOSEget_tables;  

EXCEPTION  

WHENOTHERS THEN  

raise_application_error(-20001,’An error was encountered – ‘||SQLCODE||’ -ERROR- ‘||SQLERRM);  

endMULTIPLE_CURSORS_PROC;  

Output

Procedure created.

0.16 seconds

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,

2 Comments »

Leave a Reply

error: Content is protected !!
%d bloggers like this: