Drop procedure {имя_процедуры} [,...N]; Выполнение хранимой процедуры

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

EXEC [ UTE] имя_процедуры [;номер]

[[@имя_параметра=]{значение | @имя_переменной}

[OUTPUT ]|[DEFAULT ]][,...n]

Если вызов хранимой процедуры не является единственной командой в пакете, то присутствие команды EXECUTE обязательно. Более того, эта команда требуется для вызова процедуры из тела другой процедуры или триггера.

Использование ключевого слова OUTPUT при вызове процедуры разрешается только для параметров, которые были объявлены при создании процедуры с ключевым словом OUTPUT.

Если при вызове процедуры для параметра указывается ключевое слово DEFAULT, то будет использовано значение по умолчанию. Естественно, указанное слово DEFAULT разрешается только для тех параметров, для которых определено значение по умолчанию.

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

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

Пример 1. Процедура без параметров. Создать процедуру для уменьшения размера стипендии на 10 %:

CREATE Procedure Reduce

AS

UPDATE Students SET Stipendiya = Stipendiya * 0.9

WHERE Stipendiya IS NOT NULL;

Для обращения к процедуре можно использовать команду:

EXECUTE Reduce;

Пример 2. Процедура с входными параметрами. Создать процедуру для выдачи списка студентов, получивших определенную оценку по определенному экзамену:

CREATE Procedure Subject

@Subject varchar(50), @Mark tinyint

AS

SELECT s.FIO AS 'ФИО', l.Nazvanie AS 'Дисциплина', p.Examen AS 'Оценка за экзамен'

FROM Students AS s

INNER JOIN

Progress AS p ON p.ID_Student = s.ID_Student

INNER JOIN

Lessons AS l ON l.ID_Lesson = p.ID_Lesson

WHERE l.Nazvanie = @Subject AND p.Examen = @Mark;

Для обращения к процедуре можно использовать команду:

EXEC Subject 'Объектно-ориентированное программиров­ание', 5;

Пример 3. Процедура с входными параметрами и значениями по умолчанию. Создать процедуру для выдачи списка студентов, получивших определенную оценку по определенному экзамену. По умолчанию вывести фамилии студентов, получивших оценку «3» по дисциплине «Алгебра и геометрия»:

CREATE Procedure ExamResultsDef

@Subject varchar(50)= VARYING ‘Алгебра и геометрия’,

@Mark tinyint = 3

AS

SELECT s.FIO AS 'ФИО', l.Nazvanie AS 'Дисциплина', p.Examen AS 'Оценка за экзамен'

FROM Students AS s INNER JOIN

Progress AS p ON p.ID_Student = s.ID_Student INNER JOIN

Lessons AS l ON l.ID_Lesson = p.ID_Lesson

WHERE (@Subject IS NOT NULL AND l.Nazvanie = @Subject AND p.Examen = @Mark) OR

(@Subject IS NULL AND p.Examen = @Mark);

Для обращения к процедуре можно использовать команды:

1. EXEC ExamResultsDef – в этом случае выводятся значения по умолчанию, т.е. заданные в процедуре, – оценка «3» и дисциплина «Алгебра и геометрия».

2. EXEC ExamResultsDef @Subject = 'Объектно-ориент­иро­ванное программирование', @Mark =5 – в этом случае выводится список студентов, получивших оценку «5» по дисциплине «Объектно-ориентированное программирование».

3. EXEC ExamResultsDef @Subject = 'Объектно-ориенти­ро­ванное программирование' – выводится список студентов, полу­чив­ших оценку «3» по дисциплине «Объектно-ориентированное программирование».

4. EXEC ExamResultsDef @Mark = 5 выводится список сту­дентов, получивших оценку «5» по дисциплине «Алгебра и гео­метрия».

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

Сначала разработаем процедуру для определения групп и их кураторов:

CREATE PROCEDURE Curator

@Grp VARCHAR(10),

@Srn VARCHAR(20) OUTPUT

AS

SELECT @Srn = Familia

FROM Teachers

INNER JOIN Groups ON Groups.Kurator = Teachers.ID_Teacher

WHERE Groups.Name_group = @Grp;

Затем создадим процедуру, определяющую студентов и их кураторов:

CREATE PROCEDURE StudentsCurator

@FIO VARCHAR(70),

@Crtr VARCHAR(20) OUTPUT

AS

DECLARE @GNm VARCHAR(10)

SELECT @GNm = Name_group

FROM Students s

INNER JOIN Groups g ON g.ID_Group = s.ID_Group

WHERE s.FIO = @FIO

EXEC Curator @GNm,@Crtr OUTPUT

Вызов процедуры осуществляется следующим образом:

DECLARE @Crtr VARCHAR(20)

EXECUTE StudentsCurator 'Иванов А.С.', @Crtr OUTPUT

PRINT @Crtr;