? Explicit Cursors

Explicit Cursors

Top  Previous  Next

Explicit Cursors. You have to declare or name them.

 

DECLARE

CURSOR cur_cust IS

     SELECT last_name || ‘,’ || first_name

     FROM customer ;

  v_full_name VARCHAR2(30);

BEGIN

OPEN cur_cust; -- open the cursor

LOOP

   -- fetch the explicit cursor

   FETCH cur_cust INTO v_full_name;

   EXIT WHEN cur_cust%NOTFOUND;

   DBMS_OUTPUT.PUT_LINE(‘Name : ’ || v_full_name);

END LOOP;

CLOSE cur_cust;  -- close the cursor

END;

DEFINE QUERY q-cust FOR customer.

DEFINE VARIABLE v_full_name AS CHARACTER.

 

OPEN QUERY q-cast FOR EACH customer.

REPEAT:

GET NEXT q-cast.

IF QUERY-OFF-END('q-cust') THEN LEAVE.

ASSIGN v_full_name = customer.lname + ‘,’ + customer.fname

DISPLAY  ‘Name : ’ + v_full_name.

END.

CLOSE QUERY q-cast.

Note that Oracle set conditions and columns that will be used in DECLARE section while Progress set them in OPEN QUERY section.

 

Cursors have four attributes:

NOTFOUND                is true if the most recent FETCH did not return a row

FOUND                opposite of NOTFOUND

ISOPEN                is true if associated cursor is currently open

ROWCOUNT a total number of rows, for example the following code will display the total number of rows if placed at before CLOSE.

DBMS_OUTPUT.PUT_LINE(  cur_cust%ROWCOUNT  );

 

In case of implicit cursor that does not have a name the key word SQL is used

DBMS_OUTPUT.PUT_LINE(  SQL%ROWCOUNT  );

One more Oracle example

DECLARE

v_today DATE;

CURSOR cur_cust IS

     SELECT C.last_name || ‘,’ || C.first_name

                     O.total_value

     FROM customer C,

               order O

     WHERE O.cust_num = C.cust_num

        AND O.order_date = v_today;

v_full_name VARCHAR2(30);

v_total_value NUMBER(10);

BEGIN

SELECT TRUNC(SYSDATE)

    INTO v_today

    FROM DUAL;

OPEN cur_cust; -- open the cursor

LOOP

   -- fetch the explicit cursor

   FETCH cur_cust INTO v_full_name  v_total_value ;

   EXIT WHEN cur_cust%NOTFOUND;

   DBMS_OUTPUT.PUT_LINE(‘Name : ’ || v_full_name);

   DBMS_OUTPUT.PUT_LINE(‘Name : ’ || v_total_value);

END LOOP;

CLOSE cur_cust;  -- close the cursor

END;

 

Here we have one explicit cursor “cur_cust” and one implicit cursor.

Note that cursor cur_cust includes a variable v_today that is declared before cur_cust declaration. It is a valid syntax, even though the value of v_today is not yet defined. It should be initialized before OPEN cursor statement. That is what is done here in implicit cursor that initializes v_today with today’s date before OPEN cur_cust.