Главная » Файлы » Разное » Учеба
Общие сведения о Transact-SQL
[ Скачать с сервера (136.5 Kb) ] 12.12.2012, 00:33
Лекция 3. Общие сведения о Transact-SQL

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

Цель: дать представление об основных возможностях языка Transact-SQL.

Общие сведения о Transact-SQL

В 1970-х годах был разработан специальный язык SEQUEL (Structured English Query Language), который позднее был переименован в SQL. В 1986 году комитетом ANSI (American National Standards Institute) был принят первый стандарт языка. Последним принятым стандартом является ISO SQL:2008.
Язык SQL разрабатывался как непроцедурный язык, который будет доступен обычным пользователям, а не только программистам. Однако со временем язык становился все сложнее и сейчас предназначен в большей степени для программистов. Несмотря на существование стандартов языка SQL, в каждой СУБД используется свое расширение этого языка. Основной причиной использования расширений SQL является потребность применять SQL не только как язык запросов, но и как язык программирования, для этого, как правило, вводятся так называемые хранимые процедуры. СУБД Microsoft SQL Server использует свое расширение языка, которое называется Transact-SQL и включает в себя следующее:
• управляющие конструкции;
• локальные переменные;
• дополнительные функции (математические, строковые и другие);
• поддержка аутентификации Windows.

Типы данных

MS SQL Server поддерживает все основные простые типы данных, используемые в современных языках программирования. В версии MS SQL Server 2008 были добавлены несколько новых типов, а некоторые перестали рекомендоваться к использованию.
Типы данных в MS SQL Server можно разделить на семь категорий.

1. Целые числа:
• Bit (1 байт). На самом деле первый бит в таблице будет занимать один байт, однако следующие семь бит в этой таблице будут храниться в том же байте.
• Bigint (8 байт). 64-разрядное целое число, позволяет хранить числа от –263 до +263–1.
• Int (4 байта). Диапазон значений от –2 147 483 648 до +2 147 483 647.
• SmallInt (2 байта). Диапазон значений от –32768 до +32767.
• TinyInt (1 байт). Диапазон значений от 0 до 255.

2. Числа с фиксированной запятой:
• Decimal или Numeric. Диапазон значений от –1038–1 до +1038–1.
• Money (8 байт). Денежный формат, диапазон значений от –263 до +263 с четырьмя знаками после запятой.
• SmallMoney (4 байта). Денежный формат, диапазон значений от
–214748,3648 до +214748,3647.

3. Числа с плавающей запятой:
• Float. Диапазон значений от –1,79E +308 до +1,79E +308.

4. Дата и время:
• DateTime (8 байт). Диапазон значений от 1 января 1753 года до 31 декабря 9999 года с точностью до трех сотых секунды.
• DateTime2 (6-8 байт). Новый тип данных, поддерживает точность до 0,1 мс.
• SmallDateTime (4 байта). Диапазон значений от 1 января 1900 года до 6 июня 2079 года с точностью одна минута.
• DateTimeOffset (8-10 байт). Аналогичен типу DateTime, но хранит ещё сдвиг относительно времени UTC.
• Date (3 байта). Хранит только дату. Диапазон значений от 1 января 0001 года до 31 декабря 9999 года.
• Time (3-5 байт). Хранит только время с точностью до 0,1 мс.

5. Символьные строки:
• Char. Строка фиксированной длины. Максимальная длина строки 8000 символов.
• VarChar. Строка переменной длины. Максимальная длина строки 8000, но при использовании ключевого слова «max» может хранить до 231 байт.
• Text. Применялся для хранения больших строк, сейчас рекомендуется использование varchar(max) вместо text. Оставлен для обратной совместимости.
• Nchar. Строка фиксированной длины в Юникоде. Максимальная длина строки 4000 символов.
• NvarChar. Строка переменной длины в Юникоде. Максимальная длина строки 4000 символов, но при использовании ключевого слова «max» может хранить до 231 байт.
• Ntext. Аналогичен типу text, но предназначен для работы с Юникод. Сейчас рекомендуется использовать nvarchar(max). Оставлен для обратной совместимости.

6. Двоичные данные:
• Binary. Позволяет хранить двоичные данные размером до 8000 байт.
• VarBinary. Тип данных переменной длины, позволяет хранить до 8000 байт, но при использовании ключевого слова «max» до 231 байт.
• Image. Использовался для хранения больших объемов данных, сейчас рекомендуется использовать varbinary(max). Оставлен для обратной совместимости.

7. Прочие типы данных:
• Table. Особый тип данных, используемый в основном для временного хранения таблиц и для передачи в качестве параметра в функции.
• HierarchyID. Используется для представления положения в иерархической структуре.
• Sql_variant. Тип данных, хранящий значения различных типов данных, поддерживаемых MS SQL Server.
• XML. Позволяет хранить XML-данные.
• Cursor (1 байт). Тип данных для переменных или выходных параметров хранимых процедур, которые содержат ссылку на курсор.
• Timestamp / rowversion (8 байт). Это тип данных, который представляет собой автоматически сформированные уникальные двоичные числа в базе данных. Значение данного типа генерируется БД автоматически при вставке или изменении записи.
• UniqueIdentifier (16 байт). Представляет собой GUID (Special Globally Unique Identifier). Гарантируется уникальность данного значения.

Переменные в Transact-SQL

Любой объект базы данных должен обладать уникальным именем внутри этой базы. На основе имени происходит обращение к этому объекту. Имена объектов называются идентификаторами. Transact-SQL накладывает ряд ограничений на порядок именования объектов:
• первый символ имени должен быть одним из символов латинского алфавита (A-Z, a-z), либо символом @, # или _, т.е. не допускается использование цифр и прочих специальных символов. Остальная часть имени может включать любые символы алфавита, цифры и некоторые специальные символы;
• общая длина имени обычного объекта не должна превышать 128 символов, для временных объектов – 116;
• внутри имени запрещается использование пробелов, скобок и таких символов, как ~, !, %, ^, & и др.
• имя объекта не должно совпадать с зарезервированным словом и с именем уже существующего объекта;
• если имя объекта содержит пробелы или совпадает с зарезервированным словом, то его необходимо поместить внутрь квадратных скобок [ ].

Замечание: Transact-SQL является CASE-нечувствительным языком, т.е. не различает регистра символов.

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

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

Синтаксис команды (внутри квадратных скобок в описании синтаксиса располагаются необязательные элементы):
DECLARE @ИмяПеременной ТипДанных [ ,…n]
Пример объявления одной переменной:
DECLARE @sum int
Пример объявления нескольких переменных:
DECLARE @temp int, @count float

Присвоение значения

Синтаксис команды:
SET @ИмяПеременной = Выражение
Пример присвоения значения:
SET @sum = 0
В версии MS SQL Server 2008 появились следующие нововведения.
• Инициализировать переменную стало можно сразу при объявлении, например:
DECLARE @iCounter int = 0
• Появились операторы +=, –=, *=, /= для краткой формы записи арифметических конструкций.

Управляющие конструкции Transact-SQL

Группировка команд
Группировка двух и более команд в единый блок осуществляется с помощью конструкции:
BEGIN
...
END
Такая группировка используется в условных и циклических конструкциях.

Конструкция ветвления
Выполнение той или иной группы команд в зависимости от выполнения или не выполнения некоторого условия реализуется с помощью конструкции:
IF <условие>
Оператор
[ELSE
Оператор ]
При отсутствии команд, выполняемых при несоблюдении условия, ключевое слово ELSE можно не указывать.
Следует отметить особенность проверки значений на NULL (специальный маркер, обозначающий отсутствие информации). Вместо обычного сравнения: IF @myvar = NULL, следует использовать оператор IS: IF @myvar IS NULL.

Конструкция CASE
Аналогична оператору CASE в языках программирования. В MS SQL Server 2008 оператор CASE имеет два возможных варианта использования.

1. С входным выражением:
CASE <входное выражение>
WHEN <выражение when > THEN <результат>
[...]
[ELSE <результат>]
END
Пример:
SELECT TOP 10 SalesOrderID, SalesOrderID % 10 AS ‘Last Digit’, Position = CASE SalesOrderID % 10
WHEN 1 THEN ‘Один’
WHEN 2 THEN ‘Два’
WHEN 3 THEN ‘Три’
WHEN 4 THEN ‘Четыре’
ELSE ‘Другое’
END
FROM Sales.SalesOrderHeader;
Результат запроса представлен на рис. 3.1.



Рис. 3.1. Результат использования оператора CASE

2. Без входного выражения:
CASE
WHEN <логическое выражение> THEN <результат>
[...]
[ELSE <результат>]
END
Используется, как правило, для поиска.

Пример:
SELECT TOP 10 SalesOrderID % 10 AS 'OrderLastDigit',
ProductID % 10 AS 'ProductLastDigit',
"How Close?" = CASE
WHEN (SalesOrderID % 10) < 3 THEN 'Меньше трёх'
WHEN ProductID = 6 THEN 'ProductID равен 6'
WHEN ABS(SalesOrderID % 10 - ProductID) <= 1 THEN ' В пределах одного'
ELSE 'Больше одного'
END
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderID DESC;
Результат выполнения запроса представлен на рис. 3.2.



