Запросы в хранимой процедуре

Модераторы: kdv, CyberMax

Ответить
distodaz
Сообщения: 6
Зарегистрирован: 20 окт 2009, 05:40

Запросы в хранимой процедуре

Сообщение distodaz » 21 окт 2009, 07:49

Доброго всем времени суток! Есть необходимость выдернуть из одной таблицы в другую, продажи товаров за определённый срок. Поля: артикул товара, цена товара и количество. Во конечной таблице должно находиться количество товаров проданных по определённой цене, допустим если мы продавали товар 001 по цене 100р. в количестве 5шт. в первый день, товар 001 по цене 100р. в количестве 10шт. во второй день, товар 001 по цене 50р. в количестве 2шт. в третий день, то в конечной таблице должно быть: товар 001 по цене 100р. в количестве 15шт., товар 001 по цене 50р. в количестве 2шт. С интербейзом встретился впервые, хранимые процедуры мне тоже незнакомы, опираясь на форумы накатал код:

Код: Выделить всё

CREATE PROCEDURE MIH_PRC_ROUGE (
    b date,
    e date)
returns (
    cav varchar(30),
    prv double precision,
    qtv double precision)
as
declare variable qt_sum double precision;
begin
  /* Procedure Text */
  delete from mih_rouge;
  delete from mih_tmp_rouge;
  insert into mih_tmp_rouge
  select cardarticul,pricerub,quantity from mih_tmp_cash where sell_date between :b and :e;
  /*select cardarticul,pricerub,quantity from cashsail where sell_date between :b and :e into mih_tmp_rouge;*/
  for select * from mih_tmp_rouge into :cav, :prv, :qtv
  do 
  begin
     qt_sum=0;
     select sum(quantity) from mih_tmp_cash where (cardarticul = :cav) and (pricerub = :prv) into :qt_sum;
     delete from mih_tmp_rouge where cardarticul=:cav and pricerub=:prv;
     insert into mih_rouge (cardarticul,pricerub,quantity) values(:cav,:prv,:qt_sum);
  end
end
просьба знающим людям ткнуть в недостатки, которые несомненно имеются.
Заранее благодарю за помощь.
Последний раз редактировалось distodaz 21 окт 2009, 12:09, всего редактировалось 2 раза.

Attid
Спец
Сообщения: 377
Зарегистрирован: 14 ноя 2006, 09:58

Re: Запросы в хранимой процедуре

Сообщение Attid » 21 окт 2009, 10:02

distodaz писал(а):
Column does not belong to referenced table.
SQL error code = -206.
Column unknown.
CA1.
какое слово не понятно ? версия и название сервера тоже не озвученны

distodaz
Сообщения: 6
Зарегистрирован: 20 окт 2009, 05:40

Re: Запросы в хранимой процедуре

Сообщение distodaz » 21 окт 2009, 10:09

Column does not belong to referenced table.
SQL error code = -206.
Column unknown.
CA1.
с этим справился.

Используется IB 7.5. Забыл упомянуть, таблица mih_tmp_cash занимает около 6 гигов.

Dimitry Sibiryakov
Заслуженный разработчик
Сообщения: 1436
Зарегистрирован: 15 сен 2005, 09:05

Re: Запросы в хранимой процедуре

Сообщение Dimitry Sibiryakov » 21 окт 2009, 13:29

Непонятно зачем ты её вообще создаёшь. Что мешает в FOR SELECT засунуть первый запрос? Да и вообще потребность удалять все записи из таблицы и тут же добавлять обратно... попахивает MS SQL-ем.

kdv
Forum Admin
Сообщения: 6595
Зарегистрирован: 25 окт 2004, 18:07

Re: Запросы в хранимой процедуре

Сообщение kdv » 21 окт 2009, 14:46

автор, я не понял, Вы по ходу ответов текст процедуры в оригинальном письме меняете? Ну тогда форматируйте код по человечески. И в данном случае, по окончании вопроса я не вижу смысла сохранять этот топик на форуме, потому что в результате таких исправлений в исходном письме будет нормальный код процедуры, в отношении которого вопросы или обсуждение уже бессмыслены.

p.s. set term в топку, и гранты тоже. в вопросе они не нужны, удалил.

kdv
Forum Admin
Сообщения: 6595
Зарегистрирован: 25 окт 2004, 18:07

Re: Запросы в хранимой процедуре

Сообщение kdv » 21 окт 2009, 14:50

и вообще
- действительно, удалять, потом вставлять, и т.д. - садо-мазо.
- в процедуре объявлены выходные переменные, но suspend нет. Вы уж либо крестик снимите, либо ....

http://www.ibase.ru/devinfo/sp_call.htm

сделайте селективную процедуру, которая вам по select будет возвращать то, что вы сейчас насилуете над min_rouge. И все, ваши min_rouge и min_tmp_rouge отвалятся за ненадобностью.

distodaz
Сообщения: 6
Зарегистрирован: 20 окт 2009, 05:40

Re: Запросы в хранимой процедуре

Сообщение distodaz » 22 окт 2009, 05:27

Код: Выделить всё

