Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Почему не работает процедура?  [new]
RandomName
Member

Откуда:
Сообщений: 21
Доброй ночи, есть одна бд, есть задание расчета дебиторской задолженности, написал процедуру заполняющую расчет деб_долга но она почему-то не работает. Процедура берет все значения кроме последнего, и не вычитает из долга новые поступления.
Схему бд прикрепил, вот задание:
+
4. Заполнение таблицы дебиторской задолженности
Создать таблицу дебиторской задолженности. В ней должна быть ссылка на клиента и на расход товара. Еще в ней должна быть колонка сумма. В колонке сумма должно быть указано сколько еще по данному расходу еще не оплачено. Действует правило что любое поступление денег от клиента идет прежде всего на оплату самых старых неоплаченных расходов.

Вот сама процедура:
+
go
create procedure fill_table2 as
begin
declare @id int,
        @client int,
		@summ integer,
		@loop1 integer;

declare dd_cur cursor for
  select recgoods.id,recept.client, SUM(recgoods.price*recgoods.volume),recept.ddate
  from recept join recgoods on recept.id=recgoods.id
  group by recgoods.id, recept.client,recept.ddate

set @loop1=0;
open dd_cur;
while @loop1=0 --цикл на заполнение deb_dolg
  begin
  declare @recept int,
          @ddate datetime;
  FETCH NEXT FROM dd_cur INTO @recept, @client, @summ,@ddate;--следующая строчка из select
  set @loop1=@@FETCH_STATUS;
  if @loop1=0
    insert into deb_dolg(client,recept,summ,ddate)
          values (@client,@recept,@summ,@ddate);--заносим данные
  end
close dd_cur;
deallocate dd_cur;

declare cur_inc cursor for
    ((select client, summ from bank_income)
    union all
    (select client, summ from cassa_income));
    
declare cur cursor for
    select recept, summ, id
    from deb_dolg
    where deb_dolg.client = @client
    order by ddate;
    
-----------цикл заполнения таблицы--------
declare @loop2 int; set @loop2=0;--внешний цикл
open cur_inc;-- открыаем курсоры
while @loop2=0 --проход по приходам
  begin
  declare @summInc integer;
  FETCH NEXT FROM cur_inc INTO @client, @summInc;--следующая строчка из select
  set @loop2=@@FETCH_STATUS;
  set @loop1=0;--внутренний цикл
  open cur;
  while @loop1=0 --проход по расходам
    begin
    declare @rec int, @s integer;
    fetch next from cur into @rec, @s, @id;
    set @loop1=@@FETCH_STATUS;--статус цикла
    if @summInc=@s
      begin
      DELETE FROM deb_dolg WHERE id=@id;
      set @summInc=0;
      set @loop1=1;
      end
    else if @summInc > @s
      begin
      set @summInc=@summInc-@s;
      DELETE FROM deb_dolg WHERE id=@id;
      end
    else -- приход меньше расхода
      begin
      set @summInc=@s-@summInc;
      update deb_dolg set summ=@summInc
        where id=@id;
      set @loop1=1;
      end
    end--внутренний цикл
  close cur;
  end--внешний цикл
  close cur_inc; -- закрываем курсор
  deallocate cur;
  deallocate cur_inc;
end 


Скрипт создания таблиц:
+
-- данный скрипт предназначен для создания таблиц

use sppr;-- использование созданной для даннной л/р схемы "dss"

-- таблица регионы
create table region
(
  id integer identity(1,1) not null constraint region_pk primary key,
  name char(50) not null
);

-- таблица города  
create table city
(
  id integer identity(1,1)  not null constraint city_pk primary key,
  name char(40) not null,
  region integer null, --id региона
  --связывание поля region с полем id таблицы region
  constraint region_fk foreign key (region) references region(id) on update cascade
);

-- таблица клиенты
create table clients
(
  id integer identity(1,1)  not null constraint client_pk primary key,
  name char(90) not null,--фамилия, имя, отчество
  address char(70) null,
  city integer null,
  --связывание поля city с полем id таблицы city
  constraint city_fk foreign key (city) references city(id) on update cascade
);

-- таблица склады
create table storages
(
  id integer identity(1,1)  not null constraint storage_pk primary key,
  name char(20) null
);

