PL / SQL - PL/SQL

PL / SQL ( процедурный язык для SQL) - это процедурное расширение корпорации Oracle для SQL и реляционной базы данных Oracle . PL / SQL доступен в Oracle Database (начиная с версии 6 - хранимые процедуры / функции / пакеты / триггеры PL / SQL, начиная с версии 7), базы данных Times Ten в памяти (начиная с версии 11.2.1) и IBM DB 2 (начиная с версии 9.7). Корпорация Oracle обычно расширяет функциональность PL / SQL с каждым последующим выпуском Oracle Database.

PL / SQL включает элементы процедурного языка, такие как условия и циклы . Он позволяет объявлять константы и переменные , процедуры и функции, типы и переменные этих типов и триггеры. Он может обрабатывать исключения (ошибки времени выполнения). Поддерживаются массивы с использованием коллекций PL / SQL. Реализации Oracle Database начиная с версии 8 включают функции, связанные с объектной ориентацией . Можно создавать модули PL / SQL, такие как процедуры, функции, пакеты, типы и триггеры, которые хранятся в базе данных для повторного использования приложениями, использующими любой из программных интерфейсов Oracle Database.

Исторически первая общедоступная версия определения PL / SQL была выпущена в 1995 году, а год создания Oracle ~ 1992. Он реализует стандарт ISO SQL / PSM .

Программный модуль PL / SQL

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

Анонимный блок PL / SQL

Базовая единица исходной программы PL / SQL - это блок, который объединяет связанные объявления и операторы. Блок PL / SQL определяется ключевыми словами DECLARE, BEGIN, EXCEPTION и END. Эти ключевые слова делят блок на декларативную часть, исполняемую часть и часть обработки исключений. Раздел объявления является необязательным и может использоваться для определения и инициализации констант и переменных. Если переменная не инициализирована, по умолчанию используется значение NULL . Необязательная часть обработки исключений используется для обработки ошибок времени выполнения. Требуется только исполняемая часть. Блок может иметь метку.

Например: 

<<label>>   -- this is optional
DECLARE
-- this section is optional
  number1 NUMBER(2);
  number2 number1%TYPE := 17;             -- value default
  text1   VARCHAR2(12) := 'Hello world';
  text2   DATE         := SYSDATE;        -- current date and time
BEGIN
-- this section is mandatory, must contain at least one executable statement
  SELECT street_number
    INTO number1
    FROM address
    WHERE name = 'INU';
EXCEPTION
-- this section is optional
   WHEN OTHERS THEN
     DBMS_OUTPUT.PUT_LINE('Error Code is ' || TO_CHAR(sqlcode));
     DBMS_OUTPUT.PUT_LINE('Error Message is ' || sqlerrm);
END;

Символ :=функционирует как оператор присваивания для хранения значения в переменной.

Блоки могут быть вложенными, т. Е. Поскольку блок является исполняемым оператором, он может появляться в другом блоке везде, где разрешен исполняемый оператор. Блок можно отправить в интерактивный инструмент (например, SQL * Plus) или встроить в прекомпилятор Oracle или программу OCI . Интерактивный инструмент или программа запускает блок один раз. Блок не сохраняется в базе данных, и по этой причине он называется анонимным блоком (даже если у него есть метка).

Функция

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

Функция PL / SQL имеет вид: 

CREATE OR REPLACE FUNCTION <function_name> [(input/output variable declarations)] RETURN return_type
[AUTHID <CURRENT_USER | DEFINER>] <IS|AS>   -- heading part
amount number;   -- declaration block
BEGIN   -- executable part
	<PL/SQL block with return statement>
        RETURN <return_value>;
[Exception
	none]
        RETURN <return_value>;
END;

Табличные функции с конвейерной структурой возвращают коллекции и принимают форму: 

CREATE OR REPLACE FUNCTION <function_name> [(input/output variable declarations)] RETURN return_type
[AUTHID <CURRENT_USER | DEFINER>] [<AGGREGATE | PIPELINED>] <IS|USING>
	[declaration block]
BEGIN
	<PL/SQL block with return statement>
        PIPE ROW <return type>;
        RETURN;
[Exception
	exception block]
        PIPE ROW <return type>;
        RETURN;
END;

Функция должна использовать только тип параметра IN по умолчанию. Единственным выходным значением функции должно быть возвращаемое ею значение.

