Многие программисты, работающие с Microsoft SQL Server, предпочитают писать логику приложения в коде своих программ, используя sql server только как хранилище данных. Одна из причин такого подхода - обычная лень (например, при переносе базы данных с Access на SQL Server), другая же причина - мнение, что язык SQL не столь гибок, как Delphi, C++ и другие языки программирования. Но ведь он и не предназначен для работы с конечным пользователем! На самом деле - SQL предоставляет необходимый минимум возможностей для работы с данными; надо только ими правильно воспользоваться.
В этой статье я хочу поделиться некоторыми хитростями работы с Microsoft SQL Server, которые я использую в своих проектах. Сразу оговорюсь, что все скрипты написаны для MS SQL Server 7.0.
Передача таблицы значений в хранимую процедуру
Нередко возникает вопрос: "Как передать в хранимую процедуру массив (таблицу) значений?". Стандартные решения - вызов процедуры для каждого значения по отдельности, либо передача массива в виде строки с разделителями, которая потом разбирается хранимой процедурой. Очевидно, что в случае, когда размер массива значений велик, первый подход очень сложен для сервера (и его выполнение занимает очень много времени), второй же ограничен максимальной длиной типа varchar (8000 байт).
Однако, есть решение, свободное от этих недостатков. Можно создать временную таблицу, занести в нее массив значений - и выполнить хранимую процедуру, которая будет брать данные из созданной таблицы. Все команды выполняются в одной сессии. Единственный недостаток такого подхода очевиден - процедура должна точно знать названия таблицы и полей.
Допустим, нам надо выбрать все записи из таблицы authors базы pubs, у которых поле state равно одному из значений ['ca', 'mi', 'ks'].
Создадим процедуру dbo.testproc:
create procedure dbo.testproc
as
begin
select *
from pubs..authors a
inner join #params t on
a.state = t.state
end
go
Теперь для решения нашей задачи осталось написать следующий скрипт:
create table #params
(state varchar(2))
insert into #params(state)
values('ca')
insert into #params(state)
values('mi')
insert into #params(state)
values('ks')
exec dbo.testproc
drop table #params
Просто, не правда ли? Кстати, команду drop table #params можно не выполнять. Временная таблица #params будет автоматически удалена при завершении сессии.
Сравнение двух таблиц
Допустим, нужно сравнить две таблицы по условию 'И'. Например - выбрать издателей, выпускающих книги по темам "Психология"("psychology") и "Бизнес"("business"). Элементарно? Конечно; но что делать, если условий много и их количество неизвестно? Можно объявить переменную @count, в которой будет храниться количество совпадений (количество уникальных записей из таблицы, с которой мы сравниваем). Затем надо сравнить количество неодинаковых совпадений интересующих нас тем каждого издателя с @count. Нужные нам записи - те, которые удовлетворяют последнему условию. Попробуем написать скрипт:
/* Создадим и заполним временную таблицу #testtable, содержащую условия поиска. */
create table #testtable
(type varchar(15))
insert into #testtable
values('business')
insert into #testtable
values('psychology')
/* Объявим переменную @count. */
declare
@count int
/* И сохраним в ней количество неповторяющихся записей в таблице #test */
select
@count = count(distinct type) from #testtable
select
distinct p.pub_name
from #testtable inner join
titles t on
t.type = #testtable.type inner join
publishers p on t.pub_id = p.pub_id
group by
p.pub_name
having
count(distinct t.type) = @count
Этот прием вполне совместим с приемом, описанным в предыдущем пункте. Вместе они очень полезны для процедур поиска.
Выполнение динамического запроса при отсутствии прав на выборку данных из таблицы
Иногда бывают ситуации, когда проще сформировать условие where в клиентском приложении, а затем передать его как параметр в хранимую процедуру. В случае, когда существует разрешение на выборку данных из желаемой таблицы, решение будет тривиальным. Но что делать, если пользователь имеет право пользоваться только хранимыми процедурами?
Ответ достаточно прост. Процедура работает с базой данный с "суммарными" правами владельца и пользователя, который ее вызвал. Так, любая процедура, созданная пользователем dbo, имеет все права на базу данных. Но если в процедуре встречается оператор exec, то он выполняется от имени пользователя, вызвавшего процедуру. Если мы не дали достаточно прав на выполнение запроса, присутствующего в операторе exec - произойдет ошибка. Решение этой проблемы простое - выгрузить данные во временную таблицу, а затем вывести их в соответствии с нашими условиями:
create procedure dbo.testproc
(@where varchar(8000) = null)
/* Создадим процедуру dbo.testproc */
as
begin
set nocount on
/* Она будет производить выборку данных из таблицы dbo.authors
базы данных pubs, удовлетворяющих условиям, хранящимся в переменной @where */
/* Создадим таблицу #tmp. */
create table #tmp (
au_id varchar(11) not null ,
au_lname varchar(40) not null ,
au_fname varchar(20) not null ,
phone char(12),
address varchar(40) null ,
city varchar(20) null ,
state char(2) null ,
zip char(5) null ,
contract bit not null
)
/* Выгрузим данные из таблицы dbo.authors базы данных pubs в #tmp... */
insert into #tmp select * into #tmp from pubs..authors
/* ...и выполним выборку, удовлетворяющую нашим условиям. */
if (@while = '')
select * from #tmp
else
exec('select * from #tmp where ' + @where)
set nocount off
end
Теперь можно проверить работу процедуры. Допустим, у нас существует пользователь с именем manager. Запретим ему выбирать данные из таблицы dbo.authors:
deny select on dbo.authors to manager
И разрешим выполнение процедуры dbo.testproc.
grant execute on dbo.testproc to manager
Изменим текущего пользователя на manager.
setuser 'manager'
И выполним процедуру dbo.testproc
exec dbo.testproc 'au_lname like ''gree%'' and state in (''ca'', ''mi'')'
Использование базы данных tempdb
База данных tempdb существует для хранения временных объектов и создается заново при каждом запуске sql server'а. В ней же хранятся результаты выполнения сложных запросов и процедур. Здесь мне хотелось бы поговорить об использовании временных таблиц и временных процедур.
Достаточно часто программисты допускают ошибку, пользуясь конструкцией select into в сложных запросах с выгрузкой данных во временную таблицу. Проблема в том, что во время выполнения такого запроса база данных tempdb блокируется. Это разумно только в случае, когда точно известно, что выборка данных будет кратковременной. Надо учитывать и то, что некоторые таблицы, из которых происходит выборка данных, могут быть заблокированы другими процессами. Если же количество полей временной таблицы велико или неизвестно, то для ее создания лучше воспользоваться таким запросом:
select top 0 * into #temptable from ...
Причем запрос не должен включать в себя условий отбора и сортировки, а таблицы должны быть перечислены через запятую.
Перейдем к временным процедурам. К сожалению, microsoft только к середине 2000 года заметила огромное упущение в работе с временными хранимыми процедурами - они позволяют любому пользователю выполнить любую (даже запрещенную для него) хранимую процедуру. Давайте рассмотрим эту ситуацию на примере из предыдущей главы. Запретим пользователю с именем manager выполнение процедуры dbo.testproc:
deny execute on dbo.testproc to manager
Изменим текущего пользователя на manager.
setuser 'manager'
И выполним процедуру dbo.testproc.
exec dbo.testproc 'au_lname like ''gree%'' and state in (''ca'', ''mi'')'
В ответ мы получим:
server: msg 229, level 14, state 5, procedure testproc, line 1
execute permission denied on object 'testproc ', database 'pubs', owner 'dbo'.
Теперь создадим временную процедуру #testproc:
create procedure #testproc
(@where varchar(8000) = null)
as
begin
exec dbo.testproc @where
end
И выполним ее:
exec #testproc 'au_lname like ''gree%'' and state in (''ca'', ''mi'')'
Все прекрасно работает!
Как же обезопасить данные от таких ситуаций? Процедуре придется самой проверить, кто же ее запустил, и имеет ли он на это право. Допустим, у нас есть группа пользователей managers. Добавим в нее пользователя manager. Теперь с помощью функции is_member() узнаем, принадлежит ли пользователь к этой группе. Добавим в начало процедуры dbo.testproc строки:
if (is_member('managers') != 1)
begin
raiserror('Только пользователи, входящие в группу ''managers'' могут вызывать эту процедуру', 16, 1) end
Такую проверку следует включать в начало каждой процедуры.
Официальное решение этой проблемы см. на сервере microsoft по адресу:
http://www.microsoft.com/downloads/release.asp?releaseid=22470 (для платформы intel) и http://www.microsoft.com/downloads/release.asp?releaseid=22469 (для alpha).
Обработка результата работы хранимой процедуры
Часто бывают ситуации, когда одну большую процедуру хочется разделить на несколько маленьких; или в нескольких процедурах используется одинаковый код, который лучше вынести в отдельную процедуру. Но встает вопрос: как получить от нее данные о результате ее работы? Одного кода возврата зачастую бывает мало. Тем более интересен вопрос передачи возвращаемых процедурой данных. В books online, к сожалению, этот важный момент совершенно не рассмотрен. Тем не менее, эта возможность активно используется при создании базы данных master. Синтаксис такого запроса следующий: insert into таблица exec процедура.
Для примера создадим процедуру dbo.testproc, выбирающую из таблицы dbo.authors поля au_id, au_lname и au_fname и временную таблицу #testtable с полями au_id, au_lname и au_fname.
/* Создадим процедуру dbo.testproc */
create procedure dbo.testproc
as
begin
select au_id, au_lname, au_fname from dbo.authors
end
go
/* Создадим временную таблицу #testtable */
create table #testtable
(au_id varchar(20) not null,
au_lname varchar(40) not null,
au_fname varchar(20) not null)
go
/* И выполним вставку значений из хранимой процедуры dbo.testproc */
insert into #testtable exec dbo.testproc
go
/* Теперь можно посмотреть на результат */
select * from #testtable
Шифрование данных
В приложениях, работающих с sql server, часто требуется провести дополнительную идентификацию пользователя. Например, для выполнения какой-то ответственной операции надо проверить, может ли пользователь ее выполнить. Можно, конечно, попросить его ввести пароль. Но как этот пароль хранить? Прописывать его в коде самого приложения - по крайней мере глупо; хранить как открытый текст в какой-то таблице базы данных - тоже небезопасно. Остается один выход - шифровать.
К сожалению, корпорация microsoft не захотела документировать функции, которые позволяют шифровать данные. Этими функциями являются: encrypt(), pwdencrypt() и pwdcompare(). Функция encrypt(значение) возвращает тип varbinary(6). Функция pwdencrypt(значение) возвращает тип nvarchar(32). Функция pwdcompare({ проверяемое значение }, { зашифрованное значение }) сравнивает введенное значение с зашифрованным, где проверяемое значение - строка типа nvarchar, зашифрованное значение имеет тип varbinary(16). Возвращаемые значения - 0 (если не совпадает), 1 (если совпадает), null (если произошла ошибка). Различия функций encrypt() и pwdencrypt() в том, что encrypt() кодирует значения по единому алгоритму, а pwdencrypt() - по меняющемуся. То есть, выполнив несколько раз select encrypt('test') мы будем получать одно и то же значение, а выполнение select convert(varbinary(16), pwdencrypt('test')) дает все время разные значения. Значение, зашифрованное с помощью pwdencrypt() можно сравнить с проверяемым только с помощью функции pwdcompare(), а зашифрованное с помощью encrypt() - сравнивая его с зашифрованным проверяемым значением. Но у функции pwdcompare() есть один большой минус - если на сервере установлена регистро-независимая кодовая страница, то проверка также будет регистро-независимой. То есть пароли 'test' и 'test' будут признаны идентичными. Приходится выбирать - либо пользоваться мощным алгоритмом, предоставляемым функцией pwdencrypt(), либо пользоваться менее защищенной encrypt(). Приведу примеры использования этих функций:
/* Создадим таблицу dbo.testtable, в которой будут храниться имена и пароли пользователей */
create table dbo.testtable
( username varchar(30),
password varbinary(16))
/* Добавим пользователя 'user1' с паролем 'user1', зашифрованным функцией encrypt() */
insert into dbo.testtable
(username, password)
values('user1', encrypt('user1'))
/* …пользователя 'user1_1' с паролем 'user1', функция encrypt() */
insert into dbo.testtable
(username, password)
values('user1_1', encrypt('user1'))
/* 'user1_2', пароль 'user1', функция encrypt() */
insert into dbo.testtable
(username, password)
values('user1_2', encrypt('user1'))
/* 'user2', пароль 'user2', pwdencrypt() */
insert into dbo.testtable
(username, password)
values('user2', convert( varbinary, pwdencrypt('user2')))
/* 'user2_1', пароль 'user2', pwdencrypt() */
insert into dbo.testtable
(username, password)
values('user2_1', convert( varbinary, pwdencrypt('user2')))
/* 'user2_2', пароль 'user2', pwdencrypt() */
insert into dbo.testtable
(username, password)
values('user2_2', convert( varbinary, pwdencrypt('user2')))
/* А теперь - посмотрим на результат. */
select * from dbo.testtable
-- username password -- ------------------------------ ---------------------------------- -- user1 0x7aba56bb8e350ac328af -- user1_1 0x7aba56bb8e350ac328af -- user1_2 0x5afac964b710d95ac836 -- user2 0x2131235f272d2c375058555332214c2f -- user2_1 0x213123253a58355621592d4634295358 -- user2_2 0x213124372c2b4c2228563c454f363d56
Видно, что user1 и user1_1 имеют одинаковые пароли, а user2 и user2_1 - разные. Теперь поэкспериментируем с функцией pwdcompare() и тоже посмотрим на результаты:
select username from dbo.testtable where pwdcompare('user2', password)=1
-- username -- ---------------------------- -- user2 -- user2_1 -- user2_2
select username from dbo.testtable where pwdcompare('user2', password)=1
-- username -- ---------------------------- -- user2 -- user2_1 -- user2_2
select username from dbo.testtable where pwdcompare('user1', password)=1
-- username -- ----------------------------
Аналогично для функции encrypt():
select username from dbo.testtable where encrypt('user1') = password
-- username -- ---------------------------- -- user1 -- user1_1
select username from dbo.testtable where encrypt('user1') = password
-- username -- ---------------------------- -- user1_2