1.

How to use an explicit cursor in PL/SQL?

Answer»

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