~ Video database Lab & Assignment ~

In the Hollywood video store, one of the main video staffs jobs is to

help customers to find their member#s if they forgot theirs or to

create new member#s for customers if they dont have one. In order to

complete above jobs, video staffs need to enter customers information

into a procedure to complete above jobs. You are hired to create above

procedure for the video store to match the above requirements. First

you need to create a procedure called add_member for Hollywood video

store, so the staff can input customers information into add_member

procedure. Customer information includes customer first name, last

name, street, city, and phone.

After inputting customers information into add_member procedure,

customers will receive their new member# if they are currently not

Hollywood video store members. If add_member procedure can find

member# based on customers information, the procedure will pass out

member# back. Then, customers can receive their old member#s back from

a video store staff.

Please create a procedure and name it ADD_MEMBER based on the above

business requirements and descriptions.

[project requirements]

procedure name is add_member

pass in members information into add_member procedure which

includes:

first name

last name

street

city

phone

generate the customers new member# and pass it back to user (the

member# will be displayed on the screen and the member#s status

will be displayed as a new member).

return the customers existing member# and pass it back to user

(the member# will be displayed on the screen and the member#s

status will be displayed as an old member).

save your code to a text file and name it as dba220_project01.txt

PS:

Something you might need to know when creating procedure and

testing your procedure codes:

1. You need to test your procedure by calling it inside an anonymous

block.

2. You can use substitutive variable to get customer information

3. You need to check member table with a new created member record

if the customer is a new member.

[Create add_member procedure]

CREATE OR REPLACE PROCEDURE add_member( , , )

IS

BEGIN

.

EXCEPTION

.

end add_member;

/

[ Involke add_member procedure by executing an anonymous block]

SET SERVEROUTPUT ON

DECLARE

BEGIN

ADD_MEMBER( , , , );

DBMS_OUTPUT.PUT_LINE();

DBMS_OUTPUT.PUT_LINE();

END;

/