Exec sql пример: Выполнение динамических T-SQL инструкций в Microsoft SQL Server | Info-Comp.ru

Использование хранимых процедур в SQL Server

Данный материал является переводом оригинальной статьи «MSSQLTips : Rick Dobson : Create, Alter, Drop and Execute SQL Server Stored Procedures».

У начинающих осваивать SQL Server могут присутствовать навыки проектирования и ручного запуска сценариев T-SQL, однако не все начинающие DBA понимают, как упаковать свои сценарии T-SQL для удобного повторного использования. В этой статье мы приведём примеры, иллюстрирующие основы создания, изменения и запуска хранимых процедур, чтобы упростить повторное использование кода T-SQL. Кроме этого, мы кратко опишем использование входных и выходных параметров, а также значений кодов возврата, связанных с хранимыми процедурами.

Обзор хранимых процедур SQL Server

Хранимая процедура — это сохраненный блок кода T-SQL, например запрос для вывода списка строк в таблице. Блок кода T-SQL можно сохранить в файле сценария T-SQL. Вы также можете сохранить код из файла сценария в хранимой процедуре.

Сохранение кода в хранимой процедуре, а не в файле сценария дает несколько преимуществ. Вот несколько примеров:

• Вам не нужно открывать код в хранимой процедуре, чтобы запустить ее код T-SQL. Тогда как, пользователям необходимо открыть файл сценария с его кодом, чтобы запустить код.
• Хранимые процедуры также предлагают средство ограничения доступа к базовым таблицам для запроса. Предоставляя доступ к запуску хранимых процедур без разрешения на чтение или запись в базовые таблицы, вы можете защитить данные, но по-прежнему обеспечить видимость данных в базовых таблицах с помощью хранимой процедуры.
• Вы можете использовать входные параметры с хранимыми процедурами, чтобы изменять работу кода внутри хранимой процедуры. Хотя, файлы сценариев позволяют использовать локальные переменные для изменения наборов, возвращаемых запросами, они должны предоставлять свой код, чтобы вы могли изменять локальные переменные во время выполнения.
• Обретя навыки сегментирования программного решения на части, на основе хранимых процедур, вы упрощаете изменение кода с течением времени. Добавляя код в виде коротких модульных сценариев, каждый сценарий можно будет легче читать, поддерживать и даже повторно использовать в других приложениях. Решения, основанные на файлах SQL со сценариями для запросов, могут становиться все более длинными, трудными для чтения и обслуживания, поскольку в решение продолжают вноситься последовательные изменения.

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

Многие блоки кода T-SQL можно запускать из хранимой процедуры. Обычно первоначальную версию кода тестируют внутри файла сценария T-SQL, а затем копируют код в тело оболочки хранимой процедуры.

После создания или изменения хранимой процедуры, содержащей один или несколько операторов SELECT, вы можете вызвать хранимую процедуру с помощью оператора EXEC. Следовательно, вы можете думать о хранимой процедуре как о контейнере, который облегчает повторное использование в нем кода T-SQL.

Создание новой хранимой процедуры SQL Server

Многие администраторы баз данных знакомы с созданием таблицы с помощью оператора CREATE TABLE. Точно так же администраторы-разработчики могут создать хранимую процедуру с помощью оператора CREATE PROC или CREATE PROCEDURE. Так же, как оператор CREATE TABLE добавляет таблицу в базу данных, оператор CREATE PROC добавляет хранимую процедуру в базу данных. Итак, вам нужно начать с новой или существующей базы данных, когда вы хотите создать хранимую процедуру, поскольку хранимая процедура фактически хранится в базе данных.

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

Следующий сценарий создает базу данных с именем CodeModuleTypes. Его первый оператор определяет главную базу данных как базу данных по умолчанию. Его второй оператор создает базу данных. В зависимости от ваших потребностей могут быть гораздо более сложные версии оператора CREATE DATABASE. Если не указано иное, простой оператор создания БД, подобный приведенному ниже, будет использовать настройки по умолчанию из БД model, которая является одной из стандартных баз данных, устанавливаемых вместе с SQL Server.

use master;
GO
create database CodeModuleTypes;

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

Следующий сценарий демонстрирует синтаксис, который можно использовать для создания вашей первой хранимой процедуры. Хранимая процедура в приведенном ниже коде отображает набор результатов со всеми столбцами для каждой строки из таблицы Employee в схеме HumanResources демонстрационной базы данных AdventureWorks2014. Ранее мы описали, как загрузить копию этой демонстрационной БД.

Вы можете думать о схеме, как о способе логической группировки объектов базы данных, таких как таблицы и хранимые процедуры. Эти логические группировки позволяют избежать конфликтов имен между объектами с одинаковыми именами в разных схемах. Любая база данных может иметь несколько схем. В нашем примере все хранимые процедуры обозначены, как принадлежащие схеме dbo базы данных CodeModuleTypes.

Приведенный ниже оператор CREATE PROC состоит из трех частей.

• Оператор CREATE PROC называет хранимую процедуру (и её схему, если вы явно указываете её).
• Ключевое слово as действует как маркер, обозначающий, что код определения хранимой процедуры вот-вот начнется.
• Код T-SQL, определяющий работу хранимой процедуры. В этом примере определяющим кодом является оператор SELECT для таблицы Employee в схеме HumanResources базы данных AdventureWorks2014.

