Database Interaction with PL/SQL, Explicit Cursors - Other Approaches of Using Explicit Cursor

(Page 3 of 4 )

There are several approaches to work with explicit cursors. The approach given above is a very traditional approach being followed from very early versions of Oracle. Now, let us try another approach.

Declare
  CURSOR c_emp IS
    select empno, ename from emp;
  v_empno emp.empno%type;
  v_ename emp.ename%type;
Begin
  OPEN c_emp;
  FETCH c_emp into v_empno,v_ename;
  WHILE c_emp%FOUND
  Loop
    Dbms_output.put_line(v_empno || ', ' || v_ename);
    FETCH c_emp into v_empno,v_ename;
  End loop;
  CLOSE c_emp;
End;

The above program looks almost similar to the previous section, except that I am using a WHILE loop with '%FOUND' cursor attribute. Let us consider another approach using FOR loop:

Declare
  CURSOR c_emp IS
    select empno, ename from emp;
Begin
  FOR r_emp in c_emp
  Loop
    Dbms_output.put_line(r_emp.empno || ', ' || r_emp.ename);
  End loop;
End;

In the case of the above program, the FOR statement itself opens the cursor 'c_emp' and closes after completion. We need not specifically open the cursor in the case of FOR statement. But another issue is that I am using 'r_emp' with FOR loop, which is never declared. PL/SQL allows it! The variables used with the FOR statement (in the form of IMPLICIT cursor) need not be declared. All the values of the row get stored within 'r_emp'. We extract each of those values using dot notation (as demonstrated within the DBMS_OUTPUT statement above).

We can also restrict number of rows being displayed as demonstrated in the following example:

Declare
  CURSOR c_emp IS
    select empno, ename from emp;
  v_empno emp.empno%type;
  v_ename emp.ename%type;
Begin
  OPEN c_emp;
  FETCH c_emp into v_empno,v_ename;
  WHILE c_emp%FOUND and c_emp%rowcount <=5
  Loop
    Dbms_output.put_line(v_empno || ', ' || v_ename);
    FETCH c_emp into v_empno,v_ename;
  End loop;
  CLOSE c_emp;
End;

The %ROWCOUNT cursor attribute is explained in my previous article (part-9). It just counts the number of rows fetched. I hope the rest is the same. If you are not using WHILE loop or FOR loop (like in the first example), we can replace that EXIT WHEN statement using the following statement:

Exit when c_emp%NOTFOUND or c_emp%rowcount > 5;

If you carefully observe, the WHILE loop condition is quite opposite to that of EXIT WHEN statement.

Next: Retrieving More Than One Value With FETCH >>
 

More Oracle Articles
More By Jagadish Chatarji

Comments On: Database Interaction with PL/SQL, Explicit Cursors

· just easy and explained article about new topic i heard about it very often. thnx to...    · Apart from bit late in replying, I am very happy with the compliments. thanks.    · Thanks Jagdish for explaining the concepts in a simple way. I am looking forward for...   >>> Post your comment now!