procedures. В таблице 11.2 перечислены некоторые из столбцов в user_
procedures.
Примечание Информацию о всех процедурах, к которым вы имеете доступ, можно получить
из представления all_procedures.
Знакомство с программированием на PL/SQL 377
Таблица 11.2. Некоторые столбцы в user_procedures
Столбец Тип Описание
OBJECT_V_NAME VARCHAR2(30) Имя объекта, которое может быть именем процедуры,
функции или пакета.
PROCEDURE_V_NAME VARCHAR2(30) Имя процедуры.
AGGREGATE VARCHAR2(3) Указывает, является ли процедура агрегатной функцией
(принимает значения YES или N0).
IMPLTYPEOWNER VARCHAR2(30) Имя владельца типа (если таковой имеется).
IMPLTYPEV_NAME VARCHAR2(30) Имя типа (если таковой имеется).
PARALLEL VARCHAR2(3) Указывает, может ли эта процедура или функция использоваться
в параллельных запросах (устанавливается
в YES или N0).
В следующем примере из таблицы user_procedures извлекаются столбцы
object_name, aggregate и parallel для update_product_price():
□ SELECT object_name, aggregate, parallel
FROM user_procedures - *
WHERE object_name = ‘UPDATE_PRODUCT_PRICE’;
OBJECT_NAME AGG PAR
UPDATE_PRODUCT_PRICE NO NO
Удаление процедуры
Для удаления процедуры используется оператор DROP PROCEDURE. Следующий
оператор удаляет процедуру update_product_price():
□ DROP PROCEDURE u p d a t e _ p r o d u c t _ p r i c e ;
Просмотр ошибок в процедуре
Если во время создания процедуры база данных сообщает об ошибке, можно
увидеть эти ошибки, задав команду SHOW ERRORS. Следующий оператор
CREATE PROCEDURE пытается создать процедуру, в шестой строке которой
имеется синтаксическая ошибка (имя параметра должно быть p_dob, а не
p_dobs):
□ SQL> CREATE PROCEDURE u p d a te _ cu s tome r_ d o b (
2 p _ c u s tom e r _ id INTEGER, p_dob DATE
3 ) AS
4 BEGIN
5 UPDATE c u s tom e r s
6 SET dob = p_dobs
7 WHERE c u s tom e r _ id = p _ cu s tome r_ id ;
8 END upd ate _cus tome r_d o b;
9 /
Wa rning: P r o c e d u r e c r e a t e d w i t h c o m p i l a t i o n e r r o r s
(Предупреждение: Процедура со зд ан а с ошибками компиляции)
378 Глава 11
База данных сообщила об ошибке во время компиляции процедуры.
Чтобы увидеть ошибки, следует использовать команду SHOW ERRORS:
□ SQL> SHOW ERRORS
E r r o r s f o r PROCEDURE UPDATE_CUSTOMER_DOB:
LINE/COL ERROR
5/3 PL/SQL: SQL Statement ignored
6/13 PL/SQL: 0RA-00904: invalid column name
Строка 5 была проигнорирована, поскольку в строке 6 оператора CREATE
PROCEDURE было указано неверное имя столбца. Можно исправить эту ошибку,
если использовать команду EDIT, которая в выражении CREATE PROCEDURE
заменит p_dobs Hap_dob, и перезапустить выражение, введя/.
Функции
Функция во всем подобна процедуре за тем исключением, что функция
должна возвращать некотррое значение. Хранимые процедуры и функции
принято называть общим именем хранимые подпрограммы, так как, в некотором
смысле, это маленькиме программы.
В этом разделе вы узнаете как:
■ Создавать функции.
■ Вызывать функции.
■ Получять информацию о функциях.
■ Удалять функцию.
Создание функции
Для создания функции используется оператор CREATE FUNCTION. Упрощенный
синтаксис оператора CREATE FUNCTION имеет следующий вид:
□ CREATE [OR REPLACE] FUNCTION имя_функции
[(имя_параметра [IN | OUT | IN OUT ] тип [, . . . ] ) ]
RETURN тип
{IS | AS}
BEGIN
тело_функции
END имя_функции;
где
■ OR REPLACE определяет, что функция должна заменить уже существующую
функцию;
■ имя_функции определяет имя функции;
■ имя_параметра определяет имя параметра. Функции можно передать
несколько параметров;
■ IN | OUT | IN OUT указывает режим параметров;
Знакомство с программированием на PL/SQL 379
■ тип определяет тип PL/SQL параметра;
■ тело_функции содержит программный код функции. В отличие от процедуры
тело функции должно возвращать значение указанного во
фразе RETURN типа.
Следующий оператор CREATE FUNCTION создает функцию circle_area(),
возвращающую площадь круга. Радиус круга передается в circle_area() как
параметр p_radius; обратите внимание, что circle_area() возвращает
NUMBER:
П CREATE FUNCTION circle_area (
p_radius IN NUMBER
) RETURN NUMBER AS
v_pi NUMBER := 3.1415926;
v_area NUMBER;
BEGIN
— площадь кр у га равна числу пи, умноженному на радиус в квадрате
v_area := v_pi * P0WER(р_radius, 2);
RETURN v_area;
END circle_area;
/
В следующем примере создается функция по имени average_product_
p r ic e(), возвращающая среднюю цену товаров, значения столбца product_
type_id которых равны значению переданного параметра:
□ CREATE FUNCTION average_product_v_price (
p_product_type_id IN INTEGER
) RETURN NUMBER AS
v_average_product_price NUMBER;
BEGIN
SELECT AVG(price)
INTO v_average_product_price
FROM products
WHERE product_type_id = p_product_type_id;
RETURN v_average_product_price;
END average_product_price;
/