3

projektSBDnowy.txt

.create or replace trigger personal_data after update on dients for each row begin

dbms_output.put_line{'Check if all data are written correct');

end;

/ - Procedures —this procedurę allows user to change prices (give a bonus) of all services by some %

create or replace procedurę price_bonus(v_proc number) Is

cursor v_cursor is

select name, id_service, price from sen/ices for update of price;

v_row v_cursor%rowtype; v_oldp number; v_newp number;

begin

open v_cursor;

fetch v_cursor into v_row;

while v_cursor%found loop

select price into v_oldp from services where id_service = v_row.id_service; update sen/ices set price = price-(price * (v_proc/100)) where current of v_cursor; select price into v_newp from services where id_service = v_row.id_service;

dbms_output.putJineCPrice for setvice '||v_row.name|i’ has changed from '| |v_oldp| to ’||v_newp);

fetch v_cursor into v_row;

end loop; dose v_cursor;

end;

/

-this procedurę shows all the services and prices performed by a worker, -and if the price is lower than 10 it doesn't print the price

set serveroutput on;

aeate or replace procedurę worker_prices

(

w_name varchar2, w_sumame varchar2

)

as

cursor W_cursor is

select s.price, s.name from sen/ices s inner join Workers w on s.id_worker = w.id_worker where w.name = w_name and w.sumame = w_sumame;

w_row w_cursor%rowtype;

begin

open w_cursor; fetch w_cursor into w_row; while w_cursor%found loop

if w_row.price > 10 then begin

Strona 3