use CodeModuleTypes;
go   create proc dbo.uspMyFirstStoredProcedure
as
select * 
from AdventureWorks2014.HumanResources.Employee;

После создания хранимой процедуры ее можно запустить с помощью оператора EXEC, подобно примеру, приведенному ниже. Именно этот оператор возвращает набор результатов со всеми столбцами для каждой строки из таблицы Employee.

exec dbo.uspMyFirstStoredProcedure

Вот отрывок из вывода, созданного предыдущим скриптом.

• На панели результатов показаны первые одиннадцать столбцов из первых семнадцати строк с данными о 290 сотрудниках компании AdventureWorks.
• Если вы хотите обработать строки, отображаемые оператором SELECT в хранимой процедуре, вам нужно будет сохранить строки набора результатов в какой-либо другой таблице или объекте SQL Server. Затем обработайте результаты в этом объекте.

Удаление хранимой процедуры SQL Server

Предыдущий сценарий создания хранимой процедуры завершится ошибкой, если хранимая процедура uspMyFirstStoredProcedure в схеме dbo уже существует. Одним из способов решения этой проблемы является удаление предыдущей версии хранимой процедуры, а затем повторный запуск сценария для создания новой версии хранимой процедуры. Вы можете удалить предыдущую версию хранимой процедуры с помощью оператора DROP PROC или DROP PROCEDURE.

drop proc dbo.uspMyFirstStoredProcedure

Вместо того, чтобы позволить оператору CREATE PROC завершиться с ошибкой при наличии предыдущей версии хранимой процедуры, обычно проверяют, существует ли уже хранимая процедура, и удаляют ее, чтобы избежать ошибки. Следующий фрагмент можно использовать для удаления предыдущей версии хранимой процедуры uspMyFirstStoredProcedure, если она уже существует. В зависимости от ваших требований и кода, определяющего хранимую процедуру, может быть полезно переименовать существующую в настоящее время хранимую процедуру вместо ее удаления.

-- conditionally drop a stored proc
if object_id('dbo.uspMyFirstStoredProcedure') is not null 
     drop proc dbo. uspMyFirstStoredProcedure
go

Изменение существующей хранимой процедуры SQL Server

Следующий блок кода демонстрирует оператор ALTER PROC. Этот оператор отличается от оператора CREATE PROC тем, что он может работать только с существующей хранимой процедурой.

Этот сценарий предназначен для запуска сразу после предыдущего сценария, удаляющего uspMyFirstStoredProcedure, если он уже существует.

Первые два оператора в следующем блоке кода — это операторы CREATE PROC и EXEC, которые могут создавать новую копию хранимой процедуры и запускать uspMyFirstStoredProcedure. Поскольку весь код представлен в виде одного блока, требуются три ключевых слова GO, которые не потребовались бы, если бы весь блок кода был сегментирован на четыре отдельных блока кода:

• Начальный оператор CREATE PROC должен сопровождаться ключевым словом GO, чтобы оператор CREATE PROC завершился перед первым оператором EXEC.
• Затем начальный оператор EXEC должен сопровождаться ключевым словом GO, чтобы оператор ALTER PROC был первым оператором в его пакете.
• Наконец, за оператором ALTER PROC должно следовать ключевое слово GO, чтобы оператор ALTER PROC завершился до последнего оператора EXEC.

Оператор ALTER PROC состоит из трех частей:

• Имя объекта после ALTER PROC должно совпадать с именем существующей хранимой процедуры, которую вы хотите изменить.
• Ключевое слово as действует как разделитель, отделяющий объявления ALTER PROC от нового кода T-SQL, определяющего измененную версию хранимой процедуры.
• Новый код внутри оператора ALTER PROC соединяет таблицу Person из схемы Person с таблицей Employee из схемы HumanResources в базе данных AdventureWorks2014.

-- create a new stored proc
create proc dbo.uspMyFirstStoredProcedure
as
select * 
from AdventureWorks2014. HumanResources.Employee
go   -- run stored proc
exec dbo.uspMyFirstStoredProcedure
go   -- alter stored proc
alter proc dbo.uspMyFirstStoredProcedure
as
select
 Employee.BusinessEntityID
,Person.FirstName
,Person.LastName
,Employee.JobTitle
from AdventureWorks2014.HumanResources.Employee
inner join AdventureWorks2014.Person.Person on Employee.BusinessEntityID = Person.BusinessEntityID
go   -- run altered stored proc
exec dbo.uspMyFirstStoredProcedure

Вот результат выполнения оператора EXEC в предыдущем сценарии:

• Столбцы BusinessEntityID и JobTitle взяты из таблицы Employee.
• Столбцы FirstName и LastName взяты из таблицы Person.

Помимо очевидной разницы между созданием новой хранимой процедуры и изменением существующей хранимой процедуры, оператор ALTER PROC отличается от оператора CREATE PROC другими важными особенностями. Например, оператор ALTER PROC сохраняет все параметры безопасности, связанные с существующей сохраненной процедурой, в то время, как оператор CREATE PROC не сохраняет эти параметры. Таким образом, оператор ALTER PROC подойдет лучше, чем оператор CREATE PROC, если все, что нужно сделать, это изменить код в существующей хранимой процедуре.

Начиная с SQL Server 2016 SP1, Microsoft представила новую инструкцию CREATE или ALTER для модулей кода, таких как хранимые процедуры, представления и определяемые пользователем функции. Вы можете получить представление о функциональности этого нового оператора из предыдущей статьи MSSQLTips.com.

Входные параметры хранимой процедуры SQL Server

Входной параметр позволяет разработчику изменять способ работы хранимой процедуры во время выполнения. Обычно входной параметр в предложении where оператора SELECT используется для управления строками, отображаемыми при запуске хранимой процедуры. Можно заглянуть на страницу руководства MSSQLTips.com для демонстрации того, как использовать входные параметры в предложении where.

Вот простой пример, основанный на изменении uspMyFirstStoredProcedure, который демонстрирует использование входного параметра.

Входной параметр с именем @jobtitle назван непосредственно перед ключевым словом as.

На входной параметр имеется ссылка в предложении where оператора SELECT.

Оператор EXEC, который следует за оператором ALTER PROC, присваивает значение входному параметру @jobtitle во время выполнения. Значением параметра является строка nvarchar («Production Supervisor»).

-- alter a stored proc-- this alteration has one select statement with a where clause
-- and a criterion set by an input parameter
-- and an input parameter
alter proc dbo.uspMyFirstStoredProcedure
@jobtitle nvarchar(50)
as   select
 Employee.BusinessEntityID
,Person.FirstName
,Person.LastName
,Employee.JobTitle
from AdventureWorks2014.HumanResources.Employee
inner join AdventureWorks2014.Person.Person on Employee.BusinessEntityID = Person.BusinessEntityID
where Employee.JobTitle like @jobtitle + '%'
go   -- run altered stored proc with 
-- @jobtitle parameter value is passed without naming the parameter
exec uspMyFirstStoredProcedure N'Production Supervisor'

Вот вкладка «Results«, на которой показаны выходные данные оператора SELECT внутри uspMyFirstStoredProcedure. На вкладке показан 21 сотрудник, чьи должности начинаются с «Production Supervisor». Вы можете изменить содержимое вкладки «Results», используя другое строковое значение в операторе EXEC.

Параметры вывода хранимых процедур SQL Server

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

Если вы просто хотите передать одно значение, такое как сумма или количество, из хранимой процедуры, вы можете сделать это с помощью выходного параметра. Следующий оператор ALTER PROC иллюстрирует один из способов реализации такого рода задач.

В нашем примере оператор ALTER PROC снова изменяет uspMyFirstStoredProcedure.

Входной параметр @jobtitle из предыдущей версии хранимой процедуры сохраняется.

Кроме того, перед ключевым словом as добавляется спецификация выходного параметра:

• Имя выходного параметра — @jobtitlecount.
• Тип данных для параметра — int, потому что он предназначен для хранения значения счетчика, но вы также можете использовать bigint в качестве типа данных, если это необходимо.
• Ключевое слово out завершает спецификацию параметра, чтобы указать, что этот параметр возвращает значение после запуска хранимой процедуры.

Оператор SELECT состоит из вложенного внутреннего оператора выбора внутри внешнего операторе выбора:

• Оператор внутреннего выбора возвращает строку для каждого сотрудника, JobTitle которого начинается со значения входного параметра.
• Внешний оператор select подсчитывает количество строк, возвращаемых внутренним оператором select, и присваивает счет выходному параметру @jobtitlecount.

-- alter a stored proc
-- this alteration computes an aggregate function value
-- based, in part, on an input parameter (@jobtitle)
-- and saves the computed value in an output parameter (@jobtitlecount)
alter proc dbo. uspMyFirstStoredProcedure
@jobtitle nvarchar(50), @jobtitlecount int out
as   select @jobtitlecount = count(*)
from AdventureWorks2014.HumanResources.Employee
inner join AdventureWorks2014.Person.Person on Employee.BusinessEntityID = Person.BusinessEntityID
where Employee.JobTitle like @jobtitle + '%'

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

Перед вызовом инструкции EXEC для запуска uspMyFirstStoredProcedure объявите локальную переменную @jobtitlecount для получения значения выходного параметра из хранимой процедуры.

• Значение параметра вывода появляется в операторе EXEC с завершающим ключевым словом вывода OUTPUT. Это ключевое слово указывает, что значение параметра передается из хранимой процедуры в инструкцию EXEC.
• Оператор присваивания (=) передает значение выходного параметра в локальную переменную @jobtitlecount.

Оператор SELECT после оператора EXEC отображает значение локальной переменной @jobtitlecount, которая получила значение выходного параметра.

-- run an altered stored proc with -- @jobtitle input parameter value and
-- save the returned output parameter in a local variable   declare @jobtitlecount int   exec uspMyFirstStoredProcedure N'Production Supervisor',@jobtitlecount = @jobtitlecount OUTPUT   select @jobtitlecount [Job Title Count]

Значения кода возврата хранимой процедуры SQL Server

Хранимые процедуры могут иметь значения кода возврата, которые всегда имеют тип данных int.

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

• Входной параметр имеет имя @jobtitle.
• Критерий предложения where в операторе SELECT: Employee.JobTitle, например ‘%’ + @jobtitle + ‘%’.
• Когда инструкция SELECT с предложением where возвращает хотя бы одну строку, возвращаемое значение устанавливается равным единице. В противном случае возвращаемое значение устанавливается равным нулю.
• Условие EXISTS определяет, будет ли возвращена хотя бы одна строка из оператора SELECT.
• Предложение return возвращает значение кода возврата и завершает хранимую процедуру.

-- alter a stored proc
-- this alteration verifies if a search string value
-- is in a set of column values
-- @jobtitle input parameter contains the search string value
-- JobTitle is the column of values searched
alter proc dbo.uspMyFirstStoredProcedure
@jobtitle nvarchar(50)
as   -- does at least one JobTitle contain @jobtitle?
if exists(
   select top 1 Employee.JobTitle
   from AdventureWorks2014.HumanResources.Employee
   inner join AdventureWorks2014. Person.Person on Employee.BusinessEntityID = Person.BusinessEntityID
   where Employee.JobTitle like '%' + @jobtitle + '%'
)
begin
   return(1)
end
else
begin
   return(0)
end

Следующий сценарий демонстрирует синтаксис для сбора значения кода возврата из хранимой процедуры и показывает некоторый образец кода для обработки значения кода возврата. Скрипт вызывает uspMyFirstStoredProcedure для двух разных значений @jobtitle — sals или sales. Ни одно значение столбца JobTitle не содержит sals, но хотя бы одно значение JobTitle содержит sales.

Сначала в коде объявляются две локальные переменные с именами @jobtitle и @exists.

Локальная переменная @jobtitle используется в инструкции EXEC для передачи строки поиска в uspMyFirstStoredProcedure.

Локальная переменная @exists используется для сбора значения кода возврата из uspMyFirstStoredProcedure. Оператор присваивания внутри оператора EXEC заполняет локальную переменную @exists значением кода возврата.

Оператор потока управления if … else после оператора EXEC обрабатывает возвращаемое значение из хранимой процедуры.

• Если @exists равно нулю, инструкция SELECT сообщает, что нет JobTitle со значением строки поиска во входном параметре.
• Если @exists равно единице, оператор SELECT сообщает, что существует по крайней мере одно значение JobTitle с входным параметром.

Ниже, хранимая процедура выполняется дважды. Первоначальное выполнение предназначено для поисковой строки со значением sals. Второе выполнение — для значения sales в строке поиска.

-- run an altered stored proc with 
-- @jobtitle is an input parameter
-- @exists equals 1 for at least 1 JobTitle containing @jobTitle
-- @exists equals 0 for no JobTitle containing @jobtitle
declare @jobtitle nvarchar(50), @exists int   set @jobtitle = 'sals'
exec @exists = uspMyFirstStoredProcedure @jobtitle
if @exists = 0
begin
   select 'No JobTitle values with ' + @jobtitle [search outcome]
end
else
begin
   select 'At least one JobTitle value with ' + @jobtitle [search outcome]
end   set @jobtitle = 'sales'
exec @exists = uspMyFirstStoredProcedure @jobtitle
if @exists = 0
begin
   select 'No JobTitle values with ' + @jobtitle [search outcome]
end
else
begin
   select 'At least one JobTitle value with ' + @jobtitle [search outcome]
end

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

Несколько наборов результатов из хранимой процедуры SQL Server

Следующий сценарий снова демонстрирует, как использовать входной параметр в операторе ALTER PROC. Имя входного параметра перед ключевым словом as — @jobtitle. Эта демонстрация отличается тем, что включает в себя два отдельных оператора SELECT. Первый оператор SELECT возвращает набор результатов, состоящий из всех строк, JobTitle которых начинается со значения входного параметра. Вторая инструкция SELECT возвращает скалярное значение, которое представляет собой количество сотрудников в таблице Employee, JobTitle которых начинается со значения входного параметра.

Оператор EXEC после оператора ALTER PROC вызывает uspMyFirstStoredProcedure. Литеральное строковое значение nvarchar («Production Supervisor») после имени хранимой процедуры является значением входного параметра.

-- alter a stored proc-- this alteration has two select statements
-- and an input parameter
alter proc dbo.uspMyFirstStoredProcedure
@jobtitle nvarchar(50)
as   -- 1st select statement returns a set of row values
select
   Employee.BusinessEntityID
  ,Person.FirstName
  ,Person.LastName
  ,Employee.JobTitle
from AdventureWorks2014.HumanResources.Employee
inner join AdventureWorks2014.Person.Person on Employee.BusinessEntityID = Person.BusinessEntityID
where Employee.JobTitle like @jobtitle + '%'   -- 2nd select statement returns a scalar value
select count(*) as JobTitleCount
from AdventureWorks2014.HumanResources.Employee
inner join AdventureWorks2014.Person.Person on Employee.BusinessEntityID = Person.BusinessEntityID
where Employee.JobTitle like @jobtitle + '%'

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

-- run altered stored proc
-- @jobtitle parameter value is passed without naming the parameter   
exec dbo.uspMyFirstStoredProcedure N'Production Supervisor'

Вот вкладка «Results», на которой показаны выходные данные двух операторов SELECT внутри хранимой процедуры с именем uspMyFirstStoredProcedure.

