|
Add optional parameter list into CURSOR declaration and OPEN.
Example:
DROP TABLE t1;
|
CREATE TABLE t1 (a INT, b INT, c INT);
|
INSERT INTO t1 VALUES (1,2,3);
|
DECLARE
|
CURSOR c (prm_a VARCHAR2, prm_b VARCHAR2) IS
|
SELECT a, b, c FROM t1 WHERE a=prm_a AND b=prm_b;
|
v_a INT;
|
v_b INT;
|
v_c INT;
|
BEGIN
|
OPEN c(1, 2);
|
FETCH c INTO v_a, v_b, v_c;
|
CLOSE c;
|
INSERT INTO t1 VALUES (v_a + 10, v_b + 10, v_c + 10);
|
EXCEPTION
|
WHEN NO_DATA_FOUND THEN RETURN;
|
END;
|
/
|
SELECT * FROM t1;
|
DROP TABLE t1;
|
A B C
|
---------- ---------- ----------
|
1 2 3
|
11 12 13
|
Cursor parameter name space
Cursor parameters exists in a separate name space and shadow the variables declared on the same level with the cursor, or on upper levels. This script:
SET SERVEROUTPUT ON ;
|
DROP TABLE t1;
|
DROP PROCEDURE p1;
|
CREATE TABLE t1 (a INT);
|
INSERT INTO t1 VALUES (1);
|
CREATE PROCEDURE p1(a INT)
|
AS
|
v_a INT:=NULL;
|
p_a INT:=NULL;
|
CURSOR c (p_a VARCHAR2) IS SELECT a FROM t1 WHERE p_a IS NOT NULL;
|
BEGIN
|
OPEN c(a);
|
FETCH c INTO v_a;
|
IF c%NOTFOUND THEN
|
BEGIN
|
DBMS_OUTPUT.PUT_LINE('No records found');
|
RETURN;
|
END;
|
END IF;
|
CLOSE c;
|
DBMS_OUTPUT.PUT_LINE('Fetched a record a='||TO_CHAR(v_a));
|
INSERT INTO t1 VALUES (v_a);
|
COMMIT;
|
END;
|
/
|
CALL p1(1);
|
SELECT * FROM t1;
|
returns
SQL> Fetched a record a=1
|
Notice, the procedure call inserted the second record into the table t1, because the cursor parameter p_a shadowed the local variable p_a and the condition WHERE p_a IS NOT NULL evaluated to TRUE, because the cursor parameter p_a was initialized to 1 at OPEN c(a) time. The value of the local variable p_a is not important, as it's not visible in the cursor SELECT expression.
|
[MDEV-10597]