Порядок выполнения задания 4.

1. Создать несколько скриптов с операторами Insert. При написании оператора Insert поля, которые являются первичными ключами, и поля ID_CRE и DAT_CRE заполнять не следует – они будут заполняться автоматически триггерами. Создать не более 5-ти операторов для добавления записей по каждой таблице. Просмотреть, как работает система триггеров и как формируются значения первичных ключей.

 

2.3 СОДЕРЖАНИЕ ОТЧЁТА

 

Готовится один отчёт на бригаду в печатном виде. Он должен содержать следующие разделы:

- задание;

- описание процесса создания последовательностей (приводится скрипт DDL опреации);

- описание триггеров базы данных (приводится скрипт DDL операции).

Приём отчёта осуществляется в индивидуальном порядке. Ответы на контрольные вопросы даются устно.

 

КОНТРОЛЬНЫЕ ВОПРОСЫ

 

1. Для чего нужны последовательности?

2. Объяснить назначение параметров при создании сиквенса. Каково назначение параметра Cache?

3. Для чего предназначены триггеры?

4. Объяснить смысл скрипта PL/SQL в теле любого из созданных триггеров.

 

Лабораторная работа №3
РАЗРАБОТКА ФУНКЦИЙ И ПРОЦЕДУР ДЛЯ БАЗЫ ДАННЫХ

 

Цель работы. Овладеть навыками работы с языком PL/SQL. Научиться создавать хранимые процедуры (Procedure) и функции (Function).

КРАТКАЯ ТЕОРИЯ

3.3.1. Основные возможности PL/SQL

 

Хороший способ познакомиться с PL/SQL – это посмотреть на образец программы. Приведенная ниже программа обрабатывает заказ на теннисные ракетки. Сначала в ней объявляется переменная типа NUMBER, куда будет считано из базы данных количество имеющихся теннисных ракеток. Затем это количество извлекается из таблицы с именем Inventory. Если число ракеток больше нуля, то программа обновляет таблицу и вставляет запись о покупке в другую таблицу с именем Order_record. В противном случае в таблицу Order_recordвставляется запись об исчерпании запаса ракеток.

DECLARE

in_inventory NUMBER(5);

BEGIN

SELECT quantity INTO in_inventory FROM Inventory

WHERE product = ‘ТЕННИСНАЯ РАКЕТКА’;

IF in_inventory > 0 THEN -- проверка количества

UPDATE Inventory SET quantity = quantity - 1

WHERE product = 'ТЕННИСНАЯ РАКЕТКА';

INSERT INTO Order_record

VALUES ('Теннисная ракетка заказана', SYSDATE);

ELSE

INSERT INTO Order_record

VALUES ('Теннисные ракетки закончились', SYSDATE);

END IF;

COMMIT;

END;

В PL/SQL можно использовать команды SQL для манипулирования данными ORACLE и операторы потока управления для обработки данных. Кроме того, можно объявлять константы и переменные, определять подпрограммы (процедуры и функции) и ставить ловушки для ошибок периода исполнения. Таким образом, PL/SQL объединяет силу SQL в манипулировании данными и мощь процедурных языков в их обработке.

 

Структура блока

 

PL/SQL – язык с блочной структурой, т.е. базовые компоненты (процедуры, функции и неименованные блоки), из которых состоит программа на PL/SQL, представляют собой логические блоки, которые могут содержать любое число рекурсивно вложенных подблоков. Обычно каждый логический блок отвечает за решение некоторой задачи или ее подзадачи. Таким образом, PL/SQL поддерживает подход «разделяй и побеждай» к решению задач, называемый пошаговым уточнением.

Блок (или подблок) позволяет собрать вместе логически связанные объявления и операторы. Таким образом, вы можете размещать объявления в непосредственной близости от места их использования. Объявления локальны в блоке и перестают существовать, когда блок завершается.

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

 

[DECLARE -- объявления]

 

BEGIN

-- операторы
[EXCEPTION -- обработчики]
END;

Порядок частей обусловлен логически. Сначала идет раздел объявлений, где объекты могут быть объявлены. После объявления с объектами можно работать в исполняемом разделе. Возникшие при выполнении исключения могут быть обслужены разделом обработки исключений.

Исполняемый раздел и раздел обработки исключений блока или подпрограммы на PL/SQL могут содержать рекурсивно вложенные подблоки, а раздел объявлений – нет. Зато в разделе объявлений любого блока могут быть определены локальные подпрограммы.

Однако вызывать локальные подпрограммы можно только из блока, где они определены.

 

Переменные и константы

 

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

Переменные могут принадлежать к любому типу данных SQL, как, например, VARCHAR2, DATE. NUMBER, или к любому типу данных PL/SQL, например BOOLEAN, BINARY и INTEGER. Предположим, что вы хотите объявить переменную с именем part_noдля хранения 4-разрядных целых чисел и переменную с именем in_stockдля хранения булевых значений TRUE или FALSE. Вы можете объявить эти переменные так:

 

part_no NUMBER(4);

in_stock BOOLEAN;

 

Можно также объявлять записи и таблицы PL/SQL, используя составные типы данных RECORD и TABLE.

Значения переменной можно присваивать двумя способами. В первом способе используется оператор присваивания (:=), двоеточие и сразу за ним знак равенства. Переменная помещается слева от оператора, а выражение – справа. Ниже приводятся несколько примеров:

 

tax := price * tax_rate;

bonus := current_salary * 0.10;

raise := TO_NUMBER(SUBSTR('750 raise', 1, 3));

valid := FALSE;

 

Второй способ присваивания значений переменной – это SELECT или FETCH значения из базы данных прямо в нее. В следующем примере ORACLE вычисляет премию в 10%, когда вы делаете выборку оклада служащего:

 

SELECT sal*0.10 INTO bonus FROM emp

WHERE empno=emp_id;

 

Переменную bonusможно затем использовать в других вычислениях или поместить значение в таблицу базы данных.

Объявление константы происходит аналогично объявлению переменной, но здесь вы должны добавить ключевое слово CONSTANT и сразу присвоить значение константе. С этого момента никакие присваивания константе больше не допускаются. В следующем примере объявляется константа с именем minimum_balance:

 

miniraura_balance CONSTANT REAL := 10.00;

Атрибуты

 

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

Атрибут %TYPE представляет тип данных переменной, константы или столбца базы данных. Это, в частности, полезно при объявлении переменной, которая ссылается на столбец базы данных. Предположим, например, что в таблице с именем books есть столбец с именем title. Чтобы объявить переменную с именем my_title, принадлежащую к тому же типу, что и столбец title, вы используете точечную нотацию и атрибут %TYPE следующим образом:

 

my_title books.title%TYPE

 

Объявление ту_titleс помощью %TYPE имеет два преимущества. Первое – не нужно знать точный тип данных title. Второе – если в базе данных меняется определение title (например, увеличивается размер строки символов), то соответственно изменится и тип данных тy_titleпри выполнении программы.

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

Столбцы строки и соответствующие поля записи имеют одинаковые имена и типы данных. В примере, приведенном ниже, объявляется запись с именем dept_rec. Ее поля имеют те же имена и типы данных, что и столбцы таблицы dept.

 

DECLARE

dept_rec dept%ROWTYPE;

. . .

 

Для доступа к полям записи можно использовать точечную нотацию, как показывает следующий пример:

 

my_deptno := dept_rec.deptno;

 

Если объявлен курсор для выборки фамилии, оклада, даты поступления и должности служащих, то можно использовать %ROWTYPE для объявления записи, которая будет содержать ту же информацию, следующим образом:

 

DECLARE

CURSOR c1 IS SELECT ename, sal, hiredate, job FROM emp;

emp_rec c1%ROWTYPE;

 

Когда выполняется оператор FETCH cl INTO emp_rec; значение столбца ename таблицы етр присваивается полю ename записи emp_rec, значение столбца sal присваивается полю sal и т.д. Ниже показан возможный результат (табл. 3.1).

Таблица 3.1

Пример заполнения таблицы

emp_rec
emp_rec.ename	Иван
emp_rec.sal	950.00
emp_rec.hiredate	03.12.2001
emp_rec.job	Клерк

 

 

Управляющие структуры

 

Управляющие структуры – самое важное в PL/SQL расширение по сравнению с SQL. PL/SQL позволяет не только манипулировать данными ORACLE, но и обрабатывать данные, используя операторы условного, циклического и последовательного потока управления, такие как IF-THEN-ELSE, FOR-LOOP, WHILE-LOOP, EXIT-WHEN и GOTO. В совокупности эти операторы могут обработать любую ситуацию.

Часто приходится предпринимать альтернативные действия в зависимости от обстоятельств. Оператор IF-THEN-ELSE позволяет выбирать последовательность выполнения действий в зависимости от условия. Фраза IF проверяет условие; фраза THEN определяет, что делать, если условие истинно; фраза ELSE определяет, что делать, если условие ложно или недействительно.

Рассмотрим приведенную ниже программу, которая обрабатывает банковскую транзакцию. Прежде чем позволить снять $500 со счета 3, она должна удостовериться, что денег на счете достаточно, чтобы покрыть расход. Если денег хватает, программа снимает сумму со счета; в противном случае вносит запись в таблицу для ревизии счетов.

DECLARE

acct_balance NUMBER(11, 2) ;

acct CONSTANT NUMBER(4) := 3;

debit_amt CONSTANT NUMBER(5,2) := 500.00;

BEGIN

SELECT bal INTO acct_balance FROM accounts

WHERE account_id = acct FOR UPDATE OF bal;

IF acct_balance >= debit_amt THEN

UPDATE accounts SET bal = bal - debit_amt

WHERE account_id = acct;

ELSE

INSERT INTO temp VALUES

(acct, acct_balance, 'Insufficient funds');

-- включить номер счета, текущий баланс и сообщение

END IF;

COMMIT;

END;

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

 

Циклы

 

Оператор LOOP позволяет многократно выполнить последовательность операторов. Ключевое слово LOOP должно располагаться перед первым оператором последовательности, а ключевые слова END LOOP – за последним оператором. Следующий пример показывает простейшую форму цикла, который все время повторяет последовательность операторов:

LOOP

-- последовательность операторов

END LOOP;

 

Оператор FOR-LOOP позволяет указать диапазон целых чисел и выполнить последовательность операторов один раз для каждого числа из диапазона. Предположим, например, что вы – производитель заказных автомобилей и что каждый автомобиль имеет серийный номер.

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

 

FOR i IN 1..order_qty LOOP

UPDATE sales SET custno = customer_id

WHERE snum = snum_seq.NEXTVAL; END LOOP;

 

Оператор WHILE-LOOP связывает с последовательностью операторов некоторое условие. Перед каждым повторением цикла условие вычисляется. Если результатом является TRUE, то выполняется последовательность операторов, и управление возвращается к началу цикла. Если же значением условия является FALSE или NULL, цикл обходится, и управление передается на следующий оператор.

В следующем примере ищется ближайший в иерархии подчиненности руководитель служащего с номером 7902, имеющий оклад не менее $4000:

 

DECLARE

salary emp.sal%TYPE;

mgr_num emp.mgr%TYPE;

last_name emp.ename%TYPE;

starting_empno CONSTANT NUMBER(4) := 7902;

BEGIN

SELECT sal, ragr INTO salary, mgr_num FROM emp

WHERE empno = starting_empno;

WHILE salary < 4000 LOOP

SELECT sal, mgr, ename INTO salary, mgr_num, last_name FROM emp

WHERE empno = mgr num;

END LOOP;

INSERT INTO temp VALUES (NULL, salary, last_name);

COMMIT;

END;

 

Оператор EXIT-WHEN позволяет закончить цикл, если дальнейшая обработка нежелательна или невозможна. Когда встречается оператор EXIT, то вычисляется условие во фразе WHEN. Если результатом является TRUE, цикл заканчивается и управление передается на следующий оператор. В следующем примере цикл закончится, когда значение total превысит 25000:

 

LOOP

. . .

total = total + salary;

EXIT WHEN total > 25000;

-- выйти из цикла, если условие истинно

END LOOP;

-- сюда передается управление

 

Оператор GOTO позволяет безусловный переход на метку. Метка – необъявленный идентификатор, заключенный в двойные угловые скобки, – должна стоять перед выполняемым оператором или блоком PL/SQL. При исполнении оператора GOTO управление передается помеченному оператору или блоку, как показано в следующем примере:

 

IF rating > 90 THEN

GOTO calc_raise; -- переход на метку

END IF;

. . .

«calc_raise»

IF job_title = 'SALESMAN' THEN -- управление передается сюда

raise := commission * 0.25;

ELSE

raise := salary * 0.10; END IF;

 

Курсоры

 

Для выполнения команд SQL и хранения обрабатываемой информации ORACLE использует рабочие области, называемые приватными областями SQL. Конструкция PL/SQL, называемая курсором, позволяет поименовать приватную область SQL и получить доступ к хранящейся в ней информации. Существует два вида курсоров: явные и неявные. PL/SQL неявно объявляет курсор для всех команд SQL, манипулирующих данными, включая даже запросы, возвращающие всего одну строку. Для запросов, возвращающих более одной строки, можно объявить явный курсор, чтобы обрабатывать строки каждую по отдельности. Пример:

 

DECLARE

CURSOR cl IS

