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 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; 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; 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
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
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
Leave a Reply