Проектирование и реализация базы данных «On-line магазин»

 

Название	Описание задачи	Выходные параметры
Unclaimed_Goods	Вывод товаров, которые не продавались  более месяца.	Name (Название товара), count_at_storehouse (количество товара на складе), date_of_last_reteil (дата последней продажи товара)

 

 

 

 

5.4 Хранимые  процедуры.

 

Некоторая информация не может быть получена только с помощью представлений. Для этого требуются более  сложные запросы или ввод параметров, относительно которых производится выборка. Для решения таких задач в СУБД PostgreSQL существует механизм хранимых процедур.

Разработанные хранимые процедуры описаны в  таблице 5.3.

 

Таблица 5.3. Описание разработанных  хранимых процедур.

 

Название	Описание задачи	Входные параметры	Выходные параметры
Final_Price	Подсчет конечной цены продажи	id_a: INTEGER (Номер счета)	Summ: DOUBLE PRECISION (Конечная цена продажи)
OrdersHistory	Вывод истории заказов заданного  клиента	b_id: INTEGER (Номер покупателя)	Name: VARCHAR (Название игры),  BuyedCount: INTEGER (Количество купленного товара), DateOfBuy: DATE (Дата покупки)
TopBuyer	Поиск покупателя, купившего товара на наибольшую сумму	-	Lname: VARCHAR (Фамилия покупателя), Total: DOUBLE PRECISION (Общая сумма покупок)
PercentOfUsing Shiping	Вывод статистики используемых способов доставки в процентном соотношении	-	Type_of_shiping: VARCHAR (Способ доставки), Percentage: DOUBLE PRECISION (процет использования)
Profit	Подсчет общей прибыли магазина за заданный период	Begin_Date: DATE (Начальная дата подсчета прибыли) End_Date: DATE (Конечная дата подсчета прибыли)	Cost: DOUBLE PRECISION (Затраты), Proceeds: DOUBLE PRECISION (Выручка), Profits: DOUBLE PRECISION (Прибыль)
Salary	Подсчет зарплаты сотрудников	-	LastName: VARCHAR (Фамилия сотрудника), Post: VARCHAR (Должность), Salary: DOUBLE PRECISION (Оклад), Salary_Plus_Wage_ Rate: DOUBLE PRECISION (Оклад в сумме с процентом от продаж)
Search	Поиск дисков по названию/разработчику/издателю	whattofind: VARCHAR (фраза, по которой будет вестись поиск)	Id: INTEGER (Номер игры), Name: VARCHAR (Название игры), Developer: VARCHAR (Разработчик), Publisher: VARCHAR (Издатель), Price: DOUBLE PRECISION (Цена)
TopGood	Поиск самого популярного товара по результатам продаж	-	NameOfGood: VARCHAR (Название игры), SellingCount: INTEGER (Количество проданных копий)
Top_Seller	Поиск самого активного продавца по количеству оформленных счетов	-	Name: VARCHAR (Фамилия продавца), Selled: INTEGER (Количество оформленных счетов)

 

 

6. Реализация базы данных в среде серверной СУБД        PostgreSQL 8.4.

 

Для реализации базы данных была использована программа «SQL Manager 2007 for PostgreSQL», которая является GUI-оболочкой, предназначенной для разработки и администрирования баз данных PostgreSQL.

Пример  таблицы представлен на рисунке  6.1.

 

 

Рис. 6.1. Таблица buyers.

 

6.1 Представления.

 

1. Unclaimed_Goods (Вывод товаров, которые не продавались более месяца).

 

CREATE OR REPLACE VIEW "public"."Unclaimed_Goods" (

    name,

    count_at_storehouse,

    date_of_last_reteil)

AS

SELECT g.name,

       g.count_at_storehouse,

       max(r.date_of_reteil) AS date_of_last_reteil

FROM goods g,

      reteil r

WHERE g.goods_id = r.goods_id AND

       (((SELECT max(r.date_of_reteil) AS max FROM reteil r WHERE g.goods_id =

        r.goods_id)) + 30) <= now()

GROUP BY g.name,

          g.count_at_storehouse;

 

 

 

Результат работы:

 

 

2. Триггеры.

 

1. consigment_date_check (Проверка даты поставки партии (она должна быть меньше или равна текущей))

 

CREATE TRIGGER "consigment_date_check" BEFORE INSERT OR UPDATE

ON "public"."consignment" FOR EACH ROW

EXECUTE PROCEDURE "public"."cons_date_check"();

 

CREATE OR REPLACE FUNCTION "public"."cons_date_check" () RETURNS trigger AS

$body$

BEGIN

    If NEW.consignment_date>now() THEN

           RAISE EXCEPTION 'Введенная дата больше текущей!';

           end if;

           RETURN new;

END;

$body$

