11. Лекция: Основы языка PL/SQL

В лекции обсуждаются основы языка PL/SQL, используемого для работы с БД Oracle.

Структура программы на PL/SQL

PL/SQL - это процедурный блочно-структурированный язык. Он представляет собой расширение языка SQL и предназначен для работы с СУБД Oracle.

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

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

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

Блок позволяет объединять объявления и операторы, связанные общей логикой; может быть анонимным и именованным.

Блок состоит из трех основных частей:

  • секция объявлений (необязательная часть);
  • тело блока;
  • обработчики исключений (необязательная часть).

[ <<label_name>> ]
[DECLARE

]
BEGIN

[EXCEPTION

]
END [label_name];
 - Метка блока

 - Секция объявлений


 - Тело блока

 - Обработчики исключений 

PL/SQL не чувствителен к регистру, кроме строковых переменных и констант.

Каждая конструкция PL/SQL должна заканчиваться символом ;.

Одна конструкция может быть расположена на нескольких строках.

Типы данных

Язык PL/SQL поддерживает следующие категории типов:

  • встроенные типы данных, включая коллекции и записи;
  • объектные типы данных.

Встроенные типы данных

Встроенные типы данных

На рис. 11.1 приведен список встроенных типов PL/SQL.

Список встроенных типов PL/SQL
Рис. 11.1.  Список встроенных типов PL/SQL

Скалярные типы описывают простые значения, не имеющие внутренних составляющих.

Составные типы описывают структуры, в которых имеются внутренние компоненты.

Сылочные типы содержат значения. LOB типы содержат значения, называемые локаторами, которые определяют расположение больших объектов хранимых данных (например, графические файлы).

В следующей таблице приведено описание некоторых типов данных языка PL/SQL.

СинтаксисДиапазон значений
Числовые типы
BINARY_INTEGER PLS_INTEGER (целое со знаком)-2147483647 .. 2147483647. Тип PLS_INTEGER требует меньше памяти и обрабатывается быстрее, чем другие числовые типы
NUMBER[(precision,scale)] (с плавающей точкой)1.0E-130 .. 9.99E125
NUMERIC (с фиксированной точкой)точность до 38 десятичных знаков
FLOAT (с плавающей точкой)точность до 38 десятичных знаков
REAL (с плавающей точкой)точность до 18 десятичных знаков
Символьные типы
CHAR[(maximum_length)] (для строк постоянной длины) LONG (для строк переменной длины)до 32767 байт Для столбца базы данных максимальный размер типа CHAR составляет 2000 байтов, а типа LONG - до 2 Гб
RAW(maximum_length) LONG RAW (для двоичных данных или строк байтов)до 32767 байт Для столбца базы данных типа RAW максимальный размер - 2000 байт.
VARCHAR2 (maximum_length) (для строк символов переменной длины)до 32767 байт

Все встроенные типы данных являются базовыми типами.

Любой базовый тип PL/SQL определяется как набор значений и набор операций, выполнимых над этими значениями.

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

Определение подтипа может иметь следующее формальное описание:

SUBTYPE subtype_name IS base_type;

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

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

Например:

DECLARE
   SUBTYPE MyDate IS DATE;     
      - Основан на типе DATE 
   TYPE MyRec IS RECORD (time1 INTEGER, 
                         time2 INTEGER);
   SUBTYPE MyInterval IS MyRec;     
      - Основан на типе RECORD 
   SUBTYPE ID_N IS tbl1.f1%TYPE;     
      - Основан на типе столбца

Типы, используемые как базовые, не могут содержать ограничений длины. Для создания пользовательского типа с ограничением длины предварительно объявляется переменная такого типа и уже на ее основе определяется пользовательский тип.

Например:

DECLARE
   var1 VARCHAR2(6);     
      - Объявление переменной 
      - с ограничением длины
   SUBTYPE string_6 IS var1%TYPE;     
      - Объявление

LOB-типы

LOB-типы используются для хранения больших объектов (Large Object). Стандарт SQL-99 ввел поддержку LOB-типов для расширенного уровня соответствия. Однако в Oracle реализован более полный набор LOB-типов.

В Oracle8е позволяется хранить данные LOB-типа до 4 Гбайт.

Типы LOB от типа LONG отличаются, главным образом, тем, что при выборе значения любого LOB-типа посредством оператора SELECT возвращается указатель, а не само значение; кроме того, типы LOB могут быть и внешними.

Oracle поддерживает следующие четыре типа для больших объектов:

  • BFILE - для внешнего двоичного файла;
  • BLOB - для внутреннего двоичного объекта;
  • CLOB - для внутреннего символьного объекта;
  • NCLOB - для внутреннего символьного объекта, учитывающего национальный набор символов.

Любой объект LOB состоит из двух частей: данных и указателя на эти данные, называемого локатором.

Типы BLOB, CLOB или NCLOB могут использоваться как для столбца базы данных, так и для переменной PL/SQL.

Для загрузки объекта LOB предусмотрен пакет PL/SQL DBMS_LOB.

Пакет DBMS_LOB для работы с LOB-типами содержит процедуры и функции, некоторые из которых приведены в следующей таблице.

СинтаксисОписание
APPEND (d1,d2)Добавляет d2 к d1
COMPARE(d1,d2,n,pos1,pos2)Сравнивает n байт значений d1 и d2
COPY (d,s,n,dp,sp)Копирует n байт из d в s.
FILEOPEN (bdata,m)Открывает объект типа BFILE в режиме, указанном параметром m
LOADFROMFILE (bdata1,data2,n,pos1,pos2)Копирует n байт объекта типа BFILE bdata1 в любой объект LOB data2
GETLENGTH (data)Возвращает длину указанного объекта LOB
READ (data,n,pos,buf)Читает из объекта data n байт
WRITE (data,n,pos,buf)Копирует из буфера buf n байт
EMPTY_CLOB (), EMPTY_BLOB ()Создают "пустой" объект указанного типа

Например:

DECLARE
   pf1 CLOB;
   pf2 BLOB;
   buf varchar2;
BEGIN 
CREATE TABLE tbl1 ( f1 CLOB, f2 BLOB);
INSERT INTO tbl1 VALUES 
   (empty_clob(),empty_blob() );
   SELECT f1 INTO pf1 FOR UPDATE;
   buf := 'Текст, который будет вставлен
   в объект LOB';
   DBMS_LOB.write (pf1, length(buf), 0, buf);
END; 

Приведение типов

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

Значения переменных различных типов могут присваиваться друг другу в том случае, если они образованы из одного базового типа

Объявление переменных и констант

Переменные могут иметь тип данных SQL или тип данных PL/SQL.

Переменная объявляется в секциях объявлений блока PL/SQL, подпрограммы или пакета.

Для объявления переменной после ее идентификатора следует указывать любой доступный тип данных.

Объявление переменной в PL/SQL может иметь следующие формы:

var_name type;
var_name type := expr;
var_name type DEFAULT expr;
var_name type NOT NULL := expr;
var_name type_var%TYPE;
var_name type_var%TYPE := expr;
var_name user.table.type_col%TYPE;
var_name user.table.type_col%TYPE := expr;

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

При объявлении переменной вместо оператора присваивания может указываться ключевое слово DEFAULT.

Объявляемая переменная может быть определена как NOT NULL. Такой переменной в дальнейшем нельзя присвоить значение NULL.

Переменным можно присваивать значения двумя способам - как с помощью оператора присваивания, так и как INTO-переменной, указываемой в запросе.

При объявлении константы после идентификатора должно быть указано ключевое слово CONSTANT, а после идентификатора типа - указан оператор присваивания и значение константы.

Объявление константы может иметь следующее формальное описание:

const_name CONSTANT type :=value;

Например:

val_real CONSTANT REAL := 5000.00;

Символьные константы заключаются в одинарные кавычки.

Атрибуты %TYPE и %ROWTYPE

Атрибут %TYPE позволяет объявлять переменную типа, соответствующего:

  • типу другой переменной;
  • типу столбца базы данных.

Например:

var1 REAL(14,2);
var2 var1%TYPE;     
   - Переменная var2 будет иметь тип как var1
var_f1 user1.tbl1.f1%TYPE;     
   /* Переменная var_f1 будет иметь тот же 
      тип, что и поле f1 таблицы tbl1 
     пользователя user1*/ 

Атрибут %ROWTYPE позволяет объявлять переменную типа "запись", соответствующую строке таблицы. Переменная такого типа имеет поля, совпадающие с полями таблицы по имени и типу.

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

Значения переменным, определенным с использованием атрибута %ROWTYPE, могут быть назначены как присваиванием значения одной записи другой записи, так и как INTO-переменным оператора SELECT.

Например:

DECLARE
   tbl1_rec1 tbl1%ROWTYPE;   
      - Для строки из таблицы tbl1
   tbl1_rec2 tbl1%ROWTYPE;
   CURSOR c1 IS SELECT * FROM tbl1;
   emp_rec2 c1%ROWTYPE;   
      - Для строки курсора с1, 
      - созданного из таблицы tbl1
   emp_rec3 с1%ROWTYPE;
BEGIN 
   SELECT * INTO tbl1_rec1 FROM tbl1 
            WHERE tbl1.f1=1;
   emp_rec2 := emp_rec1;   
      - Присвоение значения всем полям записи
END

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

Операторы управления языка PL/SQL

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

  • операторы выбора:
    • IF-THEN-END IF;
    • IF-THEN-ELSE-END IF;
    • IF-THEN-ELSIF-END IF;
  • операторы цикла:
    • LOOP-END LOOP;
    • WHILE-LOOP-END LOOP;
    • FOR-LOOP-END LOOP;
    • EXIT;
    • EXIT WHEN;
  • операторы безусловного перехода:
    • GOTO;
    • NULL;
    • <<labels>>.

Операторы выбора

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

- 1 форма:

IF condition THEN sequence_of_statements; 
END IF;

- 2 форма:

IF condition THEN sequence_of_statements1;
ELSE sequence_of_statements2; END IF;

- 3 форма:

IF condition1 THEN sequence_of_statements1;
ELSIF condition2 THEN sequence_of_statements2;
   - Ключевое слово ELSIF 
   - может повторяться многократно
ELSIF condition3 THEN sequence_of_statements3;
ELSE sequence_of_statements4; END IF;
   - Ключевое слово ELSE может отсутствовать

Последовательность операторов (sequence_of_statements) может включать другой вложенный оператор выбора.

Например:

BEGIN
   IF f3 = 'abc' THEN
      UPDATE tbl1 SET f2 = f2 + 50 
     WHERE f1 = 1;...
   ELSE
      UPDATE tbl1 SET f2 = f2 + 70 
     WHERE f1 = 1;......
   END IF;

Операторы цикла

Оператор цикла позволяет многократно выполнять одну последовательность операторов. Язык PL/SQL реализует три формы операторов цикла, которые могут иметь следующее формальное описание:

- 1 форма - выход из цикла должен быть указан оператором выхода:

LOOP sequence_of_statements; END LOOP;

LOOP sequence_of_statements;
   EXIT WHEN boolean_expression;   
      - Оператор выхода из цикла
END LOOP;

<<label_of_loop>>  - Метка цикла
LOOP sequence_of_statements;
END LOOP label_of_loop;   
   - Конец помеченного цикла

- 2 форма - цикл выполняется, пока условие истинно:

WHILE condition LOOP sequence_of_statements;
END LOOP;

- 3 форма - цикл выполняется заданное число раз:

FOR counter IN [REVERSE] 
    lower_bound..higher_bound
LOOP sequence_of_statements;
END LOOP;

Для выхода из цикла используются операторы EXIT и EXIT-WHEN, а для выхода из блока PL/SQL - оператор RETURN.

Цикл FOR выполняется заданное число раз, пока значение счетчика цикла принадлежит указанному диапазону. Значение счетчика цикла FOR проверяется до выполнения цикла. Диапазон значений может быть указан через символ .. (две точки). Параметр REVERSE определяет обратный отсчет для переменной цикла. Диапазон значений может быть задан выражениями, но не должен изменяться внутри цикла.

Например:

- 1. цикл LOOP:

LOOP
   FETCH c1 INTO rec1;
   EXIT WHEN c1%NOTFOUND;

END LOOP;
     - Выход из цикла, если нет
     - больше строк

- 2. цикл WHILE:

WHILE c1 >= 50 LOOP

- ...

  c1:= c1 - 1;
END LOOP; 

Операторы безусловного перехода

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

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

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

Например:

 DECLARE
   i1 INTEGER;
BEGIN
   FOR i IN 1..10 LOOP
      IF i1=1 THEN
         GOTO end_loop;   
       - Переход на конец цикла
      END IF;
      - 
   <<end_loop>> 
   NULL;   - Оператор указывается для 
           - использования метки
   END LOOP; 
END;