НОУ ИНТУИТ | Основы SQL. Лекция 13: Курсоры: принципы работы

Ульяновский государственный университет

Опубликован: 10.09.2004 | Доступ: свободный | Студентов: 34563 / 4486 | Оценка: 4.06 / 3.65 | Длительность: 16:15:00

ISBN: 978-5-9556-0101-4

Тема: Базы данных

Специальности: Администратор баз данных

Вам нравится? Нравится 249 студентам

| Поделиться |

Поддержать курс

| Скачать электронную книгу

Освобождение курсора

Закрытие курсора необязательно освобождает ассоциированную с ним память. В некоторых реализациях нужно явным образом освободить ее с помощью оператора DEALLOCATE. После освобождения курсора освобождается и память, при этом становится возможным повторное использование имени курсора.

DEALLOCATE { имя_курсора | 
  @имя_переменной_курсора }

Для контроля достижения конца курсора рекомендуется применять функцию: @@FETCH_STATUS

Функция @@FETCH_STATUS возвращает:

0, если выборка завершилась успешно;

-1, если выборка завершилась неудачно вследствие попытки выборки строки, находящейся за пределами курсора ;

-2, если выборка завершилась неудачно вследствие попытки обращения к удаленной или измененной строке.

DECLARE abc CURSOR SCROLL FOR
SELECT * FROM Клиент

Пример 13.1. Объявление курсора.

DECLARE @MyCursor CURSOR
SET @MyCursor=CURSOR LOCAL SCROLL FOR
SELECT * FROM Клиент

Пример 13.2. Использование переменной для объявления курсора.

DECLARE abc CURSOR GLOBAL SCROLL FOR
SELECT * FROM Клиент
OPEN abc

Пример 13.3. Объявление и открытие курсора.

DECLARE @MyCursor CURSOR
SET @MyCursor=abc

Пример 13.4. Использование переменной для переприсваивания курсора.

Пример 13.5. Разработать курсор для вывода списка фирм и клиентов из Москвы.

DECLARE  @firm    VARCHAR(50),
         @fam     VARCHAR(50),
         @message VARCHAR(80)
PRINT ' Список клиентов'
DECLARE klient_cursor CURSOR LOCAL FOR
    SELECT Фирма, Фамилия
    FROM Клиент
    WHERE Город='Москва'
    ORDER BY Фирма, Фамилия

OPEN klient_cursor
FETCH NEXT FROM klient_cursor INTO @firm, @fam
WHILE @@FETCH_STATUS=0
BEGIN
    SELECT @message='Клиент '+@fam+
                    ' Фирма '+ @firm
    PRINT @message

-- переход к следующему клиенту--

    FETCH NEXT FROM klient_cursor 
      INTO @firm, @fam
END
CLOSE klient_cursor
DEALLOCATE klient_cursor

Пример 13.5. Курсор для вывода списка фирм и клиентов из Москвы.

Пример 13.6. Разработать курсор для вывода списка приобретенных клиентами из Москвы товаров и их общей стоимости. В один курсор заносятся все московские клиенты, затем для каждой строки курсора, т.е. для каждого клиента, определяется и распечатывается другой курсор – его покупки. Подсчитывается общая стоимость покупок клиента.

DECLARE @id_kl    INT,
        @firm     VARCHAR(50),
        @fam      VARCHAR(50),       
        @message  VARCHAR(80),
        @nam      VARCHAR(50),
        @d        DATETIME,
        @p        INT,
        @s        INT
SET @s=0
PRINT '  Список покупок'
DECLARE klient_cursor CURSOR LOCAL FOR
    SELECT КодКлиента, Фирма, Фамилия
    FROM Клиент
    WHERE Город='Москва'
    ORDER BY Фирма, Фамилия


