Declare table ms sql

Сегодня практически каждый современный программист знает, что такое Transact-SQL. Это расширение, которое используется в SQL Server. Данная разработка тесно интегрирована в язык Microsoft SQL и добавляет конструкторы программирования, которые изначально не предусмотрены в базах данных. T-SQL поддерживает переменные, как и в большинстве других разработках. Однако это расширение ограничивает использование переменных способами, которые не распространены в других средах.

Объявление переменных в DECLARE SQL

Для объявления переменной в T-SQL используется оператор DECLARE ( ). Например, в случае объявления переменной i как целое с использованием данного оператора команда будет выглядеть так: DECLARE @i int.

Хотя Microsoft не документирует эту функцию, T-SQL также поддерживает указание ключевого слова AS между именем переменной и ее типом данных, как в следующем примере: DECLARE @i AS int. Ключевое слово AS упрощает чтение инструкции DECLARE. Единственный тип данных, который не позволяет указать ключевое слово AS, — это тип данных таблицы, который является новым в SQL Server 2000. Он дает возможность определить переменную, содержащую полную таблицу.

DECLARE SQL: описание

T-SQL поддерживает только локальные переменные, которые доступны исключительно в той партии, которая их создала. Пакет — это оператор (или группа операторов), который база данных анализирует как единицу. Каждый клиентский инструмент или интерфейс имеет свой собственный способ указания, где заканчивается пакет. Например, в Query Analyzer вы используете команду GO, чтобы указать, где заканчивается пакет. Если у вас есть синтаксическая ошибка в любом заявлении, пакет не проходит фазу разбора, поэтому клиентский инструмент не отправляет пакет на SQL Server для дальнейшей обработки. Вы можете запустить код, который объявляет переменную таблицы, а затем вставляет строку в таблицу в той же партии.

Пример SQL Declare Table:

DECLARE @mytable table
col1 int NOT NULL
INSERT INTO @mytable VALUES (1)
GO

Теперь объявите переменную таблицы в одной партии, а затем вставьте строку в таблицу в другую партию:

DECLARE @mytable table
col1 int NOT NULL
INSERT INTO @mytable VALUES (1)GO

Оператор INSERT терпит неудачу, потому что переменная таблицы выходит за пределы области видимости, и появляется следующее сообщение об ошибке:
Сервер: Msg 137, уровень 15, состояние 2, строка 2.

Переменные в процедурах (инструкции DECLARE, SET)

Поддержка локальных переменных в процедурах SQL позволяет назначать и извлекать значения данных в поддержку логики процедур. Переменные в процедурах определяются с помощью оператора DECLARE SQL. Значения могут присваиваться переменным с помощью инструкции SET или в качестве значения по умолчанию при объявлении переменной. Литералам, выражениям, результатам запроса и специальным значениям регистра могут быть присвоены переменные.

Алгоритм

При объявлении переменной вы можете указать значение по умолчанию, используя предложение DEFAULT. Строка показывает объявление переменной типа Boolean со значением по умолчанию FALSE. Оператор SET может использоваться для назначения одного значения переменной. Переменные также могут быть установлены путем выполнения инструкции SELECT или FETCH в сочетании с предложением INTO. Оператор VALUES INTO может использоваться для оценки функции или специального регистра и присваивать значение нескольким переменным.

Вы также можете присвоить результат оператора GET DIAGNOSTICS переменной. GET DIAGNOSTICS может использоваться для получения дескриптора количества затронутых строк (обновляется для оператора UPDATE, DELETE — для оператора DELETE) или статуса возврата только что выполненного SQL-оператора

Особенности

Строка DECLARE SQL демонстрирует, как часть логики может использоваться для определения значения, которое должно быть присвоено переменной. В этом случае, если строки были изменены как часть более раннего оператора DELETE, а выполнение GET DIAGNOSTICS привело к тому, что переменной v_rcount присвоено значение, большее нуля, переменной is_done присваивается значение TRUE.

Процедуры

Процедуры DECLARE SQL — это процедуры, полностью реализованные с использованием SQL, которые могут использоваться для инкапсуляции логики. Та же в свою очередь может быть вызвана как подпрограмма программирования.

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

Читайте также:  Стойки для акустической системы своими руками

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

Создание процедур

