Збережені процедури в SQL Server. Зберігаються процедури. Створення та виконання процедур, що зберігаються

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

Вступ

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

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

За

• Поділ логіки з іншими програмами. Збережені процедури інкапсулюють функціональність; це забезпечує зв'язність доступу до даних та управління ними між різними програмами.
• Ізоляція користувачів від таблиць бази даних. Це дозволяє давати доступ до процедур, що зберігаються, але не до самих даних таблиць.
• Забезпечує механізм захисту. Відповідно до попереднього пункту, якщо ви можете отримати доступ до даних лише через процедури, що зберігаються, ніхто інший не зможе стерти ваші дані через команду SQL DELETE.
• Поліпшення виконання як наслідок скорочення мережного трафіку. За допомогою процедур, що зберігаються, безліч запитів можуть бути об'єднані.

Проти

• Підвищення навантаження на сервер баз даних у зв'язку з тим, що більшість роботи виконується на серверній частині, а менша - на клієнтській.
• Доведеться багато чого навчити. Вам знадобиться вивчити синтаксис MySQL виразів для написання своїх процедур, що зберігаються.
• Ви дублюєте логіку своєї програми в двох місцях: серверний код і код для процедур, що зберігаються, тим самим ускладнюючи процес маніпулювання даними.
• Міграція з однієї СУБД на іншу (DB2, SQL Server та ін) може призвести до проблем.

Інструмент, у якому я працюю, називається MySQL Query Browser, він досить стандартний для взаємодії з базами даних. Інструмент командного рядка MySQL – це ще один чудовий вибір. Я розповідаю вам про це з тієї причини, що всіма улюблений phpMyAdmin не підтримує виконання процедур, що зберігаються.

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

Крок 1: Ставимо обмежувач

Обмежувач - це символ або рядок символів, який використовується для вказівки клієнту MySQL, що ви завершили написання виразу SQL. Цілу вічність обмежувачем був символ крапки з комою. Тим не менш, можуть виникнути проблеми, оскільки в процедурі, що зберігається, може бути кілька виразів, кожен з яких повинен закінчуватися точкою з комою. У цьому уроці я використовую рядок “//” як обмежувач.

Крок 2: Як працювати з процедурами, що зберігаються

Створення процедури, що зберігається

DELIMITER // CREATE PROCEDURE `p2` () LANGUAGE SQL DETERMINISTIC SQL SECURITY DEFINER COMMENT "A procedure" BEGIN SELECT "Hello World!"; END//

Перша частина коду створює процедуру, що зберігається. Наступна – містить необов'язкові параметри. Потім іде назва і, нарешті, тіло самої процедури.

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

4 характеристики процедури, що зберігається:

• Language: з метою забезпечення переносимості, за умовчанням вказано SQL.
• Deterministic: якщо процедура постійно повертає той самий результат, і приймає одні й самі параметри. Це для реплікації та процесу реєстрації. Значення за замовчуванням - NOT DETERMINISTIC.
• SQL Security: під час дзвінка відбувається перевірка прав користувача. INVOKER - це користувач, що викликає процедуру, що зберігається. DEFINER – це “творець” процедури. Значення за промовчанням - DEFINER.
• Comment: з метою документування, значення за замовчуванням - ""

Виклик збереженої процедури

Щоб викликати процедуру, що зберігається, необхідно надрукувати ключове слово CALL, а потім назва процедури, а в дужках вказати параметри (змінні або значення). Дужки обов'язкові.

CALL stored_procedure_name (param1, param2, ....) CALL procedure1(10 , "string parameter", @parameter_var);

Зміна процедури, що зберігається

MySQL має вираз ALTER PROCEDURE для зміни процедур, але він підходить для зміни лише деяких характеристик. Якщо вам потрібно змінити параметри або тіло процедури, слід видалити та створити її заново.

Видалення процедури, що зберігається

DROP PROCEDURE IF EXISTS p2;

Це проста команда. Вираз IF EXISTS відловлює помилку, якщо такої процедури не існує.

Крок 3: Параметри

Давайте подивимося, як можна передавати в процедуру, що зберігається, параметри.

• CREATE PROCEDURE proc1 (): пустий список параметрів
• CREATE PROCEDURE proc1 (IN varname DATA-TYPE): один вхідний параметр. Слово IN необов'язкове, тому що параметри за замовчуванням – IN (вхідні).
• CREATE PROCEDURE proc1 (OUT varname DATA-TYPE): один параметр, що повертається.
• CREATE PROCEDURE proc1 (INOUT varname DATA-TYPE): один параметр, що одночасно входить і повертається.

Природно, можна задавати кілька параметрів різних типів.

Приклад параметра IN

DELIMITER // CREATE PROCEDURE `proc_IN` (IN var1 INT) BEGIN SELECT var1 + 2 AS result; END//

Приклад параметра OUT

DELIMITER // CREATE PROCEDURE `proc_OUT` (OUT var1 VARCHAR(100)) BEGIN SET var1 = "Тим є"; END //

Приклад параметра INOUT

DELIMITER // CREATE PROCEDURE `proc_INOUT` (OUT var1 INT) BEGIN SET var1 = var1 * 2; END //

Крок 4: Змінні

Зараз я навчу вас створювати змінні та зберігати їх усередині процедур. Ви повинні оголошувати їх явно на початку блоку BEGIN/END, разом із типами даних. Як тільки ви оголосили змінну, ви можете використовувати її там, де змінні сесії, літерали або імена колонок.

Синтаксис оголошення змінної виглядає так:

DECLARE varname DATA-TYPE DEFAULT defaultvalue;

Давайте оголосимо кілька змінних:

DECLARE a, b INT DEFAULT 5; DECLARE str. VARCHAR(50); DECLARE today TIMESTAMP DEFAULT CURRENT_DATE; DECLARE v1, v2, v3 TINYINT;

Робота зі змінними

Як тільки ви оголосили змінну, ви можете встановити значення за допомогою команд SET або SELECT:

DELIMITER // CREATE PROCEDURE `var_proc` (IN paramstr VARCHAR(20)) BEGIN DECLARE a, b INT DEFAULT 5; DECLARE str. VARCHAR(50); DECLARE today TIMESTAMP DEFAULT CURRENT_DATE; DECLARE v1, v2, v3 TINYINT; INSERT INTO table1 VALUES (a); SET str = "I am a string"; SELECT CONCAT (str, paramstr), today FROM table2 WHERE b> = 5; END //

