Присвоение значения переменной в языке Transact-SQL
При объявлении переменной присваивается значение NULL. Чтобы изменить значение переменной, применяется инструкция SET. Этот способ присвоения значений переменным является предпочтительным. Кроме того, переменной можно присвоить значение, указав ее в списке выбора инструкции SELECT.
Чтобы присвоить значение переменной при помощи инструкции SET, необходимо указать ее имя и присваиваемое значение. Этот способ присвоения значений переменным является предпочтительным. Например, следующий пакет объявляет две переменные, присваивает им значения и использует их в предложении WHERE инструкции SELECT:
USE AdventureWorks2008R2;GO-- Declare two variables.DECLARE @FirstNameVariable nvarchar(50), @PostalCodeVariable nvarchar(15); -- Set their values.SET @FirstNameVariable = N'Amy';SET @PostalCodeVariable = N'BA5 3HX'; -- Use them in the WHERE clause of a SELECT statement.SELECT LastName, FirstName, JobTitle, City, StateProvinceName, CountryRegionNameFROM HumanResources.vEmployeeWHERE FirstName = @FirstNameVariable OR PostalCode = @PostalCodeVariable;GOПеременной можно присвоить значение, указав ее в списке выбора. Если список выбора ссылается на переменную, то ей должно быть присвоено скалярное значение, или инструкция SELECT должна возвращать только одну строку. Например:
 Внимание! |
