Alter procedure sql что это

Изменение хранимой процедуры

В этой статье описывается изменение хранимой процедуры в SQL Server с помощью SQL Server Management Studio или Transact-SQL.

ограничения

Хранимые процедуры Transact-SQL нельзя преобразовать в хранимые процедуры CLR, и наоборот.

Если предыдущее определение процедуры было создано с параметрами WITH ENCRYPTION или WITH RECOMPILE, эти параметры будут включены только в случае, если они указаны в инструкции ALTER PROCEDURE.

Permissions

Необходимо разрешение ALTER PROCEDURE на процедуру.

Использование SQL Server Management Studio

Чтобы изменить процедуру в SQL Server Management Studio, выполните следующие действия:

В обозревателе объектов подключитесь к экземпляру Компонент Database Engine и разверните его.
Последовательно разверните узел Базы данных, базу данных, которой принадлежит процедура, и узел Программирование.
Разверните Хранимые процедуры, щелкните правой кнопкой мыши изменяемую процедуру, затем выберите Изменить.
Изменение текста хранимой процедуры.
Для проверки синтаксиса выберите пункт Выполнить анализ в меню Запрос.
Чтобы сохранить изменения определения процедуры, выберите пункт Выполнить в меню Запрос.
Чтобы сохранить обновленное определение процедуры в виде скрипта Transact-SQL, в меню Файл выберите Команду Сохранить как. Примите предложенное имя файла или замените его новым, затем нажмите кнопку Сохранить.

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

Использование Transact-SQL

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

В обозревателе объектовподключитесь к экземпляру компонента Компонент Database Engine и разверните его.
Разверните узел Базы данных, разверните базу данных, в которой находится процедура. Также можно выбрать необходимую базу данных из списка доступных баз данных на панели инструментов. В данном примере выберите базу данных AdventureWorks2022 .
В меню Файл выберите Создать запрос.
Скопируйте и вставьте следующий пример в редактор запросов. В этом примере будет создана процедура uspVendorAllInfo , которая возвращает для всех поставщиков в базе данных Компания Adventure Works Cycles их имена, поставляемую продукцию, кредитные рейтинги и доступность.

IF OBJECT_ID ( 'Purchasing.uspVendorAllInfo', 'P' ) IS NOT NULL DROP PROCEDURE Purchasing.uspVendorAllInfo; GO CREATE PROCEDURE Purchasing.uspVendorAllInfo WITH EXECUTE AS CALLER AS SET NOCOUNT ON; SELECT v.Name AS Vendor, p.Name AS 'Product name', v.CreditRating AS 'Rating', v.ActiveFlag AS Availability FROM Purchasing.Vendor v INNER JOIN Purchasing.ProductVendor pv ON v.BusinessEntityID = pv.BusinessEntityID INNER JOIN Production.Product p ON pv.ProductID = p.ProductID ORDER BY v.Name ASC; GO

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

ALTER PROCEDURE Purchasing.uspVendorAllInfo @Product varchar(25) AS SET NOCOUNT ON; SELECT LEFT(v.Name, 25) AS Vendor, LEFT(p.Name, 25) AS 'Product name', 'Rating' = CASE v.CreditRating WHEN 1 THEN 'Superior' WHEN 2 THEN 'Excellent' WHEN 3 THEN 'Above average' WHEN 4 THEN 'Average' WHEN 5 THEN 'Below average' ELSE 'No rating' END , Availability = CASE v.ActiveFlag WHEN 1 THEN 'Yes' ELSE 'No' END FROM Purchasing.Vendor AS v INNER JOIN Purchasing.ProductVendor AS pv ON v.BusinessEntityID = pv.BusinessEntityID INNER JOIN Production.Product AS p ON pv.ProductID = p.ProductID WHERE p.Name LIKE @Product ORDER BY v.Name ASC; GO

EXEC Purchasing.uspVendorAllInfo N'LL Crankarm'; GO

Следующие шаги

ALTER PROCEDURE (Transact-SQL)
Атака путем внедрения кода SQL

Предложение alter procedure

Это SQL-предложение применяется для изменения уже существующей хранимой процедуры. Предложение ALTER PROCEDURE имеет тот же синтаксис, что и предложение CREATE PROCEDURE. Один из примеров, когда нужно изменить хранимую процедуру, — зашифровать ее исходный текст после того, как процедура отлажена, с целью скрыть ее исходный текст от других пользователей.

