oracle - update table pl/sql -

i update following table using pl/sql function, thing managed write trigger code want re-write using 'function' instead. customer 5 increase order 30 200. , enable user type in:

1) number 5 customer_id , 2) 200 updated quantity. , print out total quantity customer 5 before , after update.

    create table sales (customer_id number(10), product_id number(10), quantity number(10));      insert sales (customer_id, product_id, quantity) values(3,1,23);     insert sales (customer_id, product_id, quantity) values(1,2,34);     insert sales (customer_id, product_id, quantity) values(1,3,654);     insert sales (customer_id, product_id, quantity) values(3,7,32);     insert sales (customer_id, product_id, quantity) values(4,3,23);     insert sales (customer_id, product_id, quantity) values(3,3,111);     insert sales (customer_id, product_id, quantity) values(5,4,6); 

trigger code wrote:

create or replace trigger quantity_change  before insert or update of quantity on sales each row when (new.customer_id > 0) declare    qua number; begin    qua := :new.quantity - :old.quantity;    dbms_output.put_line('old quangtity: ' || :old.quantity);    dbms_output.put_line('new quantity: ' || :new.quantity);    dbms_output.put_line('diiference quangtity: ' || qua); end;  update sales set quantity = 200 customer_id = 5; 

i managed write procedure still stuck, dont know how enable use

create or replace procedure updatesales (  customer_id number, product_id number, quantity number) begin update sales set quantity= 100 customer_id= 4; end; 

i want use function solve issue , function somthing

    create [or replace] function function_name [(parameter_name [in | out | in out] type [, ...])]  return return_datatype {is | as} begin < function_body > end [function_name]; 

please advice

your procedure isn't using parameters have declared; body should more like:

update sales set quantity= quantity customer_id= customer_id; 

... won't expect because you've used same names parameters , columns (and haven't referenced product id @ all), every row in table updated current value. it's common use prefix formal parameter names avoid confusion, though can use procedure name explicitly when refer them.

you said want function isn't clear why. it's conventional modify data in procedures , not in functions, , if function dml can't called query , have called in pl/sql context. i'll start procedure.

you said wanted 'print out' quantity before , after update. procedure shouldn't that; should not assume user or client can handle dbms_output or have enabled. use out parameter return pre-update value caller though:

create or replace procedure update_sales (    p_customer_id in sales.customer_id%type,   p_product_id in sales.product_id%type,   p_new_quantity in sales.quantity%type,   p_old_quantity out sales.quantity%type ) begin   select quantity   p_old_quantity   sales   customer_id = p_customer_id   , product_id = p_product_id   update;    update sales   set quantity = p_new_quantity   customer_id = p_customer_id   , product_id = p_product_id; end; / 

this gets current value of quantity out variable, , locks record for update stop value changing while you're working on (probably overkill here, want learn...)

it updates same row new value passed in. finding row again using customer , product ids, , differently if want experiment - rowid local variable first query , use update, or use cursor, etc.

you call anonymous block test, , use dbms_output show old , new values; again, don't use dbms_output in production code, debugging:

set serveroutput on declare   l_customer_id sales.customer_id%type;   l_product_id sales.product_id%type;   l_new_quantity sales.quantity%type;   l_old_quantity sales.quantity%type; begin   l_customer_id := 5;   l_product_id := 4;   l_new_quantity := 200;    update_sales(l_customer_id, l_product_id, l_new_quantity, l_old_quantity);    dbms_output.put_line('quantity changed ' || l_old_quantity     || ' ' || l_new_quantity     || ' (' || to_char(l_new_quantity - l_old_quantity, 'fms999') || ')'); end; /  pl/sql procedure completed.  quantity changed 6 200 (+194) 

you can call application, using bind variables, in similar way, , have application display values.

note haven't committed or rolled changes, , session trying call procedure same values block until do; see new value (200) when run. haven't done validation or exception handling in procedure, caller needs both.

you could make function returns old value instead of using out parameter, you'd need call in similar way, , people don't expect functions change - return current state. if how want it, need modify declaration have return type , local variable; select old value local variable; , return too:

create or replace function update_sales (    p_customer_id in sales.customer_id%type,   p_product_id in sales.product_id%type,   p_new_quantity in sales.quantity%type ) return sales.quantity%type   l_old_quantity sales.quantity%type; begin   select quantity   l_old_quantity   sales   customer_id = p_customer_id   , product_id = p_product_id;    update sales   set quantity = p_new_quantity   customer_id = p_customer_id   , product_id = p_product_id;    return l_old_quantity; end; / 

you still have call pl/sql context (or jdbc callable statement):

declare   l_old_quantity sales.quantity%type; begin   l_old_quantity := update_sales(5, 4, 200);   dbms_output.put_line('quantity ' || l_old_quantity); end; /  pl/sql procedure completed.  quantity 6 

you can't call plain sql because doing dml operation:

select update_sales(5, 4, 200) dual;  error report - sql error: ora-14551: cannot perform dml operation inside query  ora-06512: @ "my_schema.update_sales", line 17 14551. 00000 -  "cannot perform dml operation inside query " *cause:    dml operation insert, update, delete or select-for-update            cannot performed inside query or under pdml slave. *action:   ensure offending dml operation not performed or            use autonomous transaction perform dml operation within            query or pdml slave.