17240

Понятие хранимой процедуры

Лекция

Информатика, кибернетика и программирование

Лекция №5 Понятие хранимой процедуры Хранимые процедуры это группа связанных между собой операторов SQL или функций хранимых в откомпилированном виде. Использование хранимых процедур вместо отдельных операторов SQL дает пользователю следующие преимущества: хра

Русский

2013-06-30

55.5 KB

3 чел.

Лекция №5

Понятие хранимой процедуры

Хранимые процедуры это группа связанных между собой операторов SQL или функций, хранимых в откомпилированном виде. Использование хранимых процедур вместо отдельных операторов SQL дает пользователю следующие преимущества:

  •  хранение в исполняемом формате (перед выполнением хранимой процедуры SQL Server генерирует для нее план исполнения, выполняет ее оптимизацию и компиляцию);
  •  поддержка модульного программирования (позволяют разбивать большие задачи на самостоятельные, более мелкие и удобные в управлении части);
  •  могут вызывать другие хранимые процедуры и функции;
  •  могут быть вызваны из прикладных программ других типов;
  •  выполняются быстрее, чем последовательность отдельных операторов;
  •  уменьшает размер запроса.

Устно. Хранение процедур в том же месте, где они исполняются, обеспечивает уменьшение объема передаваемых по сети данных и повышает общую производительность системы. Применение хранимых процедур упрощает сопровождение программных комплексов и внесение изменений в них. Обычно все ограничения целостности в виде правил и алгоритмов обработки данных реализуются на сервере баз данных и доступны конечному приложению в виде набора хранимых процедур, которые и представляют интерфейс обработки данных. Для обеспечения целостности данных, а также в целях безопасности, приложение обычно не получает прямого доступа к данным – вся работа с ними ведется путем вызова тех или иных хранимых процедур.

Хранимые процедуры существуют независимо от таблиц или каких-либо других объектов баз данных. Они вызываются клиентской программой, другой хранимой процедурой или триггером.

Хранимые процедуры в среде MS SQL Server

Типы хранимых процедур

Существуют несколько типов хранимых процедур.

  •  Системные хранимые процедуры предназначены для выполнения различных административных действий.

Устно. Можно сказать, что системные хранимые процедуры являются интерфейсом, обеспечивающим работу с системными таблицами, которая, в конечном счете, сводится к изменению, добавлению, удалению и выборке данных из системных таблиц как пользовательских, так и системных баз данных. Системные хранимые процедуры имеют префикс sp_, хранятся в системной базе данных и могут быть вызваны в контексте любой другой базы данных.

  •  Пользовательские хранимые процедуры реализуют действия пользователя. Каждая хранимая процедура располагается в конкретной БД, где и выполняется.
  •  Временные хранимые процедуры существуют определенное время в зависимости от типа процедуры. Локальные временные хранимые процедуры могут быть вызваны только из того соединения, в котором созданы. При создании такой процедуры ей необходимо дать имя, начинающееся с одного символа #. Хранимые процедуры этого типа автоматически удаляются при отключении пользователя, перезапуске или остановке сервера. Глобальные временные хранимые процедуры доступны для любых соединений сервера, на котором имеется такая же процедура. Для ее определения необходимо, чтобы имя начиналось с символов ##. Удаляются эти процедуры при перезапуске или остановке сервера, а также при закрытии соединения, в контексте которого они были созданы.

Создание, изменение и удаление хранимых процедур

Создание новой и изменение имеющейся хранимой процедуры осуществляется с помощью следующей команды:

CREATE | ALTER } PROC[EDURE] имя_процедуры

[@имя_параметра тип_данных  [VARYING ] ]

[=default] [OUTPUT] ]

[WITH { RECOMPILE | ENCRYPTION | RECOMPILE ENCRYPTION }]

[FOR REPLICATION]

AS

  sql_оператор

Рассмотрим параметры данной команды.

