Использование курсоров и циклов в Transact-SQL

Сегодня будем рассматривать очень много чего интересного, например как запустить уже созданную процедуру, которая принимает параметры, массово, т.е. не только со статическим параметрами, а с параметрами, которые будут меняться, например, на основе какой-нибудь таблицы, как обычная функция, и в этом нам помогут как раз курсоры и циклы, и как это все реализовать сейчас будем смотреть.

Как Вы поняли, курсоры и циклы мы будем рассматривать применимо к конкретной задачи. А какой задачи, сейчас расскажу.

Существует процедура, которая выполняет какие-то действия, которые не может выполнить обычная функция SQL например, расчеты и insert на основе этих расчетов. И Вы ее запускаете, например вот так:

EXEC test_PROCEDURE par1, par2

Другими словами Вы запускаете ее только с теми параметрами, которые были указаны, но если Вам необходимо запустить данную процедуру скажем 100, 200 или еще более раз, то согласитесь это не очень удобно, т.е. долго. Было бы намного проще, если бы мы взяли и запускали процедуру как обычную функцию в запросе select, например:

SELECT my_fun(id) FROM test_table

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

Примечание! Все примеры будем писать в СУБД MS SQL Server 2008, используя Management Studio. Также все нижеперечисленные действия требуют определённых знаний языка SQL, а точнее Transact-SQL. Начинающим могу посоветовать посмотреть мой видеокурс по T-SQL, на котором рассматриваются все базовые конструкции.

И так приступим, и перед тем как писать процедуру, давайте рассмотрим исходные данные нашего примера.

Допустим, есть таблица test_table

   
   CREATE TABLE [dbo].[test_table](
        [number] [numeric](18, 0) NULL,
        [pole1] [varchar](50) NULL,
        [pole2] [varchar](50) NULL
   ) ON [PRIMARY]

GO

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

   
   CREATE PROCEDURE [dbo].[my_proc_test]
     (@number numeric, @pole1 varchar(50),  @pole2 varchar(50))
   AS
   BEGIN
    INSERT INTO dbo.test_table (number, pole1, pole2) 
       VALUES (@number, @pole1, @pole2)
   END
   GO

Она просто принимает три параметра и вставляет их в таблицу.

И допустим эту процедуру, нам нужно запустить столько раз, сколько строк в какой-нибудь таблице или представлении (VIEWS) ,  другими словами запустить ее массово для каждой строки источника.

И для примера создадим такой источник, у нас это будет простая таблица test_table_vrem, а у Вас это может быть, как я уже сказал свой источник, например временная таблица или представление:

   
   CREATE TABLE [dbo].[test_table_vrem](
        [number] [numeric](18, 0) NULL,
        [pole1] [varchar](50) NULL,
        [pole2] [varchar](50) NULL
   ) ON [PRIMARY]

  GO

Заполним ее тестовыми данными:

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

exec my_proc_test 1, ‘pole1_str1’, ‘pole2_str1’

И так еще три раза, с соответствующими параметрами.

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

Первый вариант.

Используем курсор и цикл в процедуре

Перейдем сразу к делу и напишем процедуру (my_proc_test_all), код я как всегда прокомментировал:

   
   CREATE PROCEDURE [dbo].[my_proc_test_all]
   AS
  
   --объявляем переменные
   DECLARE @number bigint
   DECLARE @pole1 varchar(50)
   DECLARE @pole2 varchar(50)
   
   --объявляем курсор
   DECLARE my_cur CURSOR FOR 
     SELECT number, pole1, pole2 
     FROM test_table_vrem
   
   --открываем курсор
   OPEN my_cur
   --считываем данные первой строки в наши переменные
   FETCH NEXT FROM my_cur INTO @number, @pole1, @pole2
   --если данные в курсоре есть, то заходим в цикл
   --и крутимся там до тех пор, пока не закончатся строки в курсоре
   WHILE @@FETCH_STATUS = 0
   BEGIN
        --на каждую итерацию цикла запускаем нашу основную процедуру с нужными параметрами   
        exec dbo.my_proc_test @number, @pole1, @pole2
        --считываем следующую строку курсора
        FETCH NEXT FROM my_cur INTO @number, @pole1, @pole2
   END
   
   --закрываем курсор
   CLOSE my_cur
   DEALLOCATE my_cur
   GO

И теперь осталось нам ее вызвать и проверить результат:

Код:

   
   --до выполнения процедуры
   SELECT * FROM test_table

   --вызов процедуры
   EXEC dbo.my_proc_test_all

   --после выполнения процедуры
   SELECT * FROM test_table 

Как видите, все у нас отработало как надо, другими словами процедура my_proc_test сработала все три раза, а мы всего лишь один раз запустили дополнительную процедуру.

Второй вариант.

Используем только цикл в процедуре

Сразу скажу, что здесь требуется нумерация строк во временной таблице, т.е. каждая строка должна быть пронумерована, например 1, 2, 3 таким полем у нас во временной таблице служит number.

Пишем процедуру my_proc_test_all_v2

   
   CREATE PROCEDURE [dbo].[my_proc_test_all_v2]
   AS
   --объявляем переменные
   DECLARE @number bigint
   DECLARE @pole1 varchar(50)
   DECLARE @pole2 varchar(50)
   DECLARE @cnt int
   DECLARE @i int 
   
   --узнаем количество строк во временной таблице
   SELECT @cnt=count(*) 
   FROM test_table_vrem

   --задаем начальное значение идентификатора
   SET @i=1
   WHILE @cnt >= @i
   BEGIN
        --присваиваем значения нашим параметрам
        SELECT @number=number, @pole1= pole1, @pole2=pole2 
        FROM test_table_vrem 
        WHERE number = @I

        --на каждую итерацию цикла запускаем нашу основную процедуру с нужными параметрами   
        EXEC dbo.my_proc_test @number, @pole1, @pole2

        --увеличиваем шаг
        set @i= @i+1
        
   END
   GO

И проверяем результат, но для начала очистим нашу таблицу, так как мы же ее только что уже заполнили по средствам процедуры my_proc_test_all:

   
   --очистим таблицу
   DELETE test_table
   
   --до выполнения процедуры
   SELECT * FROM test_table
   
   --вызов процедуры
   EXEC dbo.my_proc_test_all_v2
   
   --после выполнения процедуры
   SELECT * FROM test_table

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

Заметка! Если Вас интересует SQL и T-SQL, рекомендую посмотреть мои видеокурсы по T-SQL, с помощью которых Вы «с нуля» научитесь работать с SQL и программировать с использованием языка T-SQL в Microsoft SQL Server.