Skip to content
Tutorials Center
My learning

Topics

Lesson 38 of 38 ยท Projects and Practice

Practice: PL/SQL Exercises with Solutions

Ten Oracle PL/SQL practice exercises with solutions on cursors, functions, procedures, packages, triggers, exceptions, transactions and tuning, with runnable stand-ins to check your logic.

Reading about PL/SQL is not the same as writing it. This page gives you ten exercises that cover the chapter: cursors, functions, procedures, packages, triggers, exceptions, transactions and tuning. Try each one on paper or in your own Oracle database (the free Oracle Database 23ai Free edition is enough) before you open the solution.

Oracle cannot run on this site. Solutions in PL/SQL are read-only. Where possible you can check the same logic with a runnable Python or SQLite stand-in; remember these differ from Oracle in syntax, so use them for the idea, not as copy-paste code. All exercises use a table employees(employee_id, first_name, salary, department_id).

Exercises

  1. Department payroll. Write an anonymous block that loops over every department with a cursor FOR loop and prints the department id and the total salary.
  2. Tax function. Write a function net_salary(p_gross NUMBER) that applies 10% tax up to 5,000 and 20% on the part above. Use it in a SELECT.
  3. Safe lookup. Write a function employee_name(p_id) that returns '(unknown)' instead of raising NO_DATA_FOUND.
  4. Raise procedure. Write a procedure raise_department(p_dept, p_percent) that rejects percentages above 15 with error -20001 and prints the number of updated rows.
  5. Package. Create a package stats_api with a public function median_salary(p_dept) and a private helper. Describe what goes in the spec and what in the body.
  6. Trigger. Write a trigger that prevents lowering any salary, with a clear error message.
  7. Savepoint. Insert three rows in a transaction, set a savepoint after the second, roll back to it, and commit. Which rows remain?
  8. Bulk. Rewrite a row-by-row loop that gives every employee in department 50 a 5% raise as a single statement, then as a FORALL.
  9. Exceptions. Write a block that divides 100 by each value in a list and prints the quotient, or skipped for a zero, without stopping.
  10. Tuning. SELECT * FROM employees WHERE TRUNC(hire_date) = DATE '2026-01-05' is slow on a large table with an index on hire_date. Explain why and fix it.

Solutions

1. Department payroll

SQL
BEGIN
  FOR d IN (SELECT department_id, SUM(salary) AS total
              FROM employees
             GROUP BY department_id
             ORDER BY department_id) LOOP
    DBMS_OUTPUT.PUT_LINE('Department ' || d.department_id || ': ' || d.total);
  END LOOP;
END;
/

The GROUP BY does the real work in SQL; the loop only prints. A common first attempt is one loop for departments and an inner query per department, which also works but runs many more statements. Check the aggregation in SQLite:

SQL Payroll per department (SQLite stand-in)

Press Run (or Ctrl + Enter) to see the result here.

2. Tax function

SQL
CREATE OR REPLACE FUNCTION net_salary (p_gross IN NUMBER) RETURN NUMBER DETERMINISTIC IS
BEGIN
  IF p_gross <= 5000 THEN
    RETURN p_gross * 0.90;
  END IF;
  RETURN 5000 * 0.90 + (p_gross - 5000) * 0.80;
END;
/

SELECT first_name, salary, net_salary(salary) FROM employees;

Test the boundaries (exactly 5,000, 5,000.01, 0, and NULL: the function returns NULL for a NULL input because NULL <= 5000 is not true and the arithmetic stays NULL). The Python model checks the arithmetic:

Python Net salary arithmetic (Python stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

3. Safe lookup

SQL
CREATE OR REPLACE FUNCTION employee_name (p_id IN NUMBER) RETURN VARCHAR2 IS
  v_name employees.first_name%TYPE;
BEGIN
  SELECT first_name INTO v_name FROM employees WHERE employee_id = p_id;
  RETURN v_name;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN '(unknown)';
END;
/

Only NO_DATA_FOUND is caught; any other error still surfaces. A different way is SELECT MAX(first_name) ..., which returns NULL for no match, plus NVL(..., '(unknown)'), which needs no handler. See exception handling.

4. Raise procedure

SQL
CREATE OR REPLACE PROCEDURE raise_department (p_dept IN NUMBER, p_percent IN NUMBER) IS
BEGIN
  IF p_percent IS NULL OR p_percent <= 0 OR p_percent > 15 THEN
    RAISE_APPLICATION_ERROR(-20001, 'Percent must be between 0 and 15');
  END IF;
  UPDATE employees SET salary = salary * (1 + p_percent / 100)
   WHERE department_id = p_dept;
  DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' employees updated');
END;
/

The validation comes before any change, so a rejected call touches nothing. SQL%ROWCOUNT must be read right after the UPDATE.

5. Package

The specification declares only FUNCTION median_salary (p_dept IN NUMBER) RETURN NUMBER;. The body implements it with Oracle's built-in MEDIAN aggregate and keeps a private helper, for example validate_department that raises an error for an unknown department, which is not declared in the spec and therefore callable only inside the body.

