Like many in the Oracle community, I occasionally find myself looking through forums and helping out where I can. I consistently see some basic questions cropping up in forums like Stackoverflow, often the same question over and over again – one of which is regarding the “SELECT INTO” statement. I have found myself writing responses to questions about SELECT INTO statements many times and so I figured that I would write up an explanation of how to properly use this and then I can reference this post’s URL in response to questions on this topic!

Side note: It’s been quite some time that I used PL/SQL and really enjoyed spinning up an oracle database in my free tier account and writing this post – perhaps expect a few more similar style posts alongside my usual content (maybe more commonly asked forum topics)

Syntax

The PL/SQL SELECT INTO statements are the simplest and quickest way to fetch a single row of data from a table into variables. The syntax expected when using PL/SQL SELECT INTO is as follows:

SELECT column(s)
INTO   variable(s)
FROM   table(s)
WHERE  condition(s);

When using this syntax, it is important to note that the number of columns in the list of selected columns must be the same as the number of variables in the list of variables (caveat on if you are using a record – see later in this post). In addition, the data types used for column1 & variable1, column 2 & variable2, column3 & variable3, etc must be compatible.

SELECT INTO Examples

For the examples in this post, I will use the widely known “hr schema”. The entity-relationship diagram is as below.

Selecting a single column

The following example uses a SELECT INTO statement to get the email address of an employee based on the employee_id, which is the primary key of the employees table.

DECLARE

     v_email employees.email%TYPE;

BEGIN

     -- get the email address of employee 100 and assign it to v_email
     SELECT  email
     INTO    v_email
     FROM   employees
     WHERE  employee_id = 100;

     -- write employee email address
     dbms_output.put_line('The employees email address is: ' ||  v_email);

END;
/

An explanation of this example is as below:

• I have declared a variable named v_email with a datatype which matches the column datatype in the employees table (which is VARCHAR2(25). I could have used this datatype manually, but that wouldn’t protect against database level table changes)
• I used the SELECT INTO statement in order to select the email address from the employees table and assign it to the v_email variable that I declared above.
• I used the standard dbms_output.put_line procedure to show the employee’s email address which returned the following result:

Selecting multiple columns

The following example uses a SELECT INTO statement to get the employee name from the employees table, their job title from the jobs table and their department from the departments table based on the employee_id, which is the primary key of the employees table.

DECLARE

     v_first_name employees.first_name%TYPE;
     v_last_name  employees.last_name%TYPE;
     v_job_title  jobs.job_title%TYPE;
     v_dep_name   departments.department_name%TYPE;

BEGIN

     -- get the employee details of employee ID 100 and assign it to declared variables
     SELECT emp.first_name, 
            emp.last_name,
            jobs.job_title,
            dep.department_name
     INTO   v_first_name,
            v_last_name,
            v_job_title,
            v_dep_name
     FROM   employees   emp
     JOIN   jobs        jobs ON emp.job_id        = jobs.job_id
     JOIN   departments dep  ON emp.department_id = dep.department_id
     WHERE  employee_id = '100';

     -- write employee details
     dbms_output.put_line('Name:       ' || v_first_name || ' ' || v_last_name );
     dbms_output.put_line('Job Title:  ' ||  v_job_title);
     dbms_output.put_line('Department: ' ||  v_dep_name);

END;
/

An explanation of this example is as below:

• I have declared multiple variables named v_first_name, v_last_name, v_job_title, v_dep_name with a datatype which matches the column datatype in the employees, jobs and departments tables respectively. I could have used the manual data types (VARCHAR2) but that wouldn’t protect against database level table changes.
• I used the SELECT INTO statement in order to select the first name and last name of the employee from the employees table and assign to the v_first_name and v_last_name variables respectively, then I joined the jobs table on job_id to retrieve the employee’s job title and assign to the v_job_title variable and lastly I joined the departments table on department_id to retrieve the employee’s department name and assign to the v_dep_name variable – I purposely did these joins to other tables to show that it is indeed possible to use more complex SQL (there are no limits!) when using SELECT INTO, but that the important point is that your query still only returns 1 record and that there are enough variables to assign each column too.
• I used standard dbms_output.put_line procedure to show the employee’s details which returned the following:

Selecting a complete row (record)

The following example uses a SELECT INTO statement to get the entire row of employee data of an employee based on the employee_id, which is the primary key of the employees table.

DECLARE

  rec_employee employees%ROWTYPE;

BEGIN

  -- get the data for employee ID 100
  SELECT * 
  INTO rec_employee
  FROM employees
  WHERE employee_id = 100;

  -- write employee details
  dbms_output.put_line('Name: '  || rec_employee.first_name || ' ' || rec_employee.last_name );
  dbms_output.put_line('Email: ' || rec_employee.email);
  dbms_output.put_line('Number: '|| rec_employee.phone_number);

END;
/

An explanation of this example is as below:

• I have declared a record named rec_employee with a datatype which matches the data row in the employees table. This record will therefore be able to hold an entire row of data which matches the database.
• I used the SELECT INTO statement in order to select the data row from the employees table and assign it to the rec_employee record that I declared above.
• I used the standard dbms_output.put_line procedure to show some of the employee’s data which returned the following result: (note that to reference the data in the record, you first must specify the record name and the relevant column name)

To read more about common errors when using SELECT INTO, you can read another one of my posts HERE.