cursor plsql

Context area:

When processing an SQL statement, Oracle creates a temporary work area in the system memory which contains all the information needed for processing the statement known as context area.

Cursor:

A cursor is a pointer to context area i.e. Context area is controlled by the cursor. It is used to fetch and manipulate the data returned by the SQL statement. Note: The set of rows the cursor holds is known as active set.

Types of cursors:

1. Implicit cursors. 2. Explicit cursors.

Implicit cursors:

Implicit cursors are automatically generated by Oracle while processing an SQL statement when no explicit cursor for the statement is used. They are created by default when DML statements like DELETE, INSERT, UPDATE and SELECT are executed. Oracle provides implicit cursor attributes to check the status of DML operations. When DML statements like INSERT, UPDATE, or DELETE are executed the cursor attributes tell us whether any rows are affected or not and how many have been affected. When a SELECT… INTO statement is executed in a PL/SQL Block, implicit cursor attributes can be used to find out whether any row has been returned by the SELECT statement or not and returns an error when no data is selected.

Implicit cursor attributes:

Attribute	Description
It 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 returns FALSE. Example: SQ
It returns TRUE if an INSERT, UPDATE, or DELETE statement affected no rows, or a SELECT INTO statement returned no rows. Otherwise returns FALSE. Example: SQ
Always returns FALSE for implicit cursors, because Oracle closes the SQL cursor automatically after executing its associated SQL statement. Example: SQ
It returns the number of rows affected by an INSERT, UPDATE or DELETE statement or returned by a SELECT INTO statement. Example: SQ

Example:

DECLARE  var_rows number(2);
BEGIN
  UPDATE employees 
  SET salary = salary + 2000;
  IF SQL%NOTFOUND THEN
    dbms_output.put_line('No record updated.');
  ELSIF SQL%FOUND THEN
    var_rows := SQL%ROWCOUNT;
    dbms_output.put_line(var_rows || ' records are updated.');
  END IF; 
END;
/

Output:

10 records are updated.

Explicit cursors:

Explicit cursors are the user defined cursors to gain more control over the context area. These are defined in the declaration section of the PL/SQL block. An explicit cursor is created on a SELECT Statement which returns more than one row.

Syntax for creating an explicit cursor:

CURSOR cursor_name IS select_statement;

How to use explicit cursor?

1. DECLARE the cursor for initialization in the declaration section. 2. OPEN the cursor for memory allocation in the execution section. 3. FETCH the cursor for retrieving data in the execution section. 4. CLOSE the cursor to release allocated memory in the execution section.

Declaring the cursor:

CURSOR cur_students IS
   SELECT rollNo, name, address FROM students;

Fetching the cursor:

FETCH cur_students INTO s_rollNo, s_name, s_address;

Closing the cursor:

CLOSE cur_students;

Example:

DECLARE
   s_rollNo students.rollNo%type;
   s_name students.name%type;
   s_address students.address%type;
   CURSOR cur_students is
      SELECT rollNo, name, address FROM students;
BEGIN
   OPEN cur_students;
   LOOP
      FETCH cur_students into s_rollNo, s_name, s_address;
      EXIT WHEN cur_students%notfound;
      dbms_output.put_line(s_rollNo || ' ' || s_name || ' ' || s_address);
   END LOOP;
   CLOSE cur_students;
END;
/

Output:

1 Vivek UK 
2 Anil Delhi 
3 Mahesh Rajasthan     
4 Vishal Delhi
5 Binod UP  
6 Sunil UP

Please follow and like us: