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

3. Хранимые процедуры

база файл триггер информация

Это тоже элемент базы данных, наряду с таблицами. Это специальная программа, написанная на так называемом языке процедур и триггеров и хранимая в качестве части базы данных. Поскольку мы создали хранимую процедуру, мы можем напрямую вызвать на выполнение из своего приложения. Хранимая процедура может получать входные параметры и возвращать определенные значения приложению. Язык процедур и триггеров включает в себя операторы SQL и некоторые расширения, такие как IF THEN ELSE, MHILE DO, FOR SELECT DO, исключения и обработку ошибок.

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

·  Модульное оформление;

·  Приложения, обращающиеся к одной и той же базе данных, могут делит между собой хранимые процедуры (использовать одну и ту же хранимую процедуру), избегая дублирования кода (т.е. нет необходимости в повторном коде хранимой процедуры) и снижая таким образом размер самих приложений;

·  Рациональная поддержка. Когда процедура обновляется, ее изменения автоматически отражаются во всех приложениях, которые ее используют, и новая компиляция этих приложений не требуется. Приложения только однажды компилируются для каждого клиента.

·  Улучшение работы. И это – основное достоинство этого инструмента. Хранимые процедуры выполняются сервером, а не клиентом, что снижает загрузку сети и улучшает её работу, особенно в условиях клиентского доступа. Например, в системе управления кадрами, когда общая база данных хранится на сервере и работники отдела кадров и руководство предприятия пользуются ее услугами, у некоторого клиента возникает потребность получить аналитические данные в некотором разрезе. При наличии в БД хранимой процедуры, получающей требуемую аналитику, нет нужды клиенту вытаскивать к себе все необходимые данные для расчета, а достаточно вызвать на сервере соответствующую хранимую процедуру, передав ей необходимые параметры, и эта процедура, на месте исполнив всю работу и не перегоняя большое количества данных по сети клиенту, выдаст ему только результат в виде небольшой таблицы. Этой же процедурой может воспользоваться и другой клиент и получить для себя такую же аналитику.

·  Работа с хранимыми процедурами

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

·  Интерактивно;

·  С помощью входного файла, содержащего операторы определения данных.

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

Хранимые процедуры в базах данных, преимущества использования, CREATE PROC, мастер Create Stored Procedure Wizard, макросы и модули VBA в Microsoft Access

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

модульность разработки приложений;

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

позволяют реализовывать дополнительную логику проверок, протоколирования действий пользователей и т.п.;

позволяют производить каскадные обновления;

в отличие от представлений, хранимые процедуры могут принимать и возвращать параметры;

обеспечивают очень мощные механизмы обеспечения безопасности;

позволяют повысить производительность - за счет использования откомпилированных планов хранимых процедур из кэша и за счет сокращения сетевого трафика при отправке больших наборов команд;

при помощи хранимых процедур можно реализовывать изощренные схемы обработки ошибок на сервере.

Создание хранимых процедур производится на SQL Server командой CREATE PROC или - на графическом интерфейсе - в Enterprise Manager из контейнера Stored Procedures или при помощи мастера Create Stored Procedure Wizard.

В MS Access как таковых хранимых процедур нет, но их роль могут выполнять:

макросы (типа "Запуск запроса SQL")

модули VBA.

·  Использование файла определения данных

Для создания и изменения процедуры с использованием файла определения данных используют текстовой редактор, что бы записать такой файл, затем сохраняют файл и исполняют с помощью интерактивного SQL утилиты IBConsole, в которой вставляют данные из файла, либо используют команду isgl в командной строке (режим DOS), задавая команду в виде:

C:\ISGL-INPUT FILENAME DATABASE_NAME.

Здесь FILENAME – это имя файла определения данных, а DATABASE_NAME – имя используемой БД. Файл определения данных должен включать:

·  Операторы создания, модификации и удаления процедур и исключений;

·  Любые другие ISGL – операторы. Исключения должны созданы до ссылки на них в процедурах.

Хранимая процедура меняет код профессии в таблице DAN соответствующий переменной MEN на код соответствующий переменной NOW.

SET TERM ^;

CREATE PROCEDURE IZM_PROF(MEN INTEGER,NOW INTEGER)

RETURNS(TAB_NO CHAR(5),FAM VARCHAR(30),K_P INTEGER,NOW_P INTEGER)AS

BEGIN

FOR

SELECT TAB_NO,FAM,K_P

FROM DAN

INTO:TAB_NO,FAM,K_P

DO

BEGIN

IF(K_P=MEN)THEN

BEGIN

NOW_P=NOW;

UPDATE DAN

SET K_P=:NOW_P

WHERE TAB_NO=:TAB_NO;

SUSPEND;

END

END

END^

SET TERM;^

Вызов процедуры

SELECT*

FROM IZM_PROF(12,5);

Удаление процедуры:

DROP PROCEDURE <имя процедуры>;

·  Вызов хранимых процедур.

Приложения могут вызывать хранимые процедуры из SGL, DSGL, ISGL.

Существуют два типа хранимых процедур: процедуры-выборки (selectable procedures) и исполняемые процедуры (executable procedures). Отличие в работе этих двух видов ХП заключается в том, что процедуры-выборки обычно возвращают множество наборов выходных параметров, сгруппированных построчно, которые имеют вид набора данных, а исполняемые процедуры могут либо вообще не возвращать параметры, либо возвращать только один набор выходных параметров, перечисленных в Returns. Процедуры-выборки вызываются в запросах SELECT, а исполняемые процедуры - с помощью команды EXECUTE PROCEDURE.