-- таблица "Приход товара от клиентов (от поставщиков)"
create table income
(
  id integer identity(1,1)  not null constraint income_pk primary key, -- id поставки
  ddate datetime not null,  -- дата поставки
  ndoc integer not null, -- номер документа о поставке
  client integer not null, -- клиент, от которого осуществляется поставка
  storage integer not null, -- склад, на который идет поставка
  constraint ndoc_uk unique(ndoc), -- ndoc (номер документа) не повторяется
  constraint client_fk foreign key (client) references clients(id) on update cascade,
  constraint storages_fk foreign key (storage) references storages(id) on update cascade
);

-- таблица категории товаров (мясо, рыба, молочное, ...)
create table goods_groups
(
  id integer not null constraint g_group_pk primary key,--для удобства нет автоинкремента
  name char(40) not null,-- название категории
  parent integer null, -- id старшей категории
  constraint id_fk foreign key (parent) references goods_groups(id)
);

-- таблица товары
create table goods
(
  id integer identity(1,1) not null constraint good_pk primary key, -- номер товара
  name char(50) not null,  -- наименование товара
  weight integer null, -- вес товара
  length integer null, -- длина
  height integer null, -- высота
  width integer null,  -- ширина
  g_group integer not null, -- категория товара
  constraint g_group_fk foreign key (g_group) references goods_groups(id) on update cascade
);

-- таблица приходов ("Поступление товара на склад")
create table incgoods
(
  id integer not null,   -- номер постаки 
  subid integer not null, -- номер строки в документе поставки
  
  volume integer not null, -- объем поставки (число товаров)
  price integer not null, -- цена экземпляра
  good integer not null, -- номер товара
  
  --задаем сложный первичный ключ из двух полей: id и subid.
  constraint incgood_pk primary key (id,subid),
  --связывание поля id этой таблицы с полем id таблицы income
  constraint id_fk_0 foreign key (id) references income(id) on update cascade,
  constraint goods_fk foreign key (good) references goods(id) on update cascade
);

-- таблица "Расплата через банк за поставку"
create table bank_recept
(
  id integer identity(1,1) not null constraint b_recept_pk primary key,
  ddate datetime not null,-- день расплаты
  summ integer not null,-- величина расплаты за товар
  client integer null,-- поставщик
  constraint client_fk_0 foreign key (client) references clients(id) on update cascade
);

-- таблица "Расплата через кассу за поставку товара"
create table cassa_recept
(
  id integer identity(1,1) not null constraint c_recept_pk primary key,
  ddate datetime not null,-- день расплаты
  summ integer not null,-- величина расплаты за товар
  client integer null,-- поставщик
  constraint client_fk_1 foreign key (client) references clients(id) on update cascade
);

-- таблица "Расход товара со скалада от продажи клиентам"
create table recept
(
  id integer identity(1,1) not null constraint recept_pk primary key,  
  ddate datetime not null,--дата продажи
  ndoc integer not null constraint ndoc_uk_0 unique,-- номер документа (уникальный)
  client integer null,-- покупатель
  storage integer not null,-- номер хранилища
  constraint client_fk_2 foreign key (client) references clients(id) on update cascade,
  constraint storage_fk foreign key (storage) references storages(id) on update cascade
);

-- таблица "Строки расхода товара со склада от продажи"
create table recgoods
(
  id integer not null,-- номер расхода от продажи
  subid integer not null,-- номер строки в документе расхода
  
  volume integer not null, -- количество проданных товаров
  price integer not null,-- цена 1 экземпляра
  good integer not null,-- id товара
  constraint recgood_pk primary key (id,subid),
  constraint id_fk_1 foreign key (id) references recept(id) on update cascade,
  constraint good_fk foreign key (good) references goods(id) on update cascade
);

-- таблица "Расплата за продажу клиентам через банк"
create table bank_income
(
  id integer identity(1,1) not null constraint b_income_pk primary key,
  ddate datetime not null,-- дата расплаты
  summ integer not null,-- величина расплаты
  client integer null,-- покупатель
  constraint client_fk_3 foreign key (client) references clients(id) on update cascade
);

-- таблица "Расплата за продажу клиентам через кассу"
create table cassa_income
(
  id integer identity(1,1) not null constraint c_income_pk primary key,
  ddate datetime not null,-- дата расплаты
  summ integer not null,-- величина расплаты
  client integer null,-- покупатель
  constraint client_fk_4 foreign key (client) references clients(id) on update cascade
);

-- новая таблица по заданию варианта 1
-- "Связь прихода и расхода по FIFO"
create table increc
(
  id integer identity(1,1) not null constraint ir_pk primary key,
  ddate datetime not null,-- дата прихода
  good integer not null,-- id товара
  inc_id integer not null,-- номер прихода
  inc_sub integer not null,-- номер строки в приходе
  rec_id integer not null,--номер расхода
  rec_sub integer not null,--номер строки в расходе
  volume integer not null,--величина для покрытия текущего расхода данным приходом
  constraint inc_id_fk foreign key (inc_id,inc_sub) references incgoods(id,subid) on delete cascade,
  constraint rec_id_fk foreign key (rec_id,rec_sub) references recgoods(id,subid) on delete cascade,
);

create table deb_dolg
(
	id integer identity(1,1) constraint dd_pk primary key,
	summ decimal,
	client integer,
	recept integer,
	ddate datetime,
	constraint client_fk_5 foreign key (client) references clients(id) on delete cascade,
	constraint recept_fk foreign key (recept) references recept(id) on delete cascade
);


Скрипт заполнения таблиц:
+
use sppr; -- используется схема dss

-- для таблицы регионов 
insert into region(name) values ('Москва'),('Мск. Область'),
('Санкт-Петербург'),('Владимирская Обл.'),('Тверская Обл.');

-- для таблицы городов
insert into city(name,region) values
('Москва',1),
('Истра',2),
('Санкт-Петербург',3),
('Владимир',4),
('Тверь',5);

-- для таблицы клиенты
insert into clients(name,address,city) values
('Компания 1','ул.Ленина д.1',1),
('Компания 2','ул.Восстания д.2',2),
('Компания 3','ул.Центральная д.3',3),
('Компания 4','ул.Казакова д.4',4),
('Компания 5','ул.Гаврилова д.5',5);

-- для таблицы хранилища
insert into storages(name) values
('Южное'),
('Центральное'),
('Северное'),
('Западное'),
('Восточное');

-- для таблицы поставки (income)
insert into income(ddate,ndoc,client,storage) values
('01.10.2013',100,1,1),
('02.02.2013',101,2,2),
('03.02.2013',102,3,2),
('04.02.2013',103,4,1),
('04.02.2013',104,5,2);

-- категории товаров (goods_groups)
insert into goods_groups(id,name,parent) values
(1,'Строительные',1),(2,'Хозяйственные',2),
(3,'Дерево',1),(4,'Не дерево',1),
(5,'Пакетики',2),(6,'Ведра',2);

-- товары
insert into goods(name,weight,length,height,width,g_group) values
('Палка',10,10,10,10,3),
('Кирпич',20,20,20,20,4),
('Доска',30,30,30,30,3),
('Пакет',40,40,40,40,5),
('Ведро',50,50,50,50,6);

-- таблица приходов ("Поступление товара на склад")
insert into incgoods(id,subid,volume,price,good) values
(1,1,50,100,1),
(1,2,40,200,2),
(1,3,20,300,3),
(2,1,30,400,1),
(3,1,20,200,2),
(4,1,40,300,3),
(5,1,50,100,4);

-- bank_recept
insert into bank_recept(ddate,summ,client) values
('02.11.2013',800,1),
('02.12.2013',1000,2),
('02.10.2013',500,3),
('02.12.2013',900,4),
('02.12.2013',800,5);


-- cassa_recept
insert into cassa_recept(ddate,summ,client) values
('02.10.2013',700,5),
('02.10.2013',800,4),
('02.10.2013',600,3),
('02.10.2013',500,2),
('02.10.2013',900,1);

-- recept - расход со склада за продажу клиентам
insert into recept(ddate,ndoc,client,storage) values
('03.01.2013',106,1,1),
('03.02.2013',107,2,2),
('03.01.2013',108,3,3),
('03.02.2013',109,4,4),
('03.03.2013',110,5,5);


