Cursors in Oracle
June 30, 2010 Leave a comment
CURSORS:
A cursor is a mechanism by which you can assign a name to a “select statement” and manipulate the information within that SQL statement. Cursor is a pointer, which works on active set, i.e. which points to only one row at a time in the context area’s ACTIVE SET. A cursor is a construct of pl/sql, used to process multiple rows using a pl/sql block.
Implicit and Explicit cursors:
Oracle automatically declares an implicit cursor every time a SQL statement is executed. The user is unaware of this and cannot control or process the information in an implicit cursor.
We define an explicit cursor for any query that returns more than one row of data. This means that the programmer has declared the cursor within the PL/SQL code block. This declaration allows the application to sequentially process each row of data as the cursor returns it.
Difference between implicit and explicit cursors:
An explicit cursor is declared opened and fetched from in the program block where as an implicit cursor is automatically generated for SQL statements that process a single row only.
How an Implicit cursor works?
- Any given PL/SQL block issues an implicit cursor whenever a SQL statement is executed, as long as an explicit cursor does not exist for that SQL statement.
- A cursor is automatically associated with every DML statement (UPDATE, DELETE, INSERT).
- All UPDATE and DELETE statements have cursors that identify the set of rows that will be affected by the operation.
- An INSERT statement needs a place to receive the data that is to be inserted into the database; the implicit cursor fulfills this need.
- The most recently opened cursor is called the SQL cursor.
How an Explicit cursor works?
The process of working with an explicit cursor consists of the following steps:
- Declaring the cursor: This initializes the cursor into memory.
- Opening the cursor: The declared cursor is opened, and memory is allotted.
- Fetching the cursor: The declared and opened cursor can now retrieve data.
- Closing the cursor: The declared, opened, and fetched cursor must be closed to release the memory allocation.
Declaring a Cursor:
- Cursor without parameters (simplest)
| CURSOR cursor_name IS SELECT_statement; |
- Cursor with parameters
CURSOR cursor_name (parameter_list)
IS
SELECT_statement;
- Cursor with return clause
CURSOR cursor_name
RETURN field%ROWTYPE
IS SELECT_statement;
Opening the Cursor:
When a cursor is opened the active set is determined, the rows satisfying the where clause in the select statement are added to the active set. A pointer is established and points to the first row in the active set.
OPEN cursor_name;
Fetching the Data:
The purpose of using a cursor, in most cases, is to retrieve the rows from your cursor so that some type of operation can be performed on the data. After declaring and opening your cursor, the next step is to FETCH the rows from your cursor.
FETCH cursor_name INTO <list of variables>;
Closing the Cursor:
The final step of working with cursors is to close the cursor once you have finished using it.
CLOSE cursor_name;
Example:
Declare Vno emp.empno%type; Vname emp.ename %type; Cursor emp_cursor is Select empno,ename From emp; Begin Open cursor; For I in 1..10 loop Fetch emp_cursor into vno,vname; dbms_output.put_line(to_char(vno) ||’ ‘||vname); End loop; End;
Implicit & explicit cursor attributes:
Both Implicit and Explicit cursor have 4 attributes:
Implicit Cursor:
1. %ISOPEN: The Oracle engine automatically opens and closed the SQL cursor after executing its associated select, insert, update or delete SQL statement has been processed in case of implicit cursor. Thus the SQL%ISOPEN attribute of an implicit cursor cannot be referenced outside of its SQL statement. As a result, SQL%ISOPEN always evaluates to FALSE.
2. %FOUND: Evaluates to TRUE, if an insert, update or delete affected one or more rows, or a single-row select returned one or more rows. Otherwise it evaluates to FALSE. The syntax for accessing this attribute is SQL%FOUND.
3. %NOTFOUND: It is logical opposite of %FOUND. It evaluates to TRUE, if an insert , update or delete affected no rows, or otherwise it evaluates to FALSE. The syntax for accessing this attribute is SQL%NOTFOUND.
4. %ROWCOUNT: Returns the numbers of rows affected by an insert , update or delete, or select into statement. The syntax for accessing this attribute is SQL%ROWCOUNT.
Explicit Cursor:
1. %ISOPEN: Return TRUE if cursor is open, FALSE otherwise.
2. %FOUND: Return TRUE if record was fetched successfully, FALSE otherwise.
3. %NOTFOUND: Return TRUE if record was not fetched successfully, FALSE otherwise.
4. %ROWCOUNT: Returns number of records processed from the cursor.
Example:
Begin Open emp_cursor; Loop Fetch when emp_cursor % rowcount >10 or Emp_curor % not found; dbms_output.put_line(to_char(vno)||’ ‘|| vname); End loop; Close emp_cursor; End;
Select For Update statement:
The Select For Update statement allows you to lock the records in the cursor result set. You are not required to make changes to the records in order to use this statement. The record locks are released when the next commit or rollback statement is issued.
The syntax for the Select For Update is:
CURSOR cursor_name
IS
select_statement
FOR UPDATE [of column_list] [NOWAIT];
If you plan on updating or deleting records that have been referenced by a Select For Update statement, you can use the Where Current Of statement.
Where Current Of statement:
If you plan on updating or deleting records that have been referenced by a Select For Update statement, you can use the Where Current Of statement.
The syntax for the Where Current Of statement is either:
UPDATE table_name
SET set_clause
WHERE CURRENT OF cursor_name;
OR
DELETE FROM table_name
WHERE CURRENT OF cursor_name;
The Where Current Of statement allows you to update or delete the record that was last fetched by the cursor.
Few PL/SQL Cursor Exceptions:
- Cursor_Already_Open
- Invalid_Cursor
What is REF Cursor?
A REF CURSOR is basically a data type. A variable created based on such a data type is generally called a cursor variable. A cursor variable can be associated with different queries at run-time.
To execute a multi-row query, oracle opens an unnamed work area that stores processing information. To access the information, an explicit cursor, which names the work area or, a cursor variable, which points to the work area can be used.
Whereas a cursor always refers to the same query work area, a cursor variable can refer to a different work areas. Cursor variables are like ‘c’ pointers, which hold the memory location(address) of some object instead of the object itself.
Describe a cursor for loop
You would use a CURSOR FOR Loop when you want to fetch and process every record in a cursor. The CURSOR FOR Loop will terminate when all of the records in the cursor have been fetched.
The syntax for the CURSOR FOR Loop is:
FOR record_index in cursor_name
LOOP
{.statements.}
END LOOP;
Example:
Declare Cursor emp_cursor is Select empno,ename From emp; Begin For emp_record in emp_cursor loop Dbms_output.put_line(emp_record.empno); Dbms_output.put_line(emp_record.ename) End loop; End;
Dibyajyoti Koch:A Blog on Oracle Application 