SELECT empno, ename, job FROM emp WHERE deptno = 20;

 

Множество строк, возвращаемое многострочным запросом, называется активным набором. Его размер определяется числом строк, удовлетворяющих критерию поиска. Как показано на рис. 3.1, явный курсор «указывает» на текущую строку в активном наборе. Это позволяет программе обрабатывать строки каждую в отдельности. Рассмотрим это на примере запроса:

SELECT empno, ename, job FROM emp WHERE deptno = 20;

 

Рис. 3.1 – Активный набор курсора

 

Обработка многострочного запроса в чем-то подобна обработке файла. Например, программа на COBOL открывает файл, обрабатывает записи, потом закрывает файл. Аналогично программа на PL/SQL открывает курсор, обрабатывает строки, полученные в ответ на запрос, затем закрывает курсор. Точно так же, как указатель позиции файла отмечает текущую позицию в открытом файле, курсор отмечает текущую позицию в активном наборе.

Рис. 3.2 – Работа с курсором

 

Как показано на рис. 3.2, для управления курсором используются операторы OPEN, FETCH и CLOSE.

Оператор OPEN исполняет запрос, связанный с курсором, идентифицирует активный набор и устанавливает курсор перед первой строкой. Оператор FETCH выбирает текущую строку и продвигает курсор на следующую. После обработки последней строки оператор CLOSE отключает курсор.

 

Циклы FOR с курсором

 

В большинстве ситуаций, где требуется явный курсор, можно поступать проще, используя цикл FOR с курсором вместо операторов OPEN, FETCH и CLOSE. Цикл FOR с курсором неявно объявляет свой параметр цикла как запись типа %ROWTYPE, открывает курсор, при каждом своем повторении извлекает из активного множества строки со значениями в поля записи и закрывает курсор, когда все строки обработаны. В следующем примере цикл FOR с курсором неявно объявляет етр_rес как запись, принадлежащую к типу c1%ROWTYPE:

 

DECLARE

CURSOR c1 IS

SELECT ename, sal, hiredate, deptno FROM emp;

. . .

BEGIN

. . .

FOR emp_rec IN c1 LOOP

. . .

salary_total := salary_total + emp_rec.sal;

END LOOP;

END;

 

Как показывает пример, для ссылок на отдельные поля используется точечная нотация.

 

Обработка ошибок

 

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

Предопределенные исключения неявно инициируются исполнительной системой. Например, если происходит деление на ноль, то автоматически инициируется предопределенное исключение ZERO-DIVIDE. Определяемые пользователем исключения должны быть инициированы явно при помощи оператора RAISE.

 

DECLARE

salary NUBER(7,2);

commission NUMBER(7,2);

comm_missing EXCEPTION; -- объявить исключение

BEGIN

SELECT sal, comm INTO salary, commission FROM emp

WHERE empno = :emp_id;

IF commission IS NULL THEN

RAISE comm_missing; -- инициировать исключение

ELSE

:bonus := (salary * 0.05) + (commission * 0.15);

END IF;

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

WHEN comm_missing THEN

-- операторы по обработке ошибки

END;

 

Собственные исключения можно определять в разделе объявлений любого блока или подпрограммы на PL/SQL. В исполняемом разделе проверяется условие, требующее специального внимания. Если условие оказалось истинным, выполняется оператор RAISE. В вышеописанном примере вычисляется премия, причитающаяся коммивояжеру. Премия зависит от оклада и суммы продаж (переменная commission). Поэтому, если сумма продаж отсутствует, то инициируется исключение comm_missing.

Здесь подразумевается, что переменные emp_id и bonus объявлены и инициализированы в программе на базовом языке, куда должен быть встроен этот фрагмент на PL/SQL.

Модульность

 

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

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

При вызове эта процедура принимает номер служащего. Она использует номер для выборки суммы продаж служащего из таблицы базы данных и заодно начисляет ему 25% премии. Затем проверяется величина премии. Если премия отсутствует, то инициируется исключение; в противном случае обновляется запись о служащем в платежной ведомости.

 

PROCEDURE award_bonus (emp_id NUMBER) IS

bonus REAL;

comm_missing EXCEPTION;

BEGIN

SELECT comm * 0.25 INTO bonus FROM emp

WHERE empno = emp_id;

IF bonus IS NULL THEN

RAISE comm_missing;

ELSE

UPDATE payroll SET pay = pay + bonus

WHERE empno = emp_id;

END IF;

EXCEPTION

WHEN comm_missing THEN

. . .

END award_bonus;