Форум пользователей MySQL

NDS · 01.06.2010 23:33:28

Уважаемые участники форума!
Прошу помощи в написании Хранимой Процедуры...
Задача:
Есть таблица DSR_PeriodsSet которая имеет поля ID Name PeriodID BeginPeriod EndPeriod ElementaryPeriodsLength и хранит наборы периодов следующего вида:
f8b53ed9-687b-8ddb-5d7d-7d5a749574f8 авыфавыф bd32eb68-2991-f3da-7a7c-c4e463c4f4ea 2010-04-15 06:32:28 2010-04-29 17:14:26
f8b53ed9-687b-8ddb-5d7d-7d5a749574f8 авыфавыф 8b4071c0-c724-c69e-f0e4-9c16e520fc26 2010-04-15 06:32:28 2010-04-29 17:14:26 10
f8b53ed9-687b-8ddb-5d7d-7d5a749574f8 авыфавыф e7b6f556-e8eb-9be5-8b3e-54f79a467ac9 2010-04-10 17:22:53 2010-04-22 08:41:25 15

Так же есть Хранимая Процедура getElementaryPeriods(beginPrd, endPrd, lengthPrd) которая возвращает таблицу с полями BeginElementaryPeriod EndElementaryPeriod следующего вида:
Call getElementaryPeriods('2010-04-15 06:32:28', '2010-04-29 17:14:26', 10);
2010-04-15 06:32:28 2010-04-15 06:40:00
2010-04-15 06:40:00 2010-04-15 06:50:00
2010-04-15 06:50:00 2010-04-15 07:00:00
2010-04-15 07:00:00 2010-04-15 07:10:00
2010-04-15 07:10:00 2010-04-15 07:20:00
2010-04-15 07:20:00 2010-04-15 07:30:00
2010-04-15 07:30:00 2010-04-15 07:40:00

Необходимо создать Хранимую Процедуру, которая будет создавать таблицу DSR_PeriodsByPeriodSetID которая будет содержать периоды данного набора периодов... При этом - если в поле ElementaryPeriodsLength стоит значение NULL - заносим в данную таблицу только значения BeginPeriod EndPeriod, а если в данном поле есть какое-либо значение длины элементарных периодов - необходимо занести все значение, которая вернет процедура getElementaryPeriods(beginPrd, endPrd, lengthPrd) (но... данная процедура реализована так, что при каждом вызове удаляет все значения в таблице ElementaryPeriods и заносит туда новые в зависимости от параметров... т.е. значения нужно брать из данной таблицы), при этом всем этим значениям присвоить ID данного набора...

Саму Хранимку я написал:

DELIMITER $$

DROP PROCEDURE IF EXISTS getPeriodsByPeriodSetID $$

CREATE PROCEDURE getPeriodsByPeriodSetID(IN periodSetID VARCHAR(255))

BEGIN

    -- ДЛЯ ОКОНЧАНИЯ ЦИКЛА (ОКОНЧАНИЕ КУРСОРА)

    Declare done integer default 0;

    DECLARE prdID VARCHAR( 255 );

    DECLARE beginPrd DATETIME;

    DECLARE endPrd DATETIME;

    DECLARE elemPrdLength INT( 11 );

    -- КУРСОР ДЛЯ ОБХОДА ТАБЛИЦЫ DSR_PeriodsSet

    Declare DSRPeriodsSetCursor Cursor

    for select PeriodID, BeginPeriod, EndPeriod, ElementaryPeriodsLength

        from DSR_PeriodsSet

    where ID = periodSetID;

    -- ОПРЕДЕЛЕНИЕ HANDLER'А ОКОНЧАНИЯ КУРСОРА

    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

    --СОЗДАЕМ ТЕБЛИЦУ ПЕРИОДОВ

    CREATE TABLE IF NOT EXISTS DSR_PeriodsByPeriodSetID

    (PeriodSetID VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,

     PeriodID VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,

     BeginPeriod VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,

     EndPeriod VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL

    ) TYPE=MyISAM;

    DELETE FROM DSR_PeriodsByPeriodSetID;

    --ОТКРЫВАЕМ КУРСОР ПО ПЕРИОДАМ ДАННОГО НАБОРА ПЕРИОДОВ

    Open DSRPeriodsSetCursor;

    WHILE done = 0 DO

        FETCH DSRPeriodsSetCursor INTO prdID, beginPrd, endPrd, elemPrdLength;

        IF elemPrdLength IS NULL THEN

            INSERT INTO DSR_PeriodsByPeriodSetID(PeriodSetID,

                                                 PeriodID,

                                                 BeginPeriod,

                                                 EndPeriod)

            select periodSetID,

                   prdID,

                   beginPrd,

                   endPrd;

        ELSE

            Call getElementaryPeriods(beginPrd, endPrd, elemPrdLength);

            INSERT INTO DSR_PeriodsByPeriodSetID(PeriodSetID,

                                                 PeriodID,

                                                 BeginPeriod,

                                                 EndPeriod)

            select periodSetID,

                   prdID,

                   (select BeginElementaryPeriod

                    from ElementaryPeriods),

                   (select BeginElementaryPeriod

                    from ElementaryPeriods);

        END IF;

    END WHILE;

    Close DSRPeriodsSetCursor;

    select *

    from DSR_PeriodsByPeriodSetID

    order by PeriodSetID, PeriodID, BeginPeriod, EndPeriod asc;

END $$

DELIMITER ;

Но при ее выполнения появляется ошибка Error code 1242, SQL state 21000: Subquery returns more than 1 row
т.е., я так понимаю, причина в данном фрагменте:

            select periodSetID,

                   prdID,

                   (select BeginElementaryPeriod

                    from ElementaryPeriods),

                   (select BeginElementaryPeriod

                    from ElementaryPeriods);