Процедура

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

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

CREATE PROCEDURE create_email_address ( -- Procedure heading part begins
    name1 VARCHAR2,
    name2 VARCHAR2,
    company VARCHAR2,
    email OUT VARCHAR2
) -- Procedure heading part ends
AS
-- Declarative part begins (optional)
error_message VARCHAR2(30) := 'Email address is too long.';
BEGIN -- Executable part begins (mandatory)
    email := name1 || '.' || name2 || '@' || company;
EXCEPTION -- Exception-handling part begins (optional)
WHEN VALUE_ERROR THEN
    DBMS_OUTPUT.PUT_LINE(error_message);
END create_email_address;

В приведенном выше примере показана автономная процедура - этот тип процедуры создается и сохраняется в схеме базы данных с помощью оператора CREATE PROCEDURE. Процедура также может быть создана в пакете PL / SQL - это называется процедурой пакета. Процедура, созданная в анонимном блоке PL / SQL, называется вложенной процедурой. Автономные процедуры или процедуры пакета, хранящиеся в базе данных, называются « хранимыми процедурами ».

Процедуры могут иметь три типа параметров: IN, OUT и IN OUT.

  1. Параметр IN используется только как ввод. Параметр IN передается по ссылке, хотя он может быть изменен неактивной программой.
  2. Параметр OUT изначально равен NULL. Программа присваивает значение параметра, и это значение возвращается вызывающей программе.
  3. Параметр IN OUT может иметь или не иметь начальное значение. Это начальное значение может или не может быть изменено вызываемой программой. Любые изменения, внесенные в параметр, по умолчанию возвращаются вызывающей программе путем копирования, но - с подсказкой NO-COPY - могут быть переданы по ссылке .

PL / SQL также поддерживает внешние процедуры через стандартный ext-procпроцесс базы данных Oracle .

Упаковка

Пакеты - это группы концептуально связанных функций, процедур, переменных, таблиц PL / SQL и операторов TYPE записей, констант, курсоров и т. Д. Использование пакетов способствует повторному использованию кода. Пакеты состоят из спецификации пакета и дополнительного тела пакета. Спецификация - это интерфейс к приложению; он объявляет типы, переменные, константы, исключения, курсоры и доступные подпрограммы. Тело полностью определяет курсоры и подпрограммы и, таким образом, реализует спецификацию. Два преимущества пакетов:

  1. Модульный подход, инкапсуляция / скрытие бизнес-логики, безопасность, повышение производительности, возможность повторного использования. Они поддерживают функции объектно-ориентированного программирования, такие как перегрузка функций и инкапсуляция.
  2. Используя переменные пакета, можно объявлять переменные уровня сеанса (ограниченные областью действия), поскольку переменные, объявленные в спецификации пакета, имеют область действия сеанса.

Спусковой крючок

Основная статья: Триггер базы данных

Триггер базы данных , как хранимая процедура , которая Oracle Database Запускает автоматически при возникновении определенного события. Это именованный модуль PL / SQL, который хранится в базе данных и может быть вызван повторно. В отличие от хранимой процедуры, вы можете включать и отключать триггер, но не можете вызывать его явно. Пока триггер включен, база данных автоматически вызывает его, то есть триггер срабатывает, когда происходит его инициирующее событие. Пока триггер отключен, он не срабатывает.

Вы создаете триггер с помощью оператора CREATE TRIGGER. Вы указываете инициирующее событие в терминах операторов запуска и элемента, на который они воздействуют. Говорят, что триггер создан или определен для элемента, который является таблицей, представлением , схемой или базой данных. Вы также указываете точку отсчета времени, которая определяет, срабатывает ли триггер до или после запуска оператора триггера, и будет ли он срабатывать для каждой строки, на которую влияет этот оператор триггера.

Если триггер создается в таблице или представлении, то инициирующее событие состоит из операторов DML, и триггер называется триггером DML. Если триггер создается в схеме или базе данных, то инициирующее событие состоит из операторов DDL или операций с базой данных, и триггер называется системным триггером.

Триггер INSTEAD OF - это либо триггер DML, созданный в представлении, либо системный триггер, определенный в операторе CREATE. База данных запускает триггер INSTEAD OF вместо запуска оператора запуска.

Назначение триггеров

