PL/SQL - это процедурный язык пошагового программирования, инкапсулирующий язык SQL. В результате получается хорошо развитый язык программирования третьего поколения (3GL), подобный языку C++, Pascal и т. д.
PL/SQL имеет строгие правила области видимости переменных, поддерживает параметризованные вызовы процедур и функций.
PL/SQL предусматривает строгий контроль типов, все ошибки несовместимости типов выявляются на этапе компиляции и выполнения. Так же поддерживается явное и неявное преобразование типов.
PL/SQL - поддерживает сложные структуры данных, так же предусмотрена перегрузка подпрограмм, для создания гибкой среды прикладного программирования.
Язык PL/SQL - имеет элемент Exception Handler для обработки ошибок на этапе выполнения кода PL/SQL.
PL/SQL - является машинно независимым языком программирования. Сам код программных блоков PL/SQL не зависит от платформы, на которой они выполняются.
PL/SQL - поддерживает стандартные интерфейсы работы с языками высокого уровня такими как C, C++ - через предкомпиляторы поставляемые фирмой Oracle. Например, для работы с языком C есть такое средство как OCI (Oracle Call Interface).
Так же PL/SQL имеет ряд встроенных средств для работы с Internet, прямо из хранимых процедур. Имеет поддержку создания HTTP запросов так же непосредственно из хранимых процедур.
Обычно при разработке программ все используемые в них SQL-операторы явно записываются в исходном коде. Такой вариант использования SQL-операторов обычно называют статический SQL. Многие полезные программы, однако, до момента запуска не "знают", какие именно SQL-операторы будут выполняться. Именно так и появляется динамический SQL - программа при запуске выполняет SQL-операторы, неизвестные вовремя компиляции. Например, программа генерирует запросы по ходу работы на основе введенных пользователем условий.
Использование динамического SQL - единственный способ выполнить SQL-операторы в программах на языке Java через интерфейс JDBC (выполнить динамический SQL в среде прекомпилятора SQLJ можно только через интерфейс JDBC) и на языке С при использовании библиотеки OCI.
При работе через функциональный интерфейс поддерживается только динамический SQL. Программист создает запрос в виде строки, а затем эта строка анализируется, связываются входящие в нее переменные, запрос выполняется, при необходимости выбираются строки из результирующего множества через курсор и, наконец, соответствующий курсор закрывается.
Версия с динамическим SQL требует от разработчика написания гораздо большего объема кода. При статическом SQL приложения разрабатываются быстрее, но динамический SQL обеспечивает большую гибкость при выполнении. Кроме того, статический SQL (особенно в среде PL/SQL) будет выполняться намного эффективнее, чем динамический. Используя статический SQL, PL/SQL-машина при обработке одной строки интерпретируемого кода может сделать то, на что потребуется пять или шесть строк интерпретируемого кода с динамическим SQL. Поэтому используется статический SQL где только возможно и применяется динамический, только если по-другому задачу решить нельзя. Оба они эффективны, ни один не имеет принципиальных преимуществ перед другим, и оба имеют свои специфические возможности и средства повышения производительности.
Примеры областей использования динамического SQL:
• Разработка обобщенных процедур, выполняющих стандартные действия вроде выгрузки данных в файлы.
• Разработка универсальных процедур загрузки данных в не известные заранее таблицы.
• Динамический вызов других PL/SQL-процедур во время выполнения.
• Генерация условий (например, конструкции WHERE) в процессе работы на основе введенных пользователем данных. Это, пожалуй, основная причина использования динамического SQL большинством разработчиков.
• Выполнение операторов языка определения данных (DDL). Поскольку PL/SQL не разрешает включать статические операторы DDL в код приложения, остается использовать динамический SQL. Это позволит выполнять операторы, начинающиеся с ключевых слов CREATE, ALTER, GRANT, DROP и т.п.
Встроенный динамический SQL реализуется в PL/SQL оператором EXECUTE IMMEDIATE.
Встроенный динамический SQL имеет смысл использовать в следующих случаях.
• Когда количество и типы столбцов, с которыми придется работать, заранее известны.
• Когда заранее известно количество и типы связываемых переменных. (Можно также использовать контексты приложений, чтобы с помощью более простого встроенного динамического SQL выполнять операторы с заранее неизвестным количеством или типами связываемых переменных.)
• Когда необходимо выполнять операторы DDL.
• Если динамически формируемые операторы будут выполняться лишь несколько раз (оптимальный вариант - однократно).
Пакет DBMS_SQL необходимо использовать в следующих случаях.
• Если заранее не известно количество или типы столбцов, с которыми придется работать. Пакет DBMS_SQL включает процедуры для описания результирующего множества. При использовании встроенного динамического SQL необходимо знать характеристики результирующего множества при компиляции, если результаты необходимо обрабатывать в PL/SQL.
• Если заранее не известно количество или типы связываемых переменных, с которыми придется работать. Пакет DBMS_SQL по ходу выполнения позволяет привязать с помощью процедур входные переменные к операторам.
• Когда необходимо выбирать или вставлять тысячи строк и можно использовать обработку массивов. Пакет DBMS_SQL поддерживает обработку массивов - возможность выбрать N строк за раз, одним вызовом.
• Если в сеансе многократно выполняется один и тот же оператор. Пакет DBMS_SQL позволяет один раз разобрать оператор, а затем выполнять его многократно. При использовании встроенного динамического SQL мягкий разбор будет осуществляться при каждом выполнении.
Дополнительно:
Оператор EXECUTE IMMEDIATE имеет следующий синтаксис:
где:
• оператор - любой оператор SQL или PL/SQL-блок;
• переменная1, переменная2,... переменнаяN или запись - переменные PL/SQL, в которые необходимо выбрать данные (столбцы одной строки результатов оператора SELECT);
• связываемая_переменная1,... связываемая переменнаяN - набор переменных PL/SQL, используемых для передачи входных данных/результатов;
• результат1, ... результатN - набор PL/SQL-переменных, используемых для размещения результатов, возвращаемых конструкцией RETURN оператора языка модели данных.
Тип данных Описание
-------------- ---------------------------------------------------------------
BINARY_INTEGER Этот тип данных и его подтипы NATURAL и POSITIVE применяются для
создания переменных и констант, которые хранят число со знаком.
Двоичные целые числа могут принимать значения в диапазоне от -2
в 31 степени до 2 в 31 степени минус 1.
BOOLEAN Принимается для создания переменных и констант, в которых
хранятся логические значения TRUE и FALSE.
CHAR Есть подтипы CHARACTER и STRING. Максимальный размер 32767.
NUMBER Есть подтипы DEC, DECIMAL, DOUBLE, PRECISION, FLOAT, INT,
INTEGER, NUMERIC, REAL и SMALLINT.
RECORD Используется для создания пользовательских типов записей базы
данных.
TABLE Служит для создания табличных типов данных PL/SQL.
VARCHAR2 Есть подтип VARCHAR. Максимальный размер 32767.
col%TYPE Используется для определения типа данных столбца или переменной
по типу данных другого столбца или переменной, к имени которого
или которой (col) приписан суффикс %TYPE.
tab%ROWTYPE Используется для определения типа данных записи по типу данных
столбцов таблицы, к имени которой (tab) приписан суффикс
%ROWTYPE.
Пример:
Выводит количество строк в заданной таблице.
create or replace
function get_row_cnts(p_tname in varchar2) return number
as
l_cnt number;
begin
execute immediate
'select count(*) from ' || p_tname
into l_cnt;
return l_cnt;
end;