Лабораторная работа №2 (4 часа)

Лабораторная работа №2 (4 часа)

Цель работы: научиться создавать функции, процедуры, пакеты средствами PL/SQL, научиться использовать явные курсоры.

Порядок выполнения работы.

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

Задание.

Написать и выполнить 10 сложных запросов к своей БД с использованием встроенных функций (5 из них должны использовать функции для работы с датами)

Создать процедуру, содержащую явный курсор

Создать функцию на какую-либо из задач бизнес-логики.

Объединить процедуру и функцию в пакет.

Написать скрипт для вызова процедуры и функции.

Все это должно быть выполнено ТОЛЬКО средствами PL/SQL.

Для ввода команд SQL выберите в меню пункт SQL, а затем SQL Command. Здесь вы можете вводить команды и запускать их на выполнение.

Для просмотра полученных результатов и связей выберите пункт меню Object Browser.

PL/SQL

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

Cтруктура блоков

PL/SQL - это язык, структурированный блоками. Это значит, что основные единицы (процедуры, функции и анонимные блоки), составляющие программу PL/SQL, являются логическими БЛОКАМИ, которые могут содержать любое число вложенных в них подблоков.

Структура блока имеет вид:

[ DECLARE

-- описание переменных, констант и пользовательских типов данных]

BEGIN

-- операторы SQL

-- управляющие операторы PL/SQL

[ EXCEPTION

-- действия, выполняемые при возникновении ошибки]

END;

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

При возникновении вопросов смотреть главу 1 первого источника.

Типы данных, переменные, константы и выражения

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

Символьный и числовой типы данных имеют подтипы, накладывающие ограничения на основной тип.

Тип данных Описание

BINARY_INTEGER Этот тип данных и его подтипы NATURAL и POSITIVE применяются для создания переменных и констант, которые хранят число со знаком.

NUMBER Основной тип для чисел с фиксированной и плавающей запятой.

CHAR Основной тип для символьных данных фиксированной длины.

VARCHAR2 Основной тип для символьных данных переменной длины.

BOOLEAN Основной тип для хранения одного из трех возможных значений, используемых при логических вычислениях: TRUE, FALSE и NULL.

DATE Основной тип для значений даты и времени.

RECORD Используется для создания пользовательских типов записей базы данных.

TABLE Служит для создания табличных типов данных PL/SQL.

col%TYPE Используется для определения типа данных столбца или переменной по типу данных другого столбца или переменной, к имени которого или которой (col) приписан суффикс %TYPE.

tab%ROWTYPE Используется для определения типа данных записи по типу данных столбцов таблицы, к имени которой (tab) приписан суффикс %ROWTYPE.

Примеры:

birthday DATE;

begin_date DATE: = SYSDATE;

pi CONSTANT REAL: = 3. 14159;

area REAL: = pi * radius**2;

valid_id NOT NULL BOOLEAN;

i, j, k NUMBER; -- нельзя описывать список; надо:

-- i NUMBER; j NUMBER; k NUMBER;

credit REAL(7, 2);

debit credit%TYPE; -- тип данных аналогичный типу данных " credit"

Составные типы данных.

Таблица PL/SQL - это одномерный массив с неограниченным числом строк. Для объявления этого массива (таблицы PL/SQL или TABLE) необходимо сначала определить его тип данных.

Для описания типа данных TABLE используется синтаксис:

TYPE type_name IS TABLE OF { column_type | variable%TYPE |

table. column%TYPE } [NOT NULL] INDEX BY BINARY_INTEGER;

где " type_name" - спецификатор типа, используемый в последующих объявлениях таблиц PL/SQL, и " column_type" - любой из скалярных типов данных: CHAR, DATE или NUMBER. С помощью атрибута %TYPE можно установить " type_name" соответствующим типу данных какой-либо переменной (variable) или столбца (table. column).

Пример:

Объявление переменных типа TABLE для хранения фамилий.

DECLARE

TYPE last_name_type IS TABLE OF VARCHAR2(25)

INDEX BY BINARY INTEGER;

Surname last_name_type;

Для ссылки на третью строку этой таблицы следует написать: surname(3).

Для присвоения значения конкретной строке таблицы PL/SQL используется

синтаксис:

surname(5): = ‘Петрова’;

Запись PL/SQL - это совокупность полей, каждое из которых должно иметь уникальное имя (в пределах записи). Эти поля могут принадлежать различнымтипам данных.

Если создаваемая запись (sotr) соответствует описанию столбцов какой-либо базовой таблицы (например, kadry), то ее объявление можно осуществить в разделе DECLARE с помощью атрибута %ROWTYPE:

sotr kadry%ROWTYPE;

В противном случае для объявления записи необходимо сначала определить ее тип данных. Для описания типа данных RECORD используется синтаксис:

TYPE type_name IS RECORD

( field_name1 {field_type | variable%TYPE | table. column%TYPE

| table%ROWTYPE} [NOT NULL],

field_name2 {field_type | variable%TYPE | table. column%TYPE

| table%ROWTYPE} [NOT NULL],

... );

где " type_name" - спецификатор типа, используемый в последующих объявлениях записей PL/SQL, и " field_type" - любой тип данных. С помощью атрибута %TYPE можно установить " type_name" соответствующим типу данных какой-либо переменной (variable) или столбца (table. column). Атрибут %ROWTYPE позволяет определить поле как запись, соответствующую описанию столбцов какой-либо базовой таблицы.

При объявлении типа записи можно присвоить ее полям некоторые значения. Если же для поля вводится ограничение NOT NULL (для предотвращения назначения пустых значений), то этому полю надо обязательно присвоить значение.

Пример:

Объявление переменных для хранения фамилии, имени и номера отдела нового служащего.

DECLARE

TYPE emp_rec_type IS RECORD

(surname VARCHAR2(25),

name VARCHAR2(25),

otdel NUMBER(3): = 102);

employee_rec emp_rec_type;

Ссылки на отдельные поля записи осуществляются так:

employee_rec. name;

Для присвоения значения конкретному полю записи используется синтаксис:

employee_rec. name: = v_name;

Комментарии к коду.

Для документирования и упрощения отладки можно написать комментарий к фрагменту кода. Однострочный комментарий начинается двумя дефисами (--), а если он занимает несколько строк, то заключается в символы /* и */.

Вложенные блоки.

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

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

…

x NUMBER;

BEGIN область видимости x

…

DECLARE

Y NUMBER;

BEGIN область видимости y

…

END;

…

END;

Операторы управления выполнением программы.

Оператор условного перехода IF.

По структуре оператор IF PL/SQL сходен с аналогичными операторами в других языках.

Синтаксис:

IF условие THEN операторы;

[ELSIF условие THEN операторы; ]

[ELSE] операторы; ]

END IF;

Количество предложений ELSIF не ограничено. Предложение ELSE может быть только одно.

Пример:

IF sales > 50000 THEN

bonus: = 1500;

ELSIF sales > 35000 THEN

bonus: = 500;

ELSE

bonus: = 100;

END IF;

INSERT INTO payroll VALUES (emp_id, bonus, ... );

Циклы.

Простой цикл.

Синтаксис:

LOOP

оператор 1;

. . .

EXIT [WHEN условие];

END LOOP;

Простой цикл бесконечен. Завершить цикл можно оператором EXIT.

Цикл FOR.

Синтаксис:

FOR индекс IN [REVERSE] нижняя_граница.. верхняя граница LOOP

оператор 1;

. . .

END LOOP;

индекс – неявно описанная целая переменная, значение которой автоматически увеличивается или уменьшается на 1 при каждом выполнении цикла, пока не будет достигнута верхняя граница. Объявлять индекс не нужно!!!

REVERSE – вызывает уменьшение индекса

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

FOR ctr IN 1.. 10 LOOP

...

IF NOT finished THEN

INSERT INTO... VALUES (ctr, ... ); -- законно

factor: = ctr * 2; -- законно

...

ELSE

ctr: = 10; -- незаконно

END IF;

END LOOP;

Цикл WHILE.

Цикл WHILE используется для повторения последовательности операторов в течение всего времени, пока значение условия равно TRUE.

Синтаксис:

WHILE условие LOOP

оператор 1;

. . .

END LOOP;

Пример:

WHILE total < = 25000 LOOP

...

SELECT sal INTO salary FROM emp WHERE...

total: = total + salary;

END LOOP;

Циклы могут быть вложены один в другой на несколько уровней.

При возникновении вопросов смотреть главы 1, 2, 3 первого источника.

Управление курсорами

Явные курсоры

Множество строк, возвращаемых запросом (активное множество), может состоять из нуля, одной или нескольких строк, в зависимости от того, сколько строк удовлетворяют вашим поисковым условиям. Когда запрос возвращает несколько строк, вы можете явно определить курсор для обработки этих строк.

Вы определяете курсор в декларативной части блока PL/SQL, подпрограммы или пакета путем задания его имени и специфицирования запроса. После этого вы манипулируете курсором при помощи трех команд: OPEN, FETCH и CLOSE.

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

Вы можете обрабатывать параллельно несколько запросов, объявив и открыв несколько курсоров.

Объявление курсора

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

DECLARE

CURSOR c1 IS SELECT ename, deptno FROM emp

WHERE sal > 2000;

...

BEGIN

...

Имя курсора - это необъявленный ранее идентификатор, а не переменная PL/SQL; его можно использовать только для обращения к запросу. Вы не можете присваивать значений имени курсора или использовать его в выражениях.

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

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

CURSOR c1 (median IN NUMBER) IS

SELECT job, ename FROM emp WHER sal > median;

Для объявления формальных параметров курсора используется синтаксис

CURSOR имя [ (параметр [, параметр, ... ]) ] IS

SELECT...

где " параметр", в свою очередь, имеет следующий синтаксис:

имя_переменной [IN] тип_данных [{: = | DEFAULT} значение]

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

DECLARE

CURSOR c1

(low INTEGER DEFAULT 0,

high INTEGER DEFAULT 99) IS SELECT...

...

Сфера параметров курсора локальна в этом курсоре, что означает, что к этим параметрам можно обращаться лишь в запросе, который участвует в объявлении курсора. Значения параметров курсора используются ассоциированным запросом в момент открытия курсора.

Открытие курсора

Открытие курсора предложением OPEN исполняет предложение SELECT и идентифицирует АКТИВНОЕ МНОЖЕСТВО, т. е. все строки, удовлетворяющие поисковым условиям запроса. Для курсоров, объявленных с фразой FOR UPDATE, предложение OPEN также осуществляет блокировку этих строк. Пример предложения OPEN:

OPEN c1;

Предложение OPEN не извлекает строк активного множества. Для этого используется предложение FETCH.

Передача параметров

Курсору могут быть переданы параметры при открытии. Например, при объявлении курсора

CURSOR c1 (my_ename CHAR, my_comm NUMBER) IS SELECT...

любое из следующих предложений открывает этот курсор:

OPEN c1('ATTLEY', 300);

OPEN c1(employee_name, 150);

OPEN c1('THURSTON', my_comm);

В последнем примере переменная, специфицированная в предложении OPEN, имеет такое же имя, что и параметр в объявлении курсора.

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

Если вы не хотите принять умалчиваемые значения, каждому формальному параметру в объявлении курсора следует сопоставить соответствующий фактический параметр в предложении OPEN.

Формальные параметры, объявленные с умалчиваемым значением, могут и не иметь соответствующих им фактических параметров. В этом случае они просто принимают свое умалчиваемое значение во время выполнения OPEN. Не забывайте, что формальные параметры курсора должны быть параметрами IN, так что они не могут возвращать значений фактическим параметрам.

Извлечение данных из курсора

Предложение FETCH извлекает очередную строку из активного множества. При каждом выполнении FETCH курсор продвигается к следующей строке в активном множестве. Пример предложения FETCH:

FETCH c1 INTO my_empno, my_ename, my_deptno;

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

...

OPEN c1;

LOOP

FETCH c1 INTO my_record;

EXIT WHEN c1%NOTFOUND;

-- обработать извлеченные данные

END LOOP;

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

DECLARE

my_sal emp. sal%TYPE;

my_job emp. job%TYPE;

factor INTEGER: = 2;

CURSOR c1 IS

SELECT factor*sal FROM emp WHERE job = my_job;

BEGIN

...

OPEN c1; -- здесь factor равен 2

LOOP

FETCH c1 INTO my_sal;

EXIT WHEN c1%NOTFOUND;

...

factor: = factor + 1; -- не окажет влияния на FETCH

END LOOP;

END;

В этом примере каждое извлекаемое значение sal будет умножаться на 2, несмотря на то, что значение factor увеличивается после каждой операции FETCH. Чтобы изменить активное множество или значения переменных в запросе, вы должны закрыть и заново открыть курсор с новыми значениями входных параметров.

Однако для каждой операции FETCH на одном и том же курсоре вы можете использовать собственный список INTO. Каждая FETCH извлекает строку и присваивает значения своим переменным INTO, как показывает следующий пример:

DECLARE

CURSOR c1 IS SELECT ename FROM emp;

name1 emp. ename%TYPE;

name2 emp. ename%TYPE;

name3 emp. ename%TYPE;

BEGIN

OPEN c1;

FETCH c1 INTO name1; -- извлекает первую строку

FETCH c1 INTO name2; -- извлекает вторую строку

FETCH c1 INTO name3; -- извлекает третью строку

...

CLOSE c1;

END;

Если вы выдаете FETCH, но в активном множестве больше нет строк, то значения переменных в списке INTO не определены.

Закрытие курсора

Предложение CLOSE деактивирует курсор, и активное множество становится неопределенным. Пример предложения CLOSE:

CLOSE c1;

После того, как курсор закрыт, вы можете снова открыть его. Любая иная операция на закрытом курсоре возбуждает предопределенное исключение INVALID_CURSOR, которое соответствует ошибке ORACLE с кодом ORA-01001.

Атрибуты явного курсора

Каждый курсор, явно объявленный вами, имеет четыре атрибута: %NOTFOUND, %FOUND, %ROWCOUNT и %ISOPEN. Атрибуты позволяют вам получать полезную информацию о выполнении многострочного запроса. Для обращения к атрибуту просто присоедините его имя к имени курсора. Атрибуты явного курсора можно использовать в процедурных предложениях, но не в предложениях SQL.

Использование %NOTFOUND

Когда курсор открыт, строки, удовлетворяющие ассоциированному запросу, идентифицированы и образуют активное множество. Эти строки извлекаются операцией FETCH по одной за раз. Если последняя операция FETCH вернула строку, %NOTFOUND дает FALSE.

Если последняя операция FETCH не смогла вернуть строку (так как активное множество исчерпано), %NOTFOUND дает TRUE. Операция FETCH должна в конце концов исчерпать активное множество, так что, когда это происходит, никакого исключения не возбуждается.

В следующем примере вы используете %NOTFOUND, чтобы выйти из цикла, когда FETCH не сможет вернуть строку:

LOOP

FETCH c1 INTO my_ename, my_deptno;

EXIT WHEN c1%NOTFOUND;

...

END LOOP;

Перед первой операцией FETCH атрибут %NOTFOUND дает NULL. Поэтому, если FETCH ни разу не выполнится успешно, вы никогда не выйдете из этого цикла. Причина в том, что предложение EXIT WHEN выполняется только в том случае, когда условие WHEN дает TRUE. Поэтому для безопасности вы можете предпочесть такой вариант предложения EXIT:

EXIT WHEN c1%NOTFOUND OR c1%NOTFOUND IS NULL;

Вы можете открыть несколько курсоров, а затем использовать %NOTFOUND, чтобы проверять, в каких курсорах еще есть строки. Если курсор не открыт, обращение к нему через %NOTFOUND возбуждает предопределенное исключение INVALID_CURSOR.

Использование %FOUND

%FOUND логически противоположен атрибуту %NOTFOUND. После открытия явного курсора, но до первой операции FETCH, %FOUND дает NULL. Впоследствии он дает TRUE, если последняя операция FETCH вернула строку, или FALSE, если последняя операция FETCH не смогла извлечь строку, так как больше нет доступных строк.

Следующий пример использует %FOUND, чтобы выбрать одно из двух альтернативных действий:

LOOP

FETCH c1 INTO my_ename, my_deptno;

IF c1%FOUND THEN -- извлечение успешно

INSERT INTO temp VALUES (... );

ELSE

EXIT;

...

END LOOP;

Вы можете открыть несколько курсоров, а затем использовать %FOUND, чтобы проверять, в каких курсорах еще есть строки. Если курсор не открыт, обращение к нему через %FOUND возбуждает предопределенное исключение INVALID_CURSOR.

Использование %ROWCOUNT

Когда вы открываете курсор, его атрибут %ROWCOUNT обнуляется. Перед первой операцией FETCH %ROWCOUNT возвращает 0. Впоследствии, ROWCOUNT возвращает число строк, извлеченных операциями FETCH из активного множества на данный момент. Это число увеличивается, если последняя FETCH вернула строку.

Следующий пример использует %ROWCOUNT, чтобы предпринять определенные действия, если выбрано более 10 строк:

LOOP

FETCH c1 INTO my_ename, my_deptno;

IF c1%ROWCOUNT > 10 THEN

-- выбрано больше 10 строк

...

END IF;

END LOOP;

Вы можете открыть несколько курсоров, а затем использовать %ROWCOUNT, чтобы проверять, сколько строк извлечено из каждого курсора. Если курсор не открыт, обращение к нему через %ROWCOUNT возбуждает предопределенное исключение INVALID_CURSOR.

Использование %ISOPEN

%ISOPEN дает TRUE, если явный курсор открыт, и FALSE в противном случае. Следующий пример использует %ISOPEN для выбора действия:

IF с1%ISOPEN THEN -- курсор открыт

...

ELSE -- курсор закрыт, открыть его

OPEN c1;

END IF;

Курсорные циклы FOR

Курсорный цикл FOR неявно объявляет свой индекс цикла как запись типа %ROWTYPE, открывает курсор, в цикле извлекает строки из активного множества в поля записи, и закрывает курсор, когда все строки обработаны или когда вы выходите из цикла.

В большинстве ситуаций, которые требуют явного курсора, текст программы может быть упрощен при использовании " курсора в цикле FOR", заменяющего команды OPEN,

FETCH и CLOSE. Курсор в цикле FOR:

- неявно объявляет индекс цикла записью, поля которой соответствуют столбцам (псевдонимам) предложения SELECT... из описания курсора;

- передает параметры курсора (если они есть) и открывает курсор;

- выбирает в цикле строки из полученного набора в индекс цикла (поля записи);

- закрывает курсор после обработки всех строк набора или досрочному выходу из него с помощью команд EXIT или GOTO.

Синтаксис курсора в цикле FOR имеет вид:

FOR var_rec_name IN cursor_name [ (value [, value]... ) ] LOOP

ТЕЛО ЦИКЛА

END LOOP;

где - var_rec_name индекс цикла, в котором при первом прохождении цикла хранится первая строка набора, при втором прохождении цикла - вторая строка и т. д.;

- список значений (" value" ) используется для передачи параметров курсора (он заменяет в данном случае список из команды OPEN);

- ТЕЛО ЦИКЛА содержит нужные строки повторяющейся части программы, в которых

используются переменные с именами var_rec_name. column_name, а column_name имя столбца из перечня столбцов предложения SELECT в описании курсора.

