Oracle INTO clause Example

Examples

The following example demonstrates using the SELECT INTO statement to query a single value into a PL/SQL variable, entire columns into PL/SQL collections, or entire rows into a PL/SQL collection of records:

DECLARE
   howmany NUMBER;
   some_first employees.first_name%TYPE;
   some_last employees.last_name%TYPE;
   some_employee employees%ROWTYPE;
   TYPE first_typ IS TABLE OF employees.first_name%TYPE INDEX BY PLS_INTEGER;
   TYPE last_typ IS TABLE OF employees.first_name%TYPE INDEX BY PLS_INTEGER;
   first_names first_typ;
   last_names last_typ;
   CURSOR c1 IS SELECT first_name, last_name FROM employees;
   TYPE name_typ IS TABLE OF c1%ROWTYPE INDEX BY PLS_INTEGER;
   all_names name_typ;
   TYPE emp_typ IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER;
   all_employees emp_typ;
BEGIN
-- Query a single value and store it in a variable.
   SELECT COUNT(*) INTO howmany FROM user_tables;
   dbms_output.put_line('This schema owns ' || howmany || ' tables.');

-- Query multiple columns from one row, and store them in variables.
   SELECT first_name, last_name INTO some_first, some_last
      FROM employees WHERE ROWNUM < 2;
   dbms_output.put_line('Random employee: ' || some_first ||
      ' ' || some_last);

-- Query a single row and store it in a record.
   SELECT * INTO some_employee FROM employees WHERE ROWNUM < 2;

-- Query multiple columns from multiple rows, and store them in a collection
-- of records.
   SELECT first_name, last_name BULK COLLECT INTO all_names FROM EMPLOYEES;

-- Query multiple columns from multiple rows, and store them in separate
-- collections. (Generally less useful than a single collection of records.)
   SELECT first_name, last_name
      BULK COLLECT INTO first_names, last_names
      FROM EMPLOYEES;

-- Query an entire (small!) table and store the rows
-- in a collection of records. Now you can manipulate the data
-- in-memory without any more I/O.
   SELECT * BULK COLLECT INTO all_employees FROM employees;
END;
/