Если хранимая процедура больше не нужна, мы можем удалить ее с помощью предложения DROP PROCEDURE, которое будет обсуждаться ниже.

40.Мова xml. Визначення схеми документу за допомогою xsd. Приклад схеми та документу, що задовольняє цій схемі. Приклад програмної валідації xml документу.

XML (англ. eXtensible Markup Language — расширяемый язык разметки; произносится [экс-эм-э́л]) — рекомендованный Консорциумом Всемирной паутины язык разметки, фактически представляющий собой свод общих синтаксических правил. XML — текстовый формат, предназначенный для хранения структурированных данных (взамен существующих файлов баз данных), для обмена информацией между программами, а также для создания на его основе более специализированных языков разметки (например, XHTML). XML является упрощённым подмножеством языка SGML.

XML Schema — язык описания структуры XML-документа. Спецификация XML Schema является рекомендацией W3C.

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

После проверки документа на соответствие XML Schema читающая программа может создать модель данных документа, которая включает:

словарь (названия элементов и атрибутов);
модель содержания (отношения между элементами и атрибутами и их структура);
типы данных.

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

Другим удобством XML Schema является то, что один словарь может ссылаться на другой, и, таким образом, разработчик может использовать уже существующие словари и легче устанавливать и распространять стандарты XML структуры для определённых задач (например, словарь протокола SOAP).

Простой пример схемы на XML Schema, расположенной в файле «country.xsd» и описывающей данные о населении страны:

xmlns:xs=»http://www.w3.org/2001/XMLSchema»>

Пример документа, соответствующего этой схеме:

Валидация – это процесс проверки того, что документ XML соответствует набору правил, определенных в схеме. В настоящее время разные парсеры и библиотеки поддерживают разные языки описания схем, такие как XML Schema, RELAX NG и Schematron. В Java 5 появился унифицированный прикладной интерфейс (API), позволяющий проверять документы на соответствие схемам, описанным на этих и других языках. Узнать о работе с этим интерфейсом вы сможете, прочитав эту статью.

ALTER PROCEDURE (Transact-SQL)

Изменяет ранее созданную процедуру, созданную с помощью инструкции CREATE PROCEDURE в SQL Server.

Синтаксис

-- Syntax for SQL Server and Azure SQL Database ALTER < PROC | PROCEDURE >[schema_name.] procedure_name [ ; number ] [ < @parameter_name [ type_schema_name. ] data_type >[ VARYING ] [ = default ] [ OUT | OUTPUT ] [READONLY] ] [ . n ] [ WITH [ . n ] ] [ FOR REPLICATION ] AS < [ BEGIN ] sql_statement [;] [ . n ] [ END ] >[;] ::= [ ENCRYPTION ] [ RECOMPILE ] [ EXECUTE AS Clause ]

-- Syntax for SQL Server CLR Stored Procedure ALTER < PROC | PROCEDURE >[schema_name.] procedure_name [ ; number ] [ < @parameter_name [ type_schema_name. ] data_type >[ = default ] [ OUT | OUTPUT ] [READONLY] ] [ . n ] [ WITH EXECUTE AS Clause ] AS < EXTERNAL NAME assembly_name.class_name.method_name >[;]

-- Syntax for Azure Synapse Analytics and Parallel Data Warehouse and Microsoft Fabric ALTER < PROC | PROCEDURE >[schema_name.] procedure_name [ < @parameterdata_type >[= ] ] [ . n ] AS < [ BEGIN ] sql_statement [ ; ] [ . n ] [ END ] >[;]

Сведения о синтаксисе Transact-SQL для SQL Server 2014 (12.x) и более ранних версиях см . в документации по предыдущим версиям.

Аргументы

schema_name
Имя схемы, которой принадлежит процедура.

procedure_name
Имя процедуры, которую нужно изменить. Имена процедур должны соответствовать правилам для идентификаторов.

;number
Существующий необязательный аргумент целочисленного типа, который применяется для объединения процедур с одинаковым именем и позволяет удалять их одновременно при помощи инструкции DROP PROCEDURE.

В будущей версии Microsoft SQL Server этот компонент будет удален. Избегайте использования этого компонента в новых разработках и запланируйте изменение существующих приложений, в которых он применяется.

@parameter_name
Параметр в процедуре. Можно указать до 2100 параметров.

[ type_schema_name. ] data_type
Тип данных параметра и схема, которой он принадлежит.

