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 namedSTATUS_SHIP_SPthat allows an employee in the Shipping.
Department to update anorder status to addshipping information. The BB_BASKETSTATUS
table lists events for each order so that a shopper can see thecurrent 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
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.
Executestatus_ship_sp(3,'20-FEB-12','UPS','ZW2384YXK4957')
Extracted text: bb_shopper bb_department bb_basket bb_basketstatus bb_product bb_basketitem bb_tax bb_shipping bb_productoption bb_productoptiondetail bb_productoptioncategory FIGURE 1-7 The Brewbean's database ERD