Colevate Online Tech Tutorials

Interacting with the Database (SQL in PL/SQL)

When you're working with Oracle PL/SQL, the ability to seamlessly execute SQL statements is fundamental. This integration is what makes PL/SQL such a powerful language for database development, allowing you to fetch data, modify records, and control transactions all within a programmatic flow.

SELECT ... INTO for single-row queries

The SELECT ... INTO statement is your go-to for retrieving a single row of data from the database and assigning the column values to PL/SQL variables. It's crucial for fetching specific information, such as a user's details or a product's price, directly into your program for further processing.

Note: When using SELECT ... INTO, it's vital to ensure your query is guaranteed to return at most one row. If it returns more than one row, you'll encounter a TOO_MANY_ROWS exception. If it returns no rows, a NO_DATA_FOUND exception will be raised. Best practices involve handling these exceptions for robust Oracle PL/SQL applications. This statement is a cornerstone for single-row data retrieval in PL/SQL, ideal for scenarios like fetching customer details by ID or product information.

Example 1: Basic SELECT INTO

DECLARE
    v_employee_name VARCHAR2(100);
    v_salary        NUMBER;
BEGIN
    -- Selects the employee name and salary for employee_id 100
    -- and stores them into PL/SQL variables.
    SELECT first_name || ' ' || last_name, salary
    INTO v_employee_name, v_salary
    FROM employees
    WHERE employee_id = 100;

    DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name);
    DBMS_OUTPUT.PUT_LINE('Salary: ' || v_salary);

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Employee ID 100 not found.');
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('Multiple employees found for ID 100, which should not happen.');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);
END;
/

Explanation This Oracle PL/SQL block demonstrates a basic SELECT ... INTO operation. We declare two local variables, v_employee_name and v_salary, to hold the retrieved data. The SELECT statement fetches the concatenated first and last names and the salary for an employee with employee_id = 100 from the employees table. The INTO clause then assigns these values to our declared PL/SQL variables. We include exception handling for NO_DATA_FOUND (if the employee doesn't exist) and TOO_MANY_ROWS (if the query somehow returns more than one employee for a unique ID, which indicates a data issue or logical error), making the code more robust for Oracle database operations.

Example 2: SELECT INTO with Aggregate Function

DECLARE
    v_total_employees NUMBER;
BEGIN
    -- Counts the total number of employees in the employees table
    -- and stores the result in v_total_employees.
    SELECT COUNT(*)
    INTO v_total_employees
    FROM employees;

    DBMS_OUTPUT.PUT_LINE('Total Employees: ' || v_total_employees);

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An error occurred while counting employees: ' || SQLERRM);
END;
/

Explanation Here, SELECT COUNT(*) is used with INTO to retrieve an aggregate value – the total number of employees. This is a common pattern for obtaining summary information. Since COUNT(*) always returns a single value (even if it's 0), TOO_MANY_ROWS is not a concern here, but general exception handling is still good practice for Oracle PL/SQL database queries. This illustrates how SELECT INTO can be used with SQL aggregate functions for single-value results.

Example 3: SELECT INTO with a Record Type

DECLARE
    -- Define a record type to hold employee details
    TYPE EmployeeRec IS RECORD (
        employee_id NUMBER,
        first_name  VARCHAR2(50),
        last_name   VARCHAR2(50),
        email       VARCHAR2(100)
    );
    v_employee_info EmployeeRec; -- Declare a variable of the record type
BEGIN
    -- Selects multiple columns into a single record variable.
    SELECT employee_id, first_name, last_name, email
    INTO v_employee_info
    FROM employees
    WHERE employee_id = 101;

    DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_employee_info.employee_id);
    DBMS_OUTPUT.PUT_LINE('Name: ' || v_employee_info.first_name || ' ' || v_employee_info.last_name);
    DBMS_OUTPUT.PUT_LINE('Email: ' || v_employee_info.email);

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Employee ID 101 not found.');
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('Multiple employees found for ID 101, which should not happen.');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END;
/

Explanation This example demonstrates fetching an entire row into a PL/SQL record. We define a RECORD type EmployeeRec that matches the structure of the employees table columns we intend to retrieve. Then, we declare a variable v_employee_info of this record type. The SELECT ... INTO statement directly populates all fields of the record, simplifying the code when dealing with multiple related columns. This approach is highly recommended for structured data retrieval in Oracle PL/SQL applications, promoting code readability and maintainability.

Example 4: SELECT INTO with %ROWTYPE

DECLARE
    v_employee_row employees%ROWTYPE; -- Declare a variable based on the employees table structure
BEGIN
    -- Selects an entire row into a %ROWTYPE variable.
    SELECT *
    INTO v_employee_row
    FROM employees
    WHERE employee_id = 102;

    DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_employee_row.employee_id);
    DBMS_OUTPUT.PUT_LINE('First Name: ' || v_employee_row.first_name);
    DBMS_OUTPUT.PUT_LINE('Hire Date: ' || v_employee_row.hire_date);

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Employee ID 102 not found.');
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('Multiple employees found for ID 102.');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END;
/

Explanation The %ROWTYPE attribute is a powerful feature in Oracle PL/SQL that allows you to declare a record variable with the same structure as a database table or a cursor. In this example, v_employee_row automatically inherits all the column definitions from the employees table. This is extremely useful for retrieving complete rows without needing to declare individual variables for each column, making your code more adaptable to schema changes and a key component of efficient Oracle database programming.

Example 5: SELECT INTO with Subquery

DECLARE
    v_department_name VARCHAR2(100);
BEGIN
    -- Selects the department name for a specific employee using a subquery.
    SELECT department_name
    INTO v_department_name
    FROM departments
    WHERE department_id = (SELECT department_id
                           FROM employees
                           WHERE employee_id = 103);

    DBMS_OUTPUT.PUT_LINE('Department Name: ' || v_department_name);

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Department not found for employee ID 103 or employee not found.');
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('Multiple departments found for employee ID 103, which should not happen.');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END;
/

Explanation This demonstrates using SELECT ... INTO with a subquery. The inner query SELECT department_id FROM employees WHERE employee_id = 103 first retrieves the department ID for a specific employee. This ID is then used by the outer query to fetch the corresponding department_name from the departments table, which is then assigned to v_department_name. This pattern is useful for fetching data based on related information in other tables, a common task in Oracle SQL and PL/SQL.

PL SQL

SQL in PL SQL

Interacting with the Database (SQL in PL/SQL)