Використовувати збережені процедури. У чому різниця між збереженої процедурою і поданням? Видалення збереженої процедури на T-SQL - інструкція DROP PROCEDURE

оголошення процедури

CREATE PROCEDURE [({IN | OUT | INOUT} [,…])]
[DYNAMIC RESULT SET ]
BEGIN [ATOMIC]

END

Ключові слова
. IN (Input) - вхідний параметр
. OUT (Output) - вихідний параметр
. INOUT - вхідний і вихідний, а також поле (без параметрів)
. DYNAMIC RESULT SET показує, що процедура може відкрити вказане число курсорів, які залишаться відкритими після повернення з процедури

Примітки
Не рекомендується використовувати багато параметрів в збережених процедурах (в першу чергу великих чисел і символьних рядків) з-за перевантаження мережі і стека. На практиці в існуючих діалектах Transact-SQL, PL / SQL і Informix спостерігається істотна відмінність від стандарту, як в оголошенні і використанні параметрів, оголошенні змінних, так і у виклику підпрограм. Microsoft рекомендує застосовувати наступну апроксимацію для оцінки розміру кеша процедур:
\u003d (Максимальна кількість одночасно працюючих користувачів) * (розмір самого великого плану виконання) * 1.25. Визначення розміру плану виконання в сторінках можна зробити за допомогою команди: DBCC MEMUSAGE.

виклик процедури

У багатьох існуючих СУБД виклик збережених процедур виконується за допомогою оператора:

EXECUTE PROCEDURE [(][)]

Примітка: Виклик збережених процедур може бути зроблений з програми, інший процедури, або в інтерактивному режимі.

Приклад оголошення процедури

CREATE PROCEDURE Proc1 AS // оголошуємо процедуру
DECLARE Cur1 CURSOR FOR SELECT SName, City FROM SalesPeople WHERE Rating\u003e 200 // оголошуємо курсор
OPEN Cur1 // відкриваємо курсор
FETCH NEXT FROM Cur1 // зчитуємо дані з курсора
WHILE @@ Fetch_Status \u003d 0
BEGIN
FETCH NEXT FROM Cur1
END
CLOSE Cur1 // закриваємо курсор
DEALLOCATE Cur1
EXECUTE Proc1 // запускаємо процедуру

поліморфізм
Дві підпрограми з одним і тим же ім'ям можуть бути створені в одній і тій же схемі, якщо параметри цих двох підпрограм є в таке міру відмінними один від одного, щоб їх можна було розрізняти. Для того, щоб розрізняти дві підпрограми з одним і тим же ім'ям в одній схемі, кожній з них дається альтернативне і унікальне ім'я (specific name). Таке ім'я може бути явно вказано, коли визначається підпрограма. При виклику підпрограм при наявності декількох однакових імен визначення потрібної підпрограми здійснюється в кілька етапів:
. Спочатку визначаються всі процедури з зазначеним ім'ям, А якщо таких немає, то всі функції з заданим ім'ям.
. Для подальшого аналізу залишаються тільки ті підпрограми, по відношенню до яких даний користувач має привілей на виконання (EXECUTE).
. Для них відбираються ті, у яких число параметрів відповідає числу аргументів виклику. Перевіряються зазначені типи даних у параметрів і їх позиції.
. Якщо залишилося більше однієї підпрограми, то вибирається та, кваліфікаційне ім'я якої коротше.
На практиці в Oracle поліморфізм підтримується для функцій, оголошених тільки в пакеті, [Email protected] - в різних схема, а в Sybase і MS SQL Server перевантаження заборонена.

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

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

ALTER PROCEDURE [([{IN | OUT | INOUT}])]
BEGIN [ATOMIC]

END

Привілеї на виконання процедур

GRANT EXECUTE ON TO |PUBLIC [WITH GRANT OPTION]

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

1. Вмикай в свої процедури рядок - SET NOCOUNT ON: З кожним DML виразом, SQL server дбайливо повертає нам повідомлення містить кількість оброблених записів. Дана інформація може бути нам корисна під час налагодження коду, але після буде абсолютно марною. Прописуючи SET NOCOUNT ON, ми відключаємо цю функцію. Для процедур містять кілька виразів або \\ і цикли дана дія може дати значний приріст продуктивності, тому як кількість трафіку буде значно знижено.

CREATE PROC dbo.ProcName
AS
SET NOCOUNT ON;
--Здесь код процедури
SELECT column1 FROM dbo.TblTable1
--Перключеніе SET NOCOUNT в початковий стан
SET NOCOUNT OFF;
GO

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

SELECT * FROM dbo.MyTable --Вот так робити добре
- Замість
SELECT * FROM MyTable --А так робити погано
--Визов процедури
EXEC dbo.MyProc --Опять ж добре
--Вместо
EXEC MyProc --Плохо!

3. Не використовуй префікс «sp_» в імені своїх збережених процедур: Якщо ім'я нашої процедури починається з «sp_», SQL Server в першу чергу буде шукати в своїй головній базі даних. Справа в тому, що даний префікс використовується для особистих внутрішніх процедур сервера. Тому його використання може призвести до додаткових витрат і навіть невірного результату, якщо процедура з таким же імененем як у вас буде знайдена в його базі.

4. Використовуй IF EXISTS (SELECT 1) замість IF EXISTS (SELECT *): Щоб перевірити наявність запису в іншій таблиці, ми використовуємо вираз IF EXISTS. Цей вираз повертає true якщо з внутрішнього вираження повертається хоч одне ізначеніе, не має значення «1», все колонки або таблиця. Возращаться дані, в принципі ніяк не використовуються. Таким чином для стиснення трафіку під час передачі даних логічніше використовувати «1», як показано нижче:

IF EXISTS (SELECT 1 FROM sysobjects
WHERE name \u003d "MyTable" AND type \u003d "U")

5. Використовуй TRY-Catch для вилову помилок: До 2005 сервера після кожного запиту в процедурі писалося величезна кількість перевірок на помилки. Більше коду завжди споживає більше ресурсів і більше часу. З 2005 SQL Server "ом з'явився більш правильний і зручний спосіб вирішення цієї проблеми:

BEGIN TRY
--код
END TRY
BEGIN CATCH
--код вилову помилки
END CATCH

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

P.S.
Мій перший пост, не судіть строго.

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

Поняття процедури,

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

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

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

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

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

Збережені процедури в середовищі MS SQL Server

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

Типи процедур

У SQL Server є кілька типів збережених процедур.

• Системні збережені процедури призначені для виконання різних адміністративних дій. Практично всі дії з адміністрування сервера виконуються з їх допомогою. Можна сказати, що системні збережені процедури є інтерфейсом, що забезпечує роботу з системними таблицями, яка, в кінцевому рахунку, зводиться до зміни, додаванню, видалення і вибірці даних з системних таблиць як для користувача, так і системних баз даних. Системні збережені процедури мають префікс sp_, зберігаються в системній базі даних і можуть бути викликані в контексті будь-якої іншої бази даних.
• призначені для користувача збережені процедури реалізують ті чи інші дії. збережені процедури - повноцінний об'єкт бази даних. Внаслідок цього кожна збережена процедура розташовується в конкретній базі даних, де і виконується.
• тимчасові збережені процедури існують лише деякий час, після чого автоматично знищуються сервером. Вони діляться на локальні і глобальні. локальні тимчасові збережені процедури можуть бути викликані тільки з того з'єднання, в якому створені. При створенні такої процедури їй необхідно дати ім'я, що починається з одного символу #. Як і всі тимчасові об'єкти, збережені процедури цього типу автоматично видаляються при відключенні користувача, перезапуску або зупинці сервера. Глобальні тимчасові збережені процедури доступні для будь-яких з'єднань сервера, на якому є така ж процедура. Для її визначення достатньо дати їй ім'я, яке починається з символів ##. Видаляються ці процедури при перезапуску або зупинці сервера, а також при закритті з'єднання, в контексті якого вони були створені.

Створення, зміна та видалення збережених процедур

створення збереженої процедури передбачає вирішення наступних завдань:

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

Створення нової та зміна наявної збереженої процедури здійснюється за допомогою наступної команди:

<определение_процедуры>:: \u003d (CREATE | ALTER) PROC імя_процедури [; номер] [(@ імя_параметра тип_даних) [\u003d default]] [, ... n] AS sql_оператор [... n]

Розглянемо параметри даної команди.

Використовуючи префікси sp_, #, ##, створювану процедуру можна визначити як системної або тимчасовою. Як видно з синтаксису команди, не допускається вказувати ім'я власника, якому належатиме створювана процедура, а також ім'я бази даних, де вона повинна бути розміщена. Таким чином, щоб розмістити створювану збережену процедуру в конкретній базі даних, необхідно виконати команду CREATE PROCEDURE в контексті цієї бази даних. При зверненні з тіла збереженої процедури до об'єктів тієї ж бази даних можна використовувати укорочені імена, т. е. без вказівки імені бази даних. Коли ж потрібно звернутися до об'єктів, розташованих в інших базах даних, вказівка \u200b\u200bімені бази даних обов'язково.

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

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

Для визначення типу даних, який буде мати відповідний параметр збереженої процедури, Годяться будь-які типи даних SQL, включаючи певні користувачем. Однак тип даних CURSOR може бути використаний тільки як вихідний параметр збереженої процедури, Тобто із зазначенням ключового слова OUTPUT.

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

Ключове слово VARYING застосовується спільно з параметром OUTPUT, що має тип CURSOR. Воно визначає, що вихідним параметром буде результуюча безліч.

Ключове слово DEFAULT є значення, яке буде приймати відповідний параметр за замовчуванням. Таким чином, при виклику процедури можна не вказувати явно значення відповідного параметра.

Так як сервер кешируєт план виконання запиту і компілює код, при наступному виклику процедури будуть використовуватися вже готові значення. Однак в деяких випадках все ж потрібно виконувати перекомпіляцію коду процедури. Вказівка \u200b\u200bключового слова RECOMPILE наказує системі створювати план виконання збереженої процедури при кожному її виклику.

Параметр FOR REPLICATION затребуваний при реплікації даних і включенні створюваної збереженої процедури в якості статті в публікацію.

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

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

Видалення збереженої процедури здійснюється командою:

DROP PROCEDURE (імя_процедури) [... n]

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

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

[[EXEC [UTE] імя_процедури [; номер] [[@ імя_параметра \u003d] (значення | @ ім'я_змінної) |] [, ... n]

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

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

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

З синтаксису команди EXECUTE видно, що імена параметрів можуть бути опущені при виклику процедури. Однак в цьому випадку користувач повинен вказувати значення для параметрів в тому ж порядку, в якому вони перераховувалися при запровадження процедури. присвоїти параметру значення за замовчуванням, Просто пропустивши його при перерахуванні можна. Якщо ж потрібно опустити параметри, для яких визначено значення за замовчуванням, Досить явної вказівки імен параметрів при виклику збереженої процедури. Більш того, таким способом можна перераховувати параметри і їх значення в довільному порядку.

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

Приклад 12.1. Процедура без параметрів. Розробити процедуру для отримання назв і вартості товарів, придбаних Івановим.

CREATE PROC my_proc1 AS SELECT Товар.Названіе, Товар.Цена * Сделка.Колічество AS Вартість, Кліент.Фамілія FROM Клієнт INNER JOIN (Товар INNER JOIN Угода ON Товар.КодТовара \u003d Сделка.КодТовара) ON Кліент.КодКліента \u003d Сделка.КодКліента WHERE Клієнт .Фамілія \u003d 'Іванов' Приклад 12.1. Процедура для отримання назв і вартості товарів, придбаних Івановим.

для звернення до процедури можна використовувати команди:

EXEC my_proc1 або my_proc1

Процедура повертає набір даних.

Приклад 12.2. Процедура без параметрів. Створити процедуру для зменшення ціни товару першого сорту на 10%.

для звернення до процедури можна використовувати команди:

EXEC my_proc2 або my_proc2

Процедура не повертає ніяких даних.

Приклад 12.3. Процедура з вхідним параметром. Створити процедуру для отримання назв і вартості товарів, які придбав заданий клієнт.

CREATE PROC my_proc3 @k VARCHAR (20) AS SELECT Товар.Названіе, Товар.Цена * Сделка.Колічество AS Вартість, Кліент.Фамілія FROM Клієнт INNER JOIN (Товар INNER JOIN Угода ON Товар.КодТовара \u003d Сделка.КодТовара) ON Кліент.КодКліента \u003d Сделка.КодКліента WHERE Кліент.Фамілія [Email protected] Приклад 12.3. Процедура для отримання назв і вартості товарів, які придбав заданий клієнт.

для звернення до процедури можна використовувати команди:

EXEC my_proc3 "Іванов" або my_proc3 @ k \u003d "Іванов"

Приклад 12.4. . Створити процедуру для зменшення ціни товару заданого типу відповідно до зазначеного%.

для звернення до процедури можна використовувати команди:

EXEC my_proc4 "Вафлі", 0.05 або EXEC my_proc4 @ t \u003d "Вафлі", @ p \u003d 0.05

Приклад 12.5. Процедура з вхідними параметрами і значеннями за замовчуванням. Створити процедуру для зменшення ціни товару заданого типу відповідно до зазначеного%.

CREATE PROC my_proc5 @t VARCHAR (20) \u003d 'Конфети`, @p FLOAT \u003d 0.1 AS UPDATE Товар SET Ціна \u003d Ціна * ( [Email protected]) WHERE Тип [Email protected] Приклад 12.5. Процедура з вхідними параметрами і значеннями за замовчуванням. Створити процедуру для зменшення ціни товару заданого типу відповідно до зазначеного%.

для звернення до процедури можна використовувати команди:

EXEC my_proc5 "Вафлі", 0.05 або EXEC my_proc5 @ t \u003d "Вафлі", @ p \u003d 0.05 або EXEC my_proc5 @ p \u003d 0.05

У цьому випадку зменшується ціна цукерок (значення типу не вказано при виклику процедури і береться за замовчуванням).

В останньому випадку обидва параметри (і тип, і відсотки) не вказані при виклику процедури, їх значення беруться за замовчуванням.

Приклад 12.6. Процедура з вхідними та вихідними параметрами. Створити процедуру для визначення загальної вартості товарів, проданих за конкретний місяць.

CREATE PROC my_proc6 @m INT, @s FLOAT OUTPUT AS SELECT @ s \u003d Sum (Товар.Цена * Сделка.Колічество) FROM Товар INNER JOIN Угода ON Товар.КодТовара \u003d Сделка.КодТовара GROUP BY Month (Сделка.Дата) HAVING Month ( Сделка.Дата) [Email protected] Приклад 12.6. Процедура з вхідними та вихідними параметрами. Створити процедуру для визначення загальної вартості товарів, проданих за конкретний місяць.

для звернення до процедури можна використовувати команди:

DECLARE @st FLOAT EXEC my_proc6 1, @ st OUTPUT SELECT @st

Цей блок команд дозволяє визначити вартість товарів, проданих в січні ( вхідний параметр місяць вказано рівним 1).

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

Спочатку розробимо процедуру для визначення фірми, де працює співробітник.

Приклад 12.7. Використання вкладених процедур. Створити процедуру для визначення загальної кількості товарів, придбаних фірмою, в якій працює заданий співробітник.

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

CREATE PROC my_proc8 @fam VARCHAR (20), @kol INT OUTPUT AS DECLARE @firm VARCHAR (20) EXEC my_proc7 @ fam, @ firm OUTPUT SELECT @ kol \u003d Sum (Сделка.Колічество) FROM Клієнт INNER JOIN Угода ON Кліент.КодКліента \u003d Сделка.КодКліента GROUP BY Кліент.Фірма HAVING Кліент.Фірма [Email protected] Приклад 12.7. Створення процедури для визначення загальної кількості товарів, придбаних фірмою, в якій працює заданий співробітник.

Виклик процедури здійснюється за допомогою команди:

DECLARE @k INT EXEC my_proc8 'Іванов', @ k OUTPUT SELECT @k

Коли слід використовувати збережені процедури і коли я повинен використовувати уявлення в 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 \u003d b.user_id GO

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

SELECT profile_description FROM vw_user_profile WHERE user_id \u003d @ID

Цей код можна використовувати в збереженій процедурі, наприклад:

Create procedure dbo.getDesc @ID int AS begin SELECT profile_description FROM vw_user_profile WHERE user_id \u003d @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 \u003d b.user_id

Я можу оновити дані ... як приклад я можу зробити будь-який з цих ...

Update vw_user_profile Set profile_description \u003d "Manager" where user_id \u003d 4

Update tbl_profile Set profile_description \u003d "Manager" where user_id \u003d 4

Ви не можете вставити в це уявлення, так як не всі поля у всій таблиці присутні, і я припускаю, що PROFILE_ID є первинним ключем і не може бути NULL. Однак іноді ви можете вставити INSERT в уявлення ...

Я створив уявлення для існуючої таблиці, Використовуючи ...

Create View Junk as SELECT * from

Insert into junk (Code, name) values \u200b\u200b( "glyn", "Glyn Roberts"), ( "Mary", "Maryann Roberts")

DELETE from Junk Where ID\u003e 4

І INSERT, і DELETE працювали в цьому випадку

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

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

На додаток до наведених вище коментарів я хотів би додати кілька зауважень про Views.

1. Уявлення можуть використовуватися для приховування складності. Уявіть собі сценарій, в якому 5 чоловік працюють над проектом, але тільки один з них дуже хороший з базою даних, наприклад складними об'єднаннями. У такому сценарії він може створювати види, які можуть бути легко запитані іншими членами команди, оскільки вони запитують якусь одну таблицю.
2. Безпека може бути легко реалізована Views. Припустимо, що ми співробітник таблиці, який містить чутливі стовпці, такі як Зарплата , номер SSN . Ці стовпці не повинні відображатися для користувачів, яким не дозволено їх переглядати. У цьому випадку ми можемо створити уявлення, яке буде вибирати стовпці в таблиці, які не потребують авторизації, такі як ім'я , вік і т. Д., Чи не піддаючи вразливі стовпці (наприклад, про зарплату і т. Д., Про які ми згадували раніше). Тепер ми можемо видалити дозвіл для прямого запиту до таблиці Employee і просто зберегти дозвіл на читання в поданні. Таким чином, ми можемо реалізувати безпеку за допомогою Views.

Збережені процедури 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 \u003d значення». Ви можете використовувати ці пари, розділяючи їх комою.

У самих різних прикладах люди показують створення простої процедури, і виконання її. Однак процедура може приймати такі параметри, що викликає її процес буде мати значення, близькі до нього (але не завжди). Якщо вони збігаються, то всередині тіла починаються відповідні процеси. Наприклад, якщо створити процедуру, яка буде приймати місто і регіон від абонента і повертати дані про те, скільки авторів відносяться до відповідних місту та регіону. Процедура буде запитувати таблиці авторів бази даних, наприклад, 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, проте їх вивчення необхідно тим, хто займається створенням значних баз даних. Також існує велика, навіть величезна кількість нюансів, які можуть бути корисні при вивченні збережених процедур, однак це потрібно більше для тих, хто планує щільно зайнятися програмуванням, в тому числі професійно.