Use the SELECT INTO statement - PolarDB - Alibaba Cloud Documentation Center

The SELECT INTO statement is a variant of the SELECT statement used in Structured Process Language (SPL).

The SELECT INTO and SELECT statements have the following differences:

The SELECT INTO statement is used to assign the results of a query to a variable or a record, which can be used in SPL blocks.
The SELECT INTO statement returns at most one row.

All SELECT clauses including WHERE, ORDER BY, GROUP BY, and HAVING can be used in the SELECT INTO statement. The following are the two variants of the SELECT INTO statement:

```
SELECT select_expressions INTO target FROM ...;
```
target represents 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 destination in data type, quantity, and order. Otherwise, a runtime error occurs.
```
SELECT * INTO record FROM table ...;
```
record represents a previously declared record variable.

If the query does not return a row, NULL is assigned to the destination. If the query returns multiple rows, the first row of the result is assigned to the destination, and all other rows are ignored.

Note

Note that "the first row" is not explicitly defined unless the ORDER BY clause is used.
If the query does not return a row or returns more than one row, an SPL exception occurs.
You can extend the functionality of SELECT INTO by using a BULK COLLECT clause, which retrieves multiple rows of data and stores them into a collection.

You can use the WHEN NO_DATA_FOUND clause in the EXCEPTION block to determine if the value assignment is successful by checking if at least one row is returned.

Examples

Prepare the test table emp.

CREATE TABLE emp (
    empno           NUMBER(4) NOT NULL CONSTRAINT emp_pk PRIMARY KEY,
    ename           VARCHAR2(10),
    job             VARCHAR2(9),
    mgr             NUMBER(4),
    hiredate        DATE,
    sal             NUMBER(7,2) CONSTRAINT emp_sal_ck CHECK (sal > 0),
    comm            NUMBER(7,2),
    deptno          NUMBER(2)
);

INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,'17-DEC-80',800,NULL,20);
INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,300,30);
INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,'22-FEB-81',1250,500,30);
INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,'02-APR-81',2975,NULL,20);
INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,'28-SEP-81',1250,1400,30);

Create a stored procedure named emp_sal_query: Use the SELECT INTO statement to assign the query result to a record and add an EXCEPTION block that contains a WHEN NO_DATA_FOUND conditional clause.

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;

Execute the stored procedure to perform queries.

Perform a query by using a non-existent employee number.
```
EXEC emp_sal_query(0);
```
Sample result:
```
Employee # 0 not found
```

Another conditional clause TOO_MANY_ROWS is used in the EXCEPTION section of the SELECT INTO statement. A TOO_MANY_ROWS exception occurs if multiple rows are retrieved.

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;

Sample result:

More than one employee found
First employee returned is JONES