PL/SQL: getting SELECT results into variables or cursors
Showing the result of a query inside a PL/SQL block: SELECT INTO for one row, an explicit cursor or a cursor FOR loop for several rows.
A PL/SQL block does not display the result of a bare SELECT. Inside a block, the result of a query must be stored in variables (SELECT ... INTO) or read row by row with a cursor. The right approach depends on the number of rows expected.
One row: SELECT ... INTO
DECLARE
v_foo foobar.foo%TYPE;
v_bar foobar.bar%TYPE;
BEGIN
SELECT foo, bar FROM foobar INTO v_foo, v_bar;
-- Print the foo and bar values
dbms_output.put_line('foo=' || v_foo || ', bar=' || v_bar);
EXCEPTION
WHEN NO_DATA_FOUND THEN
-- No rows selected, insert your exception handler here
NULL;
WHEN TOO_MANY_ROWS THEN
-- More than one row selected, insert your exception handler here
NULL;
END;
NO_DATA_FOUND is raised when the query returns no row, and TOO_MANY_ROWS when it returns more than one.
Several rows: explicit cursor
DECLARE
CURSOR cur_foobar IS
SELECT foo, bar FROM foobar;
v_foo foobar.foo%TYPE;
v_bar foobar.bar%TYPE;
BEGIN
-- Open the cursor and loop through the records
OPEN cur_foobar;
LOOP
FETCH cur_foobar INTO v_foo, v_bar;
EXIT WHEN cur_foobar%NOTFOUND;
-- Print the foo and bar values
dbms_output.put_line('foo=' || v_foo || ', bar=' || v_bar);
END LOOP;
CLOSE cur_foobar;
END;
Several rows: cursor FOR loop
BEGIN
-- The cursor is opened and closed automatically
FOR v_rec IN (SELECT foo, bar FROM foobar) LOOP
-- Print the foo and bar values
dbms_output.put_line('foo=' || v_rec.foo || ', bar=' || v_rec.bar);
END LOOP;
END;
Notes
dbms_output.put_lineprints nothing until the client enables server output. In SQL*Plus, runSET SERVEROUTPUT ONfirst.- The exception handlers of the first example contained only comments, which PL/SQL does not accept. A
NULL;statement was added to each handler.