1.

Why do we use Cursors in PL/SQL?

Answer»

Oracle forms a memory area, when an SQL statement is processed. This memory area is called CONTEXT area. A cursor in PL/SQL is a pointer to this context area. It has information about processing the statement. A PL/SQL cursor holds the rows (one or more) returned by a SQL statement.

The following are the two forms of cursors:

  • Implicit Cursors (Automatic)

Implicit cursors in PL/SQL are created automatically by Oracle when an SQL statement is executed. This OCCURS when there is no explicit cursor for the statement.

An implicit cursor is associated, when INSERT, UPDATE and DELETE is issued. The attributes include %FOUND, %ISOPEN, %NOTFOUND, etc. With the attributes, you can check which all rows got affected by the operation.

The attributes:

  • %FOUND: 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, it returns FALSE.
  • %NOTFOUND: The logical opposite of %FOUND. It returns TRUE if an INSERT, UPDATE, or DELETE statement affected no rows, or a SELECT INTO statement returned no rows. Otherwise, it returns FALSE.
  • %ISOPEN: It returns FALSE for implicit cursors, because Oracle closes the SQL cursor automatically after executing its associated SQL statement.
  • %ROWCOUNT: It returns the number of rows affected by an INSERT, UPDATE, or DELETE statement, or returned by a SELECT INTO statement.

To USE any of the above attribute, use the following syntax:

sql%attribute_name

Above, set any attribute name under “attribute_name”.Let us consider the following table for our example:

<DEPTBUDGET> +-----------+-----------------+---------------+------------------+ | DEPTID | DEPTNAME  | DEPTLOC | BUDGET| +----------+------------------+---------------+------------------+ |  1 | Finance          | NORTH | 56678      | |  2 | Marketing     | EAST |      87687 | |  3 | Operations   | WEST |      95979 | |  4 | Technical      | SOUTH |    76878 | |  5 | Accounting   | NORTHWEST| 86767      | +----------+------------------+------------------+--------------+

Now, we will update the budget of all the DEPARTMENTS and then check what all rows got affected using the implicit cursor attribute. With that we will use other attributes as well:

DECLARE     rows_count number(2); BEGIN   UPDATE deptbudget   SET budget = budget + 3000;   IF sql%notfound THEN      dbms_output.put_line('Nothing selected!');   ELSIF sql%found THEN      rows_count := sql%rowcount;      dbms_output.put_line( rows_count || ' rows affected! ');   END IF;   END; /    

The output:

5 rows affected!

Since we updated the budget for all the departments above, our table rows would look like this:

<DEPTBUDGET> +-----------+-----------------+---------------+------------------+ | DEPTID | DEPTNAME  | DEPTLOC | BUDGET| +----------+------------------+---------------+------------------+ |  1 | Finance          | NORTH | 59678      | |  2 | Marketing     | EAST |      90687 | |  3 | Operations   | WEST |      98979 | |  4 | Technical      | SOUTH |    79878 | |  5 | Accounting   | NORTHWEST| 89767      | +----------+------------------+------------------+--------------+
  • Explicit Cursors (Programmer Defined)

Explicit cursors are custom defined i.e. programmer defined cursors that gives more control over the context area. Oracle forms a memory area, when an SQL statement is processed, which is called context area.

The following is the syntax to create explicit cursor:

CURSOR name_of_cursor IS select_statement;

Here,
“name_of_cursor” is the cursor name

As you can see above, an explicit cursor is created on a SELECT statement. Let us now see how to work with Explicit Cursors,

  • Cursor Declaration

Let us declare a cursor and set a cursor name with the SELECT statement:

CURSOR d_deptbudget IS   SELECT deptid, deptname, deptloc, budget FROM deptbudget;

Above, “d_deptbudget” is our cursor.

  • Open the Cursor

The memory gets allocated for the cursor when it is opened in PL/SQL.Opening the cursor allocates the memory for the cursor and makes it ready for fetching the rows returned by the SQL statement into it

OPEN d_deptbudget;
  • Fetch the Cursor

Access one row at a time when a cursor is fetched in PL/SQL.The following is when you fetch a cursor which is already opened:

FETCH d_deptbudget INTO d_deptid, d_deptname, d_deptloc, d_budget;
  • Close the Cursor

When a cursor is closed, then the allocate memory is released. To close the above created cursor:

CLOSE d_deptbudget;

Let us now see an example to work with explicit cursors in PL/SQL:

DECLARE     d_deptid deptbudget.deptid%type;     d_deptname deptbudget.deptname%type;       CURSOR d_deptbudget IS        SELECT deptid, deptname FROM deptbudget; BEGIN     OPEN d_deptbudget;     LOOP        FETCH d_deptbudget into d_deptid, d_deptname;        EXIT WHEN d_deptbudget%notfound;        dbms_output.put_line(d_deptid || ' ' || d_deptname);     END LOOP;     CLOSE d_deptbudget;   END;   /  

The output display department id and name:

1 Finance 2 Marketing 3 Operations 4 Technical 5 Accounting


Discussion

No Comment Found

Related InterviewSolutions