Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / IBM DB2, WebSphere, IMS, U2 [игнор отключен] [закрыт для гостей] / Как сделать, чтобы процедура возращала результат каждую итерацию цикла? / 17 сообщений из 17, страница 1 из 1
23.10.2009, 09:58
    #36268136
Павел Гужанов
Павел Гужанов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как сделать, чтобы процедура возращала результат каждую итерацию цикла?
Создаю вот такую процедуру:

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
CREATE PROCEDURE PROC1
  (IN D DATE,
    OUT M INTEGER,
    OUT F INTEGER,
    OUT SUMMA INTEGER)
  SPECIFIC PROC1
  READS SQL DATA
  NOT DETERMINISTIC
  LANGUAGE SQL
  ------------------------------------------------------------------------
-- SQL Procedure                                                        
------------------------------------------------------------------------
BEGIN
DECLARE I INT;
SET I =  1 ;
WHILE I<= 100  DO
	SELECT COUNT(DISTINCT(INSURANCE_NUMBER)) INTO M 
    FROM TABLE1
    WHERE SEX='M' 
    AND INSURANCE_NUMBER IS NOT NULL
    AND BIRTH_DATE BETWEEN DATE(D) -  1  year AND DATE(D)
    AND END_DATE IS NULL;
    
    SELECT COUNT(DISTINCT(INSURANCE_NUMBER)) INTO F 
    FROM TABLE1
    WHERE SEX='F' 
    AND INSURANCE_NUMBER IS NOT NULL
    AND BIRTH_DATE BETWEEN DATE(D) -  1  year AND DATE(D)
    AND END_DATE IS NULL;
     
	SET SUMMA = M + F;
    
    SET D = DATE(D) -  1  year;
    
    SET I = I +  1 ;
    
    RETURN;

END WHILE;
END

Я предположил, что если RETURN находится внутри цикла, то и значения будут выводиться каждую итерацию, но при выполнении процедуры я увидел только один результат, предположительно, последний. А как сделать, чтобы выводились все результаты?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.10.2009, 10:29
    #36268232
Vladimir Kiselev
Vladimir Kiselev
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как сделать, чтобы процедура возращала результат каждую итерацию цикла?
Павел Гужанов,

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

Ну и два селекта заменить на один. Рекомендую почитать CookBook, там есть примеры как посчитать в одном цикле разные суммы, в Вашем случае можно считать М и Ж в одном запросе.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.10.2009, 14:35
    #36269187
Павел Гужанов
Павел Гужанов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как сделать, чтобы процедура возращала результат каждую итерацию цикла?
Исправил процедуру вот так:

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
CREATE OR ALTER PROCEDURE PVS
  (IN D DATE,
    OUT M INTEGER,
    OUT F INTEGER
    )
  SPECIFIC PVS
  READS SQL DATA
  NOT DETERMINISTIC
  LANGUAGE SQL
BEGIN
	DECLARE I INT;
	DECLARE C1 CURSOR WITH RETURN FOR
	SELECT DISTINCT
 	(SELECT COUNT(DISTINCT(INSURANCE_NUMBER)) 
        FROM NATURAL 
        WHERE SEX='M'
  	AND INSURANCE_NUMBER IS NOT NULL
    	AND BIRTH_DATE BETWEEN DATE(D) -  1  year AND DATE(D)
    	AND END_DATE IS NULL),
 	(SELECT COUNT(DISTINCT(INSURANCE_NUMBER)) 
        FROM NATURAL 
        WHERE SEX='F' 
 	AND INSURANCE_NUMBER IS NOT NULL
    	AND BIRTH_DATE BETWEEN DATE(D) -  1  year AND DATE(D)
    	AND END_DATE IS NULL)
 	FROM NATURAL;
        
    SET I =  1 ;
        
    OPEN C1;
	WHILE I<= 100  DO
    	FETCH c1 INTO M,F;
    	SET D = DATE(D) -  1  year;
    	SET I = I +  1 ;
	END WHILE;
	CLOSE C1;
    RETURN;
END

Получаю вот такой результат:

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
CALL PVS( '23.10.2009', ?, ?);
completed successfully.

  Значение выходных параметров
  --------------------------
  Имя параметра   : M
  Значение парам. :  0 

  Имя параметра   : F
  Значение парам. :  3 


  Набор результатов  1 
  --------------

   1             2           
  ----------- -----------

   0  записей выбрано.

  Статус возврата =  0

т.е. опять возвращается только последнее значение. Догадываюсь, что что-то не так сделал с курсором, но что?

При объявлении курсора пробовал использовать конструкцию

Код: plaintext
DECLARE C1 CURSOR WITH RETURN FOR


и

Код: plaintext
DECLARE C1 CURSOR FOR

На результате это не отразилось.

Что мне сделать, чтобы получить все 100 результатов?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.10.2009, 14:43
    #36269230
buven
buven
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как сделать, чтобы процедура возращала результат каждую итерацию цикла?
Так вам таблицу нужно вернуть? Или запись?

Проводить жизнь в ожидании мессии, который придёт и спасёт мир, всё-равно, что ждать палку в тетрисе.
Даже если и появится, то ты к тому времени наберёшь такую гору дерьма, что те будет уже абсолютно пох... :)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.10.2009, 14:45
    #36269233
Павел Гужанов
Павел Гужанов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как сделать, чтобы процедура возращала результат каждую итерацию цикла?
да, всю таблицу результатов, 100 штук
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.10.2009, 15:01
    #36269287
buven
buven
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как сделать, чтобы процедура возращала результат каждую итерацию цикла?
А в том курсоре, что у вас написан, вы фетчите 100 раз разные записи в структуру, которая у вас является строкой вида (M,F), что вы определили зписью
Код: plaintext
1.
out M integer,
out F integer


Проводить жизнь в ожидании мессии, который придёт и спасёт мир, всё-равно, что ждать палку в тетрисе.
Даже если и появится, то ты к тому времени наберёшь такую гору дерьма, что те будет уже абсолютно пох... :)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.10.2009, 15:10
    #36269320
buven
buven
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как сделать, чтобы процедура возращала результат каждую итерацию цикла?
А вообще такую задачу модно решить и без курсора.
Раздел common-table-expression


Проводить жизнь в ожидании мессии, который придёт и спасёт мир, всё-равно, что ждать палку в тетрисе.
Даже если и появится, то ты к тому времени наберёшь такую гору дерьма, что те будет уже абсолютно пох... :)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.10.2009, 15:10
    #36269322
Павел Гужанов
Павел Гужанов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как сделать, чтобы процедура возращала результат каждую итерацию цикла?
А как в таком случае мне определить выходной параметр, чтобы в него выгрузить всю таблицу результатов?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.10.2009, 15:11
    #36269324
buven
buven
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как сделать, чтобы процедура возращала результат каждую итерацию цикла?
По Фрейду опечатка :)
В предыдущем посте модно=можно.

Проводить жизнь в ожидании мессии, который придёт и спасёт мир, всё-равно, что ждать палку в тетрисе.
Даже если и появится, то ты к тому времени наберёшь такую гору дерьма, что те будет уже абсолютно пох... :)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.10.2009, 15:18
    #36269349
buven
buven
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как сделать, чтобы процедура возращала результат каждую итерацию цикла?
Код: plaintext
1.
2.
3.
4.
5.
6.
......
(IN D DATE,
    OUT result table(M INTEGER,F INTEGER) )

......

FETCH c1 INTO result;

В синтаксисе не уверен, проверить нет возможности.

И все же настоятельно рекомендую посмотреть в сторону рекурсии.

Проводить жизнь в ожидании мессии, который придёт и спасёт мир, всё-равно, что ждать палку в тетрисе.
Даже если и появится, то ты к тому времени наберёшь такую гору дерьма, что те будет уже абсолютно пох... :)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.10.2009, 15:44
    #36269443
Павел Гужанов
Павел Гужанов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как сделать, чтобы процедура возращала результат каждую итерацию цикла?
buven
Код: plaintext
1.
2.
......
    OUT result table(M INTEGER,F INTEGER) )



Синтаксис не такой, а вот найти правильный синтаксис не получается.... :о(
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.10.2009, 16:39
    #36269616
buven
buven
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как сделать, чтобы процедура возращала результат каждую итерацию цикла?
Похоже процедура здесь не может возвращать наборы.
И я не уверен, можно ли использовать курсоры в функциях, которые это могут.
Поэтому либо временная таблица, либо CTE+функция, если курсоры нельзя использовать в функциях.

Проводить жизнь в ожидании мессии, который придёт и спасёт мир, всё-равно, что ждать палку в тетрисе.
Даже если и появится, то ты к тому времени наберёшь такую гору дерьма, что те будет уже абсолютно пох... :)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.10.2009, 16:57
    #36269668
cz321
cz321
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как сделать, чтобы процедура возращала результат каждую итерацию цикла?
Читайте документацию. Процедура может возвращать result set.

Последний оператор в процедуре должен быть

Open cur1;
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.10.2009, 17:06
    #36269701