Я не знаю каким еще образом можно занести все значения из таблицы и при этом у двух других полей (periodSetID, prdID) оставить текущие значения)
Очень прошу помощи!! Возможно задача решается просто, но я не знаю решение... я в MySQL новичок...
Дипломная работа горит((( Помогите плиииз
Буду благодарен любому совету по данному вопросу.

Отредактированно NDS (01.06.2010 23:37:38)

LazY · 01.06.2010 23:48:38

Вы правильно определили источник проблемы.

Нужно

SELECT periodSetID, prdID, BeginElementaryPeriod, EndElementaryPeriod
FROM ElementaryPeriods

Если Вы пишете SELECT col1, col2, ..., [что-то еще] FROM table, Вы говорите "выбрать мне для каждой записи значение col1 (т.е. одну ячейку), col2 (тоже одну ячейку) и что-то еще". Подумайте, что получится, если [что-то еще] - это больше, чем одна ячейка.

NDS · 02.06.2010 00:01:54

Уважаемый, LazY!
Спасибо Вам за ответ!
Действительно... я изменил ошибочный фрагмент на данный:

            Call getElementaryPeriods(beginPrd, endPrd, elemPrdLength);

            INSERT INTO DSR_PeriodsByPeriodSetID(PeriodSetID,

                                                 PeriodID,

                                                 BeginPeriod,

                                                 EndPeriod)

            select periodSetID,

                   prdID,

                   BeginElementaryPeriod,

                   EndElementaryPeriod

            FROM  ElementaryPeriods;

И ошибка исчезла.
Но... при этом результат работы процедуры следующий:
2010-04-15 06:32:28 2010-04-15 06:40:00
2010-04-15 06:40:00 2010-04-15 06:50:00
2010-04-15 06:50:00 2010-04-15 07:00:00
2010-04-15 07:00:00 2010-04-15 07:10:00
2010-04-15 07:10:00 2010-04-15 07:20:00
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

(если указать тот ID, который я указал в первом сообщении)...
т.е. почему-то не обрабатывается значение NULL...
по идее - в результирующей таблице сначала должно быть значение:
2010-04-15 06:32:28 2010-04-29 17:14:26
т.к. у него ElementaryPeriodsLength - NULL
и уже потом должны быть записи из таблицы ElementaryPeriods, в которую процедура занесла значения получив параметры:
2010-04-15 06:32:28 2010-04-29 17:14:26 10

Отредактированно NDS (02.06.2010 00:10:45)

LazY · 02.06.2010 00:13:08

не обрабатывается значение NULL...

Вы уверены, что у Вас там именно NULL, а не пустая строка? (каким образом в таблицу NULL вставляли?)

NDS · 02.06.2010 00:20:09

Значение NULL я заношу в PHP-скрипте:

        $insertDataQuery .= "('".$currentPeriodsSetID."', "

                               ."'".$periodsSetName."', "

                               ."generateGUID(), "

                               ."'".$periods[$i]['BeginPeriod']."', "

                               ."'".$periods[$i]['EndPeriod']."', "

                               .($periods[$i]['ElementaryPeriodLength'] == NULL ?

                                 'NULL' :

                                 $periods[$i]['ElementaryPeriodLength']

                                )

                               .($i != count($periods) - 1 ?

                                 ")," :

                                 ");"

                                );

И среда разработки, при выборки значений из данной таблицы, точно определяет данную ячейку, как NULL...
Процедура getElementaryPeriods имеет следующий вид:

DELIMITER $$

DROP PROCEDURE IF EXISTS getElementaryPeriods $$

CREATE PROCEDURE getElementaryPeriods(IN beginInt DATETIME, IN endInt DATETIME, IN lengthPrd INT(11))

BEGIN

    DECLARE tempBeginInt DATETIME;

    DECLARE tempEndInt DATETIME;

    DECLARE beginSeconds INTEGER;

    DECLARE endSeconds INTEGER;

    DECLARE periodLengthSeconds INTEGER;

    DECLARE beginAddSeconds INTEGER;

    DECLARE endAddSeconds INTEGER;

    DECLARE exitWhile INTEGER;

    CREATE TABLE IF NOT EXISTS ElementaryPeriods

    (BeginElementaryPeriod DATETIME,

     EndElementaryPeriod DATETIME

    )TYPE=MyISAM;

    DELETE FROM ElementaryPeriods;

    set beginSeconds := 60 * MINUTE(beginInt) + SECOND(beginInt);

    set endSeconds := 60 * MINUTE(endInt) + SECOND(endInt);

    set periodLengthSeconds := 60 * lengthPrd;

    set beginAddSeconds := periodLengthSeconds -

                           beginSeconds % periodLengthSeconds;

    set endAddSeconds := 0;

    set tempBeginInt := beginInt;

    set exitWhile := 0;

    while not exitWhile do

        if beginAddSeconds <> 0 then

            set tempEndInt := DATE_ADD(tempBeginInt, INTERVAL beginAddSeconds SECOND);

            INSERT INTO ElementaryPeriods VALUES (tempBeginInt, tempEndInt);

            set tempBeginInt := tempEndInt;

            set beginAddSeconds := 0;

        end if;

        set tempEndInt := DATE_ADD(tempBeginInt, INTERVAL periodLengthSeconds SECOND);

        if DATE_ADD(tempEndInt, INTERVAL periodLengthSeconds SECOND) > endInt then

            while DATE_ADD(tempEndInt, INTERVAL endAddSeconds SECOND) <> endInt do

                 set endAddSeconds := endAddSeconds + 1;

            end while;

            set tempEndInt := DATE_ADD(tempEndInt, INTERVAL endAddSeconds SECOND);

            INSERT INTO ElementaryPeriods VALUES (tempBeginInt, tempEndInt);

            set exitWhile := 1;

        else

          INSERT INTO ElementaryPeriods VALUES (tempBeginInt, tempEndInt);

          set tempBeginInt := tempEndInt;

        end if;

        if tempEndInt = endInt then

            set exitWhile := 1;

        end if;

    end while;

    -- ИТОГОВАЯ ВЫБОРКА

    select *

    from ElementaryPeriods;

END $$

DELIMITER ;

Выборка:

select *

from DSR_PeriodsSet

where ElementaryPeriodsLength IS NULL;

Возвращает все записи, у которых в поле ElementaryPeriodsLength стоит значение NULL... т.е. данное значение определяется.

Отредактированно NDS (02.06.2010 00:24:47)

NDS · 02.06.2010 00:43:16

Все нормально... я разобрался.
данная ХП, почему-то, выводила значения итоговой выборки в ХП getElementaryPeriods()
-- ИТОГОВАЯ ВЫБОРКА
select *
from ElementaryPeriods;

я закомментировал данные строчки - и теперь выводит значения из таблицы DSR_PeriodsByPeriodSetID.
И так же убрал сортировку order by PeriodSetID, PeriodID, BeginPeriod, EndPeriod asc; из хранимой процедуры getPeriodsByPeriodSetID()
а то при выводе нарушается, собственно, сам смысл данной процедуры)))

