|Top Previous Next|
Explicit Cursors. You have to declare or name them.
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
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
CURSOR cur_cust IS
SELECT C.last_name || , || C.first_name
FROM customer C,
WHERE O.cust_num = C.cust_num
AND O.order_date = v_today;
OPEN cur_cust; -- open the cursor
-- 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);
CLOSE cur_cust; -- close the cursor
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 todays date before OPEN cur_cust.