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_USTOMEFL_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.

Explanation / Answer

a.
DELIMITER //
DROP PROCEDURE IF EXISTS db.procedure1 CREATE PROCEDURE db.procedure1 ()

declare

I_CUSTOMER_NAME varchar2(10);
CREDIT_LIMIT number(6);
I_CUSTOMER_NUM number (6) = 123456;

begin

select NAME into I_CUSTOMER_NAME, CREDIT_LIMIT into I_CREDIT_LIMIT where CUSTOMER_NUM = I_CUSTOMER_NUM;
dbms_output.Put_line(I_CUSTOMER_NAME);
dbms_output.Put_line(I_CREDIT_LIMIT);

end//
DELIMITER ;

b)
DELIMITER //
DROP PROCEDURE IF EXISTS db.procedure2 CREATE PROCEDURE db.procedure2 ()

declare

I_ORDER_DATE date;

I_CUSTOMER_NUM number (6);

I_CUSOMTER_NAME varchar2(10);

I_ORDER_NUM number(6) = 123456;

begin
select order_date into I_ORDER_DATE, customer_number into I_CUSTOMER_NUM, name INTO I_CUSOMTER_NAME from ORDERS whose order_num = I_ORDER_NUM;
dbms_output.Put_line(I_ORDER_DATE);
dbms_output.Put_line(I_CUSTOMER_NUM);
dbms_output.Put_line(I_CUSOMTER_NAME);

end//
DELIMITER ;

c)

DELIMITER //
DROP PROCEDURE IF EXISTS db.procedure3 CREATE PROCEDURE db.procedure3 ()

begin

INSERT INTO ORDERS_TABLE (column1, column2, ... column_n ) VALUES (expression1, expression2, ... expression_n );

end//
DELIMITER ;

d.
DELIMITER //
DROP PROCEDURE IF EXISTS db.procedure4 CREATE PROCEDURE db.procedure4 ()
declare
I_ORDER_DATE date = TRUNC(ADD_MONTHS(SYSDATE, -1));
I_ORDER_NUM number(6) = 123456;

begin
update ORDERS set order_date = I_ORDER_DATE where order_num = I_ORDER_NUM;
end//
DELIMITER

e.
DELIMITER //
DROP PROCEDURE IF EXISTS db.procedure5 CREATE PROCEDURE db.procedure5 ()
declare
I_ORDER_NUM number(6) = 123456;

begin
delete from ORDERS where order_num = I_ORDER_NUM;
end//
DELIMITER

In this answer I assumed table name as orders and some of the column names as mentioned in th answer.

Thanks

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

---

---

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