Skip to main content

​
What is Data Retrieval SELECT?

Data Retrieval SELECT is the primary SQL command used to query and extract data from database tables in PL/SQL. It’s commonly referred to simply as “SELECT statement” or “query.” The core purpose is to retrieve specific data subsets from one or more tables, solving the fundamental problem of accessing and filtering database information programmatically.
Without SELECT operations, PL/SQL would lack the ability to read and process the data it’s designed to manipulate.

​
SELECT Statement

The SELECT statement specifies which columns to retrieve from the database. In PL/SQL, SELECT is primarily used within cursor definitions or SELECT INTO statements for variable assignment. 
DECLARE
    v_employee_name employees.name%TYPE;
    v_salary employees.salary%TYPE;
BEGIN
    -- Basic SELECT INTO for variable assignment
    SELECT name, salary 
    INTO v_employee_name, v_salary
    FROM employees 
    WHERE employee_id = 100;
    
    DBMS_OUTPUT.PUT_LINE('Employee: ' || v_employee_name || ', Salary: ' || v_salary);
END;
/

​
FROM Clause

The FROM clause specifies the table(s) from which to retrieve data. It’s mandatory for most SELECT statements and supports single tables, multiple tables (joins), or subqueries. 
DECLARE
    CURSOR emp_dept_cursor IS
        -- FROM clause with table join
        SELECT e.employee_id, e.name, d.department_name
        FROM employees e 
        JOIN departments d ON e.department_id = d.department_id
        WHERE e.salary > 50000;
BEGIN
    FOR emp_rec IN emp_dept_cursor LOOP
        DBMS_OUTPUT.PUT_LINE(emp_rec.employee_id || ': ' || 
                           emp_rec.name || ' - ' || emp_rec.department_name);
    END LOOP;
END;
/

​
WHERE Clause

The WHERE clause filters records based on specified conditions, reducing the result set to only relevant data. 
DECLARE
    v_high_paid_count NUMBER;
    v_department_id NUMBER := 50;
BEGIN
    -- WHERE clause with multiple conditions
    SELECT COUNT(*)
    INTO v_high_paid_count
    FROM employees 
    WHERE salary > 75000 
      AND department_id = v_department_id
      AND hire_date > DATE '2020-01-01';
    
    DBMS_OUTPUT.PUT_LINE('High-paid employees in department ' || 
                       v_department_id || ': ' || v_high_paid_count);
END;
/

​
DISTINCT Keyword

DISTINCT eliminates duplicate rows from the result set, returning only unique combinations of the selected columns.
Use DISTINCT to ensure you retrieve only unique values from your query results.
DECLARE
    CURSOR unique_depts_cursor IS
        -- DISTINCT to get unique department IDs
        SELECT DISTINCT department_id
        FROM employees
        WHERE salary IS NOT NULL
        ORDER BY department_id;
BEGIN
    FOR dept_rec IN unique_depts_cursor LOOP
        DBMS_OUTPUT.PUT_LINE('Department with employees: ' || dept_rec.department_id);
    END LOOP;
END;
/

​
Column Aliases

Column aliases provide alternative names for columns in the result set, improving readability and handling complex expressions. 
DECLARE
    v_annual_compensation NUMBER;
BEGIN
    -- Column aliases for calculated values
    SELECT employee_id,
           name,
           salary AS base_salary,
           salary * 1.1 AS projected_salary,  -- Alias for expression
           (salary * 12) + NVL(commission_pct, 0) AS annual_compensation
    INTO v_annual_compensation
    FROM employees
    WHERE employee_id = 101;
    
    DBMS_OUTPUT.PUT_LINE('Annual compensation: ' || v_annual_compensation);
END;
/

​
Concatenation Operator

The concatenation operator (||) combines string values and columns into a single output string. 
DECLARE
    CURSOR employee_info_cursor IS
        -- Concatenation for formatted output
        SELECT employee_id,
               first_name || ' ' || last_name AS full_name,
               'Employee #' || employee_id || ' works in department ' || department_id AS employee_details
        FROM employees
        WHERE department_id IN (10, 20, 30);
BEGIN
    FOR emp_rec IN employee_info_cursor LOOP
        DBMS_OUTPUT.PUT_LINE(emp_rec.employee_details);
    END LOOP;
END;
/

​
Arithmetic Expressions

Arithmetic expressions perform calculations on numeric columns and constants within SELECT statements. 
DECLARE
    v_bonus_percentage NUMBER := 0.15;
    v_net_salary employees.salary%TYPE;
BEGIN
    -- Complex arithmetic expression with variables
    SELECT employee_id,
           salary,
           salary * v_bonus_percentage AS bonus,
           salary * (1 + v_bonus_percentage) AS total_compensation,
           (salary - (salary * 0.2)) AS net_salary_after_tax
    INTO v_net_salary
    FROM employees
    WHERE employee_id = 105;
    
    DBMS_OUTPUT.PUT_LINE('Net salary: ' || v_net_salary);
END;
/

​
Why is Data Retrieval SELECT Important?

SELECT is the foundation of all data access operations in PL/SQL.

​
Key Principles

1. Data Abstraction Principle - SELECT enables clean separation between data storage and business logic, allowing PL/SQL to work with data without knowing physical storage details. 2. Single Responsibility (SOLID) - Each SELECT query focuses on retrieving specific data subsets, maintaining focused, reusable data access routines. 3. Performance Optimization Foundation - Efficient SELECT statements form the basis for scalable PL/SQL applications by minimizing data transfer and processing overhead.

​
Advanced Nuances

​
Bulk Operations with SELECT

Senior developers use BULK COLLECT with SELECT to fetch multiple rows efficiently: 
DECLARE
    TYPE emp_table_type IS TABLE OF employees%ROWTYPE;
    emp_table emp_table_type;
BEGIN
    SELECT * BULK COLLECT INTO emp_table
    FROM employees 
    WHERE department_id = 50;
    -- Process entire result set in memory
END;
/

​
Dynamic SQL Integration

SELECT statements can be constructed dynamically for flexible querying scenarios, but always use bind variables to prevent SQL injection.
DECLARE
    v_sql VARCHAR2(1000);
    v_dept_name departments.department_name%TYPE;
    v_dept_id NUMBER := 60;
BEGIN
    v_sql := 'SELECT department_name FROM departments WHERE department_id = :id';
    EXECUTE IMMEDIATE v_sql INTO v_dept_name USING v_dept_id;
END;
/

​
Correlated Subqueries

Advanced filtering using subqueries that reference outer query columns: 
SELECT employee_id, name, salary
FROM employees e1
WHERE salary > (SELECT AVG(salary) 
               FROM employees e2 
               WHERE e2.department_id = e1.department_id);

​
How This Fits the Roadmap

Within the “Basic SQL Querying” section, Data Retrieval SELECT serves as the foundational building block that enables all subsequent database operations.

​
Prerequisites For:

  • JOIN operations (building on FROM clause)
  • Aggregate functions (extending SELECT capabilities)
  • Subqueries (nesting SELECT statements)
  • Data manipulation (UPDATE/DELETE with WHERE conditions)

​
Unlocks:

  • Query optimization
  • Analytic functions
  • Complex data transformation patterns
  • Senior PL/SQL development techniques

Build docs developers (and LLMs) love

Get started for freeTalk to us