Хранимые процедуры

Разбить на страницы

Показывать лекцию целиком

Цель: научиться работать с хранимыми процедурами

Перейдем к созданию хранимых процедур. Для работы с хранимыми процедурами в обозревателе объектов необходимо выделить папку ).

(рис 10.1)

Создадим процедуру, вычисляющую среднее трех чисел. Для создания новой хранимой процедуры щелкните ) и в появившемся меню выберите пункт ).

(рис 10.2)

Хранимая процедура имеет следующую структуру (рис 10.2):

Область настройки параметров синтаксиса процедуры. Позволяет настраивать некоторые синтаксические правила, используемые при наборе кода процедуры. В нашем случае это:

SET ANSI_NULLS ON - включает использование значений NULL (Пусто) в кодировке ANSI,

SET QUOTED_IDENTIFIER ON - включает возможность использования двойных кавычек для определения идентификаторов;

Область определения имени процедуры ( Procedure_Name ) и параметров передаваемых в процедуру ( @Param1, @Param2 ). Определение параметров имеет следующий синтаксис:

@<Имя параметра> <Тип данных> = <Значение по умолчанию>

Параметры разделяются между собой запятыми;

Начало тела процедуры, обозначается служебным словом "BEGIN" ;

Тело процедуры, содержит команды языка программирования запросов T-SQL;

Конец тела процедуры, обозначается служебным словом "END".

Замечание: В коде зеленым цветом выделяются комментарии. Они не обрабатываются сервером и выполняют функцию пояснений к коду. Строки комментариев начинаются с подстроки "--". Далее в коде, мы не будем отображать комментарии, они будут свернуты. Слева от раздела с комментариями будет стоять знак "+", щелкнув по которому можно развернуть комментарий.

Наберем код процедуры вычисляющей среднее трех чисел, как это показано на рис 10.3.

(рис 10.3)

Рассмотрим код данной процедуры более подробно (рис 10.3):

CREATE PROCEDURE [Среднее трех величин] - определяет имя создаваемой процедуры как "Среднее трех величин";

@Value1 Real = 0, @Value2 Real = 0, @Value3 Real = 0 - определяют три параметра процедуры Value1, Value2 и Value3. Данным параметрам можно присвоить дробные числа (Тип данных Real), значения по умолчанию равны 0;

SELECT 'Среднее значение'=(@Value1+@Value2+@Value3)/3 - вычисляет среднее и выводит результат с подписью "Среднее значение".

Остальные фрагменты кода рассмотрены выше (рис 10.2).

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

Проверим работоспособность созданной хранимой процедуры. Для запуска хранимой процедуры необходимо создать новый пустой запрос, нажав на кнопку(Новый запрос) на панели инструментов. В появившемся окне с пустым запросом наберите команду на панели инструментов (рис 10.4).

(рис 10.4)

В нижней части окна с кодом появится результат выполнения новой хранимой процедуры: ).

Теперь создадим хранимую процедуру для отбора студентов из таблицы студенты по их "ФИО". Для этого создайте новую хранимую процедуру, как это описано выше, и наберите код новой процедуры как на рис 10.5.

(рис 10.5)

Рассмотрим код процедуры ):

CREATE PROCEDURE [Отображение студентов по ФИО] - определяет имя создаваемой процедуры как "Отображение студентов по ФИО";

@FIO Varchar(50)='' - определяют единственный параметр процедуры FIO. Параметру можно присвоить текстовые строки переменной длины, длинной до 50 символов (Тип данных Varchar(50)), значения по умолчанию равны пустой строке;

SELECT * FROM dbo.Студенты WHERE ФИО=@FIO - отобразить все поля (*) из таблицы студенты (dbo.Студенты), где значение поля ФИО равно значению параметра FIO (ФИО=@FIO).

Выполним вышеописанный код и закроем окно с кодом, как описано выше.

Проверим работоспособность созданной хранимой процедуры. Создайте новый пустой запрос. В появившемся окне с пустым запросом наберите команду на панели инструментов (рис 10.6).

(рис 10.6)

В нижней части окна с кодом появится результат выполнения хранимой процедуры ).

Теперь перейдем к более сложной задаче - отобразить студентов, у которых средний балл выше заданного. Создайте новую хранимую процедуру и наберите код новой процедуры как на рис 10.7.

(рис 10.7)

Рассмотрим код процедуры ):

CREATE PROCEDURE [Отображение студентов по среднему баллу] - определяет имя создаваемой процедуры как "Отображение студентов по среднему баллу";

@Grade Real=0 - определяют параметр процедуры Grade. Параметру можно присвоить дробные числа (Тип данных Real), значения по умолчанию равны 0;

SELECT * FROM [Запрос Студенты+Оценки] 	WHERE ([Оценка первого экзамена]+[Оценка второго экзамена]+[Оценка третьего экзамена])/3>@Grade

- отобразить все поля (*) из запроса "Запрос Студенты+Оценки" (Запрос Студенты+Оценки), где средний балл больше чем значение параметра Grade (([Оценка первого экзамена]+[Оценка второго экзамена]+[Оценка третьего экзамена])/3>@Grade).

Выполним вышеописанный код и закроем окно с кодом, как описано выше. Проверим, как работает запрос, описанный выше. Для этого, создайте новый запрос и в нем наберите команду ).

(рис 10.8)

В нижней части окна с кодом появиться результат выполнения хранимой процедуры ).

В заключение решим более сложную задачу - отображение студентов старше заданного возраста. При чем возраст будет автоматически вычисляться в зависимости от даты рождения.

Создадим новую хранимую процедуру и наберем код новой процедуры как представлено на рис 10.9.

(рис 10.9)

Рассмотрим код создаваемой процедуры ):

CREATE PROCEDURE [Отображение студентов по возрасту] - определяет имя создаваемой процедуры как "Отображение студентов по возрасту";

@Age int=0 - определяют параметр процедуры Age. Параметру можно присвоить целые числа (Тип данных int), значения по умолчанию равны 0;

ФИО, [Запрос Студенты+Специальности].[Дата рождения], 'Возраст'=DATEDIFF(yy,[Запрос Студенты+Специальности].[Дата рождения], GETDATE())

- отображает из запроса "Запроса Студенты+Специальности" (FROM [Запрос Студенты+Специальности]) поля "ФИО" (ФИО) и "Дата рождения" ([Запрос Студенты+Специальности].[Дата рождения]), а также отображает возраст студента ( 'Возраст' ) в годах (yy), вычисленный исходя из его даты рождения и текущей даты (DATEDIFF(yy,[Запрос Студенты+Специальности].[Дата рождения], GETDATE())). Более того, выводятся студенты возраст которых больше определенного в параметре "Age" (DATEDIFF(yy,[Запрос Студенты+Специальности].[Дата рождения], GETDATE())>@Age).

Замечание: Встроенная функция DATEDIFF вычисляющая количество периодов между двумя датами, имеет следующий синтаксис: DATEDIFF(<период>,<начальная дата>, <конечная дата>)

Выполним код запроса "Отображение студентов по возрасту", а затем закроем окно с кодом, как описано выше. Проверим, как работает запрос. Для этого, создадим новый запрос и в нем наберем команду .

(рис 10.10)

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

(рис 10.11)

Страницы:

Цель: научиться работать с хранимыми процедурами

(рис 10.1)

(рис 10.2)

Хранимая процедура имеет следующую структуру (рис 10.2):

SET ANSI_NULLS ON - включает использование значений NULL (Пусто) в кодировке ANSI,

SET QUOTED_IDENTIFIER ON - включает возможность использования двойных кавычек для определения идентификаторов;

@<Имя параметра> <Тип данных> = <Значение по умолчанию>

Параметры разделяются между собой запятыми;

Начало тела процедуры, обозначается служебным словом "BEGIN" ;

Тело процедуры, содержит команды языка программирования запросов T-SQL;

Конец тела процедуры, обозначается служебным словом "END".

Наберем код процедуры вычисляющей среднее трех чисел, как это показано на рис 10.3.

(рис 10.3)

Рассмотрим код данной процедуры более подробно (рис 10.3):

CREATE PROCEDURE [Среднее трех величин] - определяет имя создаваемой процедуры как "Среднее трех величин";

SELECT 'Среднее значение'=(@Value1+@Value2+@Value3)/3 - вычисляет среднее и выводит результат с подписью "Среднее значение".

Остальные фрагменты кода рассмотрены выше (рис 10.2).

(рис 10.4)

В нижней части окна с кодом появится результат выполнения новой хранимой процедуры: ).

(рис 10.5)

Рассмотрим код процедуры ):

Выполним вышеописанный код и закроем окно с кодом, как описано выше.

(рис 10.6)

В нижней части окна с кодом появится результат выполнения хранимой процедуры ).

(рис 10.7)

Рассмотрим код процедуры ):

SELECT * FROM [Запрос Студенты+Оценки] 	WHERE ([Оценка первого экзамена]+[Оценка второго экзамена]+[Оценка третьего экзамена])/3>@Grade

(рис 10.8)

В нижней части окна с кодом появиться результат выполнения хранимой процедуры ).

Создадим новую хранимую процедуру и наберем код новой процедуры как представлено на рис 10.9.

(рис 10.9)

Рассмотрим код создаваемой процедуры ):

ФИО, [Запрос Студенты+Специальности].[Дата рождения], 'Возраст'=DATEDIFF(yy,[Запрос Студенты+Специальности].[Дата рождения], GETDATE())

(рис 10.10)

(рис 10.11)

Занятие пройдено

Предыдущее занятие Занятие пройдено Следующее занятие

Вернуться к учебному плану

Купить дополнительную попытку

Вы действительно хотите купить ещё одну попытку для прохождения тестирования?

Успешно!

Напишите нам, если у вас есть вопросы

Уважаемые пользователи ИНТУИТ!

Уважаемые пользователи ИНТУИТ!

Добро пожаловать на обновлённый сайт НОУ «ИНТУИТ»!