Рассмотрим следующий блок PL/SQL, который анализирует данные, собранные в ходе лабораторных экспериментов, и помещает результаты во временную таблицу. Переменная c1rec, используемая как индекс в курсорном цикле FOR, неявно объявляется как запись, хранящая все элементы данных, возвращаемые одной операцией FETCH для курсора c1. Вы обращаетесь к элементам данных, хранящимся в полях записи, используя квалифицированные ссылки.

DECLARE

result temp. col1%TYPE;

CURSOR c1 IS

SELECT n1, n2, n3 FROM data_table

WHERE exper_num = 1;

BEGIN

FOR c1rec IN c1 LOOP

/* вычислить и сохранить результаты */

result: = c1rec. n2 / (c1rec. n1 + c1rec. n3);

INSERT INTO temp VALUES (result, NULL, NULL);

END LOOP;

COMMIT;

END;

То же самое без использования цикла FOR:

DECLARE

num1 data_table. n1%TYPE; -- Объявить переменные

num2 data_table. n2%TYPE; -- с теми же типами,

num3 data_table. n3%TYPE; -- что и столбцы таблицы

result temp. col1%TYPE;

CURSOR c1 IS

SELECT n1, n2, n3 FROM data_table

WHERE exper_num = 1;

BEGIN

OPEN c1;

LOOP

FETCH c1 INTO num1, num2, num3;

EXIT WHEN c1%NOTFOUND;

-- условие c1%NOTFOUND будет равно TRUE,

-- когда FETCH не обнаружит больше строк

/* вычислить и сохранить результаты */

result: = num2/(num1 + num3);

INSERT INTO temp VALUES (result, NULL, NULL);

END LOOP;

CLOSE c1;

COMMIT;

END;

Процедуры

Процедура - это подпрограмма, которая выполняет специфическое действие. Синтаксис создания процедуры

PROCEDURE имя [ (параметр [, параметр, ... ]) ] IS

[локальные объявления]

BEGIN

исполняемые предложения

[ EXCEPTION

обработчики исключений]

END [имя];

где каждый " параметр" имеет следующий синтаксис:

имя_перем [IN | OUT | IN OUT] тип_данных [{: = | DEFAULT} знач]

Каждый параметр сопровождается режимом и типом. В качестве режима можно задавать: IN (только чтение), OUT (только запись) и IN OUT (чтение и запись).

Замечание: в описании типа нельзя задавать длину. Например: CHAR(10) или VARCHAR(20) – недопустимые описания, а CHAR или VARCHAR – допустимые. Количество символов (длина) будет определена при вызове процедуры по фактическому параметру (аргументу) функции.

Процедура начинается с ключевых слов CREATE PROCEDURE, за которыми следует ее имя и список параметров. В качестве ключевого слова (описателя) вместо CREATE может использоваться OR REPLACE. Преимущество использования этого ключевого слова в том, что если процедура с каким-то именем уже определена, то новое определение с тем же именем не вызовет ошибки. С другой стороны, предыдущее определение процедуры с аналогичным именем заменится новым определением, и старая процедура перестанет существовать.

Процедура имеет две части: спецификацию и тело. Спецификация процедуры начинается с ключевого слова PROCEDURE и заканчивается именем процедуры или списком параметров. Объявления параметров необязательны. Если процедура не принимает параметров, скобки также не кодируются.

Тело процедуры начинается с ключевого слова IS и заканчивается ключевым словом END, за которым может следовать имя процедуры.

Тело процедуры состоит из трех частей: декларативной части, исполняемой части и необязательной части обработки исключений.

Пример процедура raise_salary, которая увеличивает жалованье сотрудника:

CREATE OR REPLACE PROCEDURE raise_salary (emp_id NUMBER, increase REAL) IS

current_salary REAL;

salary_missing EXCEPTION;

BEGIN

SELECT sal INTO current_salary FROM emp

WHERE empno = emp_id;

IF current_salary IS NULL THEN

RAISE salary_missing;

ELSE

UPDATE emp SET sal = sal + increase

WHERE empno = emp_id;

