?
Explicit Cursors |
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 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 todays date before OPEN cur_cust.
|