Лекция №1: Стандарты языка sql
| Вид материала | Лекция |
- Задачи курса Основы языка sql (и его расширения, t-sql, используемого sql server 2000), 22.95kb.
- Отчёт по курсовой работе на тему «Лабораторный практикум по изучению языка структурированных, 1960.59kb.
- Бобровски С. Oracle7 и вычисления клиент/сервер, 1004.32kb.
- Тема «Введение в язык sql», 148.85kb.
- Уважаемые пользователи босс-кадровик на платформе Microsoft, 34.05kb.
- Установка sql express 2005, 24.56kb.
- Здопомогою мови sql можна створювати запити до реляційних баз даних (таких як Access),, 276.28kb.
- Программа курса: Модуль Краткий обзор sql server Что такое сервер sql server Интегрирование, 35.73kb.
- Курс лекций "Базы данных и субд" Логинова С. А. Лекция 13. Язык sql. Команды dml. Команды, 133.93kb.
- Курс 2778. Создание запросов на языке Microsoft sql server 2005 Transact-sql. Курс, 16.57kb.
Лекция №11: Основы языка PL/SQL
Структура программы на 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 должна заканчиваться символом ;.
Одна конструкция может быть расположена на нескольких строках.
Типы данныхЯзык PL/SQL поддерживает следующие категории типов:
Встроенные типы данныхНа рис. 11.1 приведен список встроенных типов 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 ph1 FOR UPDATE; buf := 'Текст, который будет вставлен в объект LOB'; DBMS_LOB.write (pf1, lenght(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;
tbl1_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;
- IF-THEN-END IF;
- операторы цикла
- LOOP-END LOOP;
- WHILE-LOOP-END LOOP;
- FOR-LOOP-END LOOP;
- EXIT;
- EXIT WHEN;
- LOOP-END LOOP;
- операторы безусловного перехода
- GOTO;
- NULL;
- <
>.
- GOTO;
Операторы выбора
Язык 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 -- Ключевое слово ELSIF
sequence_of_statements2; -- может повторяться многократно
ELSIF condition3 THEN sequence_of_statements3;
ELSE -- Ключевое слово ELSE может отсутствовать
sequence_of_statements4; END IF;
Последовательность операторов (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;
<
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;
--
<
NULL; -- Оператор указывается для использования метки
END LOOP;
END;