Процедуры Oracle — это мощные объекты базы данных, которые позволяют инкапсулировать набор операторов SQL в модульный модуль многократного использования. Они улучшают модульность кода, удобство сопровождения и производительность в базах данных Oracle. В этой статье мы рассмотрим различные методы создания процедур Oracle с примерами кода, охватывающими различные сценарии и лучшие практики.

1. Базовая процедура Oracle:
   Начнем с простого примера процедуры Oracle, которая вставляет запись в таблицу:

CREATE OR REPLACE PROCEDURE insert_employee (
    p_emp_id     IN  NUMBER,
    p_first_name IN  VARCHAR2,
    p_last_name  IN  VARCHAR2
) AS
BEGIN
    INSERT INTO employees (emp_id, first_name, last_name)
    VALUES (p_emp_id, p_first_name, p_last_name);
    COMMIT;
END;
/

2. Процедура Oracle с параметрами и курсорами.
   Процедуры Oracle могут принимать параметры и использовать курсоры для выборки и обработки данных. Вот пример, который извлекает имена сотрудников на основе идентификатора отдела:

CREATE OR REPLACE PROCEDURE get_employees_by_dept (
    p_dept_id IN  NUMBER,
    p_cursor  OUT SYS_REFCURSOR
) AS
BEGIN
    OPEN p_cursor FOR
        SELECT first_name, last_name
        FROM employees
        WHERE department_id = p_dept_id;
END;
/

3. Процедура Oracle с обработкой исключений.
   Обработка исключений имеет решающее значение для управления ошибками в процедурах Oracle. Рассмотрим следующий пример, который обрабатывает исключения при удалении сотрудника:

CREATE OR REPLACE PROCEDURE delete_employee (
    p_emp_id IN NUMBER
) AS
BEGIN
    DELETE FROM employees
    WHERE emp_id = p_emp_id;

    IF SQL%NOTFOUND THEN
        RAISE_APPLICATION_ERROR(-20001, 'Employee not found.');
    END IF;

    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
/

4. Процедура Oracle с контролем транзакций.
   Операторы управления транзакциями, такие как COMMIT и ROLLBACK, могут использоваться в процедурах Oracle для управления изменениями, внесенными в транзакцию. Вот пример:

CREATE OR REPLACE PROCEDURE transfer_funds (
    p_from_account IN  NUMBER,
    p_to_account   IN  NUMBER,
    p_amount       IN  NUMBER
) AS
BEGIN
    UPDATE accounts
    SET balance = balance - p_amount
    WHERE account_id = p_from_account;

    UPDATE accounts
    SET balance = balance + p_amount
    WHERE account_id = p_to_account;

    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
/

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