-- строки расхода за продажу (recgoods)
insert into recgoods(id,subid,volume,price,good) values
(1,1,5,100,1),
(1,2,10,100,2),
(2,1,5,100,3),
(2,2,5,100,4),
(3,1,25,70,5),
(3,2,14,50,1),
(4,1,20,70,2),
(4,2,5,300,3),
(5,1,10,200,4);


-- bank_income - расплата за продажу через банк
insert into bank_income(ddate,summ,client) values
('04.01.2013',500,1),
('04.02.2013',500,2),
('04.01.2013',1500,3),
('04.02.2013',1400,4),
('04.03.2013',1000,5);

-- cassa_income - расплата за продажу через кассу
insert into cassa_income(ddate,summ,client) values
('05.01.2013',900,1),
('05.02.2013',1000,2),
('05.01.2013',450,3),
('05.02.2013',1400,4),
('05.03.2013',2000,5);


К сообщению приложен файл. Размер - 20Kb
14 ноя 13, 03:35    [15125408]     Ответить | Цитировать Сообщить модератору
 Re: Почему не работает процедура?  [new]
Ruuu
Member

Откуда: Иркутск
Сообщений: 4272
RandomName,

отлаживать не пробовали?
14 ноя 13, 03:53    [15125414]     Ответить | Цитировать Сообщить модератору
 Re: Почему не работает процедура?  [new]
RandomName
Member

Откуда:
Сообщений: 21
Ruuu
RandomName,

отлаживать не пробовали?


Что вы имеете ввиду? Я честно говоря в Server Manager новичок и не знаю многих местных функций.
Мне интересно как такое вообще может быть что считывает все строки кроме последней? Может какая-то проблема с курсорами или циклами? Server Manager ошибок не находит, процедура добавляется и выполняется, где-то в коде что-то не учёл.

Вот еще инфа по базе если понадобится

+
Поставка товара на определённый склад от определенного клиента регистрируется в таблице income. Поставка состоит из строк(incgoods). При этом income.id = incgoods.id, а incgoods.subid – это номер строки в данной поставке. В строке указывается товар(goods), количество (volume) и цена (price).
Товары хранятся в таблице goods, для них указываются размеры и вес. Товары сгруппированы в группы(goods_groups). Группы товаров организованы в иерархическое дерево.
Фирма должна расплатиться за товар, который она получила по поставке от клиента. Деньги клиенту перечисляются либо через банк (bank_recept), либо платятся через кассу (cassa_recept).
Фирма продает товар на складе своим клиентам. Это событие регистрируется как расход со склада (recept). Расход состоит их строк (recgoods). При этом recgoods.id = recept.id, а recgoods.subid – это номер строки в данном расходе. В строке указывается товар(goods), количество (volume) и цена (price).
Клиенты расплачиваются за купленный у фирмы товар. Деньги поступает либо через банк (bank_income), либо в кассу (cassa_income).
14 ноя 13, 05:15    [15125429]     Ответить | Цитировать Сообщить модератору
 Re: Почему не работает процедура?  [new]
Ruuu
Member

Откуда: Иркутск
Сообщений: 4272
RandomName,
для SQL-Server 2008+
Запуск отладчика Transact-SQL
Пошаговое выполнение кода Transact-SQL
14 ноя 13, 06:26    [15125458]     Ответить | Цитировать Сообщить модератору
 Re: Почему не работает процедура?  [new]
StarikNavy
Member

Откуда: Москва
Сообщений: 2396
" почему-то не работает."
код ошибки/признак нерабочести самим придумать?
14 ноя 13, 09:22    [15125777]     Ответить | Цитировать Сообщить модератору
 Re: Почему не работает процедура?  [new]
Glory
Member

Откуда:
Сообщений: 104760
RandomName
Мне интересно как такое вообще может быть что считывает все строки кроме последней?

Как написали код, так он и работает

Variables may be used as part of the select_statement that declares a cursor. Cursor variable values do not change after a cursor is declared. In SQL Server version 6.5 and earlier, variable values are refreshed every time a cursor is reopened.

Так что ваши вложенные курсоры написаны неправильно

ЗЫ
И вообще вся задача решаеьтся без курсоров
14 ноя 13, 10:03    [15125926]     Ответить | Цитировать Сообщить модератору
 Re: Почему не работает процедура?  [new]
RandomName
Member

