Cursors Flashcards
What are the two types of cursors?
- Implicit cursors
- Explicit cursors
What are implicit cursors?
Automatically created by Oracle whenever an SQL statement is executed, when there is no explicit cursor for the statement.
What are explicit cursors?
Programmer-defined cursors for gaining more control over the context area
What is the syntax for creating an explicit cursor?
CURSOR cursor_name IS select_statement;
What are the steps when working with an explicit cursor?
- Declaring the cursor for initializing the memory
- Opening the cursor for allocating the memory
- Fetching the cursor for retrieving the data
- Closing the cursor to release the allocated memory
How do you declare a cursor?
CURSOR name IS SELECT col1, col2 FROM table;
How do you open a cursor?
OPEN name;
How do you fetch a cursor?
FETCH name INTO c_col1, c_col2;
How do you close a cursor?
CLOSE name;
How do you close a cursor?
CLOSE name;
What are the attributes for implicit cursors?
- %FOUND
- %NOTFOUND
- %ISOPEN
- %ROWCOUNT
What is %FOUND?
Returns TRUE if an INSERT, UPDATE, or DELETE statement affected one or more rows or a SELECT INTO statement returned one or more rows. Otherwise, it returns FALSE.
What is %NOTFOUND?
Returns TRUE if an INSERT, UPDATE, or DELETE statement affected no rows, or a SELECT INTO statement returned no rows. Otherwise, it returns FALSE.
What is %ISOPEN?
If a cursor is open, cursor_name%ISOPEN returns TRUE; otherwise, it returns FALSE.
What is %ROWCOUNT?
Returns the number of rows affected by an INSERT, UPDATE, or DELETE statement, or returned by a SELECT INTO statement.