·  SELECT - процедуры, которые приложения может использовать вместо таблицы или вьюера в операторе SELECT; результатом исполнения оператора SELECT над такой процедурой будет обычная таблица данных, какая получается, например, когда в SELECT стоит символ*. Например, если имеется SELECT - процедура с именем SP, то ее вызов на исполнение выглядеть так:

SELECT*

FROM IZM_PROF(12,5);

Если воспользоваться запросом

EXECUTE PROCEDURE UV_D (1.1);

то произойдет изменение и вывод только первой строки:

KOD_D SUMA_D N_SUMA_D

======== ======== ========

300 2500 2750

Вызов процедуры UV_MZ_D

SELECT*

FROM UV_MZ_D (2600);

произведет изменения только двух строк таблицы DOLJN, в которых поле SUMA_D меньше 2600, и выведет таблицу из этих строк.

KOD_D SUMA_D N_SUMA_D

======== ======== ========

300 2500 2600

307 2000 2600

//Поиск повторений //MS Access

SELECT данные.фамилия, данные.имя, данные.отчество

FROM данные

WHERE (((данные.фамилия) In (SELECT [фамилия] FROM [данные] As Tmp GROUP BY [фамилия] HAVING Count(*)>1 )))

ORDER BY данные.фамилия;

Поиск повторений для данные
фамилия	имя	отчество
ИВАНОВ	Иван	Иванович
ИВАНОВ	Иван	Иванович
ФРОЛОВА	Анастасия	Дмитриевна
ФРОЛОВА	Мария	Александровна

·  Удаление хранимой процедуры.

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

DROP PROCEDURE name;

Здесь name – имя хранимой процедуры.

Хранимые процедуры — основное средство программирования серверной логики. Они представляют собой откомпилированный модуль, написанный на языке Transact-SQL. В коде хранимой процедуры можно использовать не только операции выборки и модификации данных, но и логику ветвления, переменные, вызовы других процедур и некоторые другие средства, характерные для языков программирования высокого уровня. Код процедуры синтаксически анализируется при компиляции, а оптимизированный план выполнения создается при первом вызове процедуры. В отличие от запросов, хранимые процедуры имеют возможность возвращать несколько наборов записей, а также значения.

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

Хранимые процедуры используются в проектах Access везде, где могут использоваться предложения SQL. Они могут служить: в качестве источников записей в формах, отчетах, активных страницах; в качестве источников строк для полей со списком.

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

В первом случае чтобы создать процедуру проще всего щелкнуть по ярлыку Создание сохраненной процедуры в режиме конструктора (Create stored procedure in designer). При этом открывается стандартное окно Конструктора запросов, в котором можно создать инструкцию SQL, составляющие тело процедуры. Так же как и представление, хранимая процедура в данном случае может иметь ряд дополнительных свойств. Чтобы отобразить или изменить эти свойства, необходимо открыть окно свойств. На вкладке Сохраненная процедура (Stored Procedure) содержится перечень свойств, которые аналогичны свойствам представления. А на второй вкладке можно посмотреть и изменить параметры процедуры.

Откройте процедуру в режиме Конструктора. Нажмите кнопку Свойства (Properties) на панели инструментов, чтобы отобразить диалоговое окно Свойства (Properties). Раскройте вкладку Параметры сохраненной процедуры.

На вкладке отображается таблица со списком параметров процедуры. Каждый параметр обязательно должен иметь имя и тип данных. Кроме того, для него может быть указано значение по умолчанию. Обратим внимание, что имя каждого параметра начинается со значка @. Но имя параметра не может быть изменено на этой вкладке, так же как нельзя добавить новый параметр. Это делается только в бланке запроса в окне Конструктора или прямо в инструкции SQL.

Для того чтобы создать хранимую процедуру, состоящую из нескольких инструкций SQL, нужно открыть список запросов в окне проекта и нажать кнопку Создать (New). В диалоговом окне Новый запрос (New Query) следует выбрать элемент Ввод сохраненной процедуры (Create Text Stored Procedure) .

Нужно изменить имя процедуры в первой строке шаблона и ввести текст процедуры в соответствии с правилами языка Transact-SQL.

Если в теле процедуры нужно ввести инструкцию SQL SELECT, то можно воспользоваться для этого Конструктором запросов. Установите курсор на строке редактора, в которой должна быть введена инструкция SELECT и нажмите кнопку Вставить SQL (Insert SQL) на панели инструментов . Появится окно Конструктора запросов. После того как запрос будет создан, нужно закрыть это окно, подтвердив сохранение изменений инструкции SQL.

Пример использования хранимой процедуры в качестве источника записей вы увидите, если откроете отчет "2". В качестве источника записей в этом отчете используется одноименная хранимая процедура. В свойстве Входные параметры (Input Parameters) этого отчета описаны два параметра: @Beginning_date И @Ending_date.

При запуске отчета эти параметры будут запрашиваться так же, как и при использовании запроса с параметрами в базе данных Access.

SELECT данные.фамилия, данные.имя, данные.отчество, данные.образование

FROM данные LEFT JOIN оклады ON данные.[код оклада] = оклады.[код оклада]

WHERE (((оклады.[код оклада]) Is Null));

'данные' без подчиненных в 'оклады'
фамилия	имя	отчество	образование
СИДОРЧУК	Валентина	Викторовна