22 відповідей
У моєму досвіді написання в основному програм WinForms Client/Server це прості висновки, до яких я прийшов:
Використовувати процедури, що зберігаються:
- Для будь-якої складної роботи з даними. Якщо ви збираєтеся робити щось дійсно потребує таблиць курсору або temp, це зазвичай найшвидший спосіб зробити це в SQL Server.
- Якщо вам потрібно заблокувати доступ до даних. Якщо ви не надаєте доступ до таблиці користувачам (або ролі або чогось ще), ви можете бути впевнені, що єдиний спосіб взаємодії з даними - через СП, що створюється.
Використовувати спеціальні запити:
- Для CRUD, коли вам не потрібно обмежувати доступ до даних (чи це робите по-іншому).
- Для найпростіших пошуків. Створення SP для безлічі критеріїв пошуку – це біль та складність в обслуговуванні. Якщо ви можете створити швидкий пошуковий запит, використовуйте це.
У більшості моїх додатків я використовував як SP, так і ad-hoc sql, хоча я вважаю, що я використовую SP все менше і менше, оскільки вони в кінцевому підсумку є кодом як С#, тільки складніше контролювати, тестувати і підтримувати. Я рекомендував би використовувати ad-hoc sql, якщо ви не можете знайти конкретну причину.
Збережені процедури є контрактом на програмне забезпечення, який інкапсулює дії, вжиті проти бази даних. Код у процедурах і навіть сама схема бази даних може бути змінений без впливу на скомпільований, розгорнутий код, тому входи та виходи процедури залишаються незмінними.
Вбудовуючи запити у вашу програму, ви тісно пов'язуєте себе з вашою моделлю даних.
З тієї ж причини також не є гарною практикою просто створювати збережені процедури, які є запитами CRUD для кожної таблиці у вашій базі даних, так як це ще тісно пов'язано. Натомість процедури повинні бути громіздкими, крупнозернистими.
Я думаю, що це основний конфлікт між людьми, які повинні підтримувати базу даних і людей, які розробляють інтерфейси користувача.
Як людина з даними, я не став би розглядати роботу з базою даних, до якої звертаються через adhoc-запити, тому що їх важко ефективно налаштовувати або керувати. Як я можу дізнатися, що впливатиме на зміну схеми? Крім того, я не думаю, що користувачам слід надавати прямий доступ до таблиць бази даних з міркувань безпеки (і я маю на увазі не тільки атаки SQL-ін'єкцій, але також тому, що це базовий внутрішній елемент управління, який не допускає прямих прав і вимагає від усіх користувачів використовуйте тільки procs, призначені для програми, щоб запобігти можливому шахрайству.
Бази даних є об'єктно-орієнтованими, а код, який здається хорошим з об'єктно-орієнтованої точки зору, може бути вкрай поганим з точки зору бази даних.
Наші розробники повідомляють нам, що вони раді, що весь наш доступ до баз даних здійснюється через procs, тому що він значно прискорює виправлення помилки, яка залежить від даних, а потім просто запускає proc в робочому середовищі, а не створює нову гілку коду і перекомпілювати і перезавантажити у виробництво. Ми вимагаємо, щоб усі наші процеси були у підривній діяльності, тому контроль джерела не є проблемою загалом. Якщо він не знаходиться в Subversion, він періодично видалятиметься dbas, тому немає опору використанню Source Control.
Збережені процедури, безумовно, підходять... вони скомпільовані, мають план виконання перед початком роботи, і ви можете керувати правами на них.
Я не розумію цю проблему з вихідним кодом на процедурі, що зберігається. Ви напевно можете їх контролювати, якщо ви трохи дисципліновані.
Завжди починайте з файла.sql, який є джерелом процедури, що зберігається. Помістіть його в керування версіями після того, як ви написали код. Наступного разу, коли ви захочете відредагувати свою процедуру, що зберігається, ви отримаєте її зі свого вихідного елемента управління, ніж ваша база даних. Якщо ви підете цьому, у вас буде таке ж хороше джерело управління, як і ваш код.
Я хотів би процитувати Tom Kyte від Oracle тут... Ось його правило про те, де писати код... хоч і трохи незв'язаний, але добре знаю, я думаю.
У нашому додатку є шар коду, який надає вміст запиту (а іноді й виклик процедури, що зберігається). Це дозволяє нам:
- легко отримати всі запити під час керування версіями
- щоб зробити всі зміни для кожного запиту для різних серверів баз даних
- виключає повторення одного і того ж коду запиту через наш код
Контроль доступу реалізується в середньому шарі, а не в базі даних, тому нам не потрібні процедури, що зберігаються. Це певною мірою середня дорога між спеціальними запитами і процедурами, що зберігаються.
Існують переконливі аргументи для обох процедур, що зберігаються, які знаходяться в центральному репозиторії, але (потенційно) важко переноситься, а спеціальні запити легше налагоджувати, оскільки вони з вашим кодом, але вони також можуть бути складніше знайти в коді.
Аргумент, що процедури, що зберігаються, більш ефективні, більше не містить води. текст посилання
Виконання google для процедури, що зберігається vs Dynamic Query покаже гідні аргументи в будь-якому випадку і, ймовірно, краще для вас прийняти ваше власне рішення.
Деякі речі, про які потрібно подумати: Кому потрібні процедури, що зберігаються, Anyways?
Ясно, що це питання ваших власних потреб та переваг, але дуже важливо подумати про те, що при використанні спеціальних запитів у середовищі, орієнтованому на громадськість, є безпека. Завжди виконуйте їхню параметризацію і слідкуйте за типовими вразливістю, такими як SQL-ін'єкції.
Збережені процедури чудові, тому що вони можуть бути змінені без перекомпіляції. Я постараюся використовувати їх якнайчастіше.
Я використовую ad-hoc для запитів, які динамічно генеруються на основі введення користувача.
Procs з причин, згаданих іншими, а також простіше налаштувати proc за допомогою профілів або частин proc. Таким чином, вам не потрібно розповідати будь-кому про запуск своєї програми, щоб дізнатися, що відправляється на сервер SQL
Якщо ви використовуєте запити ad-hoc, переконайтеся, що вони параметризовані
Параметрований SQL або SPROC... немає значення з погляду продуктивності... ви можете запросити оптимізацію однієї з них.
Для мене остання перевага SPROC полягає в тому, що я можу виключити багато прав на управління правами SQL, тільки надаючи свої права на вхід для виконання sprocs... якщо ви використовуєте Parametized SQL, логін, пов'язаний з вашим рядком підключення, має набагато більше (запис будь-якого виду оператора вибору на одну з таблиць, до яких у них є доступ, наприклад).
Я, як і раніше, віддаю перевагу параметризованому SQL, хоча...
Аргумент продуктивності sproc є спірним - 3 верхні RDBM використовують кешування плану запитів і деякий час. Його документально підтвердили... Чи ще 1995?
Однак вбудовування SQL у вашу програму також є жахливим дизайном - обслуговування коду, мабуть, є недостатньою концепцією для багатьох.
Якщо програма може починатися з нуля за допомогою ORM (додатки із зеленим полем далекі від кількох!), це відмінний вибір, оскільки модель вашого класу керує вашою моделлю БД та заощаджує час.
Якщо структура ORM недоступна, ми використовували гібридний підхід створення XML файлу SQL-ресурсів для пошуку рядків SQL за необхідності (вони потім кешуються інфраструктурою ресурсів). Якщо SQL потребує якихось незначних маніпуляцій, виконаних у коді, - якщо потрібна велика маніпуляція рядком SQL, ми переосмислимо цей підхід.
Цей гібридний підхід полегшує управління розробниками (можливо, ми є меншістю, оскільки моя команда є досить яскравою, щоб читати план запиту), а розгортання - це проста перевірка з SVN. Крім того, він спрощує комутацію RDBM - просто замініть файл ресурсів SQL (не так просто, як інструмент ORM, звичайно, але це працює зі застарілими системами або базою даних, що не підтримується)
Мій досвід полягає в тому, що 90% запитів і/або процедур, що зберігаються, взагалі не повинні записуватися (принаймні, вручну).
Доступ до даних має генеруватися автоматично. Ви можете вирішити, чи хочете статичне генерування процедур під час компіляції або динамічно під час виконання, але якщо ви хочете додати стовпець в таблицю (властивість об'єкта), ви повинні змінити тільки один файл.
Я волію зберігати всі дані доступкод програми, в якому рівень доступу до даних виконує прямі SQL-запити. З іншого боку, логіка управління, яку я помістив у базу даних у вигляді тригерів, збережених процедур, функцій користувача і ще чогось. Прикладом того, що я вважаю гідним бази даних, є генерація даних - припустимо, що наш клієнт має ім'я FirstName і LastName. Тепер для інтерфейсу користувача потрібно DisplayName, яке виводиться з деякої нетривіальної логіки. Для цього покоління я створюю процедуру, що зберігається, яка потім запускається тригером щоразу, коли оновлюється рядок (або інші вихідні дані).
Схоже, що це дещо поширене непорозуміння, що рівень доступу до даних – це база даних, і все, що стосується доступу до даних та даних, відбувається саме там. Це просто неправильно, але я бачу багато проектів, які випливають із цієї ідеї. Можливо це локальний феномонон.
У попередній статті даного циклу ми розглянули, як можна витягти дані з таблиць, модифікувати їх структуру, створювати, модифікувати та видаляти бази даних та об'єкти, що в них містяться. У цій статті ми поговоримо більш докладно про об'єкти, характерні для серверних СУБД: уявлення, тригери і процедури, що зберігаються.
У першій статті даного циклу, опублікованій у № 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 AS select_statement
Аргумент view_name вказує ім'я уявлення. Ключове слово , що використовується в Microsoft SQL Server, дозволяє приховати вихідний текст пропозиції CREATE VIEW таблиці syscomments.
Ключове слово AS вказує, який запит SELECT реально виконуватиметься при зверненні до подання. Зверніть увагу, що цей запит не може містити ключові слова ORDER BY, COMPUTE або COMPUTE BY, INTO і не може посилатися на тимчасову таблицю.
Для модифікації створеного раніше уявлення слід використовувати пропозицію ALTER 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 [(@parameter data_type) [= default] ] [...] 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, коротко описану в наступному розділі.
Пропозиція 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. Вище ми вже описували деякі з часто використовуваних системних процедур, що зберігаються.
Зверніть увагу, що тригери не повинні повертати користувачеві дані.
У пропозиції CREATE TRIGGER можна використовувати дві спеціальні таблиці. Наприклад, таблиці deleted і inserted мають таку ж структуру, як і таблиця, на яку визначено тригер, і містять старе і нове значення записів, змінених користувачем. Наприклад, ми можемо використовувати таку SQL-пропозицію для пошуку віддалених записів:
SELECT * FROM deleted
У табл. 3 показано вміст таблиць deleted та inserted для всіх можливих змін даних.
Для зміни наявного тригера слід використовувати пропозицію ALTER TRIGGER. Ми поговоримо про нього у наступному розділі.
Для початку нам потрібно додати до таблиці два нових поля, в яких будуть утримуватися ці відомості. Назвемо їх UpdatedBy (ім'я менеджера, що оновив запис останнім) та UpdatedWhen (час, коли було змінено запис). Потім створимо тригер з назвою KeepTrack. Ось його код:
CREATE TRIGGER KeepTrack ON Customers 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. Ці дані вилучаються з тимчасової таблиці, введеної.
Як бачимо, цей тригер дозволяє стежити за змінами та вставкою нових записів у таблиці.
Перед тим, як закінчити короткий огляд тригерів, ми повинні повідомити, де можна знайти відомості про тригери. Таблиця sysobjects зберігає інформацію про тригерах та його типи, а таблиця syscomments містить їх вихідний текст.
Висновок
У цій частині ми розглянули кілька типів об'єктів баз даних - процедури, уявлення і тригери, що зберігаються. Ми дізналися наступне:
- Уявлення - це віртуальна таблиця, зазвичай створювана як підмножина стовпців однієї чи кількох таблиць. Для створення уявлення застосовується пропозиція CREATE VIEW, для модифікації – пропозиція ALTER VIEW, а для видалення – пропозиція DROP VIEW.
- Процедура, що зберігається, - це скомпільований набір SQL-пропозицій, збережений в базі даних як іменований об'єкт і виконується як єдиний фрагмент коду. Для створення процедури, що зберігається, застосовується пропозиція CREATE PROCEDURE, для зміни - ALTER PROCEDURE, а для видалення - DROP PROCEDURE.
- Тригер - це спеціальний тип процедури, що зберігається, яка автоматично викликається, коли дані в певній таблиці додаються, видаляються або змінюються за допомогою SQL-пропозицій INSERT, DELETE або UPDATE. Тригери створюються за допомогою пропозиції CREATE TRIGGER. Для зміни тригера використовується пропозиція ALTER TRIGGER, а видалення - пропозиція DROP TRIGGER.
Комп'ютерПрес 12"2000
Включай у свої процедури рядок – SET NOCOUNT ON:
З кожним DML виразом, SQL server дбайливо повертає нам повідомлення, що містить кількість оброблених записів. Ця інформація може бути нам корисна під час налагодження коду, але після цього буде абсолютно марною. Прописуючи SET NOCOUNT ON ми відключаємо цю функцію. Для процедур, що містять кілька виразів або цикли, ця дія може дати значний приріст продуктивності, тому що кількість трафіку буде значно знижена.
Transact-SQL
Використовуйте ім'я схеми з ім'ям об'єкта:
Ну, тут думаю зрозуміло. Дана операція підказує серверу де шукати об'єкти і замість того, щоб безладно шариться по своїх засіках, він відразу буде знати куди йому потрібно піти і що взяти. При великій кількості баз, таблиць і процедур, що зберігаються, може значно заощадити наш час і нерви.
Transact-SQL
SELECT * FROM dbo.MyTable --Ось так робити добре -- Замість SELECT * FROM MyTable --А так робити погано --Виклик процедури EXEC dbo.MyProc --Знову ж таки добре --Замість EXEC MyProc --Погано!
Не використовуй префікс «sp_» в імені своїх процедур, що зберігаються:
Якщо ім'я нашої процедури починається з sp_, SQL Server в першу чергу шукатиме в своїй головній базі даних. Справа в тому, що даний префікс використовується для особистих внутрішніх процедур сервера, що зберігаються. Тому його використання може призвести до додаткових витрат і навіть невірного результату, якщо процедура з таким же ім'ям, як у вас, буде знайдена в його базі.
Використовуйте IF EXISTS (SELECT 1) замість IF EXISTS (SELECT *):
Щоб перевірити наявність запису в іншій таблиці, ми використовуємо IF EXISTS вираз. Даний вираз повертає true, якщо з внутрішнього виразу повертається хоч одне значення, не важливо «1», всі колонки або таблиця. Відомі дані, в принципі не використовуються. Таким чином, для стиснення трафіку під час передачі даних логічніше використовувати «1», як показано нижче.
Збережені процедури 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, що зберігаються, проте їх вивчення необхідне тим, хто займається створенням значних баз даних. Також існує велика, навіть величезна кількість нюансів, які можуть бути корисні при вивченні процедур, що зберігаються, проте це потрібно більше для тих, хто планує щільно зайнятися програмуванням, у тому числі професійно.
Коли слід використовувати процедури, що зберігаються, і коли я повинен використовувати уявлення в SQL Server?
Дозволи дозволяють створювати динамічні запити, де ми можемо надсилати параметри?
Який з них найшвидший, і на якій підставі він швидше, ніж інший?
Перегляди або процедури, що зберігаються, постійно зберігають пам'ять?
Що це означає, якщо хтось скаже, що уявлення створюють віртуальну таблицю, а процедури створюють таблицю матеріалів?
Будь ласка, дайте мені знати про більш точках, якщо вони є.
Solutions Collecting From Web of "У чому різниця між процедурою, що зберігається, і поданням?"
Вид є віртуальнутаблицю. Ви можете приєднатися до кількох таблиць у поданні та використовувати уявлення для представлення даних, якби дані надходили з однієї таблиці.
Збережена процедура використовує параметри для виконання функції … чи це оновлення та вставка даних або повернення окремих значень або наборів даних.
Створення уявлень і процедур, що зберігаються – містить деяку інформацію від Microsoft про те, коли і чому використовувати їх.
Скажімо, у мене є дві таблиці:
tbl_user Стовпці: .user_id, .user_name, .user_pw
tbl_profile Стовпці: .profile_id, .user_id .profile_description
Тому, якщо я перебуваю в запиті з цих таблиць ALOT ... замість того, щоб робити з'єднання в КОЖНІЙ peice sql, я б визначив вигляд, наприклад:
CREATE View vw_user_profile AS Select A.user_id, B.profile_description FROM tbl_user A left join tbl_profile B on A.user_id = b.user_id GO
Тому в майбутньому, якщо я хочу запросити profile_description на ідентифікатор користувача ... все, що мені потрібно зробити, це
SELECT profile_description FROM vw_user_profile WHERE user_id = @ID
Цей код можна використовувати в процедурі, що зберігається, наприклад:
Create procedure dbo.getDesc @ID int AS begin SELECT profile_description FROM vw_user_profile WHERE user_id = @ID END GO
Тому пізніше я можу зателефонувати
Dbo.getDesc 25
і я отримаю опис для ідентифікатора користувача 25 де 25 - ваш параметр.
Очевидно, що БАГАТО більше, але це лише основна ідея.
Спочатку вам потрібно зрозуміти, що обидва – різні речі. Збережені процедури краще використовувати для операторів INSERT-UPDATE-DELETE. та Подання використовуються для операторів SELECT. і ви повинні використовувати обидва.
У виставах ви не можете змінювати дані.
Перегляди: Це віртуальна таблиця, що складається з одного або кількох рядків та стовпців з різних реальних таблиць бази даних. Це шаблон рядків та стовпців кількох таблиць. Ви не можете передавати будь-які параметри тут.
Збережені процедури: вони є набором попередньо виконаних SQL-заяв, у яких ви можете відправляти параметри як вхідні дані і отримувати вихідні дані.
Уявлення можуть використовуватися в процедурі, що зберігається, але процедура, що зберігається, не може використовуватися в Views …!
Процедура сховища використовується, коли простого SQL просто недостатньо. Процедури зберігання містять змінні, цикли та виклики інших процедур, що зберігаються. Це мова програмування, а не мова запитів.
Уявлення є статичними. Подумайте про них як про нові таблиці з певним макетом, а дані в них створюються на льоту, використовуючи запит, з яким ви його створили. Як і в будь-якій таблиці SQL, ви можете сортувати та фільтрувати її за допомогою WHERE , GROUP BY та ORDER BY .
Це залежить від того, що ви робите.
Це залежить від бази даних. Прості уявлення просто запускають запит та фільтрують результат. Але такі бази даних, як Oracle, дозволяють створити "матеріалізоване" уявлення, яке в основному є таблицею, яка автоматично оновлюється при зміні базових даних виду.
Матеріалізоване уявлення дозволяє створювати індекси в стовпцях уявлення (особливо на обчислених стовпцях, які не існують ніде у базі даних).
Я не розумію, про що ви кажете.
Основна відмінність полягає в тому, що коли ви запитуєте уявлення, це визначення вставляється у ваш запит. Процедура може давати результати запиту, але вона скомпільована і так швидко. Іншим варіантом є індексовані уявлення.
SQL View – це віртуальна таблиця, що базується на запиті SQL SELECT. Подання посилається на одну або кілька існуючих таблиць бази даних або інші уявлення. Це миттєвий знімок бази даних, тоді як процедура, що зберігається, являє собою групу операторів Transact-SQL, складену в єдиний план виконання.
Перегляд – проста демонстрація даних, що зберігаються в таблицях бази даних, тоді як процедура, що зберігається, є групою операторів, які можуть бути виконані.
Подання швидше, оскільки воно відображає дані з таблиць, на які посилається, тоді як процедура сховища виконує sql-інструкції.
Перевірте цю статтю: Перегляд проти процедур, що зберігаються. Саме те, що ви шукаєте
@Patrick правильно з тим, що він сказав, але, щоб відповісти на ваші інші питання, View створить себе в пам'яті, і в залежності від типу Joins, Data і якщо буде зроблено будь-яке агрегування, це може бути досить голодний вигляд.
Збережені процедури виконують всю свою обробку або з використанням Temp Hash Table, наприклад #tmpTable1, або в пам'яті за допомогою @tmpTable1. Залежно від того, що ви хочете сказати.
Процедура, що зберігається, схожа на функцію, але називається її прямим ім'ям. замість функцій, які фактично використовуються всередині запиту.
Очевидно, більшу частину часу таблиці пам'яті швидше, якщо ви не отримуєте багато даних.
Махеш не зовсім правий, коли він припускає, що ви не можете змінювати дані у поданні. Отже, з погляду Патріка
CREATE View vw_user_profile AS Select A.user_id, B.profile_description FROM tbl_user A left join tbl_profile B on A.user_id = b.user_id
Я можу оновити дані … як приклад я можу зробити будь-який з цих …
Update vw_user_profile Set profile_description="Manager" where user_id=4
Update tbl_profile Set profile_description="Manager" where user_id=4
Ви не можете вставити в це уявлення, так як не всі поля у всій таблиці присутні, і я припускаю, що PROFILE_ID є первинним ключем і не може бути NULL. Однак іноді ви можете вставити INSERT у виставу …
Я створив уявлення для існуючої таблиці, використовуючи …
Create View Junk як SELECT * від
Insert in junk (Code,name) values ("glyn","Glyn Roberts"), ("Mary","Maryann Roberts")
DELETE from Junk Where ID>4
І INSERT, і DELETE працювали у цьому випадку
Очевидно, ви не можете оновлювати будь-які поля, які агреговані або розраховані, але будь-яке уявлення, яке є просто прямим уявленням, має бути оновлюваним.
Якщо уявлення містить більше однієї таблиці, ви не можете вставляти або видаляти, але якщо уявлення є підмножиною однієї таблиці, ви зазвичай можете.
На додаток до наведених вище коментарів, я хотів би додати кілька зауважень про Views.
- Подання можуть використовуватися для приховування складності. Уявіть собі сценарій, в якому 5 людей працюють над проектом, але тільки один із них дуже гарний з базою даних, наприклад, складними об'єднаннями. У такому сценарії він може створювати види, які можуть бути легко запрошені іншими членами команди, оскільки вони запитують одну таблицю.
- Безпека може бути легко реалізована Views. Припустимо, що ми співробітниктаблиці, що містить чутливі стовпці, такі як Зарплата , номер SSN. Ці стовпці не повинні відображатися для користувачів, яким не дозволено їх переглядати. У цьому випадку ми можемо створити уявлення, яке вибиратиме стовпці в таблиці, які не вимагають авторизації, такі як ім'я , вік тат. д., не піддаючи вразливі стовпці (наприклад, про зарплату і т. д., про які ми згадували раніше). Тепер ми можемо видалити дозвіл для прямого запиту до таблиці Employeeі просто зберегти дозвіл на читання у поданні. Таким чином ми можемо реалізувати безпеку за допомогою Views.