Below, I have 2 scenarios. One with cursor parameter and the other without. But I noticed, the both scenarios result in same output. Are passing parameters to the cursor optional?
Working on Window 10, Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
Below is the test data:
CREATE TABLE tab1 (col1 NUMBER, col2 NUMBER);
INSERT INTO tab1 (col1, col2) VALUES (1, 1);
INSERT INTO tab1 (col1, col2) VALUES (1, 2);
INSERT INTO tab1 (col1, col2) VALUES (2, 3);
CREATE TABLE tab2 (col1 NUMBER, col2 NUMBER);
INSERT INTO tab2 (col1, col2) VALUES (10, 1);
INSERT INTO tab2 (col1, col2) VALUES (20, 2);
INSERT INTO tab2 (col1, col2) VALUES (30, 3);
/
--Scenario 1
DECLARE
v_val NUMBER;
v_out NUMBER;
CURSOR c1 IS SELECT col1 FROM tab1 WHERE col2 IN (2, 3);
CURSOR c2 (p_val NUMBER) IS SELECT col1 FROM tab2 WHERE col2 = p_val;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO v_val;
EXIT WHEN c1%notfound;
OPEN c2 (v_val);
LOOP
FETCH c2 INTO v_out;
EXIT WHEN c2%notfound;
dbms_output.put_line(v_out);
END LOOP;
CLOSE c2;
END LOOP;
CLOSE c1;
END;
/
--Scenario 2
DECLARE
v_val NUMBER;
v_out NUMBER;
CURSOR c1 IS SELECT col1 FROM tab1 WHERE col2 IN (2, 3);
CURSOR c2 IS SELECT col1 FROM tab2 WHERE col2 = v_val;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO v_val;
EXIT WHEN c1%notfound;
OPEN c2;
LOOP
FETCH c2 INTO v_out;
EXIT WHEN c2%notfound;
dbms_output.put_line(v_out);
END LOOP;
CLOSE c2;
END LOOP;
CLOSE c1;
END;
/