ХП с выходными параметрами

EXEC @имя_переменной=хранимая процедура [, параметр [, параметр…]]

Выполнить ХП с входным параметром

Use aromatherapy

Declare @count int

Exec sp_table_validation

‘oils’,

@count OUTPUT,@full_or_fast=0

Создание ХП

CREATE PROCEDURE имя_процедуры

[список_параметров]

Орераторы_процедуры

Каждый из параметров в [список_параметров] имеет структуру:

@имя_параметра тип_данных [= значение по умолчанию] [OUTPUT]

Пример ХП со значением по умолчанию:

Пример1:

CREATE PROCEDURE fam

@tab int=100

SELECT tn,f,ok FROM bd

WHERE tn = @tab

Выполнение:

Use проба

EXEC fam 18

Пример2:

USE проба

IF EXISTS (SELECT name FROM sysobjects

WHERE name = 'p5' AND type = 'P')

DROP PROCEDURE p5

CREATE PROCEDURE p5

@a1 DEC(7,2)=10,

@as CHAR(15)='см'

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

SET @a1 = '12345'

Выполнение:

declare @a2 char(6)

exec tt @a2 output

select @a2

ХП с возвращаемым значением:

CREATE PROCEDURE tt

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 имя_триггера

{table | view [WITH ENCRYPTION]

[{AFTER | INSDEAD OF}]

{[DELETE] , [INSERT] , [UPDATE]}

[{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]

Пример: