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