orks" (); OR REPLACE FUNCTION "public". " ; reteil_works "() RETURNS trigger AS
$ body $ INTEGER; into cnt g.count_at_storehouse FROM goods g WHERE (g.goods_id = NEW.goods_id); NEW.reteil_count <= cnt THENgoods SET THEN EXCEPTION 'На складі немає такої кількості даного товару на продаж!'; p align="justify"> END IF;;
$ body $ 'plpgsql'ON NULL INPUTINVOKER100;
Результат роботи:
В
. user_sum_discount (Підрахунок накопичувальної знижки для покупця при здійсненні нової покупки)
CREATE TRIGGER "user_summ_discount" AFTER INSERT "public". "reteil" FOR EACH ROW PROCEDURE "public". "user_sum_discount" (); OR REPLACE FUNCTION "public". " ; user_sum_discount "() RETURNS trigger AS
$ body $ _id INTEGER; _id INTEGER; _id INTEGER; INTEGER; DOUBLE PRECISION; DOUBLE PRECISION;: = 0; INTO b_id a.buyer_id FROM accounts a WHERE a.account_id = NEW.account_id; a_id IN SELECT a.account_id FROM accounts a WHERE a.buyer_id = b_id LOOPg_id, cnt IN SELECT r.goods_id, r.reteil_count FROM reteil r WHERE (r.account_id = a_id) LOOPINTO seb g.price_of_last_delivery FROM goods g WHERE (g.goods_id = g_id);: = summ + seb * cnt; LOOP; LOOP; buyers SET summ_discount = 0 WHERE (buyer_id = b_id); summ> = 1000 THENbuyers SET summ_discount = 1 WHERE (buyer_id = b_id); IF; summ> = 5000 THENbuyers SET summ_discount = 5 WHERE (buyer_id = b_id); IF; summ> = 10000 THENbuyers SET summ_discount = 10 WHERE (buyer_id = b_id); IF; NEW;;
$ body $ 'plpgsql'ON NULL INPUTINVOKER100;
6.3 Збережені процедури
. Final_Price (Підрахунок кінцевої ціни продажу)
CREATE OR REPLACE FUNCTION "public". "Final_Price" (id_a integer) RETURNS double precision AS
$ body $ double precision; sdisc double precision; INTEGER; id_g INTEGER; INTEGER; seb double precision; INTEGER; cnt INTEGER; INTEGER; tmp double precision;: = 0; INTO sd b.summ_discount FROM buyers b, accounts a WHERE (b.buyer_id = a.buyer_id) and (a.account_id = id_a); INTO sdisc, apr a.discount_on_shipping, a.price_of_shipping FROM accounts a WHERE (a.account_id = id_a); id_g, disc , cnt IN SELECT r.goods_id, r.discount, r.reteil_count FROM reteil r WHERE (r.account_id = id_a) LOOPINTO seb, per g.price_of_last_delivery, g.percent_of_price_increasing FROM goods g WHERE (g.goods_id = id_g);: = (seb + seb * per/100) * cnt;: = summ + tmp - tmp * disc/100; LOOP;: = summ-summ * sd/100;: = summ + apr-apr * sdisc/100; summ; ;
$ body $ 'plpgsql'ON NULL INPUTINVOKER100;
Результат роботи:
В
OrdersHistory (Висновок історії замовлень заданого клієнта)
