use sql to answer the following question
Task 1: Creating a Logon Procedure
The home page of the Brewbean’s Web site has an option for members to log on with their IDs
and passwords. Develop a procedure named
MEMBER_CK_SP
that accepts the user ID and password
as inputs, checks whether they match a
valid logon, and returns the member first name+lastname and cookie
value.
The name should be returned as a single text string containing the first and last name.
The head developer wants the number of parameters minimized so that the same
parameter is used to accept the password and return the name value.
(passwd Paramater as IN OUT use passwd as INPUT firstname||lastname as OUTPUT)
Also, if the user doesn’t enter a valid username and password, return the value
INVALID
in a parameter named
p_check.
Test the procedure using a valid logon first, with the username
rat55
and password
kile. Then try it with an invalid logon by changing the username to
rat and kile999
password.
Hint:
This procedure will have 4 parameters
p_userid IN
p_passwd IN OUT
p_cookie OUT
p_check OUT
You will select and return two columns/ information from bb_shopper table in this procedure body.
Task2 : Updating Order Status
Create a procedure named
STATUS_SHIP_SP
that allows an employee in the Shipping.
Department to update an
order status
to add
shipping information.
The BB_BASKETSTATUS
table lists events for each order so that a shopper can see the
current status,
date, and
comments as each stage
of the order process are finished.
The IDSTAGE column of the BB_BASKETSTATUS table identifies each stage; the value 3 in this column indicates that an
order has been shipped.
The procedure should allow adding a row with an IDSTAGE of 3, date shipped, tracking
number, and shipper. The BB_STATUS_SEQ sequence is used to provide a value for the primary
key column.
HINT: In your Procedure main part (BEGIN _END)