Database Interaction with PL/SQL, Explicit Cursors - Working With Explicit Cursor

(Page 2 of 4 )

I spoke too much in the previous section without giving any practical example. So, first of all let us go through an 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;
  Loop
    FETCH c_emp into v_empno,v_ename;
    Exit when c_emp%NOTFOUND;
    Dbms_output.put_line(v_empno || ', ' || v_ename);
  End loop;
  CLOSE c_emp;
End;

Let me explain the above example step by step.

CURSOR c_emp IS select empno, ename from emp;

The above statement defines a cursor with a name 'c_emp' based on the provided SELECT statement. Make sure it is only a declaration and does not process anything yet.

OPEN c_emp;

The above statement makes the SELECT statement available with 'c_emp' to execute. When this statement gets executed, all the rows (with only the columns 'empno' and 'ename') from the table 'emp' gets retrieved and stored in a memory context identified by 'c_emp'.

FETCH c_emp into v_empno,v_ename;

The above statement fetches only one consecutive row from the memory. FETCH statement in general, moves only one row forward at a time. That is why we need to use a loop. After fetching the row information, the values get assigned to the variables specified.

Exit when c_emp%NOTFOUND;

The loop gets terminated based on the above statement. You can also observe that the cursor attribute '%NOTFOUND' is being used with an explicit cursor. If no rows are fetched through the previous FETCH statement of the same cursor 'c_emp', it returns 'true' and the control skips out of the loop.

Dbms_output.put_line(v_empno || ', ' || v_ename);

The above statement displays the current values available in 'v_empno' and 'v_ename'.

CLOSE c_emp;

The above statement frees up resources by removing all the allocations of cursor 'c_emp' from the memory. A cursor once closed cannot be used anymore, unless we open it once again. And that terminates the whole story. Is it too complicated? I don't think so, except that we need to remember the syntactical issues.

Next: Other Approaches of Using Explicit Cursor >>
 

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!