Рис. 3.2. Результат использования оператора CASE

Циклическая конструкция
Transact-SQL поддерживает единственный тип цикла – цикл WHILE, синтаксис которого следующий:
WHILE условие
Оператор
[BREAK | CONTINUE]
Замечание. Вертикальная черта в описании синтаксиса означает «или», т.е. в данном примере может быть указан либо BREAK, либо CONTINUE.
Тело цикла выполняется до тех пор, пока условие истинно. Цикл можно принудительно остановить, если выполнить в теле цикла команду BREAK. Если же нужно начать цикл заново, не дожидаясь выполнения команд тела цикла, необходимо выполнить команду CONTINUE.

Конструкция WAITFOR
В некоторых случаях требуется отложить выполнение той или иной команды. Для этих целей можно воспользоваться оператором WAITFOR. Эта команда имеет следующий синтаксис:
WAITFOR DELAY <’time’> | TIME <’time’>
Если используется параметр DELAY, то указывается, сколько времени необходимо ждать MS SQL Server. Максимально возможная задержка – 24 часа. Пример использования: WAITFOR DELAY ‘01:00’, который приостановит выполнение на один час.
Если используется параметр TIME, то исполнение будет приостановлено до наступления заданного времени. Пример использования: WAITFOR TIME ‘01:00’ – приостановление выполнения кода до наступления часа ночи.

Блок TRY/CATCH
Данную конструкцию можно использовать для обработки исключительных ситуаций. Впервые это конструкция появилась в MS SQL Server 2005.
Блок TRY/CATCH в MS SQL Server работает также как и в других языках программирования. Используется следующий синтаксис:
BEGIN TRY
{ <выражения SQL> }
END TRY
BEGIN CATCH
{ <выражения SQL> }
END CATCH [ ; ]
В блоке BEGIN TRY... END TRY выполняются потенциально опасные команды, если при этом произойдет ошибка уровня 11-19, то выполнение будет передано в блок CATCH.
Уровни ошибок:
• 1–10 Информационные сообщения. Например, обнаружение NULL значений при выполнении агрегатных функций. Так как управление в блок CATCH данные ошибки не передают, то для получения информации об ошибки можно использовать функцию @@ERROR.
• 11–19 Относительно серьезные ошибки. Например, нарушение ограничений внешнего ключа.
• 20–25 Очень серьезные ошибки. Это ошибки на уровне системы, поэтому в коде нельзя узнать о ее возникновении. Такие ошибки разрывают текущее соединение и прерывают выполнение команд.

Комментарии

Существует два вида комментариев:
• однострочные – в этом случае игнорируется текст справа от символов комментария: -- (двойной дефис);
• многострочные – игнорируется текст, записанный между двумя парами символов: /* … */.
Функции Transact-SQL

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

Скалярные функции

Выделяют следующие категории скалярных функций – математические, строковые, для работы с датами, конфигурационные и системные. Рассмотрим каждую категорию.

Математические функции
Большинство математических функций возвращают результат того же типа, что и исходные значения. Например, при переводе величины угла из градусов в радианы в случае Radians(90) результат равен 1, что неверно, так как в качестве аргумента функции использовано целое число. Правильная запись: Radians (90.0).
Abs (выражение) – вычисление абсолютного значения выражения. Можно использовать как целочисленные, так и нецелочисленные величины.
IsNumeric (выражение) – проверка, имеет ли указанное выражение числовой тип данных. Результат равен 1, если выражение имеет числовой тип, иначе – 0.
Rand () – возвращает случайное значение на основе системного времени в диапазоне от 0 до 1.
Floor (выражение) – округление указанного значения до ближайшего минимального целого числа.
Ceiling (выражение) – возвращает ближайшее целое число, большее или равное данному.
Power (выражение, степень) – возведение в степень выражения. Тип возвращаемого значения совпадает с исходным типом.
Sqrt (выражение) – вычисляет квадратный корень.

Строковые функции
Ascii (строка) – возвращает ASCII-код самого левого символа строки.
Char (выражение) – возвращает символ, ASCII-код которого соответствует указанному числовому выражению.
Len (строка) – вычисляет длину строки в символах.
LTrim (строка), RTrim (строка) – удаляют начальные и концевые пробелы соответственно.
Left (строка, число), Right (строка, число) – возвращают указанное количество символов строки, начиная с левого и правого края строки соответственно.
SubString (строка, начало, длина) – возвращает для строки подстроку указанной длины, начиная с указанного символа.
CharIndex (строка1, строка2 [, старт]) – поиск подстроки строка1 в строке строка2. Возвращает порядковый номер первого символа, с которого начинается первое вхождение подстроки в строку. Дополнительно можно указать стартовую позицию, с которой будет начат поиск.
Stuff (строка1, начало, длина, строка2) – удаляет определенное количество символов строки1, начиная с указанного, и заменяет их новой строкой2.
Replace (строка1, строка2, строка3) – заменяет все вхождения строки строка2 в исходной строке строка1 на строка3.
Reverse (строка) – возвращает строку, символы которой записаны в обратном порядке по отношению к исходной строке.

Функции для работы с датами
GetDate () – возвращает текущее системное время.
IsDate (выражение) – проверяет правильность выражения на соответствие одному из возможных форматов ввода даты.
Day (дата), Month(дата), Year(дата) – возвращают день, месяц и год из указанной даты.
DateName (тип, дата) – выделяет из даты указанную в типе часть и возвращает ее в символьном формате. Формат частей: yy или yyyy – год, qq или q – квартал, mm или m – месяц, dd или d – день, wk или ww – неделя, hh – час, mi или m – минута, ss или s – секунда, ms – миллисекунда.
DatePart (тип, дата) – выделяет из даты указанную часть и возвращает ее в числовом формате.
DateAdd (тип, число, дата) – добавляет к указанной дате число, тип которого указан в первом параметре.
DateDiff (тип, начало, окончание) – возвращает разницу между указанными частями дат в указанном типе.

Конфигурационные функции
Возвращают информацию о текущей конфигурации MS SQL Server. Например:
@@Version – возвращает информацию о дате, версии и типе процессора сервера.
@@ServerName – символьное имя локального MS SQL Server.
@@Max_Connections – максимально разрешенное количество одновременных подключений к серверу.

Системные функции
Возвращают информацию о значениях, объектах и текущих параметрах MS SQL Server.
DataLength(выражение) – возвращает число, соответствующее количеству байт, необходимых для хранения результата выражения.
@@Error – код последней ошибки, произошедшей в текущем соединении. Если ошибок нет, результат равен 0.
Host_Name() – символьное имя компьютера в сети, на котором выполняется команда.
System_User и Session_User – возвращают соответственно имя учетной записи пользователя для входа и имя пользователя текущей базы данных.
@@IDLE – определяет количество миллисекунд, прошедшее со времени последнего запуска MS SQL Server.
NewID() – генерирует новое значение типа UniqueIdentifier.
Permission ([ObjectID[, ‘column’]]) – возвращает информацию о правах доступа для текущего пользователя. Аргумент ObjectID указывает идентификационный номер объекта базы данных. Для получения идентификационного номера объекта по его имени используется функция Object_ID(‘имя’).
Результатом данной функции является 32-битное значение, каждый бит которого соответствует тому или иному праву доступа. Если значение этого бита равно 1, то доступ к объекту разрешен.
Определим, например, имеет ли пользователь право выборки данных из таблицы Product БД AdventureWorks2008:
SELECT Permissions (object_id('production.product'))
Если первый младший бит результата равен 1, то выборка данных из таблицы Product разрешена.
Чтобы проверить право доступа к полю некоторой таблицы, необходимо указать идентификационный номер этой таблицы данных и в аргументе ‘column’ указать имя этого поля. Например, для проверки возможности выборки данных из поля Stor_id таблицы Sales необходимо:
SELECT Permissions (object_id('production.product'),'ProductID')
Если первый младший бит результата равен 1, то выборка данных из поля ProductID таблицы Product также разрешена.

Отладка кода в Management Studio

В MS SQL Server 2008 появилась возможность отлаживать SQL код при помощи пошагового выполнения. Для этого существуют команды Debug – Step Into (F11) и Debug – Step Over (F10). Команда Debug – Toggle Breakpoint (F9) устанавливает точку останову, до которой будет происходить выполнение кода.
Во время отладки доступны такие окна:
• Locals – автоматически содержит список всех локальных переменных и показывает их текущие значения;
• Watch – позволяет вручную добавлять переменные для отслеживания их значений;
• Callstack – показывает стек вызовов функций.

Снимок экрана во время процесса отладки показан на рис. 3.3.



Рис. 3.3. Окно отладки в Management Studio.
Желтая стрелка указывает на текущую исполняемую строку;
красный кружок – точка останова.

Краткие итоги. Рассмотрены типы данных, доступные в MS SQL Server 2008. Продемонстрировано использование базовых алгоритмических конструкций и локальных переменных, а также показана возможность отладки SQL-кода.
Категория: Учеба | Добавил: Warkl
Просмотров: 1057 | Загрузок: 161
Всего комментариев: 0
Добавлять комментарии могут только зарегистрированные пользователи.
[ Регистрация | Вход ]