please use sql to answer the following question and erd and code from the last test (tesk 3) have been providing and it connects to task 4 as well the code is shown below
CREATE PROCEDURE PROD_ADD_SP
( product_name IN VARCHAR2
, description IN VARCHAR2
, image_filename IN VARCHAR2
, price IN NUMBER
, active_status IN NUMBER
) AS
BEGIN
INSERT INTO BB_PRODUCT VALUES(product_name, description, image_filename, price, active_status);
END PROD_ADD_SP;
1
EXECUTE PROD_ADD_SP.execute('Roasted Blend','Well-balancedmix of roasted beans, a medium body','roasted.jpg',9.50,1);
2
select * from BB_PRODUCT;
Task 4:
Adding Descriptions for Order Status Codes
When a shopper returns to the Web site to check an order’s status, information from the
BB_BASKETSTATUS table is displayed. However, only thestatus code is available in the
BB_BASKETSTATUS table, not the statusdescription. We also need Description of order status.
Create a function named STATUS_DESC_SF
that accepts astage ID and returns thestatus description. The descriptions for stage IDs
are listed in below chart. Test the function in a SELECT statement that retrieves all rows in the
BB_BASKETSTATUS table for basket 4 and displays the stage ID and its description.
In the GUI, you should have one edit box accept idBasket as 4 ( or 5) and then you should find stage ID for this idBasket = 4 and understand and return to GUI description.
Hint:
CREATE OR REPLACE FUNCTIONstatus_desc_sf(p_stage NUMBER)
RETURN xxxxxxxxx
IS
BEGIN
IF p_stage = 1 THEN assign this value to a local variable := 'Order submitted';
ELSIF p_stage = 2 THEN
assign this value to a local variable := 'Accepted, sent to shipping';
ELSIF p_stage = 3 THEN
assign this value to a local variable := 'Back-ordered';
ELSIF p_stage = 4 THEN
assign this value to a local variable := 'Cancelled';
ELSIF p_stage = 5 THEN
assign this value to a local variable := 'Shipped';
END IF;
RETURN that local variable back to GUI page;
END;
Hint: You can test your function in SQL developer with below SELECT
SELECT dtStage, status_desc_sf(idStage)
FROM bb_basketstatus
WHERE idBasket =4;
Also create a GUI to do same thing from Front END
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