ITEC 340 – Spring 2005

Homework 9

Create the following tables:

Customers

CustID	Lname	Fname	Phone
123	Smith	Jerry	555-1111
321	Jones	Mark	555-2222

Accounts

AccountNum	CustID	AccountType	Description	Balance
12	123	Checking	Primary Checking	2400.00
14	123	Savings	Christmas Club	200.00
11	321	Checking	Primary Checking	1400.00
18	321	Checking	Money Market Checking	5500.00
20	321	Savings	Passbook Savings	400.00

Transactions

TransNum	TranDate	AccountNum	Description	Increase	Decrease
1	01-JAN-2005	12	Deposit	2400.00
2	05-JAN-2005	14	Deposit	300.00
3	07-JAN-2005	14	Check		100.00
4	07-JAN-2005	11	Deposit	1400.00
5	10-JAN-2005	18	Deposit	5900.00
6	11-JAN-2005	20	Transfer	400.00
7	11-JAN-2005	18	Transfer		400.00

Create a TransNum sequence and set the next value to 8.

Write a stored procedure in PL/SQL with the following signature:

BankTransfer(FromAccount IN Number, ToAccount IN Number, Amount IN Number, Message OUT Varchar2)

When called the stored procedure should

• Check for valid account numbers and sufficient funds for the transfer, Return errors if needed
• Increase the balance of the ToAccount by Amount.
• Decrease the balance of the FromAccount by Amount
• Add a Transfer transaction for the increase (for example see TransNum 6)
• Add a Transfer transaction for the decrease (for example see TransNum 7)
• Handle this entire transaction using two-stage locking
• Commit the transaction if no problems
• Roll back the transaction if any errors

Here is a simple stored procedure you can use as a beginning point:

Create or replace procedure BankTransfer (Amount IN number, Message OUT Varchar2)

AS

BEGIN

if Amount > 100 then

Message := 'insufficient funds:';

else

Message := 'transfer complete:';

end if;

EXCEPTION

When Others then

Message := ‘transaction failed:';

END;

/

To test the procedure put the following in a .SQL file and START it:

Set ServerOutput on

Declare

v_message varchar2(20);

Begin

BankTransfer(50, v_message);

dbms_output.put_line(v_message);

BankTransfer(200, v_message);

dbms_output.put_line(v_message);

End;

/

Turn in a single script named h9.sql that creates your tables, sequence, and stored procedure. Turn this in by 5:00 on Wednesday, April 20^th. Make sure your signature is as directed above as we will use a standard test script.