Главная » Microsoft SQL Server, Базы данных » Основы Transact-SQL

0

Язык Т-SQL предназначен для управления наборами данных. По этой причине он не обладает некоторыми характерными чертами традиционных языков, которые необходимы для программирования приложений. Если вы уже давно занимаетесь созданием приложений, то наверняка противопоставите мышление программирования в Т-SQL и в других языках, таких как VB, C# и Java.

Пакеты T-SQL

Запросом называют одну инструкцию Т-SQL, а пакетом — их набор. Вся последовательность инструкций пакета отправляется серверу из клиентских приложений как одна цельная единица.

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

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

Файл сценария SQL и окно анализатора запросов (Query Analyzer) может содержать несколько пакетов. В данном случае все пакеты разделяют ключевые слова терминаторов. По умолчанию этим ключевым словом является GO, и оно должно быть единственным в строке. Все другие символы (даже комментарии) нейтрализуют разделитель пакета.

Разделитель пакетов на самом деле является функцией Management Studio, а не самого сервера. Его можно изменить на странице Query Execution диалогового окна свойств программы, но я не рекомендовал бы этого делать (по крайней мере, друзьям).

Инструкции DDL

Некоторые инструкции DDL языка Т-SQL, такие как Create Procedure, обязательно должны быть первыми инструкциями пакета. Очень длинные сценарии, которые создают множество объектов, часто требуют наличия нескольких разделителей пакетов. Так как SQL Server отдельно разбирает синтаксис по пакетам, такое наличие множества разделителей помогает локализовать ошибки.

Переключение между базами данных

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

USE СНА2

На практике рекомендуется явно определять текущую базу данных с помощью команды USE — незачем надеяться на пользователя.

Выполнение пакетов

Пакет может быть выполнен несколькими способами.

?               Сценарий SQL в полном объеме (т.е. все входящие в него пакеты) может быть выполнен путем открытия файла . sql в редакторе SQL утилиты Manage ment Stu dio и нажатия клавиши <F5> (или щелчка на кнопке ! Execute панели инструментов, или выбора в меню пункта Query1^Execute). (Я настроил свою операционную систему Windows так, чтобы при двойном щелчке на файле . sql автоматически запускался анализатор запросов.)

?               В редакторе SQL утилиты Management Studio могут быть выполнены и отдельные инструкции SQL. Для этого их нужно выделить и нажать клавишу <F5> (или щелкнуть на кнопке ! Execute панели инструментов, или выбрать в меню пункт Query1^Execute).

?               В приложении пакет Т-SQL можно выполнить с помощью ADO или ODBC.

О         Сценарий Т-SQL может быть выполнен с помощью утилиты командной строки SQLCmd с передачей ей имени файла . sql в качестве параметра.

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

Дополнительная Подробно об утилите SQLCmd см. в главе 6, посвященной Management Studio.

информация

Выполнение хранимой процедуры

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

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

Следующие два вызова системной хранимой процедуры демонстрируют использование команды ехес в пакете:

Sp_help;

ЕХЕС sp_help;

В этом разделе мы рассмотрели только использование команды ехес в пакете. Более подробная информация о творческом использовании ключевого слова ехес содержится в разделе “Динамический SQL”.

Форматирование в T-SQL

На протяжении всей этой книги программный код отформатирован для улучшения наглядности; в этом разделе мы рассмотрим ключевые моменты форматирования.

Завершение инструкции

Стандарт ANSI SQL требует помещения в конце каждой инструкции точки с запятой. В то же время при программировании на языке Т-SQL точка с запятой не обязательна. При этом следует руководствоваться несколькими правилами.

?               Не помещайте ее после оператора try end.

?               Не помещайте ее после условия if.

?               Обязательно помещайте ее после общетабличных выражений СТЕ.

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

Продление строк

Инструкции Т-SQL по своей природе имеют свойство быть длинными. Некоторые запросы последней главы с многочисленными объединениями и подзапросами занимают целую страницу. Лично мне нравится, что в Т-SQL игнорируются символы пробелов и конца строки. Это значит, что длинная инструкция может быть продолжена на следующей строке без необходимости наличия какого-либо специального символа. Это свойство позволяет в значительной мере повысить читаемость программного кода.

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

Комментарии

Язык Т-SQL допускает использование в одном пакете комментариев двух стилей: ANCI и языка С. Первый из них начинается с двух дефисов и заканчивается в конце строки:

— Это комментарий стиля ANSI

Также комментарии стиля ANSI могут вставляться в конце строки инструкции:

Select FirstName, LastName – извлекаемые столбцы FROM Persons – исходная таблица

Where LastName Like ‘Hal%'; — ограничение на строки

Редактор SQL может применять и удалять комментарии во всех выделенных строках. Для этого нужно соответственно выбрать команду меню Edit^Advanced^Comment Out (<Ctrl+C> или <Ctrl+K>) или Edit^ Advanced1^ Remove Comments (<Ctrl+K> или <Ctrl+U>).

Комментарии стиля языка С начинаются с косой черты и звездочки (/*) и заканчиваются теми же символами в обратной последовательности. Этот тип комментариев лучше использовать для комментирования блоков строк, таких как заголовки или большие тестовые запросы. /*

Триггер вставки таблицы Order Пол Нильсен

версия 1.0 21 июля 2 006 года логика: и т.д.

версия 1.1: 31 июля 2006 года, добавлено то-то и то-то */

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

Отладка T-SQL

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

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

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

Select 3;

Print 6;

Результирующий набор данных отобразится в сетке и будет состоять из одной строки. В то же время во вкладке Messages отобразится следующий результат:

(1 row(s) affected)

6

Иногда полезно приостанавливать выполнение программы, чтобы увидеть блокировки или содержимое объектов. Команда pause позволяет приостановить выполнение пакета на заданное время. Например, при выполнении следующего кода его вторая строка вывода отобразится после двухсекундной паузы:

Print 1 Начало';

waitfor delay *00:00:02′ ;

Print ‘Конец1;

Результат выполнения кода:

Начало

Конец

Ключевым моментом является то, что в утилиту Management Studio версии SQL Новинка  Server 2005 не включен отладчик языка Т-SQL, — он присутствует в пакете

2005 а    Visual Studio 2005. Если в будущем отладчик будет включен в какой-либо пакет

обновлений, я сообщу об этом на сайте www. SQLServerBible. com.

Переменные

Любой язык требует для временного хранения значений в памяти наличия переменных. Переменные Т-SQL создаются с помощью команды declare, за которой следуют имя переменной и ее тип. Используемые для переменных типы данных в точности совпадают с существующими в таблицах. К этому можно добавить табличный тип и тип SQLVariant. В одной команде declare через запятую может быть перечислено несколько переменных.

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

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

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

DECLARE @Test INT,

@TestTwo NVARCHAR(25);

SELECT @Test, @TestTwo;

SET @Test = 1;

SET @TestTwo = ‘значение';

SELECT @Test, @TestTwo ;

Go

SELECT @Test as BatchTwo, @TestTwo;

NULL  NULL

(1 row(s) affected)

Value

1         значение

(1 row(s) affected)

Msg 137, Level 15, State 2, Line 2 Must declare the scalar variable “@Test”.

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

Эти переменные имеют локальную область определения, которая не распространяется на другие пакеты и хранимые процедуры.

Использование команд set и select

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

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

USE Family;

Declare ©TempID INT,

@TempLastName VARCHAR(25);

SET @TempID = 99;

SELECT

@TempID = PersonID,

@TempLastName = LastName

FROM Person

ORDER BY PersonID;

SELECT @TempID, @TempLastName;

Результат выполнения пакета:

32 @code last:Campbell

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

Если инструкция SELECT не возвращает ни одной строки, то на переменные не оказывается никакого влияния. Следующий запрос не возвращает значений, поскольку записи с идентификатором 100 в таблице Person не существует. По этой причине переменной @TempIDvariable присваивается значение последней существующей строки, при этом переменная фамилии сохраняет изначальное пустое значение:

Declare @TempID INT,

@TempLastName VARCHAR(25);

SET @TempID = 99;

SELECT @TempID = PersonID,

@TempLastName = LastName FROM Person WHERE PersonID = 100 ORDER BY PersonID;

SELECT @TempID, @TempLastName;

99 @code last:NULL

Условный отбор

Следующая инструкция SELECT содержит предложение WHERE, и ее синтаксис правильный, хотя для некоторых может выглядеть непривычно:

SELECT @переменная = выражение WHERE булево_выражение;

В данном случае предложение WHERE функционирует как условный оператор if. Если булево выражение истинно, то переменной присваивается значение, в противном случае инструкция SELECT все равно выполняется, но значение переменной не изменяется.

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

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

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

DECLARE @ProductCode CHAR(10);

SET @Code = ‘1001’;

SELECT ProductName FROM Product

WHERE Code = @ProductCode;

Будет получен следующий результат:

Name

Basic Box Kite 21 inch

Переменные с множественным присвоением

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

В этом разделе будет продемонстрирован ряд примеров из реальной жизни. Так как это несколько необычное использование инструкции SELECT, разрешите привести ее базовую форму:

SELECT @переменная = @переменная + d.столбец FROM (управляемая_таблица) as d;

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

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

В следующем примере создается список дат событий тура Outer Banks Lighthouses, предлагаемого в учебной базе данных Cape Hatter as Ad ventures:

USE CHA2;

DECLARE

@EventDates VARCHAR(1024);

SET @EventDates = ‘ ‘ ;

SELECT ©EventDates = @EventDates + CONVERT(VARCHAR(15), a.d,107 ) + ‘

FROM (select DateBegin as [d] from Event join Tour

on Event.TourID = Tour.TourlD WHERE Tour.[Name] = ‘Outer Banks Lighthouses’) as a;

SELECT Left(@EventDates, Len(@EventDates)-1)

AS ‘Outer Banks Lighthouses Events';

Результат выполнения пакета:

Outer Banks Lighthouses Events

Feb 02, 2001; Jun 06, 2001; Jul 03, 2001; Aug 17, 2001;

Oct 03, 2001; Nov 16, 2001

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

Источник: Нильсен, Пол. Microsoft SQL Server 2005. Библия пользователя. : Пер. с англ. — М. : ООО “И.Д. Вильямс”, 2008. — 1232 с. : ил. — Парал. тит. англ.

По теме:

  • Комментарии