Oracle CursorA 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 cursorSyntax 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 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 functionExample Output Function created. 0.09 seconds How to open a cursorAfter the declaration of the cursor, you have to use the open statement to open the cursor. Syntax Example How to use open cursor in a functionThis function specifies how to use the open statement. Example Output Function created. 0.09 seconds How to fetch rows from cursorThis statement is used after declaring and opening your cursor. It is used to fetch rows from cursor. Syntax 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 Statement used for fetching data Let's take an example to fetch course_id into the variable called cnumber. How to close cursorCLOSE statement is a final step and it is used to close the cursor once you have finished using it. Syntax Statement for closing cursor Example The following example specifies how to close the cursor. Cursor within cursorIt 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 Output Procedure created. 0.16 seconds Note: You have to continuously open and close the second cursor each time a new record is retrieved from the first cursor. That way, the second cursor will use the new variable values from the first cursor.Next TopicOracle Trigger |