Программа на PL/SQL состоит из блоков, анонимных или поименованных. К именованным блокам относятся хранимые процедуры, хранимые функции и триггеры
Рассмотрим хранимые процедуры. Процедуру можно вызвать на выполнение и передать ей параметры. Процедура обычно выполняет какие-то действия, в отличие от хранимых функций, которые обычно выполняют вычисления и возвращают результат.
В отличие от анонимных блоков хранимые процедуры и функции хранятся в базе данных, когда анонимные блоки не хранятся и компилируются каждый раз для того, чтобы опробовать код.
Синтаксически хранимые процедуры состоят из спецификации и тела. В спецификациях процедуры указывается ее имя и параметры. В теле указываются локальные переменные, если имеются, и команды. Синтаксис создания хранимой процедуры следующий.
CREATE [OR REPLACE] PROCEDURE имя_процедуры
[(параметр1 [режим] тип_данных1,
параметр2 [режим] тип_данных2,...)]
IS|AS
[объявления_локальных_переменных; …]
BEGIN -- действия;
END [имя_процедуры];
Ключевое слово OR REPLACE указывается для возможности изменения уже созданной процедуры. Иначе возникнет ошибка: уже существует объект базы данных с таким именем.
В скобках после имени процедуры идет перечисление ее параметров. Параметры передают данные из вызывающей программы или среды и возвращают их из подпрограммы. Каждый параметр описывается именем, типом данных и режимом. Имя параметра используется в теле процедуры как переменная.
Существуют три режима параметров: IN, OUT, IN OUT.
· IN является режимом по умолчанию, предоставляет значение процессу, выполняющему подпрограмму.
· OUT возвращает значение вызывающей программе или среде.
· IN OUT предоставляет входное значение, которое может быть возвращено после изменения.
Также важно различать формальные и фактические параметры процедуры. Формальными являются параметры, содержащиеся в спецификации процедуры. Фактическими являются параметры, указываемые при вызове процедуры. Фактические и формальные параметры должны иметь один и тот же тип данных, но совпадение имен не обязательно. Также в качестве фактических параметров могут помимо переменных выступать числа, строки и выражения (значения выражений вычисляться перед передачей параметра в процедуру).
Пример.
Процедуры можно вызывать из SQL-кода и из PL/SQL-кода. В первом случае возможны 2 варианта синтаксиса.
1. EXECUTE имя_процедуры(параметры)
2. VARIABLE переменная_1 тип_1
VARIABLE переменная_2 тип_2
...
EXECUTE имя_процедуры(параметры)
PRINT переменная_1, переменная_2
Для работы печати связанных переменных нужно один раз выполнить следующий запрос.
SET AUTOPRINT ON
Для вызова процедуры из PL/SQL-кода синтаксис проще.
Имя_процедуры(Параметры)
Параметры в процедуру можно передавать тремя способами.
1. Позиционно.
Фактические параметры перечисляются в том же порядке, что и формальные. Например.
EXECUTE add_dept ('TRAINING', 2500)
2. По имени параметра.
Параметры передаются с помощью следующей записи.
Имя_параметра => Значение_параметра
При вызове функции это выглядит следующим образом.
EXECUTE имя_процедуры(Имя_параметра_2 => Значение_параметра_2,
Имя_параметра_3 => Значение_параметра_3,
Имя_параметра_1 => Значение_параметра_1)
Например.
EXECUTE add_dept (p_loc=>2400, p_name=>'EDUCATION')
3. Комбинированно.
Комбинированный метод возможен, если параметрам заданы значения по умолчанию (задаются с помощью такого же синтаксиса как и для переменных). Тогда сначала позиционно указываются значения параметров без значений по умолчанию, а затем с помощью имени параметра передаются некоторые параметры, значения которых должны быть отличны от значений по умолчанию.
Например.
EXECUTE add_dept
EXECUTE add_dept ('ADVERTISING', loc => 1200)
EXECUTE add_dept (loc => 1200)
Для следующей процедуры.
CREATE OR REPLACE PROCEDURE add_dept(
name departments.department_name%TYPE:='Unknown',
loc departments.location_id%TYPE DEFAULT 1700)
IS
BEGIN
INSERT INTO departments (...)
VALUES (departments_seq.NEXTVAL, name, loc);
END add_dept;
Удалить процедуру можно с помощью команды DROP. Например,
DROP PROCEDURE Имя_процедуры;
Просмотреть текст процедуры можно с помощью следующего запроса.
SELECT text FROM user_source
WHERE name = 'Имя_процедуры' AND type = 'PROCEDURE' ORDER BY line;