Favn
Favn
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как сделать, чтобы процедура возращала результат каждую итерацию цикла?
buvenПохоже процедура здесь не может возвращать наборы.
И я не уверен, можно ли использовать курсоры в функциях, которые это могут.
Поэтому либо временная таблица, либо CTE+функция, если курсоры нельзя использовать в функцияхКак раз процедура может возвращать курсоры, а функция - запись или таблицу. Ссылку на возвращающую курсор функцию уже давали, табличные функции - здесь .
2 Павел Гужанов:
По поводу SQL PL почитайте DB2 SQL PL Essential Guide . Он, правда, для 9.7 прилично устарел, но для начала - вполне сойдет.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.10.2009, 10:52
    #36271887
Vladimir Kiselev
Vladimir Kiselev
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как сделать, чтобы процедура возращала результат каждую итерацию цикла?
Павел Гужанов,

Вот тупо набросал, но надо править, главное принцип:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
CREATE PROCEDURE PVS(IN D DATE)
  SPECIFIC SQL_PVS
  READS SQL DATA
  NOT DETERMINISTIC
  LANGUAGE SQL
BEGIN
  DECLARE C1 CURSOR WITH RETURN FOR
  WITH DATEGEN(STARTDATE,ENDDATE) AS
  (
    VALUES( DATE(D) -  1  year, DATE(D) -  1  day )
    UNION ALL
    SELECT DATE(STARTDATE) -  1  year,DATE(ENDDATE) -  1  year -  1  day
      FROM DATEGEN
     WHERE STARTDATE > DATE(D) -  100  years
  ),
  COUNTERS(STARTDATE,MCNT,FCNT,ALLCNT) AS
  (
  SELECT 
    D.STARTDATE,
    SUM(CASE WHEN N.SEX= 'M' THEN  1  ELSE  0  END) AS MCNT, 
    SUM(CASE WHEN N.SEX= 'F' THEN  1  ELSE  0  END) AS FCNT, 
    SUM( 1 ) AS ALLCNT
  FROM 
    NATURAL N, DATEGEN D
  WHERE 
    N.INSURANCE_NUMBER IS NOT NULL AND 
    N.BIRTH_DATE BETWEEN D.STARTDATE AND D.ENDDATE AND 
    N.END_DATE IS NULL
  GROUP BY D.STARTDATE)
  SELECT * FROM COUNTERS;

  OPEN C1;
  RETURN;
END@

DATEGEN - это "генератор периодов" (стр.365 CookBook V9.1)
COUNTERS - конечный результат

DISTINCT INSURANCE_NUMBER я не учитывал.

вызывал так:

Код: plaintext
1.
CALL PVS(CURRENT DATE);
работает.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.10.2009, 11:03
    #36271910
Mark Barinstein
Mark Barinstein
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как сделать, чтобы процедура возращала результат каждую итерацию цикла?
Павел Гужанов,

Вот ваш запрос:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
  with y(i) as (values  0  union all select i+ 1  from y where i< 99 )
  select
    d - y.i years as d
  , count(distinct(case when sex = 'M' then n.insurance_number else cast(null as int) end)) M
  , count(distinct(case when sex = 'F' then n.insurance_number else cast(null as int) end)) F
  , count(distinct(case when sex = 'M' then n.insurance_number else cast(null as int) end))
  + count(distinct(case when sex = 'F' then n.insurance_number else cast(null as int) end)) SUMMA
  from natural n, y
  where n.insurance_number is not null
  and n.birth_date between d - (y.i +  1 ) years and d - y.i years
  and n.end_date is null and n.sex in ('M', 'F')
  group by d - y.i years
Если схематично обозначить его как QUERY, то его можно использовать в процедуре:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
create procedure natural(d date)
specific pvs
dynamic result sets  1 
reads sql data
begin
 declare c1 cursor with return for QUERY;
 open c1;
end@
или функции:
Код: plaintext
1.
2.
3.
create function natural(d date)
returns table (d date, m int, f int, summa int)
specific pvs2
return QUERY@
функцию можно использовать так:
Код: plaintext
select * from table(natural(date('2009-10-26'))) t
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.10.2009, 11:21
    #36271945
Павел Гужанов
Павел Гужанов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как сделать, чтобы процедура возращала результат каждую итерацию цикла?
Vladimir KiselevПавел Гужанов,

Вот тупо набросал, но надо править, главное принцип:


Спасибо, работает. Буду разбираться :о)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / IBM DB2, WebSphere, IMS, U2 [игнор отключен] [закрыт для гостей] / Как сделать, чтобы процедура возращала результат каждую итерацию цикла? / 17 сообщений из 17, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]