Question
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
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.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started