use sql to answer the following question an erd also the pl/sql procedure has been providing as below shown Logon Procedure. Code: create or replace PROCEDURE member_ck_sp (p_user IN VARCHAR2, p_pass IN VARCHAR2, p_FullName OUT VARCHAR2) IS failour_msg varchar2(50) := 'INVALID USER NAME'; CURSOR MEMBER_CUR IS SELECT firstname, lastname, cookie, username, password FROM bb_shopper WHERE upper(username) = upper(p_user) AND password = p_pass; BEGIN p_FullName := failour_msg; FOR REC_CUR IN MEMBER_CUR LOOP p_FullName := rec_cur.firstname || ' ' || rec_cur.lastname; dbms_output.put_line('Welcome: ' || p_pass || 'Cookie: ' || rec_cur.cookie); END LOOP; if p_FullName = failour_msg then dbms_output.put_line(failour_msg); end if; END member_ck_sp; 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) BEGIN INSERT INTO bb_basketstatus (idstatus, idbasket, idstage, dtstage, shipper, shippingnum) VALUES (bb_status_seq.NEXTVAL, p_basketid, 3, p_date, p_shipper, p_shipnum); COMMIT; END; bb_status_seq.NEXTVAL is auto number generator bb_status_seq.NEXTVAL is created in the database already. Every time you call this sequence nextval it will generate automatic/an unique /non repeated number for Primary key for idstatus Test in SQL Developer Test the procedure with the following information: Basket # = 3 Date shipped = 20-FEB-12 Shipper = UPS Tracking # = ZW2384YXK4957 Test the result with below command SELECT * FROM bb_basketstatus; Test new entry with below statement and place screen shot here
use sql to answer the following question an erd also the pl/sql procedure has been providing as below shown
Logon Procedure.
Code:
create or replace PROCEDURE member_ck_sp (p_user IN VARCHAR2, p_pass IN VARCHAR2, p_FullName OUT VARCHAR2) IS failour_msg varchar2(50) := 'INVALID USER NAME'; CURSOR MEMBER_CUR IS SELECT firstname, lastname, cookie, username, password FROM bb_shopper WHERE upper(username) = upper(p_user) AND password = p_pass; BEGIN p_FullName := failour_msg; FOR REC_CUR IN MEMBER_CUR LOOP p_FullName := rec_cur.firstname || ' ' || rec_cur.lastname; dbms_output.put_line('Welcome: ' || p_pass || 'Cookie: ' || rec_cur.cookie); END LOOP; if p_FullName = failour_msg then dbms_output.put_line(failour_msg); end if; END member_ck_sp;
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)
BEGIN
INSERT INTO bb_basketstatus (idstatus, idbasket, idstage, dtstage,
shipper, shippingnum)
VALUES (bb_status_seq.NEXTVAL, p_basketid, 3, p_date, p_shipper,
p_shipnum);
COMMIT;
END;
bb_status_seq.NEXTVAL is auto number generator bb_status_seq.NEXTVAL is created in the
Test in SQL Developer
Test the procedure with the following information:
Basket # = 3
Date shipped = 20-FEB-12
Shipper = UPS
Tracking # = ZW2384YXK4957
Test the result with below command
SELECT *
FROM bb_basketstatus;
Test new entry with below statement and place screen shot here
GUI Work
Accept entry on your GUI for 4 different edit boxes and collect them and use them to call stored procedure in your code . There must be button which has name UpdateOrderStatus and when user press this button it should call this procedure and does its magic behind the schene.
Execute status_ship_sp(3,'20-FEB-12','UPS','ZW2384YXK4957')
Trending now
This is a popular solution!
Step by step
Solved in 2 steps with 2 images
