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

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

Other than the above, all of the clauses of the SELECT statement, such as WHERE, ORDER BY, GROUP BY, and HAVING, are valid for SELECT INTO. The following example shows the 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 those of the SELECT statement. The selected values must exactly match the structure of the target in data type, number, and order. Otherwise, a runtime error occurs.

SELECT * INTO record FROM table ... ;

record is a record variable that has previously been declared.

If the query returns zero rows, null values are assigned to the target. If the query returns multiple rows, the first row is assigned to the target and the rest are discarded. Note that "the first row" is not well-defined unless you have used ORDER BY.

Note If no row is returned or more than one row is returned, SPL throws an exception.

A variation of SELECT INTO uses the BULK COLLECT clause. The variation allows a result set of more than one row that is returned into a collection.

You can use the WHEN NO_DATA_FOUND clause in an EXCEPTION block to determine whether the assignment was successful. When the assignment was successful, 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. Note the addition of the EXCEPTION block containing the WHEN NO_DATA_FOUND conditional expression.

CREATE OR REPLACE PROCEDURE emp_sal_query (
    p_empno         IN emp.empno%TYPE
)
IS
    r_emp           emp%ROWTYPE;
    v_avgsal        emp.sal%TYPE;
BEGIN
    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);
    ELSE
        DBMS_OUTPUT.PUT_LINE('Employee''s salary does not exceed the '
            || 'department average of ' || v_avgsal);
    END IF;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Employee # ' || p_empno || ' not found');
END;

If the query is executed with a non-existent employee number, the following results appear:

EXEC emp_sal_query(0);

Employee # 0 not found

Another conditional clause used 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, an exception is thrown by SPL.

When the following block is executed, the TOO_MANY_ROWS exception is thrown because many employees exist in the specified department.

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

More than one employee found
First employee returned is ADAMS