На верхней панели отображается двадцать один сотрудник, чьи должности начинаются с «Production Supervisor».

На нижней панели отображается скалярное значение с количеством сотрудников, чьи должности начинаются с «Production Supervisor».

Как вы понимаете, можно изменить содержимое вкладки «Результаты», используя другое буквальное строковое значение в операторе EXEC.

Необходимые права доступа к базе данных—ArcGIS Insights

Подключения к базам данных поддерживаются в Insights in ArcGIS Enterprise и Insights desktop.

Существует два типа операций с данными в ArcGIS Insights:

• Подключаться к реляционной базе данных и использовать ресурсы без кэширования данных.
• Подключаться к реляционной базе данных и использовать ресурсы с кэшированием данных.

Подключение к реляционной базе данных и использование ресурсов с кэшированием данных позволяет Insights создавать и работать с временными таблицами в базе данных.

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

Эти подключение только для чтения. Insights не разрешает создание или изменение данных в базе данных.

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

Google BigQuery

В BigQuery права доступа применяются к пользователям через роли. Пользователь, который создает подключение базы данных к BigQuery, должен иметь права доступа основных ролей (Editor, Owner или Viewer) или эквивалентные права доступа в Пользовательской роли.

Microsoft SQL Server

Примеры

Следующий код SQL – пример того, как права доступа предоставляются базам данных SQL Server. Администраторы баз данных могут на основе этих примеров настроить предоставление прав доступа пользователям баз данных в своей организации.

Предоставление прав доступа на чтение-запись:

use <databaseName>;
GO
GRANT CREATE TABLE TO <userName>;
GRANT ALTER ON SCHEMA::dbo TO <userName>;
EXEC sp_addrolemember N'db_datareader', N'<userName>';
EXEC sp_addrolemember N'db_datawriter', N'<userName>';
GO

Предоставление прав доступа только на чтение на уровне базы данных:

use <databaseName>;
GO
EXEC sp_addrolemember N'db_datareader', N'<userName>';
GO

Предоставление прав доступа SELECT только на чтение определенных таблиц:

use <databaseName>;
GO
GRANT SELECT ON OBJECT::<schema>. <tableName1> TO <userName>;
GRANT SELECT ON OBJECT::<schema>.<tableName2> TO <userName>;

Oracle

Примеры

Следующий код SQL – пример того, как права доступа предоставляются базам данных Oracle. Администраторы баз данных могут на основе этих примеров настроить предоставление прав доступа пользователям баз данных в своей организации.

Предоставление прав доступа на чтение-запись:

GRANT CONNECT TO <userName>; 
GRANT CREATE TABLE TO <userName>; 
ALTER USER <userName> QUOTA <size> ON USERS; 
GRANT CREATE SEQUENCE TO <userName>;

Предоставление прав доступа только на чтение:

GRANT CONNECT TO <userName>; 
GRANT SELECT ON <schema>.<tableName> TO <userName>;

PostgreSQL

Примеры

Следующий код SQL – пример того, как права доступа предоставляются базам данных PostgreSQL. Администраторы баз данных могут на основе этих примеров настроить предоставление прав доступа пользователям баз данных в своей организации.

Предоставление прав доступа на чтение-запись:

GRANT CONNECT ON DATABASE <databaseName> TO <userName>;
GRANT USAGE ON SCHEMA <schemaName>TO <userName>;
GRANT CREATE ON SCHEMA <schemaName> TO <userName>;

Предоставление прав доступа только на чтение:

GRANT CONNECT ON DATABASE <databaseName> TO <userName>;
GRANT USAGE ON SCHEMA <schemaName>TO <userName>;   -- Give the access to all tables in the schema
GRANT SELECT ON <tableName> TO <userName>;  -- Or give the access to a specific table

SAP HANA

Примеры

Следующий код SQL – пример того, как права доступа предоставляются базам данных SAP HANA. Администраторы баз данных могут на основе этих примеров настроить предоставление прав доступа пользователям баз данных в своей организации.

Стандартный пользователь с правами на чтение-запись имеет все необходимые права для использования Insights.

Нестандартные пользователи:

GRANT SELECT ON SCHEMA <schemaName> to <userName>;   -- executed by the schema owner 
GRANT SELECT ON sys.st_geometry_columns TO <userName>;   -- executed by SYSTEM account
GRANT SELECT ON sys.st_spatial_reference_systems TO <userName>;

Snowflake

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

Примеры

Следующий код SQL – пример того, как права доступа предоставляются базам данных Snowflake. Администраторы баз данных могут на основе этих примеров настроить предоставление прав доступа пользователям баз данных в своей организации.

Предоставьте права доступа для всех таблиц в базе данных:

grant USAGE on DATABASE <databaseName> to role <roleName>;
grant USAGE on SCHEMA <schemaName> to role <roleName>;
grant SELECT ON ALL TABLES IN DATABASE <databaseName> to role <roleName>;
grant USAGE on WAREHOUSE <warehouseName> to role <roleName>;

Что дальше

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

Отзыв по этому разделу?

Обзор EXEC SQL и примеры

В этой статье мы рассмотрим оператор EXEC SQL в SQL Server и рассмотрим несколько примеров.

