SQL-Ex blog

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

Поехали!

Использование расширенных курсоров

Введение в расширенные курсоры

В Oracle PL/SQL курсор - это объект базы данных, который позволяет извлекать множество строк данных и манипулировать ими, как и в операции, основанной на множествах. Помимо простых явных курсоров, мы имеем понятие расширенных курсоров типа параметризованных курсоров, REF курсоров и переменных курсоров, которые привносят больше гибкости и динамизма в ваши операции.

Параметризованные курсоры

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

Вот простой пример параметризованного курсора:

DECLARE 

   CURSOR c_emp (p_deptno NUMBER) 

   IS 

      SELECT ename, job FROM emp WHERE deptno = p_deptno; 

   v_ename emp.ename%TYPE; 

   v_job emp.job%TYPE; 

BEGIN 

   OPEN c_emp(20); 

   LOOP 

      FETCH c_emp INTO v_ename, v_job; 

      EXIT WHEN c_emp%NOTFOUND; 

      DBMS_OUTPUT.PUT_LINE(v_ename || ' - ' || v_job); 

   END LOOP; 

   CLOSE c_emp; 

END; 

/

В этом примере курсор c_emp принимает параметр p_deptno. Когда мы открываем курсор, то передаем значение этого параметра для получения сотрудников из конкретного отделения с номером 20.

Курсоры REF

REF курсоры или переменные типа курсора являются более гибкими, чем статичные курсоры. В отличие от статичных курсоров, которые фиксируются во время компиляции, REF курсоры могут открываться для любого запроса во время выполнения, что делает их весьма гибкими для программ, которым необходимо компилировать запросы динамически.

Вот простой пример REF курсора:

DECLARE 

   TYPE emp_ref IS REF CURSOR; 

   c_emp emp_ref; 

   v_ename emp.ename%TYPE; 

   v_job emp.job%TYPE; 

BEGIN 

   OPEN c_emp FOR SELECT ename, job FROM emp WHERE deptno = 20; 

   LOOP 

      FETCH c_emp INTO v_ename, v_job; 

      EXIT WHEN c_emp%NOTFOUND; 

      DBMS_OUTPUT.PUT_LINE(v_ename || ' - ' || v_job); 

   END LOOP; 

   CLOSE c_emp; 

END; 

/

В этом примере мы определяем тип курсора REF emp_ref и объявляем переменную курсора c_emp этого типа. Затем мы открываем переменную курсора для запроса, который извлекает сотрудников из отдела номер 20.

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

Продвинутая обработка исключений

Обработка исключений является ключевой частью любой программы PL/SQL. Она позволяет разработчикам предвидеть и устранять ошибки или исключения, которые могут возникнуть при выполнении программы. Хотя вы уже видели базовую обработку ошибок, продвинутые методы обработки исключений обеспечивают больший контроль и гибкость в неожиданных ситуациях.

Исключения, определяемые пользователем

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

Вот пример определяемых пользователем исключений:

DECLARE

  emp_count INTEGER;

  too_many_employees EXCEPTION;

BEGIN

  SELECT COUNT(*) INTO emp_count FROM emp WHERE deptno = 20;

  IF emp_count > 50 THEN

    RAISE too_many_employees;

  END IF;

EXCEPTION

  WHEN too_many_employees THEN

    DBMS_OUTPUT.PUT_LINE('Error: Too many employees in department 20.');

END;

/

В этом примере определяемое пользователем исключение too_many_employees вызывается, когда в отделении 20 оказывается более 50 сотрудников.

Распространение исключений

Иногда вам может потребоваться обработать исключение не в том блоке, где оно возникло. В таких случаях вы можете использовать распространение исключения.

Когда исключение возникает и не обрабатывается в текущем блоке, оно распространяется на включающий (или вызывающий) блок. Это продолжается до тех пор, пока исключение либо обрабатывается, либо достигается самый внешний блок. Если исключение нигде не обрабатывается, программа завершается с ошибкой необработанного исключения.

Вот пример:

DECLARE

  value_null EXCEPTION; 

  PRAGMA EXCEPTION_INIT(value_null, -1476); 

BEGIN

  -- Это вызывает исключение

  DECLARE

    a number := 0;

    b number := 1/a;

  EXCEPTION

    WHEN value_null THEN

       dbms_output.put_line('A value is null!');

END;

В этом примере исключение распространяется от внутреннего блока во внешний блок, где оно обрабатывается.

Информационные функции исключений

Oracle предоставляет такие функции, как SQLCODE и SQLERRM, которые возвращают полезную информацию об исключениях. SQLCODE возвращает числовой код исключения, а SQLERRM возвращает сообщение, связанное с этим исключением.

Вот пример использования этих функций:

DECLARE

  a number := 0;

  b number := 1/a;

EXCEPTION

  WHEN OTHERS THEN

    dbms_output.put_line('Error code ' || SQLCODE || ': ' || SQLERRM);

END;

/

В этом примере, когда возникает исключение, выводятся код ошибки и сообщение об ошибке при использовании функций SQLCODE и SQLERRM соответственно.

Расширенная обработка исключений в PL/SQL предлагает больше контроля над процессом обработки ошибок, делая ваши программы более понятными и надежными.

Работа с пакетами Oracle

Введение в пакеты Oracle

Oracle предоставляет набор предварительно скомпилированных объектных модулей PL/SQL, известных как пакеты Oracle. Эти пакеты встроены в базу данных Oracle и значительно расширяют ее функциональность от выдачи сообщений на экран, ввода/вывода в файл и выполнения заданий по расписанию до отправки электронных писем и много другого.

Давайте рассмотрим несколько ключевых пакетов Oracle:

Пакет DBMS_OUTPUT

Пакет DBMS_OUTPUT позволяет отображать вывод, отладочную информацию или посылать сообщения из блоков, подпрограмм, пакетов и триггеров PL/SQL.

Вот базовый пример:

BEGIN

  DBMS_OUTPUT.PUT_LINE('Hello, World!');

END;

/

В этом примере на экране отображается 'Hello, World!', используя процедуру PUT_LINE из пакета DBMS_OUTPUT.

Пакет UTL_FILE

Пакет UTL_FILE позволяет программам PL/SQL читать и записывать текстовые файлы операционной системы. Он предоставляет ограниченную версию ввода-вывода в стандартный поток файлов операционной системы.

Вот простой пример записи в файл:

DECLARE

  file_handle UTL_FILE.FILE_TYPE;

BEGIN

  file_handle := UTL_FILE.FOPEN('MY_DIR', 'my_file.txt', 'W');

  UTL_FILE.PUT_LINE(file_handle, 'Hello, World!');

  UTL_FILE.FCLOSE(file_handle);

EXCEPTION

  WHEN OTHERS THEN

    IF UTL_FILE.IS_OPEN(file_handle) THEN

      UTL_FILE.FCLOSE(file_handle);

    END IF;

    RAISE;

END;

/

В этом примере мы используем пакет UTL_FILE для записи 'Hello, World!' в текстовый файл с именем ‘my_file.txt’, находящийся в каталоге ‘MY_DIR’.

Пакет DBMS_SCHEDULER

Пакет DBMS_SCHEDULER - более продвинутый и гибкий пакет выполнения заданий по расписанию, чем DBMS_JOB. Он позволяет планировать выполнение заданий (т.е. анонимных блоков PL/SQL, хранимых процедур PL/SQL и хранимых процедур Java) в заданное время/дату или через заданные интервалы.

Вот базовый пример планирования задания:

BEGIN

  DBMS_SCHEDULER.CREATE_JOB(

    job_name => 'my_test_job',

    job_type => 'PLSQL_BLOCK',

    job_action => 'BEGIN DBMS_OUTPUT.PUT_LINE(''Hello, World!''); END;',

    start_date => SYSTIMESTAMP,

    repeat_interval => 'FREQ=MINUTELY;INTERVAL=5',

    enabled => TRUE);

END;

/

В этом примере мы создаем задание с именем 'my_test_job', которое выводит 'Hello, World!' каждые 5 минут от момента создания.

Работа с пакетами Oracle может в значительной мере улучшить вашу производительность, предоставляя предварительно встроенные решения для распространенных требований от посылки электронного сообщения с помощью UTL_MAIL до обработки больших объектов с помощью DBMS_LOB и многое еще. Их использование может сделать программирование на PL/SQL более эффектным и эффективным.

Настройка производительности в PL/SQL

Введение в настройку производительности

Настройка производительности является жизненно важным аспектом программирования в PL/SQL. Она гарантирует, что ваши приложения эффективно работают при высоких рабочих нагрузках. Существуют различные методы оптимизации, но мы сфокусируемся на некоторых практических стратегиях, которые могут оказать существенное влияние на производительность ваших приложений.

Оптимизация операторов SQL

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

Массовая обработка с BULK COLLECT и FORALL

Эти функции PL/SQL позволяют выполнять "массовые" операции, которые значительно ускоряют обработку большого количества данных. Сокращая переключение контекста между движками PL/SQL и SQL, эти функции могут значительно повысить производительность.

DECLARE

   TYPE t_emp_tab IS TABLE OF employees%ROWTYPE;

   emp_tab t_emp_tab;

BEGIN

   SELECT *

   BULK COLLECT INTO emp_tab

   FROM employees;

   FOR i IN emp_tab.FIRST .. emp_tab.LAST LOOP

      emp_tab(i).salary := emp_tab(i).salary * 1.05;  -- Увеличить на 5% 

   END LOOP;

   FORALL i IN emp_tab.FIRST .. emp_tab.LAST

      UPDATE employees SET row = emp_tab(i) WHERE employee_id = emp_tab(i).employee_id;

   COMMIT;

END;

/

Использование переменных связывания

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

DECLARE

   v_employee_id employees.employee_id%TYPE := 100;

   v_first_name employees.first_name%TYPE;

BEGIN

   SELECT first_name INTO v_first_name FROM employees WHERE employee_id = v_employee_id;

   DBMS_OUTPUT.PUT_LINE('First name: ' || v_first_name);

END;

/

Оптимизатор PL/SQL

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

ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 3;

Избегать необязательных вычислений

Одна простая, но эффективная стратегия состоит в том, чтобы избегать необязательных вычислений. Если вы обнаружите, что ваш код выполняет одни и те же вычисления в нескольких местах, рассмотрите возможность выполнить вычисления один раз, сохранения результата, а затем использовать этот результат при необходимости.

DECLARE

   v_base_salary employees.salary%TYPE := 5000;

   v_bonus employees.salary%TYPE := v_base_salary * 0.1;  -- Вычисляем один раз

BEGIN

   -- Используем предварительно вычисленное значение 

   INSERT INTO bonuses (employee_id, bonus) VALUES (100, v_bonus);

   INSERT INTO bonuses (employee_id, bonus) VALUES (101, v_bonus);

   INSERT INTO bonuses (employee_id, bonus) VALUES (102, v_bonus);

END;

/

Использование инструментов для анализа производительности

Oracle предоставляет такие инструменты, как TKPROF и AUTOTRACE, которые могут использоваться для анализа производительности ваших запросов SQL и обнаружения проблемных мест. Эти инструменты дают подробные отчеты о выполнении кода SQL и PL/SQL, облегчая идентификацию тех мест, настройка производительности которых может дать выигрыш.

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

Взаимодействие PL/SQL и SQL*PLUS

Введение в SQL*Plus

SQL*Plus — это инструмент интерактивных и пакетных запросов, который устанавливается с каждой инсталляцией базы данных Oracle и обеспечивает функциональность SQL, PL/SQL и скриптов. Это важный инструмент для взаимодействия с базой данных Oracle, и понимание того, как он взаимодействует с PL/SQL, может быть полезным.

Выполнение блоков PL/SQL

Анонимные блоки PLSQL могут непосредственно выполняться в SQL*Plus. Вот пример с объявлением переменной, присвоением ей значения с его последующим выводом:

DECLARE

   v_message VARCHAR2(50) := 'Hello, SQL*Plus!';

BEGIN

   DBMS_OUTPUT.PUT_LINE(v_message);

END;

/

Не забывайте использовать прямой слэш (/) для выполнения блоков PL/SQL в SQL*Plus.

Использование переменных SQL*Plus в блоках PL/SQL

Вы можете определять переменные в SQL*Plus, а затем ссылаться на них в блоках PL/SQL:

DEFINE v_message = 'Hello, SQL*Plus!'

DECLARE

   v_local_message VARCHAR2(50) := '&v_message';

BEGIN

   DBMS_OUTPUT.PUT_LINE(v_local_message);

END;

/

В этом примере &v_message в блоке PL/SQL является переменная подстановки, которая заменяется значением v_message, определенной в SQL*Plus.

Использование команды SET SERVEROUTPUT

По умолчанию вывод пакета DBMS_OUTPUT не отображается в SQL*Plus. Для вывода вам необходимо использовать команду SET SERVEROUTPUT ON:

SET SERVEROUTPUT ON

DECLARE

   v_message VARCHAR2(50) := 'Hello, SQL*Plus!';

BEGIN

   DBMS_OUTPUT.PUT_LINE(v_message);

END;

/

Выполнение хранимых процедур и функций

Хранимые процедуры и функции также могут непосредственно быть выполнены из SQL*Plus. Для процедур вы можете просто сделать вызов:

EXECUTE procedure_name(аргументы);

Для функций, которые возвращают значение, вы можете использовать оператор SELECT:

SELECT function_name(аргументы) FROM dual;

Сценарии в SQL*Plus

SQL*Plus может также выполнять скрипты, которые являются просто текстовыми файлами со списком команд SQL*Plus, SQL и PL/SQL. Вы можете использовать эту функцию для выполнения последовательности команд всех сразу.

Например, вы можете создать файл сценария с именем script.sql и следующим содержимым:

SET SERVEROUTPUT ON

DECLARE

   v_message VARCHAR2(50) := 'Hello, SQL*Plus!';

BEGIN

   DBMS_OUTPUT.PUT_LINE(v_message);

END;

/

Затем вы можете выполнить этот скрипт в SQL*Plus с помощью следующей команды:

@script.sql

Владение взаимодействием между SQL*Plus and PL/SQL позволит вам получить максимальную отдачу от базы данных Oracle и упростит процесс разработки базы данных.

Естественная компиляция PL/SQL

Oracle предоставляет возможность естественной компиляции кода PL/SQL в машинный код, который может значительно улучшить производительность выполнения программ PL/SQL. Естественная компиляция - это процесс, при котором исходный код PL/SQL компилируется в разделяемую библиотеку в форме DLL на Windows или SO (Shared Object - разделяемый объект) в системах Unix/Linux.

Вот как вы можете включить и использовать естественную компиляцию PL/SQL:

Включение естественной компиляции

Для использования естественной компиляции вам необходимо включить ее на системном уровне. Вы можете использовать следующую команду:

ALTER SYSTEM SET PLSQL_CODE_TYPE = 'NATIVE';

Эта команда заставит Oracle естественным образом компилировать весь новый код PL/SQL.

Компилирование существующего кода

Если вы хотите откомпилировать существующий код PL/SQL, вы можете использовать следующую команду:

ALTER PROCEDURE имя_процедуры COMPILE PLSQL_CODE_TYPE = NATIVE;

Замените имя_процедуры на имя процедуры, функции, пакета или триггера, который вы хотите откомпилировать.

Проверка компиляции

Вы можете проверить, откомпилирован ли ваш код PL/SQL естественным образом или интерпретируется, с помощью следующего запроса:

SELECT name, type, plsql_code_type 

FROM user_code 

WHERE name = 'PROCEDURE_NAME';

Замените PROCEDURE_NAME на имя процелуры, функции, пакета или триггера. Столбец PLSQL_CODE_TYPE будет содержать NATIVE, если код откомпилирован, и INTERPRETED в противном случае.

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

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

Продвинутые триггеры базы данных

Триггерами базы данных являются именованные блоки PL/SQL, которые хранятся в базе данных и автоматически выполняются (срабатывают), когда наступают определенные события. В предыдущей статье мы обсудили простые триггеры, которые связаны с событиями DML (вставка, обновление, удаление) для конкретной таблицы. Однако есть еще более продвинутые типы триггеров, которые вы можете использовать для применения сложных бизнес правил или расширения функциональности вашей базы данных.

Триггеры Instead of

Эти триггеры определяются на представлениях, и они срабатывают вместо события, которое их вызвало. Они могут использоваться для модификации данных в базовой таблице, когда операция DML выполняется на представлении.

Вот пример триггера instead of:

CREATE OR REPLACE TRIGGER io_emp_view

INSTEAD OF INSERT ON emp_view

FOR EACH ROW

BEGIN

   INSERT INTO employees (employee_id, first_name, last_name)

   VALUES (:NEW.employee_id, :NEW.first_name, :NEW.last_name);

END;

/

Этот триггер должен вставить данные в таблицу employees, в то время как вставка выполняется для представления emp_view.

Триггеры системных событий

Эти триггеры срабатывают, когда возникают системные события. Наиболее общими типами системных событий являются события входа и выхода, ошибки сервера, запуск или выключение базы данных и т.п.

Вот пример триггера на вход:

CREATE OR REPLACE TRIGGER logon_audit

AFTER LOGON ON DATABASE

BEGIN

   INSERT INTO logon_audit (username, logon_time)

   VALUES (USER, SYSDATE);

END;

/

Этот триггер будет вставлять запись в таблицу logon_audit всякий раз, когда пользователь подключается к базе данных.

Триггеры DDL

Эти триггеры срабатывают в ответ на операторы DDL (языка определения данных) типа CREATE, ALTER, DROP и т.д. Они могут оказаться полезными для отслеживания изменений схемы базы данных.

Вот пример триггера DDL:

CREATE OR REPLACE TRIGGER ddl_audit

AFTER DDL ON DATABASE

BEGIN

   INSERT INTO ddl_audit (username, timestamp, operation)

   VALUES (USER, SYSDATE, ora_sysevent);

END;

/

Этот триггер будет вставлять запись в таблицу ddl_audit всякий раз, когда пользователь выполняет операцию DDL.

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

Динамический SQL в PL/SQL

Введение в динамический SQL

Динамический SQL - это технология, используемая для построения операторов SQL динамически во время исполнения. Это особенно полезно, когда вам нужно выполнять операцию, но вы заранее не знаете, с какой таблицей или какими столбцами вы будет работать. Динамический SQL применяется в PL/SQL посредством оператора EXECUTE IMMEDIATE и пакета DBMS_SQL.

EXECUTE IMMEDIATE

Оператор EXECUTE IMMEDIATE позволяет выполнить оператор динамического SQL или анонимный блок PL/SQL.

Вот пример того, как вы можете использовать EXECUTE IMMEDIATE для вставки данных в таблицу:

DECLARE

   v_table_name  VARCHAR2(30) := 'employees';

   v_sql         VARCHAR2(1000);

BEGIN

   v_sql := 'INSERT INTO ' || v_table_name || ' VALUES (100, ''John'', ''Doe'')';

   EXECUTE IMMEDIATE v_sql;

END;

/

В этом примере оператор SQL конструируется динамически, используя переменную v_table_name, а затем выполняется с помощью EXECUTE IMMEDIATE.

Пакет DBMS_SQL

Пакет DBMS_SQL предоставляет более продвинутый интерфейс для динамического SQL, позволяя выполнять анализ оператора SQL любого типа, а также связывание и определение заполнителей.

Вот пример того, как можно использовать пакет DBMS_SQL для динамического выполнения оператора SELECT:

DECLARE

   v_cursor   NUMBER;

   v_count    NUMBER;

   v_sql      VARCHAR2(1000);

BEGIN

   v_sql := 'SELECT COUNT(*) FROM employees WHERE department_id = :dept_id';   

   v_cursor := DBMS_SQL.OPEN_CURSOR;

   DBMS_SQL.PARSE(v_cursor, v_sql, DBMS_SQL.NATIVE);

   DBMS_SQL.BIND_VARIABLE(v_cursor, ':dept_id', 10);

   DBMS_SQL.DEFINE_COLUMN(v_cursor, 1, v_count);

   DBMS_SQL.EXECUTE_AND_FETCH(v_cursor, v_count);   

   DBMS_OUTPUT.PUT_LINE('Count: ' || v_count);

   DBMS_SQL.CLOSE_CURSOR(v_cursor);

END;

/

В этом примере пакет DBMS_SQL используется для анализа оператора SQL, связывания значения с заполнителем :dept_id, определения переменной для хранения результата оператора SELECT, выполнения оператора, получения результата и, наконец, закрытия курсора.

Динамический SQL может стать мощным инструментом в вашем арсенале прораммирования на PL/SQL, позволяя писать гибкий и адаптивный код, который может выполнять операции на базе условий, которые становятся известными только во время выполнения. Однако всегда следует заботиться о потенциальных рисках SQL-инъекции и принимать надлежащие меры предосторожности при построении динамических операторов SQL.

Заключение

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


Ссылки по теме
1. За и против динамического SQL
2. Использование триггера INSTEAD OF для обновления представления
3. Триггеры: от любви до ненависти
4. Работа с курсорами SQL