LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon