CREATE TABLE account
(
account_id INTEGER NOT NULL,
type VARCHAR(20) NOT NULL,
customer_id INTEGER NOT NULL,
currency CHAR(3) NOT NULL DEFAULT 'NZD',
balance DECIMAL(11, 2) NOT NULL DEFAULT 0
);
ALTER TABLE account ADD CONSTRAINT account_pk PRIMARY KEY (account_id);
CREATE TABLE transaction
(
transaction_id BIGINT NOT NULL,
account_id INTEGER NOT NULL,
type VARCHAR(20) NOT NULL,
value DECIMAL(9, 2) NOT NULL,
ref VARCHAR(40) NOT NULL DEFAULT ''
);
ALTER TABLE transaction
ADD CONSTRAINT transaction_pk PRIMARY KEY (transaction_id)
ADD CONSTRAINT transaction_fk1 FOREIGN KEY (account_id) REFERENCES account;
CREATE SEQUENCE transaction_id AS BIGINT;
INSERT INTO account(account_id, type, customer_id, currency, balance)
VALUES
(1001, 'CHECKING', 100, DEFAULT, -51.22),
(1002, 'SAVINGS', 100, DEFAULT, 20),
(2001, 'CHECKING', 200, DEFAULT, 210.77),
(2003, 'SAVINGS', 200, DEFAULT, 4002.30),
(2004, 'SAVINGS', 200, 'GBP', 199.10),
(2005, 'CHECKING', 200, DEFAULT, 125.38);
SELECT * FROM account;
ACCOUNT_ID TYPE CUSTOMER_ID CURRENCY BALANCE
----------- -------------------- ----------- -------- -------------
1001 CHECKING 100 NZD -51.22
1002 SAVINGS 100 NZD 20.00
2001 CHECKING 200 NZD 210.77
2003 SAVINGS 200 NZD 4002.30
2004 SAVINGS 200 GBP 199.10
2005 SAVINGS 200 NZD 12524.38
CREATE PROCEDURE add_bonus()
BEGIN
DECLARE v_is_payment_pending BOOLEAN;
DECLARE v_is_complete BOOLEAN DEFAULT FALSE;
DECLARE v_this_customer_id INTEGER;
DECLARE v_customer_id INTEGER;
DECLARE v_account_id INTEGER;
DECLARE v_type VARCHAR(20);
DECLARE v_net_balance DECIMAL(13, 2);
DECLARE c1 CURSOR FOR
SELECT
customer_id, account_id, type, SUM(balance) OVER (PARTITION BY customer_id) AS net_balance
FROM
account
WHERE
currency = 'NZD'
ORDER BY
customer_id;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET v_is_complete = TRUE;
-- Iterate through the accounts by customer...
OPEN c1;
FETCH c1 INTO v_customer_id, v_account_id, v_type, v_net_balance;
WHILE NOT v_is_complete DO
-- When customer changes, calculate whether the bonus might be paid.
IF v_this_customer_id IS NULL OR v_this_customer_id != v_customer_id THEN
SET v_this_customer_id = v_customer_id;
SET v_is_payment_pending = CASE WHEN v_net_balance > 0 THEN TRUE ELSE FALSE END;
END IF;
-- If a bonus payment is pending for this customer then add it to a checking account if they have one.
IF v_is_payment_pending THEN
IF v_type = 'CHECKING' THEN
SET v_is_payment_pending = FALSE;
-- Update the balance.
UPDATE account SET balance = balance + 20 WHERE account_id = v_account_id;
-- Insert the bonus transaction.
INSERT INTO transaction(transaction_id, account_id, type, value, ref)
VALUES
(NEXT VALUE FOR transaction_id, v_account_id, 'INTEREST', 20, 'Merry Christmas');
END IF;
END IF;
FETCH c1 INTO v_customer_id, v_account_id, v_type, v_net_balance;
END WHILE;
CLOSE c1;
END@
CREATE PROCEDURE add_bonus()
BEGIN ATOMIC
DECLARE v_is_payment_pending BOOLEAN;
DECLARE v_this_customer_id INTEGER;
-- Iterate through the accounts by customer...
FOR r AS
SELECT
customer_id, account_id, type, SUM(balance) OVER (PARTITION BY customer_id) AS net_balance
FROM
account
WHERE
currency = 'NZD'
ORDER BY
customer_id
DO
-- When customer changes, calculate whether the bonus might be paid.
IF v_this_customer_id IS NULL OR v_this_customer_id != r.customer_id THEN
SET v_this_customer_id = r.customer_id;
SET v_is_payment_pending = CASE WHEN r.net_balance > 0 THEN TRUE ELSE FALSE END;
END IF;
-- If a bonus payment is pending for this customer then add it to a checking account if they have one.
IF v_is_payment_pending THEN
IF r.type = 'CHECKING' THEN
SET v_is_payment_pending = FALSE;
-- Update the balance.
UPDATE account SET balance = balance + 20 WHERE account_id = r.account_id;
-- Insert the bonus transaction.
INSERT INTO transaction(transaction_id, account_id, type, value, ref)
VALUES
(NEXT VALUE FOR transaction_id, r.account_id, 'INTEREST', 20, 'Merry Christmas');
END IF;
END IF;
END FOR;
END@
CREATE MODULE batch@
ALTER MODULE batch
ADD TYPE transaction_row AS ROW ANCHOR TO ROW OF transaction@
ALTER MODULE batch
PUBLISH PROCEDURE import_transactions(p_account_json CLOB(32 K))
BEGIN
DECLARE v_index SMALLINT DEFAULT 0;
DECLARE v_txn transaction_row;
-- Get account number.
SET v_txn.account_id = JSON_VALUE(p_account_json, '$.account_id' RETURNING INTEGER);
-- Iterate through JSON transactions...
loop:
WHILE TRUE DO
SET (v_txn.type, v_txn.value, v_txn.ref) =
(
SELECT
type, value, ref
FROM
JSON_TABLE
(
p_account_json,
'strict $'
COLUMNS
(
type VARCHAR(20) PATH '$.transactions[' || v_index || '].type',
value DECIMAL(9, 2) PATH '$.transactions[' || v_index || '].value',
ref VARCHAR(40) PATH '$.transactions[' || v_index || '].ref'
)
ERROR ON ERROR
)
);
IF v_txn.value IS NULL THEN
LEAVE loop;
END IF;
-- Update the balance.
UPDATE account SET balance = balance + v_txn.value WHERE account_id = v_txn.account_id;
-- Insert the bonus transaction.
INSERT INTO transaction(transaction_id, account_id, type, value, ref)
VALUES
(NEXT VALUE FOR transaction_id, v_txn.account_id, v_txn.type, v_txn.value, v_txn.ref);
SET v_index = v_index + 1;
END WHILE;
END@
CALL batch.import_transactions('{
"account_id": 1001,
"transactions": [
{"type": "CR", "value": 2100.00, "ref": "Salary"},
{"type": "DB", "value": 9, "ref": "Skinny top-up"}
]
}')@
ALTER MODULE batch
ADD FUNCTION get_transaction_objects(p_account_id INTEGER)
RETURNS TABLE
(
transaction_json CLOB(32 K)
)
BEGIN
FOR r AS
SELECT transaction_id, type, value, ref FROM transaction WHERE account_id = p_account_id
DO
PIPE
(
JSON_OBJECT
(
KEY 'transaction_id' VALUE r.transaction_id,
KEY 'type' VALUE r.type,
KEY 'value' VALUE r.value,
KEY 'ref' VALUE r.ref
RETURNING CLOB(32 K)
)
);
END FOR;
RETURN;
END@
ALTER MODULE batch
ADD FUNCTION get_transaction_array(p_account_id INTEGER) RETURNS CLOB(32 K)
BEGIN
RETURN JSON_ARRAY(SELECT transaction_json FROM TABLE(get_transaction_objects(p_account_id)) FORMAT JSON);
END@
ALTER MODULE batch
PUBLISH FUNCTION get_transactions(p_account_id INTEGER) RETURNS CLOB(32 K)
BEGIN
RETURN
JSON_OBJECT
(
KEY 'account_id' VALUE p_account_id,
KEY 'transactions' VALUE get_transaction_array(p_account_id)
FORMAT JSON
RETURNING CLOB(32 K)
);
END@
VALUES batch.get_transactions(1001)@
ACCOUNT_ID TYPE CUSTOMER_ID CURRENCY BALANCE
----------- -------------------- ----------- -------- -------------
1001 CHECKING 100 NZD -51.22
1002 SAVINGS 100 NZD 20.00
2001 CHECKING 200 NZD 230.77
2003 SAVINGS 200 NZD 4002.30
2004 SAVINGS 200 GBP 199.10
2005 CHECKING 200 NZD 125.38
6 record(s) selected.
db2 => select * from transaction;
TRANSACTION_ID ACCOUNT_ID TYPE VALUE REF
-------------------- ----------- -------------------- ----------- ----------------------------------------
1 2001 INTEREST 20.00 Merry Christmas