Oracle PL SQL Tutorial day 11 - SELECT INTO
the SELECT INTO statement, which allows to retrieve values from a database table and assign them to variables. This statement is especially useful when dealing with single-row queries, and it’s widely used in procedures, functions, and anonymous blocks.
These values saved in variables can be use for data comparison, assignment or any other action like insert , update etc.
we’ll explore the SELECT INTO statement in detail using practical examples based on the popular HR schema.
Make sure HR schema available otherwise nomurous errors might occur. You can use any editor for this practice toad, pl SQL developer or oracle SQL developer.
You can also login to oracle SQL or command prompt and use "Ed command" and write in simple text editor. Once execute SHOW ERROR command can be use to see errors.
What Is SELECT INTO in PL/SQL?
The SELECT INTO statement is used in PL/SQL to retrieve values from a table (or multiple tables) and assign them to variables. It is used when a query returns only one row.
If the query returns no rows, Oracle raises a NO_DATA_FOUND exception.
If the query returns more than one row, Oracle raises a TOO_MANY_ROWS exception.
Syntax of SELECT INTO
SELECT column1, column2, ...
INTO variable1, variable2, ...
FROM table
WHERE condition;
Remember the number of selection should be same in INTO clause.
We can use normal SQL, join SQL or sub query as well but the return columns must match in line one and two.
The alternative of SELECT INTO is CURSOR using fetch clause.
Incase we are using procedure or function the IN parameter can be use in where clause and OUT parameters can be use in INTO clause
Simple Example Using HR Schema
Suppose we want to get the first name and salary of an employee whose employee ID is 100.
DECLARE
v_first_name employees.first_name%TYPE;
v_salary employees.salary%TYPE;
BEGIN
SELECT first_name, salary
INTO v_first_name, v_salary
FROM employees
WHERE employee_id = 100;
DBMS_OUTPUT.PUT_LINE('Employee: ' || v_first_name);
DBMS_OUTPUT.PUT_LINE('Salary: ' || v_salary);
END;
In this example:
- - The values of first_name and salary for employee ID 100 are fetched into variables.
- - DBMS_OUTPUT.PUT_LINE is used to display the results.
- In above example we just print the values but it can be use for different purpose. Common usage are compare via IF, use in Insert/update, logging purpose etc.
Handling Exceptions
Since SELECT INTO must return exactly one row, you should handle exceptions like NO_DATA_FOUND and TOO_MANY_ROWS.
DECLARE
v_email employees.email%TYPE;
BEGIN
SELECT email
INTO v_email
FROM employees
WHERE employee_id = 9999;
DBMS_OUTPUT.PUT_LINE('Email: ' || v_email);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No employee found with that ID.');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('More than one employee found.');
END;
Example: Storing Data for Later Use
Suppose you want to retrieve an employee’s department and then get the department name using the retrieved department ID.
DECLARE
v_employee_id employees.employee_id%TYPE := 101;
v_department_id employees.department_id%TYPE;
v_dept_name departments.department_name%TYPE;
BEGIN
-- Get the department_id
SELECT department_id
INTO v_department_id
FROM employees
WHERE employee_id = v_employee_id;
-- Get department name using department_id
SELECT department_name
INTO v_dept_name
FROM departments
WHERE department_id = v_department_id;
DBMS_OUTPUT.PUT_LINE('Employee belongs to: ' || v_dept_name);
END;
Using %ROWTYPE for Multiple Columns
Instead of declaring each variable individually, you can use the %ROWTYPE attribute to create a variable of the same structure as a table row.
DECLARE
emp_rec employees%ROWTYPE;
BEGIN
SELECT *
INTO emp_rec
FROM employees
WHERE employee_id = 102;
DBMS_OUTPUT.PUT_LINE('Name: ' || emp_rec.first_name || ' ' || emp_rec.last_name);
DBMS_OUTPUT.PUT_LINE('Job: ' || emp_rec.job_id);
END;
Working with Joins in SELECT INTO
You can use JOINs with SELECT INTO as long as the result returns a single row.
DECLARE
v_emp_name employees.first_name%TYPE;
v_dept_name departments.department_name%TYPE;
BEGIN
SELECT e.first_name, d.department_name
INTO v_emp_name, v_dept_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.employee_id = 103;
DBMS_OUTPUT.PUT_LINE('Employee: ' || v_emp_name);
DBMS_OUTPUT.PUT_LINE('Department: ' || v_dept_name);
END;
Common Errors with SELECT INTO
| Error | Cause | Solution |
|-----------------|---------------------------------------------|----------------------------------------|
| NO_DATA_FOUND | The query returned no rows | Handle exception or ensure valid WHERE |
| TOO_MANY_ROWS | The query returned more than one row | Use additional filters or row limiting |
| VALUE_ERROR | Data type mismatch or variable too small | Use appropriate variable types |
Advanced Use: Conditional Logic After SELECT INTO
You can make decisions based on the data you fetched:
DECLARE
v_salary employees.salary%TYPE;
BEGIN
SELECT salary
INTO v_salary
FROM employees
WHERE employee_id = 104;
IF v_salary < 5000 THEN
DBMS_OUTPUT.PUT_LINE('Eligible for raise');
ELSE
DBMS_OUTPUT.PUT_LINE('Salary already high');
END IF;
END;
Summary
| Feature | SELECT INTO Usage |
|------------------------|--------------------------------------------|
| Purpose | Retrieve single row into variables |
| Syntax | SELECT column INTO variable FROM table |
| Data Types | Use %TYPE or %ROWTYPE |
| Error Handling | Must handle NO_DATA_FOUND, TOO_MANY_ROWS |
| Use with Joins | Yes, as long as it returns one row |
Conclusion
The SELECT INTO statement is a powerful feature in Oracle PL/SQL that allows developers to retrieve and manipulate data easily in procedural code. By using it correctly—especially with the HR schema as shown—you can build effective and efficient PL/SQL programs for a variety of tasks, from data validation to complex business logic.
Whether you're fetching a single employee's details, calculating bonuses, or preparing for data-driven decision-making, SELECT INTO is a core tool you’ll frequently use in your PL/SQL toolkit.
0 Comments