Chapter 34: Exercises — Stored Procedures, UDFs, and Application Logic in the Database

Exercise 34.1: SQL PL Fundamentals

Objective: Build a stored procedure using compound statements, control flow, and variables.

Write a stored procedure MERIDIAN.PROC_CATEGORIZE_ACCOUNT that: 1. Accepts an ACCOUNT_ID as input. 2. Reads the account balance, type, and age (days since opening). 3. Assigns a tier based on these rules: - PLATINUM: balance >= $100,000 OR account age > 3,650 days with balance >= $25,000 - GOLD: balance >= $25,000 OR account age > 1,825 days with balance >= $10,000 - SILVER: balance >= $5,000 - BRONZE: all others 4. Updates the account's TIER column. 5. Returns the assigned tier and a descriptive message as OUT parameters.

Stretch goal: Add a p_dry_run IN parameter (SMALLINT). When set to 1, return the tier without updating the table.

Exercise 34.2: Error Handling and Diagnostics

Objective: Build robust error handling using DECLARE HANDLER, SIGNAL, and GET DIAGNOSTICS.

Create a procedure MERIDIAN.PROC_CLOSE_ACCOUNT that: 1. Validates the account exists and is ACTIVE. 2. Checks that the balance is zero (if not, return an error with the current balance). 3. Checks that no pending transactions exist for the account. 4. Checks that no active loans reference this account. 5. Sets the account status to 'CLOSED' and records the closure date. 6. Logs the closure in the AUDIT_LOG table. 7. Uses named conditions for each validation failure. 8. Uses GET DIAGNOSTICS to capture and log any unexpected SQL errors. 9. Returns a status code and message for every possible outcome.

Test your procedure by calling it with: - A valid, zero-balance account (expect success) - An account with a positive balance (expect rejection) - A non-existent account ID (expect "not found") - An already-closed account (expect rejection)

Exercise 34.3: Cursor Handling and Result Sets

Objective: Master cursor-based processing and result set return.

Write a procedure MERIDIAN.PROC_BRANCH_REPORT that: 1. Accepts a BRANCH_ID and a REPORT_DATE (DATE). 2. Returns three result sets: - Account Summary: Count and total balance by account type for the branch. - Top 10 Transactions: The ten largest transactions on the report date at that branch. - Alert Accounts: Accounts with balance below their type's minimum requirement. 3. Uses a cursor with FOR UPDATE to mark alert accounts with a flag column.

Exercise 34.4: Dynamic SQL Search

Objective: Build a flexible search procedure using dynamic SQL.

Create MERIDIAN.PROC_ADVANCED_SEARCH that accepts all-optional parameters: - p_customer_name (partial match on last name) - p_account_type (exact match) - p_min_balance / p_max_balance (range) - p_status (exact match) - p_branch_id (exact match) - p_opened_after / p_opened_before (date range) - p_sort_column (one of: 'BALANCE', 'LAST_NAME', 'OPENED_DATE') - p_sort_direction (ASC or DESC) - p_page_number and p_page_size

The procedure must: 1. Construct the SQL dynamically, including only WHERE clauses for non-null parameters. 2. Validate p_sort_column and p_sort_direction against allowed values (prevent SQL injection). 3. Implement pagination using OFFSET/FETCH. 4. Return the result set and a total count as an OUT parameter.

Exercise 34.5: Scalar UDF Library

Objective: Create a library of scalar UDFs for financial calculations.

Implement the following functions: 1. FN_SIMPLE_INTEREST(principal, rate, years) — returns simple interest amount. 2. FN_PRESENT_VALUE(future_value, rate, periods) — returns present value. 3. FN_DAYS_UNTIL_MATURITY(maturity_date) — returns business days until a given date. 4. FN_RISK_SCORE(balance, overdrafts, account_age_days) — returns a 0-100 risk score based on weighted factors. 5. FN_FORMAT_PHONE(phone_number) — standardizes phone numbers to (XXX) XXX-XXXX format.

For each function, specify whether it should be DETERMINISTIC or NOT DETERMINISTIC, and whether it should use RETURNS NULL ON NULL INPUT or CALLED ON NULL INPUT.

Exercise 34.6: Table UDF

Objective: Create a table function that generates derived data.

Write MERIDIAN.FN_DAILY_CASH_FLOW that accepts: - p_branch_id INTEGER - p_start_date DATE - p_end_date DATE

And returns a table with columns: - FLOW_DATE (DATE) - TOTAL_DEPOSITS (DECIMAL) - TOTAL_WITHDRAWALS (DECIMAL) - NET_FLOW (DECIMAL) - CUMULATIVE_NET (DECIMAL — running total of NET_FLOW) - TRANSACTION_COUNT (INTEGER)

Use a recursive CTE to generate the date range, and LEFT JOIN transaction data for each date.

Exercise 34.7: User-Defined Types

Objective: Practice domain modeling with distinct types.

1. Create distinct types for: LOAN_ID_TYPE (CHAR(12)), BRANCH_CODE (CHAR(5)), PHONE_NUM (VARCHAR(15)).
2. Create sourced functions for any needed aggregations.
3. Create a sample table that uses all three types.
4. Write INSERT statements and verify that DB2 rejects assignments from incompatible types.
5. Write a function that accepts PHONE_NUM and returns a formatted string.

Exercise 34.8: Complete Procedure with Testing

Objective: Build a production-quality procedure with a corresponding test procedure.

Create MERIDIAN.PROC_APPLY_INTEREST that: 1. Accepts a p_account_id and p_period ('DAILY', 'MONTHLY', 'QUARTERLY'). 2. Looks up the account's interest rate from a rate table. 3. Calculates interest based on the period and current balance. 4. Credits the interest to the account. 5. Records the interest payment as a transaction. 6. Returns the interest amount and new balance.

Then create MERIDIAN.TEST_PROC_APPLY_INTEREST that: 1. Creates test accounts with known balances and rates. 2. Tests daily, monthly, and quarterly interest calculations. 3. Verifies the calculated amounts against expected values (with tolerance for rounding). 4. Tests edge cases: zero balance, negative rate (should reject), closed account. 5. Uses savepoints to clean up test data. 6. Reports pass/fail for each test case to the TEST_RESULTS table.

Exercise 34.9: External Java Procedure

Objective: Create a Java stored procedure that leverages Java libraries.

Write a Java stored procedure MERIDIAN.PROC_VALIDATE_IBAN that: 1. Accepts an IBAN (International Bank Account Number) string. 2. Validates the format using Java regex. 3. Performs the MOD-97 checksum validation. 4. Returns whether the IBAN is valid, the country code, and the check digits. 5. Logs validation attempts to a DB2 table.

Provide both the Java source code and the CREATE PROCEDURE DDL.

Exercise 34.10: Version Management Practice

Objective: Design a versioning and deployment strategy.

For the procedures created in this chapter: 1. Create a directory structure suitable for version control. 2. Write a deploy_v1.sql script that creates all objects in dependency order. 3. Write a rollback_v1.sql script that drops all objects in reverse dependency order. 4. Simulate a V2 change: modify PROC_TRANSFER_FUNDS to accept an optional currency parameter. Create a backward-compatible wrapper. 5. Write a deploy_v2.sql script and a rollback_v2.sql script. 6. Document the deployment process in comments within the scripts.