Уважаемый LazY,
еще раз, спасибо за помощь!!

NDS · 02.06.2010 04:08:44

К сожалению я обнаружил ошибку в работе процедуры getPeriodsByPeriodSetID()... ((

select *

from DSR_PeriodsSet

where ID = '599f1a87-c93a-1312-1000-ea51d3790f8e';

Результат:
599f1a87-c93a-1312-1000-ea51d3790f8e месяца 2010 adedc6ee-8d2e-2e05-34ec-f91c22f56a36 2010-01-01 00:00:00 2010-01-31 23:59:59 NULL
599f1a87-c93a-1312-1000-ea51d3790f8e месяца 2010 b775efac-b989-b151-e3c6-7285f928cd81 2010-02-01 00:00:00 2010-02-28 23:59:59 NULL
599f1a87-c93a-1312-1000-ea51d3790f8e месяца 2010 ef8c1aeb-350e-0b8b-3b9a-54aa01763fc9 2010-03-01 00:00:00 2010-03-31 23:59:59 NULL
599f1a87-c93a-1312-1000-ea51d3790f8e месяца 2010 2c799d69-3485-b594-2ceb-cb1798cc0136 2010-04-01 00:00:00 2010-04-30 23:59:59 NULL

Call getPeriodsByPeriodSetID('599f1a87-c93a-1312-1000-ea51d3790f8e');

 

Результат:
599f1a87-c93a-1312-1000-ea51d3790f8e adedc6ee-8d2e-2e05-34ec-f91c22f56a36 2010-01-01 00:00:00 2010-01-31 23:59:59
599f1a87-c93a-1312-1000-ea51d3790f8e b775efac-b989-b151-e3c6-7285f928cd81 2010-02-01 00:00:00 2010-02-28 23:59:59
599f1a87-c93a-1312-1000-ea51d3790f8e ef8c1aeb-350e-0b8b-3b9a-54aa01763fc9 2010-03-01 00:00:00 2010-03-31 23:59:59
599f1a87-c93a-1312-1000-ea51d3790f8e 2c799d69-3485-b594-2ceb-cb1798cc0136 2010-04-01 00:00:00 2010-04-30 23:59:59
599f1a87-c93a-1312-1000-ea51d3790f8e 2c799d69-3485-b594-2ceb-cb1798cc0136 2010-04-01 00:00:00 2010-04-30 23:59:59

Почему-то дублируются последняя строчка...

т.е. цикл

    WHILE done = 0 DO

    ....

    END WHILE;

работает на одну итерацию больше =\
Я добавил в ХП переменную-счетчик

    set @temp := 0;

    -- ОТКРЫВАЕМ КУРСОР ПО ПЕРИОДАМ ДАННОГО НАБОРА ПЕРИОДОВ

    Open DSRPeriodsSetCursor;

    WHILE done = 0 DO

        FETCH DSRPeriodsSetCursor INTO prdID, beginPrd, endPrd, elemPrdLength;

        IF elemPrdLength IS NULL THEN

            INSERT INTO DSR_PeriodsByPeriodSetID(PeriodSetID,

                                                 PeriodID,

                                                 BeginPeriod,

                                                 EndPeriod)

            select periodSetID,

                   prdID,

                   beginPrd,

                   endPrd;

        ELSE

            Call getElementaryPeriods(beginPrd, endPrd, elemPrdLength);

            INSERT INTO DSR_PeriodsByPeriodSetID(PeriodSetID,

                                                 PeriodID,

                                                 BeginPeriod,

                                                 EndPeriod)

            select periodSetID,

                   prdID,

                   BeginElementaryPeriod,

                   EndElementaryPeriod

            from  ElementaryPeriods;

        END IF;

         set @temp := @temp + 1;

    END WHILE;

    Close DSRPeriodsSetCursor;

    select @temp as 'количество_итераций';

она, соответсвенно, выводит 5 итераций
хотя в наборе четыре периода...
ХП на данный момент:

DELIMITER $$

DROP PROCEDURE IF EXISTS getPeriodsByPeriodSetID $$

CREATE PROCEDURE getPeriodsByPeriodSetID(IN periodSetID VARCHAR(255))

BEGIN

    -- ДЛЯ ОКОНЧАНИЯ ЦИКЛА (ОКОНЧАНИЕ КУРСОРА)

    Declare done integer default 0;

    DECLARE prdID VARCHAR( 255 );

    DECLARE beginPrd DATETIME;

    DECLARE endPrd DATETIME;

    DECLARE elemPrdLength INT( 11 );

    -- КУРСОР ДЛЯ ОБХОДА ТАБЛИЦЫ DSR_PeriodsSet

    Declare DSRPeriodsSetCursor Cursor

    for select PeriodID, BeginPeriod, EndPeriod, ElementaryPeriodsLength

        from DSR_PeriodsSet

    where ID = periodSetID;

    -- ОПРЕДЕЛЕНИЕ HANDLER'А ОКОНЧАНИЯ КУРСОРА

    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

    --DROP TABLE DSR_PeriodsByPeriodSetID;

    --СОЗДАЕМ ТАБЛИЦУ ПЕРИОДОВ

    CREATE TABLE IF NOT EXISTS DSR_PeriodsByPeriodSetID

    (PeriodSetID VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,

     PeriodID VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,

     BeginPeriod VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,

     EndPeriod VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL

    ) TYPE=MyISAM;

    -- УДАЛЯЕМ ПРЕДЫДУЩИЕ ЗНАЧЕНИЯ

    DELETE FROM DSR_PeriodsByPeriodSetID;

--    set @temp := 0;

    -- ОТКРЫВАЕМ КУРСОР ПО ПЕРИОДАМ ДАННОГО НАБОРА ПЕРИОДОВ

    Open DSRPeriodsSetCursor;

    WHILE done = 0 DO

        FETCH DSRPeriodsSetCursor INTO prdID, beginPrd, endPrd, elemPrdLength;

        IF elemPrdLength IS NULL THEN

            INSERT INTO DSR_PeriodsByPeriodSetID(PeriodSetID,

                                                 PeriodID,

                                                 BeginPeriod,

                                                 EndPeriod)

            select periodSetID,

                   prdID,

                   beginPrd,

                   endPrd;

        ELSE

            Call getElementaryPeriods(beginPrd, endPrd, elemPrdLength);

            INSERT INTO DSR_PeriodsByPeriodSetID(PeriodSetID,

                                                 PeriodID,

                                                 BeginPeriod,

                                                 EndPeriod)

            select periodSetID,

                   prdID,

                   BeginElementaryPeriod,

                   EndElementaryPeriod

            from  ElementaryPeriods;

        END IF;

--         set @temp := @temp + 1;

    END WHILE;

    Close DSRPeriodsSetCursor;

--    select @temp as 'количество_итераций';

    -- ИТОГОВАЯ ВЫБОРКА

    select *

    from DSR_PeriodsByPeriodSetID;

END $$

DELIMITER ;

помогите плиз... из-за чего это может быть?

Отредактированно NDS (02.06.2010 12:41:17)

NDS · 02.06.2010 16:24:08

Проблема решена)
Последний период заносился два раза из-за неправильной организации курсора. Точнее обработчика его окончания...

    -- ОТКРЫВАЕМ КУРСОР ПО ПЕРИОДАМ ДАННОГО НАБОРА ПЕРИОДОВ

    Open DSRPeriodsSetCursor;

    FETCH DSRPeriodsSetCursor INTO prdID, beginPrd, endPrd, elemPrdLength;

    WHILE done = 0 DO

        IF elemPrdLength IS NULL THEN

            INSERT INTO DSR_PeriodsByPeriodSetID(PeriodSetID,

                                                 PeriodID,

                                                 BeginPeriod,

                                                 EndPeriod)

            select periodSetID,

                   prdID,

                   beginPrd,

                   endPrd;

        ELSE

            Call getElementaryPeriods(beginPrd, endPrd, elemPrdLength);

            INSERT INTO DSR_PeriodsByPeriodSetID(PeriodSetID,

                                                 PeriodID,

                                                 BeginPeriod,

                                                 EndPeriod)

            select periodSetID,

                   prdID,

                   BeginElementaryPeriod,

                   EndElementaryPeriod

            from  ElementaryPeriods;

        END IF;

        FETCH DSRPeriodsSetCursor INTO prdID, beginPrd, endPrd, elemPrdLength;

    END WHILE;

    Close DSRPeriodsSetCursor;

LazY · 02.06.2010 18:10:15

Обычно при передвижении курсора пользуются выходом из цикла с помощьюд маркеров блока:

OPEN DSRPeriodsSetCursor;
fetch: LOOP
-- LOOP - безусловный цикл;
-- fetch - маркер блока, что-то типа имени (можно и другое)
FETCH DSRPeriodsSetCursor INTO prdID, beginPrd, endPrd, elemPrdLength;
IF done = 1 THEN
LEAVE fetch; -- покидаем блок по имени fetch
END IF;
-- дальше всё остальное (которое выполнится, если CONTINUE HANDLER не сработал)
-- ...
END LOOP;

NDS · 02.06.2010 19:39:19

LazY,
спасибо за совет!
Буду иметь в виду...

Форум пользователей MySQL

#1 01.06.2010 23:33:28

Вставка множества значений из Подзапроса SELECT

#2 01.06.2010 23:48:38

Re: Вставка множества значений из Подзапроса SELECT

#3 02.06.2010 00:01:54

Re: Вставка множества значений из Подзапроса SELECT

#4 02.06.2010 00:13:08

Re: Вставка множества значений из Подзапроса SELECT

#5 02.06.2010 00:20:09

Re: Вставка множества значений из Подзапроса SELECT

#6 02.06.2010 00:43:16

Re: Вставка множества значений из Подзапроса SELECT

#7 02.06.2010 04:08:44

Re: Вставка множества значений из Подзапроса SELECT

#8 02.06.2010 16:24:08

Re: Вставка множества значений из Подзапроса SELECT

#9 02.06.2010 18:10:15

Re: Вставка множества значений из Подзапроса SELECT

#10 02.06.2010 19:39:19

Re: Вставка множества значений из Подзапроса SELECT

Board footer