Exec sql пример: EXECUTE (Transact-SQL) — SQL Server

SPBDEV Blog — Создание динамического SQL в хранимой процедуре

Tags:
SQL

Sunday, April 8, 2018 4:21:31 PM

Как построить динамический SQL в хранимой процедуре

После прочтения этой статьи вы поймете основы динамического SQL; как создавать инструкции на основе значений переменных и как выполнять эти сконструированные инструкции, используя sp_executesql и EXECUTE () из хранимой процедуры.

Все примеры этого урока основаны на Microsoft SQL Server Management и образцов баз данных, AdventureWorks и WideWorldImporters.

Создание динамического SQL в хранимой процедуре

Большинство SQL, которые мы пишем, записываются непосредственно в хранимую процедуру. Это то, что называется статическим SQL. Он называется так потому, что он не меняется. Как только он записан, его значение задано и не подлежит изменению.

Ниже приведен пример статического SQL:

SELECT    JobTitle, Count(BusinessEntityID)

FROM      HumanResources. Employee

WHERE     Year(BirthDate) = 1970

GROUP BY  JobTitle

SELECT    JobTitle, Count(BusinessEntityID)

FROM      HumanResources.Employee

WHERE     Year(BirthDate) = 1971

GROUP BY JobTitle

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

Именно здесь вступает в игру динамический SQL.

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

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

DECLARE @birthYear int = 1970

DECLARE @statement NVARCHAR(4000)

WHILE @birthYear <= 1971

BEGIN

  SET @statement = ‘

       SELECT JobTitle, Count(BusinessEntityID)

       FROM HumanResources.Employee

       WHERE Year(BirthDate) = ‘ + CAST(@birthYear as NVARCHAR) +

     ‘ GROUP BY JobTitle’

  EXECUTE sp_executesql @statement

  SET @birthYear = @birthYear + 1

END

Динамический SQL выделен жирным шрифтом. Это SQL, который построен для каждого @birthYear. По мере создания SQL он сохраняется в @statement. Затем он выполняется с использованием sp_executesql, который мы объясним ниже.

Введение в sp_executesql

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

EXECUTE sp_executesql @statement.

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

Вот простой пример:

DECLARE @statement NVARCHAR(4000)

SET @statement = N’SELECT getdate()’

EXECUTE sp_executesql  @statement

Если вы запустите это в окне запроса, вы получите подобный результат:

2018-01-24 18:49:30.143

Теперь, когда вы поняли, как работает sp_executeslq, давайте перейдем к практике. Предположим, вас попросили написать хранимую процедуру, которая возвращает либо среднее значение LineTotal, либо сумму LineTotal по ProductID для продуктов, отправленных в 2011 году.

Ваше руководство хотело бы, чтобы это было написано как хранимая процедура. Хранимая процедура должна принимать один параметр @ReturnAverage. Если это истинно, то вы вернете среднее значение, в противном случае сумму.

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

CREATE PROCEDURE uspCalcuateSalesSummaryStatic

@returnAverage bit

AS

IF (@returnAverage = 1)

BEGIN

  SELECT   SOD.ProductID,

           AVG(SOD.LineTotal) as ResultAvg

  FROM     Sales.SalesOrderDetail SOD

           INNER JOIN Sales.SalesOrderHEader SOH

                      ON SOH.SalesOrderID = SOD.SalesOrderID

  WHERE    YEAR(SOH. ShipDate) = 2011

  GROUP BY SOD.ProductID

END

ELSE

BEGIN

  SELECT   SOD.ProductID,

           SUM(SOD.LineTotal) as ResultSum

  FROM     Sales.SalesOrderDetail SOD

           INNER JOIN Sales.SalesOrderHEader SOH

                      ON SOH.SalesOrderID = SOD.SalesOrderID

  WHERE    YEAR(SOH.ShipDate) = 2011

  GROUP BY SOD.ProductID

END

Что здесь является слабым местом, так это много дублированного кода, который я выделил жирным шрифтом. Существует не так много уникального кода, но имеющийся выделен курсивом.

При всей этой избыточности у нас есть прекрасная возможность продемонстрировать некоторый динамический SQL. Давайте сделаем это!

CREATE PROCEDURE uspCalcuateSalesSummaryDynamic

                @returnAverage bit

AS

DECLARE @statement NVARCHAR(4000),

@function NVARCHAR(10)

IF (@returnAverage = 1) SET @function = ‘Avg’

ELSE SET @function = ‘Sum’

