Как вызвать функцию в oracle
Вот теперь, наконец, давайте рассмотрим такое понятие как функции PL/SQL. Раньше в шагах мы с вами рассмотрели так называемые встроенные функции PL/SQL. А сейчас мы попробуем сами научится писать то, что называется функциями. По своей сути функция это то же, что и процедура, она может принимать параметры по всем тем же правилам, что и процедуры, и кроме всего она может возвращать значения! Но не применением OUT типа передаваемого параметра, а сама по себе. То есть функция, принимает параметры и возвращает одно(!), значение! В принципе в функции можно применять параметры с типом OUT — но это очень плохая идея! Такой метод я использовать не рекомендую! Определение функции таково:
-------------- CREATE [OR REPLACE] FUNCTION - имя_функции ------------------------------------- -------------- (аргумент [IN] [OUT] [IN OUT] тип, . ) AS [IS] ----------------------------- -------------- тело процедуры ----------------------------------------------------------------- -------------- RETURN (возвращаемое_значение) -------------------------------------------------
Кое-что вам уже знакомо, за исключением того, что присутствует оператор RETURN. Посредством этого оператора функция возвращает значение. Функция, как правило, вызывается внутри какого-либо определения, т.к. вызывать функцию как оператор нет смысла. Но как вы дальше убедитесь, с помощью функция можно делать очень полезные вещи. Итак, давайте напишем функцию преобразования BOOLEAN типа в тип VARCHAR2 — это самая простая задачка во всех учебниках. Итак:
CREATE OR REPLACE FUNCTION BOOL_TO_CHAR(INBL IN BOOLEAN) RETURN VARCHAR2 IS OUT_ST VARCHAR2(5); BEGIN IF (INBL) THEN OUT_ST := 'TRUE'; ELSIF (NOT INBL) THEN OUT_ST := 'FALSE'; ELSE OUT_ST := 'NULL'; END IF; RETURN(OUT_ST); END BOOL_TO_CHAR; /
Получаем после компиляции:
SQL> CREATE OR REPLACE FUNCTION BOOL_TO_CHAR(INBL IN BOOLEAN) RETURN VARCHAR2 2 IS 3 4 OUT_ST VARCHAR2(5); 5 6 BEGIN 7 8 IF (INBL) THEN 9 OUT_ST := 'TRUE'; 10 ELSIF (NOT INBL) THEN 11 OUT_ST := 'FALSE'; 12 ELSE 13 OUT_ST := 'NULL'; 14 END IF; 15 16 RETURN(OUT_ST); 17 18 END BOOL_TO_CHAR; 19 / Функция создана.
Теперь попробуем применить ее на практике. Запишем такой анонимный блок:
SET SERVEROUTPUT ON DECLARE BEGIN DBMS_OUTPUT.enable; DBMS_OUTPUT.put_line(BOOL_TO_CHAR(TRUE)); DBMS_OUTPUT.put_line(BOOL_TO_CHAR(FALSE)); DBMS_OUTPUT.put_line(BOOL_TO_CHAR(NULL)); END; /
SQL> SET SERVEROUTPUT ON SQL> DECLARE 2 3 BEGIN 4 5 DBMS_OUTPUT.enable; 6 DBMS_OUTPUT.put_line(BOOL_TO_CHAR(TRUE)); 7 DBMS_OUTPUT.put_line(BOOL_TO_CHAR(FALSE)); 8 DBMS_OUTPUT.put_line(BOOL_TO_CHAR(NULL)); 9 10 END; 11 / TRUE FALSE NULL Процедура PL/SQL успешно завершена.
Как видите, наша функция BOOL_TO_CHAR вызвана внутри определения DBMS_OUTPUT.put_line(..), так обычно и происходит. Хорошо видно, что мы получили строки, передав булевы значения.
Теперь давайте поговорим об операторе RETURN. Этот оператор возвращает значение функции, приводя его к типу возвращаемого функцией.
------------ RETURN (значение) ------------------------------
Где значение — это то что и возвращает функция. Здесь скобки «()» — это только стиль при написании функций, для того чтобы было немного понятнее. Операторов RETURN в функции может быть несколько, при этом первый из них, завершит ее работу и вернет управление в вызывающую процедуру! Давайте напишем еще одну функцию преобразования BOOLEAN в VARCHAR2, но при этом используем немного другую логику:
CREATE OR REPLACE FUNCTION BOOL_TO_CHARTWO(INBL IN BOOLEAN) RETURN VARCHAR2 IS BEGIN IF (INBL) THEN RETURN('TRUE'); ELSIF (NOT INBL) THEN RETURN('FALSE'); ELSE RETURN('NULL'); END IF; END BOOL_TO_CHARTWO; /
Получаем после компиляции:
SQL> CREATE OR REPLACE FUNCTION BOOL_TO_CHARTWO(INBL IN BOOLEAN) RETURN VARCHAR2 2 IS 3 4 BEGIN 5 6 IF (INBL) THEN 7 RETURN('TRUE'); 8 ELSIF (NOT INBL) THEN 9 RETURN('FALSE'); 10 ELSE 11 RETURN('NULL'); 12 END IF; 13 14 END BOOL_TO_CHARTWO; 15 / Функция создана.
Хорошо видно, что мы заменили промежуточную переменную и применили три оператора RETURN. В данном случае это будет то же, что и первая функция хоть и немного в другом контексте. Запишем вот такой анонимный блок:
SET SERVEROUTPUT ON DECLARE BEGIN DBMS_OUTPUT.enable; DBMS_OUTPUT.put_line(BOOL_TO_CHARTWO(TRUE)); DBMS_OUTPUT.put_line(BOOL_TO_CHARTWO(FALSE)); DBMS_OUTPUT.put_line(BOOL_TO_CHARTWO(NULL)); END; /
SQL> SET SERVEROUTPUT ON SQL> SQL> DECLARE 2 3 BEGIN 4 5 DBMS_OUTPUT.enable; 6 DBMS_OUTPUT.put_line(BOOL_TO_CHARTWO(TRUE)); 7 DBMS_OUTPUT.put_line(BOOL_TO_CHARTWO(FALSE)); 8 DBMS_OUTPUT.put_line(BOOL_TO_CHARTWO(NULL)); 9 10 END; 11 / TRUE FALSE NULL Процедура PL/SQL успешно завершена.
Что и требовалось доказать! Так же смею заметить, что в PL/SQL с успехом можно применять рекурсию. Рекурсивные вызовы иногда делают код меньше, но запутаннее! Приведу один пример расчета факториала числа, это я подглядел у Билла Гейтса в его MSDN и переложил на PL/SQL, не все же ему таскать у других! 🙂 Итак:
CREATE OR REPLACE FUNCTION FACTORIAL(NUM IN NUMBER) RETURN NUMBER IS BEGIN IF (NUM <=1) THEN RETURN (NUM); ELSE RETURN (NUM * FACTORIAL(NUM-1)); END IF; END FACTORIAL; /
Получаем после компиляции:
SQL> CREATE OR REPLACE FUNCTION FACTORIAL(NUM IN NUMBER) RETURN NUMBER 2 IS 3 4 BEGIN 5 6 IF (NUM <=1) THEN 7 RETURN (NUM); 8 ELSE 9 RETURN (NUM * FACTORIAL(NUM-1)); 10 11 END IF; 12 13 END FACTORIAL; 14 / Функция создана.
Запишем анонимный блок для трех значений - вот такой:
SET SERVEROUTPUT ON DECLARE BEGIN DBMS_OUTPUT.enable; DBMS_OUTPUT.put_line(TO_CHAR(FACTORIAL(5))); DBMS_OUTPUT.put_line(TO_CHAR(FACTORIAL(7))); DBMS_OUTPUT.put_line(TO_CHAR(FACTORIAL(12))); END; /
SQL> SET SERVEROUTPUT ON SQL> SQL> DECLARE 2 3 BEGIN 4 5 DBMS_OUTPUT.enable; 6 DBMS_OUTPUT.put_line(TO_CHAR(FACTORIAL(5))); 7 DBMS_OUTPUT.put_line(TO_CHAR(FACTORIAL(7))); 8 DBMS_OUTPUT.put_line(TO_CHAR(FACTORIAL(12))); 9 10 END; 11 / 120 5040 479001600 Процедура PL/SQL успешно завершена.
Ух, ты! Работает! Привет Биллу! Получили три значения факториала чисел 5, 7, 12. Проверьте правильно или нет?
Вот собственно так пишутся функции. Хотите задание? А вот - в PL/SQL нет функции сложения и вычитания одного времени суток и другого! Напишите функции, которые, например, складывают и вычитают, скажем, 10:34 и 5:08! Я такое делал. Интересно, что у вас получится? Пробуйте!
Функции в 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: 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 ========================= Как вызвать функцию в oracle
Мне нужно получить таблицу(представление), которая составляется на основании других таблиц, а если точнее, то в этом представлении должно быть булевое поле, значение которого зависит от вызываемой функции. Но я не знаю как это реализовать и возможно ли такое. Например:
SELECT Code, Name, CALL_FUNCTION(Code) FROM Customer;Re: Oracle: Как добавить в запрос вызов функции?
| От: | kallisto |
| Дата: | 25.02.05 10:08 |
| Оценка: |
Здравствуйте, KBH, Вы писали:
KBH>Приветствую, товарищи.
KBH>Мне нужно получить таблицу(представление), которая составляется на основании других таблиц, а если точнее, то в этом представлении должно быть булевое поле, значение которого зависит от вызываемой функции. Но я не знаю как это реализовать и возможно ли такое. Например:
KBH>
KBH>SELECT Code, Name, CALL_FUNCTION(Code) KBH>FROM Customer; KBH>такой запрос должен содержать клаузу into
select . into var1 from . ;где var1 — переменная объявленная в списке внутренних переменных
__________________________
Re[2]: Oracle: Как добавить в запрос вызов функции?
| От: | kallisto |
| Дата: | 25.02.05 10:10 |
| Оценка: |
Здравствуйте, kallisto, Вы писали:
K>Здравствуйте, KBH, Вы писали:
KBH>>Приветствую, товарищи.
KBH>>Мне нужно получить таблицу(представление), которая составляется на основании других таблиц, а если точнее, то в этом представлении должно быть булевое поле, значение которого зависит от вызываемой функции. Но я не знаю как это реализовать и возможно ли такое. Например:
KBH>>
KBH>>SELECT Code, Name, CALL_FUNCTION(Code) KBH>>FROM Customer; KBH>>K>такой запрос должен содержать клаузу into
K>
K>select . K> into var1 K> from . ; K>K>где var1 — переменная объявленная в списке внутренних переменных
сорри, не доконца было прочитано сообщение
__________________________
Re: Oracle: Как добавить в запрос вызов функции?
| От: | kallisto | |
| Дата: | 25.02.05 10:16 | |
| Оценка: | 2 (1) | |
Здравствуйте, KBH, Вы писали:
KBH>Приветствую, товарищи.
KBH>Мне нужно получить таблицу(представление), которая составляется на основании других таблиц, а если точнее, то в этом представлении должно быть булевое поле, значение которого зависит от вызываемой функции. Но я не знаю как это реализовать и возможно ли такое. Например:
KBH>
KBH>SELECT Code, Name, CALL_FUNCTION(Code) KBH>FROM Customer; KBH>Если, я тебя правильно поняла, то вот тебе пример, где SP_TA_MC_GetFullRemark — функция, которая возвращает текствовое поле
select to_char(f.StartDate, 'DD.MM.YYYY') as "startdate", u.username as "username", xxfqt.text as "category", SP_TA_MC_GetFullRemark(f.followupid, 5) as "remark" from followup f, followupqueue fq, XXFollowUpQueueType xxfqt, all_users u where f.customerid = :customerid and f.customertype = 1 and fq.queueid = f.queueid and f.createdby = u.user_id(+) and xxfqt.itemid = fq.type and xxfqt.languageid = :languageid order by startdate descа вот и сама функция
CREATE OR REPLACE function SP_TA_MC_GetFullRemark ( pCustomerID Integer, pCustomerType Integer, pFromPos Integer Default 0) return VarChar2 is res VarChar2(2000 CHAR) :=''; c Integer :=0; begin for cc in (select REMARK from REMARK where CustomerID=pCustomerID and CustomerType=pCustomerType and SORTORDER between pFromPos and pFromPos+99 order by SORTORDER) loop if c>0 then res:=res||' '; end if; res:=res||cc.REMARK; c:=c+1; end loop; res:=REPLACE(res, CHR(13)||CHR(10), CHR(13)); return trim(res); exception when others then return substr(res, 1, 128); end; /__________________________
Re[2]: Oracle: Как добавить в запрос вызов функции?
| От: | KBH |
| Дата: | 25.02.05 10:52 |
| Оценка: |
Здравствуйте, kallisto, Вы писали:
K>Если, я тебя правильно поняла, то вот тебе пример, где SP_TA_MC_GetFullRemark — функция, которая возвращает текствовое поле.
Спасибо , оказывается моя функция возвращала значение типа BOOLEAN!
Re: Oracle: Как добавить в запрос вызов функции?
| От: | Softwarer | http://softwarer.ru |
| Дата: | 25.02.05 10:56 | |
| Оценка: | 2 (1) | |
Здравствуйте, KBH, Вы писали:
KBH>Мне нужно получить таблицу(представление), которая составляется на основании других таблиц, а если точнее, то в этом представлении должно быть булевое поле, значение которого зависит от вызываемой функции. Но я не знаю как это реализовать и возможно ли такое.
Возможно, но есть два момента. Во-первых, в SQL не поддерживается тип boolean; соответственно, поле должно быть, например, char со значениями T/F или другим подобным. Во-вторых, для того, чтобы этот запрос подходил для представления, необходимо указать имя, под которым колонка будет фигурировать в представлении.
SQL> conn sys@sanders as sysdba Connected. SQL> SQL> create function IsDBA (username varchar2) return char as 2 begin 3 for cr in (select 1 from dba_role_privs 4 where granted_role = 'DBA' and grantee = username) loop 5 return 'Y'; 6 end loop; 7 return 'N'; 8 end; 9 / Function created. SQL> create view all_users_as_dba as 2 select username, IsDBA (username) is_dba from dba_users; View created. SQL> column is_dba format a6; SQL> select * from all_users_as_dba where rownum ------------------------------ ------ BIBDEMO Y SYS Y SYSTEM Y DBSNMP N SYSMAN Y MGMT_VIEW N OLAPSYS N OWB_DT N OWB_TARGET N HTMLDB_PUBLIC_USER N 10 rows selected.Вызов внутренней функции
В определенной процедуре в пакете есть внутренняя функция описанная в declare разделe этой процедуры. Есть какая-либо возможность вызвать эту функцию извне, или она может быть вызвана только этой процедурой?
Отслеживать
51.6k 201 201 золотой знак 63 63 серебряных знака 245 245 бронзовых знаков
задан 25 сен 2018 в 6:15
Анатолий Эрнст Анатолий Эрнст
927 1 1 золотой знак 8 8 серебряных знаков 27 27 бронзовых знаков
Нет, только внутри процедуры. Как, собственно, почти везде.
25 сен 2018 в 6:20
А не подскажете, для чего обычно используют вложение функций и процедур? Чтобы защитить их от внешних вызовов или есть еще какие - то особенности? В плане практики применения можно что - то почитать )) ?
25 сен 2018 в 9:22
Чтобы защитить от внешнего вызова в пакете, достаточно не объявлять функцию в интерфейсной части. Обычно все прозаичнее - есть кусок кода, который повторяется, но нужен только внутри одной функции. Можно прямо внутри нее и объявить, чтобы пространство имен не занимать почем зря.
25 сен 2018 в 9:25
1 ответ 1
Сортировка: Сброс на вариант по умолчанию
Нет, вызвать извне функцию, объявленную внутри другой функции нельзя. Поиск подпрограммы для вызова производится начиная с текущего пространства имён и продолжается, если не найдена, последовательно в пространствах имён уровнем выше.
См. How PL/SQL Compiler Resolves Invocations в документации.
Это вполне нормально, объявить функцию только в том пространстве имён, где она впервые применяется (в пакетной процедуре, как в данном случае), и тем самым ограничить её область видимости и избежать возможных конфликтов имён.
Если надо вызвать функцию также в программном блоке где она пока не видна и избежать тем самым дупликатов кода, то необходим рефакторинг кода. Переместите функцию в пространство имен, где она будет доступна для вызова из нового и старого кода, возможно переименуйте её.
create or replace package pkg as function foo (arg number) return number; end pkg; / create or replace package body pkg as function bar (arg number) return number is factor constant number := 10; begin return arg * factor; end; function foo (arg number) return number is /** original bar was moved to the package scope */ function bar (arg number) return number is begin return pkg.bar (arg); end; begin return foo.bar (arg); end; end pkg; /