System Procedures

The assignment provides a set of procedures in Oracle PL/SQL that perform various database operations. The procedures include: (1) Adding a new account for an existing customer, which verifies the customer ID and account type before creating the new account; (2) Displaying the sum of balances by state; (3) Updating a customer's address; and (4) Displaying transaction history with amounts greater than a specified amount. The procedures also include error handling to return messages in case of failure or invalid input.

Faisal Jahdali
Second Draft
BANKING SCENARIO PROCEDURES
Faisal Jahdlai
08 December, 2016

Contents
ER DIAGRAM...........................................................................................................................2
CREATE AND INSERT TABLE COMMANDS....................................................................2
SQL PROCEDURES.................................................................................................................3
1 | P a g e

ER DIAGRAM
CREATE AND INSERT TABLE COMMANDS
CREATE SEQUENCE c1
MINVALUE 1
MAXVALUE 999999
START WITH 1
INCREMENT BY 1
CACHE 20;
2 | P a g e
Account
Acct_ID {PK}
Cust_ID {FK}
Acct_Num
Balance
Customer
Cust_ID {PK}
Cust_Type
Last_Name
First_Name
Street
City
State
ZipCode
Sex
Trans_ID {PK}
Acct_ID {FK}
Amount
Date
Trans_Type
Transaction
1,1
0,*
Has
0,* 1,1Has

CREATE SEQUENCE T1
MINVALUE 1
MAXVALUE 999999
START WITH 1
INCREMENT BY 1
CACHE 20;
3 | P a g e

create table customer
(
cust_id int primary key,
cust_type varchar2(20),
fname varchar2(20),
lname varchar2(20),
street varchar2(30),
city varchar2(30),
zip number(6,0),
state varchar2(30),
gender char(1),
constraint chk_1 check (cust_type IN ('small business', 'large business', 'personal')),
constraint chk_2 check (gender IN ('M', 'F'))
);
4 | P a g e