Откуда:
Сообщений: 21
StarikNavy,

А глаза разуть?
14 ноя 13, 16:32    [15129761]     Ответить | Цитировать Сообщить модератору
 Re: Почему не работает процедура?  [new]
RandomName
Member

Откуда:
Сообщений: 21
Glory,

А конкретно в моей процедуре где можно увидить проявление этой ошибки?
14 ноя 13, 16:53    [15129930]     Ответить | Цитировать Сообщить модератору
 Re: Почему не работает процедура?  [new]
locky
Member

Откуда: Харьков, Украина
Сообщений: 62034
RandomName
Glory,

А конкретно в моей процедуре где можно увидить проявление этой ошибки?


втут
set @loop1=0;--внутренний цикл
open cur;

на каждый момент open курсор будет открывать для @client, вычитанного последним в dd_cur, а не cur_inc
14 ноя 13, 16:57    [15129969]     Ответить | Цитировать Сообщить модератору
 Re: Почему не работает процедура?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31444
RandomName
А конкретно в моей процедуре где можно увидить проявление этой ошибки?
У вас странный стиль организации циклов.

Вы устанавливаете:
set @loop2=@@FETCH_STATUS;
После этого даже если @@FETCH_STATUS != 1, и в переменных @client и @summIn cлучайные числа, вы преспокойно используете эти числа для очередной итерации цикла.

Лучше отказаться от всяких @loop1 и @loop2 и использовать классические break и continue, тогда вероятность ошибок будет меньше.

Или ещё лучше отказаться от курсоров и циклов и сделать операции одним/несколькими запросами.
14 ноя 13, 17:08    [15130068]     Ответить | Цитировать Сообщить модератору
 Re: Почему не работает процедура?  [new]
RandomName
Member

Откуда:
Сообщений: 21
Хорошо спасибо, буду разбираться.
А почему вот в этом куске считывает только 4 строки из 5
go
create procedure fill_table2 as
begin
declare @id int,
        @client int,
		@summ integer,
		@loop1 integer;

declare dd_cur cursor for
  select recgoods.id,recept.client, SUM(recgoods.price*recgoods.volume),recept.ddate
  from recept join recgoods on recept.id=recgoods.id
  group by recgoods.id, recept.client,recept.ddate

set @loop1=0;
open dd_cur;
while @loop1=0 --цикл на заполнение deb_dolg
  begin
  declare @recept int,
          @ddate datetime;
  FETCH NEXT FROM dd_cur INTO @recept, @client, @summ,@ddate;--следующая строчка из select
  set @loop1=@@FETCH_STATUS;
    insert into deb_dolg(client,recept,summ,ddate)
          values (@client,@recept,@summ,@ddate);--заносим данные
  set @loop1=@@FETCH_STATUS;		  
  end
close dd_cur;
deallocate dd_cur;
14 ноя 13, 20:14    [15131151]     Ответить | Цитировать Сообщить модератору
 Re: Почему не работает процедура?  [new]
RandomName
Member

Откуда:
Сообщений: 21
Вернее по отладке считываются все 5 но заносятся только 4
14 ноя 13, 20:15    [15131156]     Ответить | Цитировать Сообщить модератору
 Re: Почему не работает процедура?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31444
RandomName
А почему вот в этом куске считывает только 4 строки из 5

2 раза зачем присваивать значение переменной - для надёжности? :-)
set @loop1=@@FETCH_STATUS;
set @loop1=@@FETCH_STATUS;

Если запрос
select recgoods.id,recept.client, ...
возвращает 5 строк, то очевидно цикл выполнится 6 раз.

Да вы отладьте просто, дольше спрашивать, чем поставить отладочный вывод после первого set @loop1=@@FETCH_STATUS

А после объявления курсора поставьте этот запрос, что бы понимать, что там за выборка.
14 ноя 13, 20:19    [15131177]     Ответить | Цитировать Сообщить модератору
 Re: Почему не работает процедура?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31444
RandomName
Вернее по отладке считываются все 5 но заносятся только 4
Как это, INSERT выполняется, но записи в базе не появляется, и ошибки тоже? Так не бывает.
14 ноя 13, 20:23    [15131198]     Ответить | Цитировать Сообщить модератору
 Re: Почему не работает процедура?  [new]
