What are Cursors?
Cursors help you manipulate the information retrieved by select statements. This can be done by assigning a name to the cursor.
Example:
CURSOR emp_cur
IS
SELECT emp_number from employee_tbl where employee_name = name_in;
Types of cursors in PL/SQL - Implicit cursors, Explicit cursors
Implicit cursors - These cursors are not declared by the programmer. They are issued when the SQL statement is executed. The open, close and fetching is done by itself.
Example:
UPDATE employee SET salary = salary * 2.1;
Here, an implicit cursor is issued to identify the set of rows in the table which would be affected by the update.
Explicit cursors - These cursors are defined by programmer. They are used in queries that return multiple rows.
Example:
CURSOR emp_cur
IS
SELECT emp_number from employee_tbl where employee_name = name_in;
Define cursor attributes: %FOUND, %NOTFOUND, %ROWCOUNT, and %ISOPEN
-
%FOUND - This is a Boolean variable which evaluates to TRUE if the last row is successfully fetched.
-
%NOTFOUND - This is a Boolean variable which evaluates to TRUE if the last row is not successfully fetched. This means there are no more rows to fetch.
-
%ROWCOUNT - Returns the number of rows fetched by the cursor.
-
%ISOPEN - If the cursor is open, it evaluates to TRUE else FALSE.
What are cursor variables? Explain with an example
Cursor variables are references to the query set or the rows. They help you traverse the rows and most importantly, they help you pass results from one PL/SDQL program to another.
Example:
DECLARE
/* Create the cursor type. */
TYPE employee_curtype IS REF CURSOR RETURN employee %ROWTYPE;
/* Declare a cursor variable of that type. */
employee_curvar employee_curtype;
Significance of SELECT FOR UPDATE clause. Write syntax
SELECT FOR UPDATE statement selects the rows in the cursor result set in order to lock them. The lock is released in the next commit or rollback statement.
Syntax:
CURSOR cursor_name
IS
select_statement FOR UPDATE [of column_list] [NOWAIT];
Significance of WHERE CURRENT OF clause. Write syntax
WHERE CURRENT OF clause enables you to update or delete last fetched record by the cursor. These records are referenced by the SELECT FOR UPDATE clause.
Syntax:
UPDATE table_name SET set_clause WHERE CURRENT OF cursor_name;
Why do we use cursor variables?
Cursor variables allow you to pass them as arguments to a function which in turn enables you to share results. They allow you to assign contents of one cursor to another variable. Using a single cursor variable you can fetch from different result sets.
What are the restrictions on cursor variables?
a)Because cursor variables have no persistent state, they cannot be declared in a package.
b)RPC’s cannot be used to pass cursor variables.
c)NULL values cannot be assigned to cursor variables
d)They cannot be used by dynamic SQL.
e)They cannot be used with operators for equality etc.