INSERT INTO CUSTOMER VALUES (C1.NEXTVAL, 'small business', 'JIA', 'STEVEN',
'21 PARK STREET', 'ASPEN', 889988, 'CA', 'F');
INSERT INTO CUSTOMER VALUES (C1.NEXTVAL, 'large business', 'DIA', 'STONES',
'212 PARK VIEW ROAD', 'ASPEN', 889908, 'CA', 'F');
INSERT INTO CUSTOMER VALUES (C1.NEXTVAL, 'personal', 'LIA', 'DAVID', '41
GREEN PARK STREET', 'ASPEN', 889918, 'CA', 'F');
create table account
( acct_id int primary key,
cust_id int,
acct_type varchar2(20) not null,
acct_num number(16,0)unique ,
balance number(10,2),
constraint fk_1 foreign key(cust_id) references customer(cust_id),
constraint chk22 CHECK (acct_type IN ('savings', 'checking' , 'business'))
);
5 | P a g e

INSERT INTO ACCOUNT VALUES (1, 1, 'savings', 567890000007890, 1500.90);
INSERT INTO ACCOUNT VALUES (2, 2, 'savings', 567890000009990, 2000.90);
INSERT INTO ACCOUNT VALUES (3, 3, 'checking', 567890000001090, 15000.90);
create table transaction
(
transc_id int primary key,
acct_id int,
amount number(10,2),
trans_date date,
trans_type varchar(20),
constraint fk2 foreign key(acct_id) references account(acct_id),
constraint chk_t1 CHECK (trans_type IN ('deposit', 'withdrawal', 'transfer'))
);
6 | P a g e

INSERT INTO TRANSACTION VALUES (T1.NEXTVAL, 1, 678.00, '10-11-2015',
'deposit');
INSERT INTO TRANSACTION VALUES (T1.NEXTVAL, 2, 118.00, '11-11-2015',
'withdrawal');
INSERT INTO TRANSACTION VALUES (T1.NEXTVAL, 3, 500.00, '12-11-2015',
'deposit');
SQL PROCEDURES
1. (Using a procedure) System should be able to add new customers ensuring that no duplicates
are entered, and type is valid. A message should be returned with the success or failure of the
action. (
Answer:
create or replace PROCEDURE add_new_customer
(
input_type customer.cust_type%type,
input_fname CUSTOMER.fname%type,
input_lname CUSTOMER.lname%type,
input_addr CUSTOMER.STREET%type,
input_city CUSTOMER.CITY%type,
input_zip CUSTOMER.ZIP%type,
input_state CUSTOMER.STATE%type,
7 | P a g e

input_gender CUSTOMER.GENDER%type
)
IS
record_count NUMBER;
duplicate_exception EXCEPTION;
invalid_type_exception EXCEPTION;
CURSOR customer_exists IS
SELECT COUNT (*)
FROM customer
WHERE UPPER (input_fname) = UPPER(fname)
AND UPPER(input_lname) = UPPER(lname)
AND UPPER(input_addr) = UPPER(street)
AND UPPER(input_city) = UPPER(CITY)
AND input_zip = ZIP
AND UPPER (input_state) = UPPER(STATE)
AND UPPER (input_gender) = UPPER(GENDER)
AND lower(input_type) = cust_type;
BEGIN
IF (input_type not in ('small business', 'large business', 'personal') and input_type is not null) THEN
raise invalid_type_exception;
END IF;
record_count := 0;
OPEN customer_exists;
FETCH customer_exists INTO record_count;
CLOSE customer_exists;
IF record_count > 0 THEN
RAISE duplicate_exception;
END IF;
INSERT INTO customer
(CUST_ID,cust_type, FNAME, LNAME, STREET, CITY, ZIP, STATE, GENDER)
VALUES
(c1.nextval,input_type, input_fname,
input_lname,input_addr,input_city,input_zip,input_state,input_gender);
COMMIT;
dbms_output.put_line ('New Customer inserted');
EXCEPTION
WHEN duplicate_exception THEN
dbms_output.put_line ('Customer exists already. Please try again');
WHEN invalid_type_exception THEN
dbms_output.put_line ('Invalid type entered. Please try again');
8 | P a g e

WHEN OTHERS THEN
dbms_output.put_line ('Transaction Failed, customer not inserted ');
END add_new_customer;
Test Result
It does shows me the values has been inserted, but I don’t get the
messages that the procedure should give it to me in returned .
9 | P a g e

1. (Using a procedure) System should be able to add a new account for an existing
customer. Given a customer ID, and an account type, the system should verify that
the customer id, and account type are valid, then create a new account. A message
should be returned with the success or failure of the action.
create or replace PROCEDURE add_new_account
(
input_cid account.cust_id%type,
input_actype account.acct_type%type,
input_acctid account.acct_id%type,
input_num account.acct_num%type,
input_bal account.balance%type
)
IS
not_found_exception EXCEPTION;
invalid_actype_exception EXCEPTION;
cnumber number;
cursor c1 is
SELECT cust_id
FROM customer
WHERE cust_id = input_cid;
BEGIN
10 | P a g e

IF (input_actype not in ('savings', 'checking', 'business') and input_actype is not null) THEN
raise invalid_actype_exception;
END IF;
open c1;
FETCH c1 into cnumber;
if c1%notfound then
raise not_found_exception;
END IF;
INSERT INTO account (acct_id , cust_id, acct_type, acct_num, balance) VALUES
(input_acctid , input_cid, input_actype, input_num, input_bal);
COMMIT;
dbms_output.put_line ('New account inserted');
EXCEPTION
WHEN not_found_exception THEN
dbms_output.put_line ('Customer data not found. Please try again');
WHEN invalid_actype_exception THEN
dbms_output.put_line ('Invalid account type entered. Please try again');
WHEN OTHERS THEN
dbms_output.put_line ('Transaction Failed, data not inserted ');
END add_new_account;
RESULTE
It does shows me the values has been inserted, but I don’t get the
messages that the procedure should give it to me in returned .
11 | P a g e

2. (Using a procedure) Given a state, display the sum of the balances by state.
create or replace PROCEDURE state_balance
(
input_states customer.state%type
)
IS
not_found_exception EXCEPTION;
cstate varchar2(30);
balances number(10,2);
cursor c1 is
SELECT state
FROM customer
WHERE state= input_states;
12 | P a g e

BEGIN
open c1;
fetch c1 into cstate;
if c1%notfound then
raise not_found_exception;
else
select sum(a.balance) into balances from account a, customer c where c.cust_id = a.cust_id
and c.state = cstate;
COMMIT;
dbms_output.put_line ('the balance is: '||balances);
END IF;
EXCEPTION
WHEN not_found_exception THEN
dbms_output.put_line ('state not found. Please try again');
WHEN OTHERS THEN
dbms_output.put_line ('Transaction Failed, enter a valid state');
END state_balance;
RESULT..
It does shows me the values has been inserted, but I don’t get the
messages that the procedure should give it to me in returned .
13 | P a g e

3. (Using a procedure) Given a customer id and a new address, update the customer’s
address. A message should be returned with the success or failure of the action.
CREATE OR REPLACE PROCEDURE create or replace PROCEDURE
address_update
(
input_id customer.cust_id%type,
input_addr CUSTOMER.STREET%type,
input_city CUSTOMER.CITY%type,
input_zip CUSTOMER.ZIP%type,
input_state CUSTOMER.STATE%type
)
IS
cid int;
not_found_cust_exception EXCEPTION;
14 | P a g e

CURSOR customer_exists IS
SELECT cust_id
FROM customer
WHERE cust_id = input_id;
BEGIN
OPEN customer_exists;
FETCH customer_exists INTO cid;
IF customer_exists%notfound THEN
RAISE not_found_cust_exception;
END IF;
update customer set street = input_addr, city = input_city,zip = input_zip, state =
input_state where cust_id = cid;
COMMIT;
dbms_output.put_line ('New Customer address updated');
EXCEPTION
WHEN not_found_cust_exception THEN
dbms_output.put_line ('data not found for update. Please try again');
WHEN OTHERS THEN
dbms_output.put_line ('Transaction Failed, customer not found ');
END address_update;
execute ADDRESS_UPDATE ('1','washington','towosn','21204','md');
The address has been changed, but no result message I got .
15 | P a g e

4. (Using a procedure) Given an amount, display the account number, last name, and
amount of all transactions with an amount greater than the one passed into the
procedure.
create or replace PROCEDURE transac_history
(
input_amt transaction.amount%type
)
IS
acnum account.acct_num%type;
lastname customer.lname%type;
amts transaction.amount%type;
cursor c1 is
SELECT a.acct_num, c.lname, t.amount from account a, customer c , transaction t where
a.cust_id = c.cust_id and t.acct_id = a.acct_id and t.amount > = input_amt;
BEGIN
open c1;
loop
fetch c1 into acnum, lastname, amts;
dbms_output.put_line ('account number: ' ||acnum);
dbms_output.put_line ('last name : ' ||lastname);
dbms_output.put_line ('amount: ' ||amts);
end loop;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line ('Transaction Failed, data not inserted ');
END transac_history;
16 | P a g e

5. [EXTRA CREDIT]: Write a trigger so that anytime a customer changes their address,
the old address, new address, date, customer id and user ID are stored in a log.
create or replace trigger customer_update
AFTER UPDATE
ON CUSTOMER
FOR EACH ROW
BEGIN
insert into cust_log
(cust_id , ostreet, ocity, ozip, ostate, nstreet, ncity, nzip , nstate , udate)
values
('0497196', 'washingtona ave', 'Towson', '21204', 'MD', 'Burke ave', 'Towosn', '21286', 'MD',
SYSDATE);
end;
17 | P a g e

18 | P a g e