PL/SQL Procedures & Functions

To follow along with the examples in this lesson, use the course dataset from Lesson 8.

Procedures vs Functions

An anonymous PL/SQL block runs once and disappears. A stored procedure or stored function is a named program unit saved permanently in the database. You write it once and call it by name from any SQL statement, another PL/SQL block, or an application. The key difference between the two is simple: a function always returns a value and can be used inside a SQL expression. A procedure does not return a value directly — it performs an action and communicates results through output parameters or by modifying database state.

• Both procedures and functions are stored in the database and compiled when created
• A function must contain a RETURN statement and declare a return type
• A procedure uses OUT or IN OUT parameters to pass results back to the caller
• Functions can be called from SQL — SELECT my_function(id) FROM table
• Procedures cannot be called from SQL — they are called with EXECUTE or from a PL/SQL block
• Both are stored under the current schema — query USER_PROCEDURES to list them

Creating and Calling a Procedure

A procedure is created with CREATE OR REPLACE PROCEDURE. Parameters are declared after the procedure name in parentheses with a direction — IN (input, the default), OUT (output), or IN OUT (both). The body is a standard PL/SQL block starting with IS or AS — both are interchangeable. Call a procedure with EXECUTE in SQL Developer, or from inside another PL/SQL block directly by name.

-- Create a procedure that applies a price increase to all products in a category.
-- p_category: IN parameter -- the caller passes the category name in
-- p_increase_pct: IN parameter -- percentage to increase by
-- p_rows_updated: OUT parameter -- procedure passes the row count back out
CREATE OR REPLACE PROCEDURE dataplexa_store.update_category_price (
    p_category      IN  dataplexa_store.products.category%TYPE,
    p_increase_pct  IN  NUMBER,
    p_rows_updated  OUT NUMBER
)
IS
BEGIN
    UPDATE dataplexa_store.products
    SET    price = price * (1 + p_increase_pct / 100)
    WHERE  category = p_category;
    p_rows_updated := SQL%ROWCOUNT;   -- SQL%ROWCOUNT holds the number of rows affected
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END update_category_price;
/
-- Call the procedure from an anonymous block
DECLARE
    v_rows NUMBER;
BEGIN
    dataplexa_store.update_category_price(
        p_category     => 'Electronics',
        p_increase_pct => 5,
        p_rows_updated => v_rows
    );
    DBMS_OUTPUT.PUT_LINE('Rows updated: ' || v_rows);
END;
/

• IS and AS are interchangeable in procedure and function headers — both introduce the declaration section
• SQL%ROWCOUNT is a cursor attribute that holds the number of rows affected by the most recent DML statement
• Named parameter notation (p_category => 'Electronics') makes calls self-documenting and order-independent — positional notation also works
• COMMIT and ROLLBACK inside a procedure control the transaction — if an error occurs the EXCEPTION handler rolls back before exiting

Creating and Calling a Function

A function is created with CREATE OR REPLACE FUNCTION. It must declare a return type after the parameter list and must execute a RETURN statement in the body. Functions can be called from SQL expressions — this is what makes them more flexible than procedures for calculations and lookups. A function that queries the database must either be deterministic (same inputs always produce the same output) or marked appropriately if used in SQL queries.

-- Create a function that returns the total amount spent by a customer.
-- The RETURN type is declared after the parameter list.
CREATE OR REPLACE FUNCTION dataplexa_store.get_customer_total (
    p_customer_id IN dataplexa_store.customers.customer_id%TYPE
)
RETURN NUMBER
IS
    v_total NUMBER := 0;
BEGIN
    SELECT NVL(SUM(total_amount), 0)
    INTO   v_total
    FROM   dataplexa_store.orders
    WHERE  customer_id = p_customer_id;
    RETURN v_total;   -- function must always reach a RETURN statement
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN 0;
    WHEN OTHERS THEN
        RETURN -1;    -- signal an error to the caller
END get_customer_total;
/
-- Call the function from SQL -- functions can be used in SELECT
SELECT
    c.customer_id,
    c.first_name || ' ' || c.last_name                            AS customer_name,
    dataplexa_store.get_customer_total(c.customer_id)             AS total_spent
FROM   dataplexa_store.customers c
ORDER  BY total_spent DESC
FETCH FIRST 5 ROWS ONLY;

• The function is called directly inside the SELECT list — get_customer_total(c.customer_id) — Oracle calls it once per row
• A function called from SQL cannot perform DML (INSERT, UPDATE, DELETE) or issue COMMIT/ROLLBACK — doing so raises ORA-14551
• NVL(SUM(...), 0) handles customers with no orders — SUM of zero rows returns NULL, NVL converts it to 0
• Every code path in a function must reach a RETURN statement — a function that exits without RETURN raises ORA-06503

IN, OUT, and IN OUT Parameters

Parameters control how data flows between the caller and the procedure or function. IN parameters carry values in — they are read-only inside the subprogram. OUT parameters carry values out — they are write-only and start as NULL inside the subprogram. IN OUT parameters carry values in both directions — the caller passes a value in, the subprogram can read and modify it, and the modified value is returned to the caller.

-- A procedure with IN, OUT, and IN OUT parameters.
-- Validates a discount percentage and applies it to a price.
CREATE OR REPLACE PROCEDURE dataplexa_store.apply_discount (
    p_original_price  IN     NUMBER,        -- read-only input
    p_discount_pct    IN OUT NUMBER,        -- read and write: capped at 50 if too high
    p_final_price     OUT    NUMBER         -- write-only output
)
IS
BEGIN
    -- Cap the discount at 50% -- modifies the IN OUT parameter
    IF p_discount_pct > 50 THEN
        p_discount_pct := 50;
    END IF;
    p_final_price := p_original_price * (1 - p_discount_pct / 100);
END apply_discount;
/
-- Test the procedure
DECLARE
    v_discount   NUMBER := 70;    -- intentionally too high
    v_final      NUMBER;
BEGIN
    dataplexa_store.apply_discount(
        p_original_price => 299.99,
        p_discount_pct   => v_discount,   -- passed IN OUT -- will be modified
        p_final_price    => v_final
    );
    DBMS_OUTPUT.PUT_LINE('Capped discount: ' || v_discount || '%');
    DBMS_OUTPUT.PUT_LINE('Final price:     ' || v_final);
END;
/

• v_discount was 70 when passed in — the procedure capped it to 50 and the caller sees 50 after the call because it was IN OUT
• An IN parameter cannot be assigned inside the subprogram — attempting to do so raises a compilation error
• OUT parameters arrive as NULL inside the subprogram regardless of what value the caller passed — if you need the original value, use IN OUT instead

Viewing and Dropping Procedures and Functions

Stored procedures and functions are part of the database schema. Use USER_PROCEDURES to list them and USER_SOURCE to view their source code. Drop them with DROP PROCEDURE or DROP FUNCTION. If a procedure or function is invalid — because a table it references was dropped or altered — Oracle marks it as INVALID and it must be recompiled before it can be called.

-- List all stored procedures and functions in the current schema
SELECT object_name, object_type, status
FROM   user_objects
WHERE  object_type IN ('PROCEDURE', 'FUNCTION')
ORDER  BY object_type, object_name;
-- View the source code of a procedure
SELECT line, text
FROM   user_source
WHERE  name = 'UPDATE_CATEGORY_PRICE'   -- object names are stored in uppercase
AND    type = 'PROCEDURE'
ORDER  BY line;
-- Drop a procedure and a function
DROP PROCEDURE dataplexa_store.update_category_price;
DROP FUNCTION  dataplexa_store.get_customer_total;
-- Recompile an invalid procedure
ALTER PROCEDURE dataplexa_store.update_category_price COMPILE;

• Object names in USER_SOURCE and USER_OBJECTS are stored in uppercase — queries must match exactly
• A VALID status means the object compiled successfully and is ready to run — INVALID means it needs recompilation
• ALTER PROCEDURE name COMPILE recompiles a procedure without dropping and recreating it — faster than recreating when only dependencies changed
• CREATE OR REPLACE is the preferred way to update a procedure — it preserves any grants on the object

Lesson Summary

Practice Questions

Quiz

Next up — Triggers — How to create PL/SQL triggers that fire automatically on INSERT, UPDATE, or DELETE, and how row-level and statement-level triggers differ.