Виконання віддалених збережених процедур

Існують два методи виклику збережених процедур, розміщених на іншому сервері: посилання по чотирьохкомпонентну імені і розподілений запит Обидва методи вимагають, щоб віддалений сервер був повязаний з поточним Збережені процедури можуть тільки викликатися віддалено – створюватися віддалено вони не можуть

Додаткова Про те, як встановити захищені підключення до віддалених серверів, см

інформація в розділі 15

Виклик віддаленої процедури, що зберігається за допомогою четирьохкомпонентного імені виглядає наступним чином:

сервер база_данних схема імя_процедури

У наступному прикладі в базі даних OBXKites в другому примірнику сервера NOLI (розглянутому як віддалений) створюється нова категорія товарів:

EXEC [Noli\SQL2]OBXKitesdbopProductCategory_AddNew Food1, Eatables

Як альтернатива для виклику віддаленої процедури, що зберігається може використовуватися функція OpenQuery ():

OpenQuery (імя_связанного_сервера, exec хранімая_проце, пура 1 )

У наступному прикладі на виконання запускається процедура, що зберігається pCustomerType_ Fetch, розміщена в базі даних за замовчуванням для поточного реєстраційного імені користувача, використаного для підключення до сервера NOLI \ SQL2 Якщо база даних за замовчуванням вказана некоректно, то для звернення до коректної базі буде використано тричастинне імя:

SELECT CustomerTypeName, DiscountPercent, [Default]

FROM OPENQUERY^

[Noli \ SQL2], OBXKitesdbopCustomerType_Fetch j Результат виконання запиту:

CustomerTypeName DiscountPercent Default

Preferred               10                                    0

Retail                     00                                   1

Wholesale              15                                   0

Як і у випадку з іншими розподіленими запитами, якщо транзакція оновлює дані більш ніж на одному сервері, повинна бути запущена служба координатора розподілених запитів

Завершена збережена процедура

У цьому розділі буде представлений повний сценарій з збереженими процедурами з навчальної бази даних OBXKites У ньому для додавання замовлень в базу даних будуть використовуватися три збережені процедури: pGetPrice, pOrder_AddNew і pOrder_AddItem У цьому сценарії буде продемонстровано безліч засобів Т-SQL і збережених процедур При цьому на початку кожного з відповідних підрозділів буде описано призначення і зміст процедури, якої він присвячений

Повний текст розглянутих збережених процедур і викликають їх пакетів ви можете знайти в файлах OBXKites_Create sql і OBXKites_Populate sql

Збережена процедура pGetPrice

У збереженій процедурі pGetPrice продемонстровано використання значень параметрів за замовчуванням, вихідних параметрів, обробки помилок і взаімоблокіровок На вході вона приймає код товару, необовязкову дату і необовязковий код клієнта Використовуючи цю інформацію, процедура визначає коректну ціну і повертає її у вихідний змінної

Кожен клієнт може бути віднесений до певної категорії, а кожна з категорій може мати певну величину знижки Якщо в процедуру передається код клієнта, то в процедурі в першу чергу обчислюється встановлена ​​для нього знижка в іншому випадку знижка вважається нульовою

База даних OBXKites містить таблицю прайс-листа, в якій кожен продукт може мати різні ціни в різні дні Цей метод дозволяє зберегти історію ціноутворення і вводити ціни заздалегідь Якщо функція pGetPrice запускається з порожнім значенням дати, то використовується поточна дата При цьому для визначення дати установки ціни береться в розрахунок максимальна дата, яка не перевищує поточну (або задану) Як тільки дата ціноутворення буде визначена, знайти коректну ціну просто Для обчислення дати ціноутворення у процедурі pGetPrice використовується подзапрос

Помилки обробляються в блоці Catch Якщо помилка викликана перевищенням часу очікування блокування або взаімоблокіровке, то обробник очікує ще чверть секунди, після чого переходить до мітці LockTimeOutRetry, розташованої на початку процедури, щоб повторити спробу блокування знову і успішно виконати процедуру Якщо після пяти спроб блокування так і не була встановлена, обробник повідомляє про помилку і виходить з процедури Ось повний текст збереженої процедури pGetPrice:

CREATE PROCEDURE pGetPrice(

@Code CHAR(10),

@PriceDate DATETIME = NULL,

@ContactCode CHAR(15) = NULL,

@CurrPrice MONEY OUTPUT )

AS

– Повертає активну ціну для заданої дати або поточного дня – Тип замовника визначає величину знижки

– Вихідний параметр, @ CurrPrice, міститиме активну ціну – Приклад коду для виклику цієї процедури, що зберігається:

— Declare @Price money

– Ехес GetPrice 1006 , NULL, @ Price OUTPUT

— Select @Price SET NOCOUNT ON DECLARE

@DiscountPercent NUMERIC (4,2),

@Err INT,

@ErrCounter INT SET @ErrCounter = 0 SET @CurrPrice = NULL

LockTimeOutRetry:

BEGIN TRY

IF @PriceDate IS NULL

SET @ PriceDate = GETDATEO – встановлюємо знижку

– Якщо код клієнта відсутня, вона дорівнює нулю SELECT @ DiscountPercent = CustomerTypeDiscountPercent FROM dboContact JOIN dboCustomerType

ON contactCustomerTypeID = CustomerTypeCustomerTypelD WHERE ContactCode = @ContactCode IF @DiscountPercent IS NULL SET @DiscountPercent = 0 SELECT @CurrPrice = Price * (l-@DiscountPercent)

FROM dboPrice JOIN dboProduct

ON PriceProductID = ProductProductID WHERE Code = @Code

AND EffectiveDate =

(SELECT MAX(EffectiveDate)

FROM dboPrice JOIN dboProduct

ON PriceProductID = ProductProductID WHERE Code = @Code

AND EffectiveDate &lt= @PriceDate)

IF @CurrPrice IS NULL BEGIN

RAISERROR(Code: 11%s1 has no established price1,15,1, @Code) RETURN -100

END END TRY BEGIN CATCH

SET @Err = @@ERROR

IF (@Err = 1222 OR @Err = 12 05) AND @ErrCounter = 5 BEGIN

RAISERROR (Unable to Lock Data after five attempts’, 16,1) RETURN -100

END

IF @Err = 1222 OR @Err = 1205 — Lock Timeout / Deadlock BEGIN

WAITFOR DELAY 00:00:0025

SET @ErrCounter = @ErrCounter + 1 GOTO LockTimeOutRetry END

– Невідома помилка RAISERROR (@ err, 16,1) WITH LOG RETURN -100 END CATCH

Збережена процедура pOrder_AddNew

Дані замовлення містяться у двох таблицях: [Order] і OrderDetail Перша з них містить інформацію заголовка, а друга – список товарів Відкриття замовлення передбачає збирання та перевірку інформації заголовка, генерацію номера замовлення (OrderNumber) і вставку рядка в таблицю [Order]

Збережена процедура pOrder_AddNew приймає в якості параметрів код замовника, код працівника, відповідального за прийом замовлення, місцезнаходження пункту продажу та дату прийому замовлення

Продажі іноді здійснюються заднім числом, так що припускати, що датою замовлення буде поточна – безглуздо Якщо дата замовлення не передається в процедуру, вона встановлюється в інструкції INSERT в поточну (Так як параметр приймає значення за замовчуванням – null)

Код замовника також є необовязковим параметром Якщо він не передається в процедуру, приймається значення за замовчуванням – 0 і потім перетвориться в порожнє значення ідентифікатора клієнта (ContactID) Схема бази даних допускає порожні значення ідентифікаторів замовників, припускаючи, що деякі з них захочуть залишитися анонімними

Усі записи може розпізнати людина, проте база даних для реплікації використовує глобальні універсальні ідентифікатори GUID для замовників, розташування й співробітників і перевіряє їх в таблицях класифікаторів

Коли всі коди будуть перевірені, процедура знаходить номер останнього замовлення і збільшує його на одиницю Замовлення вставляється в рядок таблиці в тій же транзакції, в якій визначається його номер Щоб уникнути дублювання номерів замовлень рівень ізоляції транзакції встановлюється в serialized (У реальних додатках зазвичай використовуються більш складні методи генерації номера замовлення, які дозволяють уникнути дублікатів в просторі всіх точок продажів)

Використовувана схема обробки помилок схожа з процедурою pGetPrice Єдина відмінність полягає в тому, що перед обробкою помилки транзакція відкочується

На закінчення процедура повертає номер замовлення в вихідної змінної, дозволяючи зухвалому пакету вставити його в таблицю і приступити до формування рядків замовлення Нижче приведений код створення збереженої процедури pOrder_AddNew

CREATE PROC pOrder_AddNew (

@ContactCode CHAR(15) = 0,

– За умовчанням замовник анонімний @ EmployeeCode CHAR (15),

@LocationCode CHAR(15),

@OrderDate DATETIME = NULL,

@OrderNumber INT OUTPUT )

AS

– Логіка:

– Якщо номер замовника вказано, перевіряємо його

SET NOCOUNT ON

DECLARE

@ContactID UNIQUEIDENTIFIER,

@OrderID UNIQUEIDENTIFIER,

@LocationID UNIQUEIDENTIFIER,

@EmployeeID UNIQUEIDENTIFIER,

@Err INT,

@ErrCounter INT SET @ErrCounter = 0

LockTimeOutRetry:

– Знаходимо ідентифікатор замовника IF @ ContactCode = О

SET @ContactID = NULL ELSE BEGIN

SELECT @ContactID = ContactID FROM dboContact

WHERE ContactCode = @ContactCode SET @Err = @@ERROR

IF @Err &lt&gt 0 GOTO ErrorHandler IF @ContactID IS NULL

BEGIN – зазначений замовник не знайдений RAISERROR (

‘Код замовника: % s не найден, 15,1, @ ContactCode) RETURN -100 END END

– Знаходимо ідентифікатор місцеположення SELECT @ LocationID = LocationID FROM dboLocation

WHERE LocationCode = @LocationCode SET @Err = @@ERROR

IF @Err &lt&gt 0 GOTO ErrorHandler IF @LocationID IS NULL

BEGIN – Місцезнаходження не знайдено RAISERROR (

‘Код регіону:’% s’ не знайдений, 15,1, @ LocationCode) RETURN -100 END

IF EXISTS(SELECT *

FROM dboLocation WHERE LocationID = @LocationID AND IsRetail = 0)

BEGIN – Ні точок продажів RAISERROR (

‘Регіон:’% s1Не має точок продажу, 15,1,

@Locat ionCode)

RETURN -100

END

– Знаходимо ідентифікатор співробітника SELECT @ EmployeeID = ContactID FROM dboContact

WHERE ContactCode = @EmployeeCode SET @Err = @@ERROR

IF @Err &lt&gt 0 GOTO ErrorHandler IF @EmployeeCode IS NULL

BEGIN – Співробітник не найден RAISERROR (

‘Код співробітника:’% s1Не найден, 15,1, © EmployееCode) RETURN -100 END

– Генерація номера замовлення SET @ OrderID = NEWID ()

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRANSACTION

SELECT @OrderNumber = Max(OrderNumber) + 1 FROM [Order]

SET @OrderNumber = ISNULL(@OrderNumber, 1)

SET @Err = @@ERROR IF @Err &lt&gt 0 BEGIN

ROLLBACK TRANSACTION GOTO ErrorHandler END

– Bee OK – виконуємо вставку INSERT dbo [Order] (

OrderlD, ContactID, OrderNumber,

EmployeelD, LocationID, OrderDate )

VALUES (

@OrderID, @ContactID,@OrderNumber,

@EmployeeID, @LocationID, ISNULL(@OrderDate,GETDATE()))

IF @Err &lt&gt 0 BEGIN