LANGUAGE 'plpgsql'

VOLATILE

CALLED ON NULL INPUT

SECURITY INVOKER

COST 100;

Результат работы:

 

 

Триггеры «goods_date_check», «reteil_date_check» имеют аналогичный код и результат работы.

2. goods_update_from_consig (Автоматическое добавление в поле «Количество на складе» таблицы «Товары» количества товара, указанного при осуществлении поставки, а также заполнение поля «Цена последней поставки»)

 

CREATE TRIGGER "goods_update_from_consig" AFTER INSERT

ON "public"."consignment" FOR EACH ROW

EXECUTE PROCEDURE "public"."goods_works"();

 

CREATE OR REPLACE FUNCTION "public"."goods_works" () RETURNS trigger AS

$body$

BEGIN

UPDATE goods SET count_at_storehouse=count_at_storehouse+NEW.goods_count

FROM consignment

WHERE (goods.goods_id=NEW.goods_id);

UPDATE goods SET price_of_last_delivery=NEW.delivery_price

FROM consignment

WHERE (goods.goods_id=NEW.goods_id);

  RETURN new;

END;

$body$

LANGUAGE 'plpgsql'

VOLATILE

CALLED ON NULL INPUT

SECURITY INVOKER

COST 100;

 

3. reteil_dateSending (Автоматическая установка даты отправки товара (дата покупки + 5 дней))

 

CREATE TRIGGER "reteil_dateSending" AFTER INSERT

ON "public"."reteil" FOR EACH ROW

EXECUTE PROCEDURE "public"."dateSending"();

 

CREATE OR REPLACE FUNCTION "public"."dateSending" () RETURNS trigger AS

$body$

BEGIN

   UPDATE accounts SET date_of_sending=NEW.date_of_reteil+5

    WHERE (accounts.account_id=NEW.account_id);           

    RETURN NEW;

END;

$body$

LANGUAGE 'plpgsql'

VOLATILE

CALLED ON NULL INPUT

SECURITY INVOKER

COST 100;

 

4. reteil_update_count (При осуществлении продажи – автоматическое уменьшение числа товара на складе на количество проданного товара, а также вывод на экран ошибки в случае, когда количество на продажу больше, чем количество товара на складе)

 

CREATE TRIGGER "reteil_update_count" BEFORE INSERT

ON "public"."reteil" FOR EACH ROW

EXECUTE PROCEDURE "public"."reteil_works"();

 

CREATE OR REPLACE FUNCTION "public"."reteil_works" () RETURNS trigger AS

$body$

DECLARE

cnt INTEGER;

BEGIN

SELECT into cnt g.count_at_storehouse FROM goods g WHERE (g.goods_id=NEW.goods_id);

IF NEW.reteil_count<=cnt THEN

  UPDATE goods SET count_at_storehouse=count_at_storehouse-NEW.reteil_count

FROM reteil

WHERE (goods.goods_id=NEW.goods_id);

RETURN NEW;

END IF;

IF NEW.reteil_count>cnt THEN

RAISE EXCEPTION 'На складе нет такого количества данного товара на продажу!';

 END IF;

END;

$body$

LANGUAGE 'plpgsql'

VOLATILE

CALLED ON NULL INPUT

SECURITY INVOKER

COST 100;

 

 

Результат работы:

 

 

5. user_sum_discount (Подсчет накопительной скидки для покупателя при осуществлении новой покупки)

 

CREATE TRIGGER "user_summ_discount" AFTER INSERT

ON "public"."reteil" FOR EACH ROW

EXECUTE PROCEDURE "public"."user_sum_discount"();

 

CREATE OR REPLACE FUNCTION "public"."user_sum_discount" () RETURNS trigger AS

$body$

DECLARE

b_id INTEGER;

a_id INTEGER;

g_id INTEGER;

cnt INTEGER;

seb DOUBLE PRECISION;

summ DOUBLE PRECISION;

BEGIN

summ:=0;

  SELECT INTO b_id a.buyer_id FROM accounts a WHERE  a.account_id=NEW.account_id;

  FOR a_id IN SELECT a.account_id FROM accounts a WHERE a.buyer_id=b_id LOOP

     FOR g_id, cnt IN SELECT r.goods_id, r.reteil_count FROM reteil r WHERE (r.account_id=a_id) LOOP

      SELECT INTO seb g.price_of_last_delivery FROM goods g WHERE (g.goods_id=g_id);

       summ:=summ+seb*cnt;

     END LOOP;

  END LOOP;

  UPDATE buyers SET summ_discount=0 WHERE (buyer_id=b_id);

  IF summ>=1000 THEN

    UPDATE buyers SET summ_discount=1 WHERE (buyer_id=b_id);

  END IF;

  IF summ>=5000 THEN

    UPDATE buyers SET summ_discount=5 WHERE (buyer_id=b_id);

  END IF;

  IF summ>=10000 THEN

    UPDATE buyers SET summ_discount=10 WHERE (buyer_id=b_id);

  END IF;

  RETURN NEW;

