Declare Variables SQL Server
В SQL Server (Transact-SQL) переменная позволяет программисту временно хранить данные во время выполнения кода.
Синтаксис
Синтаксис объявления переменных в SQL Server с помощью оператора DECLARE:
DECLARE @variable_name datatype [ = initial_value ],
@variable_name datatype [ = initial_value ],
. ;
Параметры или аргументы
variable_name — имя для назначения переменной.
datatype — тип данных для назначения переменной.
initial_value — необязательный. Это значение, которое первоначально было присвоено переменной при ее объявлении.
Пример объявления переменной
Ррассмотрим пример объявления переменной в SQL Server.
Например:
Declare sql что это
DECLARE — определить курсор
Синтаксис
DECLAREимя[ BINARY ] [ INSENSITIVE ] [ [ NO ] SCROLL ] CURSOR [ < WITH | WITHOUT >HOLD ] FORзапрос
Описание
Оператор DECLARE позволяет пользователю создавать курсоры, с помощью которых можно выбирать по очереди некоторое количество строк из результата большого запроса. Когда курсор создан, через него можно получать строки, применяя команду FETCH .
Примечание
На этой странице описывается применение курсоров на уровне команд SQL. Если вы попытаетесь использовать курсоры внутри функции PL/pgSQL , правила будут другими — см. Раздел 40.7.
Параметры
Имя создаваемого курсора. BINARY
Курсор с таким свойством возвращает данные в двоичном, а не текстовом формате. INSENSITIVE
Указывает, что данные, считываемые из курсора, не должны зависеть от изменений, которые могут происходить в нижележащих таблицах после создания курсора. В PostgreSQL это поведение подразумевается по умолчанию, так что это ключевое слово ни на что не влияет и принимается только для совместимости со стандартом SQL. SCROLL
NO SCROLL
Указание SCROLL определяет, что курсор может прокручивать набор данных и получать строки непоследовательно (например, в обратном порядке). В зависимости от сложности плана запроса указание SCROLL может отрицательно отразиться на скорости выполнения запроса. Указание NO SCROLL , напротив, определяет, что через курсор нельзя будет получать строки в произвольном порядке. По умолчанию прокрутка в некоторых случаях разрешается; но это не равнозначно эффекту указания SCROLL . За подробностями обратитесь к Замечания. WITH HOLD
WITHOUT HOLD
Указание WITH HOLD определяет, что курсор можно продолжать использовать после успешной фиксации создавшей его транзакции. WITHOUT HOLD определяет, что курсор нельзя будет использовать за рамками транзакции, создавшей его. Если не указано ни WITHOUT HOLD , ни WITH HOLD , по умолчанию подразумевается WITHOUT HOLD . запрос
Команда SELECT или VALUES , выдающая строки, которые будут получены через курсор.
Ключевые слова BINARY , INSENSITIVE и SCROLL могут указываться в любом порядке.
Замечания
Обычный курсор выдаёт данные в текстовом виде, в каком их выдаёт SELECT . Однако с указанием BINARY курсор может выдавать их и в двоичном формате. Это упрощает операции преобразования данных для сервера и клиента, за счёт дополнительных усилий, требующихся от программиста для работы с платформозависимыми двоичными форматами. Например, если запрос получает значение 1 из целочисленного столбца, обычный курсор выдаст строку, содержащую 1 , тогда как через двоичный курсор будет получено четырёхбайтовое поле, содержащее внутреннее представление значения (с сетевым порядком байтов).
Двоичные курсоры должны применяться с осмотрительностью. Многие приложения, в том числе psql , не приспособлены к работе с двоичными курсорами и ожидают, что данные будут поступать в текстовом формате.
Примечание
Когда клиентское приложение выполняет команду FETCH , используя протокол « расширенных запросов » , в сообщении Bind этого протокола указывается, в каком формате, текстовом или двоичном, должны быть получены данные. Это указание переопределяет свойство курсора, заданное в его объявлении. Таким образом, концепция курсора, объявляемого двоичным, становится устаревшей при использовании протокола расширенных запросов — любой курсор может быть прочитан как текстовый или двоичный.
Если в команде объявления курсора не указано WITH HOLD , созданный ей курсор может использоваться только в текущей транзакции. Таким образом, оператор DECLARE без WITH HOLD бесполезен вне блока транзакции: курсор будет существовать только до завершения этого оператора. Поэтому PostgreSQL сообщает об ошибке, если такая команда выполняется вне блока транзакции. Чтобы определить блок транзакции, примените команды BEGIN и COMMIT (или ROLLBACK ).
Если в объявлении курсора указано WITH HOLD и транзакция, создавшая курсор, успешно фиксируется, к этому курсору могут продолжать обращаться последующие транзакции в этом сеансе. (Но если создавшая курсор транзакция прерывается, курсор уничтожается.) Курсор со свойством WITH HOLD (удерживаемый) может быть закрыт явно, командой CLOSE , либо неявно, по завершении сеанса. В текущей реализации строки, представляемые удерживаемым курсором, копируются во временный файл или в область памяти, так что они остаются доступными для следующих транзакций.
Объявить курсор со свойством WITH HOLD можно, только если запрос не содержит указаний FOR UPDATE и FOR SHARE .
Указание SCROLL добавляется при определении курсора, который будет выбирать данные в обратном порядке. Это поведение требуется стандартом SQL. Однако для совместимости с предыдущими версиями, PostgreSQL допускает выборку в обратном направлении и без указания SCROLL , если план запроса курсора достаточно прост, чтобы реализовать прокрутку назад без дополнительных операций. Тем не менее, разработчикам приложений не следует рассчитывать на то, что курсор, созданный без указания SCROLL , можно будет прокручивать назад. С указанием NO SCROLL прокрутка назад запрещается в любом случае.
Выборка в обратном направлении также запрещается, если запрос содержит указания FOR UPDATE и FOR SHARE ; в этом случае указание SCROLL не принимается.
Внимание
Прокручиваемые и удерживаемые ( WITH HOLD ) курсоры могут выдавать неожиданные результаты, если они вызывают изменчивые функции (см. Раздел 35.6). Когда повторно выбирается ранее прочитанная строка, функции могут вызываться снова и выдавать результаты, отличные от полученных в первый раз. Один из способов обойти эту проблему — объявить курсор с указанием WITH HOLD и зафиксировать транзакцию, прежде чем читать из него какие-либо строки. В этом случае весь набор данных курсора будет материализован во временном хранилище, так что изменчивые функции будут выполнены для каждой строки лишь единожды.
Если запрос в определении курсора включает указания FOR UPDATE или FOR SHARE , возвращаемые курсором строки блокируются в момент первой выборки, так же, как это происходит при выполнении SELECT с этими указаниями. Кроме того, при чтении строк будут возвращаться их наиболее актуальные версии; таким образом, с этими указаниями курсор будет вести себя как « чувствительный курсор » , определённый в стандарте SQL. (Указать INSENSITIVE для курсора с запросом FOR UPDATE или FOR SHARE нельзя.)
Внимание
Обычно рекомендуется использовать FOR UPDATE , если курсор предназначается для применения в командах UPDATE . WHERE CURRENT OF и DELETE . WHERE CURRENT OF . Указание FOR UPDATE предотвращает изменение строк другими сеансами после того, как они были считаны, и до того, как выполнится команда. Без FOR UPDATE последующая команда с WHERE CURRENT OF не сработает, если строка будет изменена после создания курсора.
Ещё одна причина использовать указание FOR UPDATE в том, что без него последующие команды с WHERE CURRENT OF могут выдать ошибку, если запрос курсора не удовлетворяет оговоренному в стандарте SQL критерию « простой изменяемости » (в частности, курсор должен ссылаться только на одну таблицу и не должен использовать группировку и сортировку ( ORDER BY )). Курсоры, не удовлетворяющие этому критерию, могут работать либо не работать, в зависимости от конкретного выбранного плана; так что в худшем случае приложение может работать в тестовой, но сломается в производственной среде. С указанием FOR UPDATE курсор гарантированно будет изменяемым.
Не использовать же FOR UPDATE для команд с WHERE CURRENT OF в основном имеет смысл, только если требуется получить прокручиваемый курсор или курсор, не отражающий последующие изменения (то есть, продолжающий показывать прежние данные). Если это действительно необходимо, обязательно учтите при реализации приведённые выше замечания.
В стандарте SQL механизм курсоров предусмотрен только для встраиваемого SQL . Сервер PostgreSQL не реализует для курсоров оператор OPEN ; курсор считается открытым при объявлении. Однако ECPG , встраиваемый препроцессор SQL для PostgreSQL , следует соглашениям стандарта, в том числе поддерживая для курсоров операторы DECLARE и OPEN .
Получить список всех доступных курсоров можно, обратившись к системному представлению pg_cursors .
Примеры
DECLARE liahona CURSOR FOR SELECT * FROM films;
Другие примеры использования курсора можно найти в FETCH .
Совместимость
В стандарте SQL говорится, что чувствительность курсоров к параллельному обновлению нижележащих данных по умолчанию определяется реализацией. В PostgreSQL курсоры по умолчанию нечувствительные, а чувствительными их можно сделать с помощью указания FOR UPDATE . Другие СУБД могут работать иначе.
Стандарт SQL допускает курсоры только во встраиваемом SQL и в модулях. PostgreSQL позволяет использовать курсоры интерактивно.
Двоичные курсоры являются расширением PostgreSQL .
См. также
| Пред. | Наверх | След. |
| DEALLOCATE | Начало | DELETE |
Переменные и управляющие конструкции
Переменная представляет именованный объект, который хранит некоторое значение. Для определения переменных применяется выражение DECLARE , после которого указывается название и тип переменной. При этом название локальной переменной должно начинаться с символа @ :
DECLARE @название_переменной тип_данных
Например, определим переменную name, которая будет иметь тип NVARCHAR:
DECLARE @name NVARCHAR(20)
Также можно определить через запятую сразу несколько переменных:
DECLARE @name NVARCHAR(20), @age INT
С помощью выражения SET можно присвоить переменной некоторое значение:
DECLARE @name NVARCHAR(20), @age INT; SET @name='Tom'; SET @age = 18;
Так как @name предоставляет тип NVARCHAR, то есть строку, то этой переменной соответственно и присваивается строка. А переменной @age присваивается число, так как она представляет тип INT.
Выражение PRINT возвращает сообщение клиенту. Например:
PRINT 'Hello World'
И с его помощью мы можем вывести значение переменной:
DECLARE @name NVARCHAR(20), @age INT; SET @name='Tom'; SET @age = 18; PRINT 'Name: ' + @name; PRINT 'Age: ' + CONVERT(CHAR, @age);
При выполнении скрипта внизу SQL Server Management Studio отобразится значение переменных:
Также можно использовать для получения значения команду SELECT :
DECLARE @name NVARCHAR(20), @age INT; SET @name='Tom'; SET @age = 18; SELECT @name, @age;
В чем разница между объявлениями переменных VARIABLE, DEFINE и DECLARE?
1. var[iable] — это способ объявления переменных в SQL*Plus, которые должны иметь какой-либо тип из указанных в справке. Их можно использовать в sql и в pl/sql как для подстановки каких-либо значений так и для сохранения значений, например:
variable value varchar2(10); begin select 'a' into :value from dual; end; / select :value from dual / 2. def[ine] — это способ объявления переменных в SQL*Plus, в которых можно указать текст, который будет подставлен вместо них в те места, где они используются. Так же с помощью этой команды можно получить список всех существующих переменных которые можно использовать для подстановки.
Если объявить переменную заранее и присвоить ей значение, то оно просто будет подставлено в тексте запроса:
define value = dual select * from &value / Этот запрос выдаст такой результат:
old 1: select * from &value new 1: select * from dual D - X
В переменной можно указать почти любой текст:
define value ='23 from dual' select 1&value / old 1: select 1&value new 1: select 123 from dual 123 ---------- 123 Если переменной заранее не присвоить какое-либо текстовое значение, а просто использовать в тексте запроса, то SQL*Plus предложит ввести ее значение:
select * from &another_value / После выполнения этого текста SQL*Plus отобразит на экране просьбу указать значения для переменной:
Enter value for another_value:
Указав которое (в нашем случае dual ) и нажав Enter мы увидим такой результат:
old 1: select * from &another_value new 1: select * from dual D - X
При вызове просто команды
define
Выведется примерно такой список уже существующих переменных:
DEFINE _DATE = "03-AUG-15" (CHAR) DEFINE _CONNECT_IDENTIFIER = "orcl" (CHAR) DEFINE _USER = "SYS" (CHAR) DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR) DEFINE _SQLPLUS_RELEASE = "1102000200" (CHAR) DEFINE _EDITOR = "Notepad" (CHAR) DEFINE _O_VERSION = "Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR) DEFINE _O_RELEASE = "1102000200" (CHAR) DEFINE VALUE = "dual" (CHAR)
3. declare — это часть объявления pl/sql блока кода define . begin . end после которой и до begin идет объявление переменных, которые можно использовать внутри блока begin . end Например:
declare val1 number; val2 varchar2(10); val3 date; begin select 1, 'a' into val1, val2 from dual; val3 := sysdate; dbms_output.put_line(val1); dbms_output.put_line(val2); dbms_output.put_line(val3); end; / Отслеживать
ответ дан 3 авг 2015 в 20:40
3,807 1 1 золотой знак 18 18 серебряных знаков 27 27 бронзовых знаков
Разница объявляемых переменных заключается в месте их применения и, соответственно, в месте хранения значений этих переменных.
- Подстановочные переменные хоста (host or bind variables) и переменные замещения (substitute variable) объявляются и хранятся в клиентской программе.
Обявление переменных в клиенте зависит от клинтской программы и может несколько отличаться или вообще отсутствовать. Объявления def[ine] и var[iable] присходят от SQL*Plus, но поддерживаются также многими другими программными продуктами для работы с БД Oracle, например: SQL Developer, TOAD.- var[iable] — подстановочные переменные с указанием типа данных.
Инициализировать эти переменные возможно только в PL/SQL блоке. Могут использоваться для подстановки как входных так и выходных значений полей в DML запросах. Подстановочные переменные могут так же быть в анонимных PL/SQL блоках как для передачи так и для чтения. Подстановка переменных осуществляется на этапе подстановки (bind) для входных значений, или определения результата (define output) для выходных значений, непосредственно перед выполнением (execute).
Важно: Подстановочные переменные не могут быть использованы для имён полей, таблиц, представлений и других объектов БД, так как они необходимы на этапе подготовки к выполнению (parse). - def[ine] — переменные замещения. Они объявляются и сразу же инициализируются символьным значением. Все встретившиеся имена переменных с префиксом & будут замещены символьным значением этих переменных. Замещение произойдёт в клиенте ешё до отправки DML/DDL/DCL выражения или PL/SQL блока на выполнение серверу БД, поэтому каких либо ограничений, какая их часть может быть замещена, не существует.
- var[iable] — подстановочные переменные с указанием типа данных.
- Переменные языка PL/SQL обьявляются в програмном блоке. Они не зависят от клиентской программы, так как в клиенте это только текст программы, который должен быть отправлен на сервер БД. Инициализируются переменные этого типа при выполнении програмного блока на сервере БД. Хранятся эти переменные в UGA (user global area) так же на сервере БД.
- PL/SQL переменные обьявляются в блоке после ключевого слова declare . В именных блоках переменные могут быть объявлены сразу после заголовка create|alter . is|as объявления/изменения именного объекта. PL/SQL блоки могут содержать вложенные блоки. Зона видимости переменных ограничена блоком, в котором они объявлены. Время жизни переменных объявленых в пакетах (packaged variables) — сессия, во всех остальных случаях — время выполнения именного объекта или анонимного блока.
Пример исполъзующий все виды вышеописаных переменных для динамического выполнения скриптов в SQL*Plus — есть некое клолличество скриптов, но зарение неизвестно какой из них вызывать, т.е. его надо определить динамически и тут же вызвать. Все виды переменных и алиас колонки умышлено используют одно и то же имя sqlfile :
define sqlfile='default'; variable sqlfile varchar2(100); declare sqlfile varchar2(100); begin > declare -- в этом блоке динамически определяем имя скрипта localFile constant varchar2(100) := 'my_sqlscript_01'; begin sqlfile := localFile; end; :sqlfile := sqlfile; end; / column sqlfile new_value sqlfile noprint format A100; select nvl(:sqlfile, '&sqlfile') sqlfile from dual; host echo "prompt # &sqlfile running . " >sql/&sqlfile\.sql @sql/&sqlfile # my_sqlscript_01 running .