Write PL/SQL procedures to accomplish the following tasks. Test and show the pro
ID: 3919421 • Letter: W
Question
Write PL/SQL procedures to accomplish the following tasks. Test and show the procedures and triggers work. Capture output in spool showing the operations. For procedures, obtain the relevant value from the user in a variable name, then pass it on to the procedure and execute it. Keep the name of this user input variable distinct from the ones you used in the procedure. Make each trigger work and show how you verified it.
Write a procedure that retrieves the order date, customer number, and name based on the order number. Use I_ORDER_NUM as input parameter and place the values in the variables I_ORDER_DATE, I_CUSTOMER_NUM,I_CUSTOMER_NAME, respectively. Output the contents of I_ORDER_DATE, I_CUSTOMER_NUM, and I_CUSTOMER_NAME.
Explanation / Answer
CREATE OR REPLACE PROCEDURE Order_details
(I_ORDER_NUM IN ORDER.ORDER_NUM%TYPE) AS
I_ORDER_DATE ORDER.ORDER_DATE%TYPE;
I_CUSTOMER_NUM ORDER.CUSTOMER_NUM%TYPE;
I_CUSTOMER_NAME ORDER.CUSTOMER_NAME%TYPE;
CURSOR OrdGroup IS
SELECT ORDER.ORDER_DATE,ORDER.CUSTOMER_NUM,ORDER.CUSTOMER_NAME
FROM ORDER WHERE ORDER.ORDER_NUM=I_ORDER_NUM;
BEGIN
OPEN OrdGroup;
LOOP
FETCH OrdGroup INTO I_ORDER_DATE,I_CUSTOMER_NUM,I_CUSTOMER_NAME
EXIT WHEN OrdGroup%NOTFOUND;
DBMS_OUTPUT.PUTLINE(I_ORDER_DATE);
DBMS_OUTPUT.PUTLINE(I_CUSTOMER_NUM);
DBMS_OUTPUT.PUTLINE(I_CUSTOMER_NAME);
END LOOP;
CLOSE OrdGroup;
END;
/
--To get results
BEGIN
Order_details(123);
END;
/
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.