Типы программ PL/SQL. Хранимые процедуры и функции

В ORACLE различают три типа программ PL/SQL: анонимные блоки, хранимые процедуры и функции. Анонимные блоки – неименованные блоки PL/SQL не хранящиеся в базе данных. В процессе работы приложение посылает такой блок серверу, и после его обработки блок прекращает свое существование.

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

Создание процедур. Процедуры создаются посредством инструкции create procedure:

сreate [ or replace ] procedure имя_процедуры [(аргумент1 [{ in | out | in out }] тип, … аргумент2 [{ in | out | in out }] тип)] { is | as } тело процедуры/

Чтобы изменить текст процедуры, её необходимо удалить и повторно создать её. Во время разработки процедур эта операция повторяется достаточно часто, поэтому ключевые слова or replace позволяют выполнить такую операцию за один раз. Если процедура существует, то она удаляется без всякого предупреждения (в данном случае вызов инструкции drop procedure не требуется), если же не существовала, то она просто создаётся.

Параметры in, out и in out используются как при создании процедур, так и функций. Смысл параметра in в следующем: Значение физического параметра передаётся в функцию. Внутри процедуры формальный параметр рассматривается в качестве константы PL/SQL (параметр только для чтения) и не может быть изменен. Когда процедура завершается и управление программой передаётся в вызывающую среду, фактический параметр не изменяется. При использовании параметра out любое значение, которое имеет фактический параметр при вызове процедуры, игнорируется. Внутри процедуры формальный параметр рассматривается в качестве неинициализированной переменной, т.е содержит null -значение, и можно как записать в него значение, так и считать значение из него. Когда управление передаётся в вызывающую среду, содержание формальной переменной присваивается фактическому параметру. Параметр in out ─ это комбинация параметров in и out. В данном случае формальный параметр рассматривается в качестве инициализированной переменной.

Создание функций. Создание функций отличается от создания процедур практически названием инструкции и наличием оператора return.

сreate [ or replace ] function имя_функции [(аргумент1 [{ in | out | in out }] тип, … аргумент2 [{ in | out | in out }] тип)] return возвращаемый тип{ is | as } тело функции оператор return /

Оператор return имеет общий синтаксис: return выражение. Выражение ─ это возвращаемое значение. Значение выражения преобразуется в тип, указанный в команде return при описании функции, если, конечно, это значение уже не имеет данный тип.

Ниже приведен пример создания простой хранимой функции:

create or replace function get_customer_address (last in varchar2, firsr in varchar2)

return varchar2 is

addr varchar2 (20);

begin

select address into addr

from customers

where lname=last and fname=first;

return addr;

exception

when others then

return NULL

end get_customer_address;

Вызов процедур и функций. Приложение может вызывать процедуру в анонимном блоке PL/SQL. Вызов функций может осуществляться либо в анонимном блоке посредством оператора присваивания, либо в условии when SQL-оператора.

Приведём примеры:

declare

cur_cust_last varchar2 (100);

cur_cust_first varchar2 (100);

cur_cust_addr varchar2 (100);

begin

…

cur_cust_addr:= get_customer_address(cur_cust_last, cur_cust_first);

…

end/

Вызов функции в условии SQL-оператора:

delete from orders

where address= get_customer_address (‘Иванов’,’Иван’);

· Варианты заданий

1. Написать процедуру, повышающую заработную плату сотрудников, обслуживающих максимальное количество объектов аренды.

Создать функцию, подсчитывающую количество значений неопределенно (null) в столбце зарплат сотрудников, возвратить подсчитанное количество.

2. Написать процедуру изменения домашнего адреса клиента по указанному в качестве параметра имени.

Создать функцию, возвращающую адрес отделения с наименьшим количеством сотрудников.

3. Создать процедуру, переносящую информацию о сотрудниках, родившихся не позднее 1 января 1980 года, во вспомогательную таблицу.

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

4. Создать процедуру, выполняющую подсчет количества клиентов, обратившихся в каждый из офисов. Для вывода информации воспользоваться модулем DBMS_OUTPUT для отладки программ. (Описание модулей приводится в теоретических сведениях к лабораторной работе 5.)

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

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

Создать процедуру, обеспечивающую удаление арендатора из таблицы по указанному имени и фамилии.

6. Создать процедуру, обеспечивающую вывод на экран информации обо всех сотрудниках, работающих в городе заданном в качестве аргумента.

Написать функцию, которая определяет, работает ли тот или иной сотрудник на предприятии (путем возвращения TRUE или FALSE), а в параметре out по выходе из функции записывает его телефонный номер

7. Создать процедуру, копирующую строки с результатами осмотров за текущую неделю, во вспомогательную таблицу.

Создать функцию, возвращающую идентификатор отделения, в котором работает максимальное количество сотрудников.

8. Создать процедуру, которая в параметре in out возвращала адрес объекта недвижимости. Фамилия владельца указывается в этом же параметре. Осуществить вызов с целью вывода переменной на экран посредством использования процедур модуля DBMS_OUTPUT.

Создать функцию, подсчитывающую количество осмотров за текущий день. В вызывающую среду возвращать количество осмотров, в параметре out ─ сделанные комментарии.

9. Создать функцию, определяющую действительно ли владелец (параметр 1) является владельцем объекта (параметр 2). Возвращать TRUE или FALSE. В случае FALSE в параметр 1 записывать фамилию истинного владельца данного объекта.

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

10. Создать процедуру, “переводящую” сотрудников заданного отделения в другие отделения этого же города. В каждый из офисов переводить по приблизительно одинаковому, в смысле среднего значения, количеству сотрудников.

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

· Контрольные вопросы

1. Назовите, какие типы программ используются в PL/SQL. Чем они отличаются друг от друга?

2. Как обрабатываются многострочные запросы в программах PL/SQL?

3. Расскажите о структурных особенностях программ PL/SQL.

4. Как производится обработка ошибочных ситуаций в программах PL/SQL?

Поиск по сайту: