SELECT INTO
The SELECT INTO statement in SPL is not exactly the same as the SELECT command in standard SQL, as shown below.
The purpose of the SELECT INTO statement is to assign the results to variables or records used in the SPL program.
SELECT INTO can only access one row in the result set.
In addition to what is mentioned above, the clauses of the standard SQL SELECT command such as WHERE, ORDER BY, GROUP BY, HAVING can be used in SELECT INTO. Here are two different uses of the SELECT INTO statement.
SELECT select_expressions INTO target FROM ...;
target is a comma-separated list of simple variables. select_expressions and the rest of the statement are the same as the SELECT command in standard SQL. The data type, number, and order of the selected values must be the same as the structure of the target variables, otherwise there will be errors during program execution.
SELECT * INTO record FROM table ... ;
record is a previously declared record type variable
If the query returns no records, then the null value is assigned to the target variable. If the query returns multiple records, the first record is assigned to the target variable and the rest of the records are discarded.
(Note that unless an ORDER BY statement is used, the "first data" is not really the first data)
(Note: SPL will generate an exception whether no rows are returned or more than one row is returned)
(Note: There is a different use of SELECT INTO where multiple rows in the result set can be returned to a collection by using the BULK COLLECT clause)
The WHEN NO_DATA_FOUND clause can be used in the exception handling block to confirm that the assignment was successful (i.e., that the query returned at least one row)
This version of the emp_sal_query procedure uses another use of the SELECT INTO statement to return the result set into a single row.
\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_sal_query (p_empno IN NUMBER)
IS
r_emp emp%ROWTYPE;
v_avgsal emp.sal%TYPE;
BEGIN
SELECT ename, job, hiredate, sal, deptno
INTO r_emp.ename, r_emp.job, r_emp.hiredate, r_emp.sal, r_emp.deptno
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;
END;
/
\set PLSQL_MODE off
Top comments (0)