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.
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