END;

$body$

LANGUAGE 'plpgsql'

VOLATILE

CALLED ON NULL INPUT

SECURITY INVOKER

COST 100;

 

 

3. Хранимые процедуры.

 

1. Final_Price (Подсчет конечной цены продажи)

 

CREATE OR REPLACE FUNCTION "public"."Final_Price" (id_a integer) RETURNS double precision AS

$body$

DECLARE

  summ double precision;  sdisc double precision;

  apr INTEGER;  id_g INTEGER;

  disc INTEGER;   seb double precision;

  per INTEGER;  cnt INTEGER;

  sd INTEGER;  tmp double precision;

BEGIN

  summ:=0;

  SELECT INTO sd b.summ_discount FROM buyers b, accounts a WHERE (b.buyer_id=a.buyer_id) and (a.account_id=id_a);

  SELECT INTO sdisc, apr a.discount_on_shipping, a.price_of_shipping FROM accounts a WHERE (a.account_id=id_a);

  FOR id_g, disc, cnt IN SELECT r.goods_id, r.discount, r.reteil_count FROM reteil r WHERE (r.account_id=id_a) LOOP

      SELECT INTO seb, per   g.price_of_last_delivery, g.percent_of_price_increasing FROM goods g WHERE (g.goods_id=id_g);

      tmp:=(seb+ seb*per/100)*cnt;

      summ:=summ + tmp - tmp*disc/100;

    END LOOP;

    summ:=summ-summ*sd/100;

    summ:=summ+apr-apr*sdisc/100;

    RETURN summ;

END;

$body$

LANGUAGE 'plpgsql'

VOLATILE

CALLED ON NULL INPUT

SECURITY INVOKER

COST 100;

Результат работы:

 

2. OrdersHistory (Вывод истории заказов заданного клиента)

 

CREATE OR REPLACE FUNCTION "public"."OrdersHistory" (b_id integer, out "Name" varchar, out "BuyedCount" integer, out "DateOfBuy" date) RETURNS SETOF record AS

$body$

DECLARE

a_id INTEGER;

r_id INTEGER;

BEGIN

FOR a_id IN SELECT a.account_id FROM accounts a WHERE a.buyer_id="b_id" LOOP

  FOR r_id,"BuyedCount", "DateOfBuy" IN SELECT r.goods_id,  r.reteil_count, r.date_of_reteil FROM reteil r

  WHERE r.account_id=a_id LOOP

   SELECT INTO "Name" g.name FROM goods g WHERE g.goods_id=r_id;

  RETURN NEXT;

  END LOOP;

  END LOOP;

END;

$body$

LANGUAGE 'plpgsql'

VOLATILE

CALLED ON NULL INPUT

SECURITY INVOKER

COST 100;

Результат работы:

 

 

3. TopBuyer (Поиск покупателя, купившего товара на наибольшую сумму)

 

CREATE OR REPLACE FUNCTION "public"."TopBuyer" (out "Lname" varchar, out "Total" double precision) RETURNS record AS

$body$

DECLARE

  summ double precision; id_g INTEGER;

  id_a INTEGER; id_b INTEGER;  disc INTEGER;

  seb double precision; per INTEGER;

  cnt INTEGER;  tmp double precision;

  nname varchar;

BEGIN

"Total":=0;

FOR id_b, nname IN SELECT b.buyer_id, b.last_name FROM buyers b LOOP

summ:=0;

  FOR id_a  IN SELECT a.account_id FROM accounts a WHERE (a.buyer_id=id_b) LOOP   

   FOR id_g, disc, cnt IN SELECT r.goods_id, r.discount, r.reteil_count FROM reteil r WHERE (r.account_id=id_a) LOOP

      SELECT INTO seb, per g.price_of_last_delivery, g.percent_of_price_increasing FROM goods g WHERE (g.goods_id=id_g);

      tmp:=(seb+ seb*per/100)*cnt;

      summ:=summ + tmp - tmp*disc/100;

   END LOOP;

  END LOOP;

    if summ>"Total" then  

    "Total":=summ;

    "Lname":=nname;

    end if;

    END LOOP;

END;

$body$

LANGUAGE 'plpgsql'

VOLATILE

CALLED ON NULL INPUT

SECURITY INVOKER

COST 100;

Результат работы:

 

 

4. PercentOfUsingShiping (Вывод статистики используемых способов доставки в процентном соотношении)

 

CREATE OR REPLACE FUNCTION "public"."PercentOfUsingShiping" (out "Type_of_shiping" varchar, out "Percentage" double precision) RETURNS SETOF record AS