Дополнительные сведения об ограничениях типов данных см. в разделе CREATE PROCEDURE (Transact-SQL).

VARYING
Указывает результирующий набор, поддерживаемый в качестве выходного параметра. Этот параметр формируется динамически хранимой процедурой, и его содержимое может меняться. Область применения — только параметры cursor. Этот параметр недопустим для процедур CLR.

default
Значение по умолчанию для аргумента.

OUT | OUTPUT
Указывает, что параметр является выходным.

READONLY
Указывает, что параметр не может быть обновлен или изменен в тексте процедуры. Если тип параметра является возвращающим табличное значение типом, то должно быть указано ключевое слово READONLY.

RECOMPILE
Указывает, что ядро СУБД не кэширует план для этой процедуры, и процедура перекомпилируется во время выполнения.

ШИФРОВАНИЕ
Область применения: SQL Server (SQL Server 2008 (10.0.x) и более поздних версий) и База данных SQL Azure.

Указывает, что ядро СУБД преобразует исходный текст инструкции ALTER PROCEDURE в нефускированный формат. Выходные данные обфускации не отображаются непосредственно в каких-либо представлениях каталога в SQL Server. Пользователи, не имеющие доступа к системным таблицам или файлам баз данных, не смогут получить скрытый текст. Однако этот текст будет доступен привилегированным пользователям, которые либо смогут обращаться к системным таблицам через порт DAC, либо будут иметь непосредственный доступ к файлам баз данных. Кроме того, пользователи, имеющие право на подключение отладчика к серверному процессу, могут получить исходный текст процедуры из памяти во время выполнения. Дополнительные сведения о доступе к метаданным системы см. в статье Настройка видимости метаданных.

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

Этот параметр не может быть указан в хранимой процедуре среды CLR.

Во время обновления ядро СУБД использует скрытые комментарии, хранящиеся в sys.sql_modules, для повторного создания процедур.

EXECUTE AS
Определяет контекст безопасности, в котором должна выполняться хранимая процедура при обращении к ней.

Дополнительные сведения см. в разделе EXECUTE AS (Transact-SQL).

Указывает, что хранимые процедуры, созданные для репликации, не могут выполняться на подписчике. Хранимая процедура, созданная с аргументом FOR REPLICATION, используется в качестве фильтра и выполняется только в процессе репликации. Параметры не могут быть объявлены, если указан параметр FOR REPLICATION. Этот параметр недопустим для процедур CLR. Параметр RECOMPILE не учитывается для процедур, созданных с параметром FOR REPLICATION.

Этот параметр недоступен в автономной базе данных.

< [ BEGIN ] sql_statement [;] [ . n ] [ END ] >
Одна или несколько инструкций Transact-SQL, составляющих текст процедуры. Инструкции можно заключить в необязательные ключевые слова BEGIN и END. Дополнительные сведения см. в подразделах «Рекомендации», «Общие замечания» и «Ограничения» раздела CREATE PROCEDURE (Transact-SQL).

EXTERNAL NAME assembly_name.class_name.method_name
Применимо: SQL Server 2008 (10.0.x) и более поздних версий.

Указывает метод сборки платформа .NET Framework для хранимой процедуры CLR для ссылки. class_name должен быть допустимым идентификатором SQL Server и должен существовать в качестве класса в сборке. Если для класса через точку (.) указано пространство имен, имя класса должно быть выделено квадратными скобками ([]) или кавычками («»). Указанный метод класса должен быть статическим.

По умолчанию SQL Server не может выполнять код CLR. Можно создавать, изменять и удалять объекты базы данных, ссылающиеся на модули среды CLR; Однако эти ссылки нельзя выполнить в SQL Server, пока не включите параметр clr. Для включения параметра используйте хранимую процедуру sp_configure.

Процедуры CLR не поддерживаются в автономной базе данных.

Общие замечания

Хранимые процедуры Transact-SQL нельзя преобразовать в хранимые процедуры CLR, и наоборот.

Инструкция ALTER PROCEDURE не изменяет разрешения и не влияет на хранимые процедуры и триггеры. Тем не менее при изменении в хранимую процедуру включаются текущие значения для параметров сеанса QUOTED_IDENTIFIER и ANSI_NULLS. Если при создании хранимой процедуры использовались другие значения параметров, ее поведение может измениться.

Если предыдущее определение процедуры было создано с параметрами WITH ENCRYPTION или WITH RECOMPILE, эти параметры будут включены только в том случае, если они указаны в инструкции ALTER PROCEDURE.

Дополнительные сведения о хранимых процедурах см. в статье CREATE PROCEDURE (Transact-SQL).

Безопасность

Разрешения

Требуется разрешение ALTER на процедуру или членство в предопределенной роли базы данных db_ddladmin.

Примеры

В следующем примере создается хранимая процедура uspVendorAllInfo . Эта процедура возвращает имена всех поставщиков, которые предоставляют Adventure Works Cycles, продукты, которые они предоставляют, их кредитные рейтинги и их доступность. После создания процедура изменяется и возвращает другой результирующий набор.

IF OBJECT_ID ( 'Purchasing.uspVendorAllInfo', 'P' ) IS NOT NULL DROP PROCEDURE Purchasing.uspVendorAllInfo; GO CREATE PROCEDURE Purchasing.uspVendorAllInfo WITH EXECUTE AS CALLER AS SET NOCOUNT ON; SELECT v.Name AS Vendor, p.Name AS 'Product name', v.CreditRating AS 'Rating', v.ActiveFlag AS Availability FROM Purchasing.Vendor v INNER JOIN Purchasing.ProductVendor pv ON v.BusinessEntityID = pv.BusinessEntityID INNER JOIN Production.Product p ON pv.ProductID = p.ProductID ORDER BY v.Name ASC; GO

В следующем примере изменяется хранимая процедура uspVendorAllInfo . Здесь удаляется предложение EXECUTE AS CALLER и изменяется текст процедуры, чтобы возвращать только поставщиков, предлагающих указанный товар. Содержимое результирующего набора определяется при помощи функций LEFT и CASE .

USE AdventureWorks2022; GO ALTER PROCEDURE Purchasing.uspVendorAllInfo @Product VARCHAR(25) AS SET NOCOUNT ON; SELECT LEFT(v.Name, 25) AS Vendor, LEFT(p.Name, 25) AS 'Product name', 'Rating' = CASE v.CreditRating WHEN 1 THEN 'Superior' WHEN 2 THEN 'Excellent' WHEN 3 THEN 'Above average' WHEN 4 THEN 'Average' WHEN 5 THEN 'Below average' ELSE 'No rating' END , Availability = CASE v.ActiveFlag WHEN 1 THEN 'Yes' ELSE 'No' END FROM Purchasing.Vendor AS v INNER JOIN Purchasing.ProductVendor AS pv ON v.BusinessEntityID = pv.BusinessEntityID INNER JOIN Production.Product AS p ON pv.ProductID = p.ProductID WHERE p.Name LIKE @Product ORDER BY v.Name ASC; GO