ROLLBACK TRANSACTION GOTO ErrorHandler END

COMMIT TRANSACTION

RETURN – номер замовлення @ OrderNumber встановлений ErrorHandler:

IF (@Err = 1222 OR @Err = 1205) AND @ErrCounter = 5 BEGIN

RAISERROR (Unable to Lock Data after five attempts’, 16,1) RETURN -10 0 END

IF @ Err = 1222 OR @ Err = 1205 – Відсутність блокування

– Або взаімоблокіровка

BEGIN

WAITFOR DELAY *00:00:0025

SET @ErrCounter = @ErrCounter + 1 GOTO LockTimeOutRetry END

– Інакше – невідома помилка RAISERROR (@ err, 16,1) WITH LOG RETURN -100

Збережена Процедура pOrder_AddItem

Коли рядок замовлення вставлена ​​в таблицю, третя збережена процедура вставляє в замовлення рядка товарів Процедура продажу повинна бути досить гнучкою, тому в даній процедурі передбачено безліч значень за замовчуванням Єдиними обовязковими параметрами є номер замовлення та кількість Товар може визначатися або назвою, або кодом Ціна за одиницю товару витягується за допомогою раніше описаної збереженої процедури pGetPrice На закінчення, якщо потрібно доставка товару замовнику, вводяться її дата і коментар Якщо інформація про доставку відсутня, процедура припускає, що доставляти товар потрібно негайно

Як і інші збережені процедури, pOrder_AddItem починається з перевірки всіх параметрів і вставки асоційованих ідентифікаторів в таблицю OrderDetail

У цій процедурі ціна товару визначається процедурою pGetPrice, якщо в параметрі вона порожня (В главі 22 ми створимо функцію fGetPrice) З метою порівняння в даній процедурі також використаний виклик цієї функції, але ця частина тексту закоментований Нижче наведено текст збереженої процедури pOrder_AddItem

CREATE PROCEDURE pOrder_AddItem(

@OrderNumber CHAR(15),

@ Code CHAR (15) = 0, – за умовчанням товар шукається на складі @ NonStockProduct NVARCHAR (256) = NULL,

@Quantity NUMERIC(7,2),

@ UnitPrice MONEY = 0, – за умовчанням ціна визначається

– По прайс-листу @ ShipRequestDate DATETIME = NULL, – за умовчанням доставка

– Негайна

@ ShipComment NVARCHAR (256) = NULL – коментар не обовязковий)

AS

DECLARE

@OrderID UNIQUEIDENTIFIER,

@ProductID UNIQUEIDENTIFIER,

@ContactCode CHAR(15),

@PriceDate DATETIME,

@Err INT,

@ErrCounter INT SET @ErrCounter = 0

LockTimeOutRetry:

– Отримуємо ідентифікатор замовлення SELECT @ OrderID = OrderlD FROM dbo [Order]

WHERE OrderNumber = @OrderNumber SET @Err = @@ERROR

IF @Err &lt&gt 0 GOTO ErrorHandler

– Отримуємо ідентифікатор товару SELECT @ ProductID = ProductID FROM Product WHERE Code = @ Code SET @ Err = @ @ ERROR

IF @Err &lt&gt 0 GOTO ErrorHandler

Отримуємо код клієнта і дату установки прайс-листа

SELECT @ContactCode = ContactCode, @PriceDate = OrderDate FROM dbo[Order]

LEFT JOIN Contact

ON [Order]ContactID = ContactContactID SET @Err = @@ERROR

IF @Err &lt&gt 0 GOTO ErrorHandler

– Отримуємо ціну за одиницю товару IF @ UnitPrice IS NULL EXEC pGetPrice

@ Code, @ PriceDate, @ ContactCode, @ UnitPrice OUTPUT – Альтернативний виклик функції fGetPrice – SET @ UnitPrice = dbofGetPrice (

— @Code,@PriceDate, @ContactCode)

SET @Err = @@ERROR

IF @Err &lt&gt 0 GOTO ErrorHandler

IF @UnitPrice IS NULL BEGIN

RAISERROR(

‘Для коду товару: 1% S’ ціна не встановлена , 15,1,

@Code)

RETURN -1 END

– Встановлюємо дату доставки IF @ ShipRequestDate IS NULL

SET @ ShipRequestDate = @ PriceDate – Виконуємо вставку рядка INSERT OrderDetail (

OrderlD, ProductID, NonStockProduct, Quantity,

UnitPrice, ShipRequestDate, ShipComment)

VALUES (

@OrderID, @ProductID, @NonStockProduct, @Quantity,

@UnitPrice, @ShipRequestDate, @ShipComment)

SET @Err = @@ERROR

IF @Err &lt&gt 0 GOTO ErrorHandler RETURN 0

ErrorHandler:

IF (@Err = 1222 OR @Err = 1205) AND ©ErrCounter = 5 BEGIN

RAISERROR (

‘Неможливо блокувати дані за пять спроб’, 16,1)

RETURN -100 END

– Перевищено час очікування блокування або взаимоблокировки IF @ Err = 1222 OR @ Err = 1205 BEGIN

WAITFOR DELAY 00:00:0025

SET @ErrCounter = @ErrCounter + 1 GOTO LockTimeOutRetry END

else – невідома помилка

RAISERROR (@err, 16,1) WITH LOG RETURN -100

Додавання замовлення

Щоб ви змогли побачити створені збережені процедури в дії, в наступному пакеті, взятому з сценарію OBXKites_Populate sql, демонструється створення двох замовлень Цей пакет може бути реально посланий на SQL Server для вставки в базу даних OBXKites двох замовлень

Збережена процедура pOrder_AddNew створює рядок нового замовлення і повертає зухвалому пакету його номер Після цього пакет створює рядка замовлення, передаючи цей номер збереженій процедурі pOrder_AddItem Локальна змінна пакета @ OrderNumber використовується для отримання номера замовлення з процедури pOrder_AddNew і передачі його в кожен виклик процедури pOrder_AddItem

При формуванні першого замовлення в явному вигляді вказані імена параметрів при формуванні другого замовлення параметри перераховуються в установленому у визначенні процедури порядку

DECLARE @OrderNumber INT

– Перше замовлення EXEC pOrder_AddNew

@ContactCode = 101,

@EmployeeCode = 12 0,

@LocationCode = CH,

@OrderDate=NULL,

@OrderNumber = @OrderNumber output EXEC pOrder_SetPriority @OrderNumber, 4

EXEC pOrder_AddItem

@OrderNumber = @OrderNumber,

@Code = 1002,

@NonStockProduct = NULL,

@Quantity = 12,

@UnitPrice = NULL,

@ShipRequestDate = 111/15/01 @ShipComment = NULL

– Другий замовлення EXEC pOrder_AddNew

‘101, 420, CH, NULL, @OrderNumber output EXEC pOrder_AddItem

@OrderNumber, 4002, NULL, 3, NULL, NULL, NULL EXEC pOrder_AddItem

@OrderNumber, 4003, NULL, 5, NULL, NULL, NULL EXEC pOrder_AddItem

@OrderNumber, 1004, NULL, 2, NULL, NULL, NULL EXEC pOrder_AddItem

@OrderNumber, 4044, NULL, 1, NULL, NULL, NULL

Резюме

Використання збережених процедур є одним з способів збереження та оптимізації пакетів Збережені процедури компілюються і зберігаються в памяті при першому запуску Не існує більш швидкого методу виконання інструкцій SQL і не існує програмних обєктів, що знаходяться ближче до даних Подібно пакетам, збережені процедури можуть повернути набір даних, скориставшись інструкцією SELECT

У наступному розділі ми розглянемо користувача функції, які комбінують гідності збережених процедур з переносимістю уявлень

Джерело: Нільсен, Пол Microsoft SQL Server 2005 Біблія користувача : Пер з англ – М: ООО ІД Вільямс , 2008 – 1232 с : Ил – Парал тит англ

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*