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)
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)
Trending now
This is a popular solution!
Step by step
Solved in 2 steps
