PL/SQL SELECT INTO Statement

The SELECT INTO statement in PL/SQL is used to work with database values inside a program. It allows retrieved data to be used in different programming operations.

• Stores query results in PL/SQL variables using the INTO clause.
• Performs mathematical operations, such as calculating employee bonuses.
• Uses IF-ELSE statements to make decisions based on retrieved values.
• Checks whether values meet specific conditions before further processing.

Syntax

SELECT column_1, column_2, ...
INTO variable_1, variable_2, ...
FROM table_name
WHERE condition;

• column_1, column_2: Columns whose values you want to retrieve.
• variable_1, variable_2: PL/SQL variables that store the retrieved values.
• table_name: Name of the table from which data is retrieved.
• WHERE condition: Specifies which record should be selected.

Example: First, we create a demo employees table on which we use the SELECT INTO statement.

Working

The SELECT INTO statement retrieves data from a table and assigns the selected column values to corresponding PL/SQL variables.

A PL/SQL block generally contains the following sections:

• DECLARE: Declares variables and constants. This section is optional.
• BEGIN: Contains the executable statements.
• EXCEPTION: Handles runtime errors. This section is optional.
• END: Marks the end of the PL/SQL block.

Example 1: SELECT INTO a Single Variable

In this example, we will retrieve the salary of the employee whose emp_id is 1 and store it in a variable named v_salary.

Query:

DECLARE
    v_salary NUMBER(8);
BEGIN
    SELECT salary
    INTO v_salary
    FROM employees
    WHERE emp_id = 1;

    DBMS_OUTPUT.PUT_LINE('salary: ' || v_salary);
END;
/

Output:

Example 2: SELECT INTO Multiple Variables

In this example, we will retrieve the salary and age of the employee whose emp_id is 1.

Query:

DECLARE
    v_salary NUMBER(8);
    v_age NUMBER;
BEGIN
    SELECT salary, age
    INTO v_salary, v_age
    FROM employees
    WHERE emp_id = 1;

    DBMS_OUTPUT.PUT_LINE('salary: ' || v_salary);
    DBMS_OUTPUT.PUT_LINE('age: ' || v_age);
END;
/

Output:

Example 3: SELECT INTO with WHERE Clause

The WHERE clause filters records according to a specified condition. In this example, we will retrieve the name of the employee whose age is 25.

Query:

DECLARE
    v_name employees.first_name%TYPE;
BEGIN
    SELECT first_name
    INTO v_name
    FROM employees
    WHERE age = 25;

    DBMS_OUTPUT.PUT_LINE('first_name ' || v_name);
END;
/

Output:

Example 4: SELECT INTO with DISTINCT

The DISTINCT keyword returns unique values. In this example, we will retrieve a unique employee age and store it in a variable.

Query:

DECLARE
    v_age NUMBER;
BEGIN
    SELECT DISTINCT age
    INTO v_age
    FROM employees;

    DBMS_OUTPUT.PUT_LINE('age ' || v_age);
END;
/

Output:

Example 5: SELECT INTO with ORDER BY and FETCH FIRST

The ORDER BY clause sorts query results, while FETCH FIRST limits the number of rows returned. In this example, we will retrieve the highest salary from the employees table.

Query:

DECLARE
    v_salary NUMBER(8);
BEGIN
    SELECT salary
    INTO v_salary
    FROM employees
    ORDER BY salary DESC
    FETCH FIRST 1 ROW ONLY;

    DBMS_OUTPUT.PUT_LINE('highest_salary: ' || v_salary);
END;
/

Output: