Основы языка PL/SQL

Типы данных

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

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

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

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

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

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

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

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

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

Любой базовый тип 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 поддерживает следующие четыре типа для больших объектов:

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

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

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

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

Например:

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 FROM tbl1 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, используемый для управления ходом выполнения программы, может относиться к одной из следующих групп операторов:

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

Язык 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;