Лабораторные работы / лабы задания / db_lab_08_of_12_ivt-4

Лабораторная работа №8

Представления, хранимые процедуры, функции в SQL Server

Цель: работы: получить навыки работы с представлениями, хранимыми процедурами и функциями в MS SQL Server.

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

Далее следует краткий конспект теоретического материала.

Теоретические сведения *

SQL Server позволяет создавать в БД не только таблицы, в которых хранятся данные, но и многочисленные вспомогательные объекты.

Представление VIEW – именованная динамически поддерживаемая сервером выборка из одной или нескольких таблиц (виртуальная таблица). Представление – это хранимый оператор SELECT, который выполняется при каждом обращении к Представлению.

Хранимая процедура STORED PROCEDURE – именованный набор конструкций, решающий конкретную задачу.

Функция FUNCTION – именованный набор конструкций языка, решающий конкретную задачу и возвращающий результат. Оператор RETURN.

Представления

Синтаксис:

{CREATE| ALTER} VIEW имя_представления

[(имя_столбца [,...n])] [WITH ENCRYPTION] AS SELECT_оператор [WITH CHECK OPTION]

Примеры:

Показать в представлении клиентов из Москвы. Создание представления:

CREATE VIEW view1 AS

SELECT КодКлиента, Фамилия, ГородКлиента

FROM Клиент

WHERE ГородКлиента='Москва‘

Выборка данных из представления:

SELECT * FROM view1

Модификация представления:

INSERT INTO view1 VALUES (12,'Петров', 'Самара')

Но данные уже могут быть в таблице. Чтобы не возникло ошибок:

ALTER VIEW view1

SELECT КодКлиента, Фамилия, ГородКлиента

FROM Клиент

WHERE ГородКлиента='Москва‘

WITH CHECK OPTION

Модифицируемое представление определяется следующими критериями:

-основывается только на одной базовой таблице;

-содержит первичный ключ этой таблицы;

-не содержит DISTINCT в своем определении;

-не использует GROUP BY или HAVING в своем определении;

-по возможности не применяет в своем определении подзапросы;

-не использует константы или выражения значений среди выбранных полей вывода;

- в просмотр должен быть включен каждый столбец таблицы, имеющий атрибут NOT NULL ;

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

-основывается на одиночном запросе, поэтому объединение UNION не разрешено.

Пользовательские функции

Скалярные (scalar) функции возвращают скалярное значение, могут включать множество команд, объединяемых в один блок с помощью конструкции BEGIN...END;

Синтаксис:

{CREATE | ALTER } FUNCTION [владелец.]

имя_функции

( [ { @имя_параметра скаляр_тип_данных [=default] } [,...n] ] ) RETURNS скаляр_тип_данных

[WITH {ENCRYPTION | SCHEMABINDING} [,...n] ] [AS]

BEGIN

<тело_функции>

RETURN скаляр_выражение

END

ENCRYPTION – тело функции будет недоступно для открытого просмотра

SCHEMABINDING – запрет функции вносить изменения в БД

Пример:

Функция скалярного типа для вычисления суммарного количества товара, поступившего за определенную дату:

CREATE FUNCTION user1.sales(@data DATETIME)

RETURNS INT AS

BEGIN

DECLARE @c INT

SET @c=(SELECT SUM(количество) FROM Сделка

WHERE дата=@data

)

RETURN(@c) END

Встраиваемые (inline) функции содержат одну команду SELECT и возвращают набор данных в виде таблицы;

Синтаксис:

{CREATE | ALTER} FUNCTION [владелец.]

имя_функции

( [ { @имя_параметра скаляр_тип_данных [=default] } [,...n] ] )

RETURNS TABLE

[ WITH {ENCRYPTION | SCHEMABINDING} [,...n] ] [AS]

RETURN [(] SELECT_оператор [)]

Пример: функция для определения двух наименований товара с наибольшим остатком:

CREATE FUNCTION user1.itog() RETURNS TABLE

AS

RETURN (SELECT TOP 2 Товар.Название FROM Товар INNER JOIN Склад

ON Товар.КодТовара=Склад.КодТовара

ORDER BY Склад.Остаток DESC

)

Многокомандные (multi-statement) функции также возвращают значение в виде таблицы, но в теле функции может находиться множество команд SQL (INSERT, UPDATE и др.)

Синтаксис:

{CREATE | ALTER} FUNCTION [владелец.]

имя_функции

( [ { @имя_параметра скаляр_тип_данных [=default] } [,...n] ] ) RETURNS @имя_параметра TABLE

<определение_таблицы>

[ WITH {ENCRYPTION | SCHEMABINDING} [,...n] ] [AS]

BEGIN

<тело_функции>

RETURN END

Пример: функция, которая для некоторого сотрудника выводит список всех его подчиненных (как непосредственно ему, так и через других сотрудников).

CREATE FUNCTION fn_findReports (@id_emp CHAR(20)) RETURNS @report TABLE (

empid CHAR(20) PRIMARY KEY, mgrid CHAR(20)

) AS BEGIN

DECLARE @r INT DECLARE @t TABLE (

empid CHAR(20) PRIMARY KEY, mgrid CHAR(20),

pr INT DEFAULT 0

)

INSERT @t

SELECT emp, mgr, 0 FROM emp_mgr WHERE emp=@id_emp

SET @r=@@ROWCOUNT WHILE @r>0

BEGIN

UPDATE @t SET pr=1 WHERE pr=0 INSERT @t

SELECT e.emp, e.mgr, 0 FROM emp_mgr e, @t t

WHERE e.mgr=t.empid AND t.pr=1 SET @r=@@ROWCOUNT

UPDATE @t SET pr=2 WHERE pr=1 END

INSERT @report

SELECT empid, mgrid FROM @t RETURN

END

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

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

Преимущества использования:

-необходимые операторы уже содержатся в БД;

-все они прошли этап синтаксического анализа и находятся в исполняемом формате;

-перед выполнением хранимой процедуры SQL Server генерирует для нее план исполнения,

выполняет оптимизацию;

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

-хранимые процедуры могут вызывать другие хранимые процедуры и функции;

-хранимые процедуры могут быть вызваны из прикладных программ других типов;

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

-уменьшение размера запроса, посылаемого от клиента на сервер;

-независимость от языка программирования клиента СУБД.

Системные хранимые процедуры предназначены для выполнения различных административных действий. Практически все действия по администрированию сервера выполняются с их помощью. Системные хранимые процедуры являются интерфейсом, обеспечивающим работу с системными таблицами, которая, в конечном счете, сводится к изменению, добавлению, удалению и выборке данных из системных таблиц как пользовательских, так и системных баз данных. Системные хранимые процедуры имеют префикс sp_, хранятся в системной базе данных и могут быть вызваны в контексте любой другой базы данных.

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

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

Локальные временные хранимые процедуры могут быть вызваны только из того соединения, в котором созданы. При создании такой процедуры ей необходимо дать имя, начинающееся с одного символа #. Как и все временные объекты, хранимые процедуры этого типа автоматически удаляются при отключении пользователя, перезапуске или остановке сервера.

Глобальные временные хранимые процедуры доступны для любых соединений сервера, на котором имеется такая же процедура. Для ее определения достаточно дать ей имя, начинающееся с символов ##. Удаляются эти процедуры при перезапуске или остановке сервера, а также при закрытии соединения, в контексте которого они были созданы.

Порядок создания процедуры:

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

Также, можно создать свою собственную системную хранимую процедуру, назначив ей имя с префиксом sp_ и поместив ее в системную базу данных. Такая процедура будет доступна в контексте любой базы данных локального сервера;

планирование прав доступа: хранимая процедура будет иметь те же права доступа к объектам базы данных, что и создавший ее пользователь;

Создание и изменение процедуры:

Синтаксис:

{CREATE | ALTER} [PROCEDURE] имя_процедуры [;номер]

[{@имя_параметра тип_данных } [VARYING] [=default][OUTPUT] ] [,...n] [WITH { RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION }]

AS

sql_оператор [...n]

VARYING применяется совместно с параметром OUTPUT, имеющим тип CURSOR, и определяет, что выходным параметром будет результирующее множество.

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

CREATE PROC my_proc @k VARCHAR(20)

AS SELECT

Товар.Название, Товар.Цена*Сделка.Количество AS Стоимость, Клиент.Фамилия

FROM Клиент INNER JOIN (

Товар INNER JOIN Сделка ON Товар.КодТовара=Сделка.КодТовара ) ON Клиент.КодКлиента=Сделка.КодКлиента

WHERE Клиент.Фамилия=@k

Синтаксис вызова процедуры:

[EXEC[UTE]] имя_процедуры [;номер] [ [@имя_параметра=]{значение | @имя_переменной}

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

Примеры для my_proc:

EXEC my_proc 'Иванов‘

или

my_proc @k='Иванов'

Пример:

Создание процедуры, обновляющей зарплату:

CREATE PROCEDURE Pr_test_1 @Emp_id int, @sal int

AS BEGIN

UPDATE Emp SET Salary=@sal WHERE ID_emp=@Emp_id; END

Вызов процедуры:

Pr_test_1 1,5050

Лабораторные задания

Задание 1. Базу данных, созданную в рамках Л.р. №7 дополнить представлениями, пользовательскими функциями и хранимыми процедурами в соответствии с информационными потребностями пользователей.

Задание 2. Проверить работоспособность созданных объектов. Результат занести в отчет.

Литература:

1. Материалы курса «Основы SQL», лекции 10-12, режим доступа: http://www.intuit.ru/studies/courses/5/5/info (* - при описании теоретических сведений использованы материалы данного источника)