Крок 5: Структури керування потоками

MySQL підтримує конструкції IF, CASE, ITERATE, LEAVE LOOP, WHILE та REPEAT для управління потоками в межах збереженої процедури. Ми розглянемо, як використовувати IF, CASE та WHILE, оскільки вони найчастіше використовуються.

Конструкція IF

За допомогою конструкції IF ми можемо виконувати завдання, що містять умови:

DELIMITER // CREATE PROCEDURE `proc_IF` (IN param1 INT) BEGIN DECLARE variable1 INT; SET variable1 = param1 + 1; IF variable1 = 0 THEN SELECT variable1; END IF; IF param1 = 0 THEN SELECT "Parameter value = 0"; ELSE SELECT "Parameter value<>0"; END IF; END //

Конструкція CASE

CASE - це ще один метод перевірки умов та вибору відповідного рішення. Це відмінний спосібзаміни безлічі конструкцій IF. Конструкцію можна описати двома способами, надаючи гнучкість в управлінні безліччю умовних виразів.

DELIMITER // CREATE PROCEDURE `proc_CASE` (IN param1 INT) BEGIN DECLARE variable1 INT; SET variable1 = param1 + 1; CASE variable1 WHEN 0 THEN INSERT INTO table1 VALUES (param1); WHEN 1 THEN INSERT INTO table1 VALUES (variable1); ELSE INSERT INTO table1 VALUES (99); END CASE; END //

DELIMITER // CREATE PROCEDURE `proc_CASE` (IN param1 INT) BEGIN DECLARE variable1 INT; SET variable1 = param1 + 1; CASE WHEN variable1 = 0 THEN INSERT INTO table1 VALUES (param1); WHEN variable1 = 1 THEN INSERT INTO table1 VALUES (variable1); ELSE INSERT INTO table1 VALUES (99); END CASE; END //

Конструкція WHILE

Технічно існує три види циклів: цикл WHILE, цикл LOOP і цикл REPEAT. Ви також можете організувати цикл за допомогою техніки програмування “Дарта Вейдера”: вираз GOTO. Ось приклад циклу:

DELIMITER // CREATE PROCEDURE `proc_WHILE` (IN param1 INT) BEGIN DECLARE variable1, variable2 INT; SET variable1 = 0; WHILE variable1< param1 DO INSERT INTO table1 VALUES (param1); SELECT COUNT(*) INTO variable2 FROM table1; SET variable1 = variable1 + 1; END WHILE; END //

Крок 6: Курсори

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

MySQL підтримує курсори в процедурах, що зберігаються. Ось короткий синтаксис створення та використання курсору.

DECLARE cursor-name CURSOR FOR SELECT...; /*Оголошення курсору та його заповнення */ DECLARE CONTINUE HANDLER FOR NOT FOUND /*Що робити, коли більше немає записів*/ OPEN cursor-name; /*Відкрити курсор*/ FETCH cursor-name INTO variable [, variable]; /*Призначити значення змінної, що дорівнює поточному значенню стовпця*/ CLOSE cursor-name; /*Закрити курсор*/

У цьому прикладі ми проведемо деякі прості операції з використанням курсору:

DELIMITER // CREATE PROCEDURE `proc_CURSOR` (OUT param1 INT) BEGIN DECLARE a, b, c INT; DECLARE cur1 CURSOR FOR SELECT col1 FROM table1; DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1; OPEN cur1; SET b = 0; SET c = 0; WHILE b = 0 DO FETCH cur1 INTO a; IF b = 0 THEN SET c = c + a; END IF; END WHILE; CLOSE cur1; SET param1 = c; END //

У курсорів є три властивості, які вам необхідно зрозуміти, щоб уникнути несподіваних результатів:

• Не чутливий: курсор, що відкрився одного разу, не відображатиме зміни в таблиці, що відбулися пізніше. Насправді, MySQL не гарантує, що курсор оновиться, так що не сподівайтеся на це.
• Доступний лише для читання: курсори не можна змінювати.
• Без перемотування: курсор здатний проходити лише в одному напрямку – вперед, ви не зможете пропускати рядки, не вибираючи їх.

Висновок

У цьому уроці я ознайомив вас з основами роботи з процедурами, що зберігаються, і з деякими специфічними властивостями, пов'язаними з нею. Звичайно, вам потрібно буде поглибити знання в таких галузях, як безпека, вираз SQL і оптимізація, перш ніж стати справжнім гуру MySQL процедур.

Ви повинні підрахувати, які переваги дасть вам використання процедур, що зберігаються у вашому конкретному додатку, і тільки потім створювати лише необхідні процедури. Загалом я використовую процедури; на мою думку, їх варто впроваджувати в проекти внаслідок їхньої безпеки, обслуговування коду та загального дизайну. До того ж, не забувайте, що над процедурами MySQL досі ведеться робота. Чекайте на покращення, що стосуються функціональності та покращень. Прошу, не соромтеся ділитися думками.

Останнє оновлення: 14.08.2017

Нерідко операція з даними представляє набір інструкцій, які необхідно виконати у певній послідовності. Наприклад, при додаванні покупки товару необхідно внести дані до таблиці замовлень. Однак перед цим треба перевірити, а чи є товар, що купується в наявності. Можливо, при цьому доведеться перевірити ще низку додаткових умов. Тобто фактично процес купівлі товару охоплює кілька дій, які мають виконуватись у певній послідовності. І в цьому випадку більш оптимально інкапсулюватиме всі ці дії в один об'єкт - процедуру, що зберігається(stored procedure).

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

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

І ще один важливий аспект – продуктивність. Збережені процедури зазвичай виконуються швидше, ніж звичайні SQL-інструкції. Все тому, що код процедур компілюється один раз при першому її запуску, а потім зберігається в скомпільованій формі.

Для створення процедури , що зберігається застосовується команда CREATE PROCEDURE або CREATE PROC .

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

Наприклад, нехай у базі даних є таблиця, яка зберігає дані про товари:

CREATE TABLE Products (Id INT IDENTITY PRIMARY KEY, ProductName NVARCHAR(30) NOT NULL, Manufacturer NVARCHAR(20) NOT NULL, ProductCount INT DEFAULT 0, Price MONEY NOT NULL);

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

USE productsdb; GO CREATE PROCEDURE ProductSummary AS SELECT ProductName AS Product, Manufacturer, Price FROM Products

Оскільки команда CREATE PROCEDURE повинна викликатися в окремому пакеті, після команди USE, яка встановлює поточну базу даних, використовується команда GO визначення нового пакета.

Після імені процедури має бути ключове слово AS.

Для відокремлення тіла процедури від решти скрипта код процедури нерідко поміщається в блок BEGIN...END:

USE productsdb; GO CREATE PROCEDURE ProductSummary AS BEGIN SELECT ProductName AS Product, Manufacturer, Price FROM Products END;

Після додавання процедури ми її можемо побачити у вузлі бази даних у SQL Server Management Studio у підвузлі Programmability -> Stored Procedures:

І ми зможемо керувати процедурою також через візуальний інтерфейс.

Виконання процедури

Для виконання процедури, що зберігається, викликається команда EXEC або EXECUTE :

EXEC ProductSummary

Видалення процедури

Для видалення процедури застосовується команда DROP PROCEDURE:

DROP PROCEDURE ProductSummary

Збережені процедури SQL є виконуваний програмний модуль, який може зберігатися у вигляді різних об'єктів. Іншими словами, це об'єкт, у якому містяться SQL-інструкції. Ці процедури, що зберігаються, можуть бути виконані в клієнті прикладних програмотримати хорошу продуктивність. Крім того, такі об'єкти нерідко викликаються з інших сценаріїв або навіть будь-якого іншого розділу.

Вступ

Багато хто вважає, що вони схожі на різні процедури (відповідно, крім MS SQL). Мабуть, це справді так. Вони мають схожі параметри, можуть видавати схожі значення. Більше того, у ряді випадків вони стикаються. Наприклад, вони поєднуються з базами даних DDL та DML, а також з функціями користувача (кодова назва – UDF).

Насправді ж збережені процедури SQL мають широкий спектр переваг, які виділяють їх серед подібних процесів. Безпека, варіативність програмування, продуктивність - усе це приваблює користувачів, які працюють із базами даних, дедалі більше. Пік популярності процедур припав на 2005-2010 роки, коли вийшла програма від "Майкрософт" під назвою SQL Server Management Studio. З її допомогою працювати з базами даних стало набагато простіше, практичніше та зручніше. Рік у рік такий набирав популярності серед програмістів. Сьогодні ж є абсолютно звичною програмою, яка для користувачів, які «спілкуються» з базами даних, стала нарівні з «Екселем».

При виклику процедури вона вмить обробляється самим сервером без зайвих процесів і втручання користувача. Після цього можна здійснювати будь-яке видалення, виконання, зміну. За все це відповідає DDL-оператор, який самостійно робить найскладніші дії з обробки об'єктів. Причому це відбувається дуже швидко, а сервер фактично не навантажується. Така швидкість і продуктивність дозволяють швидко передавати великі обсяги інформації від користувача на сервер і навпаки.

Для реалізації цієї технології роботи з інформацією існує кілька мов програмування. До них можна віднести, наприклад, PL/SQL від Oracle, PSQL у системах InterBase та Firebird, а також класичний «майкрософтівський» Transact-SQL. Всі вони призначені для створення і виконання процедур, що зберігаються, що дозволяє у великих обробниках баз використовувати власні алгоритми. Це потрібно і для того, щоб ті, хто здійснює керування такою інформацією, могли захистити всі об'єкти від несанкціонованого доступу сторонніх осіб та, відповідно, створення, зміни чи видалення тих чи інших даних.

Продуктивність

Ці об'єкти баз даних може бути запрограмовані різними шляхами. Це дозволяє користувачам вибирати тип використовуваного способу, який буде найбільш підходящим, що заощаджує сили та час. Крім того, процедура сама обробляється, що дозволяє уникнути величезних часових витрат на обмін між сервером та користувачем. Також модуль можна перепрограмувати і змінити в потрібний напрямок у будь-який момент. Особливо варто відзначити швидкість, з якою відбувається запуск збереженої процедури SQL: цей процес відбувається швидше за інших, схожих з ним, що робить його зручним і універсальним.

Безпека

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

Передача даних

Зв'язок між процедурою SQL, що зберігається, і клієнтським додатком полягає у використанні параметрів і значеннях, що повертаються. Останнім не обов'язково передавати дані в процедуру, що зберігається, проте ця інформація (в основному за запитом користувача) і переробляється для SQL. Після того як процедура, що зберігається, завершила свою роботу, вона відсилає пакети даних назад (але, знову ж таки, за бажанням) до додатку, що викликав його, використовуючи різні методи, за допомогою яких може бути здійснений як виклик збереженої процедури SQL, так і повернення, наприклад:

Передача даних за допомогою параметра типу Output;

Передача даних за допомогою оператора повернення;

Надсилання даних за допомогою оператора вибору.

А тепер розберемося, як виглядає цей процес зсередини.

1. Створення EXEC-збереженої процедури в SQL

Ви можете створити процедуру в MS SQL (Managment Studio). Після того, як створиться процедура, вона буде перерахована у програмований вузол бази даних, в якій процедура створення виконується оператором. Для виконання збережених процедур SQL використовують EXEC-процес, який містить ім'я самого об'єкта.

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

Справа в тому, що тіло може мати локальні змінні, розташовані в ній, і ці змінні локальні також по відношенню до процедур. Іншими словами, їх можна розглядати лише всередині тіла процедури Microsoft SQL Server. Збережені процедури у разі вважаються локальними.

Таким чином, щоб створити процедуру, нам потрібне ім'я процедури і щонайменше один параметр в якості тіла процедури. Зверніть увагу, що відмінним варіантом у такому разі є створення та виконання процедури з ім'ям схеми у класифікаторі.

Тіло процедури може мати будь-який вид наприклад, такі як створення таблиці, вставки одного або декількох рядків таблиці, встановлення типу і характеру бази даних і так далі. Проте тіло процедури обмежує виконання деяких операцій у ньому. Деякі з важливих обмежень наведені нижче:

Тіло не повинно створювати будь-якої іншої процедури, що зберігається;

Тіло не повинно створити хибне уявлення про об'єкт;

Тіло не повинно створювати жодних тригерів.

2. Встановлення змінної у тіло процедури

Ви можете зробити змінні локальними для тіла процедури, і вони будуть перебувати виключно всередині тіла процедури. Хорошою практикою є створення змінних на початку тіла процедури, що зберігається. Але також можна встановлювати змінні в будь-якому місці в тілі даного об'єкта.

Іноді можна помітити, що кілька змінних встановлені в одному рядку, і кожен змінний параметр відокремлюється комою. Також зверніть увагу, що змінна має префікс @. У тілі процедури можна встановити змінну, куди ви хочете. Наприклад, змінна @NAME1 може бути оголошена ближче до кінця тіла процедури. Для того щоб надати значення оголошеної змінної використовується набір особистих даних. На відміну від ситуації, коли оголошено більше однієї змінної в одному рядку, у такій ситуації використовується лише один набір особистих даних.

Часто користувачі запитують: "Як призначити кілька значень в одному операторі в тілі процедури?" Що ж. Питання цікаве, але зробити це набагато простіше, ніж ви думаєте. Відповідь: за допомогою таких пар, як "Select Var = значення". Ви можете використовувати ці пари, розділяючи їх комою.

У найрізноманітніших прикладах люди показують створення простої процедури, що зберігається, і виконання її. Однак процедура може приймати такі параметри, що процес, що викликає її, матиме значення, близькі до нього (але не завжди). Якщо вони збігаються, то всередині тіла розпочинаються відповідні процеси. Наприклад, якщо створити процедуру, яка прийматиме місто та регіон від абонента, що викликає, і повертати дані про те, скільки авторів відносяться до відповідного міста та регіону. Процедура буде вимагати таблиці авторів бази даних, наприклад, Pubs, до виконання цього підрахунку авторів. Щоб отримати ці бази даних, наприклад, Google завантажує сценарій SQL зі сторінки SQL2005.

У попередньому прикладі процедура приймає два параметри, які англійською умовно будуть називатися @State і @City. Тип даних відповідає типу, визначеному у додатку. Тіло процедури має внутрішні змінні @TotalAuthors (всього авторів), і ця змінна використовується для відображення їх кількості. Далі з'являється розділ вибору запиту, який все підраховує. Нарешті, підраховане значення відображається у вікні виводу за допомогою оператора друку.

Як у SQL виконати збережену процедуру

Є два способи виконання процедури. Перший шлях показує, передаючи параметри, як розділений ком список виконується після імені процедури. Допустимо, ми маємо два значення (як у попередньому прикладі). Ці значення збираються за допомогою змінних параметрів процедури @State та @City. У цьому способі передачі параметрів важливий порядок. Такий метод називається порядковою передачею аргументів. У другому способі параметри безпосередньо призначені, і в цьому випадку порядок не важливий. Цей другий спосіб відомий як передача іменованих аргументів.

Процедура може дещо відхилятися від типової. Так само, як і в попередньому прикладі, але тільки тут параметри зрушуються. Тобто параметр @City зберігається першим, а @State зберігається поруч із значенням за промовчанням. Стандартний параметр виділяється зазвичай окремо. Збережені процедури SQL проходять просто параметри. У цьому випадку за умови параметр «UT» замінює значення за замовчуванням «СА». У другому виконанні проходить лише одне значення аргументу для параметра @ City, і параметр @ State приймає значення за умовчанням «СА». Досвідчені програмісти радять, щоб усі змінні за умовчанням розташовувалися ближче до кінця списку параметрів. В іншому випадку виконання неможливо, і тоді ви повинні працювати з передачею іменованих аргументів, що довше і складніше.

4. Збережені процедури SQL Server: способи повернення

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

Повернення значення процедури, що зберігається;

Вихід параметра процедур, що зберігаються;

Вибір однієї з процедур, що зберігаються.

4.1 Повернення значень збережених процедур SQL

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

Тепер давайте подивимося, як виконати процедуру і вивести значення, яке їй повертається. Виконання процедури вимагає встановлення змінної та друку, яка проводиться після цього процесу. Зверніть увагу, що замість оператора друку можна використовувати Select-оператор, наприклад, Select @RetValue, а також OutputValue.

4.2 Вихід параметра процедур SQL, що зберігаються

Значення у відповідь може бути використано для повернення однієї змінної, що ми і бачили в попередньому прикладі. Використання параметра Output дозволяє процедурі відправити одне або кілька значень змінних для сторони, що викликає. Вихідний параметр позначається саме цим ключовим словом «Output» під час створення процедури. Якщо параметр заданий як вихідний параметр, то об'єкт процедури повинен надати йому значення. Збережені процедури SQL, приклади яких можна побачити нижче, у разі повертаються з підсумковою інформацією.

У нашому прикладі буде два вихідних імені: @ TotalAuthors і @ TotalNoContract. Вони вказуються у списку параметрів. Ці змінні надають значення всередині тіла процедури. Коли ми використовуємо вихідні параметри, викликаючий абонентможе бачити значення, встановлене всередині процедури тіла.

Крім того, у попередньому сценарії дві змінні оголошуються, щоб побачити значення, які встановлюють збережені процедури MS SQL Server у вихідному параметрі. Тоді процедура виконується шляхом подання нормального значення параметра CA. Наступні параметри є вихідними і, отже, оголошені змінні передаються у порядку. Зверніть увагу, що при проходженні змінних вихідне ключове слово також задається тут. Після того, як процедура виконана успішно, значення, які повертаються за допомогою вихідних параметрів, виводяться на вікно повідомлень.

4.3 Вибір однієї з процедур SQL, що зберігаються

Ця техніка використовується для повернення набору значень у вигляді таблиці даних (RecordSet) до зухвалої процедури, що зберігається. В цьому приклад SQLпроцедура, що зберігається, з параметрами @AuthID запитує таблицю «Автори» шляхом фільтрації повертаються записів за допомогою цього параметра @AuthId. Оператор Select вирішує, що має бути повернено викликаючому процедури, що зберігається. При виконанні процедури, що зберігається AuthId передається назад. Така процедура тут завжди повертає лише один запис або взагалі жодної. Але процедура, що зберігається, не має жодних обмежень на повернення більше одного запису. Нерідко можна зустріти приклади, у яких повернення даних із використанням обраних параметрів з участю обчислених змінних відбувається шляхом надання кількох підсумкових значень.

На закінчення

