Oracle PL/SQL - SELECT INTO Statement for Assigning Row to Record Variable

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Data Type
  4. RECORD Types

Introduction

The syntax of a simple SELECT INTO statement is: 

SELECT select_list INTO record_variable_name FROM table_or_view_name;

For each column in select_list, the record variable must have a corresponding, type-compatible field.

The columns in select_list must appear in the same order as the record fields.

In the following code, the record variable rec1 represents a partial row of the emp table-the columns last_name and empid.

The SELECT INTO statement selects from emp the row for which job_id is 'CODER' and assigns the values of the columns last_name and empid in that row to the corresponding fields of rec1.

Demo

SQL>
SQL> drop table emp;

Table dropped.--  w  w  w  . j  a  v  a 2 s  .  c om

SQL> CREATE TABLE emp(
  2  empid NUMBER(6),
  3  first_name VARCHAR2(20),
  4  last_name VARCHAR2(25),
  5  email VARCHAR2(25),
  6  phone_number VARCHAR2(20),
  7  hire_date DATE,
  8  job_id VARCHAR2(10),
  9  salary NUMBER(8,2),
 10  commission_pct NUMBER(2,2),
 11  manager_id NUMBER(6),
 12  department_id NUMBER(4)) ;
SQL>
SQL> INSERT INTO emp VALUES( 100, 'Steven', 'King', 'SKING', '123.123.4567', TO_DATE('17-JUN-1987', 'dd-MON-yyyy'), 'CODER', 24000, NULL, NULL, 90);
SQL>
SQL>
SQL> DECLARE
  2    TYPE RecordTyp IS RECORD (
  3      last emp.last_name%TYPE,
  4      id   emp.empid%TYPE
  5    );
  6    rec1 RecordTyp;
  7  BEGIN
  8    SELECT last_name, empid INTO rec1
  9    FROM emp
 10    WHERE job_id = 'CODER';
 11
 12    DBMS_OUTPUT.PUT_LINE ('Employee #' || rec1.id || ' = ' || rec1.last);
 13  END;
 14  /
Employee #100 = King

PL/SQL procedure successfully completed.

SQL>

Related Topic