SQL in PL/SQL

PL/SQL can embed DML statements (INSERT, UPDATE, DELETE, MERGE) and single-row SELECT INTO directly in its block body. These statements communicate with Oracle's SQL engine — understanding the boundary between the two engines is key to writing efficient PL/SQL.

SELECT INTO

SELECT INTO fetches exactly one row into one or more variables:

DECLARE
    v_first_name  employees.first_name%TYPE;
    v_last_name   employees.last_name%TYPE;
    v_salary      employees.salary%TYPE;
BEGIN
    SELECT first_name, last_name, salary
    INTO   v_first_name, v_last_name, v_salary
    FROM   employees
    WHERE  employee_id = 100;

    DBMS_OUTPUT.PUT_LINE(
        v_first_name || ' ' || v_last_name ||
        ' earns $' || TO_CHAR(v_salary, 'FM999,999.00')
    );
END;
/

NO_DATA_FOUND and TOO_MANY_ROWS

SELECT INTO raises exceptions if the row count is not exactly 1:

DECLARE
    v_name  employees.first_name%TYPE;
BEGIN
    -- Might raise NO_DATA_FOUND or TOO_MANY_ROWS
    SELECT first_name
    INTO   v_name
    FROM   employees
    WHERE  department_id = 90;   -- multiple rows in dept 90!

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('No employee found.');
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('More than one employee matches — use a cursor.');
END;
/

DML Inside PL/SQL

INSERT, UPDATE, DELETE, and MERGE work inside PL/SQL exactly as they do in SQL:

INSERT

BEGIN
    INSERT INTO employees (
        employee_id, first_name, last_name, email,
        hire_date, job_id, salary, department_id
    ) VALUES (
        employees_seq.NEXTVAL, 'Jane', 'Doe', 'JDOE',
        SYSDATE, 'IT_PROG', 7500, 60
    );
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Inserted employee. ID: ' || employees_seq.CURRVAL);
END;
/

UPDATE

DECLARE
    v_raise_pct  NUMBER := 10;
BEGIN
    UPDATE employees
    SET    salary = salary * (1 + v_raise_pct / 100)
    WHERE  department_id = 60
    AND    salary < 6000;

    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' employees received a raise.');
    COMMIT;
END;
/

DELETE

BEGIN
    DELETE FROM job_history
    WHERE  end_date < ADD_MONTHS(SYSDATE, -120);  -- older than 10 years

    IF SQL%ROWCOUNT > 0 THEN
        DBMS_OUTPUT.PUT_LINE('Purged ' || SQL%ROWCOUNT || ' old records.');
        COMMIT;
    ELSE
        DBMS_OUTPUT.PUT_LINE('Nothing to purge.');
    END IF;
END;
/

RETURNING INTO

RETURNING captures values from a DML statement into PL/SQL variables — eliminating a follow-up SELECT:

DECLARE
    v_new_salary  employees.salary%TYPE;
    v_emp_name    VARCHAR2(100);
BEGIN
    UPDATE employees
    SET    salary = salary * 1.15
    WHERE  employee_id = 107
    RETURNING salary,
              first_name || ' ' || last_name
    INTO   v_new_salary, v_emp_name;

    DBMS_OUTPUT.PUT_LINE(v_emp_name || ' new salary: $' || v_new_salary);
    COMMIT;
END;
/

MERGE (Upsert)

MERGE inserts or updates in one statement — ideal for loading data:

BEGIN
    MERGE INTO employees tgt
    USING (SELECT 999        AS employee_id,
                  'Test'     AS first_name,
                  'User'     AS last_name,
                  'TUSER'    AS email,
                  SYSDATE    AS hire_date,
                  'IT_PROG'  AS job_id,
                  6000       AS salary,
                  60         AS department_id
           FROM dual) src
    ON (tgt.employee_id = src.employee_id)
    WHEN MATCHED THEN
        UPDATE SET tgt.salary      = src.salary,
                   tgt.last_name   = src.last_name
    WHEN NOT MATCHED THEN
        INSERT (employee_id, first_name, last_name, email,
                hire_date, job_id, salary, department_id)
        VALUES (src.employee_id, src.first_name, src.last_name, src.email,
                src.hire_date, src.job_id, src.salary, src.department_id);

    DBMS_OUTPUT.PUT_LINE('MERGE complete: ' || SQL%ROWCOUNT || ' row(s).');
    COMMIT;
END;
/

Transaction Control

PL/SQL has full transaction control:

DECLARE
    v_emp_id  NUMBER := 999;
BEGIN
    -- Start of implicit transaction
    INSERT INTO employees (employee_id, first_name, last_name, email,
                           hire_date, job_id, salary)
    VALUES (v_emp_id, 'Temp', 'Worker', 'TWORKER',
            SYSDATE, 'IT_PROG', 4000);

    SAVEPOINT after_insert;

    UPDATE employees
    SET    salary = 5000
    WHERE  employee_id = v_emp_id;

    -- Something goes wrong — roll back to savepoint
    ROLLBACK TO SAVEPOINT after_insert;
    -- INSERT is preserved; UPDATE is rolled back

    COMMIT;  -- commit the insert
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;  -- undo everything on unexpected error
        RAISE;
END;
/

Bind Variables

PL/SQL variables used in SQL statements are automatically treated as bind variables — Oracle parses the SQL once and re-executes with different values, improving performance and preventing SQL injection:

DECLARE
    v_dept_id   employees.department_id%TYPE := 80;
    v_min_sal   employees.salary%TYPE        := 10000;
    v_count     NUMBER;
BEGIN
    -- v_dept_id and v_min_sal are bind variables in the SQL engine
    SELECT COUNT(*)
    INTO   v_count
    FROM   employees
    WHERE  department_id = v_dept_id
    AND    salary        > v_min_sal;

    DBMS_OUTPUT.PUT_LINE('Count: ' || v_count);
END;
/

Summary

• SELECT INTO fetches one row; raises NO_DATA_FOUND or TOO_MANY_ROWS if the count is wrong.
• DML (INSERT, UPDATE, DELETE, MERGE) embeds directly in PL/SQL blocks.
• RETURNING ... INTO captures post-DML values without a follow-up query.
• Use COMMIT, ROLLBACK, and SAVEPOINT for transaction control — commit infrequently, never inside fast loops.
• PL/SQL variables in SQL act as bind variables automatically — better performance and security than string concatenation.