Oracle defines an execution cycle that executes an SQL statement and associates a cursor with it. Before using an explicit cursor, you must declare it in the declaration section of a block or package as follows:.
Before start fetching rows from the cursor, you must open it. To open a cursor, you use the following syntax:. When you open a cursor, Oracle parses the query, binds variables, and executes the associated SQL statement. Oracle also determines an execution plan, associates host variables and cursor parameters with the placeholders in the SQL statement, determines the result set, and sets the cursor to the first row in the result set.
You must declare a variable before referencing it in a cursor declaration. You cannot assign values to a cursor name or use it in an expression. However, cursors and variables follow the same scoping rules. You retrieve data from a cursor by opening it, then fetching from it.
The scope of cursor parameters is local to the cursor, meaning that they can be referenced only within the query used in the cursor declaration. The values of cursor parameters are used by the associated query when the cursor is opened.
The datatype of a cursor parameter must be specified without constraints, that is, without precision and scale for numbers, and without length for strings. Cursors To execute a multi-row query, Oracle opens an unnamed work area that stores processing information.
Book List. Fetch: When the cursor is opened, rows can be fetched from the cursor one by one or in a block to perform data manipulation.
Close: After data manipulation, close the cursor explicitly. Deallocate: Finally, delete the cursor definition and release all the system resources associated with the cursor. Types of Cursors Cursors are classified depending on the circumstances in which they are opened.
Skip to content. Change Language. Related Articles. Table of Contents.
0コメント