Category : Files from Magazines
Archive   : DBMS9108.ZIP
Filename : ROTI2.AUG

Listing 2

DECLARE
CURSOR trans IS
SELECT transaction_number, account_id,
transaction_type, amount
FROM transactions
ORDER BY transaction_number
FOR UPDATE OF posted;
used NUMBER(6);
limit NUMBER(6);
purchase_too_big EXCEPTION;
payment_too_big EXCEPTION;
invalid_transaction_type EXCEPTION;
BEGIN
FOR t IN trans LOOP
BEGIN -- start an inner block
-- get credit used/limit and validate account
SELECT credit_used, credit_limit
INTO used, limit
FROM accounts
WHERE account_id = t.account_id;
-- process transaction based on type:
-- P = purchase, C = credit (payment)
IF t.transaction_type = 'P' THEN
IF t.amount > limit - used THEN
RAISE purchase_too_big;
END IF;
UPDATE accounts
SET credit_used = credit_used + t.amount
WHERE account_id = t.account_id;
ELSIF t.transaction_type = 'C' THEN
IF t.amount > used THEN
RAISE payment_too_big;
END IF;
UPDATE accounts
SET credit_used = credit_used - t.amount
WHERE account_id = t.account_id;
ELSE
RAISE invalid_transaction_type;
END IF;
-- mark transaction as posted if update succeeded
UPDATE transactions
SET posted = 'Y'
WHERE CURRENT OF trans;
-- handle error conditions
EXCEPTION
WHEN NO_DATA_FOUND THEN -- account_id not found
INSERT INTO errors
VALUES (t.transaction_number,
'Invalid account: ' || t.account_id);
WHEN purchase_too_big THEN
INSERT INTO errors
VALUES (t.transaction_number,
'Purchase too big: ' || TO_CHAR(t.amount));
WHEN payment_too_big THEN
INSERT INTO errors
VALUES (t.transaction_number,
'Payment too big: ' || TO_CHAR(t.amount));
WHEN invalid_transaction_type THEN
INSERT INTO errors
VALUES (t.transaction_number,
'Invalid type: ' || t.transaction_type);
END;
END LOOP;
COMMIT;
END;
/