begin
  delete from mih_rouge;
  qt_sum=0;
  for select distinct cardarticul,pricerub from mih_tmp_cash
        where sell_date between :b and :e
        into :cav, :prv
  do
     begin
        select sum(quantity) from mih_tmp_cash 
        where (cardarticul = :cav) and (pricerub = :prv) and (sell_date between :b and :e) 
        into :qt_sum;
        insert into mih_rouge (cardarticul,pricerub,quantity) values(:cav,:prv,:qt_sum);
     end
  suspend;
end
вот такой запрос получился, работает почему-то долго, может есть способ оптимизировать?

kdv
Forum Admin
Сообщения: 6595
Зарегистрирован: 25 окт 2004, 18:07

Re: Запросы в хранимой процедуре

Сообщение kdv » 22 окт 2009, 12:32

лениво влезать в смысл приведенных запросов, но еще раз подумайте - зачем Вам нужны эти временные таблицы?
вот такой запрос получился, работает почему-то долго, может есть способ оптимизировать?
нечего тут оптимизировать, в том смысле, что это все проктология, без исключений. У меня такое впечатление что вместо нормальных запросов Вы занимаетесь ступенчатым переносом данных во временные таблицы просто потому, что не можете сформулировать запрос к исходным таблицам. И в результате решили действовать по шагам - вот мы берем distinct, кладем сюда, вот берем сумму, помещаем туда...

Гораздо правильнее было бы начать сначала, т.е. привести исходные данные, и что нужно получить (не упоминая временные таблицы вообще).

p.s. и опять я за Вас форматирую запрос. В чем проблема?

kdv
Forum Admin
Сообщения: 6595
Зарегистрирован: 25 окт 2004, 18:07

Re: Запросы в хранимой процедуре

Сообщение kdv » 22 окт 2009, 12:38

может есть способ оптимизировать?
смотрите:

Код: Выделить всё

select distinct cardarticul,pricerub from mih_tmp_cash
        where sell_date between :b and :e
у вас в этой таблице "6 гигов данных". есть индекс по sell_date? Вы в курсе, что distinct приведет к сортировке результата на диске? Смотрите план запроса.

и дальше, тут же опять по этой же здоровенной таблице

Код: Выделить всё

select sum(quantity) from mih_tmp_cash
where (cardarticul = :cav) and (pricerub = :prv) and (sell_date between :b and :e)


к этой же таблице, с тем же условием sell_date between :b and :e, да еще и по тем же УЖЕ отобранным cav и prv.

как так можно писать - не представляю. Читайте про group by, что-ли...

distodaz
Сообщения: 6
Зарегистрирован: 20 окт 2009, 05:40

Re: Запросы в хранимой процедуре

Сообщение distodaz » 22 окт 2009, 19:23

Неужто запрос будет выглядеть вот так?

Код: Выделить всё

select cardarticul, pricerub, sum(quantity) from mih_tmp_cash
        where sell_date between :b and :e
	group by cardarticul, pricerub

kdv
Forum Admin
Сообщения: 6595
Зарегистрирован: 25 окт 2004, 18:07

Re: Запросы в хранимой процедуре

Сообщение kdv » 22 окт 2009, 20:11

ну а как?

distodaz
Сообщения: 6
Зарегистрирован: 20 окт 2009, 05:40

Re: Запросы в хранимой процедуре

Сообщение distodaz » 03 ноя 2009, 07:35

В продолжение темы =)
позволяет ли IB делать подзапрос в запросе?
Хотелось бы что-то вроде:

Код: Выделить всё

select mtr.cardarticul, mtr.pricerub, mtr.quantity, dc.price_rub
        from (select cs.cardarticul, cs.pricerub, sum(cs.quantity), cs.cashnumber from cashsail cs
                 where cs.sell_date between :b and :e
                 group by  cs.cashnumber, cs.cardarticul, cs.pricerub;) mtr
        join mih_mag_kas mmk on mtr.cashnumber=mmk.cashnumber
        left join disccard dc on mmk.price_kind=dc.price_kind and mtr.cardarticul=dc.articul ;

а в ответ ругается:
Invalid token.
Dynamic SQL Error.
SQL error code = -104.
Token unknown - line 2, char 13.
select.

Dimitry Sibiryakov
Заслуженный разработчик
Сообщения: 1436
Зарегистрирован: 15 сен 2005, 09:05

Re: Запросы в хранимой процедуре

Сообщение Dimitry Sibiryakov » 03 ноя 2009, 13:45

distodaz писал(а):позволяет ли IB делать подзапрос в запросе?
Подзапросы - да. Но то, что ты хочешь это не подзапрос. Такого IB не позволяет.

distodaz
Сообщения: 6
Зарегистрирован: 20 окт 2009, 05:40

Re: Запросы в хранимой процедуре

Сообщение distodaz » 03 ноя 2009, 15:08

Так можно выполнить SELECT из SELECT'a или нет? Киньтесь ссылкой пожалуйста =)

WildSery
Заслуженный разработчик
Сообщения: 1738
Зарегистрирован: 05 июн 2006, 16:19

Re: Запросы в хранимой процедуре

Сообщение WildSery » 03 ноя 2009, 17:52

Какую такую ссылку? У тебя должна быть документация к IB 7.5
Derived tables им не поддерживаются.

Ответить