Examine the following PL/SQL Procedure

-- process an order

create or replace procedure ord_p_proc(v_pid in VARCHAR2, v_qnty in NUMBER) AS begin

declare

v_qty_on_hand NUMBER(10);

begin

select quantity into v_qty_on_hand from products

where pid = v_pid; if v_qty_on_hand > v_qnty then

update products set quantity = quantity - v_qnty

where pid = v_pid;

insert into orders

values(1027, 'mar', 'c003', 'a01', v_pid, v_qnty, 45.00); else

insert into order_errors

values(v_pid, 'low inventory');

end if; commit; end; end; Rewrite the procedure so that:

the parameters v_pid and v_qnty are defined to be exactly the same type as pid and quantity in the orders table, no matter what those types may be.

the procedure figures out what the next order number (ordno) should be and uses that in the 'insert into' statement instead of hard-coding the ordno.

the procedure handles unexpected occurrences such as the possibility that v_pid received does not exist in the database (ie, add exception handling).

the procedure either receives the month value as a parameter or looks it up from the system date. The procedure looks up the unit price and discount, then calculates dollars. Do not use a trigger for this assignment.

the procedure receives and verifies customer id and agent id.