Внедрение SQL-процедур может играть важную роль в архитектуре базы данных, разработке приложений и производительности системы. Разработка требует четкого понимания требований, возможностей и использования функций, а также знания любых ограничений. Процедуры SQL создаются по инструкции CREATE PROCEDURE. Когда создается алгоритм, запросы в теле процедуры отделяются от процедурной логики. Чтобы максимизировать производительность, SQL-запросы статически компилируются в разделы в пакете

Переменные

Локальная переменная Transact-SQL — это объект, который может содержать одно значение данных определенного типа. Обычно используются переменные в партиях и сценариях:

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

Имена ряда функций Transact-SQL начинаются со знаков (@@). Хотя в более ранних версиях Microsoft SQL Server функции @@ называются глобальными переменными. @@ — это системные функции, и их использование подчиняется правилам синтаксиса для функций.

Объявление переменной

Оператор DECLARE определяет переменную Transact-SQL согласно следующему алгоритму:

  • определение имени, которое должно иметь один символ @ в качестве первого символа;
  • назначение заданного или определенного пользователем типа данных и длины;
  • для числовых переменных также назначаются точность и масштаб.
  • для переменных типа XML может быть назначена дополнительная сборка схемы.
  • Установка значения в NULL. Например, оператор DECLARE в SQL-запросе создает локальную переменную с именем @mycounter с типом данных int.

Чтобы объявить несколько локальных переменных, используйте запятую после определения первой локальной переменной, а затем укажите следующее имя локальной сети и тип данных. Например, следующий оператор создает три локальные переменные с именем @LastName, @FirstName и @StateProvince и инициализирует каждый из NULL. Объем переменной — это диапазон операторов Transact-SQL, которые могут ссылаться на переменную. Объем переменной длится от той точки, которая объявляется до конца партии или хранимой процедуры, в которой она объявлена.

ПРИМЕНЯЕТСЯ К: SQL Server (начиная с 2008) База данных SQL Azure Хранилище данных SQL Azure Parallel Data Warehouse

Переменные объявляются в теле пакета или процедуры при помощи инструкции DECLARE, а значения им присваиваются при помощи инструкций SET или SELECT. При помощи этой инструкции можно объявлять переменные курсоров для использования в других инструкциях. После декларации все переменные инициализируются значением NULL, если иное значение не предоставляется как часть декларации.

Синтаксические обозначения в Transact-SQL

@local_variable
Имя переменной. Имена переменных должны начинаться с символа @. Имена локальных переменных должны соответствовать правилам для идентификаторы.

Тип данных
Любой системный тип данных, определяемый пользователем табличный тип среды CLR или псевдоним типа данных. Переменная не может быть текст, ntext, или изображение тип данных.

Дополнительные сведения о системных типах данных см. в разделе типы данных (Transact-SQL). Дополнительные сведения об определяемых пользователем типов CLR и псевдонимов типов данных см. в разделе CREATE TYPE (Transact-SQL).

=значение
Подставляет значение переменной. Значение может быть константой или выражением, но должно совпадать с объявленным типом переменной или явно преобразовываться в этот тип. Дополнительные сведения см. в разделе Выражения (Transact-SQL).

@cursor_variable_name
Имя переменной курсора. Имена переменных курсора должны начинаться с символа @ и должны соответствовать правилам именования идентификаторов.

CURSOR
Указывает, что переменная является локальной переменной курсора.

@table_variable_name
Имя переменной типа таблицы. Имена переменных должны начинаться с символа @ и соответствовать правилам именования идентификаторов.

Определяет таблицы тип данных. Декларация таблицы включает определения столбцов, имен, типов данных и ограничений. Допустимы только ограничения PRIMARY KEY, UNIQUE, NULL и CHECK. Псевдоним типа данных не может использоваться как скалярный тип данных столбца, если к этому столбцу привязано правило или значение по умолчанию.

Аргумент представляет собой подмножество данных, используемых для определения таблицы в инструкции CREATE TABLE. Сюда включены элементы и наиболее существенные определения. Дополнительные сведения см. в разделе CREATE TABLE (Transact-SQL).

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

column_name
Имя столбца таблицы.

Читайте также:  Дигма или престижио планшеты что лучше

scalar_data_type
Указывает, что столбец имеет скалярный тип данных.

computed_column_expression
Выражение, определяющее значение вычисляемого столбца. Значение вычисляется из выражения при помощи других столбцов той же таблицы. Например, вычисляемый столбец может иметь определение стоимость AS цены * qty. Выражение может быть именем невычисляемого столбца, константой, встроенной функцией, переменной или любым их сочетанием, созданным с помощью одного или нескольких операторов. Выражение не может быть вложенным запросом или определяемой пользователем функцией. Выражение не может ссылаться на определяемый пользователем тип данных CLR.