Команда EXEC используется для выполнения хранимой процедуры или переданной ей строки SQL. Вы также можете использовать полную команду EXECUTE, аналогичную EXEC.

Синтаксис команды EXEC в SQL Server

Ниже приведен основной синтаксис команды EXEC в SQL Server.

Чтобы проиллюстрировать примеры, я создам образец хранимой процедуры и таблицы.

Выполнение хранимой процедуры

Чтобы выполнить хранимую процедуру с помощью EXEC, передайте имя процедуры и параметры, если они есть. Пожалуйста, обратитесь к приведенному ниже сценарию T-SQL для выполнения хранимой процедуры.

Мы также можем присвоить переменной значение, возвращаемое хранимой процедурой. Пожалуйста, обратитесь к следующему примеру скрипта T-SQL.

Исполняющая строка

Чтобы выполнить строку, создайте строку и передайте ее команде EXEC SQL. Пожалуйста, обратитесь к приведенному ниже примеру, который выполняет строку.

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

Создание строки из переменной и ее выполнение с помощью команды EXEC SQL может привести к внедрению нежелательного кода. Есть несколько способов избежать SQL-инъекций. Мы рассмотрим эти методы в другой статье.

Выполнение запросов на удаленном сервере

Предложение linked_server_name вместе с командой EXEC используется для выполнения запросов на удаленном сервере. Связанный сервер должен быть настроен и Опция RPC Out должна быть включена на связанном сервере для выполнения запросов на удаленном сервере.

Пожалуйста, обратитесь к следующему примеру выполнения запроса на удаленном сервере. Замените имя связанного сервера на имя вашего связанного сервера.

Если мы не укажем имя базы данных, оператор EXEC SQL выполнит запрос к базе данных по умолчанию для входа, используемого на связанном сервере.

Если вы хотите выполнить запрос в определенной базе данных, используйте в запросе «USE имя_базы_данных». Пожалуйста, обратитесь к приведенному ниже примеру.

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

Чтобы выполнить хранимую процедуру на удаленном сервере, используйте приведенный ниже сценарий T-SQL, заменив имя связанного сервера, имя базы данных и имя хранимой процедуры.

Ниже приведен пример выполнения хранимой процедуры на связанном сервере с использованием нотации из четырех частей. Здесь « TEST01V» — имя сервера, « test » — имя базы данных, а « dbo » — имя схемы.

ВЫПОЛНЕНИЕ С ПЕРЕКОМПИЛЯЦИЕЙ

Эта опция выполнения в операторе EXEC SQL создает новый план и отбрасывает его после использования. Если существует существующий план процедуры, он сохраняется в кэше. Если для процедуры нет существующего плана, то при использовании опции перекомпиляции план не будет сохранен в кеше.

Пожалуйста, обратитесь к приведенному ниже примеру для выполнения процедуры с опцией перекомпиляции. Перед выполнением этого я очистил кеш плана с помощью DBCC FREEPROCCACHE().

После выполнения приведенного выше сценария T-SQL я выполнил приведенный ниже сценарий, чтобы проверить кэшированный план.

7777777777777

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

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

Пожалуйста, обратитесь к изображению ниже для набора результатов вышеуказанного запроса. Мы видим, что идентификатор плана и счетчики использования одинаковы, а существующий план не изменился. EXEC WITH RECOMPILE не использовал существующий план в кеше и создал новый план, использовал его и отбросил.

ВЫПОЛНИТЬ С НАБОРАМИ РЕЗУЛЬТАТОВ

Этот параметр используется для изменения набора результатов хранимой процедуры или строки, выполняемой в соответствии с определением, указанным в предложении WITH RESULT SETS.

См. следующий пример выполнения хранимой процедуры с НАБОРАМИ РЕЗУЛЬТАТОВ.

Мы можем изменить заголовки набора результатов и тип данных возвращаемого столбца, выполнив хранимую процедуру. Это похоже на использование convert(), cast() и псевдонимов столбцов в обычном скрипте T-SQL.

Если процедура или строка T-SQL возвращает более одного набора результатов, мы должны определить несколько наборов результатов в предложении WITH RESULTS SETS, иначе будет выдана следующая ошибка: «Операция EXECUTE не удалась, поскольку в предложении WITH RESULT SETS указан 1 набор результатов. ), и инструкция пыталась отправить больше наборов результатов, чем это».

Обратитесь к следующему примеру, чтобы использовать предложение WITH RESULTS SETS в операторе EXEC SQL для нескольких наборов результатов, возвращаемых хранимой процедурой или строкой. В этом примере хранимая процедура возвращает два одинаковых набора результатов. Я определил два результата в предложении WITH RESULTS SETS, изменив тип данных и заголовки набора результатов в обоих наборах результатов.

Заключение

В этой статье мы рассмотрели различные аспекты инструкции EXEC SQL на нескольких примерах. Если у вас есть какие-либо вопросы, пожалуйста, не стесняйтесь задавать их в разделе комментариев ниже.

• Автор
• Последние сообщения

Ранга Бабу

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

Последние сообщения Ранги Бабу (посмотреть все)

Как выполнить динамический SQL в SQL Server

Автор Иосип Сабан• 09 июня 2021 г. •
09:56•
Разработка баз данных, операторы, хранимые процедуры

