≡× МЕНЮ

• Программы
• Windows
• Устройства
• Браузеры
• Обзоры 

Переменные в запросах. Табличные переменные в Microsoft SQL Server Sql более 1 переменной

Переменные T-SQL создаются с помощью команды DECLARE, имеющей следующий синтаксис:

DECLARE@Имя_Переменной Тип_Данных [,

@Имя_Переменной Тип_Данных, …]

Все имена локальных переменных должны начинаться символом @. Например, для объявления локальной переменной UStr, которая хранит до 16 символовUnicode, можно использовать следующую инструкцию:

DECLARE @UStr varchar(16)

Используемые для переменных типы данных в точности совпадают с существующими в таблицах. В одной команде DECLAREчерез запятую может быть перечислено несколько переменных. В частности в следующем примере создаются две целочисленные переменныеaиb:

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

Задание значений переменных

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

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

SET @b = @a * 1.5

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

SET @c = COUNT(*) FROM City

а следующий оператор выполняется вполне успешно:

SET @c = (SELECT COUNT(*) FROM City)

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

SELECT @c = COUNT(*) FROM City

Обратите внимание на то, что данный код немного понятнее (в частности, он более лаконичен, хотя и выполняет те же действия).

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

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

Оператор SELECT применяется, если присваивание значения переменной должно быть основано на запросе.

Использование переменных в запросах SQL

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

Везде, где в запросе может использоваться выражение, может использоваться и переменная. В следующем примере продемонстрировано использование переменной в предложении WHERE:

DECLARE @IdProd int;

SET @IdProd = 1;

SELECT

WHERE IdProd = @IdProd;

Глобальные системные переменные

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

SELECT @@CONNECTIONS

Здесь используется глобальная переменная @@CONNECTIONSдля извлечения количества подключений кSQLServerсо времени запуска программы.

Среди наиболее часто применяемых системных переменных можно отметить следующие:

@@ERROR- Содержит номер ошибки, возникшей при выполнении последнего оператораT-SQLв текущем соединении. Если ошибка не обнаружена, содержит 0. Значение этой системной переменной переустанавливается после выполнения каждого очередного оператора. Если требуется сохранить содержащееся в ней значение, то это значение следует переносить в локальную переменную сразу же после выполнения оператора, для которого должен быть сохранен код ошибки.

@@IDENTITY- Содержит последнее идентификационное значение, вставленное в базу данных в результате выполнения последнего оператораINSERT. Если в последнем оператореINSERTне произошла выработка идентификационного значения, системная переменная @@IDENTITYсодержитNULL. Это утверждение остается справедливым, даже если отсутствие идентификационного значения было вызвано аварийным завершением при выполнении оператора. А если с помощью одного оператора осуществляется несколько операций вставки, этой системной переменной присваивается только последнее идентификационное значение.

@@ROWCOUNT- Одна из наиболее широко используемых системных переменных. Возвращает информацию о количестве строк, затронутых последним оператором. Обычно применяется для контроля ошибок, отличных от тех, которые относятся к категории ошибок этапа прогона программы. Например, если в программе обнаруживается, что после вызова на выполнение оператораDELETEс конструкциейWHEREколичество затронутых строк равно нулю, то можно сделать вывод, что произошло нечто непредвиденное. После этого сообщение об ошибке может быть активизировано вручную.

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

Цель работы – знакомство с основными приципами программирования в MS SQL Server средствами встроенного языка Transact SQL.

1. Знакомство с правилами обозначения синтаксиса команд в справочной системе MS SQL Server (утилита Books Online).

2. Изучение правил написания программ на Transact SQL.

3. Изучение правил построения идентификаторов, правил объявления переменных и их типов.

4. Изучение работы с циклами и ветвлениями.

5. Изучение работы с переменными типа Table и Cursor.

6. Проработка всех примеров, анализ результатов их выполнения.

7. Выполнение индивидуальных заданий по вариантам.

Пояснения к выполнению работы

Для освоения программирования используем пример базы данных c названием DB_Books, которая была создана в лабораторной работе №1.

При выполнении примеров и заданий обращайте внимание на соответствие названий БД, таблиц и других объектов проекта.

1. Специальные знаки и простейшие операторы в Transact SQL

Знак	Назначение	Знак	Назначение
*	Знак умножения	» »	В них заключают строковые значения, если SET QUOTED_IDENTIFIER OFF
-	Знак вычитания	‘ ’	В них заключают строковые значения*
%	Остаток от деления двух чисел	<>	Не равно
+	Знак сложения или конкатенации (объединение двух строк в одну)		Аналог кавычек, в них можнозаключать названия идентификаторов, если в их названиях встречаются пробелы
=	Знак равенства или сравнения	!<	Не менее чем
⇐	Меньше или равно	!>	Не более чем
>=	Больше или равно	>	Больше
!=	Не равно	<	Меньше
@	Ставится перед именем переменной	.	Разделяет родительские и подчиненные объекты
@@	Указывает на системные функции	/	Знак деления
–	Однострочный комментарий или комментарий с текущей позиции и до конца строки	/* */	Многострочный комментарий

2. Идентификаторы

Идентификаторы – это имена объектов, на которые можно ссылаться в программе, написанной на языке Transact SQL. Первый символ может состоять из букв английского алфавита или «_», »@», »#». В качестве остальных символов идентификатора могут быть дополнительно использованы цифры и символ «$». Имя идентификатора не должно совпадать с зарезервированным словом (среда SQL Server Management Studio подсвечивает зарезервированные слова синим цветом).

Иногда в качестве идентификатора удобно использовать словосочетание. Можно воспользоваться заменой пробела на нижнее подчеркивание, а можно использовать ограничители идентификаторов - квадратные скобки или одинарные кавычки. Здесь надо обратить внимание на параметр QUOTED_IDENTIFIER.

Для ограничителей идентификаторов при установленном параметре

SET QUOTED_IDENTIFIER ON

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

Если использовать установленный параметр в режиме

SET QUOTED_IDENTIFIER OFF

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

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

3. Объявление переменных

Синтаксис

DECLARE @имя_переменной1 тип_переменной, …, @имя_переменнойN тип_переменной

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

DECLARE @имя_переменной1 тип_переменной(размер) , …, @имя_переменнойN тип_переменной(размер)

Пример:

DECLARE @a INT , @b NUMERIC (10 , 2 ) DECLARE @str CHAR (20 )

4. Присвоение значений переменным и вывод значений на экран

Присвоение с помощью SET – обычное присвоение, синтаксис:

SET @имя_переменной = значение

Пример:

DECLARE @a INT , @b NUMERIC (10 , 2 ) SET @a = 20 SET @b = (@a+ @a) / 15 SELECT @b -- вывод на экран результата

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

Пример:

DECLARE @a INT SELECT @a = COUNT (* ) FROM Authors

Пример:

DECLARE @str CHAR (30 ) SELECT @str = name_author FROM Authors

В данном примере в переменную поместится последнее значение из результата запроса.

Пример:

DECLARE @a INT SET @a = (SELECT COUNT (* ) FROM Authors)

5. Работа с датой и временем

Оператор SET DATEFORMAT dmy | ymd | mdy задает порядок следования компонентов даты.

Пример:

SET DATEFORMAT dmy -- задает порядок следования компонентов даты. DECLARE @d DateTime -- объявляем переменную типа DateTime SET @d = "30.10.1821 0:00:00" SET @d = @d+ 1 -- прибавляем один день SELECT @d -- выводим значение переменной d в таблице

6. Создание временной таблицы через переменную типа TABLE

Объявляется через DECLARE с указанием в скобках столбцов таблицы, их типов, размеров, значений по умолчанию, а также индексов типа PRIMARY KEY или UNIQUE.

Пример:

/*Объявляем таблицу*/ DECLARE @mytable TABLE ( -- Объявление переменной типа TABLE id INT , -- Тип целых значений myname CHAR (20 ) DEFAULT "Введите имя" -- Тип значений и в ячейке выводится сообщение ) INSERT INTO @mytable(id) VALUES (1 ) -- Добавляем строку SELECT * FROM @mytable -- Выводим значение

Пример:

/*Объявляем таблицу*/ DECLARE @mytable TABLE ( id INT , myname CHAR (20 ) DEFAULT "Введите имя" ) INSERT @mytable SELECT Code_publish, City FROM Publishing_house SELECT * FROM @mytable

7. Преобразование типов переменных

Функция CAST возвращает значение, преобразованное к указанному типу:

CAST (@переменная или значение AS требуемый_тип_данных)

Пример:

DECLARE @d DateTime, @str CHAR (20 ) -- объявляем переменные типа DateTime и char SET @d = "31.01.2005 13:23:15" -- устанавливаем значение переменной d SET @str = CAST (@d AS CHAR (20 ) ) -- устанавливаем значение str, приводим к формату char SELECT @str -- выводит значение переменной

Функция CONVERT возвращает значение, преобразованное к указанному типу по заданному формату. Изучить дополнительно, по желанию.

8. Операторские скобки

BEGIN /* В них нельзя помещать команды, изменяющие структуры объектов БД. Операторские скобки должны содержать хотя бы один оператор. Требуются для конструкций поливариантных ветвлений, условных и циклических конструкций */ END

9. Условная конструкция IF

Синтаксис:

IF условие Набор операторов1 ELSE Набор операторов2

Пример:

DECLARE @a INT DECLARE @str CHAR (40 ) -- объявляет переменную типа CHAR SET @a = (SELECT COUNT (name_author) FROM Authors) -- устанавливает значение a, равное количеству -- записей в столбце name_author IF @a > 10 BEGIN SET @str = "Количество авторов больше 10" -- если а>10, то выводится сообщение "Количество авторов больше 10" SELECT @str END ELSE BEGIN SET @str = " Количество авторов = " + str(@a) -- иначе количество авторов равно значению переменной а SELECT @str -- вывод значения переменной str END

10. Цикл WHILE

Синтаксис:

WHILE Условие Набор операторов1 BREAK Набор опреторов2 CONTINUE

Конструкции BREAK и CONTINUE являются необязательными. Цикл можно принудительно остановить, если в его теле выполнить команду BREAK. Если же нужно начать цикл заново, не дожидаясь выполнения всех команд в теле, необходимо выполнить команду CONTINUE.

Пример:

DECLARE @a INT -- объявляет переменную типа INT SET @a = 1 -- устанавливает значение а =1 WHILE @a < 100 -- цикл работает пока значение переменной а<100 BEGIN PRINT @a IF (@a> 40 ) AND (@a< 50 ) -- если значение переменной а>40 и a <50 BREAK -- выход и выполнение 1-й команды за циклом ELSE -- иначе SET @a = @a+ rand() * 10 -- устанавливаем значение а равное переменной а + случайное число из диапазона (0, 1), -- умноженное на 10 CONTINUE -- запускаем цикл заново, не дожидаясь выполнения всех команд в теле END PRINT @a -- вывод на экран значения переменной

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

CURSOR – это набор строк, являющийся результатом выполнения запроса. В один момент времени доступна лишь одна строка (текущая), по курсору можно передвигаться и получать доступ к элементарным данным. При объявлении курсора создается временная копия данных, которая сохраняется в БД tempdb.

Динамический курсор – данные в курсоре могут быть изменены.

Статический курсор – данные в курсоре не меняются.

Стандартный способ объявления курсора, синтаксис в обозначениях MS SQL Server:

DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR FOR select_statement [ FOR { READ ONLY | UPDATE [ OF column_name [ ,... n ] ] } ]

Примеры объявления курсоров

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

DECLARE MyCursor1 CURSOR FOR (SELECT * FROM Authors)

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

DECLARE MyCursor1 INSENSITIVE CURSOR FOR (SELECT * FROM Authors)

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

DECLARE MyCursor1 SCROLL CURSOR FOR (SELECT * FROM Authors)

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

DECLARE MyCursor1 INSENSITIVE SCROLL CURSOR FOR (SELECT * FROM Authors)

5. Объявляем курсор с названием MyCursor1, который содержит всю информацию об авторах, двигаться по нему можно только от первой записи вниз до последней. Курсор является динамическим. Данные доступны только для чтения.

DECLARE MyCursor1 CURSOR FOR (SELECT * FROM Authors) FOR READ ONLY

6. Объявляем курсор с названием MyCursor1, который содержит всю информацию об авторах, двигаться по нему можно только от первой записи вниз до последней. Курсор является динамическим. Данные курсора можно менять.

DECLARE MyCursor1 CURSOR FOR (SELECT * FROM Authors) FOR UPDATE

12. Операторы для работы с курсором

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

Синтаксис оператора OPEN в обозначениях MS SQL Server:

OPEN { { [ GLOBAL ] cursor_name} | cursor_variable_name }

Пример:

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

Синтаксис оператора CLOSE в обозначенияхMS SQL Server:

CLOSE { { [ GLOBAL ] cursor_name} | cursor_variable_name}

Пример:

DECLARE MyCursor1 CURSOR FOR (SELECT * FROM Authors) OPEN MyCursor1 CLOSE MyCursor1

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

Синтаксис оператора DEALLOCATE в обозначениях MS SQL Server:

DEALLOCATE { { [ GLOBAL ] cursor_name} | @cursor_variable_name }

Пример:

DECLARE MyCursor1 CURSOR FOR (SELECT * FROM Authors) OPEN MyCursor1 --здесь операторы работы с курсором CLOSE MyCursor1 DEALLOCATE MyCursor1

FETCH – оператор движения по записям курсора и извлечения данных текущей записи в указанные переменные. Синтаксис оператора FETCH в обозначениях MS SQL Server:

FETCH [ [ NEXT | PRIOR | FIRST | LAST | ABSOLUTE { n | @nvar } | RELATIVE { n | @nvar } ] FROM ] { { [ GLOBAL ] cursor_name} | @cursor_variable_name } [ INTO @variable_name [ 1 ,..., n ] ]

Пример:

DECLARE MyCursor1 SCROLL CURSOR FOR (SELECT * FROM Authors) DECLARE @i BIGINT , @s CHAR (20 ) , @d smalldatetime OPEN MyCursor1 FETCH FIRST FROM MyCursor1 INTO @i, @s, @d PRINT @i PRINT @s PRINT @d CLOSE MyCursor1 DEALLOCATE MyCursor1

@@FETCH_STATUS – данная функция определяет признак конца или начала текущего курсора. Функция принимает одно из следующих значений:

1 – попытка выйти за пределы первой записи вверх (в никуда);

2 – попытка выйти за пределы последней записи вниз(в никуда).

Пример:

DECLARE MyCursor1 SCROLL CURSOR FOR (SELECT * FROM Authors) DECLARE @i BIGINT , @s CHAR (20 ) , @d smalldatetime -- объявляем переменные OPEN MyCursor1 -- открываем MyCursor1 FETCH FIRST FROM MyCursor1 INTO @i, @s, @d -- устанавливаем курсор на первую строку таблицы Authors WHILE @@FETCH_STATUS = 0 -- пока находимся в пределах таблицы BEGIN FETCH NEXT FROM MyCursor1 INTO @i, @s, @d -- записываем данные из текущей строки в переменные PRINT @i -- выводим code_author в переменную i PRINT @s -- выводим name_author в переменную s PRINT @d -- выводим birthday в переменную d END CLOSE MyCursor1 -- закрываем курсор DEALLOCATE MyCursor1 -- освобождаем память

13. Пример создания и запуска процедуры с курсором

Пример создания процедуры USE DB_book; GO IF OBJECT_ID ("Book" , "P" ) IS NOT NULL -- если уже существует объект (процедура) с именем "Book", DROP PROCEDURE Book; -- удаляем этот объект GO CREATE PROCEDURE Book -- создаем процедуру Book, возвращающую значение переменной cur1 @cur1 CURSOR VARYING OUTPUT -- параметры курсора в хранимой процедуре обязательно должны быть -- CURSOR VARYING OUTPUT AS -- далее - тело процедуры SET @cur1 = CURSOR FOR -- объявляем переменную типа курсор SELECT name_author FROM Authors; -- выводим в курсор значения записи name_author OPEN @cur1; -- открываем переменную GO --пример запуска процедуры с курсором USE DB_book; GO DECLARE @MyCursor CURSOR; -- объявляем курсор DECLARE @a CHAR (30 ) ; -- объявляем переменную @a EXEC book @cur1 = @MyCursor OUTPUT; -- запускаем процедуру и выводим возвращаемые значения -- в объявленный курсор FETCH NEXT FROM @MyCursor INTO @a; -- устанавливаем курсор на первую запись таблицы и выводим -- значение в переменную @a WHILE (@@FETCH_STATUS = 0 ) -- пробегаем по всем строкам таблицы BEGIN PRINT @a; -- выводим на экран значение переменной @a FETCH NEXT FROM @MyCursor INTO @a; -- устанавливаем курсор на следующую запись таблицы и выводим -- значение в переменную @a END ; CLOSE @MyCursor; -- закрываем курсор DEALLOCATE @MyCursor; -- освобождаем память GO

14. Встроенные функции

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

математические функции;

строковые функции;

функции для работы с датой и временем;

функции конфигурирования;

функции системы безопасности;

функции управления метаданными;

статистические функции.

Краткий обзор строковых функций

Название функций
ASCII	Возвращает код ASCII левого символа строки
CHAR	По коду ASCII возвращает символ
CHARINDEX	Определяет порядковый номер символа, с которого начинается вхождение подстроки в строку
DIFFERENCE	Возвращает показатель совпадения строк
LEFT	Возвращает указанное число символов с начала строки
LEN	Возвращает длину строки
LOWER
LTRIM	Удаляет пробелы в начале строки
NCHAR	Возвращает по коду символ Unicode
PATINDEX	Выполняет поиск подстроки в строке по указанному шаблону
REPLACE	Заменяет вхождения подстроки на указанное значение
QUOTENAME	Конвертирует строку в формат Unicode
REPLICATE	Выполняет тиражирование строки определенное число раз
REVERSE	Возвращает строку, символы которой записаны в обратном порядке
RIGHT	Возвращает указанное число символов с конца строки
RTRIM	Удаляет пробелы в конце строки
SOUNDEX	Возвращает код звучания строки
SPACE	Возвращает указанное число пробелов
STR	Выполняет конвертирование значения числового типа в символьный формат
STUFF	Удаляет указанное число символов, заменяя новой подстрокой
SUBSTRING	Возвращает для строки подстроку указанной длины с заданного символа
UNICODE	Возвращает Unicode-код левого символа строки
UPPER	Переводит все символы строки в верхний регистр

Использование функций для работы с числами

Краткий обзор математических функций

Название функций	Действие, выполняемое функцией
ABS	Вычисляет абсолютное значение числа
ACOS	Вычисляет арккосинус
ASIN	Вычисляет арксинус
ATAN	Вычисляет арктангенс
ATN2	Вычисляет арктангенс с учетом квадратов
CEILING	Выполняет округление вверх
COS	Вычисляет косинус угла
COT	Переводит все символы строки в нижний регистр
DEGREES	Преобразует значение угла из радиан в градусы
EXP	Возвращает экспоненту
FLOOR	Выполняет округление вниз
LOG	Вычисляет натуральный логарифм
LOG10	Вычисляет десятичный логарифм
PI	Возвращает значение «пи»
POWER	Возводит число в степень
RADIANS	Преобразует значение угла из градуса в радианы
RAND	Возвращает случайное число
ROUND	Выполняет округление с заданной точностью
SIGN	Определяет знак числа
SIN	Вычисляет синус угла
SQUARE	Выполняет возведение числа в квадрат
SQRT	Извлекает квадратный корень
TAN	Возвращает тангенс угла

Краткий обзор основных функций для работы с датой и временем

DATEADD	Добавляет к дате указанное значение дней, месяцев, часов и т.д.
DATEDIFF	Возвращает разницу между указанными частями двух дат
DATENAME	Выделяет из даты указанную часть и возвращает ее в символьном формате
DATEPART	Выделяет из даты указанную часть и возвращает ее в числовом формате
DAY	Возвращает число из указанной даты
GETDATE	Возвращает текущее системное время
ISDATE	Проверяет правильность выражения на соответствие одному из возможных форматов ввода даты
MONTH	Возвращает значение месяца из указанной даты
YEAR	Возвращает значение года из указанной даты
MINUTE	Возвращает значение минут из указанной даты/времени
HOUR	Возвращает значение часов из указанной даты/времени
SECOND	Возвращает значение секунд из указанной даты/времени

Варианты заданий к лабораторной работе №4

Общие сведения

Для получения более подробной информации о работе тех или иных операторов или функций можно запустить утилиту Books Online из состава MS SQL Server и в разделе «Указатель» набрать искомый ключевой элемент.

Специальные знаки и простейшие операторы в Transact SQL

Пример. Проверить работу описанной установки SET QUOTED_IDENTIFIER

Напишем следующий запрос (объявим строковую переменную и значение её заключим в двойные кавычки)

DECLARE @A CHAR (30 ) SET @A = "ПРИВЕТ"

Если предварительно было установлено SET QUOTED_IDENTIFIER ON , то запрос завершится ошибкой. Чтобы запрос сработал нужно выставить SET QUOTED_IDENTIFIER OFF:

SET QUOTED_IDENTIFIER OFF DECLARE @A CHAR (30 ) SET @A = "ПРИВЕТ"

Задание .

1. Проверить работу описанной установки SET DATEFORMAT.

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

Пример. Объявить переменную Perem1 типа денежный, а переменную Perem2 типа число с целой частью равной 8 и дробной частью равной 2.

DECLARE @a MONEY, @b NUMERIC (10 , 2 ) DECLARE @str CHAR (20 )

Money - тип данных, представляющие денежные (валютные) значения. Диапазон От -922 337 203 685 477,5808 до 922 337 203 685 477,5807 (8 байт). Numeric(n,m) - тип данных с фиксированной запятой (n - общее число десятичных разрядов, m - количество знаков после запятой).

Задания.

2. Объявить переменную Perem1 типа строка длиной 100, а переменную Perem2 типа длинное целое.

3. Объявить переменную Perem1 типа динамическая строка с максимальной длиной 1000, а переменную Perem2 типа целое число.

4. Объявить переменную Perem1 типа строка длиной 30, а переменную Perem2 типа число с целой частью равной 10 и дробной частью равной 3.

5. Объявить переменную Perem1 типа дата/ время, а переменную Perem2 типа число в диапазоне от 0 до 255.

Присвоение значений переменным и вывод значений на экран

Сочетание ключевых слов SET и SELECT

Работа с датой и временем

14. Определить переменную Date1 типа дата/время. Присвоить ей значение даты 31.12.2006 в формате dd.mm.yyyy.

15. Определить переменную Date1 типа дата/время. Присвоить ей значение даты 31.12.2006 в формате mm.dd.yyyy.

16. Определить переменную Date1 типа дата/время. Присвоить ей значение даты 31.12.2006 в формате yyyy.mm.dd. Создание временной таблицы через переменную типа TABLE

Использование локальных переменных

Пример. Создать локальную таблицу с названием TEMP и полями типа дата/время, длинное целое, строка. Добавить в нее две записи с данными и вывести результат на экран.

DECLARE @temp TABLE ( id INT , DATE DATETIME, num BIGINT DEFAULT 0 , myname CHAR (50 ) DEFAULT "Введите имя" ) INSERT INTO @temp(id, DATE ) VALUES (2 , "25.11.2001" ) INSERT INTO @temp(id, DATE , myname) VALUES (3 , "26.11.2001" , "Кирилл" ) SELECT * FROM @temp

Задания.

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

18. Создать локальную таблицу с названием TEMP и полями типа целое, динамическая строка, бит со значением по умолчанию «1». Добавить в нее две записи с данными и вывести результат на экран.

19. Создать локальную таблицу с названием TEMP и полями типа дата/время, длинное целое, строка. Добавить в нее две записи с данными и вывести результат на экран.

20. Создать локальную таблицу с названием TEMP и полями типа дата/время, длинное целое с автонаращиванием, динамическая строка. Добавить в нее две записи с данными и вывести результат на экран.

Преобразование типов переменных

Пример. Объявить переменные типа BIT, NVARCHAR, DATETIME. Присвоить значения, соответствующие типам. Выполнить преобразование переменных типа BIT, NVARCHAR, DATETIME в FLOAT, INT, BIGINT соответственно и вывести результат на экран.

DECLARE @d BIT, @str NVARCHAR(30 ) , @dat DATETIME SET @d = "True" SET @dat = "31.01.2005 13:23:15" SET @str = "1111111111" SELECT CAST (@d AS FLOAT ) AS float1 SELECT CAST (@dat AS BIGINT ) AS bigint1 SELECT CAST (@str AS INT ) AS int1

Задания.

21. Объявить переменные типа FLOAT, CHAR, TINYINT. Присвоить значения, соответствующие типам. Выполнить преобразование переменных типа FLOAT, CHAR, TINYINT в INT, DATETIME, BIT соответственно и вывести результат на экран.

22. Объявить переменные типа INT, DATETIME, BIT. Присвоить значения, соответствующие типам. Выполнить преобразование переменных типа INT, DATETIME, BIT в FLOAT, CHAR, TINYINT соответственно и вывести результат на экран.

23. Объявить переменные типа NUMERIC, VARCHAR, DATETIME. Присвоить значения, соответствующие типам. Выполнить преобразование переменных типа NUMERIC, VARCHAR, DATETIME в FLOAT, CHAR, BIGINT соответственно и вывести результат на экран.

Условная конструкция IF

Задания.

24. Подсчитать количество поставщиков в таблице Deliveries. Если их в таблице от 2 до 5, то ничего не сообщать, в противном случае вывести сообщение вида «В таблице … поставщиков» (вместо многоточия поставить точное количество поставщиков).

25. Подсчитать сумму закупок книг в таблице покупок. Если полученная сумма в диапозоне от 1000 до 5000, то ничего не сообщать, в противном случае вывести сообщение вида «Сумма закупок = …» (вместо многоточия поставить точную сумму).

26. Подсчитать среднюю стоимость закупки книг в таблице покупок. Если полученная стоимость в диапозоне от 1000 до 5000, то ничего не сообщать, в противном случае вывести сообщение вида «Средняя стоимость закупки = …» (вместо многоточия поставить точную среднюю стоимость).

27. Определить минимальную стоимость закупки книг в таблице покупок. Если полученная стоимость в диапозоне от 200 до 300, то ничего не сообщать, в противном случае вывести сообщение вида «Минимальная стоимость закупки = …» (вместо многоточия поставить точную стоимость).

Цикл WHILE

Пример. Определить количество записей в таблице Authors. Пока записей меньше 100, делать в цикле добавление записи в таблицу с автоматическим наращиванием значения ключевого поля, а вместо имени автора ставить значение „Автор не известен“.

USE DB_book DECLARE @a INT DECLARE @b INT SET @a= (SELECT COUNT (Authors. Code_author) FROM authors) SET @b= @a+ 1 WHILE @b < 100 BEGIN INSERT INTO Authors(code_author, name_author) VALUES (@b, "Автор неизвестен" ) SET @b= @b+ 1 -- цикл работает пока значение переменной b<100 END SELECT * FROM Authors

Задания.

28. Определить количество записей в таблице издательств. Пока записей меньше 20, делать в цикле добавление записи в таблицу с автоматическим наращиванием значения ключевого поля, а вместо названия издательства ставить значение „не известно“.

29. Определить количество записей в таблице поставщиков. Пока записей меньше 17, делать в цикле добавление записи в таблицу с автоматическим наращиванием значения ключевого поля, а вместо названия поставщика ставить значение „не известен“.

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

Пример. Создать статический курсор по данным таблицы Books с полями Code_book, Title_book.

DECLARE MyCursor1 INSENSITIVE SCROLL CURSOR FOR (SELECT Code_book, Title_book FROM Books) OPEN MyCursor1 FETCH FIRST FROM MyCursor1 CLOSE MyCursor1 DEALLOCATE MyCursor1

Задания.

30. Создать динамический курсор по данным таблицы поставщиков (таблица Deliveries) с полями Name_delivery, Name_company.

31. Создать статический курсор по данным таблицы Books и Authors с полями Code_book, Title_book, Name_author.

32. Создать статический курсор по данным таблицы Books и Publishing_house с полями Code_book, Title_book, Publish.

Операторы для работы с курсором

Пример. Создать динамический курсор для чтения по данным таблицы Deliveries с полями Code_delivery, Name_delivery. Вывести данные 3-й записи.

DECLARE MyCursor1 SCROLL CURSOR FOR (SELECT Code_delivery, Name_delivery FROM Deliveries) DECLARE @i BIGINT , @s CHAR (30 ) OPEN MyCursor1 FETCH FIRST FROM MyCursor1 INTO @i, @s FETCH NEXT FROM MyCursor1 INTO @i, @s FETCH NEXT FROM MyCursor1 INTO @i, @s PRINT @i PRINT @s CLOSE MyCursor1 DEALLOCATE MyCursor1

Задания.

33. Сделать текущей БД db_books. Поместить в курсор данные таблицы Purchases. Перебрать все записи таблицы Purchases. Просуммировать значения произведений полей Cost и Amount и результат сохранить в переменной Sum_table, которую после суммирования вывести на экран. Закрыть и удалить из памяти курсор.

34. Объявить статический курсор по данным таблиц Authors и Books. Вывести данные 5-й записи.

Использование функций для работы со строковыми переменными

Базовый текст дан в отдельном файле по вариантам. Для выполнения этого блока заданий в начале программы, которую вы создаете, объявите переменную типа varchar и присвойте ей в качестве значения строку с любым базовым текстом, который будет анализироваться и/или справляться в заданиях.

Пример. Удалить в тексте лишние пробелы. Лишними считаются те, которые идут непосредственно за пробелом. Подсчитать количество исправлений.

DECLARE @text VARCHAR (100 ) -- Объявляем переменную типа динамической строки длинною до 100 символов DECLARE @i INT -- Счетчик цикла DECLARE @c CHAR (1 ) -- Отдельный символ строки DECLARE @ctemp CHAR (1 ) -- Предыдущий по отношению к @c символ строки DECLARE @a INT -- Количество лишних пробелов SET @a= 0 ; SET @text = " съешь еще этих мягких французских булок с изюмом " SET @ctemp = SUBSTRING (@text, 1 , 1 ) -- Полагаем @ctemp равным первому символу строки SET @i = 2 While @i < len (@text) + 1 BEGIN SET @c = SUBSTRING (@text, @i, 1 ) -- Если два соседние символа - пробелы, удаляем один из них IF ((@ctemp= " " ) AND (@c= " " ) ) BEGIN SET @text = (SELECT stuff (@text, @i- 1 , 1 , "" ) ) SET @a= @a+ 1 END SET @ctemp = @c SET @i= @i+ 1 END SELECT @text SELECT @a

37. По правилам оформления машинописных текстов перед знаками.,!?:; пробелы не ставятся, но обязательно ставятся после этих знаков. Удалите лишние пробелы. Подсчитать количество исправлений.

38. По правилам оформления машинописных текстов перед знаками.,!?:; пробелы не ставятся, но обязательно ставятся после этих знаков. Расставьте недостающие пробелы. Подсчитать количество исправлений.

39. Найти из исходного текста второе предложение и вернуть его в переменную Perem, а также вывести на экран весь исходный текст и найденное предложение.

40. Удалить из базового текста 2, 4, 6, 8 слова.

41. Удалить из базового текста 3, 5, 7, 10 слова.

42. Вставить в базовый текст вместо букв «а» - «АА».

43. Вставить в базовый текст вместо букв «е» и «о» - «ББ». 44. Поменять местами первое и последнее слова в базовом тексте.

Использование функций для работы с типом дата/время

Пример. Вывести на экран название текущего месяца и текущее время. Записать в таблицу Purchases в поле Date_order одинаковую дату поступления, которая равна 12.03.2000.

SELECT DATENAME(MONTH , GETDATE() ) + ", " + CAST (DATEPART(HOUR , GETDATE() ) AS CHAR (4 ) ) + ":" + CAST (DATEPART(MINUTE , GETDATE() ) AS CHAR (4 ) ) + ":" + CAST (DATEPART(SECOND , GETDATE() ) AS CHAR (4 ) ) UPDATE PURCHASES SET DATE_ORDER = "12.03.2000"

Задания.

45. Разобрать на отдельные составляющие текущую дату и время и вывести значения на экран в следующем порядке (вместо многоточий): «Сегодня: День = …, Месяц = …, Год = …, Часов = …, Минут = …, Секунд= …»

46. В исходный текст, сохраненный в переменной Perem, после слова » время » вставить текущее время. Результат сохранить в той же переменной Perem и вывести на экран.

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

Описание табличных переменных MS SQL Server

Табличные переменные – это переменные с особым типом данных TABLE, которые используются для временного хранения результирующего набора данных в виде строк таблицы. Появились они еще в 2005 версии SQL сервера. Использовать такие переменные можно и в хранимых процедурах, и в функциях, и в триггерах, и в обычных SQL пакетах. Создаются табличные переменные так же, как и обычные переменные, путем их объявления инструкцией DECLARE.

Переменные такого типа предназначены в качестве альтернативы временным таблицам. Если говорить о том, что лучше использовать табличные переменные или временные таблицы, то однозначного ответа нет, у табличных переменных есть и плюсы, и минусы. Например, лично мне нравиться использовать табличные переменные, потому что их удобно создавать (т.е. объявлять ) и не нужно думать об их удалении или очищение в конце инструкции, так как они автоматически очищаются (как и обычные переменные ). Но при этом табличные переменные лучше использовать только тогда, когда Вы собираетесь хранить в них небольшой объём данных, в противном случае рекомендуется использовать временные таблицы.

Преимущества табличных переменных в Microsoft SQL Server

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

Недостатки табличных переменных в MS SQL Server

• Запросы, которые изменяют переменные TABLE, не создают параллельных планов выполнения запроса;
• Переменные TABLE не имеют статистики распределения и не запускают повторных компиляций, поэтому рекомендуется использовать их для небольшого количества строк;
• Табличные переменные нельзя изменить после их создания;
• Табличные переменные нельзя создавать путем инструкции SELECT INTO;
• Переменные TABLE не изменяются в случае откатов транзакций, так как имеют ограниченную область действия и не являются частью постоянных баз данных.

Примеры использования табличных переменных в Microsoft SQL Server

Сейчас давайте перейдем к практике, и для начала хотелось бы отметить, что в качестве сервера у меня выступает Microsoft SQL Server 2016 Express , другими словами все запросы ниже запускались на данной версии СУБД.

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

CREATE TABLE TestTable(ProductId INT IDENTITY(1,1) NOT NULL, ProductName VARCHAR(50) NULL CONSTRAINT PK_TestTable PRIMARY KEY CLUSTERED (ProductId ASC)) GO INSERT INTO TestTable (ProductName) VALUES ("Компьютер"), ("Монитор"), ("Принтер") GO SELECT * FROM TestTable

С помощью инструкции CREATE TABLE я создал таблицу TestTable, затем для добавления данных в таблицу я использовал инструкцию INSERT совместно с конструктором табличных значений VALUES , затем с помощью SELECT сделал выборку из только что созданной таблицы.

Объявление табличной переменной и ее использование

В данном примере мы объявим табличную переменную, добавим в нее данных, и сделаем выборку из двух таблиц (табличной переменной и обычной таблицы ) с объединением.

Объявление табличной переменной DECLARE @TableVar TABLE(ProductId INT NOT NULL, Price MONEY NULL); --Добавление данных в табличную переменную INSERT INTO @TableVar (ProductId, Price) VALUES (1, 500), (2, 300), (3, 200) --Использование табличной переменной с объединением данных SELECT TTable.ProductId, TTable.ProductName, TVar.Price FROM @TableVar TVar LEFT JOIN TestTable TTable ON TVar.ProductId = TTable.ProductId

Создание табличной переменной с первичным ключом, ограничением UNIQUE и с некластеризованным индексом

В данном примере показано, как можно создавать первичный ключ, ограничения UNIQUE и некластеризованные индексы для табличных переменных. Возможность создания некластеризованного индекса появилась, начиная с версии Microsoft SQL Server 2014.

Объявление табличной переменной DECLARE @TableVar TABLE(ProductId INT NOT NULL PRIMARY KEY, --Первичный ключ ProductName VARCHAR(50) NOT NULL, Price MONEY NOT NULL, UNIQUE (ProductName, Price), --Ограничение INDEX IX_TableVar NONCLUSTERED (Price) --Некластеризованный индекс); --Добавление данных в табличную переменную INSERT INTO @TableVar (ProductId, ProductName, Price) VALUES (1, "Компьютер", 500), (2, "Монитор", 300), (3, "Принтер", 200); --Выборка данных SELECT ProductName FROM @TableVar WHERE Price > 200

На этом мой рассказ о табличных переменных закончен, если Вы хотите детально изучить язык T-SQL, то рекомендую почитать мою книгу «Путь программиста T-SQL », надеюсь, материал был Вам полезен, пока!

Прежде чем использовать переменную или константу в программе, ее почти всегда необходимо объявить. Все объявления должны размещаться в разделе объявлений программы PL/SQL . В PL/SQL объявления могут относиться к переменным, константам, TYPE (например, коллекциям или записям) и исключениям. В этой статье рассматриваются объявления переменных и констант .

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

Когда вы объявляете переменную, PL/SQL выделяет память для хранения ее значения и присваивает выделенной области памяти имя, по которому это значение можно извлекать и изменять. В объявлении также задается тип данных переменной; он используется для проверки присваиваемых ей значений. Базовый синтаксис объявления переменной или константы:

Имя тип_данных [ := | DEFAULT значение_по_умолчанию]; BEGIN FOR book_rec IN (SELECT * FROM book) LOOP process_book (book_rec); END LOOP; END;

Преимущества объявлений с привязкой

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

• Синхронизация со столбцами базы данных. Переменная PL/SQL часто «представляет» информацию из таблицы базы данных. Если явно объявить переменную, а затем изменить структуру таблицы, это может привести к нарушению работы программы.
• Нормализация локальных переменных. Допустим, переменная PL/SQL хранит вычисляемые значения, которые используются в разных местах приложения. К каким последствиям может привести повторение (жесткое кодирование) одних и тех же типов данных и ограничений во всех объявлениях?