Материал: 2110

Использование функций для работы с числами 51. Вывести значение формулы (1), переменные которой нужно опи-

сать и присвоить произвольные значения.

Использование функций для работы с типом ДАТА/ВРЕМЯ 60. Вывести на экран название текущего месяца и текущее время. За-

писать в таблицу Purchases в поле Date_order одинаковую дату поступле- ния, которая равна 12.03.2000.

60

61.Разобрать на отдельные составляющие текущую дату и время и вы- вести значения на экран в следующем порядке (вместо многоточий):

62."Сегодня: День = …, Месяц = …, Год = …, Часов = …, Минут = …, Секунд= …"

63.В исходный текст, сохраненный в переменной Perem, после слова "время" вставить текущее время. Результат сохранить в той же переменной Perem и вывести на экран.

Работа с переменной типа ЗАПИСЬ

64.Создать запись с названием TEMP, аналогичную по структуре с за- писью таблицы Поставщики. Присвоить полям записи данные и добавить в таблицу Поставщики значения записи TEMP.

65.Создать локальную таблицу с названием TEMP, аналогичную по структуре с записью таблицы Издательства. Присвоить полям записи дан- ные и добавить в таблицу Издательства значения записи TEMP.

Лабораторная работа №5

СОЗДАНИЕ ХРАНИМЫХ ПРОЦЕДУР И ФУНКЦИЙ В ORACLE

Цель работы – научиться создавать и использовать хранимые процедуры и функции в схемах Oracle.

Содержание работы:

1.Проработка всех примеров, анализ результатов их выполнения в утилите SQL *Plus Worksheet. Проверка наличия созданных процедур в те- кущей схеме.

2.Проверка всех выполненных примеров в утилите Enterprise

MANAGER Console.

3.Выполнение всех примеров и заданий по ходу лабораторной работы.

4.Выполнение индивидуальных заданий по вариантам.

Пояснения к выполнению работы

Для освоения программирования хранимых процедур используем при- мер базы данных в табличном пространстве DB_Books, которая была соз- дана в лабораторной работе №1. При выполнении примеров и заданий об- ращайте внимание на соответствие названий таблиц и других объектов проекта.

Хранимые процедуры представляют собой набор команд, состоящий из одного или нескольких операторов SQL или функций и сохраняемый в ба- зе данных в откомпилированном виде.

Процедуры и функции - это объекты базы данных и, следовательно, они создаются командой CREATE и уничтожаются командой DROP. При соз-

61

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

Чтобы создать процедуру или функцию, необходимо иметь системные привилегии CREATE PROCEDURE. Для создания процедуры, функции или пакета в схеме, отличной от текущей схемы пользователя, требуются системные привила на CREATE ANY PROCEDURE. После определения имени новой процедуры или функции необходимо задать ее имена, типы и виды параметров. Для каждого параметра должен быть указан один из ви- дов параметра - IN, OUT или IN OUT. Вид параметра IN предполагает, что значение параметра должно быть определено при обращении к процедуре и не изменяется процедурой.

Попытка изменить в теле процедуры параметр вида IN приведет к со- общению об ошибке. Вид параметра OUT предполагает изменение значе- ния параметра в процессе работы процедуры, то есть параметр вида OUT - это возвращаемый параметр. Параметр IN OUT - это параметр, которому при вызове должно быть присвоено значение, которое может быть измене- но в теле процедуры. Параметры процедур или функций имеют виды, при- своенные по умолчанию.

Дополнительно к определениям, необходимым для процедуры, в опре- делении функции должен быть тип данных единственного возвращаемого функцией значения. Возврат значения функции выполняется командой PL/SQL RETURN. Оператор определения процедуры Oracle использует следующий синтаксис:

CREATE [OR REPLACE] PROCEDURE [имя_схемы.]имя_процедуры

[(имя_параметра [{IN | OUT | IN OUT}] тип_данных

[, имя_параметра [{IN | OUT | IN OUT}] тип_данных...] )] {IS | AS}

программа_нa_PL/SQL

Напоминание. При классическом описании синтаксиса { } обозначают обязательный выбор одной из альтернатив, | – разделяет альтернативы, [ ] – указывают на необязательные параметры, которые могут быть опущены.

Ключевое слово OR REPLACE указывает на безусловное замещение старого текста процедуры. Если ключевое слово OR REPLACE не указано, и процедура определена, то замещения старого значения кода процедуры не происходит, и возвращается сообщение об ошибке.

62

В описании переменных процедуры не используется ключевое слово DECLARE. Блок определения данных начинается сразу после ключевого слова AS (или IS – по выбору пользователя).

Напомним, что функции PL/SQL отличаются от процедур тем, что воз- вращают в вызывающую среду значение параметра.

Оператор определения функции Oracle использует следующий синтаксис:

CREATE [OR REPLACE] FUNCTION [имя_схемы.]имя_функции

[(имя_параметра [{IN | OUT | IN OUT}] тип_данных

[, имя_параметра [{IN | OUT | IN OUT}] тип_данных...] )] RETURN тип_данных

{IS | AS}

программа_нa_PL/SQL

Описание типа данных для возвращаемого функцией значения требу- ется обязательно. При описании переменных функции так же, как и при описании переменных процедуры, не используется ключевое слово DECLARE. Блок определения данных начинается сразу после ключевого слова IS (или AS, по выбору пользователя).

Удаление хранимой процедуры или функции

Если характер использования приложения изменился, то для освобож- дения ресурсов базы данных может потребоваться уничтожить процедуру пли функцию. В собственной схеме пользователю не требуются дополни- тельные привилегии для уничтожения процедуры пли функции. Для унич- тожения процедуры или функции в схеме другого пользователя необходи- мо наличие привилегии DROP ANY PROCEDURE.

DROP PROCEDURE [имя_схемы.]имя_процедуры

или

DROP FUNCTION [имя_схемы.]имя_функции

Пример создания процедуры без параметров:

CREATE OR REPLACE PROCEDURE ADMIN_BOOKS.Count_Books IS

perem1 integer; begin

Select count(Code_book) INTO perem1 from ADMIN_BOOKS.Books; end Count_books;

63

Задание 1. Создайте данную процедуру в разделе Schema/ Procedure в

схеме ADMIN_BOOKS через утилиту Enterprise MANAGER Console. За-

пустите ее в утилите SQL *Plus Worksheet с помощью программы: begin

ADMIN_BOOKS.Count_Books;

end;

Проверьте результат.

Пример создания процедуры c входным параметром:

CREATE OR REPLACE PROCEDURE

ADMIN_BOOKS.Count_Books_Pages (Count_pages IN NUMBER)

IS

perem2 integer; begin

Select count(Code_book) INTO perem2 from Books WHERE Pages>=Count_pages;

end Count_Books_Pages;

Задание 2. Создайте данную процедуру в разделе Schema/ Procedure в

схеме ADMIN_BOOKS через утилиту Enterprise MANAGER Console. За-

пустите ее в утилите SQL *Plus Worksheet с помощью программы: begin

ADMIN_BOOKS.Count_Books_Pages(12);

end;

Проверьте результат.

Пример создания процедуры c входными параметрами:

CREATE OR REPLACE PROCEDURE ADMIN_BOOKS.Count_Books_Title (Count_pages IN Int, Title IN Char)

IS

perem3 integer; begin

Select count(Code_book) INTO perem3 from Books WHERE Pages>=Count_pages AND Title_book LIKE trim(Title)||'%';

end Count_Books_Title;

Задание 3. Создайте данную процедуру в разделе Schema/ Procedure в

схеме ADMIN_BOOKS через утилиту Enterprise MANAGER Console. За-

пустите ее в утилите SQL *Plus Worksheet с помощью программы: begin

ADMIN_BOOKS.Count_Books_Title (100, 'П');

64