Используя префиксы sp_, #, ##, создаваемую процедуру можно определить в качестве системной или временной. При обращении из тела хранимой процедуры к объектам той же базы данных можно использовать укороченные имена, т. е. без указания имени базы данных. Когда же требуется обратиться к объектам, расположенным в других базах данных, указание имени базы данных обязательно.

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

Для определения типа данных, который будет иметь соответствующий параметр хранимой процедуры, годятся любые типы данных SQL.

Наличие ключевого слова OUTPUT означает, что соответствующий параметр предназначен для возвращения данных из хранимой процедуры. Указание ключевого слова OUTPUT предписывает серверу при выходе из хранимой процедуры присвоить текущее значение параметра локальной переменной, которая была указана при вызове процедуры в качестве значения параметра. Отметим, что при указании ключевого слова OUTPUT значение соответствующего параметра при вызове процедуры может быть задано только с помощью локальной переменной.

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

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

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

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

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

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

Удаление хранимой процедуры осуществляется командой:

DROP PROCEDURE {имя_процедуры}

Выполнение хранимой процедуры

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

[[ EXEC [ UTE] имя_процедуры

[[@имя_параметра=]{значение | @имя_переменной} [OUTPUT ]|[DEFAULT ] ]

Если вызов хранимой процедуры не является единственной командой в пакете, то присутствие команды EXECUTE обязательно. Более того, эта команда требуется для вызова процедуры из тела другой процедуры или триггера.

Использование ключевого слова OUTPUT при вызове процедуры разрешается только для параметров, которые были объявлены при создании процедуры с ключевым словом OUTPUT.

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

Устно. Из синтаксиса команды EXECUTE видно, что имена параметров могут быть опущены при вызове процедуры. Однако в этом случае пользователь должен указывать значения для параметров в том же порядке, в каком они перечислялись при создании процедуры. Присвоить параметру значение по умолчанию, просто пропустив его при перечислении нельзя. Если же требуется опустить параметры, для которых определено значение по умолчанию, достаточно явного указания имен параметров при вызове хранимой процедуры. Более того, таким способом можно перечислять параметры и их значения в произвольном порядке.

Отметим, что при вызове процедуры указываются либо имена параметров со значениями, либо только значения без имени параметра. Их комбинирование не допускается.

Пример 1. Процедура без параметров. Разработать процедуру для получения названий и стоимости товаров, приобретенных Ивановым.

CREATE PROC my_proc1

AS

SELECT Товар.Название,

      Товар.Цена*Сделка.Количество

  AS Стоимость, Клиент.Фамилия

 FROM Клиент INNER JOIN

 (Товар INNER JOIN Сделка

 ON Товар.КодТовара=Сделка.КодТовара)

 ON Клиент.КодКлиента=Сделка.КодКлиента

 WHERE Клиент.Фамилия=’Иванов’

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

EXEC my_proc1 или my_proc1

Процедура возвращает набор данных.

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

CREATE PROC my_proc2

AS

UPDATE Товар SET Цена=Цена*0.9

 WHERE Сорт=’первый’

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

EXEC my_proc2 или my_proc2

Процедура не возвращает никаких данных.

Пример 3. Процедура с входным параметром. Создать процедуру для получения названий и стоимости товаров, которые приобрел заданный клиент.

CREATE PROC my_proc3

 @k VARCHAR(20)

AS

SELECT Товар.Название,

      Товар.Цена*Сделка.Количество

 AS Стоимость, Клиент.Фамилия

 FROM Клиент INNER JOIN

 (Товар INNER JOIN Сделка

 ON Товар.КодТовара=Сделка.КодТовара)

 ON Клиент.КодКлиента=Сделка.КодКлиента

 WHERE Клиент.Фамилия=@k

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

EXEC my_proc3 'Иванов' или

my_proc3 @k='Иванов'

Пример 4. Процедура с входными параметрами. Создать процедуру для уменьшения цены товара заданного типа в соответствии с указанным %.

CREATE PROC my_proc4

 @t VARCHAR(20), @p FLOAT

AS

UPDATE Товар SET Цена=Цена*(1-@p)

 WHERE Тип=@t

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

EXEC my_proc4 'Вафли',0.05 или

EXEC my_proc4 @t='Вафли', @p=0.05

Пример 5. Процедура с входными параметрами и значениями по умолчанию. Создать процедуру для уменьшения цены товара заданного типа в соответствии с указанным %.

CREATE PROC my_proc5

 @t VARCHAR(20)=’Конфеты',

 @p FLOAT=0.1

AS

UPDATE Товар SET Цена=Цена*(1-@p)

 WHERE Тип=@t

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

EXEC my_proc5 'Вафли',0.05 или

EXEC my_proc5 @t='Вафли', @p=0.05 или

EXEC my_proc5 @p=0.05

В этом случае уменьшается цена конфет (значение типа не указано при вызове процедуры и берется по умолчанию).

EXEC my_proc5

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

Пример 6. Процедура с входными и выходными параметрами. Создать процедуру для определения общей стоимости товаров, проданных за конкретный месяц.

CREATE PROC my_proc6

 @m INT,

 @s FLOAT OUTPUT

AS

SELECT @s=Sum(Товар.Цена*Сделка.Количество)

 FROM Товар INNER JOIN Сделка

 ON Товар.КодТовара=Сделка.КодТовара

 GROUP BY Month(Сделка.Дата)

 HAVING Month(Сделка.Дата)=@m

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

DECLARE @st FLOAT

EXEC my_proc6 1,@st OUTPUT

SELECT @st

Этот блок команд позволяет определить стоимость товаров, проданных в январе (входной параметр месяц указан равным 1).

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

Сначала разработаем процедуру для определения фирмы, где работает сотрудник.

CREATE PROC my_proc7

 @n VARCHAR(20),

 @f VARCHAR(20) OUTPUT

AS

SELECT @f=Фирма

 FROM Клиент

 WHERE Фамилия=@n

Пример 7. Использование вложенных процедур. Создать процедуру для определения общего количества товаров, приобретенных фирмой, в которой работает заданный сотрудник.

Затем создадим процедуру, подсчитывающую общее количество товара, который закуплен интересующей нас фирмой.

CREATE PROC my_proc8

 @fam VARCHAR(20),

 @kol INT OUTPUT

AS

DECLARE @firm VARCHAR(20)

EXEC my_proc7 @fam,@firm OUTPUT

SELECT @kol=Sum(Сделка.Количество)

 FROM Клиент INNER JOIN Сделка

 ON Клиент.КодКлиента=Сделка.КодКлиента

 GROUP BY Клиент.Фирма

 HAVING Клиент.Фирма=@firm

Вызов процедуры осуществляется с помощью команды:

DECLARE @k INT

EXEC my_proc8 ‘Иванов’,@k OUTPUT

SELECT @k


 

А также другие работы, которые могут Вас заинтересовать

84564. Загальна характеристика системи дихання. Основні етапи дихання. Біомеханіка вдиху і видиху 49.56 KB
  Основні етапи дихання. Дихання процес обміну газів О2 та СО2 між атмосферним повітрям та тканинами організму. СИСТЕМА ДИХАННЯ ВИКОНАВЧІ ОРГАНИ МЕХАНІЗМИ РЕГУЛЯЦІЇ Грудна клітина Нервові Гуморальні Дихальні мязи Плевра Забезпечення оптимального газообміну між атмосферним повітрям та тканинами організму.
84565. Еластична тяга легень, негативний внутрішньоплевраль-ний тиск 43.41 KB
  Еластична тяга легень є сумою трьох сил: 1 сила поверхневого натягу шару рідини води яка вистеляє альвеоли зсередини. Це основна сила яка примушує альвеоли зменшувати свій розмір а легені спадатися; вона складає 2 3 від всієї еластичної тяги легень. Сурфактант вистелає альвеоли зсередини на кордоні з повітряним середовищем. Питома активність сурфактанту тобто його властивість зменшувати силу поверхневого натягу залежить від товщини його шару на поверхні альвеоли чим більша його товщина тим більша питома активність.
84566. Зовнішнє дихання. Показники зовнішнього дихання та їх оцінка 46.93 KB
  Показники зовнішнього дихання та їх оцінка. ПОКАЗНИКИ ЗОВНІШНЬОГО ДИХАННЯ СТАТИЧНІ ДИНАМІЧНІ ОБЄМИ ЧДР ХОД АВЛ КВЛ МВЛ КРД РД ЄМНОСТІ ДО РОвд РОвид ЗО ЖЄЛ Євд ФЗЄ ЗЄЛ Характеризують реалізацію резервів зовнішнього дихання в умовах спокійного та форсованого дихання Характеризують резерви можливості звнішнього дихання Основними методами дослідження показників зовнішнього дихання є спірометрія та спірографія. Спірографія метод графічної реєстрації дихальних рухів в умовах спокійного та форсованого дихання.
84568. Дифузія газів у легенях. Дифузійна здатність легень і фактори, від яких вона залежить 56 KB
  Обмін газів О2 та СО2 між альвеолярним повітрям та кровю проходить тільки пасивно за механізмом дифузії. Дифузія газів в легенях підкоряється закону Фіка: обєм дифузії газу V прямо пропорційний площі дифузії S коефіцієнту дифузії К градієнту тиску газу по обидві сторони альвеолокапілярної мембрани Р1 Р2 і обернено пропорційний товщині цієї мембрани L: Площа дифузії в легенях S це площа альвеол які вентилюються та кровопостачаються. Збільшення площі дифузії може зумовити збільшення глибини дихання і обємної швидкості...
84569. Транспорт кисню кров’ю. Киснева ємкість крові 36.49 KB
  Киснева ємкість крові. Розчинений у плазмі крові. в 1л крові розчиняється 3 мл кисню. Виходячи з цього розраховують кисневу ємкість крові максимальну кількість О2 котру може звязати 1л крові.
84570. Крива дисоціації оксигемоглобіну, фактори, що впливають на її хід 49.75 KB
  Це означає що зниження тиску кисню в альвеолах до 60 мм.ст мало вплине на транспорт кисню кровю хоча напруження кисню в плазмі буде знижуватися пропорційно зниженню тиску О2 в альвеолах. супроводжується значним зниженням HbO2 в крові він активно дисоціює з утворенням гемоглобіну та вільного кисню. І що активніше функціонує тканина тим нижчий в ній рівень О2 посилена дисоціація HbO2 з вивільненням молекулярного кисню котрий утилізується тканинами.
84571. Транспорт вуглекислого газу кров’ю. Роль еритроцитів в транспорті вуглекислого газу 43.36 KB
  Вуглекислий газ транспортується наступними шляхами: Розчинений у плазмі крові близько 25 мл л. У вигляді солей вугільної кислоти букарбонати каліі та натрію плазми крові 510 мл л. Але бікарбонатні йони утворюються в значній концентрації і тому за градієнтом концентрації в обмін на йони хлору надходять у плазму крові. Дифузія газів в тканинах підкоряється загальним законам обєм дифузії прямопропорційний площі дифузії градієнту напруження газів в крові та тканинах.
84572. Фізіологічна роль дихальних шляхів, регуляція їх просвіту 42.27 KB
  В дихальних шляхах повітря: зігрівається; зволожується тому повітря в легенях насичене водяними парами на 100 незалежно від вологості атмосферного повітря; очищується завдяки наявності війчастого епітелію та бокалоподібних клітин які секретують слиз рух війок забезпечує проходження слизу і осівших на поверхні дихальних шляхів чужорідних частинок мікроорганізмів в напрямку гортані та глотки де вони проковтуються або відхаркуються частина осівших на поверхні дихальних шляхів мікроорганізмів і частинок знешкоджуються макрофагами....