A Cursor is basically a pointer to the Context Area. PL/SQL block controls the context area using a cursor. A cursor holds the one or more rows returned by a PL/SQL statement. Cursors and can be named as well as be kept anonymous. Named cursors can help for its reference so that it can be fetched in a program later on and process the rows returned by the SQL statement one by one.
In Oracle SQL, there are two types of cursors:
1. Implicit cursors
2. Explicit cursors
Implicit Cursors
The Implicit Cursor is the Default Cursor in PL/SQL blocks. These are created when there is no explicit cursor for the statement or when PL/SQL encounters a SELECT statement that returns just one row and also when Data Manipulation Language (DML) statements like DELETE, INSERT and UPDATE are encountered.
Some of the PL/SQL attributes that are used frequently are mentioned below:
- %ROWCOUNT: It Returns the number of rows influenced by an UPDATE, DELETE or an INSERT statement.
- %NOTFOUND: It Returns TRUE if an UPDATE, DELETE or an INSERT statement influenced zero rows or a SELECT INTO statement returned no rows. Else, it Returns a FALSE.
- %FOUND: It Returns TRUE if an UPDATE, DELETE or an INSERT statement influenced one or more rows or a SELECT INTO statement returned one or more rows. Else, it returns FALSE.
- %ISOPEN: It Returns FALSE for Implicit cursors as Oracle closes the PL/SQL cursor by default after processing its associated PL/SQL statement.
Explicit Cursors
Explicit cursors are used if you need to have better control over the Context Area via Cursor. They should be used when you are executing a SELECT statement query that will return more than one row. Cursors can process one record at a given point of time even though it stores more than one record. An explicit cursor is defined in the declaration section of the PL/SQL Block. Explicit Cursor and Implicit Cursor both have the same output, but accessing is different.
Syntax For An Explicit Cursor
|
1 2 3 |
Cursor Cursor-Name IS Select-Statement; |
Processing an Explicit Cursor involves the following steps:
1. Initialize the Cursor: Initializing the Cursor also means the Declaration of the cursor in the Declaration Section of a PL/SQL Block. The name of the cursor requires to be defined along with the SELECT Statement.
Example
|
1 |
CURSOR admin_no IS SELECT ano, aname from Admin; |
2. Open the Cursor: Opening the Cursor also means allocating the memory for the Cursor in the Context Area which thereby makes it sufficient to fetch and store records in it.
Example
|
1 |
OPEN admin_no; |
3. Fetch the Cursor: Fetching the Cursor involves retrieval of data using the Fetch statement. It is used to help the Cursor process and access records or rows at a time.
Example
|
1 2 3 |
FETCH a_admin INTO a_ano, a_aname; |
4. Close the Cursor: Once the work associated for a Cursor to be completed is accomplished, it is necessary to release the Allocated Memory of the Cursor to let other tasks occupy memory with any memory down-time.
Example
|
1 |
CLOSE Admin_no; |
Program to write a Cursor to list all the Administrators in the Database Table named as Admin
|
1 2 3 4 5 6 7 8 9 10 11 |
declare CURSOR c1 IS SELECT aname from Admin; rec c1%rowtype; begin for rec in c1 loop dbms_output.put_line(rec.aname); end loop; end; / |
I want more examples(programs)for explicit cursors,can u plz send it to me?