| Когда при выполнении инструкции SELECT переменной присваивается несколько значений, сервер SQL Server не гарантирует порядок вычисления выражений. Обратите внимание, что этот эффект проявляется, только если инструкция присваивает значение переменной. |
Если инструкция SELECT возвращает более одной строки, и переменная ссылается на нескалярное выражение, ей присваивается значение, которое возвращается для выражения в последней строке результирующего набора. Например, в следующем пакете переменной @EmpIDVariable присваивается значение идентификатора BusinessEntityID последней возвращенной строки, равное 1:
USE AdventureWorks2008R2;GODECLARE @EmpIDVariable int; SELECT @EmpIDVariable = BusinessEntityIDFROM HumanResources.EmployeeORDER BY BusinessEntityID DESC; SELECT @EmpIDVariable;GO54. Курсоры, их создание и типы.
Создание курсора
Под курсором в Oracle понимается получаемый при выполнении запроса результирующий набор и связанный с ним указатель текущей записи.
В PL/SQL поддерживаются два типа курсоров: явные и неявные.
Явный курсор объявляется разработчиком, а неявный курсор не требует объявления.
Курсор может возвращать одну строку, несколько строк или ни одной строки.
Для запросов, возвращающих более одной строки, можно использовать только явный курсор.
Курсор может быть объявлен в секциях объявлений любого блока PL/SQL, подпрограммы или пакета.
Для управления явным курсором используются операторы CURSOR , OPEN , FETCH и CLOSE .
Оператор CURSOR выполняет объявление явного курсора .
Оператор OPEN открывает курсор, создавая новый результирующий набор на базе указанного запроса.
Оператор FETCH выполняет последовательное извлечение строк из результирующего набора от начала до конца.
Оператор CLOSE закрывает курсор и освобождает занимаемые им ресурсы.
Для объявления явного курсора используется оператор CURSOR , который может иметь следующее формальное описание:
CURSOR cursor_name [(parameter[,parameter]...)] [RETURN return_type] IS select_statement;Каждый параметр parameter определяется как:
cursor_parameter_name [IN] datatype [{:= | DEFAULT} expr]Параметр return_type определяет запись или строку таблицы базы данных, используемую для возвращаемых значений. Тип возвращаемого значения должен соответствовать столбцам, перечисленным в операторе SELECT. Список параметров определяет параметры курсора, передаваемые на сервер каждый раз при выполнении оператора OPEN .
Одновременно с созданием результирующего набора можно выполнить блокировку выбираемых строк. Для этого в операторе SELECT следует указать фразу FOR UPDATE.
Для задания параметров курсора подходит как позиционная, так и именованная нотация.
Для работы с курсором можно использовать следующие атрибуты, указываемые после имени курсора:
· %ISOPEN - возвращает значение TRUE, если курсор открыт.
· %FOUND - определяет, найдена ли строка, удовлетворяющая условию.
· %NOTFOUND - возвращает TRUE, если строка не найдена.
· %ROWCOUNT - возвращает номер текущей строки.
Оператор FETCH может быть выполнен в цикле LOOP-END LOOP. Это позволяет последовательно просматривать весь результирующий набор, который был открыт оператором OPEN .
Например:
DECLARE CURSOR c1 IS SELECT f1, f2, f3, f4 FROM tbl1 WHERE f4 > 100; CURSOR c2 RETURN tbl2%ROWTYPE IS SELECT * FROM tbl2 WHERE f1_t2 = 10; - Список параметров - курсора CURSOR c3 (p1 INTEGER DEFAULT 10, p2 INTEGER DEFAULT 1300) IS SELECT f1, f2, f3, f4 FROM tbl1 WHERE f4 > p1 AND f2 = p2;- ...BEGIN OPEN c1; - Открытие курсора c1LOOP - Выборка одной строки FETCH c1 INTO rec1; - Строка успешно выбрана EXIT WHEN c1%NOTFOUND;END LOOP;- Закрытие курсораCLOSE c1;- Открытие курсора c3OPEN c3(10,700);- ...END;Для повторного создания результирующего набора для других значений параметров курсор следует закрыть, а затем повторно открыть.
При выполнении SQL-оператора, для которого не был объявлен явный курсор, Oracle автоматически открывает неявный курсор.
При применении неявного курсора нельзя использовать операторы управления курсором OPEN , FETCH и CLOSE .
Если при неявном курсоре в результирующий набор записывается более одной строки, то Oracle инициирует исключение TOO_MANY_ROWS.
Если курсор создается в пакете, то его объявление и спецификация могут быть разделены: объявление курсора указывается в секции объявлений пакета, а спецификация курсора - в теле пакета.
Объявление курсора при создании пакета может иметь следующее формальное описание:
CURSOR cursor_name [(parameter [, parameter]...)]RETURN return_type;Например:
- Создание пакетаCREATE PACKAGE p1 AS - Объявление курсора CURSOR c1 RETURN tbl1%ROWTYPE;END p1;- Создание тела пакетаCREATE PACKAGE BODY p1 AS- Спецификация курсораCURSOR c1 RETURN tbl1%ROWTYPE IS SELECT * FROM tbl1 WHERE f3 > 700;END p1;ODBC определяет четыре типа курсоров, поддерживаемых Microsoft SQL Server и драйвером ODBC для собственного клиента SQL Server. Эти курсоры различаются по способности обнаруживать изменения в результирующем наборе и по потребляемым ими ресурсам, например памяти и пространству в базе данных tempdb. Курсор может обнаружить изменения в строках только при попытках повторной выборки этих данных; не существует способа для источника данных известить курсор об изменениях в текущих выбранных строках. На способность курсора обнаруживать изменения, которые не были внесены через курсор, также влияет уровень изоляции транзакций.
Существует четыре типа курсоров ODBC, поддерживаемых SQL Server.
· Однопроходные курсоры не поддерживают прокрутку, они поддерживают только последовательную выборку строк от начала курсора до его конца.
· Статические курсоры встраиваются в tempdb при открытии курсора. Они всегда отображают результирующий набор точно в том виде, в котором он был при открытии курсора. Они никогда не отражают изменения в данных. SQL Server статические курсоры всегда доступны только для чтения. Так как статический серверный курсор построен как рабочая таблица базы данных tempdb, то размер строк результирующего набора не может превышать максимальный размер строк, разрешенный в SQL Server.
· Курсоры, управляемые набором ключей, имеют членство и порядок строк в результирующем наборе, установленные при открытии курсора. Изменения в неключевых столбцах видимы через курсор.
· Динамические курсоры — это противоположность статических курсоров. Динамические курсоры отражают все изменения строк в результирующем наборе. Значения типа данных, порядок и членство строк в результирующем наборе могут меняться для каждой выборки.
55. Назначение курсоров. Последовательность операций с курсором.
Операции в реляционной базе данных выполняются над множеством строк. Например, набор строк, возвращаемый инструкцией SELECT, содержит все строки, которые удовлетворяют условиям, указанным в предложении WHERE инструкции. Такой полный набор строк, возвращаемых инструкцией, называется результирующим набором.Приложения, особенно интерактивные, не всегда эффективно работают с результирующим набором как с единым целым. Им нужен механизм, позволяющий обрабатывать одну строку или небольшое их число за один раз. Курсоры являются расширением результирующих наборов, которые предоставляют такой механизм.
Курсоры позволяют усовершенствовать обработку результатов:
· позиционируясь на отдельные строки результирующего набора;
· получая одну или несколько строк от текущей позиции в результирующем наборе;
· поддерживая изменение данных в строках в текущей позиции результирующего набора;
· поддерживая разные уровни видимости изменений, сделанных другими пользователями для данных, представленных в результирующем наборе;
· предоставляя инструкциям Transact-SQL в скриптах, хранимых процедурах и триггерах доступ к данным результирующего набора.
Основные понятия
Реализации курсоров
SQL Server поддерживает три способа реализации курсоров.
курсоры Transact-SQL
Основаны на синтаксисе DECLARE CURSOR и в основном используются в скриптах, хранимых процедурах и триггерах Transact-SQL . Transact-SQL реализуются на сервере и управляются инструкциями Transact-SQL , отправляемыми от клиента серверу. Они также могут содержаться в пакетах, хранимых процедурах или триггерах.
Серверные курсоры интерфейса прикладного программирования (API)
Поддерживают функции курсоров API в OLE DB и ODBC. Курсоры API реализуются на сервере. Всякий раз, когда клиентское приложение вызывает функцию курсора API, поставщик OLE DB или драйвер ODBC для собственного клиента SQL Server передает требование на сервер для выполнения действия в отношении серверного курсора API.
Клиентские курсоры
Реализуются внутренне драйвером ODBC для собственного клиента SQL Server и DLL, реализующим API-интерфейс ADO. Клиентские курсоры реализуются посредством кэширования всех строк результирующего набора на клиенте. Каждый раз, когда клиентское приложение вызывает функцию курсора API, драйвер ODBC для собственного клиента SQL Server или ADO DLL выполняет операцию курсора на строках результирующего набора, кэшированных на клиенте.
Типы курсоров
Однонаправленный
Курсор последовательного доступа не поддерживает прокрутку, он поддерживает только последовательную выборку строк от начала курсора до его конца. Строки нельзя получить из базы данных, пока они не будут выбраны. Результаты всех инструкций INSERT, UPDATE и DELETE, влияющих на строки результирующего набора (выполненных текущим пользователем или зафиксированных другими пользователями), отображаются как строки, выбранные из курсора.
Так как курсор не может быть прокручен назад, большинство изменений, сделанных в строках базы данных после извлечения сроки, не видны через курсор. Если значение, использованное для определения положения строки в результирующем наборе, модифицируется, например в случае обновления столбца, входящего в кластеризованный индекс, то значение видимо через курсор.
Хотя в моделях курсоров API базы данных курсор последовательного доступа рассматривается как курсор отдельного типа, в SQL Server принят другой подход. SQL Server принимает однонаправленность и возможность прокрутки курсоров как параметры, которые могут быть применены к статическим, управляемым набором ключей и динамическим курсорам. Transact-SQL курсоры поддерживают однонаправленные статические, управляемые набором ключей и динамические курсоры. Модели курсора API базы данных предполагают, что статические, управляемые набором ключей и динамические курсоры всегда могут быть прокручены. Если атрибут или свойство курсора API базы данных установлены в значение «однонаправленный», SQL Server реализует это как однонаправленный динамический курсор.
Статические
Полный результирующий набор статического курсора создается в базе данных tempdb при открытии курсора. Статический курсор всегда отображает результирующий набор точно в том виде, в котором он был при открытии курсора. Статическими курсорами обнаруживаются лишь некоторые изменения или не обнаруживаются вовсе, но при этом в процессе прокрутки такие курсоры потребляют сравнительно мало ресурсов.
Курсор не отражает изменения в базе данных, влияющие на вхождение в результирующий набор или изменяющие значения в столбцах строк, составляющих набор строк. Статический курсор не отображает новые строки, вставленные в базу данных после открытия курсора, даже если они соответствуют критериям поиска инструкции SELECT курсора. Если входящие в результирующий набор строки обновляются другими пользователями, то новые значения данных в статическом курсоре не отображаются. Статический курсор продолжает отображать строки, удаленные из базы данных после открытия курсора. Операции UPDATE, INSERT и DELETE не отображаются в статическом курсоре (до тех пор, пока курсор не будет закрыт и открыт повторно), не отображаются даже изменения, сделанные в том же соединении, в котором был открыт курсор.
SQL Server статические курсоры всегда доступны только для чтения.
Так как результирующий набор статического курсора хранится в рабочей таблице базы данных tempdb, то размер строк результирующего набора не может превышать максимальный размер строк таблицы SQL Server .
Transact-SQL использует для описания статических курсоров термин «нечувствительный».Некоторые интерфейсы API баз данных называют их курсорами моментальных снимков.
Keyset
Членство и порядок строк в курсоре, управляемом набором ключей, являются фиксированными при открытии курсора. Такие курсоры управляются с помощью набора уникальных идентификаторов — ключей. Ключи создаются из набора столбцов, который уникально идентифицирует строки результирующего набора. Набор ключей — это набор ключевых значений всех строк, попадающих под действие инструкции SELECT на момент открытия курсора. Набор ключей, управляющий курсором, создается в базе данных tempdbпри открытии курсора.
Динамический
Динамические курсоры — это противоположность статических курсоров. Динамические курсоры отражают все изменения строк в результирующем наборе при прокрутке курсора.Значения типа данных, порядок и членство строк в результирующем наборе могут меняться для каждой выборки. Все инструкции UPDATE, INSERT и DELETE, выполняемые пользователями, видимы посредством курсора. Обновление видимы сразу, если они сделаны посредством курсора с помощью либо API-функции (например, SQLSetPos ), либо предложения WHERE CURRENT OF Transact-SQL . Обновления, сделанные вне курсора, не видны до момента фиксации, если только уровень изоляции транзакций с курсорами не имеет значение READ UNCOMMITTED. В планах динамических курсоров никогда не используются пространственные индексы.
Запрос курсора
SQL Server поддерживает два метода запроса курсоров.
· Transact-SQL
Язык Transact-SQL поддерживает синтаксис для использования курсоров, созданных в соответствии с синтаксисом курсоров ISO.
· API-функции курсоров базы данных.
SQL Server поддерживает функциональность курсоров для следующих API-интерфейсов баз данных:
o ADO ( Microsoft ActiveX Data Object);
o OLE DB
o открытый интерфейс доступа к базам данных (ODBC).
Оба этих способа никогда не должны использоваться в приложении одновременно.Приложение, применяющее API-интерфейс для определения режима работы курсоров, не может затем выполнить инструкцию Transact-SQL DECLARE CURSOR для запроса нового курсора Transact-SQL . Инструкция DECLARE CURSOR может использоваться только в том случае, если все атрибуты API-курсоров будут установлены в значения по умолчанию.
Если не был запрошен ни Transact-SQL , ни API-курсор, SQL Server целиком возвращает по умолчанию результирующий набор приложению (это называется результирующим набором по умолчанию).
Обработка курсоров
Transact-SQL и API-курсоры имеют различный синтаксис, но для всех курсоров SQL Server используется одинаковый цикл обработки.
1. Связать курсор с результирующим набором инструкции Transact-SQL и задать его характеристики (например возможность обновления строк).
2. Выполнить инструкцию Transact-SQL для заполнения курсора.
3. Получить в курсор необходимые строки. Операция получения в курсор одной и более строк называется выборкой. Выполнение серии выборок для получения строк в прямом или обратном направлении называется прокруткой.
4. При необходимости выполнить операции изменения (обновления или удаления) строки в текущей позиции курсора.
5. Закрыть курсор.
Курсор в SQl – это временная выборка записей в процессе выполнения функции, над которой могут выполняться необходимые вам действия, данная выборка является указателем на область памяти.
Курсоры могут быть очень полезны, например если вам в функции необходимо выполнять определенные действия с каким то набором строк, до начала выполнения функции вы даже не знаете, сколько строк будет при обработке той или иной записи. Если проще курсор это просто запрос, который запускается в процессе выполнения функции.
Например, у вас есть определенный запрос (набор записей) над каждой строкой которого необходимо выполнять какие то хитрые действия, которые нужно запомнить для операции над следующей строкой этого же запроса.
О том, что курсоры могут быть полезны, мы поговорили, но когда их лучше использовать? А использовать их лучше всего только тогда, когда у вас нет другого выхода! Потому что курсор является очень ресурсоемким решением. В принципе если Вы будете выполнять операции над небольшим количеством записей то это приемлемо, а если необходимо обработать большой объем данных, то вы можете очень долго ждать, пока будет выполняться ваша функция, а как вы знаете быстрота в нашем деле чуть ли не главный фактор.
О теории мы поговорили пора переходить к практике, и для начала общий синтаксис курсора в функции.
CREATE OR REPLACE FUNCTION название функции(типы переменных)
RETURNS тип возвращаемого значения AS
$BODY$
DECLARE
объявление переменных
объявление курсора
BEGIN
открытие курсора
перебор данных и операции над ними
закрытие курсора
RETURN возвращение значения;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
Теперь давайте приведем рабочий пример использования курсора, т.е. напишем функцию, в которой мы будем использовать курсор.
Примечание! Данный пример не из жизни, он смоделирован мной, поэтому у вас такой ситуации может и не возникнуть.
Для начала давайте определим, с какими данными мы будем работать, допустим, у нас есть таблица, в которой хранятся записи о сотрудниках которые постоянно находятся в командировках, или просто у них есть служебный транспорт, да это и не важно, важно то, что им выделяют деньги на расходы, а они отчитываются по этим расходам.
Пример таблицы с несколькими записями:
| id_per | id_user | rashod | summa | pr |
где,
id_per – период по которому идет отчет;
id_user – идентификатор сотрудника;
rashod – сумма расходов за этот период;
summa - сумма которая выделялась на расходы;
pr – возможная премия, на погашение задолженности в прошлом периоде.
Стоит следующая задача, нам необходимо определить тот период у сотрудников, в котором они расходовали средств больше, чем им выдали, и потом не возместили. При условии, что в следующем месяце им могут возместить этот расход (колонка pr) а могут и не возместить.
Т.е. в нашем примере у сотрудника с id_user = 1 этот период будет с id_per = 2, а у сотрудника с id_user = 2 этот период будет с id_per = 3, другими словами во втором периоде они оба перерасходовали выданные им средства, но сотруднику с id_user = 2 в следующем месяце их возместили, а с id_user = 1 нет, поэтому первый период возникновения перерасхода (причем не погашенного) у сотрудника с id_user = 1 будет именно период с id_per = 2.
В общем как то так:). Но для нас главное научится использовать курсор в функции, и наша функция будет выглядеть вот так.
Схема в базе PostgreSQL называется test и таблица тоже называется test, а функцию я назвал test.my_fun(numeric). Numeric – это как вы помните тип входящего параметра.
CREATE OR REPLACE FUNCTION test.my_fun(numeric)
RETURNS numeric AS
$BODY$
DECLARE
_id_user ALIAS FOR $1;
--объявляем курсор
crs_my CURSOR FOR select id_per, rashod, summa from test.test where
id_user = _id_user order by id_per;
--объявляем нужные нам переменные
_id_per numeric;
_rashod numeric;
_summa numeric;
_pr numeric;
_var numeric;
_rezult numeric;
BEGIN
_pr:=0;
OPEN crs_my;--открываем курсор
LOOP --начинаем цикл по курсору
--извлекаем данные из строки и записываем их в переменные
FETCH crs_my INTO _id_per, _rashod, _summa;
--если такого периода и не возникнет, то мы выходим
IF NOT FOUND THEN EXIT;END IF;
--ищем сумму возмещения, если она была
select into _pr pr from test.test where id_user=_id_user and id_per = _id_per+1;
_var = _rashod - _summa;
if _var > 0 then
_var = _var - _pr;
End if;
_rezult=_id_per;
--если _var даже после возмещения больше нуля, то выходим и возвращаем период
EXIT when _var > 0;
END LOOP;--заканчиваем цикл по курсору
CLOSE crs_my; --закрываем курсор
RETURN _rezult;--возвращаем результат
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
В функции я все прокомментировал, надеюсь понятно. Главное здесь это наш курсор crs_my, затем мы просто открываем его, извлекаем данные с помощью FETCH по каждой строке наших данных и делаем это с помощью цикла LOOP. Есть один нюанс, когда будете писать запрос для курсора, в ваших функциях то учитывайте сортировку, потому что цикл работает с первой строки записи, и если необходимо изменить порядок извлечения данных то делайте это в запросе, который используется в курсоре.
Использовать функцию можно вот так:
Select test.my_fun(1)
Результат, как вы помните, будет 2.
Если хотите запустить по всем записям, то используйте вот такой запрос:
Select test.my_fun(id_user) from test.test
Вот такой простой пример! Надеюсь, курсоры вам как-то помогут в ваших функциях, но снова напомню, злоупотреблять курсорами не следует, а использовать их нужно только по необходимости. Удачи!
· Открытие - при открытии курсора, переменные связывания используются для определения результирующего множества команды SQL. Указатель активной (текущей) строки устанавливается на первой строке. В некоторых случаях явное открытие курсора не требуется; ядро PL/SQL само выполняет эту операцию (например, для неявных курсоров или встроенного динамического SQL).
· Исполнение - на этапе исполнения оператор выполняется внутри ядра SQL.
· Выборка - при выполнении запроса команда FETCH извлекает следующую строку из результирующего множества курсора. При каждой выборке PL/SQL передвигает курсор вреперд на одну строку по результирующему множеству. При работе с явными курсорами следует помнить, что в случае, когда строк для извлечения больше нет, FETCH ничего не делает (не инициирует исключение).
· Закрытие - на этом этапе курсор закрывается, освобождается используемая им память. После закрытия курсор уже не содержит результирующее множество. В некоторых случаях явное закрытие курсора не требуется, ядро PL/SQL само выполняет эту операцию (например, для неявных курсоров или встроенного динамического SQL
56. Модификация и удаление строк через курсоры.
Если ваш курсор является модифицируемым. Изменение исходных значений во множестве курсора выполняется достаточно просто. Предусмотрена специальная форма фразы WHERE, которая поддерживает модификацию через курсор:
UPDATE таблица_или_представлениеSET список_для_модификацииWHERE CURRENT OF курсор_или_переменнаяЭто называется позиционным обновлением. Transact-SQL также поддерживает позиционное удаление, которое имеет следующую форму записи:
DELETE таблица_или_представлениеWHERE CURRENT OF курсор_или_переменная