Функции в PL/SQL

Какую проблему помогают решить функции

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

Пример создания простой функции

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

Вот так может выглядеть код для вычисления нашей скидки:

declare
    -- Сумма скидки
    l_discount number;
    -- Стоимость товара
    l_price number := 350;
begin
    l_discount := round(l_price * (10 / 100), 2);
    
    dbms_output.put_line(l_discount);
end;
/

В результате на экран выведется размер скидки - 35.

Мы посчитали размер скидки по одному товару. А что, если нам нужно посчитать скидку по трем товарам в отдельности? Пишем код:

declare
    l_discount_1 number;
    l_discount_2 number;
    l_discount_3 number;
    l_price_1 number := 350;
    l_price_2 number := 100;
    l_price_3 number := 25;
begin
    l_discount_1 := round(l_price_1 * (10 / 100), 2);
    l_discount_2 := round(l_price_2 * (10 / 100), 2);
    l_discount_3 := round(l_price_3 * (10 / 100), 2);

    dbms_output.put_line(l_discount_1);
    dbms_output.put_line(l_discount_2);
    dbms_output.put_line(l_discount_3);
end;
/

Вывод:

35
10
2.5

Может показаться, что все выглядит вполне себе хорошо - необходимая логика реализована, в чем проблема?

Представим, что в какой-то момент начальство говорит вам о том, что размер скидки должен округляться до одного знака после запятой, а не до двух? Нам придется изменить это в трех местах. То же относится и к размеру скидки - что, если в определенный момент времени скидка станет не 10, а 20 или 30 процентов?

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

create function getDiscount(
    pprice number
) return number
is
begin
    return round(pprice * (10 / 100), 2);
end;
/

Если запустить код выше, то в текущей схеме БД станет доступна функция getDiscount. Используем ее, чтобы модифицировать предыдущий пример:

declare
    l_discount_1 number;
    l_discount_2 number;
    l_discount_3 number;
    l_price_1 number := 350;
    l_price_2 number := 100;
    l_price_3 number := 25;
begin
    l_discount_1 := getDiscount(l_price_1);
    l_discount_2 := getDiscount(l_price_2);
    l_discount_3 := getDiscount(l_price_3);

    dbms_output.put_line(l_discount_1);
    dbms_output.put_line(l_discount_2);
    dbms_output.put_line(l_discount_3);
end;
/

Вывод:

35
10
2.5

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

По-умолчанию, при попытке создать функцию с именем, которое уже существует в текущей схеме, Oracle выдаст ошибку. Для того, чтобы этого не возникало, при описании функции используется конструкция create or replace.

Рассмотрим подробнее синтаксис создания функции в PL/SQL.

-- Используем create or replace,
-- чтобы заменить предыдущую функцию
-- с таким же именем на новую
create or replace function funcName(
    param1 number
) return number
is
begin
    return 1;
end;
/

Основные элементы которые нужно указать при создани функции это:

Имя функции
Параметры(могут отсутствовать)
Тип возвращаемого значения
Тело функции
Команда Return

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

Выше мы создали функцию с именем funcName, которая принимает один параметр с типом number и возвращает значение типа number( в нашем примере это всегда число 1).

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

create function funcName(
    p1 number,
    p2 varchar2
) return boolean
is
begin
    return false;
end;  
/

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

create function funcName() return bool
is
begin
    return false;
    -- Функция всегда будет возвращать False,
    -- следующий return никогда не будет выполнен
    return true;
end;
/

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

Следующее описание функции вызовет ошибку:

create function funcName(
    p1 number(10,2),
    p2 varchar2(100 char)
) return boolean
is
begin
    return false;
end;
/

Функции могут быть и без аргументов:

-- Возвращает процент скидки
create function discValue() return number is
begin
    return 10;
end;
/

Функции с ошибками все равно создаются

Если мы попытаемся создать функцию с ошибкой, то она все равно создастся в схеме БД, но использовать ее будет нельзя, т.к. она будет иметь статус invalid.

Создадим следующую функцию:

create function invalidFunc() return number is
begin
    return false;
end;
/

Здесь у нас ошибка, т.к. функция должна возвращать тип number, а мы возвращаем значение типа boolean. Oracle выдаст что-нибудь в этом духе:

Errors: FUNCTION INVALIDFUNC
Line/Col: 1/22 PLS-00103: Encountered the symbol ")" when
expecting one of the following:

   <an identifier> <a double-quoted delimited-identifier>
   current delete exists prior

Ok, пробуем создать функцию без ошибок:

create function invalidFunc() return number is
begin
    return 1;
end;
/

И в итоге получаем сообщение ORA-00955: name is already used by an existing object, что означает что в схеме уже есть объект с таким именем. В нашем случае - это функция invalidFunc, которая была создана во время предыдущей попытки.

Поэтому, чтобы заменить предыдущий вариант функции на новый, нужно использовать конструкцию create or replace.

Удаление функции

Чтобы удалить функцию из схемы, используется команда drop function:

drop function invalidFunc;

Локальные переменные

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

Для реализации более сложной бизнес-логики или для улучшения "читаемости" кода лучше можно использовать локальные переменные фукнции

переменные, которые доступны для использования только внутри функции и нигде больше. Они объявляются между ключевым словом IS и BEGIN.

Попробуем переписать нашу функцию getDiscount с использованием локальных переменных:

create or replace function getDiscount(
    pprice number
) return number
is
    discPerc constant number := 10;
    discount number;
begin
    discount := round(pprice * (discPerc / 100), 2);

    return discount;
end;
/

Использование функций в SQL запросах

Функции PL/SQL можно использовать в SQL запросах. Для демонстрации этого создадим таблицу с товарами и ценами:

create table products(
    id number primary key,
    name varchar2(300 char),
    price number
);

insert into products
values(1, 'Фотоаппарат', 1340);

insert into products
values(2, 'Клавиатура', 55);

insert into products
values(3, 'Планшет', 800);

Теперь получим список товаров и размер скидки на них:

select name, price, getDiscount(price) disc
from products

Результат:

NAME         PRICE  DISC
=========================
Фотоаппарат | 1340 | 134
Клавиатура  | 55   | 5.5
Планшет     | 800  | 80
=========================

Функции в PL/SQL ​

Какую проблему помогают решить функции ​

Пример создания простой функции ​

Функции с ошибками все равно создаются ​

Удаление функции ​

Локальные переменные ​

Использование функций в SQL запросах ​