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
- Let's see how to define a cursor called c1. We are using a table name "course" having columns "course_id" and "course_name".
- 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
How to open a cursor
- After the declaration of the cursor, you have to use the open statement to open the cursor.
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.
How to fetch rows from cursor
- This statement is used after declaring and opening your cursor. It is used to fetch rows from cursor.
- It specifies the name of the cursor that you wish to fetch rows.
- It specifies the list of variables that you wish to store the cursor result set in.
- Consider a cursor defined as
Statement used for fetching data
- Let's take an example to fetch course_id into the variable called cnumber.
How to close cursor
- CLOSE statement is a final step and it is used to close the cursor once you have finished using it.
Statement for closing cursor
- The following example specifies how to close the cursor.
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.