Хранимая процедура — объект базы данных представляющий

Зарегистрируйтесь, чтобы просмотреть полный документ!

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

- объект базы данных, представляющий собой набор SQL-инструкций, который компилируется один раз и хранится на сервере - могут выполняться стандартные операции с базами данных (как DDL, так и DML). - хранимые процедуры должны быть вызваны с помощью функции CALL

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

$Синтаксис --SQL Server Stored Procedure Syntax CREATE { PROC | PROCEDURE } [schema_name. ]$

Синтаксис --SQL Server Stored Procedure Syntax CREATE { PROC | PROCEDURE } [schema_name. ] procedure_name [ ; number ] [ { @parameter [ type_schema_name. ] data_type } [ VARYING ] [ = default ] [ OUT | OUTPUT | [READONLY] ] [ , . . . n ] [ WITH <procedure_option> [ , . . . n ] ] [ FOR REPLICATION ] AS { [ BEGIN ] sql_statement [; ] [. . . n ] [ END ] } [; ] <procedure_option> : : = [ ENCRYPTION ] [ RECOMPILE ] [ EXECUTE AS Clause ]

Аргументы • schema_name - Имя схемы, которой принадлежит процедура. Процедуры привязаны к схеме. Если имя схемы не указано при создании процедуры, то автоматически назначается схема по умолчанию для пользователя, который создает процедуру. • procedure_name Имя процедуры. Имена процедур должны соответствовать требованиям, предъявляемым к идентификаторам, и должны быть уникальными в схеме. • ; number - Необязательный целочисленный аргумент, используемый для группирования одноименных процедур. Все сгруппированные процедуры можно удалить, выполнив одну инструкцию DROP PROCEDURE. • {[BEGIN]sql_statement[; ][. . . n][END]} Одна или несколько инструкций Transact-SQL, составляющих текст процедуры. Инструкции можно заключить в необязательные ключевые слова BEGIN и END.

Аргументы (продолжение) • @ parameter - Параметр, объявленный в процедуре. Укажите имя параметра, начинающееся со знака @. Имя параметра должно соответствовать правилам для идентификаторов. Параметры являются локальными в пределах процедуры; в разных процедурах могут быть использованы одинаковые имена параметров. • OUT | OUTPUT - Показывает, что параметр процедуры является выходным. Используются параметры OUTPUT для возврата значений в вызвавший процедуру код. • EXECUTE AS clause - Определяет контекст безопасности, в котором должна быть выполнена процедура. • VARYING - Указывает результирующий набор, поддерживаемый в качестве выходного параметра. Этот параметр динамически формируется процедурой, и его содержимое может различаться. Применяется только к параметрам типа cursor. Этот параметр недопустим для процедур CLR.

Рекомендации • Начинайте текст процедуры с инструкции SET NOCOUNT ON • При создании или упоминании объектов процедуре используйте имена схем. базы данных в • Не выполняйте обработку или передачу слишком большого объема данных и ограничивайте область результатов в коде процедуры. • Используйте явные транзакции, указывая ключевые слова BEGIN/END TRANSACTION, и по возможности сокращайте транзакции.

• Используйте функцию Transact-SQL TRY…CATCH для обработки ошибок в пределах процедуры. • Используйте ключевое слово DEFAULT для всех столбцов таблицы, на которые ссылаются инструкции Transact-SQL CREATE TABLE и ALTER TABLE в тексте процедуры. • Используйте ключевые слова NULL и NOT NULL для каждого столбца во временной таблице. • Используйте оператор UNION ALL вместо операторов UNION и OR, если нет необходимости получить уникальные значения.

Примеры CREATE PROCEDURE My. Proc AS UPDATE dbo. [Order Details] SET Quantity = 100 Для вызова процедуры: Execute My. Proc

CREATE PROCEDURE Delete. Employee @emp. Id INT, @counter INT OUTPUT AS SELECT @counter = COUNT(*) FROM Works_on WHERE Emp. Id = @emp. Id DELETE FROM Employee WHERE Id = @emp. Id DELETE FROM Works_on WHERE Emp. Id = @emp. Id; Для вызова процедуры: DECLARE @quantity. Delete. Employee INT; EXECUTE Delete. Employee @emp. Id=18316, @counter=@quantity. Delete. Employee OUTPUT; PRINT N'Удалено сотрудников: ' + convert(nvarchar(30), @quantity. Delete. Employee);

CREATE PROCEDURE Employees. In. Dept (@city varchar(10), @Year. Order. Date int) AS SELECT Last. Name, Birth. Date, City FROM dbo. Employees JOIN dbo. Orders ON dbo. Orders. Employee. ID = dbo. Employees. Employee. ID WHERE dbo. Employees. City = @city and YEAR(dbo. Orders. Order. Date) = @Year. Order. Date

CREATE PROCEDURE Production. usp. Delete. Work. Order ( @Work. Order. ID int ) AS SET NOCOUNT ON; BEGIN TRY BEGIN TRANSACTION -- Delete rows from the child table, Work. Order. Routing, for the specified work order. DELETE FROM Production. Work. Order. Routing WHERE Work. Order. ID = @Work. Order. ID; -- Delete the rows from the parent table, Work. Order, for the specified work order. DELETE FROM Production. Work. Order WHERE Work. Order. ID = @Work. Order. ID; COMMIT END TRY BEGIN CATCH -- Determine if an error occurred. IF @@TRANCOUNT > 0 ROLLBACK -- Return the error information. DECLARE @Error. Message nvarchar(4000), @Error. Severity int; SELECT @Error. Message = ERROR_MESSAGE(), @Error. Severity = ERROR_SEVERITY(); RAISERROR(@Error. Message, @Error. Severity, 1); END CATCH; GO EXEC Production. usp. Delete. Work. Order 13;

Transact-SQL Stored procedure

UDF

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

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

$Синтаксис --Transact-SQL Scalar Function Syntax CREATE FUNCTION [ schema_name. ] function_name ( [ {$

Синтаксис --Transact-SQL Scalar Function Syntax CREATE FUNCTION [ schema_name. ] function_name ( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type [ = default ] [ READONLY ] } [ , . . . n ] ] ) RETURNS return_data_type [ WITH <function_option> [ , . . . n ] ] [ AS ] BEGIN function_body RETURN scalar_expression END [ ; ]

Аргументы • schema_name - Имя схемы, которой принадлежит функция. Функции привязаны к схеме. Если имя схемы не указано при создании функции, то автоматически назначается схема по умолчанию для пользователя, который создает функцию. • function_name - Имя функции. Имена функций должны соответствовать требованиям, предъявляемым к идентификаторам, и должны быть уникальными в схеме. • @parameter_name - Аргумент пользовательской функции. Может быть объявлен один или несколько аргументов. • parameter_data_type - Тип данных параметра (возможно, с указанием схемы, которой он принадлежит). Для функций Transact-SQL допустимы любые типы данных, включая определяемые пользователем типы данных CLR и определяемые пользователем табличные типы, за исключением типа данных timestamp.

• READONLY - Указывает, что параметр не может быть обновлен или изменен при определении функции. Если тип параметра является определяемым пользователем табличным типом, то должно быть указано ключевое слово READONLY. • return_data_type - Возвращаемое значение скалярной функции, определяемой пользователем. Для функций Transact-SQL допустимы любые типы данных, включая определяемые пользователем типы данных CLR, за исключением типа данных timestamp. • function_body - Указывает серию инструкций Transact-SQL, которая в совокупности не вызывает побочных эффектов (например, изменение содержимого таблиц) и формирует возвращаемое значение функции. function_body используется только в скалярных функциях и функциях, возвращающих табличное значение, из нескольких инструкций. • scalar_expression - Указывает скалярное значение, возвращаемое скалярной функцией. • WITH ENCRYPTION - в системном каталоге кодирует содержащую текст инструкции CREATE FUNCTION. информацию,

Примеры CREATE FUNCTION Compute. Costs (@percent INT = 10) RETURNS DECIMAL(16, 2) BEGIN DECLARE @add. Costs DEC (14, 2), @sum. Budget DEC(16, 2) SELECT @sum. Budget = SUM (Budget) FROM Project SET @add. Costs = @sum. Budget * @percent/100 RETURN @add. Costs END;

Вызов UDF USE Sample. Db; -SELECT Number, Project. Name FROM Project WHERE Budget < dbo. Compute. Costs(25); • Инструкция SELECT в примере отображает названия и номера всех проектов, бюджеты которых меньше, чем общие дополнительные расходы по всем проектам при заданном значении процентного увеличения. • В инструкциях Transact-SQL имена функций необходимо задавать, используя имена, состоящие из двух частей: schema name и function name, поэтому в примере мы использовали префикс схемы dbo.

• Определенную пользователем функцию можно вызывать с помощью инструкций Transact-SQL, таких как SELECT, INSERT, UPDATE или DELETE. • Вызов функции осуществляется, указывая ее имя с парой круглых скобок в конце, в которых можно задать один или несколько аргументов. • Аргументы - это значения или выражения, которые передаются входным параметрам, определяемым сразу же после имени функции. • При вызове функции, когда для ее параметров не определены значения по умолчанию, для всех этих параметров необходимо предоставить аргументы в том же самом порядке, в каком эти параметры определены в инструкции CREATE FUNCTION.

CREATE FUNCTION Employees. In. Project (@project. Number CHAR(4)) RETURNS TABLE AS RETURN (SELECT First. Name, Last. Name FROM Works_on, Employee WHERE Employee. Id = Works_on. Emp. Id AND Project. Number = @project. Number) SELECT * FROM Employees. In. Project('p 3')

Справка по UDF • http: //professorweb. ru/my/sqlserver/2012/level 3/3_3. php • https: //msdn. microsoft. com/ruru/library/ms 186755(v=sql. 105). aspx

Скачать презентацию Хранимая процедура — объект базы данных представляющий

[БД-15.1] Процедуры и функции.pptx

Количество слайдов: 24