END IF;

EXCEPTION

WHEN NO_DATA_FOUND THEN

INSERT INTO emp_audit VALUES (emp_id, 'No such number');

WHEN salary_missing THEN

INSERT INTO emp_audit VALUES (emp_id, 'Salary is null');

END raise_salary;

Функции

Функция - это подпрограмма, которая вычисляет значение. Функции структурируются так же, как и процедуры, с той разницей, что функции содержат фразу RETURN. Синтаксис создания функции

FUNCTION имя [ (аргумент [, аргумент, ... ]) ] RETURN тип_данн IS

[локальные объявления]

BEGIN

исполняемые предложения

[EXCEPTION

обработчики исключений]

END [имя];

где каждый " аргумент" имеет следующий синтаксис:

имя_перем [IN | OUT | IN OUT] тип_данных [{: = | DEFAULT} знач]

В исполняемой части процедуры должно встретиться хотя бы одно предложение RETURN.

Функция начинается с ключевых слов CREATE FUNCTION, за которыми следует ее имя и список параметров. В качестве ключевого слова (описателя) вместо CREATE может использоваться OR REPLACE. Преимущество использования этого ключевого слова в том, что если функция с каким-то именем уже определена, то новое определение с тем же именем не вызовет ошибки. С другой стороны, предыдущее определение процедуры с аналогичным именем заменится новым определением, и старая процедура перестанет существовать.

Пример:

функция balance возвращает баланс заданного бухгалтерского счета:

CREATE OR REPLACE FUNCTION balance (acct_id NUMBER)

RETURN REAL IS

acct_bal REAL;

BEGIN

SELECT bal INTO acct_bal FROM accts WHERE acctno = acct_id;

RETURN acct_bal;

END balance;

Пакеты

ПАКЕТЫ дают метод инкапсулирования и хранения взаимосвязанных процедур, функций и других конструктов пакета как единицы в базе данных. Предоставляя администратору базы данных или разработчику приложений организационные преимущества, пакеты в то же время расширяют функциональные возможности и увеличивают производительность базы данных.

Пакеты обычно состоят из двух частей, спецификации и тела, хотя иногда в теле нет необходимости. СПЕЦИФИКАЦИЯ пакета – это интерфейс с вашими приложениями; она объявляет типы, переменные, константы, исключения, курсоры и подпрограммы, доступные для использования в пакете. ТЕЛО пакета полностью определяет курсоры и подпрограммы, тем самым реализуя спецификацию пакета.

Синтаксис объявления пакетов

PACKAGE имя IS -- спецификация (видимая часть)

-- объявления общих типов и объектов

-- спецификации подпрограмм

END [имя];

PACKAGE BODY имя IS -- тело (скрытая часть)

-- объявления личных типов и объектов

-- тела подпрограмм

[BEGIN

-- предложения инициализации]

END [имя];

Спецификация содержит ОБЩИЕ объявления, которые видимы вашему приложению. Тело содержит детали реализации и ЛИЧНЫЕ объявления, которые скрыты от вашего приложения. Спецификация перечисляет ресурсы пакета, доступные приложениям. Она содержит всю информацию, необходимую вашему приложению для использования этих ресурсов.

Упреждающие объявления позволяют группировать логически связанные подпрограммы в пакет. Спецификации подпрограмм идут в спецификацию пакета, а тела подпрограмм - в тело пакета, где они невидимы приложениям. Таким образом, благодаря пакетам можно скрыть детали реализации.

Только объявления в спецификации пакета являются видимыми и доступными приложениям. Детали реализации в теле пакета остаются скрытыми и недоступными.

Если у вас есть Процедурное расширение базы данных, то пакеты можно компилировать и сохранять в базе данных ORACLE, где их содержимое может совместно использоваться многими приложениями. Когда вы первый раз обращаетесь к пакетированной подпрограмме, в память загружается весь пакет. Поэтому последующие обращения к другим подпрограммам пакета не требуют дисковых операций. Таким образом, пакеты могут повысить продуктивность разработки и улучшить производительность выполнения.

Для обращения к типам, объектам и подпрограммам, объявленным в спецификации пакета, используются квалифицированные ссылки:

имя_пакета. имя_типа

имя_пакета. имя_объекта

имя_пакета. имя_подпрограммы

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

Часть инициализации пакета не играет большой роли, потому что, в отличие от подпрограмм, пакет нельзя вызывать или передавать ему параметры. Следовательно, часть инициализации пакета отрабатывает лишь один раз, при первом обращении к пакету.

Пример создания и использования процедуры и курсора

Пример 1:

В следующем примере вы просматриваете все партии определенного товара с номером goods_num, хранящихся на определенном складе с номером ware_num, и забираете их, пока не наберете необходимый для обслуживания оптового покупателя объем в need_count единиц. Факт оптовой торговой операции необходимо зафиксировать в таблице opt_operation (operation_id, date_operation, ware_num, goods_num, need_count);

CREATE SEQUENCE oper;

CREATE TABLE opt_operation (

operation_id NUMBER(3) NOT NULL PRIMARY KEY,

date_operation VARCHAR2(20),

ware_num NUMBER(3),

goods_num NUMBER(4),

need_count NUMBER(5));

CREATE OR REPLACE PROCEDURE opt_buy (goods_num IN NUMBER, ware_num IN NUMBER, need_count IN NUMBER) AS

CURSOR stor_goods (goods_num1 NUMBER, ware_num1 NUMBER) IS

SELECT quantity FROM storages WHERE

ware_id= ware_num1 AND goods_id = goods_num1 AND quantity > 0

ORDER BY end_time, quantity

FOR UPDATE OF quantity;

goods_qua storages. quantity%TYPE; -- переменная для текущего количества товара

total_goods NUMBER(5): = 0; --переменная для накопления количества товаров

stor_looked NUMBER(3): = 0;

/* переменная для подсчета количества затронутых партий товаров*/

date_oper VARCHAR2(20); -- переменная для формирования даты операции

volume_goods NUMBER(6, 2); --переменная для получения объема единицы товара

BEGIN

OPEN stor_goods(goods_num, ware_num);

WHILE total_goods < need_count LOOP

FETCH stor_goods INTO goods_qua;

EXIT WHEN stor_goods%NOTFOUND;

/*увеличиваем счетчик затронутых партий*/

stor_looked: = stor_looked + 1;

IF total_goods + goods_qua < need_count THEN

--полностью забираем всю партию товара

UPDATE storages SET quantity = 0 WHERE CURRENT OF stor_goods;

total_goods: = total_goods + goods_qua;

ELSE -- полной партии товара будет больше, чем необходимо

UPDATE storages SET quantity = quantity-(need_count-total_goods)

WHERE CURRENT OF stor_goods;

total_goods: = need_count;

END IF;

END LOOP;

CLOSE stor_goods;

date_oper: =TO_CHAR(SYSDATE, ’DD: MM: YY/HH: MI’);

INSERT INTO opt_operation VALUES (oper. NEXTVAL, date_oper, ware_num, goods_num, need_count);

SELECT volume INTO volume_goods FROM goods WHERE goods_id= goods_num;

UPDATE Warehouses SET Volume_rest= Volume_rest+ volume_goods* need_count WHERE

ware_id= ware_num;

-- увеличиваем объем склада, с которого забраны товары

COMMIT;

END;

Вызов процедуры может быть следующим:

Begin

opt_buy (4, 1, 35);

end;

Требования к отчету.

Отчет по лабораторной работе должен быть представлен в электронном виде и содержать:

1) Цель работы

2) Скрипт процедуры с курсором, функции, пакета, скрипт для их запуска со скриншотами результатов.

3) 10 сложных запросов с применением встроенных функций со скриншотами результатов.

В ходе защиты лабораторной работы необходимо продемонстрировать умения создавать процедуры, функции, пакеты, курсоры, а также создавать запросы со встроенными функциями ORACLE.