Триггеры можно писать для следующих целей:

  • Автоматическое создание некоторых производных значений столбцов
  • Обеспечение ссылочной целостности
  • Регистрация событий и хранение информации о доступе к таблицам
  • Аудит
  • Синхронная репликация таблиц
  • Введение авторизации безопасности
  • Предотвращение недействительных транзакций

Типы данных

Основные типы данных в PL / SQL включают NUMBER, CHAR, VARCHAR2, DATE и TIMESTAMP.

Числовые переменные

variable_name number([P, S]) := 0;

Чтобы определить числовую переменную, программист добавляет к определению имени тип переменной ЧИСЛО . Чтобы указать (необязательную) точность (P) и (необязательный) масштаб (S), можно добавить их в круглые скобки, разделенные запятой. («Точность» в этом контексте относится к количеству цифр, которое может содержать переменная, а «масштаб» относится к количеству цифр, которые могут следовать за десятичной точкой.)

Выбор других типов данных для числовых переменных будет включать: binary_float, binary_double, dec, decimal, double precision, float, integer, int, numeric, real, small-int, binary_integer.

Символьные переменные

variable_name varchar2(20) := 'Text';

-- e.g.: 
address varchar2(20) := 'lake view road';

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

Другие типы данных для символьных переменных включают: varchar, char, long, raw, long raw, nchar, nchar2, clob, blob и bfile.

Переменные даты

variable_name date := to_date('01-01-2005 14:20:23', 'DD-MM-YYYY hh24:mi:ss');

Переменные даты могут содержать дату и время. Время можно опустить, но нет способа определить переменную, которая содержит только время. Типа DATETIME нет. И есть тип ВРЕМЯ. Но не существует типа TIMESTAMP, который может содержать точную временную метку с точностью до миллисекунды или наносекунды. Типы данных Oracle

TO_DATEФункция может быть использована для преобразования строк в значение даты. Функция преобразует первую строку в кавычках в дату, используя в качестве определения вторую строку в кавычках, например: 

 to_date('31-12-2004', 'dd-mm-yyyy')

или 

 to_date ('31-Dec-2004', 'dd-mon-yyyy', 'NLS_DATE_LANGUAGE = American')

Для преобразования дат в строки используется функция TO_CHAR (date_string, format_string).

PL / SQL также поддерживает использование литералов даты и интервала ANSI. Следующий пункт дает 18-месячный диапазон: 

WHERE dateField BETWEEN DATE '2004-12-30' - INTERVAL '1-6' YEAR TO MONTH
    AND DATE '2004-12-30'

Исключения

Исключения - ошибки во время выполнения кода - бывают двух типов: определяемые пользователем и предопределенные.

Определяемые пользователем исключения всегда явно вызываются программистами с помощью команд RAISEили RAISE_APPLICATION_ERRORв любой ситуации, когда они определяют невозможность продолжения нормального выполнения. Команда RAISEимеет синтаксис: 

RAISE <exception name>;

Корпорация Oracle заранее определила несколько исключений, например NO_DATA_FOUND, TOO_MANY_ROWSи т. Д. Каждое исключение имеет номер ошибки SQL и сообщение об ошибке SQL, связанное с ним. Программисты могут получить доступ к ним, используя SQLCODEи SQLERRMфункции.

Типы данных для определенных столбцов

Variable_name Table_name.Column_name%type;

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

Программисты указывают определяемые пользователем типы данных с помощью синтаксиса: 

type data_type is record (field_1 type_1 := xyz, field_2 type_2 := xyz, ..., field_n type_n := xyz);

Например: 

declare
    type t_address is record (
        name address.name%type,
        street address.street%type,
        street_number address.street_number%type,
        postcode address.postcode%type);
    v_address t_address;
begin
    select name, street, street_number, postcode into v_address from address where rownum = 1;
end;

Этот пример программы определяет свой собственный тип данных, называемый t_address , который содержит имя поля , улицу, street_number и почтовый индекс .

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

Используя этот тип данных, программист определил переменную с именем v_address и загрузил в нее данные из таблицы ADDRESS.

Программисты могут обращаться к отдельным атрибутам в такой структуре с помощью точечной нотации, таким образом: 

v_address.street := 'High Street';

Условные утверждения

