EXEC @имя_переменной=хранимая процедура [, параметр [, параметр…]]
Выполнить ХП с входным параметром
Use aromatherapy
Go
Declare @count int
Exec sp_table_validation
‘oils’,
@count OUTPUT,@full_or_fast=0
Создание ХП
CREATE PROCEDURE имя_процедуры
[список_параметров]
AS
Орераторы_процедуры
Каждый из параметров в [список_параметров] имеет структуру:
@имя_параметра тип_данных [= значение по умолчанию] [OUTPUT]
Пример ХП со значением по умолчанию:
Пример1:
CREATE PROCEDURE fam
@tab int=100
AS
SELECT tn,f,ok FROM bd
WHERE tn = @tab
Выполнение:
Use проба
Go
EXEC fam 18
Пример2:
USE проба
GO
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'p5' AND type = 'P')
DROP PROCEDURE p5
GO
CREATE PROCEDURE p5
@a1 DEC(7,2)=10,
@as CHAR(15)='см'
AS
PRINT 'Площадь круга при радиусе равном'+
STR(@a1,7,2)+' '+@as
PRINT 'равняется '+STR(@a1*@a1*3.14,8,3)+' '+@as+'^2'
Выполнение:
EXEC p5 1,'М'
Пример ХП с выходным параметром:
CREATE PROCEDURE tt
@a1 char(6)OUTPUT
AS
SET @a1 = '12345'
Выполнение:
declare @a2 char(6)
exec tt @a2 output
select @a2
ХП с возвращаемым значением:
CREATE PROCEDURE tt
AS
DECLARE @dd int
SELECT ot,tn, FROM bd
-- захват ошибки
SET @dd = @@ERROR
RETURN(@dd)
Выполнение:
declare @a1 int
exec @a1=tt
select @a1 as 'код выполнения'
Операторы
Операторы присваивания
DECLARE @v1 datetime, @v2 datetime
SET @v1=Getdate()
SET @v2=@v1+1.5
SELECT ' нач. знач. ' =@v1, ' конеч. знач. ' =@v2
Конкатенация
DECLARE @v1 char(15), @v2 varchar(15)
SET @v1= ' Иванов '
SET @v2= ' Петров '
SELECT @v1+ ' и ' +@v2 ' + ' - студенты '
Операторы сравнения
=, >, <, <=, >=, <> или!=,!<,!>
IF Datepart(hh, Getdate() > 12 SELECT ‘ Вторая половина дня‘
ELSE SELECT ‘Первая половина дня’
Операторы логические
NOT, AND, OR, BETWEEN, ALL, IN, EXISTS, ANY, SOME, LIKE
ALL – сравнивает скалярное значение со всеми значениями в наборе, если
условие выполняется для всех значений набора – то TRUE
IF (' Ищенко В.А. ' <>ALL (SELECT f FROM bd))
IF (' Moskow ' <> ALL (SELECT f FROM bd))
PRINT ' нет в списке '
ELSE PRINT 'Имеется в списке Ищенко В.А. '
ANY, SOME – если условия выполняются хотя бы для одного значения
набора
IF (' Ищенко В.А. ' =ANY (SELECT f FROM bd))
PRINT 'Имеется в списке Ищенко В.А. '
ELSE PRINT ' нет в списке '
IN – совпадает выражение хотя бы с одним значением набора (аналог ANY), но позволяет использовать не только скалярные выр. но и др. или служит для ограничения выборки
IF (' Moskow ' IN (SELECT city FROM authors))…
…WHERE ot in (21,15,200)
EXISTS – TRUE, если подзапрос возвращает хотя бы строку, в отличии от
ANY можно задавать множество условий
IF EXISTS (SELECT * FROM bd WHERE f= ' Ищенко В.А. ' OR …)
PRINT 'Имеется в списке Ищенко В.А. '
ELSE PRINT ' нет в списке '
LIKE – поиск значений по заданному шаблону
% - любое количество символов
_ - подставлен один любой символ
[…] - можно подставить любой символ из указанного набора
^ - допускаются символы не входящие в набор
… WHERE city LIKE ' _Sa% '
… WHERE city LIKE ' _[^a-gh]% '
IF EXISTS (SELECT * FROM bd WHERE f LIKE ' Ищенко % ')
PRINT 'Имеется в списке Ищенко '
ELSE PRINT ' нет в списке '
ТРИГГЕРЫ
Триггер – особый вид хранимой процедуры, которую SQL Server выполняет автоматически при использовании команд INSERT, UPDATE, DELETE.
Существуют два вида триггеров: AFTER, INSTEAD OF.
Триггер AFTER
Вызывается после выполнения команды и работает только с таблицами. Для каждой команды может быть создано несколько триггеров и каждый триггер может применен
для нескольких команд.
Триггер INSTEAD OF
Заменяет команду, для которой он объявлен. Для каждой команды может быть создано несколько триггеров и каждый триггер может применен
для нескольких команд. Триггер создается для таблиц и представлений. Для каждого действия над таблицей или представления может быть создан только один триггер.
Создание триггеров
CREATE TRIGGER имя_триггера
ON
{table | view [WITH ENCRYPTION]
[{AFTER | INSDEAD OF}]
{[DELETE], [INSERT], [UPDATE]}
AS
[{IF UPDATE (colum)
[{AND | OR} UPDATE (column)]
[…n] | IF (columns_update()
{ bitwise_operator} updated_betmask}
(comparison_operator} column_betmask
[…n]
sql_statement […n]}}
где:
table | view – имя таблицы или представления к которой прикрепляется триггер
WITH ENCRYPTION – шифровка кода текста триггера
{[DELETE], [INSERT], [UPDATE]}- cоздаваемый триггер будет вызываться при попытке удаления, вставки, изменения данных в указанной таблице
AS – заканчивается определение триггера
IF UPDATE (colum)- разрешает выполнение триггера только при изменении в определенном столбце. Если изменяются несколько столбцов то: UPDATE (a1) OR
UPDATE (a2) AND UPDATE (a3)
IF (columns_update() – возвращает двоичное значение, каждый бит которого соответствует столбцу. Если есть изменение в столбцах, то в этих битах единицы, иначе ноль. Правый бит соответствует левому столбцу таблицы.
Парамеры, применяемые для проверки изменения столбцов:
bitwise_operator – подразумевает указание оператора побайтовой обработки & (побайтовое AND). Можно ограниченно использовать и др. побайтовые операторы.
updated_betmask – подразумевает указание константы, определяющей маску для анализа столбцов.
Пример: (columns_update() & 9) Маска для 1 и 4 столбцов
comparison_operator – после побитовой обработки проверяется полученный результат, т.е. ставятся оператор =,<,> и т.д.
column_betmask – указывается битовая маска, с которой сравнивается полученное значение. Триггер выполняется, если условие справедливо.
Пример: IF (columns_updated() & 9) = 9 если меняются 1 и 4 столбцы
IF (columns_updated() & 9) > 0 если меняется 1 или 4 столбец или оба
IF (columns_updated() & 9) < 9 если меняется 1 или 4 столбец
[…n] – можно выполнять множество проверок, какие столбцы были изменены.
sql_statement […n] – команды tsql, выполняемые при вызове триггера
Удаление триггеров
DROP TRIGGERS {trigger} [,…n]
Пример:
Создадим таблицу:
CREATE TABLE Список
(US char(5), DAT varchar(30), com varchar(30)
