• ×

    • Курсоры в хранимых процедурах MySQL. Курсоры в Mysql Курсоры в sql синтаксис и примеры

    09.02.2022

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

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

    Существует процедура, которая выполняет какие-то действия, которые не может выполнить обычная функция 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

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

    Явный курсор представляет собой команду 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.

    Курсор в SQL – это область в памяти базы данных, которая предназначена для хранения последнего оператора SQL. Если текущий оператор – запрос к базе данных, в памяти сохраняется и строка данных запроса, называемая текущим значением, или текущей строкой курсора . Указанная область в памяти поименована и доступна для прикладных программ.

    В соответствии со стандартом SQL при работе с курсорами можно выделить следующие основные действия :

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

    SQL Server поддерживает три вида курсоров :

    • курсоры SQL применяются в основном внутри триггеров, хранимых процедур и сценариев;
    • курсоры сервера действуют на сервере и реализуют программный интерфейс приложений для ODBC, OLE DB, DB_Library;
    • курсоры клиента реализуются на самом клиенте. Они выбирают весь результирующий набор строк из сервера и сохраняют его локально, что позволяет ускорить операции обработки данных за счет снижения потерь времени на выполнение сетевых операций.

    Управление курсором в среде MS SQL Server

    Управление курсором реализуется путем выполнения следующих команд:

    • DECLARE – создание или объявление курсора ;
    • OPEN – открытие курсора , т.е. наполнение его данными;
    • FETCH – выборка из курсора и изменение строк данных с помощью курсора;
    • CLOSE – закрытие курсора ;
    • DEALLOCATE – освобождение курсора , т.е. удаление курсора как объекта.

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

    В стандарте SQL для создания курсора предусмотрена следующая команда:

    <создание_курсора>::= DECLARE имя_курсора CURSOR FOR SELECT_оператор ]}]

    При использовании ключевого слова INSENSITIVE будет создан статический курсор . Изменения данных не разрешаются, кроме того, не отображаютсяизменения , сделанные другими пользователями. Если ключевое слово INSENSITIVE отсутствует, создается динамический курсор .



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

    SELECT-оператор задает тело запроса SELECT, с помощью которого определяется результирующий набор строк курсора .

    При указании аргумента FOR READ_ONLY создается курсор "только для чтения", и никакие модификации данных не разрешаются. Он отличается отстатического , хотя последний также не позволяет менять данные. В качестве курсора "только для чтения" может быть объявлен динамический курсор , что позволит отображать изменения , сделанные другим пользователем.

    Создание курсора с аргументом FOR UPDATE позволяет выполнять в курсоре изменение данных либо в указанных столбцах, либо, при отсутствии аргумента OF имя_столбца, во всех столбцах.

    В среде MS SQL Server принят следующий синтаксис команды создания курсора :

    <создание_курсора>::= DECLARE имя_курсора CURSOR FOR SELECT_оператор ]]

    При использовании ключевого слова LOCAL будет создан локальный курсор , который виден только в пределах создавшего его пакета, триггера, хранимой процедуры или пользовательской функции. По завершении работы пакета, триггера, процедуры или функции курсор неявно уничтожается. Чтобы передать содержимое курсора за пределы создавшей его конструкции, необходимо присвоить его параметру аргумент OUTPUT.

    Если указано ключевое слово GLOBAL, создается глобальный курсор ; он существует до закрытия текущего соединения.

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

    При указании SCROLL создается прокручиваемый курсор ; обращаться к данным можно в любом порядке и в любом направлении.

    При указании STATIC создается статический курсор .

    При указании KEYSET создается ключевой курсор.

    При указании DYNAMIC создается динамический курсор .

    Если для курсора READ_ONLY указать аргумент FAST_FORWARD, то созданный курсор будет оптимизирован для быстрого доступа к данным. Этот аргумент не может быть использован совместно с аргументами FORWARD_ONLY и OPTIMISTIC.

    В курсоре , созданном с указанием аргумента OPTIMISTIC, запрещается изменение и удаление строк , которые были изменены после открытия курсора .

    При указании аргумента TYPE_WARNING сервер будет информировать пользователя о неявном изменении типа курсора , если он несовместим с запросомSELECT.

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

    Для открытия курсора и наполнения его данными из указанного при создании курсора запроса SELECT используется следующая команда:

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

    После открытия курсора происходит выполнение связанного с ним оператора SELECT, выходные данные которого сохраняются в многоуровневой памяти.

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

    Сразу после открытия курсора можно выбрать его содержимое (результат выполнения соответствующего запроса) посредством следующей команды:

    FETCH [ FROM ]{{имя_курсора }| @имя_переменной_курсора } ]

    При указании FIRST будет возвращена самая первая строка полного результирующего набора курсора , которая становится текущей строкой.

    При указании LAST возвращается самая последняя строка курсора . Она же становится текущей строкой.

    При указании NEXT возвращается строка, находящаяся в полном результирующем наборе сразу же после текущей. Теперь она становится текущей. По умолчанию команда FETCH использует именно этот способ выборки строк.

    Ключевое слово PRIOR возвращает строку, находящуюся перед текущей. Она и становится текущей.

    Аргумент ABSOLUTE {номер_строки | @переменная_номера_строки} возвращает строку по ее абсолютному порядковому номеру в полном результирующем наборе курсора . Номер строки можно задать с помощью константы или как имя переменной, в которой хранится номер строки. Переменная должна иметь целочисленный тип данных. Указываются как положительные, так и отрицательные значения. При указании положительного значения строка отсчитывается от начала набора, отрицательного – от конца. Выбранная строка становится текущей. Если указано нулевое значение, строка не возвращается.

    Аргумент RELATIVE {кол_строки | @переменная_кол_строки} возвращает строку, находящуюся через указанное количество строк после текущей. Если указать отрицательное значение числа строк, то будет возвращена строка, находящаяся за указанное количество строк перед текущей. При указании нулевого значения возвратится текущая строка. Возвращенная строка становится текущей.

    Чтобы открыть глобальный курсор , перед его именем требуется указать ключевое слово GLOBAL. Имя курсора также может быть указано с помощью переменной.

    В конструкции INTO @имя_переменной [,...n] задается список переменных, в которых будут сохранены соответствующие значения столбцов возвращаемой строки. Порядок указания переменных должен соответствовать порядку столбцов в курсоре , а тип данных переменной – типу данных в столбце курсора . Если конструкция INTO не указана, то поведение команды FETCH будет напоминать поведение команды 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 Server (начиная с 2008)База данных SQL AzureХранилище данных SQL AzureParallel Data Warehouse

    Определяет такие атрибуты серверного курсора языка Transact-SQL, как свойства просмотра и запрос, используемый для построения результирующего набора, на котором работает курсор. Инструкция DECLARE CURSOR поддерживает как синтаксис стандарта ISO, так и синтаксис, использующий набор расширений языка Transact-SQL.

    ISO Syntax DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR FOR select_statement [ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ] [; ] Transact-SQL Extended Syntax DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ] [ FORWARD_ONLY | SCROLL ] [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] [ TYPE_WARNING ] FOR select_statement [ FOR UPDATE [ OF column_name [ ,...n ] ] ] [; ]

    cursor_name
    cursor_name

    INSENSITIVE
    tempdb ; таким образом, изменения базовых таблиц не отражаются в возвращенных выборками этого курсора данных, и этот курсор не допускает изменения. При использовании синтаксиса ISO, если не указан параметр INSENSITIVE, зафиксированные обновления и удаления, сделанные в базовых таблицах, отображаются в последующих выборках.

    SCROLL
    Указывает, что доступны все параметры выборки (FIRST, LAST, PRIOR, NEXT, RELATIVE, ABSOLUTE). Если в инструкции DECLARE CURSOR стандарта ISO не указан параметр SCROLL, то поддерживается только параметр выборки NEXT. Параметр SCROLL не может указываться вместе с параметром FAST_FORWARD.

    select_statement
    Стандартная инструкция SELECT, которая определяет результирующий набор курсора. Ключевые слова FOR BROWSE и INTO недопустимы в select_statement объявление курсора.

    select_statement конфликт с курсором запрошенного типа.

    READ ONLY

    Обновление ]
    column_name [, .. .n ] указан, только перечисленные столбцы позволяют вносить изменения. Если инструкция UPDATE используется без списка столбцов, то обновление возможно для всех столбцов.

    cursor_name
    Имя Transact-SQL определенного серверного курсора. cursor_name должны соответствовать правилам для идентификаторов.

    LOCAL
    Указывает, что курсор является локальным по отношению к пакету, хранимой процедуре или триггеру, в котором он был создан. Имя курсора допустимо только внутри этой области. На курсор могут ссылаться локальные переменные пакета, хранимые процедуры, триггеры или выходной параметр хранимой процедуры. Параметр OUTPUT используется для передачи локального курсора вызывающему его пакету, хранимой процедуре или триггеру, который затем может присвоить параметр переменной курсора с целью последующего обращения к курсору после завершения хранимой процедуры. Курсор неявно освобождается после завершения выполнения пакета, хранимой процедуры или триггера, за исключением случая, когда курсор был передан параметру OUTPUT. Если курсор был передан параметру OUTPUT, то курсор освобождается при освобождении всех ссылающихся на него переменных или при выходе из области видимости.

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

    FORWARD_ONLY
    Указывает, что курсор может просматриваться только от первой строки к последней. Поддерживается только параметр выборки FETCH NEXT. Если параметр FORWARD_ONLY указан без ключевых слов STATIC, KEYSET или DYNAMIC, то курсор работает как курсор DYNAMIC. Если не указаны ни аргумент FORWARD_ONLY, ни аргумент SCROLL, по умолчанию используется аргумент FORWARD_ONLY, если нет ключевых слов STATIC, KEYSET или DYNAMIC. Курсоры STATIC, KEYSET и DYNAMIC имеют значение по умолчанию SCROLL. В отличие от таких интерфейсов API баз данных, как ODBC и ADO, режим FORWARD_ONLY поддерживается следующими курсорами языка Transact-SQL: STATIC, KEYSET и DYNAMIC.

    STATIC
    Определяет курсор, который создает временную копию данных для использования курсором. Все запросы к курсору обращаются к указанной временной таблице в tempdb ; таким образом, изменения базовых таблиц не отражаются в возвращенных выборками этого курсора данных, и этот курсор не допускает изменения.

    KEYSET
    Указывает, что членство или порядок строк в курсоре неизменны при его открытии. Набор ключей, однозначно определяющих строки, встроен в таблицу в tempdb называется ключей .

    Изменения неключевых значений в базовых таблицах, сделанные владельцем курсора или зафиксированные другими пользователями, отображаются при просмотре курсора владельцем. Изменения, сделанные другими пользователями, не отображаются (изменения не могут быть сделаны с помощью серверного курсора языка Transact-SQL). Если удаляется строка, попытка выборки строк возвращает @@FETCH_STATUS -2. Обновления значений ключа из-за границ курсора аналогично удалению старой строки с последующей вставкой новой строки. Строка с новыми значениями не видна и попытки выборки строки со старыми значениями возвращают @@FETCH_STATUS -2. Обновления видимы сразу, если они сделаны через курсор с помощью предложения WHERE CURRENT OF.

    DYNAMIC
    Определяет курсор, отображающий все изменения данных, сделанные в строках результирующего набора при просмотре этого курсора. Значения данных, порядок и членство строк в каждой выборке могут меняться. Параметр выборки ABSOLUTE динамическими курсорами не поддерживается.

    FAST_FORWARD
    Указывает курсор FORWARD_ONLY, READ_ONLY, для которого включена оптимизация производительности. Параметр FAST_FORWARD не может указываться вместе с параметрами SCROLL или FOR_UPDATE.

    READ_ONLY
    Предотвращает изменения, сделанные через этот курсор. Предложение WHERE CURRENT OF не может иметь ссылку на курсор в инструкции UPDATE или DELETE. Этот параметр имеет преимущество над установленной по умолчанию возможностью обновления курсора.

    SCROLL_LOCKS
    Указывает, что позиционированные обновления или удаления, осуществляемые с помощью курсора, гарантированно будут выполнены успешно. SQL Server блокирует строки по мере их считывания в курсор для обеспечения доступности этих строк для последующих изменений. Параметр SCROLL_LOCKS не может указываться вместе с параметром FAST_FORWARD или STATIC.

    OPTIMISTIC
    Указывает, что позиционированные обновления или удаления, осуществляемые с помощью курсора, не будут выполнены, если с момента считывания в курсор строка была обновлена. SQL Server не блокирует строки по мере их считывания в курсор. Вместо этого используются сравнения timestamp значения столбца или контрольных сумм, если в таблице нет timestamp столбец, чтобы определить, изменялась ли строка после считывания в курсор. Если строка была изменена, то попытки позиционированного обновления или удаления будут безрезультатными. Параметр OPTIMISTIC не может указываться вместе с параметром FAST_FORWARD.

    TYPE_WARNING
    Указывает, что клиенту будет отправлено предупреждение, если курсор неявно будет преобразован из одного запрашиваемого типа в другой.

    select_statement
    Стандартная инструкция SELECT, которая определяет результирующий набор курсора. Ключевые слова COMPUTE, COMPUTE BY, FOR BROWSE и INTO недопустимы в select_statement объявление курсора.

    SQL Serverнеявно преобразует курсор в другой тип, если предложения в select_statement конфликт с курсором запрошенного типа. Дополнительные сведения см. в разделе «Неявные преобразования курсора».

    ДЛЯ обновления ]
    Определяет обновляемые столбцы в курсоре. If OF column_name [, ... n ] предоставляется, только перечисленные столбцы позволяют вносить изменения. Если инструкция UPDATE используется без списка столбцов, то обновление возможно для всех столбцов, за исключением случая, когда был указан параметр параллелизма READ_ONLY.

    Инструкция DECLARE CURSOR определяет такие атрибуты серверного курсора языка Transact-SQL, как свойства просмотра и запрос, используемый для построения результирующего набора, на котором работает курсор. Инструкция OPEN заполняет результирующий набор, а оператор FETCH возвращает из него строку. Инструкция CLOSE очищает текущий результирующий набор, связанный с курсором. Инструкция DEALLOCATE освобождает ресурсы, используемые курсором.

    Первая форма инструкции DECLARE CURSOR использует синтаксис ISO для задания параметров работы курсора. Вторая форма инструкции DECLARE CURSOR использует расширения языка Transact-SQL, позволяющие определять курсоры с помощью таких же типов, как типы, используемые в курсорных функциях API баз данных, таких как ODBC и ADO.

    Нельзя смешивать две эти формы. Если указать SCROLL или без УЧЕТА ключевые слова перед ключевым словом CURSOR, нельзя использовать ключевые слова между КУРСОРА, а также для select_statement ключевые слова. При указании ключевые слова между КУРСОРА, а также для select_statement ключевые слова, нельзя указать SCROLL или INSENSITIVE перед ключевым словом CURSOR.

    Если при использовании синтаксиса языка Transact-SQL для инструкции DECLARE CURSOR не указываются параметры READ_ONLY, OPTIMISTIC или SCROLL_LOCKS, то принимается следующее значение по умолчанию.

      Если инструкция SELECT не поддерживает обновления (или недостаточно разрешений, или при доступе к удаленным таблицам, не поддерживающим обновление, и т. п.), то курсору присваивается параметр READ_ONLY.

      Курсоры STATIC и FAST_FORWARD по умолчанию имеют значение READ_ONLY.

      Курсоры DYNAMIC и KEYSET по умолчанию имеют значение OPTIMISTIC.

    Ссылки на курсоры могут производиться только другими инструкциями языка Transact-SQL. Функции API баз данных не могут ссылаться на курсоры. Например, после объявления курсора функции и методы OLE DB, ODBC или ADO не могут ссылаться на его имя. Строки курсора не могут быть выбраны с помощью соответствующих функций и методов API; для этой цели необходимо использовать инструкции FETCH языка Transact-SQL.

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

    Переменные могут использоваться как часть select_statement , в котором объявлен курсор. Значения переменных курсора после его объявления не изменяются.

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

    Нельзя использовать курсоры или триггеры в таблице с кластеризованным индексом columnstore. Это ограничение не применяется к некластеризованные индексы; можно использовать курсоры и триггеры в таблице с некластеризованным индексом columnstore.

    A. Использование простого курсора и синтаксиса

    Результирующий набор, создаваемый при открытии данного курсора, включает в себя все строки и столбцы таблицы. Этот курсор можно обновлять, все обновления и удаления представлены в выборке для этого курсора. FETCH``NEXT доступна только выборка, поскольку SCROLL не был указан параметр.

    DECLARE vend_cursor CURSOR FOR SELECT * FROM Purchasing.Vendor OPEN vend_cursor FETCH NEXT FROM vend_cursor;

    Б. Использование вложенных курсоров для вывода отчета

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

    SET NOCOUNT ON ; DECLARE @vendor_id int , @vendor_name nvarchar (50 ), @message varchar (80 ), @product nvarchar (50 ); PRINT "-------- Vendor Products Report --------"; DECLARE vendor_cursor CURSOR FOR SELECT VendorID, Name FROM Purchasing.Vendor WHERE PreferredVendorStatus = 1 ORDER BY VendorID; OPEN vendor_cursor FETCH NEXT FROM vendor_cursor INTO @vendor_id, @vendor_name WHILE @@FETCH_STATUS = 0 BEGIN PRINT " " SELECT @message = "----- Products From Vendor: " + @vendor_name PRINT @message -- Declare an inner cursor based -- on vendor_id from the outer cursor. DECLARE product_cursor CURSOR FOR SELECT v.Name FROM Purchasing.ProductVendor pv, Production.Product v WHERE pv.ProductID = v.ProductID AND pv.VendorID = @vendor_id -- Variable value from the outer cursor OPEN product_cursor FETCH NEXT FROM product_cursor INTO @product IF @@FETCH_STATUS <> 0 PRINT " <>" WHILE @@FETCH_STATUS = 0 BEGIN SELECT @message = " " + @product PRINT @message FETCH NEXT FROM product_cursor INTO @product END CLOSE product_cursor DEALLOCATE product_cursor -- Get the next vendor. FETCH NEXT FROM vendor_cursor INTO @vendor_id, @vendor_name END CLOSE vendor_cursor; DEALLOCATE vendor_cursor;

    Похожие статьи
     
    Категории
    Видеоматериалы
    Популярное
    Новое