В следующем сегменте кода показана конструкция IF-THEN-ELSIF-ELSE. Части ELSIF и ELSE являются необязательными, поэтому можно создавать более простые конструкции IF-THEN или IF-THEN-ELSE. 

IF x = 1 THEN
   sequence_of_statements_1;
ELSIF x = 2 THEN
   sequence_of_statements_2;
ELSIF x = 3 THEN
   sequence_of_statements_3;
ELSIF x = 4 THEN
   sequence_of_statements_4;
ELSIF x = 5 THEN
   sequence_of_statements_5;
ELSE
   sequence_of_statements_N;
END IF;

Оператор CASE упрощает некоторые большие структуры IF-THEN-ELSIF-ELSE. 

CASE
   WHEN x = 1 THEN sequence_of_statements_1;
   WHEN x = 2 THEN sequence_of_statements_2;
   WHEN x = 3 THEN sequence_of_statements_3;
   WHEN x = 4 THEN sequence_of_statements_4;
   WHEN x = 5 THEN sequence_of_statements_5;
   ELSE sequence_of_statements_N;
END CASE;

Оператор CASE может использоваться с предопределенным селектором: 

CASE x
   WHEN 1 THEN sequence_of_statements_1;
   WHEN 2 THEN sequence_of_statements_2;
   WHEN 3 THEN sequence_of_statements_3;
   WHEN 4 THEN sequence_of_statements_4;
   WHEN 5 THEN sequence_of_statements_5;
   ELSE sequence_of_statements_N;
END CASE;

Обработка массивов

PL / SQL называет массивы «коллекциями». Язык предлагает три типа коллекций:

  1. Ассоциативные массивы (индексируемые таблицы)
  2. Вложенные таблицы
  3. Varrays (массивы переменного размера)

Программисты должны указать верхний предел для varrays, но не для таблиц с индексированием или вложенных таблиц. Язык включает несколько методов сбора, используемых для управления элементами коллекции: например, FIRST, LAST, NEXT, PRIOR, EXTEND, TRIM, DELETE и т. Д. Таблицы индексирования могут использоваться для моделирования ассоциативных массивов, как в этом примере функции памятки. для функции Аккермана в PL / SQL .

Ассоциативные массивы (индексные таблицы)

С помощью индексных таблиц массив может быть проиндексирован числами или строками. Он аналогичен карте Java , которая состоит из пар ключ-значение. Есть только одно измерение, и оно безгранично.

Вложенные таблицы

С вложенными таблицами программист должен понимать, что именно вложено. Здесь создается новый тип, который может состоять из ряда компонентов. Затем этот тип можно использовать для создания столбца в таблице, и в этот столбец вложены эти компоненты.

Varrays (массивы переменного размера)

Используя Varrays, вы должны понимать, что слово «переменная» во фразе «массивы переменного размера» не применимо к размеру массива так, как вы могли бы подумать. Размер, с которым объявлен массив, фактически фиксирован. Количество элементов в массиве может изменяться до заявленного размера. Возможно, тогда массивы переменного размера не такие переменные по размеру.

Курсоры

Курсор представляет собой механизм, указатель на частную область SQL , которая хранит информацию , поступающая из SELECT , или язык манипулирования данных (DML) заявления (INSERT, UPDATE, DELETE или MERGE). Курсор держит строки (один или несколько) , возвращаемой SQL заявление. Набор строк, удерживаемых курсором, называется активным набором.

Курсор может быть явным или неявным. В цикле FOR должен использоваться явный курсор, если запрос будет использоваться повторно, в противном случае предпочтителен неявный курсор. При использовании курсора внутри цикла рекомендуется использовать FETCH при необходимости массового сбора данных или при необходимости динамического SQL.

Зацикливание

В процедурном языке по определению, PL / SQL предоставляет несколько итераций конструкции, в том числе основных операторов цикла, циклов WHILE , для циклов и курсора для петель. Начиная с Oracle 7.3 был введен тип REF CURSOR, позволяющий возвращать наборы записей из хранимых процедур и функций. Oracle 9i представил предопределенный тип SYS_REFCURSOR, что означает, что нам больше не нужно определять наши собственные типы REF CURSOR.

Операторы LOOP

<<parent_loop>>
LOOP
	statements

	<<child_loop>>
	loop
		statements
		exit parent_loop when <condition>; -- Terminates both loops
		exit when <condition>; -- Returns control to parent_loop
	end loop child_loop;
        if <condition> then
           continue; -- continue to next iteration
        end if;

	exit when <condition>;
END LOOP parent_loop;

Циклы могут быть прерваны с помощью EXIT ключевого слова или путем создания исключения .

FOR петли

DECLARE
    var NUMBER;
BEGIN
    /* N.B. for loop variables in PL/SQL are new declarations, with scope only inside the loop */
    FOR var IN 0 .. 10 LOOP
        DBMS_OUTPUT.PUT_LINE(var);
    END LOOP;

    IF var IS NULL THEN
        DBMS_OUTPUT.PUT_LINE('var is null');
    ELSE
        DBMS_OUTPUT.PUT_LINE('var is not null');
    END IF;
END;

Выход: 

 0
 1
 2
 3
 4
 5
 6
 7
 8
 9
 10
 var is null

Курсор для циклов

FOR RecordIndex IN (SELECT person_code FROM people_table)
LOOP
  DBMS_OUTPUT.PUT_LINE(RecordIndex.person_code);
END LOOP;

Циклы Cursor-for автоматически открывают курсор , считывают свои данные и снова закрывают курсор.

В качестве альтернативы, программист PL / SQL может заранее определить оператор SELECT курсора, чтобы (например) разрешить повторное использование или сделать код более понятным (особенно полезно в случае длинных или сложных запросов). 

DECLARE
  CURSOR cursor_person IS
    SELECT person_code FROM people_table;
BEGIN
  FOR RecordIndex IN cursor_person
  LOOP
    DBMS_OUTPUT.PUT_LINE(recordIndex.person_code);
  END LOOP;
END;

Концепция person_code в цикле FOR выражается точечной нотацией ("."): 

RecordIndex.person_code

Динамический SQL

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

В случае динамического SQL PL / SQL ранние версии Oracle Database требовали использования сложной DBMS_SQLбиблиотеки пакетов Oracle . Однако более поздние версии представили более простой «собственный динамический SQL» вместе со связанным EXECUTE IMMEDIATEсинтаксисом.

Похожие языки

PL / SQL работает аналогично встроенным процедурным языкам, связанным с другими реляционными базами данных . Например, Sybase ASE и Microsoft SQL Server имеют Transact-SQL , PostgreSQL имеет PL / pgSQL (который до некоторой степени имитирует PL / SQL), MariaDB включает синтаксический анализатор совместимости PL / SQL, а IBM DB2 включает процедурный язык SQL, который соответствует ISO SQL «s SQL / PSM стандарт.

Разработчики PL / SQL смоделировали его синтаксис на основе Ada . И Ada, и PL / SQL имеют Паскаль в качестве общего предка, поэтому PL / SQL также напоминает Паскаль во многих аспектах. Однако структура пакета PL / SQL не похожа на базовую структуру программы Object Pascal, реализованную модулем Borland Delphi или Free Pascal . Программисты могут определять общедоступные и частные глобальные типы данных, константы и статические переменные в пакете PL / SQL.

PL / SQL также позволяет определять классы и создавать их экземпляры как объекты в коде PL / SQL. Это похоже на использование в объектно-ориентированных языках программирования, таких как Object Pascal , C ++ и Java . PL / SQL относится к классу как к «абстрактному типу данных» (ADT) или «определяемому пользователем типу» (UDT) и определяет его как тип данных Oracle SQL , а не определяемый пользователем тип PL / SQL, что позволяет его использование как в Oracle SQL Engine, так и в Oracle PL / SQL Engine. Конструктор и методы абстрактного типа данных написаны на PL / SQL. Результирующий абстрактный тип данных может работать как объектный класс в PL / SQL. Такие объекты также могут сохраняться как значения столбцов в таблицах базы данных Oracle.

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

Продукт StepSqlite - это компилятор PL / SQL для популярной небольшой базы данных SQLite, который поддерживает подмножество синтаксиса PL / SQL. В выпуске Oracle Berkeley DB 11g R2 добавлена ​​поддержка SQL на основе популярного API SQLite путем включения версии SQLite в Berkeley DB. Следовательно, StepSqlite также можно использовать как сторонний инструмент для запуска кода PL / SQL в Berkeley DB.

Смотрите также

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

дальнейшее чтение

внешние ссылки