Процедура, що зберігається, є досить серйозним програмним модулем, що повертає або передає, а також встановлює необхідні змінні завдяки клієнтському додатку. Оскільки процедура, що зберігається, виконується на сервері сама, обміну даними у величезних обсягах між сервером і клієнтським додатком (для деяких обчислень) можна уникнути. Це дозволяє знижувати навантаження на сервер SQL, що, звичайно ж, йде на руку їх власникам. Одним з підвидів є процедури T SQL, що зберігаються, проте їх вивчення необхідне тим, хто займається створенням значних баз даних. Також існує велика, навіть величезна кількість нюансів, які можуть бути корисні при вивченні процедур, що зберігаються, проте це потрібно більше для тих, хто планує щільно зайнятися програмуванням, у тому числі професійно.

У Microsoft SQL Server для реалізації та автоматизації своїх власних алгоритмів ( розрахунків) можна використовувати процедури, що зберігаються, тому сьогодні ми з Вами поговоримо про те, як вони створюються, змінюються і видаляються.

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

Примітка! Початківцям програмістам рекомендую наступні корисні матеріали на тему T-SQL:

• Для більш детального вивчення мови T-SQLтакож рекомендую почитати книгу - Шлях програміста T-SQL. Самовчитель з мови Transact-SQL;
• Професійні онлайн-курси з T-SQL

Що таке процедури, що зберігаються в T-SQL?

Збережені процедури– це об'єкти бази даних, у яких закладено алгоритм як набору SQL інструкцій. Іншими словами, можна сказати, що процедури, що зберігаються - це програми всередині бази даних. Збережені процедури використовуються для збереження на сервері повторно використовуваного коду, наприклад, Ви написали якийсь алгоритм, послідовний розрахунок або багатокрокову SQL інструкцію, і щоб кожен раз не виконувати всі інструкції, що входять до цього алгоритму, Ви можете оформити його у вигляді процедури, що зберігається. При цьому, коли Ви створюєте процедуру SQL, сервер компілює код, а потім, при кожному запуску цієї процедури SQL сервер не буде повторно його компілювати.

Для того, щоб запустити процедуру, що зберігається в SQL Server, необхідно перед її назвою написати команду EXECUTE, також можливе скорочене написання цієї команди EXEC. Викликати збережену процедуру в інструкції SELECT , наприклад, як функцію не вийде, тобто. процедури запускаються окремо.

У процедурах, що зберігаються, на відміну від функцій, вже можна виконувати операції модифікації даних такі як: UNSERT, UPDATE, DELETE. Також у процедурах можна використовувати SQL інструкціїпрактично будь-якого типу, наприклад, CREATE TABLE до створення таблиць чи EXECUTE, тобто. виклик інших процедур. Виняток складає кілька типів інструкцій таких як: створення або зміна функцій, уявлень, тригерів, створення схем і ще кілька інших подібних інструкцій, наприклад, також не можна в процедурі, що зберігається, перемикати контекст підключення до бази даних (USE).

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

Збережені процедури дуже корисні, вони допомагають нам автоматизувати або спростити багато операцій, наприклад Вам постійно потрібно формувати різні складні аналітичні звіти з використанням зведених таблиць, тобто. оператор PIVOT. Щоб спростити формування запитів із цим оператором ( як Ви знаєте, у PIVOT синтаксис досить складний), Ви можете написати процедуру, яка буде динамічно формувати зведені звіти, наприклад, у матеріалі «Динамічний PIVOT в T-SQL» представлений приклад реалізації даної можливості у вигляді процедури, що зберігається.

Приклади роботи з процедурами, що зберігаються в Microsoft SQL Server

Вихідні дані для прикладів

Всі приклади нижче будуть виконані в Microsoft SQL Server 2016 Express. Для того щоб продемонструвати, як працюють процедури, що зберігаються з реальними даними, нам потрібні ці дані, давайте їх створимо. Наприклад, давайте створимо тестову таблицю і додамо до неї кілька записів, припустимо, що це буде таблиця, що містить список товарів за їх ціною.

Інструкція створення таблиці CREATE TABLE TestTable( INT IDENTITY(1,1) NOT NULL, INT NOT NULL, VARCHAR(100) NOT NULL, MONEY NULL) GO -- Інструкція додавання даних INSERT INTO TestTable(CategoryId, ProductName, Price) VALUES (1 , "Миша", 100), (1, "Клавіатура", 200), (2, "Телефон", 400) GO --Запит на вибірку SELECT * FROM TestTable

Дані є, тепер давайте переходити до створення збережених процедур.

Створення процедури, що зберігається на T-SQL – інструкція CREATE PROCEDURE

Збережені процедури створюються за допомогою інструкції CREATE PROCEDUREПісля цієї інструкції Ви повинні написати назву Вашої процедури, потім у разі потреби в дужках визначити вхідні та вихідні параметри. Після цього Ви пишіть ключове слово AS та відкриваєте блок інструкцій ключовим словом BEGIN, закриваєте цей блок словом END. Усередині цього блоку Ви пишіть усі інструкції, які реалізують Ваш алгоритм або якийсь послідовний розрахунок, інакше кажучи, програмуєте на T-SQL.

Наприклад давайте напишемо збережену процедуру, яка додаватиме новий запис, тобто. новий товар до нашої тестової таблиці. Для цього ми визначимо три вхідні параметри: @CategoryId – ідентифікатор категорії товару, @ProductName – найменування товару та @Price – ціна товару, даний параметрбуде ми необов'язковий, тобто. його можна буде не передавати в процедуру ( наприклад, ми не знаємо ще ціну), для цього в його визначенні ми задамо значення за замовчуванням. Ці параметри у тілі процедури, тобто. в блоці BEGIN ... END можна використовувати, так само як і звичайні змінні ( як Ви знаєте, змінні позначаються знаком @). Якщо Вам потрібно вказати вихідні параметри, то після назви параметра вказуйте ключове слово OUTPUT ( або скорочено OUT).

У блоці BEGIN…END ми напишемо інструкцію додавання даних, а також в завершенні процедури інструкцію SELECT, щоб процедура, що зберігається, повернула нам табличні дані про товари у зазначеній категорії з урахуванням нового, щойно доданого товару. Також у цій процедурі я додав обробку вхідного параметра, а саме видалення зайвих прогалин на початку і в кінці текстового рядка з метою виключення ситуацій, коли випадково занесли кілька прогалин.

Ось код цієї процедури ( його я також прокоментував).

