Структура программы на PL/SQL
PL/SQL - это процедурный блочно-структурированный язык. Он представляет собой расширение языка SQL и предназначен для работы с СУБД Oracle.
PL/SQL предоставляет разработчику приложений и интерактивному пользователю следующие основные возможности:
реализация подпрограмм как отдельных блоков, в том числе использование вложенных блоков;
создание пакетов, процедур и функций, хранимых в базе данных;
предоставление интерфейса для вызова внешних процедур;
поддержка как типов данных SQL, так и типов, вводимых в PL/SQL;
применение явного и неявного курсора, а также оператора цикла FOR для курсора;
введение у переменных PL/SQL и курсоров атрибутов, которые позволяют ссылаться на тип данных или структуру элемента;
введение типов коллекций и объектных типов;
поддержка набора операторов управления и операторов цикла;
реализация механизма обработки исключений.
Основной программной единицей PL/SQL является блок, который может содержать вложенные блоки, называемые иногда подблоками.
Блок позволяет объединять объявления и операторы, связанные общей логикой; может быть анонимным и именованным.
Блок состоит из трех основных частей:
секция объявлений (необязательная часть);
тело блока;
обработчики исключений (необязательная часть).
[ <> ]
[DECLARE
]
BEGIN
[EXCEPTION
]
END [label_name];
|
- Метка блока
- Секция объявлений
- Тело блока
- Обработчики исключений
|
PL/SQL не чувствителен к регистру, кроме строковых переменных и констант.
Каждая конструкция PL/SQL должна заканчиваться символом ;.
Одна конструкция может быть расположена на нескольких строках.
Работа с процедурами и функциями
CREATE OR REPLACE FUNCTION FName (V1 TYPE, V2 TYPE, ...) RETURN TYPE IS
...
BEGIN
...
END FName;
---
CREATE OR REPLACE PROCEDURE TEST_POZ(
PR_A IN NUMBER,
PR_B IN NUMBER,
PR_C IN VARCHAR2,
PR_D IN VARCHAR2
)
IS
BEGIN
NULL;
END TEST_POZ;
Типы данных
Язык PL/SQL поддерживает следующие категории типов:
встроенные типы данных, включая коллекции и записи;
объектные типы данных.
Встроенные типы данных
Встроенные типы данных
На рис. 11.1 приведен список встроенных типов 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 ()
|
Создают "пустой" объект указанного типа
|
Приведение типов
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-операторе вставки или обновления для каждого столбца таблицы должно быть указано значение поля записи.
|
