Oracle PL/SQL Stored Procedures and Functions: A Practical Guide
Stored procedures and functions are the building blocks of reusable, maintainable PL/SQL code. They let you encapsulate business logic in the database, share it across multiple applications, and keep it in a single place that is easy to update and test.
In this guide, we cover how to create procedures and functions, the difference between them, parameter modes, function return types, overloading, and the patterns that make stored code genuinely useful in enterprise development.
Stored Procedures vs Functions: The Core Difference
Both are named, compiled, stored PL/SQL programs. The key difference:
- A procedure performs an action. It may produce side effects (insert records, send emails, update data) and can return values through OUT parameters, but it does not have a return value in the traditional sense
- A function computes and returns a single value. It is meant to be used in an expression — in a SELECT statement, in an IF condition, or as a default value
A useful heuristic: if you can use it in a SELECT, it is probably a function. If it is an action, it is probably a procedure.
Creating a Stored Procedure
CREATE OR REPLACE PROCEDURE update_employee_salary (
p_employee_id IN NUMBER,
p_new_salary IN NUMBER,
p_success OUT BOOLEAN,
p_message OUT VARCHAR2
) IS
v_current_salary employees.salary%TYPE;
BEGIN
-- Fetch current salary for validation
SELECT salary
INTO v_current_salary
FROM employees
WHERE employee_id = p_employee_id;
-- Business rule: salary cannot decrease
IF p_new_salary < v_current_salary THEN
p_success := FALSE;
p_message := 'New salary cannot be less than current salary ('
|| v_current_salary || ').';
RETURN;
END IF;
-- Apply the update
UPDATE employees
SET salary = p_new_salary,
last_updated = SYSDATE
WHERE employee_id = p_employee_id;
COMMIT;
p_success := TRUE;
p_message := 'Salary updated successfully.';
EXCEPTION
WHEN NO_DATA_FOUND THEN
p_success := FALSE;
p_message := 'Employee ID ' || p_employee_id || ' not found.';
WHEN OTHERS THEN
ROLLBACK;
p_success := FALSE;
p_message := 'Unexpected error: ' || SQLERRM;
END update_employee_salary;The CREATE OR REPLACE clause means you can run this statement again to update the procedure without dropping and recreating it. Existing grants are preserved.
Parameter Modes: IN, OUT, IN OUT
| Mode | Direction | Typical Use |
|---|---|---|
| IN | Caller → Procedure | Input values (cannot be modified inside procedure) |
| OUT | Procedure → Caller | Return values (caller receives the value after call) |
| IN OUT | Both directions | Value passed in, modified, and returned back |
-- Calling the procedure
DECLARE
v_success BOOLEAN;
v_message VARCHAR2(500);
BEGIN
update_employee_salary(
p_employee_id => 101,
p_new_salary => 7500,
p_success => v_success,
p_message => v_message
);
IF v_success THEN
DBMS_OUTPUT.PUT_LINE('Success: ' || v_message);
ELSE
DBMS_OUTPUT.PUT_LINE('Failed: ' || v_message);
END IF;
END;Named parameter notation (p_param => value) is recommended for procedures with multiple parameters. It makes the call self-documenting and is immune to parameter order changes.
Creating a Stored Function
CREATE OR REPLACE FUNCTION get_employee_annual_salary (
p_employee_id IN NUMBER
) RETURN NUMBER IS
v_monthly_salary employees.salary%TYPE;
v_commission employees.commission_pct%TYPE;
BEGIN
SELECT salary, NVL(commission_pct, 0)
INTO v_monthly_salary, v_commission
FROM employees
WHERE employee_id = p_employee_id;
-- Annual = (monthly salary + commission) * 12
RETURN (v_monthly_salary + (v_monthly_salary * v_commission)) * 12;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
END get_employee_annual_salary;Functions can be called directly in SQL — this is one of their main advantages:
-- Using the function in a SELECT statement
SELECT
employee_id,
last_name,
salary AS monthly_salary,
get_employee_annual_salary(employee_id) AS annual_salary
FROM employees
WHERE department_id = 20;Deterministic Functions
If a function always returns the same result for the same inputs (no side effects, no database reads), mark it DETERMINISTIC. Oracle can cache the results and skip re-evaluation for repeated calls with the same arguments:
CREATE OR REPLACE FUNCTION format_phone (
p_phone IN VARCHAR2
) RETURN VARCHAR2 DETERMINISTIC IS
BEGIN
-- Format 10-digit number as (XXX) XXX-XXXX
IF LENGTH(REGEXP_REPLACE(p_phone, '[^0-9]', '')) = 10 THEN
RETURN '(' || SUBSTR(p_phone, 1, 3) || ') ' ||
SUBSTR(p_phone, 4, 3) || '-' || SUBSTR(p_phone, 7, 4);
ELSE
RETURN p_phone; -- Return as-is if not a valid 10-digit number
END IF;
END format_phone;Packages: Organizing Related Code
Individual procedures and functions are fine for small projects. As your codebase grows, packages become essential. A package groups related procedures, functions, types, and variables under one namespace:
-- Package Specification (the public interface)
CREATE OR REPLACE PACKAGE employee_mgr IS
PROCEDURE hire_employee (
p_first_name IN VARCHAR2,
p_last_name IN VARCHAR2,
p_department_id IN NUMBER,
p_salary IN NUMBER,
p_employee_id OUT NUMBER
);
PROCEDURE terminate_employee (
p_employee_id IN NUMBER,
p_reason IN VARCHAR2
);
FUNCTION get_headcount (
p_department_id IN NUMBER
) RETURN NUMBER;
END employee_mgr;
-- Package Body (the implementation)
CREATE OR REPLACE PACKAGE BODY employee_mgr IS
PROCEDURE hire_employee (
p_first_name IN VARCHAR2,
p_last_name IN VARCHAR2,
p_department_id IN NUMBER,
p_salary IN NUMBER,
p_employee_id OUT NUMBER
) IS
BEGIN
INSERT INTO employees (employee_id, first_name, last_name, department_id, salary, hire_date)
VALUES (employee_seq.NEXTVAL, p_first_name, p_last_name, p_department_id, p_salary, SYSDATE)
RETURNING employee_id INTO p_employee_id;
COMMIT;
END hire_employee;
PROCEDURE terminate_employee (
p_employee_id IN NUMBER,
p_reason IN VARCHAR2
) IS
BEGIN
UPDATE employees SET status = 'TERMINATED', termination_reason = p_reason
WHERE employee_id = p_employee_id;
COMMIT;
END terminate_employee;
FUNCTION get_headcount (p_department_id IN NUMBER) RETURN NUMBER IS
v_count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count FROM employees
WHERE department_id = p_department_id AND status = 'ACTIVE';
RETURN v_count;
END get_headcount;
END employee_mgr;Calling package members uses dot notation: employee_mgr.hire_employee(...) and employee_mgr.get_headcount(20).
Error Handling in Procedures
Production procedures need robust error handling. The standard pattern:
CREATE OR REPLACE PROCEDURE process_monthly_payroll (
p_month IN VARCHAR2 -- Format: 'YYYY-MM'
) IS
BEGIN
-- Your payroll processing logic
NULL;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK; -- Never leave partial commits
-- Log with enough context to diagnose the issue later
INSERT INTO error_log (log_time, program, error_code, error_msg, context)
VALUES (SYSDATE, 'PROCESS_MONTHLY_PAYROLL', SQLCODE,
SUBSTR(SQLERRM, 1, 500), 'Month: ' || p_month);
COMMIT; -- Commit the log entry (use autonomous transaction for safety)
RAISE; -- Re-raise so the caller knows something went wrong
END;Viewing Stored Code
-- List all procedures and functions in your schema
SELECT object_name, object_type, status
FROM user_objects
WHERE object_type IN ('PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY')
ORDER BY object_type, object_name;
-- View the source code of a specific object
SELECT text FROM user_source
WHERE name = 'UPDATE_EMPLOYEE_SALARY'
AND type = 'PROCEDURE'
ORDER BY line;Conclusion
Stored procedures and functions are where PL/SQL development truly begins. Moving business logic out of application layers and into the database brings significant benefits: it is easier to test, easier to secure, and shared across every application that accesses the data.
Master the basics — procedure vs function, parameter modes, proper exception handling — then graduate to packages to keep your codebase organized as it grows. The investment pays dividends every time someone needs to understand, debug, or extend the application.