ГлавнаяРазработка баз данных, операторы, хранимые процедурыВыполнение динамического запроса в SQL Server

Динамический SQL — это оператор, созданный и выполняемый во время выполнения, обычно содержащий динамически генерируемые части строки SQL, входные параметры или и то, и другое.

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

Мы используем два способа выполнения динамического SQL: команда EXEC и хранимая процедура sp_executesql .

Содержание

• Как использовать команду EXEC или EXECUTE
• Примеры использования расширенной процедуры sp_executesql
• Динамический SQL в хранимых процедурах
• Параметр OUTPUT в sp_executesql
• Защита от SQL-инъекций с помощью процедуры sp_executesql
• Сравнение функций команды EXEC и хранимой процедуры sp_executesql
• Заключение

Как использовать команду EXEC или EXECUTE

В первом примере мы создаем простую динамическую инструкцию SQL из AdventureWorks 9База данных 0777 . В примере есть один фильтр, который передается через объединенную строковую переменную @AddressPart и выполняется в последней команде:

 USE AdventureWorks2019.
-- Объявить переменную для хранения сгенерированного оператора SQL
ОБЪЯВИТЬ @SQLExec NVARCHAR(4000)
DECLARE @AddressPart NVARCHAR(50) = 'a'
-- Построить динамический SQL
SET @SQLExec = 'SELECT * FROM Person.Address WHERE AddressLine1 LIKE ''%' + @AddressPart + '%'''
-- Выполнение динамического SQL
EXEC (@SQLExec)
 

Обратите внимание, что запросы, построенные путем объединения строк, могут привести к уязвимостям SQL-инъекций. Я настоятельно рекомендую вам ознакомиться с этой темой. Если вы планируете использовать такую ​​архитектуру разработки, особенно в общедоступном веб-приложении, она будет более чем полезна.

Затем мы должны обрабатывать значения NULL в конкатенациях строк . Например, переменная экземпляра @AddressPart из предыдущего примера может сделать недействительным весь оператор SQL, если будет передано это значение.

Самый простой способ справиться с этой потенциальной проблемой — использовать функцию ISNULL для создания действительного оператора SQL. , ‘ ‘) + ‘%»’

Важно! Команда EXEC не предназначена для повторного использования кэшированных планов выполнения! Он будет создавать новый для каждого выполнения.

Чтобы продемонстрировать это, мы дважды выполним один и тот же запрос, но с другим значением входного параметра. Затем сравниваем планы выполнения в обоих случаях:

 USE AdventureWorks2019
-- Случай 1
ОБЪЯВИТЬ @SQLExec NVARCHAR(4000)
DECLARE @AddressPart NVARCHAR(50) = 'a'
 
SET @SQLExec = 'SELECT * FROM Person.Address WHERE AddressLine1 LIKE ''%' + @AddressPart + '%'''
EXEC (@SQLExec)
-- Случай 2
УСТАНОВИТЕ @AddressPart = 'б'
 
SET @SQLExec = 'SELECT * FROM Person.Address WHERE AddressLine1 LIKE ''%' + @AddressPart + '%'''
EXEC (@SQLExec)
-- Сравните планы
ВЫБЕРИТЕ chdpln.objtype
, chdpln.cacheobjtype
, chdpln.usecounts
, sqltxt.текст
  ИЗ sys.dm_exec_cached_plans как chdpln
       ПЕРЕКРЕСТНОЕ ПРИМЕНЕНИЕ sys. dm_exec_sql_text(chdpln.plan_handle) как sqltxt
 ГДЕ sqltxt.text КАК 'SELECT *%';
 

Примеры использования расширенной процедуры sp_executesql

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

 sp_executesql @SQLStatement, N'@ParamNameDataType' , @Parameter1 = 'Value1' 

Давайте начнем с простого примера, который показывает, как передать оператор и параметры:

 EXECUTE sp_executesql
               ВЫБЕРИТЕ *
                     ОТ Лицо.Адрес
WHERE AddressLine1 LIKE ''%'' + @AddressPart + ''%''', -- Оператор SQL
              N'@AddressPart NVARCHAR(50)', -- Определение параметра
             @AddressPart = 'а'; -- Значение параметра
 

В отличие от команды EXEC расширенная хранимая процедура sp_executesql повторно использует планы выполнения, если выполняется с тем же оператором, но с другими параметрами. Поэтому лучше использовать sp_executesql , а не EXEC команду :

 EXECUTE sp_executesql
               ВЫБЕРИТЕ *
                     ОТ Лицо. Адрес
WHERE AddressLine1 LIKE ''%'' + @AddressPart + ''%''', -- Оператор SQL
              N'@AddressPart NVARCHAR(50)', -- Определение параметра
             @AddressPart = 'а'; -- Значение параметра
ВЫПОЛНИТЬ sp_executesql
               ВЫБЕРИТЕ *
                     ОТ Лицо.Адрес