RandomName
Member

Откуда:
Сообщений: 21
alexeyvg,

Как-то так, причем если взять и выполнить только тут кусок, без остального кода то заполнятся все 5 + 5ая строка второй раз
14 ноя 13, 20:38    [15131297]     Ответить | Цитировать Сообщить модератору
 Re: Почему не работает процедура?  [new]
RandomName
Member

Откуда:
Сообщений: 21
Вот еще раз изучил отладку полной процедуры,
Считывает 1ю строку
Заносит 1ю строку
loop=0
Считывает 2ю строку
Заносит 2ю строку
loop=0
Считывает 3ю строку
Заносит 3ю строку
loop=0
Считывает 4ю строку
Заносит 4ю строку
loop=0
Считывает 5ю строку
Заносит 5ю строку
loop=0
Считывает 5ю строку
Заносит 5ю строку
loop=-1

Хоть 5ая строка считалась и занеслась 2 раза она всеравно не появляется в таблице.
14 ноя 13, 21:05    [15131429]     Ответить | Цитировать Сообщить модератору
 Re: Почему не работает процедура?  [new]
RandomName
Member

Откуда:
Сообщений: 21
И даже так заполняет только 4
go
begin
declare @id int,
        @client int,
		@summ integer,
		@loop1 integer;

declare dd_cur cursor for
  select recgoods.id,recept.client, SUM(recgoods.price*recgoods.volume),recept.ddate
  from recept join recgoods on recept.id=recgoods.id
  group by recgoods.id, recept.client,recept.ddate

set @loop1=7;
open dd_cur;
while @loop1>0 --цикл на заполнение deb_dolg
  begin
  declare @recept int,
          @ddate datetime;
  FETCH NEXT FROM dd_cur INTO @recept, @client, @summ,@ddate;--следующая строчка из select
    insert into deb_dolg(client,recept,summ,ddate)
          values (@client,@recept,@summ,@ddate);--заносим данные
  set @loop1=@loop1-1;		  
  end
close dd_cur;
deallocate dd_cur;
14 ноя 13, 21:22    [15131511]     Ответить | Цитировать Сообщить модератору
 Re: Почему не работает процедура?  [new]
RandomName
Member

Откуда:
Сообщений: 21
Так, первую часть процедуры переделал на безкурсорну/циклическую, как и говорили
go
begin
declare @id int,
        @client int,
		@summ integer,
		@loop1 integer;
		@summInc integer;
BEGIN TRAN
	insert into deb_dolg(client,recept,summ,ddate)
	select recgoods.id,recept.client, SUM(recgoods.price*recgoods.volume),recept.ddate
    from recept join recgoods on recept.id=recgoods.id
    group by recgoods.id, recept.client,recept.ddate
COMMIT TRAN 


Но что делать со второй частью где начинается пересчёт таблицы, ведь там без циклов не обойтись
14 ноя 13, 21:38    [15131572]     Ответить | Цитировать Сообщить модератору
 Re: Почему не работает процедура?  [new]
invm
Member

Откуда: Москва
Сообщений: 9412
RandomName
ведь там без циклов не обойтись
Все начинающие так думают.
https://www.sql.ru/forum/1018035/zadachka-pro-yabloki-ili-sliyanie-2h-tablic
14 ноя 13, 21:45    [15131593]     Ответить | Цитировать Сообщить модератору
 Re: Почему не работает процедура?  [new]
RandomName
Member

Откуда:
Сообщений: 21
invm,

Спасибо! Разбираюсь, пока только сделал временные таблицы, и не понимаю что делать дальше..


if @@trancount > 0
 rollback;
 
begin tran;

declare @p table (p_client integer, p_summ integer, subid integer identity, primary key (subid));
declare @g table (g_recept integer, g_summ integer, id integer, subid integer identity, primary key (subid));

insert into @p (p_client,p_summ)
	((select client, summ from bank_income)
    union all
    (select client, summ from cassa_income))
 
insert into @g(g_recept, g_summ, id)
    select recept, summ, id
    from deb_dolg
    order by ddate;


надо теперь еще разобраться в задаче с яблоками, не пойму где прописывать условия и что при этих условиях делать
14 ноя 13, 22:29    [15131744]     Ответить | Цитировать Сообщить модератору
 Re: Почему не работает процедура?  [new]
