Процедурный язык запросов PL/SQL, страница 3

Рисунок 2 Таблица PL/SQL

·  RECORD (запись)

o  Запись должна содержать одну или несколько компонент (полей) скалярного типа, типа RECORD или типа TABLE (Рисунок 3)

o  Совокупность полей рассматривается как логическая единица

o  Записи удобны для выборки строк данных из таблицы базы данных для обработки

Рисунок 3 Запись PL/SQL

Объявление таблицы PL/SQL происходит в два этапа: сначала объявляется тип данных TABLE, потом объявляется переменная этого типа.

TYPE имя_типаIS TABLE OF скалярный_тип[NOT NULL] INDEX BY BINARY_INTEGER;

идентификатор имя_типа;

имя_типа – имя типа TABLE

скалярный_тип – тип данных для элементов таблицы PL/SQL. Можно использовать атрибут %TYPE

NOT NULL – не позволяет присваивать неопределенные значения таблице PL/SQL этого типа

идентификатор – имя идентификатора

SQL> DECLARE

  2  TYPE tp IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;

  3  var1 tp;

  4  BEGIN

  5     NULL;

  6  END;

  7  /

PL/SQL procedure successfully completed.

Объявление записи PL/SQL, также, происходит в два этапа: сначала объявляется тип данных RECORD, потом объявляется переменная этого типа.

TYPE имя_типа ISRECORD (имя_поля тип_поля [NOTNULL] [{ := | DEFAULT } выражение],
имя_поля тип_поля [NOTNULL] [{ := | DEFAULT } выражение], ...);

идентификатор имя_типа;

имя_типа – имя типа RECORD

имя_поля – имя поля записи PL/SQL

тип_поля – тип данных поля. Можно использовать атрибуты %TYPE и %ROWTYPE

NOT NULL – не позволяет присваивать неопределенные значения полям записи этого типа

выражение – любое выражение PL/SQL, используемое для инициализации поля

идентификатор – имя идентификатора

Для объявления записи на основе набора столбцов из таблицы или представления базы данных используется атрибут %ROWTYPE. При таком объявлении поля записи PL/SQL наследуют имена и типы данных соответствующих столбцов таблицы или представления.

имя_таблицы%ROWTYPE

SQL> DECLARE

  2  TYPE tp IS RECORD

  3       (f1 BINARY_INTEGER,

  4        f2 BOOLEAN := FALSE,

  5        f3 CHAR(10));

  6  var1 tp;

  7  var2 dept%ROWTYPE;

  8  BEGIN

  9     NULL;

 10  END;

 11  /

PL/SQL procedure successfully completed.

Область видимости переменных

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

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

SQL> DECLARE

  2  var1 NUMBER;

  3  BEGIN

  4     var1 := 123;

  5     DECLARE

  6     var1 NUMBER;

  7     BEGIN

  8        var1 := 456;

  9     END;

 10     DBMS_OUTPUT.put_line(var1);

 11  END;

 12  /

123

PL/SQL procedure successfully completed.

Присвоение значений переменным

Для присвоения значения переменной используется оператор присваивания PL/SQL (:=)

Для скалярного идентификатора:
идентификатор := выражение;

Для таблицы PL/SQL
имя_таблицы(значение_первичного_ключа) := выражение;

Для записи PL/SQL
имя_записи.имя_поля := выражение;

SQL> DECLARE

  2  var1 NUMBER;

  3  TYPE tt IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;

  4  var2 tt;

  5  TYPE tr IS RECORD

  6       (f1 BINARY_INTEGER,

  7        f2 BOOLEAN := FALSE,

  8        f3 CHAR(10));

  9  var3 tr;

 10  BEGIN

 11     var1 := 123;

 12     var2(1) := 456;

 13     var2(1000) := 789;

 14     var3.f2 := TRUE;

 15  END;

 16  /

PL/SQL procedure successfully completed.

Выражения PL/SQL

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

·  Арифметические

·  Логические

·  Оператор конкатенации

Кроме этого используется оператор возведения в степень (**).

Порядком выполнения операторов, как и в SQL,  можно управлять с помощью скобок.

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

·  Числовых функций

·  Символьных функций

·  Функций преобразования типов данных

o  TO_CHAR

o  TO_DATE

o  TO_NUMBER

·  Функций для работы с датами

Использование групповых функций допускается только в командах SQL.

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

Ссылка на внешние переменные (bind variable)

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

Ссылке на хост-переменную должно предшествовать двоеточие.

SQL*Plus содержит команды для объявления и вывода хост-переменных

§  В SQL*Plus для объявления хост-переменной используется команда VARIABLE

VARIABLE переменная[NUMBER | CHAR | CHAR(n) | VARCHAR2(n)]

§  Для вывода текущего значения хост-переменных используется команда PRINT

PRINT переменная

§  Одну команду PL/SQL можно выполнить с помощью команды EXECUTE. В этом случае ключевые слова BEGIN и END необязательны

EXECUTE команда_plsql

SQL> VARIABLE v_host NUMBER

SQL> EXECUTE :v_host := 123

PL/SQL procedure successfully completed.

SQL> PRINT v_host

   V_HOST

---------

      123

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

Переменные подстановки	Хост-переменные
Объявляются с помощью команд DEFINE, ACCEPT	Объявляются с помощью команды VARIABLE
Ссылке на переменную должен предшествовать амперсант (&)	Ссылке на переменную должно предшествовать двоеточие (:)
Значение переменной нельзя изменить или присвоить в блоке PL/SQL	Значение переменной можно изменить или присвоить в блоке PL/SQL

SQL> DEFINE v_subst = 100

SQL> VARIABLE v_host NUMBER

SQL> DECLARE

  2     v_local NUMBER;

  3  BEGIN

  4     v_local := 1000 + &v_subst;

  5     :v_host := v_local;

  6  END;

  7  /

old   4:    v_local := 1000 + &v_subst;

new   4:    v_local := 1000 + 100;

PL/SQL procedure successfully completed.

SQL> PRINT v_host

   V_HOST

---------

     1100

Литература

1.  Вильям Дж. Пэйдж Использование Oracle8/8i: пер. с англ. – М.: Издательский дом "Вильямс", 1999.