SQL
CREATE OR REPLACE PACKAGE stats_api AS
  FUNCTION median_salary (p_dept IN NUMBER) RETURN NUMBER;
END stats_api;
/
CREATE OR REPLACE PACKAGE BODY stats_api AS
  PROCEDURE validate_department (p_dept IN NUMBER) IS
    v_count NUMBER;
  BEGIN
    SELECT COUNT(*) INTO v_count FROM employees WHERE department_id = p_dept;
    IF v_count = 0 THEN
      RAISE_APPLICATION_ERROR(-20002, 'No employees in department ' || p_dept);
    END IF;
  END;

  FUNCTION median_salary (p_dept IN NUMBER) RETURN NUMBER IS
    v_median NUMBER;
  BEGIN
    validate_department(p_dept);
    SELECT MEDIAN(salary) INTO v_median FROM employees WHERE department_id = p_dept;
    RETURN v_median;
  END;
END stats_api;
/

6. Trigger that blocks pay cuts

SQL
CREATE OR REPLACE TRIGGER employees_no_pay_cut
  BEFORE UPDATE OF salary ON employees
  FOR EACH ROW
  WHEN (NEW.salary < OLD.salary)
BEGIN
  RAISE_APPLICATION_ERROR(-20003,
    'Salary of employee ' || :OLD.employee_id || ' may not drop from ' ||
    :OLD.salary || ' to ' || :NEW.salary);
END;
/

It is a BEFORE row trigger so the change is rejected before it is stored, and the WHEN clause keeps it from firing for raises. The SQLite equivalent shows the rejection:

SQL Blocking pay cuts (SQLite stand-in)

Press Run (or Ctrl + Enter) to see the result here.

7. Savepoint

SQL
INSERT INTO t VALUES (1);
INSERT INTO t VALUES (2);
SAVEPOINT after_two;
INSERT INTO t VALUES (3);
ROLLBACK TO SAVEPOINT after_two;
COMMIT;

Rows 1 and 2 remain: ROLLBACK TO undid only row 3, and the transaction stayed open until COMMIT. Verify in SQLite:

SQL Which rows remain? (SQLite stand-in)

Press Run (or Ctrl + Enter) to see the result here.

8. Bulk raise

SQL
-- one statement (best)
UPDATE employees SET salary = salary * 1.05 WHERE department_id = 50;

-- when per-row logic is needed
DECLARE
  TYPE t_ids IS TABLE OF employees.employee_id%TYPE;
  v_ids t_ids;
BEGIN
  SELECT employee_id BULK COLLECT INTO v_ids FROM employees WHERE department_id = 50;
  FORALL i IN 1 .. v_ids.COUNT
    UPDATE employees SET salary = salary * 1.05 WHERE employee_id = v_ids(i);
END;
/

The single statement is simpler and faster. FORALL only pays off when the new values come from procedural code. See bulk processing.

9. Skipping zeros

SQL
DECLARE
  TYPE t_nums IS TABLE OF NUMBER;
  v_list t_nums := t_nums(5, 0, 20, 0, 4);
BEGIN
  FOR i IN 1 .. v_list.COUNT LOOP
    BEGIN
      DBMS_OUTPUT.PUT_LINE(100 / v_list(i));
    EXCEPTION
      WHEN ZERO_DIVIDE THEN
        DBMS_OUTPUT.PUT_LINE('skipped');
    END;
  END LOOP;
END;
/

The inner BEGIN ... EXCEPTION ... END is a sub-block per item, so the failure ends only that iteration. This prints 20, skipped, 5, skipped, 25. The same pattern in Python:

Python One handler per item (Python stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

10. The slow TRUNC query

TRUNC(hire_date) wraps the column in a function, so the B-tree index on hire_date cannot be searched and Oracle scans the whole table. Rewrite the condition so the column stands alone:

SQL
SELECT *
  FROM employees
 WHERE hire_date >= DATE '2026-01-05'
   AND hire_date <  DATE '2026-01-06';

An alternative is a function-based index on TRUNC(hire_date). Confirm the improvement with EXPLAIN PLAN, as described in execution plans and SQL tuning.

What to practice next

Build a small project of your own with a schema, a package, at least one trigger and a bulk import. Write tests as anonymous blocks that call your package with good and bad data and check the error codes. Then read an execution plan for every query in your report. Use the ledger and inventory projects as templates.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. In exercise 7, which rows remain after COMMIT?

    ROLLBACK TO the savepoint undid only the insert made after it.
  2. 2. Why should the validation in raise_department come before the UPDATE?

    Checking inputs first keeps failed calls free of side effects.
  3. 3. Which is the best way to give 5% raises to everyone in a department when no per-row logic is needed?

    Set-based SQL avoids the context switches of a loop.
  4. 4. What makes the sub-block pattern in exercise 9 work?

    The handler ends the inner block only; the outer loop goes on with the next element.

Summary

  • Practice each construct on its own, then combine them in a project.
  • Prefer set-based SQL, validate inputs first, handle exceptions close to the risky statement, and keep predicates sargable.
  • Check logic with whatever runs: the stand-ins here model the ideas, but always test final PL/SQL on a real Oracle instance.

Back to the track overview: Oracle SQL and PL/SQL