Интерактивен учебник по информатика

Защо съхранени процедури

Езикът SQL, за разлика от C#, по принцип не се компилира, а се интерпретира: при всяко изпълнение сървърът прави синтактичен анализ на оператора и чак тогава го изпълнява — и това се повтаря всеки път. Механизмът на съхранените процедури спестява тази загуба: синтактичната правилност се проверява еднократно, резултатът се съхранява на сървъра и при следващо извикване заявката се изпълнява направо.

Ключово

Нещо повече — елементи на заявката може да се оформят като формални параметри (спомнете си параметризираните заявки в Access), а конкретните стойности — фактически параметри — се задават при извикването.

Получаване на достъп

Не всяко конфигуриране на сървъра позволява създаване на процедури: десен бутон върху базата → Properties → страницата Permissions → отметка в реда Create procedure, стълб Grant (за изменения — и в реда Alter).

Темплейтът на процедурата

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE PROCEDURE [dbo].sp<име на процедурата>
  @<параметър1> <тип> = <стойност по премълчаване>,
  @<параметърN> <тип> = <стойност по премълчаване>
AS
BEGIN
  SET NOCOUNT ON;
  <тяло на процедурата>
END
GO

Редовете с -- са коментари — не влизат в кода на процедурата.
SET ANSI_NULLS ON — сравняване с Null само чрез IS NULL / IS NOT NULL (при OFF са допустими и = Null, != Null).
SET QUOTED_IDENTIFIER ON— текстовите константи са в апострофи; кавичките трябват само за имена като O'Neal (тогава — OFF).
Имената на параметрите започват със знака @ — затова имена на атрибути не бива да почват с него.
Тялото е между BEGIN и END — както тялото на C# функция между { и }.
SET NOCOUNT ON — скрива стандартното съобщение за брой редове след SELECT.
GO изпраща операторите дотук за изпълнение — не е команда на T-SQL, а на инструментите (SSMS, SQLCMD, OSQL).

Конвенцията sp и капанът sp_

Прието е имената на собствените процедури да започват с sp (stored procedure). Но вградените процедури на SQL Server започват със sp_ — не започвайте вашите с тази комбинация!

Създаване и изпълнение

Пример 1 — spStudentList без параметри

CREATE PROCEDURE [dbo].spStudentList AS
BEGIN
  SET NOCOUNT ON;
  SELECT name, surname, family FROM Student;
END

Натискаме Execute — SSMS не извежда резултат от заявката, а съобщението „Commands completed successfully“: процедурата е компилирана и съхранена (виж я в Programability/Stored Procedures). Изпълняваме я с:

EXEC [dbo].spStudentList

Промяна на процедура

Отваряме кода, правим измененията и заменяме CREATE с ALTER — Execute записва новата процедура под старото име.

Пример 2 — spListBorn с параметри

Списък на учениците, родени в зададен интервал: два параметъра @from и @to от тип nchar(6), защото проверяваме по първите 6 цифри на ЕГН (функцията LEFT намира префикс със зададена дължина):

CREATE PROCEDURE [dbo].spListBorn
  @from nchar(6), @to nchar(6)
AS
BEGIN
  SET NOCOUNT ON;
  SELECT name, surname, family, LEFT(egn,6) FROM Student
  WHERE LEFT(egn,6) >= @from AND LEFT(egn,6) <= @to;
END

EXEC dbo.spListBorn @from = '021201', @to = '030331'

Практикум

1) Създайте и изпълнете процедурите от урока с различни стойности на параметрите. 2) Модифицирайте spListBorn (с ALTER) да сортира в намаляващ ред на датите на раждане. 3) Оформете като процедури параметризираните заявки от предишния модул.

Какво трябва да запомниш

SQL се интерпретира; съхранената процедура се проверява еднократно и после само се изпълнява.
Параметри: формални (в дефиницията, с @) и фактически (при EXEC).
Имена: sp… да, sp_… никога (запазено за вградените).
SET NOCOUNT ON скрива съобщението за брой редове.
CREATE → ALTER за промяна; EXEC за изпълнение.