Процедуры cursor в sql server. Команда DECLARE CURSOR общие правила. Освобождение памяти, deallocate

Явный курсор представляет собой команду SELECT , явно определенную в разделе объяв­лений программы. При объявлении явного курсора ему присваивается имя. Для команд INSERT, UPDATE, MERGE и DELETE явные курсоры определяться не могут.

Определив команду SELECT как явный курсор, программист получает контроль над основными стадиями выборки информации из базы данных Oracle. Он определяет, когда открыть курсор (OPEN), когда выбрать из него строки (FETCH), сколько выбрать строк и когда закрыть курсор с помощью команды CLOSE . Информация о текущем состоянии курсора доступна через его атрибуты. Именно высокая детализация контроля делает явные курсоры бесценным инструментом для программиста.

Рассмотрим пример:

1 FUNCTION jealousy_level (2 NAME_IN IN friends.NAME%TYPE) RETURN NUMBER 3 AS 4 CURSOR jealousy_cur 5 IS 6 SELECT location FROM friends 7 WHERE NAME = UPPER (NAME_IN); 8 8 jealousy_rec jealousy_cur%ROWTYPE; 9 retval NUMBER; 10 BEGIN 11 OPEN jealousy_cur; 13 12 FETCH jealousy_cur INTO jealousy_rec; 15 13 IF jealousy_cur%FOUND 14 THEN 15 IF jealousy_rec.location = "PUERTO RICO" 16 THEN retval:= 10; 17 ELSIF jealousy_rec.location = "CHICAGO" 18 THEN retval:= 1; 19 END IF; 20 END IF; 24 21 CLOSE jealousy_cur; 26 22 RETURN retval; 23 EXCEPTION 24 WHEN OTHERS THEN 25 IF jealousy_cur%ISOPEN THEN 26 CLOSE jealousy_cur; 27 END IF; 28 END;

В нескольких ближайших разделах подробно рассматривается каждая из перечисленных операций. Термин «курсор» в них относится к явным курсорам, если только в тексте явно не указано обратное.

Объявление явного курсора

Чтобы получить возможность использовать явный курсор, его необходимо объявить в разделе объявлений блока PL/SQL или пакета:

CURSOR имя_курсора [ ([ параметр [, параметр...]) ] [ RETURN спецификация_геЕигп ] IS команда_SELECT ];

Здесь имя курсора - имя объявляемого курсора; спеиифишция_те?ит - необязательная секция RETURN; KOMaHdaSELECT - любая допустимая SQL -команда SELECT . Курсору также могут передаваться параметры (см. далее раздел «Параметры курсора»). Нако­нец, после команды SELECT...FOR UPDATE можно задать список столбцов для обновления (также см. далее). После объявления курсор открывается командой OPEN , а выборка строк из него осуществляется командой FETCH .

Несколько примеров объявлений явных курсоров.

• Курсор без параметров . Результирующим набором строк этого курсора является набор идентификаторов компаний, выбранных из всех строк таблицы:

CURSOR company_cur IS SELECT company_id FROM company;

• Курсор с параметрами. Результирующий набор строк этого курсора содержит единственную строку с именем компании, соответствующим значению переданного параметра:

CURSOR name_cur (company_id_in IN NUMBER) IS SELECT name FROM company WHERE company_id = company_id_in;

• Курсор с предложением RETURN . Результирующий набор строк этого курсора содержит все данные таблицы employee для подразделения с идентификатором 10:

CURSOR emp_cur RETURN employees%ROWTYPE IS SELECT * FROM employees WHERE department_id = 10;

Имя курсора

Имя явного курсора должно иметь длину до 30 символов и соответствовать тем же правилам, что и остальные идентификаторы PL/SQL . Имя курсора не является пере­менной - это идентификатор указателя на запрос. Имени курсора не присваивается значение, его нельзя применять в выражениях. Курсор используется только в командах OPEN, CLOSE и FETCH , а также для уточнения атрибута курсора.

Объявление курсора в пакете

Явные курсоры объявляются в разделе объявлений блока PL/SQL . Курсор может объявляться на уровне пакета, но не в конкретной процедуре или функции пакета. Пример объявления двух курсоров в пакете:

PACKAGE book_info IS CURSOR titles_cur IS SELECT title FROM books; CURSOR books_cur (title_filter_in IN books.title%TYPE) RETURN books%ROWTYPE IS SELECT * FROM books WHERE title LIKE title_filter_in; END;

Первый курсор titles_cur возвращает только названия книг. Второй, books_cur , воз­вращает все строки таблицы books, в которых названия книг соответствуют шаблону, заданному в качестве параметра курсора (например, «Все книги, содержащие строку " PL/SQL "»). Обратите внимание: во втором курсоре используется секция RETURN , которая объявляет структуру данных, возвращаемую командой FETCH .

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

• Запись, определяемая на основе строки таблицы данных с помощью атрибута %ROWTYPE .
• Запись, определяемая на основе другого, ранее объявленного курсора, также с по­мощью атрибута %rowtype .
• Запись, определенная программистом.

Количество выражений в списке выборки курсора должно соответствовать количеству столбцов записи имя_таблицы%ROWTYPE, Kypcop%ROWTYPE или тип записи. Типы данных элементов тоже должны быть совместимы. Например, если второй элемент списка вы­борки имеет тип NUMBER , то второй столбец записи в секции RETURN не может иметь тип VARCHAR2 или BOOLEAN .

Прежде чем переходить к подробному рассмотрению секции RETURN и ее преимуществ, давайте сначала разберемся, для чего вообще может понадобиться объявление курсоров в пакете? Почему не объявить явный курсор в той программе, в которой он использу­ется - в процедуре, функции или анонимном блоке?

Ответ прост и убедителен. Определяя курсор в пакете, можно многократно использовать заданный в нем запрос, не повторяя один и тот же код в разных местах приложения. Реа­лизация запроса в одном месте упрощает его доработку и сопровождение кода. Некоторая экономия времени достигается за счет сокращения количества обрабатываемых запросов.

Также стоит рассмотреть возможность создания функции, возвращающей курсорную переменную на базе REF CURSOR . Вызывающая программа осуществляет выборку строк через курсорную переменную. За дополнительной информацией обращайтесь к разделу «Курсорные переменные и REF CURSOR ».

Объявляя курсоры в пакетах для повторного использования, следует учитывать одно важное обстоятельство. Все структуры данных, в том числе и курсоры, объ­являемые на «уровне пакета» (не внутри конкретной функции или процедуры), сохраняют свои значения на протяжении всего сеанса. Это означает, что пакетный курсор будет оставаться открытым до тех пор, пока вы явно не закроете его, или до завершения сеанса. Курсоры, объявленные в локальных блоках, автоматически закрываются при завершении этих блоков.

А теперь давайте разберемся с секцией RETURN . У объявления курсора в пакете имеется одна интересная особенность: заголовок курсора может быть отделен от его тела. Такой заголовок, больше напоминающий заголовок функции, содержит информацию, которая необходима программисту для работы: имя курсора, его параметры и тип возвращаемых данных. Телом курсора служит команда SELECT . Этот прием продемонстрирован в новой версии объявления курсора books_cur в пакете book_info:

PACKAGE book_info IS CURSOR books_cur (title_filter_in IN books.title%TYPE) RETURN books%ROWTYPE; END; PACKAGE BODY book_info IS CURSOR books_cur (title_filter_in IN books.title%TYPE) RETURN books%ROWTYPE IS SELECT * FROM books WHERE title LIKE title_filter_in; END;

Все символы до ключевого слова IS образуют спецификацию, а после IS следует тело курсора. Разделение объявления курсора может служить двум целям.

• Сокрытие информации . Курсор в пакете представляет собой «черный ящик». Это удобно для программистов, потому что им не нужно ни писать, ни даже видеть команду SELECT . Достаточно знать, какие записи возвращает этот курсор, в каком порядке и какие столбцы они содержат. Программист, работающий с пакетом, ис­пользует курсор как любой другой готовый элемент.
• Минимум перекомпиляции . Если скрыть определение запроса в теле пакета, то изменения в команду SELECT можно будет вносить, не меняя заголовок курсора в спецификации пакета. Это позволяет совершенствовать, исправлять и повторно компилировать код без перекомпиляции спецификации пакета, благодаря чему за­висящие от этого пакета программы не будут помечены как недействительные и их также не нужно будет перекомпилировать.

Открытие явного курсора

Использование курсора начинается с его определения в разделе объявлений. Далее объявленный курсор необходимо открыть. Синтаксис оператора OPEN очень прост:

OPEN имя_курсора [ (аргумент [, аргумент...]) ];

Здесь имякурсора - это имя объявленного ранее курсора, а аргумент - значение, передаваемое курсору, если он объявлен со списком параметров.

Oracle также поддерживает синтаксис FOR при открытии курсора, который ис­пользуется как для курсорных переменных (см. раздел «Курсорные переменные и REF CURSOR »), так и для встроенного динамического SQL .

Открывая курсор, PL/SQL выполняет содержащийся в нем запрос. Кроме того, он идентифицирует активный набор данных - строки всех участвующих в запросе таблиц, соответствующие критерию WHERE и условию объединения. Команда OPEN не извлекает данные - это задача команды FETCH .

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

Более того, если команда SELECT содержит секцию FOR UPDATE , все идентифицируемые курсором строки блокируются при его открытии.

При попытке открыть уже открытый курсор PL/SQL выдаст следующее сообщение об ошибке:

ORA-06511: PL/SQL: cursor already open

Поэтому перед открытием курсора следует проверить его состояние по значению атри­бута %isopen:

IF NOT company_cur%ISOPEN THEN OPEN company_cur; END IF;

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

Если в программе выполняется цикл FOR с использованием курсора, этот курсор не нуждается в явном открытии (выборке данных, закрытии). Ядро PL/SQL делает это автоматически.

Выборка данных из явного курсора

Команда SELECT создает виртуальную таблицу - набор строк, определяемых условием WHERE со столбцами, определяемыми списком столбцов SELECT . Таким образом, курсор представляет эту таблицу в программе PL/SQL . Основным назначением курсора в программах PL/SQL является выборка строк для обработки. Выборка строк курсора выполняется командой FETCH:

FETCH имя_курсора INTO запись_или_список_переменных;

Здесь имя курсора - имя курсора, из которого выбирается запись, а запись или список переменных - структуры данных PL/SQL , в которые копируется следующая строка активного набора записей. Данные могут помещаться в запись PL/SQL (объявленную с атрибутом %ROWTYPE или объявлением TYPE) или в переменные (переменные PL/SQL или переменные привязки - как, например, в элементы Oracle Forms).

Примеры явных курсоров

Следующие примеры демонстрируют разные способы выборки данных.

• Выборка данных из курсора в запись PL/SQL:

DECLARE CURSOR company_cur is SELECT ...; company_rec company_cur%ROWTYPE; BEGIN OPEN company_cur; FETCH company_cur INTO company_rec;

• Выборка данных из курсора в переменную:

FETCH new_balance_cur INTO new_balance_dollars;

• Выборка данных из курсора в строку таблицы PL/SQL, переменную и переменную привязки Oracle Forms:

FETCH emp_name_cur INTO emp_name (1), hiredate, :dept.min_salary;

Данные, выбираемые из курсора, всегда следует помещать в запись, объявленную на основе того же курсора с атрибутом %ROWTYPE ; избегайте выборки в списки переменных. Выборка в запись делает код более компактным и гибким, позволяет изменять список выборки без изменения команды FETCH .

Выборка после обработки последней строки

Открыв курсор, вы по очереди выбираете из него строки, пока они все не будут исчер­паны. Однако и после этого можно выполнять команду FETCH .

Как ни странно, в этом случае PL/SQL не инициирует исключение. Он просто ничего не делает. Поскольку выбирать больше нечего, значения переменных в секции INTO команды FETCH не изменяются. Иначе говоря, команда FETCH не устанавливает значения этих переменных равными NULL .

Псевдонимы столбцов явного курсора

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

Псевдоним (alias) столбца представляет собой альтернативное имя, указанное в ко­манде SELECT для столбца или выражения. Задав подходящие псевдонимы в SQL*Plus , можно вывести результаты произвольного запроса в удобочитаемом виде. В подоб­ных ситуациях псевдонимы не являются обязательными. С другой стороны, при использовании явных курсоров псевдонимы вычисляемых столбцов необходимы в следующих случаях:

• при выборке данных из курсора в запись, объявленную с атрибутом %ROWTYPE на основе того же курсора;
• когда в программе содержится ссылка на вычисляемый столбец.

Рассмотрим следующий запрос. Команда SELECT выбирает названия всех компаний, заказывавших товары в течение 2001 года, а также общую сумму заказов (предпола­гается, что для текущего экземпляра базы данных по умолчанию используется маска форматирования DD-MON-YYYY):

SELECT company_name, SUM (inv_amt) FROM company c, invoice i WHERE c.company_id = i.company_id AND i.invoice_date BETWEEN "01-JAN-2001" AND "31-DEC-2001";

При выполнении этой команды в SQL*Plus будет получен следующий результат:

COMPANY_NAME	SUM (INV_AMT)
ACME TURBO INC.	1000
WASHINGTON HAIR CO.	25.20

Как видите, заголовок столбца SUM (INV_AMT) плохо подходит для отчета, но для простого просмотра данных он вполне годится. Теперь выполним тот же запрос в программе PL/ SQL с использованием явного курсора и добавим псевдоним столбца:

DECLARE CURSOR comp_cur IS SELECT c.name, SUM (inv_amt) total_sales FROM company C, invoice I WHERE C.company_id = I.company_id AND I.invoice_date BETWEEN "01-JAN-2001" AND "31-DEC-2001"; comp_rec comp_cur%ROWTYPE; BEGIN OPEN comp_cur; FETCH comp_cur INTO comp_rec; END;

Без псевдонима я не смогу сослаться на столбец в структуре записи comp_rec . При на­личии псевдонима с вычисляемым столбцом можно работать точно так же, как с любым другим столбцом запроса:

IF comp_rec.total_sales > 5000 THEN DBMS_OUTPUT.PUT_LINE (" You have exceeded your credit limit of $5000 by " || TO_CHAR (comp_rec.total_sales - 5000, "$9999")); END IF;

При выборке строки в запись, объявленную с атрибутом %ROWTYPE , доступ к вычис­ляемому столбцу можно будет получить только по имени - ведь структура записи определяется структурой самого курсора.

Закрытие явного курсора

Когда-то в детстве нас учили прибирать за собой, и эта привычка осталась у нас (хотя и не у всех) на всю жизнь. Оказывается, это правило играет исключительно важную роль и в программировании, и особенно когда дело доходит до управления курсорами. Никогда не забывайте закрыть курсор, если он вам больше не нужен!

Синтаксис команды CLOSE:

CLOSE имя_курсора;

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

• Если курсор объявлен и открыт в процедуре, не забудьте его закрыть после завер­шения работы с ним; в противном случае в вашем коде возникнет утечка памяти. Теоретически курсор (как и любая структура данных) должен автоматически закры­ваться и уничтожаться при выходе из области действия. Как правило, при выходе из процедуры, функции или анонимного блока PL/SQL действительно закрывает все от­крытые в нем курсоры. Но этот процесс связан с определенными затратами ресурсов, поэтому по соображениям эффективности PL/SQL иногда откладывает выявление и закрытие открытых курсоров. Курсоры типа REF CURSOR по определению не могут быть закрыты неявно. Единственное, в чем можно быть уверенным, так это в том, что по завершении работы «самого внешнего» блока PL/SQL , когда управление будет возвращено SQL или другой вызывающей программе, PL/SQL неявно закроет все открытые этим блоком или вложенными блоками курсоры, кроме REF CURSOR . В статье «Cursor reuse in PL/SQL static SQL » из Oracle Technology Network приво­дится подробный анализ того, как и когда PL/SQL закрывает курсоры. Вложенные анонимные блоки - пример ситуации, в которой PL/SQL не осуществляет неяв­ное закрытие курсоров. Интересная информация по этой теме приведена в статье Джонатана Генника « Does PL/SQL Implicitly Close Cursors ?».
• Если курсор объявлен в пакете на уровне пакета и открыт в некотором блоке или программе, он останется открытым до тех пор, пока вы его явно не закроете, или до завершения сеанса. Поэтому, завершив работу с курсором пакетного уровня, его следует немедленно закрыть командой CLOSE (и кстати, то же самое следует делать в разделе исключений):

BEGIN OPEN my_package.my_cursor; ... Работаем с курсором CLOSE my_package.my_cursor; EXCEPTION WHEN OTHERS THEN IF mypackage.my_cursor%ISOPEN THEN CLOSE my_package.my_cursor; END IF; END;

• Курсор можно закрывать только в том случае, если ранее он был открыт; в про­тивном случае будет инициировано исключение INVALID_CURS0R . Состояние курсора проверяется с помощью атрибута %ISOPEN:

IF company_cur%ISOPEN THEN CLOSE company_cur; END IF;

• Если в программе останется слишком много открытых курсоров, их количество может превысить значение параметра базы данных OPEN_CURSORS . Получив сообще­ние об ошибке, прежде всего убедитесь в том, что объявленные в пакетах курсоры закрываются после того, как надобность в них отпадет.

Атрибуты явных курсоров

Oracle поддерживает четыре атрибута (%FOUND, %NOTFOUND, %ISOPEN, %ROWCOUNTM) для полу­чения информации о состоянии явного курсора. Ссылка на атрибут имеет следующий синтаксис: курсор%атрибут

Здесь курсор - имя объявленного курсора.

Значения, возвращаемые атрибутами явных курсоров, приведены в табл. 1.

Таблица 1. Атрибуты явных курсоров

Значения атрибутов курсоров до и после выполнения различных операций с ними указаны в табл. 2.

Работая с атрибутами явных курсоров, необходимо учитывать следующее:

• При попытке обратиться к атрибуту %FOUND, %NOTFOUND или %ROWCOUNT до открытия курсора или после его закрытия Oracle инициирует исключение INVALID CURSOR (ORA-01001).
• Если после первого выполнения команды FETCH результирующий набор строк ока­жется пустым, атрибуты курсора возвращают следующие значения: %FOUND = FALSE , %NOTFOUND = TRUE и %ROWCOUNT = 0.
• При использовании BULK COLLECT атрибут %ROWCOUNT возвращает количество строк, извлеченных в заданные коллекции.

Таблица 2. Значения атрибутов курсоров

Операция	%FOUND	%NOTFOUND	%ISOPEN	%ROWCOUNT
До OPEN	Исключение ORA-01001	Исключение ORA-01001	FALSE	Исключение ORA-01001
После OPEN	NULL	NULL	TRUE	0
До первой выборки FETCH	NULL	NULL	TRUE	0
После первой выборки FETCH	TRUE	FALSE	TRUE	1
Перед последующими FETCH	TRUE	FALSE	TRUE	1
После последующих FETCH	TRUE	FALSE	TRUE	Зависит от данных
Перед последней выборкой FETCH	TRUE	FALSE	TRUE	Зависит от данных
После последней выборки FETCH	TRUE	FALSE	TRUE	Зависит от данных
Перед CLOSE	FALSE	TRUE	TRUE	Зависит от данных
После CLOSE	Исключение	Исключение	FALSE	Исключение

Использование всех этих атрибутов продемонстрировано в следующем примере:

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

PL/SQL позволяет передавать параметры курсорам. Они выполняют те же функции, что и параметры программных модулей, а также несколько дополнительных.

• Расширение возможности многократного использования курсоров . Вместо того чтобы жестко кодировать в предложении WHERE значения, определяющие условия отбора данных, можно использовать параметры для передачи в это предложение новых значений при каждом открытии курсора.
• Решение проблем, связанных с областью действия курсоров . Если вместо жестко закодированных значений в запросе используются параметры, результирующий набор строк курсора не привязан к конкретной переменной программы или бло­ка. Если в программе имеются вложенные блоки, курсор можно определить на верхнем уровне и использовать его во вложенных блоках с объявленными в них переменными.

Количество параметров курсора не ограничено. При вызове OPEN для курсора должны быть заданы значения всех параметров (кроме параметров, для которых определены значения по умолчанию).

В каких случаях курсору требуются параметры? Общее правило здесь то же, что и для процедур и функций: если предполагается, что курсор будет использоваться в разных местах и с разными значениями в секции WHERE , для него следует определить параметр. Давайте сравним курсоры с параметром и без. Пример курсора без параметров:

CURSOR joke_cur IS SELECT name, category, last_used_date FROM Jokes;

В результирующий набор курсора включаются все записи таблицы joke . Если же нам нужно только некоторое подмножество строк, в запрос включается секция WHERE:

CURSOR joke_cur IS SELECT name, category, last_used_date FROM jokes WHERE category = "HUSBAND";

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

Курсоры с параметрами

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

PROCEDURE explain_joke (main_category_in IN joke_category.category_id%TYPE) IS /* || Курсор со списком параметров, состоящим || из единственного строкового параметра. */ CURSOR joke_cur (category_in IN VARCHAR2) IS SELECT name, category, last_used_date FROM Joke WHERE category = UPPER (category_in); joke_rec joke_cur%ROWTYPE; BEGIN /* Теперь при открытии курсора ему передается аргумент */ OPEN joke_cur (main_category_in); FETCH joke_cur INTO joke_rec;

Между именем курсора и ключевым словом IS теперь содержится список параметров. Жестко закодированное значение HUSBAND в предложении WHERE заменено ссылкой на параметр UPPER (category_in). При открытии курсора можно будет задать значение HUSBAND , husband или HuSbAnD - курсор все равно будет работать. Название категории, для которой курсор должен вернуть строки таблицы joke, задается в операторе OPEN (в скобках) в виде литерала, константы или выражения. В момент открытия курсора производится разбор команды SELECT , а параметр связывается со значением. Затем определяется результирующий набор строк - и курсор готов к выборке.

Открытие курсора с параметрами

Новый курсор можно открывать с указанием любой категории:

OPEN joke_cur (Jokes_pkg.category); OPEN joke_cur ("husband"); OPEN joke_cur ("politician"); OPEN joke_cur (Jokes_pkg.relation || "-IN-LAW");

Параметры курсора чаще всего используются в условии WHERE , но ссылаться на них можно и в других местах команды SELECT:

DECLARE CURSOR joke_cur (category_in IN ARCHAR2) IS SELECT name, category_in, last_used_date FROM joke WHERE category = UPPER (category_in);

Вместо того чтобы считывать категорию из таблицы, мы просто подставляем параметр category_in в список выборки. Результат остается прежним, потому что условие WHERE ограничивает категорию выборки значением параметра.

Область действия параметра курсора

Область действия параметра курсора ограничивается этим курсором. На параметр курсо­ра нельзя ссылаться за пределами команды SELECT , связанной с курсором. Приведенный ниже фрагмент PL/SQL не компилируется, потому что идентификатор program_name не является локальной переменной в блоке. Это формальный параметр курсора, который определен только внутри курсора:

DECLARE CURSOR scariness_cur (program_name VARCHAR2) IS SELECT SUM (scary_level) total_scary_level FROM tales_from_the_crypt WHERE prog_name = program_name; BEGIN program_name:= "THE BREATHING MUMMY"; /* Недопустимая ссылка */ OPEN scariness_cur (program_name); .... CLOSE scariness_cur; END;

Режимы параметра курсора

Синтаксис параметров курсоров очень похож на синтаксис процедур и функций - за исключением того, что параметры курсоров могут быть только параметрами IN . Для параметров курсоров нельзя задавать режимы OUT или IN OUT . Эти режимы позволяют передавать и возвращать значения из процедур, что не имеет смысла для курсора. Существует только один способ получения информации от курсора: выборка записи и копирование значений из списка столбцов в секции INTO

Значения параметров по умолчанию

Параметрам курсоров могут присваиваться значения по умолчанию. Пример курсора со значением параметра по умолчанию:

CURSOR emp_cur (emp_id_in NUMBER:= 0) IS SELECT employee_id, emp_name FROM employee WHERE employee_id = emp_id_in;

Поскольку для параметра emp_id_in определено значение по умолчанию, в команде FETCH его значение можно не указывать. В этом случае курсор вернет информацию о сотруднике с кодом 0.

In my T-SQL code I always use set based operations. I have been told these types of operations are what SQL Server is designed to process and it should be quicker than serial processing. I know cursors exist but I am not sure how to use them. Can you provide some cursor examples? Can you give any guidance on when to use cursors? I assume Microsoft included them in SQL Server for a reason so they must have a place where they can be used in an efficient manner.

Solution

In some circle"s cursors are never used, in others they are a last resort and in other groups they are used regularly. In each of these camps they have different reasons for their stand on cursor usage. Regardless of your stand on cursors they probably have a place in particular circumstances and not in others. So, it boils down to your understanding of the coding technique then your understanding of the problem at hand to make a decision on whether or not cursor-based processing is appropriate or not. To get started let"s do the following:

• Look at an example cursor
• Break down the components of the cursor
• Provide additional cursor examples
• Analyze the pros and cons of cursor usage

How to Create a SQL Server Cursor

Creating a SQL Server cursor is a consistent process, so once you learn the steps you are easily able to duplicate them with various sets of logic to loop through data. Let"s walk through the steps:

1. First, you declare your variables that you need in the logic.
2. Second you declare cursor with a specific name that you will use throughout the logic. This is immediately followed by opening the cursor.
3. Third, you fetch a record from cursor to begin the data processing.
4. Fourth, is the data process that is unique to each set of logic. This could be inserting, updating, deleting, etc. for each row of data that was fetched. This is the most important set of logic during this process that is performed on each row.
5. Fifth, you fetch the next record from cursor as you did in step 3 and then step 4 is repeated again by processing the selected data.
6. Sixth, once all of the data has been processed, then you close cursor.
7. As a final and important step, you need to deallocate the cursor to release all of the internal resources SQL Server is holding.

From here, check out the examples below to get started on knowing when to use SQL Server cursors and how to do so.

Example SQL Server Cursor

Here is an example cursor from tip Simple script to backup all SQL Server databases where backups are issued in a serial manner:

DECLARE @name VARCHAR(50) -- database name DECLARE @path VARCHAR(256) -- path for backup files DECLARE @fileName VARCHAR(256) -- filename for backup DECLARE @fileDate VARCHAR(20) -- used for file name SET @path = "C:\Backup\" SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) DECLARE db_cursor CURSOR FOR SELECT name FROM MASTER.dbo.sysdatabases WHERE name NOT IN ("master","model","msdb","tempdb") OPEN db_cursor FETCH NEXT FROM db_cursor INTO @name WHILE @@FETCH_STATUS = 0 BEGIN SET @fileName = @path + @name + "_" + @fileDate + ".BAK" BACKUP DATABASE @name TO DISK = @fileName FETCH NEXT FROM db_cursor INTO @name END CLOSE db_cursor DEALLOCATE db_cursor

SQL Server Cursor Components

Based on the example above, cursors include these components:

• DECLARE statements - Declare variables used in the code block
• SET\SELECT statements - Initialize the variables to a specific value
• DECLARE CURSOR statement - Populate the cursor with values that will be evaluated
  • NOTE - There are an equal number of variables in the DECLARE CURSOR FOR statement as there are in the SELECT statement. This could be 1 or many variables and associated columns.
• OPEN statement - Open the cursor to begin data processing
• FETCH NEXT statements - Assign the specific values from the cursor to the variables
  • NOTE - This logic is used for the initial population before the WHILE statement and then again during each loop in the process as a portion of the WHILE statement
• WHILE statement - Condition to begin and continue data processing
• BEGIN...END statement - Start and end of the code block
  • NOTE - Based on the data processing multiple BEGIN...END statements can be used
• Data processing - In this example, this logic is to backup a database to a specific path and file name, but this could be just about any DML or administrative logic
• CLOSE statement - Releases the current data and associated locks, but permits the cursor to be re-opened
• DEALLOCATE statement - Destroys the cursor

Recommended Reading

Learn more about SQL Server Cursors and alternatives:

Additional SQL Server Cursor Examples

In the example above backups are issued via a cursor, check out these other tips that leverage cursor-based logic:

• Script to create commands to disable, enable, drop and recreate Foreign Key constraints in SQL Server

SQL Server Cursor Analysis

The analysis below is intended to serve as insight into various scenarios where cursor-based logic may or may not be beneficial:

• Online Transaction Processing (OLTP) - In most OLTP environments, SET based logic makes the most sense for short transactions. Our team has run into a third-party application that uses cursors for all of its processing, which has caused issues, but this has been a rare occurrence. Typically, SET based logic is more than feasible and cursors are rarely needed.
• Reporting - Based on the design of the reports and the underlying design, cursors are typically not needed. However, our team has run into reporting requirements where referential integrity does not exist on the underlying database and it is necessary to use a cursor to correctly calculate the reporting values. We have had the same experience when needing to aggregate data for downstream processes, a cursor-based approach was quick to develop and performed in an acceptable manner to meet the need.
• Serialized processing - If you have a need to complete a process in serialized manner, cursors are a viable option.
• Administrative tasks - Many administrative tasks need to be executed in a serial manner, which fits nicely into cursor-based logic, but other system-based objects exist to fulfill the need. In some of those circumstances, cursors are used to complete the process.
• Large data sets - With large data sets you could run into any one or more of the following:
  • Cursor based logic may not scale to meet the processing needs.
  • With large set-based operations on servers with a minimal amount of memory, the data may be paged or monopolize the SQL Server which is time consuming can cause contention and memory issues. As such, a cursor-based approach may meet the need.
  • Some tools inherently cache the data to a file under the covers, so processing the data in memory may or may not actually be the case.
  • If the data can be processed in a staging SQL Server database the impacts to the production environment are only when the final data is processed. All of the resources on the staging server can be used for the ETL processes then the final data can be imported.
  • SSIS supports batching sets of data which may resolve the overall need to break-up a large data set into more manageable sizes and perform better than a row by row approach with a cursor.
  • Depending on how the cursor or SSIS logic is coded, it may be possible to restart at the point of failure based on a
  • Repeat a batch with the GO command

  Next Steps

  • When you are faced with a data processing decision determine where you stand with SQL Server cursor usage. They may or may not have a place in your application or operational processes. There are many ways to complete a task, so using a cursor could be a reasonable alternative or not. You be the judge.
  • If you run into issues with another coding technique and need to get something done quickly, using a cursor may be a viable alternative. It may take longer to process the data, but the coding time might be much less. If you have a one-time process or nightly processing, this could do the trick.
  • If cursors are shunned in your environment, be sure to select another viable alternative. Just be sure the process will not cause other issues. As an example, if a cursor is used and millions of rows are processed will this potentially flush all of the data from cache and cause further contention? Or with a large data set will the data be paged to disk or written to a temporary directory?
  • As you evaluate a cursor-based approach versus other alternatives make a fair comparison of the techniques in terms of time, contention and resources needed. Hopefully these factors will drive you to the proper technique.

Команда DECLARE CURSOR позволяет построчно извлекать записи из таблицы для манипулирования. Это позволяет производить построчную обработку вместо традиционной обработки наборами данных, которую осуществляет SQL.

В самом первом приближении при работе с курсором используются следующие шаги.

Курсор создается командой DECLARE. Курсор открывается командой OPEN.

Операции с курсором производятся при помощи команды FETCH. Курсор закрывается командой CLOSE.

В команде DECLARE CURSOR указывается инструкция SELECT. Каждую строку, возвращаемую инструкцией SELECT, можно извлекать и обрабатывать индивидуально. В следующем примере для Oracle курсор объявляется в блоке объявлений вместе с несколькими другими переменными. После этого в последующем блоке BEGIN…END курсор открывается, по нему производится выборка, и курсор закрывается.

CURSOR title_price_cursor IS SELECT title, price FROM titles

WHERE price IS NOT NULL; title_price_val title_price_cursor ROWTYPE; new_price NUMBER(10.2);

OPEN title_price_Cursor;

FETCH title_price_cur-sor INTO title_price_val;

new_price:= "title_price_val.price" * 1.25 INSERT INTO new_title_price VALUES

(title_price_val.title, new_price) CLOSE title_price_cursor; END;

Поскольку в этом примере используется PL/SQL, большую часть кода мы в этой книге разъяснять не будем. Однако в блоке DECLARE ясно видно объявление курсора. В исполняемом блоке PL/SQL курсор инициализируется командой OPEN, значения извлекаются командой FETCH и, наконец, курсор закрывается командой CLOSE.

Инструкция SELECT- это основа курсора, так что хорошей практикой является ее тщательное тестирование перед включением в инструкцию DECLARE CURSOR. Инструкция SELECT может работать с базовой таблицей или представлением. Поэтому курсоры «только для чтения» могут работать с необновляемыми представлениями. Инструкция SELECT может содержать такие предложения, как ORDER BY, GROUP BY и HAVING, если эти предложения не обновляют исходную таблицу. Если курсор определен как FOR UPDATE, то рекомендуется удалять такие предложения из инструкции SELECT.

Локальные курсоры часто используются в качестве выходных параметров хранимых процедур. Поэтому в хранимой процедуре можно определить и заполнить курсор и передать его вызвавшему ее пакетному заданию или хранимой процедуре.

В следующем простом примере для DB2 мы объявим курсор, который просматривает номера департаментов, названия департаментов и номера менеджеров в admin_group "ХО1".

DECLARE dept_cursor CURSOR

FOR SELECT dept_nbr, dept_name, mgr_nbr

WHERE admin_group="X01"

ORDER BY d"ept_name ASC, dept_nbr DESC, mgr_nbr DESC;

В следующем примере для Microsoft SQL Server объявляется и открывается курсор для таблицы publishers. Курсор отбирает из таблицы publishers первую запись, соответствующую инструкции SELECT, и вставляет ее в другую таблицу. Затем он переходит к следующей записи, потом к следующей - до тех пор, пока все записи не будут обработаны. И наконец, курсор закрывается и высвобождает память (команда DEALLOCATE используется только в Microsoft SQL Server).

DECLARE @publisher_name VARCHAR(20)

DECLARE pub_cursor CURSOR FOR SELECT pub_name FROM publishers WHERE country "USA"

FETCH NEXT FROM pub_cursor INTO publisher_name

WHILE @s>FETCH_STATUS=0

INSERT INTO foreign_publishers VALUES(«j>publisher_name)

CLOSE pub_cursor DEALLOCATE pub_cursor

В этом примере можно видеть, как курсор передвигается по набору записей. (Этот пример призван только продемонстрировать данную идею, поскольку в действительности существует лучший способ решения данной задачи, а именно инструкция INSERT, SELECT.)

Курсор - ссылка на контекстную область памяти. В некоторых реализациях языка программирования SQL (Oracle, Microsoft SQL Server) - получаемый при выполнении запроса результирующий набор и связанный с ним указатель текущей записи. бы сказал, что курсор - это виртуальная таблица которая представляет собой альтернативное хранилище данных. При этом курсор, позволяет обращаться к своим данным, как к данным обычного массива.
Используются курсоры в хранимых процедурах. Довольно теории давайте рассмотрим пример:
У нас есть база данных (база немного не хорошая, это одна из моих лабораторных работ, но наш препод по базам данных настаивал на такой структуре)

/*данные о банке */
CREATE TABLE `bank` (

`BankName` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,


PRIMARY KEY (`BankId`)

)ENGINE=InnoDB
CHARACTER SET "utf8" COLLATE "utf8_bin" ;
/*данные о вкладах */
CREATE TABLE `bankdistribution` (
`BankId` INTEGER (11) NOT NULL ,
`Persent` INTEGER (11) DEFAULT NULL ,
`ContributeAmount` DECIMAL (10,0) NOT NULL ,
`ClientId` INTEGER (11) NOT NULL ,
PRIMARY KEY (`BankId`, `ClientId`),
KEY `BankId` (`BankId`),
KEY `ClientId` (`ClientId`),
CONSTRAINT `bankdistribution_fk` FOREIGN KEY (`BankId`) REFERENCES `bank` (`BankId`),
CONSTRAINT `bankdistribution_fk1` FOREIGN KEY (`ClientId`) REFERENCES `client` (`ClientId`)
)ENGINE=InnoDB
/*данные о вкладчиках*/
CREATE TABLE `client` (
`ClientId` INTEGER (3) NOT NULL AUTO_INCREMENT,
`CreditCardId` BIGINT(10) NOT NULL ,
`Surname` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
`Name` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
`FirstName` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
`Phone` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
`Address` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
`SafeId` INTEGER (5) NOT NULL ,
PRIMARY KEY (`ClientId`, `CreditCardId`),
KEY `ClientId` (`ClientId`)

)ENGINE=InnoDB
AUTO_INCREMENT=11 CHARACTER SET "utf8" COLLATE "utf8_bin"

Допустим нам нужно получать по очереди каждый банк и производить с ним какие то действия, помочь в этом нам мог бы такой вот запрос

Select `bank`.* FROM `bank` LIMIT НОМЕР_НУЖНОЙ_НАМ_ЗАПИСИ,1

. Таким образом используя LIMIT НОМЕР_НУЖНОЙ_НАМ_ЗАПИСИ,1 мы извлекаем в цикле из таблицы bank по очереди каждую запись и производим с ней нужные нам действия, при этом увеличивая значение НОМЕР_НУЖНОЙ_НАМ_ЗАПИСИ на 1. Теперь сделаем то же самое но используя курсор

Begin
/* переменные куда мы извлекаем данные */
Declare vBankId integer ;
Declare vBankName VARCHAR (50);
Declare vAddress VARCHAR (50);
Declare vPhone VARCHAR (50);
/* переменная hadler - a*/
Declare done integer default 0;
/*Объявление курсора*/
Declare BankCursor Cursor for Select `bank`.`BankId`,`bank`.`BankName`,`bank`.`Address`,`bank`.`Phone`, FROM `bank` where 1;
/*HANDLER назначение, которого поясним чуть ниже*/
DECLARE CONTINUE HANDLER FOR SQLSTATE "02000" SET done=1;
/* открытие курсора */
Open BankCursor;
/*извлекаем данные */
WHILE done = 0 DO

