Объекты баз данных и работа с ними.

Временные таблицы (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):

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

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

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

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

Создание курсора: 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]’)

)