[COLLATE collation_name]
Задает параметры сортировки для столбца. collation_name может быть либо именем параметров сортировки Windows, либо имя параметров сортировки SQL и применяется только к столбцам char, varchar, текст, nchar, nvarchar, и ntext типов данных. Если этот аргумент не указан, столбцу назначаются либо параметры сортировки определяемого пользователем типа данных (если столбец принадлежит к определяемому пользователем типу данных), либо параметры сортировки текущей базы данных.

Дополнительные сведения об именах параметров сортировки Windows и SQL см. в разделе COLLATE (Transact-SQL) .

DEFAULT
Указывает значение, присваиваемое столбцу в случае отсутствия явно заданного значения при вставке. Определения DEFAULT могут применяться к любым столбцам, кроме тех, которые определены как отметка времени , либо свойство IDENTITY. Определения DEFAULT удаляются, когда таблица удаляется из памяти. По умолчанию могут использоваться только константные значения, например символьные строки, системные функции, например SYSTEM_USER(), или NULL. Для сохранения совместимости с более ранними версиями сервера SQL Server значению DEFAULT может быть присвоено имя ограничения.

constant_expression
Константа, NULL или системная функция, используемые в качестве значения по умолчанию для столбца.

IDENTITY
Указывает, что новый столбец является столбцом идентификаторов. При добавлении в таблицу новой строки SQL Server формирует для столбца уникальное, последовательное значение. Столбцы идентификаторов наиболее часто используются в сочетании с ограничениями PRIMARY KEY для выполнения функции уникального идентификатора строки таблицы. Свойство IDENTITY могут быть назначены tinyint, smallint, int, decimal(p,0), или numeric(p,0) столбцы. Для каждой таблицы можно создать только один столбец идентификаторов. Ограниченные значения по умолчанию и ограничения DEFAULT не могут использоваться в столбце идентификаторов. Необходимо указывать либо оба значения seed и increment, либо ни тот, ни другой. Если ничего не указано, применяется значение по умолчанию (1,1).

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

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

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

NULL | NOT NULL
Указывает, является ли значение null допустимым в переменной. Значение по умолчанию — NULL.

PRIMARY KEY
Ограничение, которое с помощью уникального индекса требует целостности сущностей для данного столбца или столбцов. Можно создать только одно ограничение PRIMARY KEY для таблицы.

UNIQUE
Ограничение, которое с помощью уникального индекса обеспечивает целостность сущностей для данного столбца или столбцов. В таблице может быть несколько ограничений UNIQUE.

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

Logical_Expression
Логическое выражение, возвращающее значения TRUE или FALSE.

Переменные часто используются в пакете или процедуре в качестве счетчиков для циклов WHILE, LOOP или в блоке IF…ELSE.

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

Областью локальной переменной является пакет, в котором она объявлена.

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

На переменную курсора, которая в настоящее время содержит назначенный ей курсор, можно ссылаться в качестве источника из:

позиционированных инструкций DELETE или UPDATE;

инструкции SET CURSOR с использованием переменных (в правой части).

Во всех этих инструкциях SQL Server формирует ошибку, если переменная курсора, на которую они ссылаются, существует, но не содержит курсор, назначенный ей в настоящее время. Если переменная курсора, на которую производится ссылка, не существует, сервер SQL Server формирует ту же ошибку, что и для необъявленной переменной другого типа.

Может быть целью типа курсора или другой переменной курсора. Дополнительные сведения см. в разделе УСТАНОВИТЬ @local_variable (Transact-SQL).

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

Читайте также:  Вставила симку в телефон пишет нет сети

Должна рассматриваться в качестве указателя на курсор.

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

В следующем примере локальная переменная с именем @find используется для получения контактных данных для лиц с фамилией, начинающейся на Man .

Ниже приводится результирующий набор.

LastName FirstName Phone

Manchepalli Ajay 1 (11) 500 555-0174

Manek Parul 1 (11) 500 555-0146

Manzanares Tomas 1 (11) 500 555-0178

(3 row(s) affected)

Б. Использование инструкции DECLARE с двумя переменными

