Основы программирования на Transact – SQL

 

Цель работы –изучение структурированного языка запросов Transact – SQL, являющегося основой системы программирования SQL Server 2000, и приобретение навыков применение инструментальных средств разработки и программирования объектов создаваемых баз данных.

Система программирования SQL Server 2000 относится к классу командно – интерпретирующих систем сверхвысокого уровня. Единицами действий системы являются команды, исполняемые в режиме интерпретации сразу же по мере их поступления в сервер. Основой этой системы программирования является проблемно – ориентированный структурированный язык запросов (Structured Query Language) Transact – SQL, который расширяется и развивает возможности стандарта ANSI SQL – 92.

 

 

Transact – SQL включает следующие средства:

1. данные различного типа баз данных и переменных;

2. константы, стандартные и ограниченные идентификаторы;

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

4. SQL – команды для создания, изменения и удаления баз данных и их объектов, а также для определения запросов на ввод, обработку и извлечение данных;

5. управляющие программные структуры, определяющие условия и порядок выполнения команд в заданной последовательности или пакете команд;

6. встроенные (системные) и определяемые пользователем функции;

7. встроенные (системные) и определяемые пользователем хранимые процедуры.

 

 

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

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

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

 

 

В языке Transact – SQL имеются следующие виды констант:

1. битовые: 0 и 1;

2. логические: FALSE и TRUE;

3. бинарные в шестнадцатеричном представлении: 0*9E70DA;

4. символьные: ‘ABC’; “ABC” (если QUOTED_IDENTIFIER = OFF); N ‘ABC’ (Unicode); N “ABC” (Unicode);

5. целые: 1; 2; 175;

6. с фиксированной точкой: 12.35; - 16.753;

7. с плавающей точкой: 1.75Е5; 3.84Е – 3;

8. для даты: “ April 15.2003”; “4/15/2003”; “20031207”;

9. для времени: 14:30; 14:30:20:999; 4am; 4pm;

10. денежные: $100;?200; 2.15.

 

 

Комментарии в языке бывают двух типов: сточные, начинающиеся с двух символов минуса

– и блочные, заключаемые символами /* и */.

Все объекты базы данных должны иметь имена, которые используются в командах для ссылки на эти объекты. Любой объект базы данных должен быть уникально идентифицирован. Помимо программных имен сервер автоматически генерирует внутренние уникальные имена для идентификации объектов баз данных, например, PK_ _Table X_ _ 014543FA.

 

 

Программные имена задаются идентификаторами двух типов:

1. стандартными идентификаторами: Table X; Key Col;

2. ограниченными идентификаторами: [My Table]; [Order]; “My Table”; “Order” (если

QUOTED_IDENTIFIER = ON).

 

 

Длина идентификатора – от 1 до 128 символов. Идентификатором не может быть какое-

либо зарезервированное ключевое слово языка.

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

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

В соответствии с идеологией SQL Server 2000 каждый объект создается определенным пользователем и принадлежит той или иной базе данных. В свою очередь база данных расположена на конкретном сервере. Из имен объекта, пользователя, базы данных и сервера создается полное имя (complete name) или полностью определенное имя (full qualified name), записываемое в следующем виде:

[[[server.].[database].[owner_name].] object_name.

 

 

Варианты обращения к объектам базы данных: A.B.C.D; A.B..D; A..C.D; A..D; B.C.D; B..D; C.D; D.

Чтобы сослаться на конкретный столбец таблицы или представления, необходимо в полном имени указать пятый элемент: А.В.С.D.E.

В Transact – SQL существует несколько способов передачи данных между командами. Одним из таких способов является передача данных через локальные переменные, объявляемые следующим образом:

DECLARE {@ имя локальной переменной тип данных}[,…n]

Таким образом, знак @ является признаком имени локальной переменной. Этот же знак используется для определения имен параметров функций и хранимых процедур. Часть синтаксиса [,…n]означает повторение синтаксической конструкции, взятой в фигурные скобки:

DECLARE @Ivar int, DECLARE @IBit bit

 

Значения переменным можно присвоить с помощью команд SET и SELECT. Командой SET

можно присвоить значение только одной переменной: SET @Ivar = 5

SET @IBit = 0

 

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

SELECT @Ivar = SUM (price) FROM titles _ _ см. окно Result.

 

Для вывода значений переменных следует использовать команды: SELECT – для вывода данных в стандартный набор строк;

PRINT – для вывода данных как служебной информации.

 

 

Примеры вывода значений переменных:

SELECT @Ivar _ _ в окно Grids утилиты Query Analyzer; PRINT @IBit _ _ в окно Messages утилиты Query Analyzer.

 

 

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

Операторами выражения могут быть унарные (+ и - ), бинарные арифметические операторы (+, -, *, % ), оператор присваивания (=), строковая операция конкатенации (+), операторы сравнения (=, >, <, <=, >=, =, != или <>, !<, !>), логические операторы (NOT, AND, OR, ALL, ANY, BETWEEN, EXIST, IN, LIKE, SOME ) и битовые операторы (&, |, ^).

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

 

 

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

1. BEGIN…END – для создания блока последовательных команд.

2. IN…ELSE – для определения условия выбора команды или блока.

3. CASE…END – для реализации условного выражения с несколькими альтернативами: CFSE…WHEN…WHEN…ELSE…END.

4. COALESCE – для обработки совместных выражений (возвращают первое ненулевое значение в списке);

5. WHIL…BREAK…CONTINUE – для организации и управления циклически выполняемых команд.

 

 

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

1. Выражение {= | < > | ! = | > | >= | ! >|, = | !<} ALL подзапрос.

Здесь скалярное выражение вычисляется и сравнивается с каждым значением, возвращаемым подзапросом. Если сравнение дает истину для всех возвращаемых подзапросом значений, то этот оператор возвращает истину.

2. Если вместо ALL записать SOME или ANY, то результатом будет истина, если хотя в одной строке будет выполняться заданное сравнение.

3. Выражение [NOT] BETWEEN Н Выражение AND В Выражение возвращает истину, когда значение выражения лежит в диапазоне значений Н выражения и В Выражения (или не лежит).

4. Оператор EXISTS (подзапрос) возвращает значение истина, если подзапрос возвращает хотя бы одну строку.

5. Выражение [NOT] IN (подзапрос \ выражение [,…n]) возвращает значение истина, если значение левого выражения совпадает с одним из значений подзапроса или списка значений правых выражений (или не совпадает).

6. Выражение [NOT] LIKE шаблон [ESCAPE знак] дает истину, если значение выражения соответствует или не соответствует шаблону, в котором “%” означает любое количество произвольных символов, “_” – один произвольный символ, “[символы]” – один из указанных в скобках, “[^ символы]” – все символы, кроме указанных. Знак после слова ESCAPE позволяет указать, что следующий за ним знак шаблона не является управляющим знаком шаблона, т.е. знаком “%”, “_” и т.д., а представляет обычный знак строки.

 

 

Раздел документации сервера T – SQL Help содержит описание каждой команды языка Transact – SQL B и набор примеров их использования. Синтаксис команды определяется с помощью специального метаязыка, основанного на нормальных формах Бекуса Наура (БНФ). Для команды CREATE DATABASE запись синтаксиса на этом метаязыке будет выглядеть следующим образом:

CRIATE DATABASE database_name

[ON

[<filespec> [,…n]] [<filegroup> [,…n]]

]

[LOG ON {<file spec> [,…n]}] [COLLATE collation_name] [FOR LOAD| FOR ATTACH]

 

<filespec> : : = [PRIMARY]

( [NAME=Logial_file_name,] FILENAME=’os_file_name’ [, SIZE=size]

[, MAXSIZE={MAX_SIZE|UNLIMITED}]

[, FILEGROWTH=growth_increment]) [,…n]

 

<filegroup> : : =

FILEGROUP filegroup_ name <filespec> [,…n]

 

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

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

Метасинтаксические знаки имеют следующий смысл:

 

::= |	[ ]	― ― ―	есть по определению; выбор альтернативы; возможное отсутствие части определения;
	{ }	―	объединение частей определения для выюора или повторения;
	[,…n]	―	повторение предшествующей части 1, 2, …, n раз с разделителем запятая для этой части (разделитель может
			быть любой;)
	<…>	―	метапеременная, которая имеет свое определение.

 

Из определения синтаксиса команды GREATE DATABASE следует:

a) для создания базы данных в ряде случаев достаточно задать лишь ее имя;

имеется возможность задавать полные пути и имена файлов, как для данных, так и для журнала транзакций (logon);

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

c) можно использовать группы файлов для ускорения операций ввода – ввода

 

Задание 1.Используя команду SELECT в форме SELECT {выражение as имя колонки} [,…], создать и сохранить в файле SQLExpression пакет команд, обеспечивающий демонстрацию следующих возможностей языка Transact – SQL:

a) использование констант всех видов;

b) применение ограниченных идентификаторов;

c) использование полностью определенных имен объектов;

d) объявление переменных и их использование для связи между командами;

e) использование в выражениях арифметических и строковых операторов, операторов сравнения, логических и битовых операторов;

f) использование системных функций и функций, определенных пользователем;

g) использование управляющих конструкций языка.

 

 

Задание 2.Написать команду для создания таблицы, колонки которой имели бы все допустимые типы данных и значения по умолчанию. Для простоты имя колонки должно начинаться с буквы F, а далее следует использовать имя типа: Fbigint, Fint и т.д. Скрипт сохранить в файле SQLColType.

 

 

Задание 3.Написать команды объявления переменных всех типов, задать им значения с помощью операторов присваивания и включить эти значения в таблицу, созданную в задании 2

 

 

Задание 4.Написать примеры использования переменных вариантного типа sql_variant.

 

 

Задание 5.Открыть таблицу systypes и ознакомиться с ее структурой. Создать два соответственных типа данных.

 

 

Задание 6.Написать запросы для базы данных Pubs, используя логические операторы

ALL, SOME, ANY, BETWEEN, EXISTS, IN, LIKE.