CURSOR <left paren> subquery <right paren>

DROP TABLE t1;

CREATE TABLE t1 (a INT);

INSERT INTO t1 VALUES (1);

INSERT INTO t1 VALUES (2);

INSERT INTO t1 VALUES (3);

CREATE OR REPLACE FUNCTION f1(c SYS_REFCURSOR) RETURN INT

AS

  v INT;

  t INT := 0;

BEGIN

  LOOP

    FETCH c INTO v;

    EXIT WHEN c%NOTFOUND;

    t:= t + v;

  END LOOP;

  RETURN t;

END;

/

CREATE OR REPLACE PROCEDURE p1

AS

  t INT;

BEGIN

  FOR i IN 1..3 LOOP

    SELECT f1(cursor(SELECT a FROM t1 WHERE a<=i)) INTO t FROM DUAL;

    DBMS_OUTPUT.PUT_LINE(' sum(t1.a<=' || i || ')=' || t);

  END LOOP;

END;

/

CALL p1;

 sum(t1.a<=1)=1

 sum(t1.a<=2)=3

 sum(t1.a<=3)=6