RandomName
Member

Откуда:
Сообщений: 21
Вобщем с яблоками не разобрался!
Решил вернуться к старой процедуре, дополнив её заполнением таблицы без курсора, и исправив ошибки.
go
begin
declare @id int,
        @client int,
		@summ integer,
		@loop1 int,
		@loop2 int, 
		@summInc integer,
		@clientdeb int, 
		@rec int, 
		@s integer;

BEGIN TRAN
	insert into deb_dolg(client,recept,summ,ddate)
	select recept.client, recgoods.id,SUM(recgoods.price*recgoods.volume),recept.ddate
    from recept join recgoods on recept.id=recgoods.id
    group by recgoods.id, recept.client,recept.ddate
COMMIT TRAN

declare cur_inc cursor for
    ((select client, summ from bank_income)
    union all
    (select client, summ from cassa_income))
	order by client;
    
declare cur cursor local for
    select client, recept, summ, id
    from deb_dolg
    order by client, ddate;
    
-----------цикл заполнения таблицы--------
set @loop2=0;--внешний цикл
while @loop2=0 --проход по приходам
  begin
  open cur_inc;-- открыаем курсоры
  FETCH NEXT FROM cur_inc INTO @client, @summInc;--следующая строчка из select
  set @loop2=@@FETCH_STATUS;
  set @loop1=0;--внутренний цикл
  while @loop1=0 --проход по расходам
	begin	
		open cur;
		FETCH NEXT FROM cur INTO @clientdeb, @rec, @s, @id;
		set @loop1=@@FETCH_STATUS;--статус цикла
		if @client!=@clientdeb --Если клиент 2го не равен 1го
			begin
				set @loop1=1;
			end
		else if @summInc=@s --Если Приход равен Долгу
			begin
				DELETE FROM deb_dolg WHERE id=@id;
				set @summInc=0;
				update deb_dolg set summ=@SummInc
				where id=@id;
				set @loop1=1;
			end
		else if @summInc > @s --Если Приход больше Долга
			begin
				set @summInc=@summInc-@s;
				update deb_dolg set summ=0
				where id=@id;
			end
		else -- Если Приход меньше Долга
			begin
				set @s=@s-@summInc;
				update deb_dolg set summ=@s
				where id=@id;
				set @loop1=1;
			end
    end--внутренний цикл
	close cur;
  end--внешний цикл
  close cur_inc; -- закрываем курсор
  deallocate cur;
  deallocate cur_inc;
end 


Теперь работает только первый FETCH, а второй он вообще игнорирует, смотрел в отладчике, он по нему проходит и не заносит значения во временные переменные.
Суть такая что надо взять Сумму оплаты из первый таблицы, и вычесть её из долга во второй таблице, и если долг не покрыт и есть еще платежи от этого клиента, то взять следующий его платёж из первой таблицы и вычесть из остатка во второй, если долг не покрыт и платежей от него больше нет то просто оставить в таблице долга его долг, а если покрыли то сделать тоже самое со след. клиентом. И фишка еще в том что у клиента может быть 2 разных долга по двум разным заказам, и нельзя долги объединять.

Была еще одна попытка без циклов:

go
create procedure fill_table_0 as
begin
declare cur cursor for
  with inc as
	(
		select t.client, sum(t.summ) as summ
		from
		((select client, summ from bank_income)
		union all
		(select client, summ from cassa_income)) t
		group by client
	),
	tbl as
	(
		select recgoods.id,
		recept.client,
		SUM(recgoods.price*recgoods.volume) as summ,
		recept.ddate
		from recept join recgoods on recept.id=recgoods.id
		group by recgoods.id, recept.client,recept.ddate
	)
	select tbl.client,tbl.id as recept,
  case when tbl.summ>inc.summ then tbl.summ-inc.summ else 0.0 end,
  tbl.ddate
	from inc inner join tbl
	on inc.client=tbl.client;
  
declare @recept int,
  @client int,
	@summ decimal,
  @date datetime,
	@loop integer;

truncate table deb_dolg;  

