October 2005
Intermediate to advanced
454 pages
14h 44m
English
As the examples in the previous section suggest, it is possible to pass cursors as parameters using straight SQL. This can also be done in a SELECT statement using the CURSOR keyword .
SELECT count_valid(CURSOR(SELECT order_number
FROM orders
WHERE processed IS NULL))
FROM dual;The count_valid function might look something like this:
CREATE OR REPLACE FUNCTION count_valid( p_curs SYS_REFCURSOR )
RETURN NUMBER IS
v_on NUMBER;
v_ret_val NUMBER := 0;
BEGIN
FETCH p_curs INTO v_on;
LOOP
EXIT WHEN p_curs%NOTFOUND;
IF extensive_validation(v_on) THEN
v_ret_val := v_ret_val + 1;
END IF;
FETCH p_curs INTO v_on;
END LOOP;
RETURN(v_ret_val);
END;The SELECT statement is passed right into the function that then loops through the records it returns, validating them and then returning a count of those deemed valid. This results in two cursors in the shared pool and the soft-closed list for the user.
SQL_TEXT
----------------------------------------
SELECT "A2"."ORDER_NUMBER" "ORDER_NUMBER
" FROM "ORDERS" "A2" WHERE "A2"."PROCESS
ED" IS NULL
SELECT count_valid(CURSOR(SELECT order_n
umber FROM o
rders WHERE p
rocessed IS NULL)) FROM dual