WHERE AddressLine1 LIKE ''%'' + @AddressPart + ''%''', -- Оператор SQL
              N'@AddressPart NVARCHAR(50)', -- Определение параметра
             @AddressPart = 'б'; -- Значение параметра
ВЫБЕРИТЕ chdpln.objtype
, chdpln.cacheobjtype
, chdpln.usecounts
, sqltxt.текст
  ИЗ sys.dm_exec_cached_plans как chdpln
       ПЕРЕКРЕСТНОЕ ПРИМЕНЕНИЕ sys.dm_exec_sql_text(chdpln.plan_handle) как sqltxt
  ГДЕ sqltxt.text LIKE '%Person.Address%';
 

Динамический SQL в хранимых процедурах

До сих пор мы использовали динамический SQL в скриптах. Однако реальные преимущества становятся очевидными, когда мы выполняем эти конструкции в пользовательских объектах программирования — пользовательских хранимых процедурах.

Давайте создадим процедуру, которая будет искать человека в базе данных AdventureWorks на основе различных значений входных параметров процедуры. Из пользовательского ввода мы создадим динамическую команду SQL и выполним ее, чтобы вернуть результат вызывающему пользовательскому приложению:

 СОЗДАТЬ ИЛИ ИЗМЕНИТЬ ПРОЦЕДУРУ [dbo].[test_dynSQL]
(
  @FirstName NVARCHAR(100) = NULL
 , @MiddleName NVARCHAR(100) = NULL
 , @LastName NVARCHAR(100) = NULL
)
В КАЧЕСТВЕ
НАЧИНАТЬ
УСТАНОВИТЬ БЕЗ СЧЕТА;
 
ОБЪЯВИТЬ @SQLExec NVARCHAR(MAX)
ОБЪЯВИТЬ @Параметры NVARCHAR(500)
 
SET @Parameters = '@FirstName NVARCHAR(100),
  @MiddleName NVARCHAR(100),
@Фамилия NVARCHAR(100)
'
 
УСТАНОВИТЕ @SQLExec = 'ВЫБЕРИТЕ *
ОТ Человека.Человека
ГДЕ 1 = 1
'
ЕСЛИ @FirstName НЕ NULL И LEN(@FirstName) > 0
   SET @SQLExec = @SQLExec + ' AND FirstName LIKE ''%'' + @FirstName + ''%'' '
ЕСЛИ @MiddleName НЕ NULL И LEN(@MiddleName) > 0
                SET @SQLExec = @SQLExec + ' AND MiddleName LIKE ''%''
                                                                    + @MiddleName + ''%'' '
ЕСЛИ @LastName НЕ NULL И LEN(@LastName) > 0
 SET @SQLExec = @SQLExec + ' AND LastName LIKE ''%'' + @LastName + ''%'' '
EXEC sp_Executesql @SQLExec
, @Параметры
 , @[электронная почта защищена], @[электронная почта защищена],
                                                @[электронная почта защищена]
 
КОНЕЦ
ИДТИ
EXEC [dbo]. [test_dynSQL] 'Ke', NULL, NULL
 

Параметр OUTPUT в sp_executesql

            Мы можем использовать sp_executesql с параметром OUTPUT, чтобы сохранить значение, возвращаемое инструкцией SELECT. Как показано в приведенном ниже примере, это обеспечивает количество строк, возвращаемых запросом в выходную переменную @Output:

 DECLARE @Output INT
ВЫПОЛНИТЬ sp_executesql
        N'SELECT @Output = СЧЁТ(*)
            ОТ Лицо.Адрес
WHERE AddressLine1 LIKE ''%'' + @AddressPart + ''%''', -- Оператор SQL
              N'@AddressPart NVARCHAR(50), @Output INT OUT', -- Определение параметра
             @AddressPart = 'a', @Output = @Output OUT; -- Параметры
ВЫБЕРИТЕ @выход
 

Защита от внедрения кода SQL с помощью sp_executesql Процедура

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

Мы создаем простую хранимую процедуру и выполняем ее с допустимыми и недопустимыми параметрами:

 СОЗДАТЬ ИЛИ ИЗМЕНИТЬ ПРОЦЕДУРУ [dbo].[test_dynSQL]
(
  @InputTableName NVARCHAR(500)
)
В КАЧЕСТВЕ
НАЧИНАТЬ
  DECLARE @AddressPart NVARCHAR(500)
  DECLARE @Output INT
  ОБЪЯВИТЬ @SQLExec NVARCHAR(1000)
  ЕСЛИ СУЩЕСТВУЕТ (ВЫБЕРИТЕ 1 ИЗ sys.objects, ГДЕ тип = 'u' И имя = @InputTableName)
  НАЧИНАТЬ
      ВЫПОЛНИТЬ sp_executesql
        N'SELECT @Output = СЧЁТ(*)
            ОТ Лицо.Адрес
WHERE AddressLine1 LIKE ''%'' + @AddressPart + ''%''', -- Оператор SQL
              N'@AddressPart NVARCHAR(50), @Output INT OUT', -- Определение параметра
             @AddressPart = 'a', @Output = @Output OUT; -- Параметры
       ВЫБЕРИТЕ @выход
  КОНЕЦ
  ЕЩЕ
  НАЧИНАТЬ
     THROW 51000, 'Указано неверное имя таблицы, возможная SQL-инъекция. Выход из процедуры', 1
  КОНЕЦ
КОНЕЦ
EXEC [dbo].[test_dynSQL] 'Человек'
 

 EXEC [DBO]. [TEST_DYNSQL] 'Примечательный' 

Сравнение функций команды EXEC и SP_EXECUTESQL.