HomeOracle Page 2 - Database Interaction with PL/SQL, Explicit Cursors
Working With Explicit Cursor - Oracle
This article introduces the concept of explicit cursor. We will also examine different approaches to work with explicit cursor. This builds on the concept or cursors, which I looked at in my previous article along with looking at SQL cursor and cursor attributes.
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.
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'.
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.