oracle - update/insert PL/SQL -

let's customer 4 wishes increase order 23 100. enable user type in:

1. the number 4 customer_id
2. 100 updated quantity.

i want write pl/sql function receive these 2 values , update sales table reflect change. print out on screen main section of code total quantity customer 4 before , after update. please have tried following code not sure structure

create or replace trigger orders_before_insert before insert  on ord  each row  declare   v_price number;  new number; begin   select pr v_price product product_id =:new.product_id  ;  -- update create_date field current system date :new.total_cost := :new.quantity * v_price;  end; 

here proof of concept procedure. doesn't have validation or error handling expect in proper procedure.

create or replace procedure update_order      (p_order_id in orders.id%type       , p_additional_qty in orders.qty%type       , p_orig_total out number        , p_new_total out number )     l_total number;     l_orig number; begin     update orders     set qty = qty + p_additional_qty     id = p_order_id     returning (qty - p_additional_qty)* price                 ,  qty * price l_orig, l_total;     p_orig_total := l_orig;     p_new_total := l_total;  end update_order; / 

in sql*plus use accept command input values user. declare variables hold computed values var, , output them print.

here test data:

sql> select * orders;          id        qty      price ---------- ---------- ----------         42         23      19.99  sql> 

and here how call procedure using sql*plus execute command:

sql> var tot number sql> var orig number sql> accept order_id prompt "enter order id: " enter order id: 42 sql> accept add_qty prompt "please enter add qty: " please enter add qty: 77 sql> exec update_order (&order_id, &add_qty, :orig, :tot)  pl/sql procedure completed.  sql> print :orig        orig ----------     459.77  sql> print :tot         tot ----------       1999  sql>  

to script it, place commands in text file, , run in sql*plus this:

sql>  @your_script.sql 

---

note have deviated assignment's instructions in couple of ways.

1. i don't display original total cost upfront. need select record first update it. in real life want avoid 2 operations.
2. i'm calculating totals rather storing them. again approach take in real system. however, if table has total_cost column need amend update according.