Oracle Database

-- PL/SQL Stored Procedure Example
CREATE OR REPLACE PROCEDURE process_daily_transactions(
    p_date IN DATE DEFAULT TRUNC(SYSDATE)
) AS
    v_total_amount NUMBER;
    v_transaction_count NUMBER;
BEGIN
    -- Calculate daily totals
    SELECT
        COUNT(*),
        SUM(amount)
    INTO
        v_transaction_count,
        v_total_amount
    FROM transactions
    WHERE TRUNC(transaction_date) = p_date;

    -- Insert into daily_summary
    INSERT INTO daily_summary (
        summary_date,
        transaction_count,
        total_amount
    ) VALUES (
        p_date,
        v_transaction_count,
        v_total_amount
    );

    -- Commit transaction
    COMMIT;

    DBMS_OUTPUT.PUT_LINE('Processed ' || v_transaction_count || ' transactions');
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR(-20001, 'Error processing transactions: ' || SQLERRM);
END process_daily_transactions;
/

-- Trigger for audit logging
CREATE OR REPLACE TRIGGER trg_account_audit
    AFTER UPDATE ON accounts
    FOR EACH ROW
BEGIN
    INSERT INTO audit_log (
        table_name,
        record_id,
        old_value,
        new_value,
        changed_by,
        changed_date
    ) VALUES (
        'ACCOUNTS',
        :NEW.account_id,
        :OLD.balance,
        :NEW.balance,
        USER,
        SYSDATE
    );
END;
/