$body$

DECLARE

  cnt1 INTEGER;

  cnt2 INTEGER;

  cnt3 INTEGER;

  summ DOUBLE PRECISION;

BEGIN

    SELECT INTO cnt1 COUNT(a.type_of_shipping) FROM accounts a WHERE (upper(a.type_of_shipping)=upper('Авиапочта'));

    SELECT INTO cnt2 COUNT(a.type_of_shipping) FROM accounts a WHERE (upper(a.type_of_shipping)=upper('Наземная почта'));

    SELECT INTO cnt3 COUNT(a.type_of_shipping) FROM accounts a WHERE (upper(a.type_of_shipping)=upper('Курьер'));

 

  summ:=cnt1+cnt2+cnt3;

   "Type_of_shiping":='Авиапочта';

   "Percentage":=100*cnt1/summ;

  RETURN NEXT;

   "Type_of_shiping":='Наземная почта';

   "Percentage":=100*cnt2/summ;

  RETURN NEXT;

   "Type_of_shiping":='Курьер';

   "Percentage":=100*cnt3/summ;

  RETURN NEXT;

END;

$body$

LANGUAGE 'plpgsql'

VOLATILE

CALLED ON NULL INPUT

SECURITY INVOKER

COST 100;

Результат работы:

 

5. 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$

DECLARE

  summ double precision;  id_g INTEGER;

  id_a INTEGER;  disc INTEGER;

  seb double precision;  per INTEGER;

  cnt INTEGER;  sd INTEGER;

  tmp double precision;  tmp2 double precision;

BEGIN

  IF "Begin_Date"<"End_Date" THEN

  "Proceeds":=0;

  "Cost":=0;

  FOR id_a IN SELECT a.account_id FROM accounts a LOOP

     summ:=0;

     tmp2:=0;

  SELECT INTO sd b.summ_discount FROM buyers b, accounts a WHERE (b.buyer_id=a.buyer_id) and (a.account_id=id_a);

     FOR 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") LOOP

      SELECT INTO seb, per   g.price_of_last_delivery, g.percent_of_price_increasing FROM goods g WHERE (g.goods_id=id_g);

      tmp2:=tmp2 + seb*cnt;

      tmp:=(seb+ seb*per/100)*cnt;

      summ:=summ + tmp - tmp*disc/100;

    END LOOP;

    summ:=summ-summ*sd/100;

    "Cost":="Cost"+tmp2;

    "Proceeds":="Proceeds"+summ;

END LOOP;

  "Profits":="Proceeds" - "Cost";

    RETURN NEXT;

   ELSE

  RAISE EXCEPTION 'Начальная дата не может быть больше конечной!';

  END IF;

END;

$body$

LANGUAGE 'plpgsql'

VOLATILE

CALLED ON NULL INPUT

SECURITY INVOKER

COST 100;

 

 

 

Результат работы:

 

 

 

6. Salary (Подсчет зарплаты сотрудников)

 

CREATE 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$

DECLARE

  summ double precision;  id_g INTEGER;

  id_a INTEGER;  id_ss INTEGER;

  disc INTEGER;   seb double precision;

  per INTEGER;  cnt INTEGER;

  wr INTEGER;  tmp double precision;

  nname varchar;

BEGIN

FOR 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

summ:=0;

  FOR id_a  IN SELECT a.account_id FROM accounts a WHERE (a.employee_id=id_ss) LOOP   

   FOR id_g, disc, cnt IN SELECT r.goods_id, r.discount, r.reteil_count FROM reteil r WHERE (r.account_id=id_a) LOOP

      SELECT INTO seb, per g.price_of_last_delivery, g.percent_of_price_increasing FROM goods g WHERE (g.goods_id=id_g);

      summ:=summ + (seb+ seb*per/100)*cnt;

    END LOOP;

    END LOOP;

    "Salary_Plus_Wage_Rate":="Salary"+ summ*wr/100;

     RETURN NEXT;

    END LOOP;

END;

$body$

LANGUAGE 'plpgsql'

VOLATILE

CALLED ON NULL INPUT

SECURITY INVOKER

COST 100;

 

Результат работы:

 

 

 

7. Search(Поиск дисков по названию/разработчику/издателю)

 

CREATE OR REPLACE FUNCTION "public"."Search" (whattofind varchar, out id integer, out "Name" varchar, out "Developer" varchar, out "Publisher" varchar, out "Price" double precision) RETURNS SETOF record AS

$body$

BEGIN

FOR "id", "Name", "Developer", "Publisher", "Price" IN SELECT g.goods_id,  g.name, g.developer, g.publisher, (g.price_of_last_delivery+g.price_of_last_delivery*g.percent_of_price_increasing/100), g.count_at_storehouse