Explicit cursor.sql

From WikiJava

PL/SQL declares a cursor implicitly for all SQL data manipulation statements, including quries that return only one row. However, for queries that return more than one row you must declare an explicit cursor or use a cursor FOR loop.

Explicit cursor is a cursor in which the cursor name is explicitly assigned to a SELECT statement via the CURSOR...IS statement. For An explicit cursor you must Declare, Open, Fetch and Close it.

the article

This simple explicit cursor is used to get the employee number and name, then concatenate the two and print out the result.

explicit_cursor.sql

--Author: Alex Mutuku Mbolonzi
--Date: 16/10/2007
 
--Declare the cursor and record
 
DECLARE
 
 
 CURSOR emp_cursor IS SELECT empno,ename FROM emp;
 
 
 TYPE emp_rec_type IS RECORD (empno emp.empno%TYPE,ename emp.ename%TYPE);
 
 
 emp_rec emp_rec_type;
 
 
  BEGIN
 
--open the cursor
 
 
   OPEN emp_cursor;
 
--create a loop to iterate through the rows
 
    FOR i IN 1..4 LOOP
 
--put each row retrieved into the record
 
 
     FETCH emp_cursor INTO emp_rec;
 
--print out each row that is retrived
 
     DBMS_OUTPUT.PUT_LINE(emp_rec.empno

Comments from the users

To be notified via mail on the updates of this discussion you can login and click on watch at the top of the page