fy"> "Percentage": = 100 * cnt3/summ; NEXT;;
$ body $ 'plpgsql'ON NULL INPUTINVOKER100;
Результат роботи:
В
4. Profit (Підрахунок загального прибутку магазину за заданий період)
CREATE OR REPLACE FUNCTION "public". "Profit" ("Begin_Date" date, "End_Date" date, out "Cost" double precision, out "Proceeds" double precision, out "Profits" double precision) RETURNS SETOF record AS
$ body $ double precision; id_g INTEGER; _a INTEGER; disc INTEGER; double precision; per INTEGER; INTEGER; sd INTEGER; double precision; tmp2 double precision; "Begin_Date" <"End_Date" ; THEN
"Proceeds": = 0;
"Cost": = 0; id_a IN SELECT a.account_id FROM accounts a LOOP: = 0;: = 0; INTO sd b.summ_discount FROM buyers b, accounts a WHERE (b.buyer_id = a.buyer_id) and (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) and (r.date_of_reteil BETWEEN " ; Begin_Date "and" End_Date ") LOOPINTO seb, per g.price_of_last_delivery, g.percent_of_price_increasing FROM goods g WHERE (g.goods_id = id_g);: = tmp2 + seb * cnt;: = (seb + seb * per/100) * cnt;: = summ + tmp - tmp * disc/100; LOOP;: = summ-summ * sd/100;
"Cost": = "Cost" + tmp2;
"Proceeds": = "Proceeds" + summ; LOOP;
"Profits": = "Proceeds" - "Cost"; NEXT;
ELSE
RAISE EXCEPTION 'Початкова дата не може бути більше кінцевої!';
END IF;;
$ body $ 'plpgsql'ON NULL INPUTINVOKER100;
Результат роботи:
В
. Salary (Підрахунок зарплати співробітників)
OR REPLACE FUNCTION "public". "Salary" (out "LastName" varchar, out "Post" varchar, out "Salary" double precision, out "Salary_Plus_Wage_Rate" double precision ) RETURNS SETOF record AS
$ body $ double precision; id_g INTEGER; _a INTEGER; id_ss INTEGER; INTEGER; seb double precision; INTEGER; cnt INTEGER; INTEGER; tmp double precision; varchar; id_ss, "LastName", " ; Post "," Salary ", wr IN SELECT ss.employee_id, ss.last_name, ss.post, ss.salary, ss.wage_rate FROM shop_staff ss LOOP: = 0; id_a IN SELECT a.account_id FROM accounts a WHERE ( a.employee_id = id_ss) LOOP 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);: = summ + (seb + seb * per/100) * cnt; LOOP; LOOP;
"Salary_Plus_Wage_Rate": = "Salary" + summ * wr/100; NEXT; LOOP;;