Створюємо процедуру CREATE PROCEDURE TestProcedure (--Вхідні параметри @CategoryId INT, @ProductName VARCHAR(100), @Price MONEY = 0) AS BEGIN --Інструкції, що реалізують Ваш алгоритм --Обробка вхідних параметрів --Видалення зайвих прогалин на початку і в кінці текстового рядка SET @ProductName = LTRIM(RTRIM(@ProductName)); --Додаємо новий запис INSERT INTO TestTable(CategoryId, ProductName, Price) VALUES (@CategoryId, @ProductName, @Price) --Повертаємо дані SELECT * FROM TestTable WHERE CategoryId = @CategoryId END GO

Запуск процедури, що зберігається на T-SQL - команда EXECUTE

Запустити цю процедуру, як я вже зазначав, можна за допомогою команди EXECUTE або EXEC. Вхідні параметри передаються до процедур шляхом простого їх перерахування та вказівки відповідних значень після назви процедури ( для вихідних параметрів також потрібно вказувати команду OUTPUT). Однак назву параметрів можна і не вказувати, але в цьому випадку необхідно дотримуватись послідовності вказівки значень, тобто. вказувати значення в тому порядку, в якому визначено вхідні параметри ( це стосується і вихідних параметрів).

Параметри, які мають значення за промовчанням, можна і не вказувати, це так звані необов'язкові параметри.

Ось кілька різних, але еквівалентних способів запуску процедур, що зберігаються, зокрема нашої тестової процедури.

1. Викликаємо процедуру без вказівки ціни EXECUTE TestProcedure @CategoryId = 1, @ProductName = "Тестовий товар 1"-2. Викликаємо процедуру із зазначенням ціни EXEC TestProcedure @CategoryId = 1, @ProductName = "Тестовий товар 2", @Price = 300-3. Викликаємо процедуру, не вказуючи назву параметрів EXEC TestProcedure 1, "Тестовий товар 3", 400

Зміна процедури, що зберігається на T-SQL – інструкція ALTER PROCEDURE

Внести зміни до алгоритму роботи процедури можна за допомогою інструкції ALTER PROCEDURE. Іншими словами, щоб змінити вже існуючу процедуру, Вам достатньо замість CREATE PROCEDURE написати ALTER PROCEDURE, а все інше змінювати по необхідності.

Припустимо, нам необхідно внести зміни до нашої тестової процедури, скажімо, параметр @Price, тобто. ціну, ми зробимо обов'язковим, для цього приберемо значення за умовчанням, а також уявімо, що у нас зникла необхідність в отриманні результуючого набору даних, для цього ми просто приберемо інструкцію SELECT із процедури, що зберігається.

Змінюємо процедуру ALTER PROCEDURE TestProcedure (--Вхідні параметри @CategoryId INT, @ProductName VARCHAR(100), @Price MONEY) AS BEGIN --Інструкції, що реалізують Ваш алгоритм --Обробка вхідних параметрів --Видалення зайвих прогалин на початку та в кінці текстової рядки SET @ProductName = LTRIM(RTRIM(@ProductName)); --Додаємо новий запис INSERT INTO TestTable(CategoryId, ProductName, Price) VALUES (@CategoryId, @ProductName, @Price) END GO

Видалення процедури, що зберігається на T-SQL – інструкція DROP PROCEDURE

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

Наприклад, давайте видалимо створену нами тестову процедуру.

DROP PROCEDURE TestProcedure

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

У мене все, сподіваюся, матеріал був Вам цікавий і корисний, поки що!

Для програмування розширених процедур, що зберігаються, Microsoft надає ODS (Open Data Service) API набір макросів і функцій, що використовуються для побудови серверних додатків дозволяють розширити функціональність MS SQL Server 2000.

Розширені процедури, що зберігаються - це звичайні функції написані на С/C++ із застосуванням ODS API і WIN32 API, оформлені у вигляді бібліотеки динамічного компонування (dll) і покликані, як я вже говорив, розширювати функціональність SQL сервера. ODS API надає розробнику багатий набір функцій дозволяють передавати дані клієнту, отримані будь-яких зовнішніх джерел даних (data source) як звичайних наборів записів (record set). Також, extended stored procedure може повертати значення через переданий їй параметр (OUTPUT parametr).

Як працюють розширені процедури, що зберігаються.

• Коли клієнтська програма викликає розширену процедуру, що зберігається, запит передається в TDS форматі через мережеву бібліотеку Net-Libraries і Open Data Service ядру MS SQL SERVER.
• SQL Sever знаходить dll бібліотеку асоційовану з ім'ям розширеної зберігання процедури і завантажує її в свій контекст, якщо вона не була завантажена туди раніше, і викликає розширену збережену процедуру, реалізовану як функцію всередині dll.
• Розширена процедура, що зберігається, виконує на сервері необхідні їй дії і передає набір результатів клієнтському додатку, використовуючи сервіс, що надається ODS API.

Особливості розширених процедур, що зберігаються.

• Розширені процедури, що зберігаються - це функції виконуються в адресному просторі MS SQL Server і в контексті безпеки облікового записупід якою запущено службу MS SQL Server;
• Після того, як dll бібліотека з розширеними процедурами, що зберігаються, була завантажена в пам'ять, вона залишається там до тих пір, поки SQL Server не буде зупинений, або поки адміністратор не вивантажить її примусово, використовуючи команду:
  DBCC DLL_name (FREE).
• Розширена процедура, що зберігається, запускається на виконання так само, як і звичайна процедура, що зберігається:
  EXECUTE xp_extendedProcName @param1, @param2 OUTPUT
  @param1 вхідний параметр
  @param2 вхідний/вихідний параметр

Створення розширених процедур, що зберігаються.

Розширена процедура, що зберігається ця функція має наступний прототип:

SRVRETCODE xp_extendedProcName (SRVPROC * pSrvProc);

Параметр pSrvProcпокажчик на SRVPROC структуру, яка є описником (handle) кожного конкретного клієнтського підключення. Поля цієї структури недокументовані та містять інформацію, яку бібліотека ODS використовує для управління комунікацією та даними між серверним додатком (Open Data Services server application) та клієнтом. У будь-якому випадку Вам не потрібно звертатися до цієї структури і тим більше не можна модифікувати її. Цей параметр потрібно вказувати під час виклику будь-якої функції ODS API, тому надалі я не зупинятимусь на його описі.
Використання префікса xp_ необов'язкове, однак існує угода починати ім'я розширеної процедури, що зберігається, саме так, щоб підкреслити відмінність від звичайної збереженої процедури, імена яких, як Ви знаєте, прийнято починати з префікса sp_.
Також слід пам'ятати, що імена розширених процедур, що зберігаються, чутливі до регістру. Не забувайте про це, коли викликатимете розширену процедуру, що зберігається, інакше замість очікуваного результату, Ви отримаєте повідомлення про помилку.
Якщо Вам необхідно написати код ініціалізації/деініціалізації dll, використовуйте для цього стандартну функцію DllMain(). Якщо у Вас немає такої необхідності і ви не хочете писати DLLMain(), то компілятор збере свою версію функції DLLMain(), яка нічого не робить, а просто повертає TRUE. Усі функції, що викликаються з dll (тобто розширені збережені процедури) повинні бути оголошені як експортовані. Якщо Ви пишете на MS Visual C++, використовуйте директиву __declspec(dllexport). Якщо компілятор не підтримує цю директиву, опишіть експортовану функцію в секції EXPORTS в DEF файлі.
Отже, для створення проекту нам знадобляться такі файли:

• Srv.h заголовний файл, містить опис функцій та макросів ODS API;
• Opends60.lib файл імпорту бібліотеки Opends60.dll, яка і реалізує весь сервіс, що надається ODS API.

Declspec(dllexport) ULONG __GetXpVersion()
{
return ODS_VERSION;
}

Коли MS SQL Server завантажує DLL c extended stored procedure, він насамперед викликає цю функцію, щоб отримати інформацію про версію бібліотеки.

Для написання своєї першої extended stored procedure Вам знадобиться встановити на свій комп'ютер:

MS SQL Server 2000 будь-якої редакції (я маю Personal Edition). У процесі інсталяції обов'язково оберіть опцію source sample
- MS Visual C++ (я використав версію 7.0), але точно знаю підійде і 6.0

Установка SQL Server -a потрібна для тестування та налагодження вашої DLL. Можливе і налагодження по мережі, але я цього ніколи не робив, і тому встановив все на свій локальний диск. У постачання Microsoft Visual C++ 7.0 редакції Interprise Edition входить майстер Extended Stored Procedure DLL Wizard. В принципі, нічого понад природне він не робить, а тільки генерує заготівлю шаблон розширеної процедури, що зберігається. Якщо Вам подобаються фахівці, можете використовувати його. Я ж волію робити все ручками, і тому не розглядатиму цей випадок.

Тепер до справи:
- Запустіть Visual C++ і створіть новий проект – Win32 Dynamic Link Library.
- Увімкніть у проект заголовковий файл - #include ;
- Зайдіть в меню Tools => Options і додайте шляхи пошуку include та library файлів. Якщо при установці MS SQL Server Ви нічого не змінювали, то задайте:

C:Program FilesMicrosoft SQL Server80ToolsDevToolsInclude для заголовних файлів;
- C:Program FilesMicrosoft SQL Server80ToolsDevToolsLib для файлів бібліотеки.
- Вкажіть ім'я файлу бібліотеки opends60.lib в опціях лінкера.

У цьому підготовчий етап закінчено, можна розпочинати написання своєї першої extended stored procedure.

Постановка задачі.

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

Розробити розширену процедуру для MS SQL Server 2000, яка отримує повний списоккористувачів зареєстрованих у домені, і повертає його клієнту як стандартного набору записів (record set). Як перший вхідний параметр функція отримує ім'я сервера містить базу даних каталогу (Active Directory), тобто ім'я контролера домену. Якщо цей параметр дорівнює NULL, необхідно передати клієнту список локальних груп. Другий параметр буде використовуватися extended stored procedure для повернення значення результату успішної/неуспішної роботи (OUTPUT параметр). Якщо розширена процедура, що зберігається, виконана успішно, тоді необхідно передати кількість записів повернутих в клієнтський record set , якщо в процесі роботи не вдалося отримати необхідну інформацію, значення другого параметра необхідно встановити в -1, як ознака неуспішного завершення.

Умовний прототип розширеної процедури, що зберігається наступний:

xp_GetUserList(@NameServer varchar, @CountRec int OUTPUT);

#include
#include
#define XP_NOERROR 0
#define XP_ERROR -1


__declspec(dllexport) SERVRETCODE xp_GetGroupList(SRVPROC* pSrvProc)
{

//Перевірка кількості переданих параметрів

//Перевірка типу переданих параметрів

//Перевірка, чи є параметр 2 OUTPUT параметром

//Перевірка, чи параметр 2 має достатню довжину для збереження значення

//Отримання вхідних параметрів

//Отримання списку користувачів

// Посилання отриманих даних клієнту як стандартного набору записів (record set)

//Установка значення OUTPUT параметра

return (XP_NOERROR);
}

Робота з вхідними параметрами

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

Перша дія, яку має виконати наша exteneded stored procedure - отримати параметри, які були передані їй при викликі. Дотримуючись наведеного вище алгоритму, нам необхідно виконати такі дії:

Визначити кількість переданих параметрів;
- переконається, що передані параметри мають правильний тип даних;
- Переконатись, що вказаний OUTPUT параметр має достатню довжину, для збереження в ньому значення повертається нашою extended stored procedure.
- отримати передані параметри;
- Встановити значення вихідного параметра як результат успішного/неуспішного завершення роботи extended stored procedure.

Тепер докладно розглянемо кожен пункт:

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

Для отримання кількості переданих параметрів необхідно використати функцію:

int srv_rpcparams (SRV_PROC * srvproc);

Визначення типу даних та довжини переданих параметрів

Для отримання інформації про тип та довжину переданих параметрів Microsoft рекомендує використовувати функцію srv_paramifo. Ця універсальна функція замінює виклики srv_paramtype, srv_paramlen, srv_parammaxlen, які вважаються застарілими. Ось її прототип:

int srv_paraminfo (
SRV_PROC * srvproc,
int n,
BYTE * pbType,
ULONG* pcbMaxLen,
ULONG * pcbActualLen,
BYTE * pbData,
BOOL * pfNull);

Перевірка, чи є другим параметром OUTPUT параметром.

Функція srv_paramstatus() призначена для визначення статусу переданого параметра:

int srv_paramstatus (
SRV_PROC * srvproc,
int n
);

n номер параметра переданого в розширену збережену процедуру під час виклику. Нагадаю: параметри завжди нумеруються з 1.
Для повернення значення srv_paramstatus використовує нульовий біт. Якщо він встановлений в 1 переданий параметр є параметром OUTPUT, якщо в 0 звичайним параметром, переданим за значенням. Якщо, exteneded stored procedure була викликана без параметрів, функція поверне -1.

