Временные таблицы ( Temporary tables ): бывают локальные и глобальные.

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

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

Представления (Views) :

Представление для пользователей базы данных выглядит как таблица, однако на самом деле его содержимое формируется запросом. Физически данные, виртуально принадлежащие представлению, находятся в таблицах, к которым обращается этот запрос. Однако для клиентов MS SQL Server запросы на выборку данных из представления выполняются как для полноценной таблицы. Представление может быть использовано: (1) для защиты конфиденциальной информации; (2) для упрощения доступа к информации и (3) сокращения времени доступа. Для таких целей представление может быть проиндексировано.

Достоинства: представление может выбирать данные (1) из таблиц текущей и любой другой базы данных, (2) из представлений текущей и любой другой базы данных (в том числе расположенные на разных серверах).

Недостатки: (1) представление не может ссылаться на временные таблицы, и невозможно создать временные представления; (2) в запросе, определяющим представление, нельзя использовать разделы ORDER BY и INTO ; (3) имеется ряд ограничений на изменение, добавление и удаление данных в представлении, созданном для нескольких таблиц.

Команды TSQL , используемые для работы с представлениями:

CREATE VIEW <имя представления> AS < SELECT -запрос> – создание представления.

CREATE VIEW My_view AS SELECT au_lname, au_fname, address

FROM authors WHERE state='CA'

ALTER VIEW <имя представления> AS < SELECT -запрос> – изменение представления (может потребоваться, например, в случае, если нежелательно изменять права доступа к уже существовавшему представлению.

DROP VIEW <имя представления>, …– удаление представлений (одного или нескольких)

Хранимая процедура sp _ help возвращает информацию о различных параметрах представления, в качестве единственного аргумента которой указывается имя представления. Эта же процедура возвращает и об объектах других типов – таблицах, триггерах, индексах, хранимых процедурах и др.

Курсоры ( Cursors ):

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

•  курсоры позволяют работать со строками таблицы посредством указания их порядкового номера в наборе данных;

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

Жизненный цикл курсора :

Создание курсора : DECLARE <имя курсора> [ INSENSITIVE ] [ SCROLL ] CURSOR FOR < SELECT -оператор> FOR { READ ONLY | UPDATE }

Здесь ключевое слово INSENSITIVE означает, что курсор будет статическим (слепок с данных), в то время как по умолчанию курсор создаётся динамическим (выборка осуществляется каждый раз при обращении к строке). Ключевое слово SCROLL означает, что курсор можно прокручивать в любом направлении, иначе курсор создаётся «последовательным».

Открытие курсора : OPEN [ GLOBAL ] <имя курсора>. Курсор, указанный как GLOBAL , не удаляется автоматически при завершении работы той процедуры или пакета, из которых он был вызван.

Считывание данных : FETCH [[ NEXT | PRIOR | FIRST | LAST | ABSOLUTE n | RELATIVE n ] FROM ] [ GLOBAL ] <имя курсора> [ INTO @ variable _ name , …]. SQL Server 2000 позволяет считывать из курсора всего одну строку . Ключевое слово FIRST – возвратить первую строку курсора; LAST – последнюю строку курсора; NEXT – следующую строку за текущей, возвращённая строка становится текущей; PRIOR – предыдущую перед текущей; ABSOLUTE n – возвращает строку по её абсолютному порядковому номеру в курсоре; RELATIVE – через n строк после текущей. Данные столбцов будут сохраняться в каждую из указанных переменных в порядке их перечисления.

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

Удаление данных : выполняет команда DELETE с синтаксисом, предназначенным для работы с курсорами.

Закрытие курсора : CLOSE [ GLOBAL ] <имя курсора>

Освобождение курсора : DEALLOCATE [ GLOBAL ] <имя курсора>

Пример использования курсора :

DECLARE fo_curs CURSOR STATIC FOR

SELECT name_rus from fo ORDER BY name_rus

DECLARE @name varchar(50)

OPEN fo_curs

FETCH FIRST FROM fo_curs INTO @name

WHILE @@FETCH_STATUS=0

BEGIN

PRINT @name

FETCH NEXT FROM fo_curs INTO @name

END

CLOSE fo_curs

DEALLOCATE fo_curs

Хранимые процедуры ( Stored Procedures ):

являются основным механизмом, с помощью которого регулируются вся работа с базами данных на сервере. Хранимая процедура – это именованный набор команд TSQL , хранящийся непосредственно на сервере и представляющий собой самостоятельный объект базы данных.

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

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

Создание хранимой процедуры:

CREATE PROC [ EDURE ] <имя> [;<число>] [{@<переменная-параметр> <тип данных>} [ VARYING ] [= <значение по умолчанию>] [ OUTPUT ] ,…] [ WITH { RECOMPILE | ENCRIPTION }] AS <набор выполняемых SQL -инструкций> [ RETURN ]

Здесь <число> – возможность указать идентификационный номер хранимой процедуры; OUTPUT – наличие этого ключевого слова будет означать, что соответствующий параметр предназначается для возвращения данных из хранимой процедуры (при этом параметр может быть использован и для передачи данных в хранимую процедуру); VARYING – ключевое слово, которое используется совместно с параметром OUTPUT , имеющим тип данных Cursor , которое определяет, что в качестве выходного параметра будет представлено результирующее множество; RECOMPILE – план выполнения процедуры создаётся при каждом её вызове. ENCRIPTION – выполняется кодирование хранимой процедуры при записи в системные таблицы. Для возврата из хранимой процедуры можно использовать команду RETURN .

Запуск хранимой процедуры осуществляется указанием её имени и, если необходимо, со списком значений параметров. При вызове процедуры из тела другой процедуры используется следующий синтаксис: EXEC [ UTE ] <имя процедуры> {[[@<имя параметра> =] <значение> | @<имя переменной> [ OUTPUT ] | DEFAULT ],…}.

Пример пакета :

-- описываем хранимую процедуру

CREATE PROCEDURE MyProc

@lastname char(64), @firstname char(64) AS

SELECT a.au_lname, a.au_fname, t.title

FROM authors a, titles t, titleauthors ta

WHERE a.au_lname = @lastname AND a.au_fname = @firstname

AND a.au_id = ta.au_id AND t.title_id = ta.title_id

-- создаём хранимую процедуру

GO

-- вызываем только что созданную хранимую процедуру

MyProg 'Иван','Бездомный'

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

Процедура как результат своей работы может возвратить (1) выборку из таблиц ( RecordSet ), (2) значения параметров, заданных как OUTPUT , (3) код завершения, который может генерироваться командой RETURN n . Просмотр кода процедуры выполняется с помощью системной процедуры sp _ helptext , а контрольную информацию о ней можно вывести с помощью процедуры sp _ help <имя процедуры>.

Удаление хранимой процедуры осуществляется командой DROP PROCEDURE <имя процедуры>, …

Изменение имени хранимой процедуры осуществляется системной процедурой sp _ rename .

Для модификации хранимой процедуры используется команда ALTER PROCEDURE <имя процедуры>. Фактически эта команда аналогична CREATE PROCEDURE , только сделанные ранее административные разрешения сохраняются. Для редактирования хранимой процедуры лучше использовать средства, предоставляемые центром управления MS Access Enterprise Manager .

Триггеры ( Triggers ):

Триггером в SQL Server называется специальная хранимая процедура, привязанная к конкретной таблице (представлению) и запускаемая сервером автоматически при обращении к этой таблице. Когда пользователь, например, успешно изменил данные в таблице, сервер автоматически запускает триггер, причём если произойдёт откат триггера, то это повлечёт и отмену пользовательских изменений данных. Триггеры могут использоваться (1) для нестандартного контроля целостности данных, (2) для вычисления значений в полях таблицы по значениям других полей, (3) для ограничения действий различных групп пользователей. Существует три типа триггеров – соответственно для команд INSERT , UPDATE и DELETE . Внутри триггера допускается использование практически любых команд TSQL , включая вызовы хранимых процедур и обращение к функциям пользователя.

Пример: триггер для таблицы authors , который будет запрещать вставку новых строк в таблицу, выдавая при этом сообщение «Вставка строк запрещена».

CREATE TRIGGER auth_tr ON authors

FOR INSERT AS

PRINT ” Вставка строк запрещена ”

ROLLBACK TRAN

Команды ALTER TRIGGER и DROP TRIGGER используются соответственно для изменения и удаления триггера

Пользовательские функции ( User - defined functions ):

появились только в SQL Server 2000.

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

Функции не могут возвращать данные непосредственно клиенту, как это может делать хранимая процедура. То есть, не разрешается использование в теле функции команды PRINT , а также команды SELECT для непосредственного возвращения данных.

Пример создания и вызова функции вида inline (всегда возвращающей RecordSet ):

CREATE FUNCTION MyFunc ( @State char(2) )

RETURNS TABLE AS

RETURN SELECT au_id, au_lname, au_fname

FROM authors

WHERE state = @state

GO

SELECT * FROM MyFunc('CA') ORDER BY au_lname, au_fname

Команды ALTER FUNCTION и DROP FUNCTION используются соответственно для изменения и удаления триггера

Правила ( Rules ):

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

Пример: создание правила, выполняющего проверку 15 <= x <= 75 :

CREATE RULE MyRule AS @exp BETWEEN 15 AND 75

-- свяжем это правило со столбцом price таблицы titles

sp_bindrule MyRule 'titles.price'

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

Для освобождения столбца от правила используется хранимая процедура sp _ unbindrule <имя объекта> . Для удаления правила используется команда DROP RULE . Перед удалением правило должно быть освобождено от всех столбцов.

Умолчания ( Default ):

Умолчание – механизм, задающий значение для столбца в случае, если оно не было определено при вставке строки. В качестве значения по умолчанию может быть указана константа или значение, возвращаемое функцией. Подобно правилам, умолчания являются самостоятельными объектами базы данных.

Пример создания умолчания и связывания его со столбцом:

CREATE DEFAULT MyDef AS ' неизвестно '

sp_bindefault MyDef 'titles.title'

Тип данных значения по умолчанию должен совпадать с типом данных столбца или должно быть возможным неявное преобразование значение к типу данных столбца. Для освобождения столбца от умолчания используется хранимая процедура sp _ unbindefault <имя объекта> . Для удаления умолчания используется команда DROP DEFAULT .

Ограничения целостности ( Constraints ):

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

Частично ограничения целостности были рассмотрены ранее для команд CREATE TABLE и ALTER TABLE . В этих командах после (необязательного) ключевого слова CONSTRAINT для отдельного столбца или таблицы в целом могут идти следующие типы ограничений:

• NULL | NOT NULL
• PRIMARY KEY
• UNIQUE
• FOREIGN KEY … REFERENCES
• ON DELETE
• ON UPDATE
• CHECK <логическое выражение> – контроль вводимых значений каким-либо логическим выражением.

Пример :

CREATE TABLE publichers (

pub_id int NOT NULL PRIMARY KEY,

pub_name varchar(40) DEFAULT (' неизвестно ')

CHECK (pub_id LIKE '99[0-9][0-9]')

)