SQL Cursors

SQL Cursors

SQL cursors provide a way for your program to select multiple rows of data from the database and then to process each row individually. Cursors are PL/SQL constructs that enable you to process, one row at a time, the result of a multi row query.
ORACLE uses work areas to execute SQL statements, PL/SQL allows user to name private work areas and access the stored information. The PL/SQL construct to identify each and every work area used by SQL is called a Cursor.
There are two type of cursors.
  • 1.Implicit cursors
  • 2.Explicit cursors

1.Implicit SQL Cursors

Implicit cursors are declared by ORACLE for each UPDATE, DELETE and INSERT SQL commands. Explicit cursors are declared and used by the user to process multiple rows, returned by SELECT statement.
The set of rows returned by a query is called the Active Set. Its size depends on the number of rows that meet the search criteria of the SQL query. The data that is stored in the cursor is called the Active Data Set.
ORACLE cursor is a mechanism used easily processes multiple rows of data. Cursors contain a pointer that keeps track of current row being accessed, which enables your program to process the rows at a time.
Example:
When a user executes the following SELECT statement
SELECT emp_no, emp_name,job, salary
FROM employee
WHERE dept = ‘physics’
The resultant dataset will be displayed as follows
1234  A.N Sharanu  Asst. Professor  22,000.00
1345  N.Bharath       Senior Lecturer  17,000.00
1400  M.Mala            Lab Incharge       9,000.00

2.Explicit SQL Cursor

The following are the steps to using explicitly defined cursors within PL/SQL

  • a)Declare the cursor
  • b)Open the cursor
  • c)Fetch data from the cursor
  • d)Close the cursor

a)Declaring a sql cursor

Declaring a cursor enables you to define the cursor and assign a name to it. It has following syntax.
CURSOR   cursor-name   IS SELECT statement
Ex: CURSOR c_name IS
SELECT emp_name FROM emp WHERE dept = ‘physics’

b)Opening a sql cursor

Opening a cursor executes the query and identifies the active set that contains all the rows, which meet the query search criteria.
Syntax:
OPEN cursor_name
Ex:
OPEN c_name
Open statement retrieves the records from the database and places it is in the cursor (private SQL area).

c)Fetching data from sql cursor:

The fetch statement retrieves the rows from the active set one row at a time. The fetch statement is used usually used in conjunction with iterative process(looping statements). In iterative process the cursor advanced to the next row in the active set each time the fetch command is executed. The fetch command is the only means to navigate through the active set.
Syntax:
FETCH cursor-name INTO record-list
Record-list is the list of variables that will receive the columns (fields ) from the active set.
Ex:  LOOP
………………………
……………………
FETCH c_name INTO name;
…………………
END LOOP;

d) Closing a sql cursor:

Closing statement close/deactivates/disables the previously opened cursor and makes the active set undefined. Once it is closed, you cannot perform any operation on it. Once a cursor is closed, the user can reopen the cursor by using Open statement.
Syntax:
CLOSE cursor_name
Ex: Close c_name;

Explicit sql cursor attributes:

Oracle provides certain attributes/cursor variables to control the execution of the cursor. Whenever any cursor (explicit or implicit) is opened and used. ORACLE creates a set of four system variables via which ORACLE keep track of the “current status” of the cursor. Programmers can access these variables they are

  • %NOT FOUND: Evaluates to TRUE if the last fetch is failed i.e. no more rows are left.
             Syntax: cursor_name  %NOT FOUND
  • % FOUND: Evaluates to TRUE , when last fetch succeeded.
             Syntax: cursor_name  % FOUND
  • %ISOPEN: Evaluates to TRUE if cursor is opened, otherwise evaluates to FALSE.
             Syntax: cursor_name  %ISOPEN
  • %ROWCOUNT: Returns the numberof row fetched
             Syntax: cursor_name  %ROWCOUNT

Leave a Reply

Your email address will not be published.