В следующем примере возвращаются имена коммерческих представителей компании Компания Adventure Works Cycles, находящихся в Северной Америке и имеющих объемы продаж на сумму не менее&2; 000 000 долларов в год.

В. Объявление переменной типа table

В следующем примере создается переменная типа table , в которой хранятся значения, задаваемые в предложении OUTPUT инструкции UPDATE. Две следующие инструкции SELECT возвращают значения в табличную переменную @MyTableVar , а результаты операции обновления — в таблицу Employee . Обратите внимание, что результаты в INSERTED.ModifiedDate столбцов отличаются от значений в ModifiedDate столбец Employee таблицы. Это связано с тем, что для таблицы AFTER UPDATE определен триггер ModifiedDate , обновляющий значение Employee до текущей даты. Однако столбцы, возвращенные из OUTPUT , отражают состояние данных перед срабатыванием триггеров. Дополнительные сведения см. в разделе предложение OUTPUT (Transact-SQL).

Г. Объявление переменной определяемого пользователем табличного типа

Следующий пример демонстрирует создание параметра, возвращающего табличное значение, или табличной переменной с именем @LocationTVP . Здесь требуется соответствующий определяемый пользователем табличный тип с именем LocationTableType . Дополнительные сведения о том, как создать тип определяемой пользователем таблицы в разделе CREATE TYPE (Transact-SQL). Дополнительные сведения о возвращающих табличное значение параметров в разделе Use Table-Valued параметры (компонент Database Engine).

Д. Использование инструкции DECLARE

В следующем примере локальная переменная с именем @find используется для получения контактных данных для лиц с фамилией, начинающейся на Walt .

Е. Использование инструкции DECLARE с двумя переменными

В следующем примере извлекается использует переменные, чтобы указать имена и фамилии сотрудников из DimEmployee таблицы.

Долгое время в SQL сервере в качестве хранения каких-либо промежуточных данных использовались временные таблицы.
Создавались они примерно следующим образом:
create table #t1(tid int,tname nvarchar(80));

Но, начиная с 2005 версии ситуация несколько изменилась.

Появились, так называемые табличные переменные
В чем разница?
Во-первых в объявлении. Для объявления табличной переменной необходимо объявить ее:
declare @t1 table (tid int, tname nvarchar(80))

в дальнейшем использование этой переменной ничем не отличается от той же временной таблицы:
declare @t1 table (tid int, tname nvarchar(80))

insert into @t1 values (10,’Акулина’);
insert into @t1 values (20,’Бронислав’);
insert into @t1 values (30,’Богдан’);
insert into @t1 values (40,’Борислав’);

select tid, tname from @t1;

Преимущества:
• Автоматически очищаются в конце функции, хранимой процедуры или пакета, где они были определены
• При использовании в хранимых процедурах табличных переменных приходится прибегать к рекомпиляциям реже, чем при использовании временных таблиц
• Транзакции с использованием табличных переменных продолжаются только во время процесса обновления соответствующих табличных переменных. Поэтому табличные переменные реже подвергаются блокировке и требуют меньших ресурсов для ведения журналов регистрации
• Табличной переменной можно присвоить результат выполнения табличной функции, для повторного использования результатов
• Табличную переменную можно передавать как параметр в хранимую процедуру (SQL Server 2008)

Недостатки:
• На табличных переменных нельзя создавать некластерные индексы
• Табличные переменные не содержат статистику
• Табличные переменные не могут использоваться в INSERT EXEC или SELECT INTO
• Запросы, изменяющие табличные переменные, не создают параллельных планов выполнения запроса

В 2008 версии пошли немного дальше… и теперь, для того, что бы использовать какие-нибудь заранее предопределенные данные, уже не нужно создавать ни временную таблицу, ни табличную переменную…
Достаточно обойтись одним оператором value:

select * from (
values (10,’Акулина’)
, (20,’Бронислав’)
, (30,’Богдан’)
, (40,’Борислав’)
) t (tid, tname)

или например так:

select max(case when n=1 then > from
(
select sourceID id , ROW_NUMBER () over (order by sourceID) r
from LoyaltyDiff_BrokerMessagesReceived (nolock)
)t
cross join (values (0) ,(1))nn(n)

Вывод: Используйте новые возможности!

Данная статья не подлежит комментированию, поскольку её автор ещё не является полноправным участником сообщества. Вы сможете связаться с автором только после того, как он получит приглашение от кого-либо из участников сообщества. До этого момента его username будет скрыт псевдонимом.