Встановіть значення вихідного параметра.

Вихідному параметру, переданому в розширену збережену, можна передати значення, використовуючи функцію srv_paramsetoutput. Ця нова функція замінює виклик функції srv_paramset, яка тепер вважається застарілою, т.к. не підтримує нові типи даних, введені в ODS API та дані нульової довжини.

int srv_paramsetoutput (
SRV_PROC *srvproc,
int n,
BYTE *pbData,
ULONG cbLen,
BOOL fNull
);

#include

#define XP_NOERROR 0
#define XP_ERROR 1

#define MAX_SERVER_ERROR 20000
#define XP_HELLO_ERROR MAX_SERVER_ERROR+1

void printError (SRV_PROC*, CHAR*);

#ifdef __cplusplus
extern "C" (
#endif

SRVRETCODE __declspec(dllexport) xp_helloworld(SRV_PROC* pSrvProc);

#ifdef __cplusplus
}
#endif

SRVRETCODE xp_helloworld(SRV_PROC* pSrvProc)
{
char szText = "Hello World!";
BYTE bType;
ULONG cbMaxLen;
ULONG cbActualLen;
BOOL fNull;

/* Визначення кількості переданих у розширену збережену
процедуру параметрів */
if (srv_rpcparams(pSrvProc) != 1)
{
printError(pSrvProc, "Не правильна кількість параметрів!");
return (XP_ERROR);
}

/* Отримання інформації про тип даних та довжину переданих параметрів */
if (srv_paraminfo(pSrvProc, 1, &bType, &cbMaxLen,
&cbActualLen, NULL, &fNull) == FAIL)
{
printError (pSrvProc,
"Не вдається отримати інформацію про вхідні параметри...");
return (XP_ERROR);
}

/* Перевіряємо, чи є переданий параметр OUTPUT параметром */
if ((srv_paramstatus(pSrvProc, 1) & SRV_PARAMRETURN) == FAIL)
{
printError (pSrvProc,
"Переданий параметр не є OUTPUT параметром!");
return (XP_ERROR);
}

/* Перевіряємо тип даних переданого параметра */
if (bType != SRVBIGVARCHAR && bType != SRVBIGCHAR)
{
printError (pSrvProc, "Неправильний тип переданого параметра!");
return (XP_ERROR);
}

/* Переконаємося, що переданий параметр має достатню довжину для збереження рядка, що повертається */
if (cbMaxLen< strlen(szText))
{
printError (pSrvProc,
"Передано параметр недостатньої довжини для збереження n рядка, що повертається!");
return (XP_ERROR);
}

/* Встановлюємо значення OUTPUT параметра */
if (FAIL == srv_paramsetoutput(pSrvProc, 1, (BYTE*)szText, 13, FALSE))
{
printError (pSrvProc,
"Не можу встановити значення OUTPUT параметра...");
return (XP_ERROR);
}

srv_senddone(pSrvProc, (SRV_DONE_COUNT | SRV_DONE_MORE), 0, 1);
return (XP_NOERROR);
}

void printError (SRV_PROC *pSrvProc, CHAR* szErrorMsg)
{
srv_sendmsg(pSrvProc, SRV_MSG_ERROR, XP_HELLO_ERROR, SRV_INFO, 1,
NULL, 0, 0, szErrorMsg,SRV_NULLTERM);

Srv_senddone(pSrvProc, (SRV_DONE_ERROR | SRV_DONE_MORE), 0, 0);
}

Не розглянутими залишилися функції srv_sendmsg та srv_senddone. Функція srv_sendmsg використовується для надсилання повідомлень клієнту. Ось її прототип:

int srv_sendmsg (
SRV_PROC * srvproc,
int msgtype,
DBINT msgnum,
DBTINYINT class,
DBTINYINT state,
DBCHAR * rpcname,
int rpcnamelen,
DBUSMALLINT linenum,
DBCHAR * message,
int msglen
);

У процесі роботи розширена процедура, що зберігається, повинна регулярно повідомляти клієнтському додатку свій статус, тобто. надсилати повідомлення про виконані дії. Для цього і призначено функцію srv_senddone:

int srv_senddone (
SRV_PROC * srvproc,
DBUSMALLINT status,
DBUSMALLINT info,
DBINT count
);

статус статус прапор. Значення цього параметра можна задавати за допомогою логічних операторів AND і OR для комбінування констант наведених у таблиці:
Status flag Опис
SRV_DONE_FINAL Поточний набір результатів є остаточним;
SRV_DONE_MORE Поточний набір результатів не є остаточним слід очікувати на чергову порцію даних;
SRV_DONE_COUNT Параметр count містить правильне значення
SRV_DONE_ERROR Використовується для сповіщення про помилки та негайне завершення.
intoзарезервований, необхідно встановити 0.
count кількість результуючих наборів даних, що посилаються клієнту. Якщо прапор status встановлений в SRV_DONE_COUNT, то count повинен містити правильну кількість наборів, що посилається клієнту, в записів.
Значення, що повертаються:
- у разі успіху SUCCEED
- При невдачі FAIL.

Установка розширених процедур, що зберігаються на MS SQL Server 2000

1.Скопіюйте dll бібліотеку з розширеною збереженою процедурою в каталог binn на машині з встановленим MS SQL Server. У мене цей шлях наступний: C:Program FilesMicrosoft SQL ServerMSSQLBinn;
2.Зареєструйте розширену процедуру, що зберігається на серверt, виконавши наступний скрипт:

USE Master
EXECUTE SP_ADDEXTENDEDPROC xp_helloworld, xp_helloworld.dll

Протестуйте роботу xp_helloworld, виконавши такий скрипт:

DECLARE @Param varchar(33)
EXECUTE xp_helloworld @Param OUTPUT
SELECT @Param AS OUTPUT_Param

Висновок

На цьому першу частину моєї статті закінчено. Тепер я впевнений, Ви готові впоратися з нашим технічним завданням на всі 100%. У наступній статті Ви дізнаєтесь:
- Типи даних, визначені в ODS API;
- особливості налагодження розширених збережених процедур;
- як формувати recordset-и та передавати їх клієнтському додатку;
- Частково ми розглянемо функції Active Directory Network Manegment API, необхідні для отримання списку доменних користувачів;
- Створимо готовий проект (реалізуємо наше технічне завдання)
Сподіваюся – до швидкої зустрічі!

PS: файли прикладу для статті завантажити для студії 7.0