OPEN klient_cursor
FETCH NEXT FROM klient_cursor
INTO @id_kl, @firm, @fam
WHILE @@FETCH_STATUS=0
BEGIN
    SELECT @message='Клиент '+@fam+
        ' Фирма '+ @firm
    PRINT @message
    SELECT @message='Наименование товара Дата
        покупки Стоимость'
    PRINT @message
    DECLARE tovar_cursor CURSOR FOR
        SELECT Товар.Название, Сделка.Дата,
            Товар.Цена*Сделка.Количество AS
    Стоимость
        FROM Товар INNER JOIN Сделка ON Товар.
    КодТовара=Сделка.КодТовара
        WHERE Сделка.КодКлиента=@id_kl

    OPEN tovar_cursor
    FETCH NEXT FROM tovar_cursor 
      INTO @nam, @d, @p
    IF @@FETCH_STATUS<>0
        PRINT ' Нет покупок'
    WHILE @@FETCH_STATUS=0
    BEGIN
        SELECT @message='   '+@nam+'   '+
           CAST(@d AS CHAR(12))+'  '+
           CAST(@p AS CHAR(6))
        PRINT @message
        SET @s=@s+@p
        FETCH NEXT FROM tovar_cursor
        INTO @nam, @d, @p  
    END
    CLOSE tovar_cursor
    DEALLOCATE tovar_cursor
 
    SELECT @message='Общая стоимость '+
	    CAST(@s AS CHAR(6))
    PRINT @message    

-- переход к следующему клиенту--

    FETCH NEXT FROM klient_cursor 
    INTO @id_kl, @firm, @fam
END
CLOSE klient_cursor
DEALLOCATE klient_cursor

Пример 13.6. Курсор для вывода списка приобретенных клиентами из Москвы товаров и их общей стоимости.

Пример 13.7. Разработать прокручиваемый курсор для клиентов из Москвы. Если номер телефона начинается на 1, удалить клиента с таким номером и в первой записи курсора заменить первую цифру в номере телефона на 4.

DECLARE @firm     VARCHAR(50),
        @fam      VARCHAR(50),
        @tel      VARCHAR(8), 
        @message  VARCHAR(80)
PRINT '  Список клиентов'
DECLARE klient_cursor CURSOR GLOBAL SCROLL
KEYSET FOR
        SELECT Фирма, Фамилия, Телефон
        FROM Клиент
        WHERE Город='Москва'
        ORDER BY Фирма, Фамилия
FOR UPDATE
OPEN klient_cursor
FETCH NEXT FROM klient_cursor 
    INTO  @firm, @fam, @tel
WHILE @@FETCH_STATUS=0
BEGIN         
    SELECT @message='Клиент '+@fam+
        '  Фирма  '+@firm '  Телефон '+ @tel
    PRINT @message

-- если номер телефона начинается на 1, 
-- удалить клиента с таким номером
    IF @tel LIKE ‘1%’
       DELETE Клиент
       WHERE CURRENT OF klient_cursor
    ELSE 

-- переход к следующему клиенту

    FETCH NEXT FROM klient_cursor 
	   INTO @firm, @fam, @tel
END

FETCH ABSOLUTE 1 FROM klient_cursor 
    INTO @firm, @fam, @tel

-- в первой записи заменить первую цифру в
-- номере телефона на 4

   UPDATE Клиент SET Телефон=’4’ +     
   RIGHT(@tel,LEN(@tel)-1))
        WHERE CURRENT OF klient_cursor
SELECT @message='Клиент '+@fam+'  Фирма  '+
    @firm '  Телефон '+ @tel
    PRINT @message
CLOSE klient_cursor
DEALLOCATE klient_cursor

Пример 13.7. Прокручиваемый курсор для клиентов из Москвы.

Пример 13.8. Использование курсора как выходного параметра процедуры. Процедура возвращает набор данных – список товаров.

CREATE PROC my_proc
@cur CURSOR VARYING OUTPUT
AS
SET @cur=CURSOR FORWARD_ONLY STATIC FOR
SELECT Название FROM Товар
OPEN @cur

Пример 13.8. Использование курсора как выходного параметра процедуры.

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

DECLARE @my_cur CURSOR
DECLARE @n VARCHAR(20)
EXEC my_proc @cur=@my_cur OUTPUT
  FETCH NEXT FROM @my_cur INTO @n
  SELECT @n
WHILE (@@FETCH_STATUS=0)
BEGIN
  FETCH NEXT FROM @my_cur INTO @n
  SELECT @n
END
CLOSE @my_cur
DEALLOCATE @my_cur

Дальше >>

Авторизоваться

Основы SQL

Курсоры: принципы работы

Освобождение курсора

Вопросы и ответы