open cur;
while @@FETCH_STATUS=0
  begin
  fetch next from cur into @client, @recept, @summ, @date;
  if @@FETCH_STATUS=0
    insert into deb_dolg(client,recept,summ,ddate)
    values (@client,@recept,@summ,@date);
  end
  close cur;
  deallocate cur;
end 

Кончилось тем что не добавляет новый долг в таблицу долгов если небыло оплаты от клиента, и вычитает из всех долгов клиента один платёж.
15 ноя 13, 04:53    [15132788]     Ответить | Цитировать Сообщить модератору
 Re: Почему не работает процедура?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31444
RandomName
Теперь работает только первый FETCH, а второй он вообще игнорирует
Теперь курсоры у вас открываются внутри их циклов :-)
15 ноя 13, 09:04    [15133287]     Ответить | Цитировать Сообщить модератору
 Re: Почему не работает процедура?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31444
alexeyvg
RandomName
Теперь работает только первый FETCH, а второй он вообще игнорирует
Теперь курсоры у вас открываются внутри их циклов :-)
Напишите уже нормально, без этих ваших @loop

open cur_inc;    -- открыаем курсор
while 1=1	-- внешний цикл
begin
    FETCH NEXT FROM cur_inc INTO @client, @summInc;
    if @@FETCH_STATUS <> 0
        break;

    open cur;         -- открыаем курсор
    while 1=1         -- внутренний цикл
    begin
        FETCH NEXT FROM cur INTO @clientdeb, @rec, @s, @id;
        if @@FETCH_STATUS <> 0
            break;
        .............
        .............
        .............
    end
end
15 ноя 13, 09:10    [15133321]     Ответить | Цитировать Сообщить модератору
 Re: Почему не работает процедура?  [new]
RandomName
Member

Откуда:
Сообщений: 21
alexeyvg,

Спасибо, поправил, вот что получилось:

-----------цикл заполнения таблицы--------
open cur_inc;-- открыаем курсоры
open cur;
while 1=1 --проход по приходам
  begin
    FETCH NEXT FROM cur INTO @clientdeb, @rec, @s, @id;
  if @@FETCH_STATUS<>0
  break;
--  open cur;
  while 1=1 --проход по расходам
    begin
	FETCH NEXT FROM cur_inc INTO @client, @summInc;--следующая строчка из select
    if @@FETCH_STATUS <> 0
	break;
	if @client<>@clientdeb break;
    else if @summInc=@s --приход равен долгу
      begin
      update deb_dolg set summ=0
	  where id=@id;
      set @summInc=0;
      end
    else if @summInc > @s --приход больше долга
      begin
      set @summInc=@summInc-@s;
      update deb_dolg set summ=0
	  where id=@id;
      end
    else -- приход меньше расхода
			begin
				set @s=@s-@summInc;
				update deb_dolg set summ=@s
				where id=@id;
			end
    end--внутренний цикл
  --close cur;
  end--внешний цикл
  close cur;
  close cur_inc; -- закрываем курсор
  deallocate cur;
  deallocate cur_inc;
end 


Теперь циклы работают правильно, поэтому появилась новая проблема, стоит условие после второго считывания курсора "если Клиент с задолженностью != клиенту который оплатил то прерываем внутренний цикл" Тоесть получается так:

Считали клиента (1) и сумму с долгом
Считали клиента (1) и сумму оплаты
Клиент оплаты (1) = клиенту с долгом (1)
Производим расчёт
Считали след. клиента (1) и сумму оплаты
Клиент оплаты (1) = клиенту с долгом (1)
Производим расчёт
Считали след клиента (2) и сумму оплаты
Клиент оплаты (2) != клиенту с долгом (1)
прерывание внутреннего цикла
Считали след. клиента (2) и сумму с долгом
Считали след клиента (3) и сумму оплаты
Клиент оплаты (3) != клиенту с долгом (2)
прерывание внутреннего цикла
и тд, тоесть расчет производится правильно только для первого клиента, как можно это исправить?
15 ноя 13, 20:40    [15139263]     Ответить | Цитировать Сообщить модератору
 Re: Почему не работает процедура?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31444
RandomName
тоесть расчет производится правильно только для первого клиента, как можно это исправить?

Думаю, начать нужно не с написания кода, а в написании (придумывании) алгоритма (на русском языке).
15 ноя 13, 22:02    [15139542]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить