Write PL/SQL or T-SQL procedures to accomplish the following tasks: a. Obtain th

Question

Write PL/SQL or T-SQL procedures to accomplish the following tasks: a. Obtain the name and credit limit of the customer whose number currently is stored in I_CUSTOMER_NUM. Place these values in the variables I_CUSTOMER_NAME and I_CREDIT_LIMIT, respectively. Output the contents of I_CUSTOMER_NAME and I_CREDIT_LIMIT. b. Obtain the order date, customer number, and name for the order whose number currently is stored in I_ORDER_NUM. Place these values in the variables I_ORDER_DATE, I_CUSTOMER_NUM and I_CUSTOMER_NAME, respectively. Output the contents of I_ORDER_DATE, I_CUSTOMER_NUM, and I_CUSTOMER_NAME. c. Add a row to the ORDERS table. SQL Functions and Procedures 269 d. Change the date of the order whose number is stored in I_ORDER_NUM to the date currently found in I_ORDER_DATE. e. Delete the order whose number is stored in I_ORDER_NUM

Explanation / Answer

a) CREATE PROCEDURE sCustDetails @I_CUSTOMER_NUM int AS BEGIN DECLARE @I_CUSTOMER_NAME varchar(20) DECLARE @I_CREDIT_LIMIT varchar(20) SET @I_CUSTOMER_NAME= SELECT FIRST_NAME FROM CUSTOMER WHERE CUSTOMER_NUM =@I_CUSTOMER_NUM SET @I_CREDIT_LIMIT =SELECT LAST_NAME FROM CUSTOMER WHERE CUSTOMER_NUM=@I_CUSTOMER_NUM SELECT @I_CUSTOMER_NAME,@I_CREDIT_LIMIT END GO Sample Test Case: exec sCustDetails 1234 b) CREATE PROCEDURE sOrderDetails @I_ORDER_NUM int AS BEGIN DECLARE @I_ORDER_DATE varchar(20) DECLARE @I_CUSTOMER_NUM varchar(20) DECLARE @I_CUSTOMER_NAME int SET @I_ORDER_DATE = SELECT ORDER_DATE FROM ORDERS WHERE CUSTOMER_NUM =@I_ORDER_NUM SET @I_CUSTOMER_NUM = SELECT CUSTOMER_NUM FROM ORDERS WHERE CUSTOMER_NUM =@I_ORDER_NUM SET @I_CUSTOMER_NAME= SELECT CUSTOMER_NAME FROM ORDERS WHERE CUSTOMER_NUM =@I_ORDER_NUM SELECT @I_ORDER_DATE,@I_CUSTOMER_NUM,@I_CUSTOMER_NAME END GO Sample Test Case: exec sOrderDetails 1234 c) CREATE PROCEDURE sInsertOrders As BEGIN INSERT INTO ORDERS VALUES(5,'11/12/2012',12,'DEO') END d) UPDATE ORDERS SET ORDER_DATE=@I_ORDER_DATE WHERE ORDER_NUM=@I_ORDER_NUM e) DELETE FROM ORDERS WHERE ORDER_NUM=@I_ORDER_NUM

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.