Referring to a cursor in an anonymous block

Question

How can you refer to a specific cursor value if multiple values ​​are returned?

DECLARE X INTEGER; CURSOR c1 IS SELECT col1, col2, col3.....; BEGIN OPEN c1; LOOP EXIT WHEN c1%NOTFOUND; FETCH (col2 from c1) INTO X; END LOOP; END; 

Answer 1

Why do you need this? Presumably, if you select three columns in the cursor declaration, you will need all three columns in the code, so you will need to extract all three columns into three separate local variables, i.e.

 DECLARE x integer; y integer; z integer; CURSOR c1 IS SELECT column1, column2, column3 FROM some_table; BEGIN OPEN c1; LOOP FETCH c1 INTO x, y, z; EXIT WHEN c1%NOTFOUND; END LOOP; CLOSE c1; END; 

Alternatively, you can declare a record type based on a cursor declaration and extract into it

 DECLARE CURSOR c1 IS SELECT column1, column2, column3 FROM some_table; c1_rec c1%ROWTYPE; BEGIN OPEN c1; LOOP FETCH c1 INTO c1_rec; dbms_output.put_line( c1_rec.column2 ); EXIT WHEN c1%NOTFOUND; END LOOP; CLOSE c1; END; 

You can also completely get rid of the explicit loop, which is usually preferable, since you do not need to worry about cursor leaks and (in modern versions) Oracle can automatically make bulk teams for you

 DECLARE CURSOR c1 IS SELECT column1, column2, column3 FROM some_table; BEGIN FOR c1_rec IN c1 LOOP dbms_output.put_line( c1_rec.column2 ); END LOOP; END;