Процедуры

📖 Описание

Процедура — подпрограмма PL/SQL для выполнения действий. Она может принимать и возвращать значения через параметры.


📌 СОЗДАТЬ ПРОЦЕДУРУ (CREATE PROCEDURE)

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


🧩 Синтаксис

CREATE [OR REPLACE] PROCEDURE имя_процедуры
[ (параметр [, параметр, ...]) ] IS
            [локальные объявления]
        BEGIN
            исполняемые предложения
        [EXCEPTION
            обработчики исключений]
        END [имя_процедуры];

Режим IN передаёт входное значение; OUT возвращает значение вызывающему коду; IN OUT передаёт начальное значение и возвращает изменённое. По умолчанию используется IN.


💻 Подготовка

Пример сохраняет исходную идею: найти код занятия явным курсором и записать выбранное название с найденным кодом или маркером отсутствия. В своей учебной схеме нужны CREATE TABLE, CREATE PROCEDURE и квота.

CREATE TABLE demo_proc_workshops (
  workshop_code NUMBER PRIMARY KEY,
  workshop_name VARCHAR2(60 CHAR) NOT NULL UNIQUE
);
CREATE TABLE demo_proc_requests (
  workshop_name VARCHAR2(60 CHAR) NOT NULL,
  workshop_code NUMBER NOT NULL,
  requested_on DATE NOT NULL
);
INSERT INTO demo_proc_workshops VALUES (841, 'Paper Mosaic');
INSERT INTO demo_proc_workshops VALUES (842, 'Wood Carving');
COMMIT;

UNIQUE гарантирует не больше одной строки по названию. В таблице заявок код 0 означает неизвестное занятие; поэтому здесь намеренно нет внешнего ключа на каталог. В прикладной схеме вместо маркера можно выбрать NULL или отклонение заявки.

💻 Создание процедуры

CREATE OR REPLACE PROCEDURE demo_add_workshop_request (
  p_workshop_name IN VARCHAR2,
  p_requested_on IN DATE DEFAULT DATE '2026-12-12'
) IS
  l_code demo_proc_workshops.workshop_code%TYPE;
  CURSOR c_workshop IS
    SELECT workshop_code
    FROM demo_proc_workshops
    WHERE workshop_name = p_workshop_name;
BEGIN
  OPEN c_workshop;
  FETCH c_workshop INTO l_code;
  IF c_workshop%NOTFOUND THEN
    l_code := 0;
  END IF;
  CLOSE c_workshop;

  INSERT INTO demo_proc_requests
    (workshop_name, workshop_code, requested_on)
  VALUES (p_workshop_name, l_code, p_requested_on);
END demo_add_workshop_request;
/

При отсутствии строки FETCH не выбрасывает NO_DATA_FOUND: проверяется %NOTFOUND. Курсор закрывается до INSERT. Процедура не фиксирует транзакцию; вызывающий код выбирает COMMIT или ROLLBACK. Пустое имя или NULL-дата не проходят ограничения таблицы, и ошибка передаётся вызывающему коду.

OR REPLACE пересоздаёт определение существующей самостоятельной процедуры. Для проверки компиляции:

SELECT line, position, text
FROM user_errors
WHERE name = 'DEMO_ADD_WORKSHOP_REQUEST' AND type = 'PROCEDURE'
ORDER BY sequence;

При успешной компиляции ошибок нет. В SQL*Plus/SQLcl символ / запускает завершённый блок в режиме сценария.

📌 Вызов

Вызовы расположены до удаления процедуры. Один использует значение даты по умолчанию, другой — именованные аргументы:

SAVEPOINT before_procedure_demo;
BEGIN
  demo_add_workshop_request('Paper Mosaic');
  demo_add_workshop_request(
    p_workshop_name => 'Glass Lantern',
    p_requested_on => DATE '2026-12-19'
  );
END;
/

SELECT workshop_name, workshop_code,
       TO_CHAR(requested_on, 'YYYY-MM-DD') AS request_day
FROM demo_proc_requests
ORDER BY workshop_code, workshop_name;
WORKSHOP_NAME WORKSHOP_CODE REQUEST_DAY
Glass Lantern 0 2026-12-19
Paper Mosaic 841 2026-12-12
ROLLBACK TO before_procedure_demo;
SELECT COUNT(*) AS requests_left FROM demo_proc_requests;
-- REQUESTS_LEFT = 0

IN — входной параметр и режим по умолчанию. OUT возвращает значение через переменную вызывающего кода; IN OUT принимает и возвращает значение. Процедура отличается от функции: у неё нет возвращаемого выражения RETURN value. RETURN без выражения может досрочно завершить процедуру.

📌 DROP PROCEDURE

🧩 Синтаксис

DROP PROCEDURE procedure_name;

💻 Пример

DROP PROCEDURE demo_add_workshop_request;

SELECT object_name FROM user_objects
WHERE object_type = 'PROCEDURE'
  AND object_name = 'DEMO_ADD_WORKSHOP_REQUEST';

Проверка не возвращает строк. Таблицы сохраняются; определение процедуры удалено и ROLLBACK его не восстановит. DDL фиксирует транзакцию, поэтому демонстрационные заявки откатываются заранее. DROP PROCEDURE применяется к самостоятельной процедуре, а для процедуры внутри пакета меняют определение пакета.

См. документацию Oracle: CREATE-PROCEDURE.

См. документацию Oracle: DROP-PROCEDURE.

Обработка ошибок: WHEN OTHERS и предопределённые исключения.