Что такое PL/SQL?sql-4

PL/SQL (Procedural Language extensions to SQL) — это процедурное расширение языка SQL, разработанное Oracle Corporation для работы с Oracle Database. PL/SQL сочетает в себе мощь SQL с возможностями процедурных языков программирования, такими как переменные, условия, циклы и обработка ошибок.

Ключевые особенности PL/SQL

  1. Интеграция с SQL
    PL/SQL позволяет встраивать SQL-запросы непосредственно в процедурный код, обеспечивая seamless-взаимодействие.

  2. Процедурная парадигма
    В отличие от декларативного SQL, PL/SQL является императивным языком с поддержкой:

    • Переменных и констант
    • Условных операторов (IF-THEN-ELSE)
    • Циклов (FOR, WHILE)
    • Обработки исключений

  3. Серверная исполняемая среда
    Код PL/SQL выполняется непосредственно на сервере БД, что уменьшает сетевой трафик и повышает производительность.

Основные компоненты PL/SQL

1. Блоки кода

Базовая структура PL/SQL состоит из анонимных или именованных блоков:

DECLARE
    -- Объявление переменных
    v_employee_name VARCHAR2(100);
    v_salary NUMBER;
BEGIN
    -- Исполняемая часть
    SELECT name, salary INTO v_employee_name, v_salary
    FROM employees WHERE id = 100;

    -- Условная логика
    IF v_salary < 5000 THEN
        UPDATE employees SET salary = salary * 1.1 WHERE id = 100;
        DBMS_OUTPUT.PUT_LINE('Salary increased for ' || v_employee_name);
    END IF;
EXCEPTION
    -- Обработка ошибок
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Employee not found');
END;

2. Хранимые программы

PL/SQL поддерживает создание постоянных объектов БД:

Процедуры:

CREATE OR REPLACE PROCEDURE increase_salary (
    p_employee_id IN NUMBER,
    p_percentage IN NUMBER
) AS
    v_current_salary NUMBER;
BEGIN
    SELECT salary INTO v_current_salary
    FROM employees WHERE id = p_employee_id;

    UPDATE employees SET salary = salary * (1 + p_percentage/100)
    WHERE id = p_employee_id;

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

Функции:

CREATE OR REPLACE FUNCTION get_employee_salary (
    p_employee_id IN NUMBER
) RETURN NUMBER AS
    v_salary NUMBER;
BEGIN
    SELECT salary INTO v_salary
    FROM employees WHERE id = p_employee_id;

    RETURN v_salary;
END get_employee_salary;

Триггеры:

CREATE OR REPLACE TRIGGER log_salary_changes
BEFORE UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
    INSERT INTO salary_audit (
        employee_id,
        old_salary,
        new_salary,
        change_date
    ) VALUES (
        :OLD.id,
        :OLD.salary,
        :NEW.salary,
        SYSDATE
    );
END;

3. Пакеты

Логическая группировка связанных процедур, функций и переменных:

CREATE OR REPLACE PACKAGE employee_mgmt AS
    PROCEDURE hire_employee(p_name VARCHAR2, p_dept_id NUMBER);
    PROCEDURE fire_employee(p_emp_id NUMBER);
    FUNCTION get_dept_employees(p_dept_id NUMBER) RETURN SYS_REFCURSOR;
END employee_mgmt;

CREATE OR REPLACE PACKAGE BODY employee_mgmt AS
    PROCEDURE hire_employee(p_name VARCHAR2, p_dept_id NUMBER) IS
    BEGIN
        -- Реализация процедуры
    END;

    -- Остальные реализации
END employee_mgmt;

Преимущества PL/SQL

  1. Производительность

    • Снижение сетевого трафика (выполнение на сервере)
    • Поддержка bulk-операций (FORALL, BULK COLLECT)

  2. Модульность

    • Возможность создания повторно используемых компонентов
    • Инкапсуляция логики в пакетах

  3. Безопасность

    • Выполнение с правами владельца (definer's rights)
    • Защита от SQL-инъекций

Пример сложной логики

CREATE OR REPLACE PROCEDURE process_year_end_bonus AS
    CURSOR emp_cur IS
        SELECT id, salary, hire_date FROM employees
        WHERE active_flag = 'Y';

    v_bonus NUMBER;
    v_years_service NUMBER;
BEGIN
    FOR emp_rec IN emp_cur LOOP
        v_years_service := TRUNC(MONTHS_BETWEEN(SYSDATE, emp_rec.hire_date)/12);

        -- Сложная логика расчета бонуса
        IF v_years_service > 10 THEN
            v_bonus := emp_rec.salary * 0.15;
        ELSIF v_years_service > 5 THEN
            v_bonus := emp_rec.salary * 0.10;
        ELSE
            v_bonus := emp_rec.salary * 0.05;
        END IF;

        INSERT INTO bonuses (employee_id, amount, year)
        VALUES (emp_rec.id, v_bonus, EXTRACT(YEAR FROM SYSDATE));
    END LOOP;

    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END process_year_end_bonus;

Резюмируем

PL/SQL — это мощное расширение SQL, которое превращает Oracle Database в полноценную платформу для разработки серверных приложений. Оно особенно полезно для:

  • Комплексной бизнес-логики
  • Пакетной обработки данных
  • Создания API уровня базы данных
  • Административных задач

Глубокое знание PL/SQL делает разработчика крайне ценным специалистом в экосистеме Oracle, позволяя создавать высокопроизводительные и надежные решения для работы с данными.

Вопросы

Собеседования