The SELECT INTO statement is an SPL variation of the SQL SELECT command. The differences are:

  • SELECT INTO assigns the results to variables or records where they can then be used in SPL program statements.
  • The accessible result set of SELECT INTO is at most one row.

Other than these differences, all of the clauses of the SELECT command, such as WHERE, ORDER BY, GROUP BY, and HAVING, are valid for SELECT INTO.


These examples show two variations of SELECT INTO:

SELECT <select_expressions> INTO <target> FROM ...;

target is a comma-separated list of simple variables. select_expressions and the remainder of the statement are the same as for the SELECT command. The selected values must exactly match in data type, number, and order the structure of the target or a runtime error occurs.

SELECT * INTO <record> FROM <table> ...;

record is a record variable that was previously declared.

If the query returns zero rows, null values are assigned to the targets. If the query returns multiple rows, the first row is assigned to the targets and the rest are discarded. ("The first row" isn't well-defined unless you used ORDER BY.)

  • In either case, where no row is returned or more than one row is returned, SPL throws an exception.

  • There is a variation of SELECT INTO using the BULK COLLECT clause that allows a result set of more than one row that's returned into a collection. See SELECT BULK COLLECT for more information.

Including the WHEN NO_DATA_FOUND clause

You can use the WHEN NO_DATA_FOUND clause in an EXCEPTION block to determine whether the assignment was successful, that is, at least one row was returned by the query.

This version of the emp_sal_query procedure uses the variation of SELECT INTO that returns the result set into a record. It also uses the EXCEPTION block containing the WHEN NO_DATA_FOUND conditional expression.

    p_empno         IN emp.empno%TYPE
    r_emp           emp%ROWTYPE;
    v_avgsal        emp.sal%TYPE;
    SELECT * INTO r_emp
        FROM emp WHERE empno = p_empno;
    DBMS_OUTPUT.PUT_LINE('Employee # : ' || p_empno);
    DBMS_OUTPUT.PUT_LINE('Name       : ' || r_emp.ename);
    DBMS_OUTPUT.PUT_LINE('Job        : ' || r_emp.job);
    DBMS_OUTPUT.PUT_LINE('Hire Date  : ' || r_emp.hiredate);
    DBMS_OUTPUT.PUT_LINE('Salary     : ' || r_emp.sal);
    DBMS_OUTPUT.PUT_LINE('Dept #     : ' || r_emp.deptno);

    SELECT AVG(sal) INTO v_avgsal
        FROM emp WHERE deptno = r_emp.deptno;
    IF r_emp.sal > v_avgsal THEN
        DBMS_OUTPUT.PUT_LINE('Employee''s salary is more than the '
            || 'department average of ' || v_avgsal);
        DBMS_OUTPUT.PUT_LINE('Employee''s salary does not exceed the '
            || 'department average of ' || v_avgsal);
    END IF;
        DBMS_OUTPUT.PUT_LINE('Employee # ' || p_empno || ' not found');

If the query is executed with a nonexistent employee number, the results appear as follows:

EXEC emp_sal_query(0);

Employee # 0 not found

Including a TOO_MANY_ROWS exception

Another conditional clause useful in the EXCEPTION section with SELECT INTO is the TOO_MANY_ROWS exception. If more than one row is selected by the SELECT INTO statement, SPL throws an exception.

When the following block is executed, the TOO_MANY_ROWS exception is thrown since there are many employees in the specified department:

    v_ename         emp.ename%TYPE;
    SELECT ename INTO v_ename FROM emp WHERE deptno = 20 ORDER BY ename;
        DBMS_OUTPUT.PUT_LINE('More than one employee found');
        DBMS_OUTPUT.PUT_LINE('First employee returned is ' || v_ename);

More than one employee found
First employee returned is ADAMS

See Exception handling for information on exception handling.