Vendor Product name Rating Availability -------------------- ------------- ------- ------------ Proseware, Inc. LL Crankarm Average No Vision Cycles, Inc. LL Crankarm Superior Yes (2 row(s) affected)`

Часть 7. Представления, триггеры и хранимые процедуры

В предыдущей статье данного цикла мы рассмотрели, как можно извлечь данные из таблиц, модифицировать их структуру, создавать, модифицировать и удалять базы данных и объекты, в них содержащиеся. В настоящей статье мы поговорим более подробно об объектах, характерных для серверных СУБД: представлениях, триггерах и хранимых процедурах.

В первой статье данного цикла, опубликованной в № 3’2000 нашего журнала, мы отмечали, что большинство современных серверных СУБД поддерживают представления, триггеры и хранимые процедуры. Представления также поддерживаются и многими настольными СУБД, например Access, dBase, Clipper.

Следует отметить, что триггеры и хранимые процедуры обычно пишутся на языках программирования, представляющих собой процедурные расширения языка SQL. Эти расширения содержат операторы, позволяющие описывать алгоритмы, например do…while, if…then…else, отсутствующие в самом языке SQL (если вы помните, SQL — непроцедурный язык, и на нем можно сформулировать задание, но нельзя описывать алгоритмы его выполнения). В отличие от языка SQL, подчиняющегося стандарту, его процедурные расширения никак не стандартизованы, и разные СУБД используют разные синтаксические конструкции для реализации одних и тех же алгоритмических конструкций, но обсуждение различий в синтаксисе расширений SQL для разных СУБД выходит за рамки данной статьи.

Для иллюстрации того, как можно использовать представления, триггеры и хранимые процедуры, мы выбрали Microsoft SQL Server 7.0 и базу данных NorthWind, входящую в комплект поставки этой СУБД.

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

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

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

Представления

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

В большинстве случаев представления используются для обеспечения безопасности данных. Например, некоторые категории пользователей могут иметь доступ к представлению, но не к таблицам, данные которых его формируют; кроме того, SQL-запрос может содержать параметр USER (имя, под которым зарегистрировался пользователь), и в этом случае данные, доступные при обращении к представлению, будут зависеть от имени пользователя.

Ниже перечислены основные характеристики представлений:

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

Для создания представления мы можем использовать SQL-предложение CREATE VIEW, для его модификации — предложение ALTER VIEW, а для удаления его — предложение DROP VIEW.

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

Предложение CREATE VIEW

Синтаксис предложения для создания представления напоминает SQL-предложение SELECT с несколькими дополнительными ключевыми словами. Ниже приведен его упрощенный синтаксис:

CREATE VIEW view_name [WITH ENCRYPTION] AS select_statement

Аргумент view_name указывает на имя представления. Ключевое слово [WITH ENCRYPTION], используемое в Microsoft SQL Server, позволяет скрыть исходный текст предложения CREATE VIEW в таблице syscomments.

Ключевое слово AS указывает, какой запрос SELECT реально будет выполняться при обращении к представлению. Обратите внимание на то, что этот запрос не может содержать ключевых слов ORDER BY, COMPUTE или COMPUTE BY, INTO и не может ссылаться на временную таблицу.

Для модификации созданного ранее представления следует использовать предложение ALTER VIEW, кратко описанное в следующем разделе.

Предложение ALTER VIEW

Предложение ALTER VIEW имеет тот же синтаксис, что и предложение CREATE VIEW. Его основное назначение — внести изменения в уже имеющееся представление.

Если представление нам больше не нужно, мы можем удалить его с помощью оператора DROP VIEW, описанного ниже.

Предложение DROP VIEW

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

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

Создание и использование представлений

Предложение CREATE VIEW используется для создания представлений, позволяющих извлекать данные, удовлетворяющие определенным требованиям. Представление создается в текущей базе данных и хранится как ее отдельный объект.

Наилучший способ создать представление — создать запрос SELECT и, проверив его, добавить недостающую часть предложения CREATE VIEW. Давайте рассмотрим исходный текст представления Products by Category в базе данных NorthWind (листинг 1).

Первая строка, выделенная жирным шрифтом, представляет собой то, чем отличается SQL-предложение для создания представления от обычного запроса SELECT, выполняющего работу по выбору данных. Предложение SELECT, содержащееся в этом представлении, выбирает поля из двух таблиц — поле CategoryName из таблицы CATEGORIES и поля ProductName, QuantityPerUnit, UnitsInStock, Discontinued из таблицы PRODUCTS. После этого данные двух таблиц связываются по полю CategoryID, и только те продукты, которые еще имеются на складе (см. критерий после ключевого слова WHERE), включаются в результирующий набор данных. Результат обращения к этому представлению показан на рис. 1.

Теперь давайте создадим представление, показывающее все территории восточного региона. Это представление базируется на следующем запросе (листинг 2).

Убедившись в том, что предложение SELECT возвращает результаты, которые нам нужны, мы добавляем оператор CREATE VIEW и присваиваем создаваемому представлению имя EASTTERR (листинг 3).

Вместо создания текста представления вручную можно использовать визуальные инструменты, обычно входящие в состав СУБД. На рис. 2 показано, как то же самое представление может быть создано с помощью инструмента View Designer, который является составной частью Enterprise Manager, входящего в Microsoft SQL Server.

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

Прежде чем мы закончим краткий обзор представлений, поговорим немного о том, как получить дополнительную информацию о них. В Microsoft SQL Server 7.0 мы можем использовать следующие системные хранимые процедуры:

для получения сведений о представлении можно использовать системную хранимую процедуру sp_help. Например, sp_help EastTerr вернет сведения о только что созданном представлении;
для получения исходного текста представления можно использовать хранимую процедуру sp_helptext;
для того чтобы найти список таблиц, от которого зависит представление, можно использовать системную хранимую процедуру sp_depends;
для переименования представления можно использовать системную хранимую процедуру sp_rename.

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

Хранимые процедуры

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

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

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

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

Для создания, изменения и удаления процедур существуют специальные SQL-предложения — CREATE PROCEDURE, ALTER PROCEDURE и DROP PROCEDURE. Мы рассмотрим их в следующем разделе.

Предложение CREATE PROCEDURE

Предложение CREATE PROCEDURE используется для создания хранимой процедуры. Оно имеет следующий упрощенный синтаксис:

CREATE PROC proc_name [ [= default] [OUTPUT] ] [. ] AS sql_statements

Аргумент proc_name устанавливает имя хранимой процедуры, которое должно быть уникально в рамках текущей базы данных. Аргумент @parameter определяет параметр процедуры. В предложении CREATE PROCEDURE можно определить один или более параметров. Если для параметра нет значения по умолчанию, он должен быть передан пользователем (или клиентским приложением) при вызове процедуры. В Microsoft SQL Server 7.0 число параметров хранимой процедуры не должно превышать 1024; по умолчанию они могут иметь NULL-значения.

Отметим, однако, что некоторые универсальные механизмы доступа к данным могут накладывать дополнительные ограничения на число параметров хранимых процедур. Например, BDE-драйвер для Oracle 8 способен работать только с процедурами, число параметров которых не превышает 10.

Аргумент data_type указывает тип данных для параметра. Ключевое слово default может быть использовано для установки значений по умолчанию — это может быть константа или NULL. Если указано значение по умолчанию, процедура может быть вызвана без указания значения параметра. Если процедура использует параметр с ключевым словом LIKE, ее значение по умолчанию может содержать групповые символы (%, _, [] и [^]).

Ключевое слово OUTPUT показывает, что это возвращаемый параметр.

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

Процедура, созданная с помощью предложения CREATE PROCEDURE, будет сохранена в текущей базе данных. В Microsoft SQL Server имена процедур содержатся в системной таблице sysobjects, а исходный текст — в таблице syscomments.

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

Предложение ALTER PROCEDURE

Предложение DROP PROCEDURE

Это предложение используется для удаления хранимых процедур из базы данных. Предложение DROP PROCEDURE принимает один аргумент — имя удаляемой процедуры.

При удалении хранимой процедуры сведения о ней удаляются из системных таблиц sysobjects и syscomments.

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

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

SELECT Territories.TerritoryDescription, Region.RegionDescription FROM Territories INNER JOIN Region ON Territories.RegionID = Region.RegionID WHERE Territories.RegionID = 1

Чтобы выбрать другой регион, нам нужно изменить условие в предложении WHERE в последней строке запроса. Следовательно, если мы используем переменную (назовем ее RegID), мы сможем выбрать один из четырех регионов без изменения других частей запроса.

В базе данных NorthWind четыре региона с номерами от 1 до 4. Это означает, что переменная RegID должна быть целого типа. Код хранимой процедуры приведен ниже:

CREATE PROCEDURE ShowRegion @RegID int AS SELECT Territories.TerritoryDescription, Region.RegionDescription FROM Territories INNER JOIN Region ON Territories.RegionID = Region.RegionID WHERE Territories.RegionID = @RegID

Обратите внимание на то, что мы оставили почти весь текст запроса SELECT нетронутым (он выделен курсивом) и только добавили предложение CREATE PROCEDURE с именем вновь созданной хранимой процедуры (в первой строке), объявление параметра (во второй строке) и ключевое слово AS, указывающее на начало предложений, реально выполняющих действия.

Результат выполнения созданной процедуры в SQL Server Query Analyzer для RegID =2 показан на рис. 3.

Очевидно, что мы можем применять хранимые процедуры не только для реализации расширенных версий представлений или «интеллектуальных» запросов SELECT. Хранимые процедуры предоставляют механизмы, позволяющие автоматизировать многие рутинные задачи.

В Microsoft SQL Server 7.0 мы также можем использовать системные хранимые процедуры для работы с обычными хранимыми процедурами:

sp_stored_procedures — показывает список хранимых процедур;
sp_helptext — показывает исходный текст хранимой процедуры;
sp_depends — показывает сведения о зависимостях хранимых процедур;
sp_procoption — устанавливает опции хранимых процедур или устанавливает их;
sp_recompile — перекомпилирует процедуру в момент ее следующего вызова;
sp_rename — меняет имя процедуры.

Системные хранимые процедуры

Поскольку мы говорим о Microsoft SQL Server, следует отметить огромное количество системных хранимых процедур, реализованных в нем. Имена системных хранимых процедур начинаются с SP_ или XP_ и хранятся в базе данных master. Выше мы уже описывали некоторые из часто используемых системных хранимых процедур.

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

Системные хранимые процедуры можно использовать в клиентских приложениях. Для этого следует обратиться к базе данных master и выбрать имя соответствующей процедуры.

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

Триггеры

В первой статье данного цикла мы кратко описывали, что такое триггеры и как они используются в базах данных. Дадим определение триггера. Tриггер — это специальный тип хранимой процедуры, которая автоматически вызывается, когда данные в определенной таблице добавляются, удаляются или изменяются с помощью SQL-предложений INSERT, DELETE или UPDATE. В зависимости от того, какое из событий, связанных с изменением данных, инициирует запуск триггера, он называется insert trigger, delete trigger или update trigger.

Некоторые базы данных позволяют создавать разные триггеры для выполнения до и после вставки, удаления или изменения записей. Обратите также внимание на то, что большинство СУБД позволяют создавать несколько триггеров для одного и того же события. В этом случае слеует определить порядок, в котором они будут выполняться.

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

Поскольку триггер вызывается автоматически самой СУБД (в нашем случае Microsoft SQL Server), его нельзя вызвать из клиентского приложения.

Триггеры могут опосредованно вызывать другие триггеры. Например, если триггер, выполняющийся в данный момент, содержит код, модифицирующий другую таблицу, имеющую собственный триггер, последний будет запущен. В свою очредь, он может вызвать следующий триггер и так далее. Такая последовательность тригеров называется термином nested triggers. Microsoft SQL Server поддерживает до 32 уровней вложения таких триггеров.

Как и многие другие объекты баз данных, триггеры создаются с помощью предложения CREATE. Чтобы изменить триггер, мы используем предложение ALTER, а для удаления триггера — предложение DROP. В последующих разделах мы рассмотрим синтаксис этих предложений и затем создадим триггер, который может быть использован для записи сведений о мдификациях данных.

Предложение CREATE TRIGGER

Это предложение создает новый триггер для определеной таблицы для любого из предложений INSERT, DELETE или UPDATE. Ниже приведен упрощенный синтаксис такого предложения:

CREATE TRIGGER trigger_name ON table_name FOR [WITH ENCRYPTION] AS sql_statements

Аргумент trigger_name указывает имя триггера. Это имя должно быть уникально для базы данных. Аргумент table_name — имя таблицы, при модификации которой выполняется данный триггер. Таблица должна быть «настоящей» — нельзя указать имя представления вместо таблицы.

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

Необязательное ключевое слово [WITH ENCRYPTION] может быть использовано для сокрытия исходного кода триггера — в этом случае триггер не будет виден в таблице syscomments.

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

Обратите внимание на то, что триггеры не должны возвращать пользователю данные.

В предложении CREATE TRIGGER можно использовать две специальные таблицы. Например, таблицы deleted и inserted имеют ту же структуру, что и таблица, для которой определен триггер, и содержат старое и новое значения записей, измененных пользователем. Например, мы можем использовать следующее SQL-предложение для поиска удаленных записей:

SELECT * FROM deleted

В табл. 3 показано содержимое таблиц deleted и inserted для всех возможных изменений данных.

Для изменения имеющегося триггера следует использовать предложение ALTER TRIGGER. Мы поговорим о нем в следующем разделе.

Предложение ALTER TRIGGER

Данное предложение имеет тот же синтаксис, что и предложение CREATE TRIGGER, которое мы обсудим выше. Это предложение применяется для изменения текста имеющегося триггера. Например, если мы решили, что какой-либо триггер, определенный для всех трех типов модификаций данных, теперь не должен выполняться, когда записи удаляются, нам следует использовать тот же самый код, с помощью которого мы создавали триггер, но удалить ключевое слово UPDATE в предложении FOR.

Если триггер больше не нужен, мы можем удалить его из базы данных. Для этого применяется предложение DROP TRIGGER, которое мы рассмотрим ниже.

Предложение DROP TRIGGER

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

Создание и использование триггеров

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

Для начала нам нужно добавить к таблице два новых поля, в которых будут содержаться эти сведения. Назовем их UpdatedBy (имя менеджера, обновившего запись последним) и UpdatedWhen (время, когда была изменена запись). Затем создадим триггер с именем KeepTrack. Вот его код:

CREATE TRIGGER KeepTrack ON Customers FOR INSERT, UPDATE AS UPDATE Customers SET Customers.UpdatedBy = USER_NAME(), Customers.UpdatedWhen = GETDATE() FROM inserted, Customers WHERE inserted.CustomerID = Customers.CustomerID

Как видно из исходного текста триггера, он выполняется после каждой операции INSERT и UPDATE в таблице Customers. Этот триггер будет сохранять имя менеджера (пользователя базы данных) в поле Customers.UpdatedBy и дату и время изменения — в поле Customers.UpdatedWhen. Эти данные извлекаются из временной таблицы inserted.

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

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

Заключение

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

Представление — это виртуальная таблица, обычно создаваемая как подмножество столбцов одной или нескольких таблиц. Для создания представления применяется предложение CREATE VIEW, для модификации — предложение ALTER VIEW, а для удаления — предложение DROP VIEW.
Хранимая процедура — это скомпилированный набор SQL-предложений, сохраненный в базе данных как именованный объект и выполняющийся как единый фрагмент кода. Для создания хранимой процедуры применяется предложение CREATE PROCEDURE, для изменения — ALTER PROCEDURE, а для удаления — DROP PROCEDURE.
Tриггер — это специальный тип хранимой процедуры, которая автоматически вызывается, когда данные в определенной таблице добавляются, удаляются или изменяются с помощью SQL-предложений INSERT, DELETE или UPDATE. Триггеры создаются с помощью предложения CREATE TRIGGER. Для изменения триггера используется предложение ALTER TRIGGER, а для удаления — предложение DROP TRIGGER.

ПК и комплектующие

Настольные ПК и моноблоки
Портативные ПК
Серверы
Материнские платы
Корпуса
Блоки питания
Оперативная память
Процессоры
Графические адаптеры
Жесткие диски и SSD
Оптические приводы и носители
Звуковые карты
ТВ-тюнеры
Контроллеры
Системы охлаждения ПК
Моддинг
Аксессуары для ноутбуков

Принтеры, сканеры, МФУ
Мониторы и проекторы
Устройства ввода
Внешние накопители
Акустические системы, гарнитуры, наушники
ИБП
Веб-камеры
KVM-оборудование

Сетевые медиаплееры
HTPC и мини-компьютеры
ТВ и системы домашнего кинотеатра
Технология DLNA
Средства управления домашней техникой

Планшеты
Смартфоны
Портативные накопители
Электронные ридеры
Портативные медиаплееры
GPS-навигаторы и трекеры
Носимые гаджеты
Автомобильные информационно-развлекательные системы
Зарядные устройства
Аксессуары для мобильных устройств

Цифровые фотоаппараты и оптика
Видеокамеры
Фотоаксессуары
Обработка фотографий
Монтаж видео

Операционные системы
Средства разработки
Офисные программы
Средства тестирования, мониторинга и диагностики
Полезные утилиты
Графические редакторы
Средства 3D-моделирования

Веб-браузеры
Поисковые системы
Социальные сети
«Облачные» сервисы
Сервисы для обмена сообщениями и конференц-связи
Разработка веб-сайтов
Мобильный интернет
Полезные инструменты

Средства защиты от вредоносного ПО
Средства управления доступом
Защита данных

Проводные сети
Беспроводные сети
Сетевая инфраструктура
Сотовая связь
IP-телефония
NAS-накопители
Средства управления сетями
Средства удаленного доступа

Системная интеграция
Проекты в области образования
Электронный документооборот
«Облачные» сервисы для бизнеса
Технологии виртуализации

1999	1	2	3	4	5	6	7	8	9	10	11	12
2000	1	2	3	4	5	6	7	8	9	10	11	12
2001	1	2	3	4	5	6	7	8	9	10	11	12
2002	1	2	3	4	5	6	7	8	9	10	11	12
2003	1	2	3	4	5	6	7	8	9	10	11	12
2004	1	2	3	4	5	6	7	8	9	10	11	12
2005	1	2	3	4	5	6	7	8	9	10	11	12
2006	1	2	3	4	5	6	7	8	9	10	11	12
2007	1	2	3	4	5	6	7	8	9	10	11	12
2008	1	2	3	4	5	6	7	8	9	10	11	12
2009	1	2	3	4	5	6	7	8	9	10	11	12
2010	1	2	3	4	5	6	7	8	9	10	11	12
2011	1	2	3	4	5	6	7	8	9	10	11	12
2012	1	2	3	4	5	6	7	8	9	10	11	12
2013	1	2	3	4	5	6	7	8	9	10	11	12