SET @statement =

   ‘SELECT  SOD. ProductID,’ +

            @function + + ‘(SOD.LineTotal) as Result’ + @function + ‘

    FROM   Sales.SalesOrderDetail SOD

            INNER JOIN Sales.SalesOrderHEader SOH

                       ON SOH.SalesOrderID = SOD.SalesOrderID

    WHERE    YEAR(SOH.ShipDate) = 2011

    GROUP BY SOD.ProductID’

EXECUTE sp_executesql @statement

Здесь вместо двух полных версий SQL, один для AVG, другой для SUM, мы создаем запрошенную версию «на лету».

SQL построен и сохраняется в переменной @statement. Эта переменная построена на основе значения параметра @returnAverage. Если установлено значение 1, то @function представляет Среднее; в противном случае — Суммирование.

Отладка динамического SQL

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

Запустите хранимую процедуру с помощью команды запуска отладчика, а затем введите код.

Продолжайте до тех пор, пока вы не прочитаете инструкцию Execute, выделенную ниже.

Использование отладчика

Как только вы достигнете этой инструкции, наведите указатель мыши на @statement, и когда появится подсказка инструмента, выберите текстовый визуализатор.

Отладчик является мощным и заслуживающим понимания. Я бы очень хотел, чтобы вы узнали об этом здесь.

Использование sp_executesql с параметрами

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

Инструкция принимает форму:

EXECUTE sp_executesql @statement, @parameterDefinition, @parm1=value1…, @parm2=value2, …

Итак, давайте поясним детали.

• @statement — это SQL, который мы хотим выполнить.
• @parameterDefinition — это строка, содержащая определение всех параметров, указанных в @statement. Перечислен каждый параметр и тип, найденный @statement. Имя и тип разделяются пробелом. Несколько параметров разделяются запятой.

Затем мы устанавливаем значения параметров, задавая параметры и желаемое значение. Параметры перечислены в порядке, определенном в строке @parameterDefinition.

• @ parm1 — это первый параметр, определенный в строке @parameterDefinition. Value — это значение, которое вы хотите установить.
• @ parm2 — это второй параметр, если он определен, как указано в параметре @parameterDefinition.
• и так далее…

Вот простой пример, который добавляет два числа, чтобы попробовать:

DECLARE @statement NVARCHAR(4000)

DECLARE @parameterDefinition NVARCHAR(4000)

SET @statement = N’SELECT @a + @b’

SET @parameterDefinition = N’@a int, @b int’

EXECUTE sp_executesql  @statement, @parameterDefinition, @a=10, @b=5

Выделены различные части инструкции:

• @statement (жирный шрифт) — обратите внимание, что он включает в себя 2 параметра: @a и @b. Также обратите внимание, что они не заявлены в TSQL. Скорее, они установлены в определении параметра.
• @parameterDefinition (курсив) — каждый указанный параметр определяется как тип int.

Значения параметров (жирный шрифт+курсив) — здесь мы устанавливаем значение параметра.

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

Эти параметры определяются как целые числа. Значение каждого параметра устанавливается в команде sp_executesql.

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

Давайте рассмотрим наш предыдущий пример и расширим его. Вместо того, чтобы жестко кодировать shipDate в запросе, как мы это сделали, давайте введем это как параметр. Это делает запрос более гибким и работает с годами, кроме 2011 года.

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

Обновленная хранимая процедура с изменениями показана ниже.

CREATE PROCEDURE uspCalcuateSalesSummaryDynamic2

                @returnAverage bit,

                @shipDate int

AS

DECLARE @statement NVARCHAR(4000),

@parameterDefinition NVARCHAR(4000),

@function NVARCHAR(10)

IF (@returnAverage = 1) SET @function = ‘Avg’

ELSE SET @function = ‘Sum’

SET @parameterDefinition = ‘@shipDateYear int’

SET @statement =

   ‘SELECT   SOD.ProductID,’ +

             @function + + ‘(SOD.LineTotal) as Result’ + @function + ‘

    FROM    Sales. SalesOrderDetail SOD

             INNER JOIN Sales.SalesOrderHEader SOH

                        ON SOH.SalesOrderID = SOD.SalesOrderID

    WHERE    YEAR(SOH.ShipDate) = @shipDateYear

    GROUP BY SOD.ProductID’

EXECUTE sp_executesql @statement, @parameterDefinition, @shipDateYear=@shipDate

Чтобы запустить это, просто вызовите procpackSalesSummaryDynamic2 proc из окна запросов, используя следующую команду:

EXECUTE uspCalcuateSalesSummaryDynamic2 1,2011

Если вы это сделаете, вы увидите следующие результаты.

Результаты запроса

Позвольте мне показать вам одно прекрасное упрощение, давайте объединим @shipDateYear и @shipDate в один параметр. Мы исключим @shipDateYear из нашего кода. Это облегчит отслеживание и чтение:

CREATE PROCEDURE uspCalcuateSalesSummaryDynamic2

                @returnAverage bit,

<span>                @shipDate int

AS

DECLARE @statement NVARCHAR(4000),

       @parameterDefinition NVARCHAR(4000),

       @function NVARCHAR(10)

IF (@returnAverage = 1) SET @function = ‘Avg’

ELSE SET @function = ‘Sum’

SET @parameterDefinition = ‘@shipDate int’

SET @statement =

   ‘SELECT   SOD. ProductID,’ +

             @function + + ‘(SOD.LineTotal) as Result’ + @function + ‘

    FROM    Sales.SalesOrderDetail SOD

             INNER JOIN Sales.SalesOrderHEader SOH

                        ON SOH.SalesOrderID = SOD.SalesOrderID

    WHERE    YEAR(SOH.ShipDate) = @shipDate

    GROUP BY SOD.ProductID’

EXECUTE sp_executesql @statement, @parameterDefinition, @shipDate

Обратите внимание, что инструкция EXECUTE намного проще, нет необходимости назначать параметр инструкции SQL @shipDateYear параметру хранимой процедуры parameter @ shipDate.

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

Запуск динамического SQL с помощью EXECUTE ()

Вы также можете использовать команду EXEC или EXECUTE для запуска динамического SQL. Формат этой команды:

EXECUTE (@statement)

Вот простой пример:

DECLARE @statement NVARCHAR(4000)

SET @statement = N’SELECT getdate()’

EXECUTE (@statement)

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

Msg 2812, Level 16, State 62, Line 3

Could not find stored procedure ‘SELECT getdate()’.

Конечно, это дает отличную подсказку! Если хотите, можете использовать переменные, чтобы указать, какие хранимые процедуры вызывать.

sp_executesql против EXECUTE

Возможно, вам интересно, зачем использовать sp_executesql в сравнении с EXECUTE. Каковы различия между ними?

Вот несколько причин, по которым Microsoft рекомендует использовать sp_executesql для запуска динамического SQL:

• С помощью EXECUTE все параметры могут быть преобразованы из своего исходного типа в Unicode. Это затрудняет способность оптимизатора сопоставлять динамически построенный SQL с уже существующим планом.
• Используя sp_executesql, оптимизатор распознает параметры в динамическом SQL, что упрощает оптимизатор для соответствия планам.
• Легче читать параметризованные запросы, чем читать кучу объединяющего их текста.
• Параметрированные запросы менее подвержены атакам SQL-инъекций.

СОЗДАНИЕ ХРАНИМЫХ ПРОЦЕДУР В MICROSOFT SQL SERVER

Для освоения программирования хранимых процедур используем при-
мер базы данных c названием DB_Books, которая была создана в лабора-
торной работе №1. При выполнении примеров и заданий обращайте вни-
мание на соответствие названий БД, таблиц и других объектов проекта.

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

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

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

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

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

Синтаксис оператора создания новой или изменения имеющейся хранимой процедуры в обозначениях MS SQL Server:

{CREATE | ALTER } PROC[EDURE] имя_процедуры [;номер]
[{@имя_параметра тип_данных } [VARYING ] [=DEFAULT][OUTPUT] ][,. ..n]
[WITH { RECOMPILE | ENCRYPTION | RECOMPILE,
ENCRYPTION }]
[FOR REPLICATION]
AS
sql_оператор [...n]

Рассмотрим параметры данной команды.

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

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

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

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

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

Параметр FOR REPLICATION востребован при репликации данных и
включении создаваемой хранимой процедуры в качестве статьи в публикацию.
Ключевое слово ENCRYPTION предписывает серверу выполнить
шифрование кода хранимой процедуры, что может обеспечить защиту от
использования авторских алгоритмов, реализующих работу хранимой процедуры.
Ключевое слово AS размещается в начале собственно тела хранимой
процедуры. В теле процедуры могут применяться практически все команды SQL, объявляться транзакции, устанавливаться блокировки и вызываться другие хранимые процедуры. Выход из хранимой процедуры можно
осуществить посредством команды RETURN.

DROP PROCEDURE {имя_процедуры} [,...n]

