Packages

24.1 Specification and Body

CREATE OR REPLACE PACKAGE order_pkg AS
    -- Public types, constants, exceptions, subprograms
    c_max_items CONSTANT PLS_INTEGER := 50;
    e_out_of_stock EXCEPTION;
    TYPE item_rec IS RECORD (product_id NUMBER, qty NUMBER);
    TYPE item_tab IS TABLE OF item_rec;

    FUNCTION  create_order (p_customer_id NUMBER, p_items item_tab) RETURN NUMBER;
    PROCEDURE cancel_order (p_order_id NUMBER);
    FUNCTION  order_total  (p_order_id NUMBER) RETURN NUMBER;
END order_pkg;
/

CREATE OR REPLACE PACKAGE BODY order_pkg AS
    g_order_count PLS_INTEGER := 0;          -- private package state (per session)

    PROCEDURE check_stock (p_product_id NUMBER, p_qty NUMBER) IS   -- private subprogram
        v_stock NUMBER;
    BEGIN
        SELECT stock INTO v_stock FROM products WHERE product_id = p_product_id FOR UPDATE;
        IF v_stock < p_qty THEN RAISE e_out_of_stock; END IF;
    END;

    FUNCTION create_order (p_customer_id NUMBER, p_items item_tab) RETURN NUMBER IS
        v_order_id NUMBER;
    BEGIN
        INSERT INTO orders (customer_id) VALUES (p_customer_id) RETURNING order_id INTO v_order_id;
        FOR i IN 1 .. p_items.COUNT LOOP
            check_stock(p_items(i).produ
…

24.2 Package Concepts

Benefits: encapsulation, information hiding, overloading, session state, better dependency management (changing the body doesn't invalidate dependents).

Overloading: same name, different parameter signatures.

Forward declarations. Package state: ORA-04068: existing state of packages has been discarded after recompilation. PRAGMA SERIALLY_REUSABLE avoids persistent state.

Bodiless packages for constants and types.

Standard packages: STANDARD, DBMS_STANDARD.