Создание и применение хранимых процедур
Хранимые процедуры — основное средство программирования серверной логики. Они представляют собой откомпилированный модуль, написанный на языке Transact-SQL. В коде хранимой процедуры можно использовать не только операции выборки и модификации данных, но и логику ветвления, переменные, вызовы других процедур и некоторые другие средства, характерные для языков программирования высокого уровня. Код процедуры синтаксически анализируется при компиляции, а оптимизированный план выполнения создается при первом вызове процедуры. В отличие от запросов, хранимые процедуры имеют возможность возвращать несколько наборов записей, а также значения.
Для вызова процедуры клиентская программа или другая процедура должны указать имя выполняемой процедуры и передать ей набор входных параметров.
Хранимые процедуры используются в проектах Access везде, где могут использоваться предложения SQL. Они могут служить:
- в качестве источников записей в формах, отчетах, активных страницах;
- в качестве источников строк для полей со списком.
Однако нужно помнить, что хранимые процедуры возвращают необновляемый набор записей.
Хранимая процедура может содержать одну инструкцию SQL, и в этом случае для ее создания можно использовать графический режим Конструктора запросов. Если же она состоит из нескольких инструкций SQL, тогда она создается и изменяется в редакторе SQL.
В первом случае чтобы создать процедуру проще всего щелкнуть по ярлыку Создание сохраненной процедуры в режиме конструктора (Create stored procedure in designer). При этом открывается стандартное окно Конструктора запросов, в котором можно создать инструкцию SQL, составляющие тело процедуры. Так же как и представление, хранимая процедура в данном случае может иметь ряд дополнительных свойств. Чтобы отобразить или изменить эти свойства, необходимо открыть окно свойств. На вкладке Сохраненная процедура (Stored Procedure) содержится перечень свойств, которые аналогичны свойствам представления. А на второй вкладке можно посмотреть и изменить параметры процедуры.
Для примера воспользуемся хранимой процедурой Exployee Sales by Country.
- Откройте эту процедуру в режиме Конструктора.
- Нажмите кнопку Свойства (Properties) на панели инструментов, чтобы отобразить диалоговое окно Свойства (Properties).
- Раскройте вкладку Параметры сохраненной процедуры (Stored Procedure Parameters).
На вкладке отображается таблица со списком параметров процедуры. Каждый параметр обязательно должен иметь имя и тип данных. Кроме того, для него может быть указано значение по умолчанию. Обратите внимание, что имя каждого параметра начинается со значка @. Но имя параметра не может быть изменено на этой вкладке, так же как нельзя добавить новый параметр. Это делается только в бланке запроса в окне Конструктора или прямо в инструкции SQL.
Для того чтобы создать хранимую процедуру, состоящую из нескольких инструкций SQL, нужно открыть список запросов в окне проекта и нажать кнопку Создать (New). В диалоговом окне Новый запрос (New Query) следует выбрать элемент Ввод сохраненной процедуры (Create Text Stored Procedure) .
Нужно изменить имя процедуры в первой строке шаблона и ввести текст процедуры в соответствии с правилами языка Transact-SQL.
Если в теле процедуры нужно ввести инструкцию SQL SELECT, то можно воспользоваться для этого Конструктором запросов. Установите курсор на строке редактора, в которой должна быть введена инструкция SELECT и нажмите кнопку Вставить SQL (Insert SQL) на панели инструментов . Появится окно Конструктора запросов. После того как запрос будет создан, нужно закрыть это окно, подтвердив сохранение изменений инструкции SQL.
Пример использования хранимой процедуры в качестве источника записей вы увидите, если откроете отчет "Employee Sales by Country". В качестве источника записей в этом отчете используется одноименная хранимая процедура. В свойстве Входные параметры (Input Parameters) этого отчета описаны два параметра: @Beginning_date И @Ending_date.
При запуске отчета эти параметры будут запрашиваться так же, как и при использовании запроса с параметрами в базе данных Access.
О языке Transact-SQL, используемом в хранимых процедурах, см. документацию по Microsoft SQL Server.