Для выполнения хранимой процедуры используется команда:
[[ EXEC [ UTE] имя_процедуры [;номер]
[[@имя_параметра=]{значение | @имя_переменной}
[OUTPUT ]|[DEFAULT ]][,...n]

Если вызов хранимой процедуры не является единственной командой в
пакете, то присутствие команды EXECUTE обязательно. Более того, эта
команда требуется для вызова процедуры из тела другой процедуры или
триггера.

Использование ключевого слова OUTPUT при вызове процедуры раз-
решается только для параметров, которые были объявлены при создании
процедуры с ключевым словом OUTPUT.

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

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

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

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

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

CREATE PROCEDURE Count_Books AS
SELECT COUNT(Code_book) FROM Books
GO

Задание 1. Создайте данную процедуру в разделе Stored Procedures ба-
зы данных DB_Books через утилиту SQL server Management Studio. Запус-
тите ее с помощью команды

EXEC Count_Books

Проверьте результат.

Пример создания процедуры c входным параметром:

CREATE PROCEDURE Count_Books_Pages @Count_pages AS INT
AS
SELECT COUNT(Code_book) FROM Books WHERE Pages>=@Count_pages
GO

Задание 2. Создайте данную процедуру в разделе Stored Procedures ба-
зы данных DB_Books через утилиту SQL server Management Studio. Запус-
тите ее с помощью команды

EXEC Count_Books_Pages 100

Проверьте результат.

Пример создания процедуры c входными параметрами:

CREATE PROCEDURE Count_Books_Title @Count_pages AS INT, @Title AS
CHAR(10)
AS
SELECT COUNT(Code_book) FROM Books WHERE Pages>=@Count_pages
AND Title_book LIKE @Title
GO

Задание 3. Создайте данную процедуру в разделе Stored Procedures ба-
зы данных DB_Books через утилиту SQL server Management Studio. Запус-
тите ее с помощью команды

EXEC Count_Books_Title 100, 'П%'

Проверьте результат.

Пример создания процедуры c входными параметрами и выходным параметром:

CREATE PROCEDURE Count_Books_Itogo @Count_pages INT, @Title
CHAR(10) , @Itogo INT OUTPUT
AS
SELECT @Itogo = COUNT(Code_book) FROM Books WHERE
Pages>=@Count_pages AND Title_book LIKE @Title
GO

Задание 4. Создайте данную процедуру в разделе Stored Procedures ба-
зы данных DB_Books через утилиту SQL server Management Studio. Запус-
тите с помощью набора команд:

 sql>
Declare @q As int
EXEC Count_Books_Itogo 100, 'П%', @q output
select @q

Проверьте результат.

Пример создания процедуры c входными параметрами и RETURN:

CREATE PROCEDURE checkname @param INT
AS
IF (SELECT Name_author FROM authors WHERE Code_author = @param) =
'Пушкин А. С.'
RETURN 1
ELSE
RETURN 2

Задание 5. Создайте данную процедуру в разделе Stored Procedures ба-
зы данных DB_Books через утилиту SQL server Management Studio. Запус-
тите ее с помощью команд:

DECLARE @return_status INT
EXEC @return_status = checkname 1
SELECT 'Return Status' = @return_status

Пример создания процедуры без параметров для увеличения значения
ключевого поля в таблице Purchases в 2 раза:

CREATE PROC update_proc
AS
UPDATE Purchases SET Code_purchase = Code_purchase*2

Процедура не возвращает никаких данных.

Задание 6.
Создайте данную процедуру в разделе Stored Procedures ба-
зы данных DB_Books через утилиту SQL server Management Studio. Запус-
тите ее с помощью команды

EXEC update_proc

Пример процедуры с входным параметром для получения всей ин-
формации о конкретном авторе:

CREATE PROC select_author @k CHAR(30)
AS
SELECT * FROM Authors WHERE name_author=@k

Задание 7.
Создайте данную процедуру в разделе Stored Procedures ба-
зы данных DB_Books через утилиту SQL server Management Studio. Запус-
тите ее с помощью команд:

EXEC select_author 'Пушкин А.С.' или
select_author @k='Пушкин А.С.' или
EXEC select_author @k='Пушкин А.С.'

Пример создания процедуры с входным параметром и значением по
умолчанию для увеличения значения ключевого поля в таблице Purchases
в заданное количество раза (по умолчанию в 2 раза):

CREATE PROC update_proc @p INT = 2
AS
UPDATE Purchases SET Code_purchase = Code_purchase *@p

Процедура не возвращает никаких данных.

Задание 8.
Создайте данную процедуру в разделе Stored Procedures ба-
зы данных DB_Books через утилиту SQL server Management Studio. Запус-
тите ее с помощью команд:

EXEC update_proc 4 или
EXEC update_proc @p = 4 или
EXEC update_proc --будет использовано значение по умолчанию.

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

CREATE PROC count_purchases
@d1 SMALLDATETIME, @d2 SMALLDATETIME,
@c INT OUTPUT
AS
SELECT @c=COUNT(Code_purchase) FROM Purchases WHERE Date_order
BETWEEN @d1 AND @d2
SET @c = ISNULL(@c,0)

Задание 9.
Создайте данную процедуру в разделе Stored Procedures ба-
зы данных DB_Books через утилиту SQL server Management Studio. Запус-
тите ее с помощью команд:

DECLARE @c2 INT
EXEC count_purchases ’01-jun-2006’, ’01-jul-2006’, @c2 OUTPUT
SELECT @c2

Варианты заданий к лабораторной работе №4

Общие положения.

В утилите SQL Server Management Studio создать новую страницу для
кода (кнопка «Создать запрос»). Программно сделать активной созданную
БД DB_Books с помощью оператора Use. Создать хранимые процедуры c
помощью операторов Create procedure, причем самостоятельно определить имена процедур. Каждая процедура будет выполнять по одному SQL
запросу, которые были выполнены во второй лабораторной работе. Причем код SQL запросов нужно изменить таким образом, чтобы в них можно
было передавать значения полей, по которым осуществляется поиск.

Например, исходное задание и запрос в лабораторной работе №2:

/*Выбрать из справочника поставщиков (таблица Deliveries) названия ком-
паний, телефоны и ИНН (поля Name_company, Phone и INN), у которых
название компании (поле Name_company) „ОАО МИР“.

SELECT Name_company, Phone, INN FROM Deliveries
WHERE Name_company = 'ОАО МИР'

*/
–В данной работе будет создана процедура:

CREATE PROC select_name_company @comp CHAR(30)
AS
SELECT Name_company, Phone, INN FROM Deliveries
WHERE Name_company = @comp

–Для запуска процедуры используется команда:

EXEC select_name_company 'ОАО МИР'

Сохранить файл программы с названием ФамилияСтудента_ЛАб_4.
В SQL Server Management Studio в разделе хранимых процедур БД
DB_Books проверить наличие процедур.

Список заданий

В утилите SQL Server Management Studio создать новую программу.
Программно сделать активной индивидуальную БД, созданную в лабораторной работе №1, с помощью оператора Use. Создать хранимые процедуры c помощью операторов Create procedure, причем самостоятельно определить имена процедур. Каждая процедура будет выполнять по одному SQL запросу, которые представлены в виде отдельных заданий по вариантам.

Сохранить файл программы с названием Фамилия Студента_Лаб_4_№варианта. В SQL Server Management Studio в разделе хранимых процедур индивидуальной БД проверить наличие процедур.

Вариант 1

1. Вывести список сотрудников, у которых есть хотя бы один ребенок.

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

3. Вывести список родителей, у которых есть несовершеннолетние дети.

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

Вариант 2

1. Вывести список приборов с указанным типом.

2. Вывести количество отремонтированных приборов и общую стоимость ремонтов у указанного мастера.

3. Вывести список владельцев приборов и количество их обращений,
отсортированный по количеству обращений по убыванию.

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

Вариант 3

1. Вывести список цветков с указанным типом листа.

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

3. Вывести дату продажи, сумму, продавца и цветок по указанному коду продажи.

4. Вывести список цветов и сорт для цветов с высотой больше указанного числа или цветущий.

Вариант 4

1. Вывести список лекарств с указанным показанием к применению.

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

3. Вывести дату поставки, сумму, ФИО руководителя от поставщика и
название лекарства по коду поступления больше указанного числа.

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

Вариант 5

1. Вывести список сотрудников с указанной должностью.

2. Вывести список списанного оборудования по указанной причине.

3. Вывести дату поступления, название оборудования, ФИО ответственного и дату списания для оборудования, списанного в указанный
период.

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

Вариант 6

1. Вывести список блюд с весом больше указанного числа.

2. Вывести список продуктов, в названии которых встречается указанный фрагмент слова.

3. Вывести объем продукта, название блюда, название продукта с кодом блюда от указанного начального значения по определенному
конечному значению.

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

Вариант 7

1. Вывести список сотрудников с указанной должностью.

2. Вывести список документов, в содержании которых встречается указанный фрагмент слова.

3. Вывести дату регистрации, тип документа, ФИО регистратора и название организации для документов, зарегистрированных в указанный период.

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

Вариант 8

1. Вывести список сотрудников с указанной причиной увольнения.

2. Вывести список документов с датой регистрации в указанный период.

3. Вывести дату регистрации, причину увольнения, ФИО сотрудника
для документов, зарегистрированных в указанный период.

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

Вариант 9

1. Вывести список сотрудников, бравших отпуск указанного типа.

2. Вывести список документов с датой регистрации в указанный период.

3. Вывести дату регистрации, тип отпуска, ФИО сотрудника для документов, зарегистрированных в указанный период.

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

Вариант 10

1. Вывести список сотрудников с указанной должностью.

2. Вывести список документов, в содержании которых встречается указанный фрагмент слова.

3. Вывести дату регистрации, тип документа, ФИО отправителя и название организации для документов, зарегистрированных в указанный период.

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

Вариант 11

1. Вывести список сотрудников, назначенных на указанную должность.

2. Вывести список документов с датой регистрации в указанный период.

3. Вывести дату регистрации, должность, ФИО сотрудника для документов, зарегистрированных в указанный период.

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

Вариант 12

1. Вывести список оборудования с указанным типом.

2. Вывести список оборудования, которое брал в прокат определенный
клиент.

3. Вывести список лиц, бравших оборудование в прокат и количество
их обращений, отсортированный по количеству обращений по убыванию.

4. Вывести информацию о клиентах, отсортированных по адресам.

Вариант 13

1. Вывести список оборудования с указанным типом.
2. Вывести список оборудования, которое списал определенный сотрудник.

3. Вывести количество списанного оборудования, сгруппированного по
типам оборудования.

4. Вывести информацию о сотрудниках с датой приема на работу
больше определенной даты.

Вариант 14

1. Вывести список цветков с указанным типом листа.

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

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

4. Вывести список цветов и сорт для цветов с высотой больше определенного числа или цветущий.

Вариант 15

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

2. Вывести общую сумму оплат за номера для каждого клиента.

3. Вывести дату заезда, тип номера, ФИО клиентов, зарегистрированных в указанный период.

4. Вывести список зарегистрированных клиентов в номерах определенного типа.

Вариант 16

1. Вывести список оборудования с указанным типом.

2. Вывести список оборудования, которое брал в прокат определенный
клиент.

3. Вывести список лиц, бравших оборудование в прокат и количество
их обращений, отсортированных по количеству обращений по убыванию.

4. Вывести информацию о клиентах, отсортированных по адресам.

Вариант 17

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

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

3. Вывести сумму стоимости ценностей с кодом в указанном диапазоне.

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

Вариант 18

1. Вывести список ремонтных работ, выполненных определенным мастером.

2. Вывести список этапов работ, входящих в работы, в названии которых встречается указанное слово.

3. Вывести сумму стоимости этапов ремонтных работ для работ с кодом в указанном диапазоне.

4. Вывести список мастеров с датой приема на работу в указанном диапазоне.

Вариант 19

1. Вывести список лекарств с определенным показанием.

2. Вывести список номеров чеков, по которым продано больше определенного числа лекарств.

3. Вывести дату продажи, сумму, ФИО кассира и лекарство по чеку с
указанным номером.

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

Вариант 20

1. Вывести список сотрудников с указанной должностью.

2. Вывести список документов, в содержании которых встречается указанный фрагмент слова.

3. Вывести дату регистрации, тип документа, ФИО исполнителя и факт
исполнения для документов, зарегистрированных в указанный период.

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

Назад: СОЗДАНИЕ ХРАНИМЫХ ПРОЦЕДУР В MICROSOFT SQL SERVER

Выполнение операторов SQL, которые не возвращают данные

При выполнении действий с базой данных, которые не возвращают данные, используйте Exec или
Метод ExecContext из пакета database/sql . Операторы SQL, которые вы
выполнить таким образом, включая INSERT , DELETE и UPDATE .

Если ваш запрос может возвращать строки, используйте метод Query или QueryContext .
вместо. Дополнительные сведения см. в разделе Запрос к базе данных.

Метод ExecContext работает так же, как метод Exec , но с дополнительным
аргумент context.Context , как описано в
Отмена незавершенных операций.

Код в следующем примере использует
DB.Exec для выполнения
оператор для добавления нового альбома записей в таблицу альбомов .

 функция AddAlbum (альбом альбома) (int64, ошибка) {
    результат, ошибка := db.Exec("ВСТАВИТЬ В альбом (название, исполнитель) ЗНАЧЕНИЯ (?, ?)", alb.Title, alb.Artist)
    если ошибка != ноль {
        вернуть 0, fmt.Errorf("AddAlbum: %v", ошибка)
    }
    // Получить сгенерированный идентификатор нового альбома для клиента. 
    идентификатор, ошибка := результат.LastInsertId()
    если ошибка != ноль {
        вернуть 0, fmt.Errorf("AddAlbum: %v", ошибка)
    }
    // Возвращаем идентификатор нового альбома.
    идентификатор возврата, ноль
}
 

DB.Exec возвращает значения: sql.Result
и ошибка. Когда ошибка nil , вы можете использовать результат , чтобы получить идентификатор.
последнего вставленного элемента (как в примере) или для получения количества строк
пострадала от операции.

Примечание. Заполнители параметров в подготовленных операторах различаются в зависимости от
СУБД и драйвер, которые вы используете. Например,
Драйвер pq для Postgres требует
заполнитель вроде $1 вместо ? .

Если ваш код будет многократно выполнять один и тот же оператор SQL, рассмотрите
используя sql.Stmt для создания повторно используемого подготовленного оператора из SQL
заявление. Дополнительные сведения см. в разделе Использование подготовленных операторов.

Внимание! Не используйте функции форматирования строк, такие как fmt.Sprintf
для сборки оператора SQL! Вы можете ввести риск SQL-инъекции.
Дополнительные сведения см. в статье Как избежать риска внедрения кода SQL.

Функции для выполнения операторов SQL, которые не возвращают строки

Функция	Описание
DB.Exec DB.ExecContext	Изолировано выполнить один оператор SQL.
Tx.Exec Tx.ExecContext	Выполнение оператора SQL в рамках более крупной транзакции. Подробнее см. Выполнение транзакций.
Stmt.Exec Stmt.ExecContext	Выполнить уже подготовленный оператор SQL. Подробнее см. Использование готовых операторов.
Соединение.ExecContext	Для использования с зарезервированными соединениями. Подробнее см. Управление соединениями.

Использование динамического SQL в COBOL

Использование динамического SQL в COBOL

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

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

SELECT с переменным списком требует использования SQLDA и адресных функций.
(переменные-указатели COBOL). Для других типов динамического SQL могут потребоваться
особенности при определенных условиях.

Область дескрипторов SQL (SQLDA) — это структура данных, которая содержит
информация о выполнении динамических операторов SQL. См.
Справочник по SQL для получения дополнительной информации о включении
SQLDA в вашей программе. Также см. документацию по COBOL для объяснения
использование адресных функций.

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

• НАЧАЛО ОБЪЯВЛЕНИЯ СЕКЦИИ
• ПОЛУЧИТЬ
• ЗАКРЫТЬ
• ВКЛЮЧАЕТ
• ЗАЯВИТЬ
• ОТКРЫТЬ
• ОПИСАТЬ
• ПОДГОТОВКА
• КОНЕЦ ОБЪЯВЛЕНИЯ СЕКЦИИ
• ВЫБЕРИТЕ
• ВЫПОЛНИТЬ
• SET переменная хоста
• ВЫПОЛНИТЬ НЕМЕДЛЕННО
• КОГДА

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

Для динамического выполнения оператора, отличного от SELECT:

1. (Необязательно) Протестируйте каждую инструкцию SQL, используя
   Интерактивный
   SQL, чтобы убедиться, что получены правильные результаты.
2. Загрузите оператор SQL в область данных.
3. Выполните инструкцию PREPARE, а затем EXECUTE (или EXECUTE IMMEDIATE).

   Обязательно обработайте все возникшие ошибки.

В следующем примере выполняется инструкция UPDATE, которая дает каждому
сотрудник повышение на 10 процентов:

 ПЕРЕМЕСТИТЬ 'обновить набор зарплаты сотрудника = зарплата * 1,10' В SQLCMD.
     EXEC SQL
         ВЫПОЛНИТЬ НЕМЕДЛЕННО :sqlcmd
     END-EXEC
     ... 

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

Чтобы динамически выполнить оператор SELECT с фиксированным списком:

1. (Необязательно) Протестируйте каждую инструкцию SQL, используя
   Интерактивный
   SQL, чтобы убедиться, что получены правильные результаты.
2. Загрузите оператор SQL в область данных.
3. Используйте PREPARE, чтобы проверить оператор и преобразовать его в
   исполняемая форма.
4. Используйте DECLARE, чтобы объявить курсор для инструкции.
5. Используйте OPEN, чтобы открыть курсор.
6. Используйте FETCH для извлечения строки из фиксированного списка переменных.
7. Когда достигнут конец данных, используйте CLOSE, чтобы закрыть курсор.

   Обязательно обработайте все ошибки.

В следующем примере показано, как выполнить оператор SELECT с фиксированным списком.
динамически.

 * включить тетрадь SQLDA
     EXEC SQL
         ВКЛЮЧИТЬ SQLDA
     END-EXEC

* присвоить значение переменной sqlcmd. 
     MOVE 'выберите e_no, lname from employee where dept="1050"' TO sqlcmd.

* подготовить команду SELECT.
     EXEC SQL
         ПОДГОТОВЬТЕ q1 ИЗ :sqlcmd
     END-EXEC

* объявить курсор для команды SELECT.
     EXEC SQL
         ОБЪЯВИТЬ c1 КУРСОР ДЛЯ q1
     END-EXEC

* открыть курсор.
     EXEC SQL
         ОТКРЫТЫЙ c1
     END-EXEC

* выборка данных в программные переменные EMPNO, LASTNME и FIRSTNME.
     EXEC SQL
         ВЫБЕРИТЕ c1 В :EMPNO, :LASTNME
     END-EXEC

* закрыть курсор.
     EXEC SQL
         ЗАКРЫТЬ c1
     КОНЕЦ-ВЫПОЛНЕНИЕ 

Оператор SELECT с переменным списком извлекает элементы данных
различного количества и формата. Поскольку количество и тип элементов данных не
известно, вы не можете определить переменные хоста заранее.

Однако, включив SQLDA в свою программу, вы можете использовать PREPARE для
определите оператор SELECT и за ним следует оператор DESCRIBE. Этот процесс
вставляет информацию об элементе данных в SQLDA, которую ваша программа может использовать для
выполнить оператор SELECT. Точно так же выборка записей выполняется с использованием
SQLDA.

Чтобы выполнить оператор SELECT с переменным списком:

1. Включите тетрадь SQLDA в свою программу.
2. Используйте PREPARE для определения инструкции (либо уже в инструкции
   буфер или для чтения), чтобы его можно было преобразовать в исполняемую форму.
3. Используйте DESCRIBE для подготовленного оператора, чтобы определить, сколько столбцов
   будут получены и их типы данных. Вы можете использовать PREPARE INTO, чтобы ПОДГОТОВИТЬСЯ
   и DESCRIBE в одном выражении.
4. Если SQLDA недостаточно велика, чтобы содержать описания столбцов для
   каждого столбца, ваша программа должна определить, сколько места необходимо, получить, что
   пространство для хранения, создайте новую SQLDA и снова DESCRIBE.
5. Теперь программа должна определить, сколько места необходимо для данных
   в каждом столбце (и, возможно, индикаторная переменная). Хранилище должно быть получено
   так что, когда значение будет получено, ваша программа будет знать, куда поместить данные.
   Получение этого хранилища осуществляется путем анализа SQLDA. Для большего
   информацию о SQLDA см. в SQL Option SQL
   Ссылка.
6. Выполните инструкцию SELECT, объявив и открыв курсор для
   подготовленное заявление.
7. Получить строку данных, указав USING DESCRIPTOR SQLDA в FETCH
   заявление.
8. Когда достигнут конец данных, закрыть курсор.

В следующем фрагменте программы показано, как выполнить команду SELECT с переменным списком.
заявление динамически.

 * включить тетрадь SQLDA.
 EXEC SQL
 ВКЛЮЧИТЬ SQLDA
 END-EXEC
* присвоить значение переменной sqlcmd.
 MOVE 'выбрать * из сотрудника, где отдел = "1050"' В sqlcmd.
* подготовить команду SELECT.
 EXEC SQL
 ПОДГОТОВЬТЕ q1 ИЗ :sqlcmd
 END-EXEC
* выделить место для SQLDA. ..
 ДВИГАЙТЕСЬ 20 В SQLN.
* описать команду SELECT.
 EXEC SQL
 ОПИСАТЬ q1 В SQLDA
 END-EXEC
* убедитесь, что SQLDA достаточно велика.
 ЕСЛИ (SQLN <= SQLD)
 ВЫПОЛНЯТЬ ...
 ...
 ПЕРЕМЕСТИТЕ SQLD В SQLN.
 EXEC SQL
 ОПИСАТЬ q1 В SQLDA
 END-EXEC
 КОНЕЦ ВЫПОЛНЕНИЯ
 КОНЕЦ ЕСЛИ  * анализировать результаты DESCRIBE.
...
* выделить память для хранения одной строки результата. ...
* объявить курсор для команды SELECT.
 EXEC SQL
 ОБЪЯВИТЬ c1 КУРСОР ДЛЯ q1
 END-EXEC  * открыть курсор.
 EXEC SQL
 ОТКРЫТЫЙ c1
 END-EXEC  * выборка данных в программные переменные EMPNO, LASTNME и FIRSTNME.
 EXEC SQL
 FETCH c1 ИСПОЛЬЗОВАНИЕ дескриптора SQLDA
 END-EXEC  * отображать результаты выборки следующей записи, если она существует.
 ВЫПОЛНЯТЬ ДО SQLCODE <> 0
 ОТОБРАЖАТЬ ...
 EXEC SQL
 FETCH c1 ИСПОЛЬЗОВАНИЕ дескриптора SQLDA
 END-EXEC
 КОНЕЦ ВЫПОЛНЕНИЯ
 ОТОБРАЖЕНИЕ 'КОНЕЦ СПИСКА'.