делаем нужные нам действия
END WHILE ;
/*закрытие курсора */
Close BankCursor;
END ;

* This source code was highlighted with Source Code Highlighter .

Error: 1329 SQLSTATE: 02000 (ER_SP_FETCH_NO_DATA)

Message: No data - zero rows fetched, selected, or processed

SQLSTATE: 02000 срабатывает когда достигнут конец курсора, или когда select или update возвращяет пустую строку.

Следующей строкой мы объявили курсор DECLARE cursor_name CURSOR FOR select_statement;
Открываем курсор Open cursor_name;
Дальше пока не достигаем конец курсора (WHILE done = 0 DO) извлекаем данные и обрабатываем их.
Перед выходом из хранимой процедуры необходимо курсор закрыть. Close cursor_name;

Вроде ничего сложного. Но с SQLSTATE "02000" связанно много подводных камней.

WHILE done = 0 DO
FETCH BankCursor INTO vBankId,vBankName,vAddress,vPhone;

Select (ContributeAmount) INTO vContributeAmountSUM FROM bankdistribution where BankId = vBankId limit 1;
делаем какие то действия
END WHILE ;

* This source code was highlighted with Source Code Highlighter .

Всё хорошо, и правильно с точки зрения синтаксиса. Но с логической точки зрения нет. Может случиться так что вкладчики не открыли счетов в каком то банке, тогда для Select (ContributeAmount) INTO vContributeAmountSUM FROM bankdistribution where BankId = vBankId limit 1; сработает SQLSTATE: 02000, переменная done установится в 1, и цикл while закончиться раньше чем мы ожидали. Этого можно избежать поступив следующим образом

WHILE done = 0 DO
FETCH BankCursor INTO vBankId,vBankName,vAddress,vPhone;
/* извлечем для банка сумму любого из его вкладов */


if (vContributeAmountSUM > 0) then
/* извлечем для банка сумму любого из его вкладов */

end if ;
делаем какие то действия
END WHILE ;

* This source code was highlighted with Source Code Highlighter .

первым запросом мы проверили а есть ли вклады (если их нет то vContributeAmountSUM == 0) и только если таковые имеются мы извлекаем данные.

Теперь допустим нам нужно излечь общую сумму на счетах в разных банках у каждого клиента
Declare ClientSummCursor Cursor for Select sum

Declare ClientSummCursor Cursor for Select sum (`bankdistribution`.`ContributeAmount`),`bankdistribution`.`ClientId` FROM `bankdistribution` Inner Join client on (client.ClientId = bankdistribution.`ClientId`) where 1 group by `bankdistribution`.`ClientId`;

Open ClientSummCursor;
WHILE done = 0 DO
FETCH BankCursor INTO vBankId,vBankName,vAddress,vPhone;
/* извлечем для банка сумму любого из его вкладов */
Select Сount(ContributeAmount) INTO vContributeAmountSUM FROM bankdistribution where BankId = vBankId limit 1;
/* проверим действительно ли есть вклады в этом банке */
if (vContributeAmountSUM > 0) then
/* извлечем для банка сумму любого из его вкладов */
Select ContributeAmount INTO vContributeAmountSUM FROM bankdistribution where BankId = vBankId limit 1;
end if ;


делаем какие то действия.
END WHILE ;

* This source code was highlighted with Source Code Highlighter .

Может возникнуть та же ситуация, когда данные в курсоре ClientSummCursor, закончатся раньше чем данные в BankCursor, сработает SQLSTATE: 02000, переменная done установится в 1, и цикл while закончиться раньше чем мы ожидали. Этого можно избежать поступив следующим образом

Open ClientSummCursor;
WHILE done = 0 DO
FETCH BankCursor INTO vBankId,vBankName,vAddress,vPhone;
/* извлечем для банка сумму любого из его вкладов */
Select Сount(ContributeAmount) INTO vContributeAmountSUM FROM bankdistribution where BankId = vBankId limit 1;
/* проверим действительно ли есть вклады в этом банке */
if (vContributeAmountSUM > 0) then
/* извлечем для банка сумму любого из его вкладов */
Select ContributeAmount INTO vContributeAmountSUM FROM bankdistribution where BankId = vBankId limit 1;
end if ;
/* до извлечения данных из второго курсора запомним состояние sqlstate */
SET old_status = done;
/* извлекаем нужные нам данные */
FETCH ClientSummCursor INTO vSum,vClientId;
/* проверяем были ли извлечены данные, не стработал ли sqlstate 0200 */
if (done = 0) then
делаем какие то действия.
end if ;
/* перед окончанием while восттановим значение переменной done */
set done = old_status;
END WHILE ;

* This source code was highlighted with Source Code Highlighter .

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

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

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

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

EXEC test_PROCEDURE par1, par2

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

SELECT my_fun(id) FROM test_table

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

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

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

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

CREATE TABLE .( (18, 0) NULL, (50) NULL, (50) NULL) ON GO

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

CREATE PROCEDURE . (@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 .( (18, 0) NULL, (50) NULL, (50) NULL) ON GO

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

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

exec my_proc_test 1, ‘pole1_str1’, ‘pole2_str1’

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

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

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

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

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

CREATE PROCEDURE . 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 . 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

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