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 the status code is available in the BB_BASKETSTATUS table, not the status description. We also need Description of order status. Create a function named STATUS_DESC_SF that accepts a stage ID and returns the status 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 FUNCTION status_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;
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 the status code is available in the
BB_BASKETSTATUS table, not the status description. We also need Description of order status.
Create a function named STATUS_DESC_SF
that accepts a stage ID and returns the status 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 FUNCTION status_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
Trending now
This is a popular solution!
Step by step
Solved in 2 steps with 2 images
