sql_mode=ORACLE: Cursors with parameters

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

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;

SQL> Fetched a record a=1

SQL> 

	 A

----------

	 1

	 1