Лекция № 5
Тема: «Динамический SQL в хранимых процедурах»
План
1. Понятие динамического SQL запроса
2. Выполнение динамических SQL инструкций в Microsoft SQL Server
3. Выполнение динамических SQL инструкций в MySQL
1. Понятие динамического SQL запроса
Динамический SQL запрос - это некоторый код, который создаётся и сохраняется в переменной, пока не возникнет необходимость его выполнения. Большинство разработчиков предпочитают не использовать динамические SQL запросы по целому ряду причин. Среди наиболее типичных причин выделяют следующие:
– план выполнения динамического запроса не кэшируется и всякий раз выполняется СУБД заново, что значительно влияет на скорость работы приложения и увеличивает нагрузку на сервер;
– такие запросы являются потенциально опасными с точки зрения безопасности доступа к БД.
Тем не менее, использование динамических запросов иногда является лучшим способом получения результата, и для них есть место почти в каждой базе данных.
Есть задачи, когда динамический запрос является самым легким и, возможно, единственным решением и может исполняться также как статический запрос.
Когда эффективность и быстродействие имеют высший приоритет, тогда Вы должны всячески избегать динамических запросов.
2. Выполнение динамических SQL инструкций в Microsoft SQL Server
Microsoft SQL Server позволяет выполнять SQL инструкции, сформированные динамически, так как иногда без этого просто не обойтись. Например, для того чтобы динамически выполнять инструкции, которые с первого взгляда кажутся статическими.
Динамическая SQL инструкция – это просто текстовая строка, которая после преобразования и подставки всех значений, исполняется SQL сервером как обычная SQL инструкция.
Таким образом, чтобы сформировать динамическую SQL инструкцию, необходимо просто сформировать текстовую строку с указанием необходимых переменных, значения которых Вы хотите подставлять, или произвести конкатенацию строк с переменными, используя оператор + (плюс).
В Microsoft SQL Server существует два способа запускать на выполнения строки, содержащие SQL инструкции, это: команда Execute и системная хранимая процедура Sp_ExecuteSql.
Исходные данные для примеров
Перед тем как переходить к рассмотрению примеров выполнения динамических инструкций, давайте создадим тестовые данные, например, таблицу TestTable, и добавим в нее несколько строк.
--создание таблицы
Create Table TestTable
(
ProductId int primary key identity(1,1) not null,
CategoryId int not Null,
ProductName varchar(100) not Null,
Price money Null
)
Go
--вставляем в таблицу данные
Insert Into TestTable (CategoryId, ProductName, Price)
Values (1, 'Клавиатура', 100),
(1, 'Мышь', 50),
(2, 'Системный блок', 200)
Go
--выборка данных
Select * From TestTable
Команда Execute в T-SQL
Execute (сокращенно Exec) – команда для запуска хранимых процедур и SQL инструкций в виде текстовых строк.
Перед тем как переходить к примерам, следует отметить, что использование динамического кода с использованием команды EXEC – это не безопасно! Дело в том, что для того чтобы сформировать динамическую SQL инструкцию, необходимо использовать переменные для динамически изменяющихся значений. Так вот, если эти значения будут приходить от клиентского приложения, т.е. от пользователя, злоумышленники могут передать и, соответственно, внедрить в нашу инструкцию вредоносный код в виде текста, а мы его просто исполним в БД, думая, что нам передали обычные параметры. Поэтому все такие значения следует очень хорошо проверять, перед тем как подставлять в инструкцию.
Для примера сформируем динамический SQL запрос, текст которого мы сохраним в переменной, и затем выполним его с помощью команды Exec.
Текст запроса будет храниться в переменной @Sql_Query, в переменной @Var1 будет храниться значение, которое мы будем подставлять в наш запрос, для того чтобы этот запрос стал динамическим.
Для формирования строки мы будет использовать конкатенацию строк, а именно оператор + (плюс), только стоит понимать, что в этом случае выражения, участвующие в операции, должны иметь текстовый тип данных. Переменная @Var1 у нас будет иметь тип данных int, поэтому, чтобы соединить ее со строкой, мы предварительно преобразуем ее значение к типу данных varchar.
--объявляем переменные
Declare @Sql_Query varchar(200),
@Var1 int;
--присваиваем значение переменным
Set @Var1 = 1;
--формируем SQL инструкцию
Set @Sql_Query = 'Select * From TestTable Where ProductID = ' + Cast(@Var1 as varchar(10));
--выполняем текстовую строку как SQL инструкцию
Exec (@Sql_Query)
Хранимая процедура Sp_ExecuteSql в T-SQL
Sp_ExecuteSql – это системная хранимая процедура Microsoft SQL Server, которая выполняет SQL инструкции. Эти инструкции могут содержать параметры, тем самым делая их динамическими.
Процедура Sp_ExecuteSql имеет несколько параметров, первым параметром указывается текст SQL инструкции, вторым объявляются переменные, третий и все последующие — это передача значений для переменных в процедуру и, соответственно, подстановка в нашу инструкцию.
Все параметры процедуры Sp_ExecuteSql необходимо передавать в формате Unicode (тип данных строк должен быть nvarchar).
В примере итоговый результат у нас будет точно таким же, как и в примере с Exec, только динамические значения, у нас это переменная @Var1, мы объявим и передадим в виде параметров хранимой процедуры Sp_ExecuteSql.
--объявляем переменные
Declare @Sql_Query nvarchar(200);
--формируем SQL инструкцию
Select @Sql_Query = N'Select * From TestTable Where ProductID = @Var1;';
--выполняем текстовую строку как SQL инструкцию
Exec Sp_ExecuteSql @Sql_Query,--текст SQL инструкции
N'@Var1 as int', --объявление переменной @Var1
@Var1 = 1 --передаем значение для переменной @Var1
3. Выполнение динамических SQL инструкций в MySQL
СУБД MySQL также имеет встроенный механизм выполнения динамического SQL запроса.
Для этого нужно сначала собрать его текст через функцию Concat() в переменной и далее выполнить, используя определения Prepare и Execute, DeAllocate Prepare.
Определение Prepare готовит SQL запрос к выполнению и присваивает ему имя, по которому этот запрос можно будет выполнить. Определение записывается так:
Prepare имя_запроса From переменная_с_SQL_запросом
Определение Execute выполняет подготовленный SQL запрос по его имени. Определение записывается так:
Execute имя_запроса;
После выполнения запроса, если он больше не нужен, то его нужно удалить с помощью команды вида:
DeAllocate Prepare имя_запроса;
Например, напишем запрос, который из таблицы stud отберет студентов с фамилией на заданную букву и с нужным годом рождения. Год рождения и нужную букву для фамилии будем задавать с помощью переменных.
Set @f='П'; ##фамилия на букву П
Set @d=1999; ## год рождения 1999
##формируем текст запроса
Set @s=Concat('Select * From Stud Where fam like ''',
@f,'%'' and year(datar)= ',@d);
##готовим текст запроса для выполнения и присваиваем ему имя
Prepare SqlCom From @s;
##выполняем запрос по его имени
Execute SqlCom;
##удаляем подготовленный запрос по его имени
DeAllocate Prepare SqlCom;
Вопросы для самоконтроля
1. Что таrое динамический SQL? Его достоинства и недостатки?
2. Как выполнить динамический SQL в СУБД SQL Server?
3. Как выполнить динамический SQL в СУБД MySQL?