Details

    • Sprint:
      10.2.2-3, 10.2.2-1, 10.2.2-2, 10.2.2-4, 10.1.18

      Description

      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
      

      SQL> 
      	 A
      ----------
      	 1
      	 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.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                bar Alexander Barkov
                Reporter:
                bar Alexander Barkov
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: