Вопрос : Зачем нужны хранимые процедуры, и как ими пользоваться?
Ответ: 1. Зачем нужны хранимые процедуры.
Когда кто-либо говорит про трехзвенную архитектуру и сравнивает ее с клиент-серверной архитектурой, то это примерно выглядит следующим образом:
"трехзвенная архитектура" - есть хранилище данных (1-е звено), есть сервер приложений (2-е звено), который выбирает из этого хранилища данные и определенным образом эти данные обрабатывает и после обработки конечный результат уже посылает на терминал клиента (3-е звено).
"клиент-сервер" - есть хранилище данных (сервер) и клиент, который с этого сервера выбирает данные с помощью определенного языка запросов (SQL).
Возможно данное определение технологии "клиент-сервер" и было правильным, но только очень давно. Когда еще не было нормальных баз данных, а были СУБД (навроде FoxPro) со встроенной поддержкой sql-запросов.
Более правильное описание технологии "клиент-сервер" следующее:
Есть хранилище данных (1-е звено) и клиент (3-е звено), который с этого сервера выбирает данные с помощью определенного языка запросов (SQL), но еще есть сервер приложений (2-е звено), уже встроенный в базу данных, с помощью которого можно обрабатывать данные любыми известными реляционной алгебре способами и уже после этого передавать конечный результат на клиента.
Так что закатывать глаза и говорить, что "трехзвенка это круто, не то что клиент-сервер" мягко говоря глупо. Каждая из этих технологий имеет свой круг применения. Трехзвенная архитектура, как правило, нужна только в случаях выполнения ОЧЕНЬ сложных обработок данных на серверной части.
Хранимые процедуры как раз и выполняют роль сервера приложений. С их помощью с данными можно делать все. Для этого достаточно вызвать заранее написанный код в виде хранимой процедуры со стороны клиента.
Еще хранимые процедуры могут использоваться для ограничения доступа к базе данных. Например, можно запретить для пользователей доступ на добавление записей в таблицу, и выполнять добавление записей с помощью специальной хранимой процедуры, доступ к которой открыт для всех.
2. Как пользоваться хранимыми процедурами.
Если особо не распространяться на эту тему, то можно выделить два типа хранимых процедур: которые возвращают набор данных, и которые только выполняют какие-либо вычисления и ничего не возвращают.
Для примеров будем использовать таблицы, созданные с помощью следующего скрипта:
CREATE TABLE Master1
(
Master1ID int IDENTITY (1,1) NOT NULL,
Detail1ID int NULL,
Detail2ID int NULL,
Name varchar(200),
CONSTRAINT PK_Master1 PRIMARY KEY CLUSTERED
(Master1ID)
)
GO
CREATE TABLE Detail1
(
Detail1ID int IDENTITY (1,1) NOT NULL,
Name varchar(200),
CONSTRAINT PK_Detail1 PRIMARY KEY CLUSTERED
(Detail1ID)
)
GO
CREATE TABLE Detail2
(
Detail2ID int IDENTITY (1,1) NOT NULL,
Name varchar(200),
CONSTRAINT PK_Detail2 PRIMARY KEY CLUSTERED
(Detail2ID)
)
GO
insert Detail1 (Name) values('Рабочий')
insert Detail1 (Name) values('Инженер')
insert Detail1 (Name) values('Дворник')
insert Detail1 (Name) values('Программист')
GO
insert Detail2 (Name) values('высшее')
insert Detail2 (Name) values('среднее')
insert Detail2 (Name) values('неполное высшее')
insert Detail2 (Name) values('кандидат наук')
GO
insert Master1 (Detail1ID,Detail2ID,Name) values(1,1,'Иванов')
insert Master1 (Detail1ID,Detail2ID,Name) values(2,2,'Петров')
insert Master1 (Detail1ID,Detail2ID,Name) values(1,2,'Сидоров')
insert Master1 (Detail1ID,Detail2ID,Name) values(4,3,'Лаврененко')
insert Master1 (Detail1ID,Detail2ID,Name) values(null,1,'Кошкин')
insert Master1 (Detail1ID,Detail2ID,Name) values(3,null,'Самойлов')
GO
А. Процедура, возвращающая набор данных.
Создание процедуры:
CREATE PROCEDURE msp_List1 @ID int
AS
select a.Master1ID, a.Name, b.Name, c.Name
from Master1 a
left join Detail1 b on b.Detail1ID=a.Detail1ID
left join Detail2 c on c.Detail2ID=a.Detail2ID
where a.Master1ID=@ID
GO
Выполнение:
exec msp_List1 4
GO
Результат:
Master1ID Name Name Name
----------- -------------------- -------------------- --------------------
4 Лаврененко Программист неполное высшее
(1 row(s) affected)
Чтобы использовать набор данных, возвращаемый хранимой процедурой для обработки на сервере (в других процедурах и т.п.) можно вставить его во временную таблицу и уже с ней работать:
Master1ID Name1 Name2 Name3
----------- -------------------- -------------------- --------------------
1 Иванов Рабочий высшее
(1 row(s) affected)
Можно воспользоваться функцией OPENROWSET или OPENQUERY, но лично я не рекомендую, т.к. для этого надо писать пароли или создавать linked server.
B. Процедура, не возвращающая набор данных.
Мы не будем писать процедуру, которая вообще ничего не возвращает. Хотя такие процедуры тоже нужны. В них может быть заполнение каких-либо данных, изменение данных или еще что-либо похожее. Лучше написать процедуру с возвращаемым параметром.
Создание процедуры:
CREATE PROCEDURE msp_List2 @ID int, @Name varchar(200) OUTPUT
AS
select @Name=Name
from Master1
where Master1ID=@ID
GO
Можно возвращать несколько переменных за раз. В качестве возвращаемой переменной может быть CURSOR (начиная с MSSQL2000).
В MSSQL2000 появились функции, которые могут возвращать результат так, как это делают привычные функции, навроде getdate(), или набор данных. При этом вызов функции может быть прямо в секции from. Например,
select * from MyFunc() where MyField=@something
В некоторых случаях использование пользовательских функций гораздо удобнее.
На этом закончим. Удалить созданные объекты можно с помощью следующего кода:
DROP PROCEDURE msp_List1
DROP PROCEDURE msp_List2
DROP TABLE Master1
DROP TABLE Detail1
DROP TABLE Detail2
GO
Надеюсь, Вы нашли эту информацию полезной. Вопросы, предложения и пожелания шлите на адрес sql@likor.ru С уважением, Сергей Кошкин.
