Part 157: Oracle SQL & PL/SQL: Packages & Fast Materialized Views

Enterprise capabilities in Oracle Database: modular code, isolated transactions, and incremental view refreshes.

1. PL/SQL Packages: Specification vs Body

Oracle encapsulates business logic using modular packages: - Package Specification: The public interface (constants, types, cursor signatures, procedure headers). - Package Body: The private implementation code.

-- 1. Specification (Header)
CREATE OR REPLACE PACKAGE finance_pkg AS
    FUNCTION get_tax_rate(p_country_id VARCHAR2) RETURN NUMBER;
    PROCEDURE apply_discount(p_order_id NUMBER, p_percent NUMBER);
END finance_pkg;
/

-- 2. Package Body (Implementation)
CREATE OR REPLACE PACKAGE BODY finance_pkg AS
    FUNCTION get_tax_rate(p_country_id VARCHAR2) RETURN NUMBER IS
    BEGIN
        IF p_country_id = 'US' THEN RETURN 0.0825;
        ELSE RETURN 0.15;
        END IF;
    END get_tax_rate;

    PROCEDURE apply_discount(p_order_id NUMBER, p_percent NUMBER) IS
    BEGIN
        UPDATE orders SET total = total * (1 - p_percent / 100) WHERE id = p_order_id;
    END apply_discount;
END finance_pkg;
/

2. Autonomous Transactions (PRAGMA AUTONOMOUS_TRANSACTION)

Allows a stored procedure to commit or rollback independently of the caller's transaction. Indispensable for audit and error logging:

CREATE OR REPLACE PROCEDURE log_error(p_msg VARCHAR2) IS
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    INSERT INTO app_error_log (msg, logged_at) VALUES (p_msg, SYSTIMESTAMP);
    COMMIT; -- Commits the error log even if caller rolls back!
END;
/

3. Fast Materialized Views with MView Logs

Oracle can refresh a materialized view incrementally using change logs:

-- Create log on base table
CREATE MATERIALIZED VIEW LOG ON sales WITH ROWID, SEQUENCE (amount) INCLUDING NEW VALUES;

-- Create Fast Refresh Materialized View
CREATE MATERIALIZED VIEW mv_daily_sales
REFRESH FAST ON COMMIT AS
SELECT trunc(sale_time) AS sale_day, COUNT(*) AS cnt, SUM(amount) AS total_revenue
FROM sales
GROUP BY trunc(sale_time);

Key Takeaways

• Packages compile specifications separately from implementations, reducing dependency invalidations.
• Fast materialized views compute delta updates in milliseconds upon base commit.

In Part 158, we explore SQLite Deep Dive: WAL Mode, In-Memory DBs & PRAGMAs!