Как вызвать функцию в oracle
Перейти к содержимому

Как вызвать функцию в oracle

Как вызвать функцию в 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! Я такое делал. Интересно, что у вас получится? Пробуйте!

Функции Oracle PL/SQL

Функция - это подпрограмма, которая вычисляет значение.

В этом учебном пособии вы узнаете, как создавать и удалять функции в Oracle/PLSQL с синтаксисом и примерами.

СОЗДАТЬ ФУНКЦИЮ (CREATE FUNCTION)

Как и на других языках программирования, вы можете создавать свои собственные функции в Oracle.

Синтаксис:

CREATE [OR REPLACE] FUNCTION имя_функции
[ (параметр [, параметр, . ]) ]
RETURN тип_данных IS | AS
[локальные объявления]
BEGIN
исполняемые предложения
[EXCEPTION
обработчики исключений]
END [имя_функции];

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

OUT - параметр не может ссылаться на процедуру или функцию, но значение параметра может быть изменено процедурой или функцией.

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

Пример

Рассмотрим на примере, как создать функцию в Oracle.

Функции в Oracle, возвращающие таблицу

Те, кто пришел в Oracle из MSSQL, наверняка столкнулись (как и я) с массой неожиданностей.

create function Foo1 (param1 nvarchar, param2 decimal(18,2))
return table (
id number,
nn nvarchar(50)
)
as
.

Знакомо, не правда ли? Если подобная функция прекрасно возвращала ADO Recordset из MS SQL, то в Oracle такой халявы нет. Однако получать наборы данных из функций через ADO просто необходимо, если мы хотим придерживаться грамотной структуры объектной модели.

Создадим две таблицы — сотрудников и подразделений.

--создание табличных пространств
create tablespace ALEX_DATA datafile 'C:\oracle\user_data\tblsp_alexdata.dat'
size 10M REUSE AUTOEXTEND ON NEXT 2M MAXSIZE 200M;
create tablespace ALEX_INDEX datafile 'C:\oracle\user_data\tblsp_alexix.dat'
size 1M REUSE AUTOEXTEND ON NEXT 1M MAXSIZE 200M;
/
--создание таблиц
create table ALEX.T_EMPLOYEES(
id number(5) not null,
id_department number(5) not null,
empinfo nvarchar2(50) not null
) tablespace ALEX_DATA;
create table ALEX.T_DEPARTMENTS(
id number(5) not null,
depinfo nvarchar2(50) not null
) tablespace ALEX_DATA;
/
--создание индексов
create index IXPK_T_EMPLOYEES on ALEX.T_EMPLOYEES(id)
tablespace ALEX_INDEX;
create index IXPK_T_DEPARTMENTS on ALEX.T_DEPARTMENTS(id)
tablespace ALEX_INDEX;
/
--создание реляционных связей
alter table ALEX.T_DEPARTMENTS
add constraint PK_T_DEPARTMENTS primary key (ID) using index IXPK_T_DEPARTMENTS;
/
alter table ALEX.T_EMPLOYEES
add constraint PK_T_EMPLOYEES primary key (ID) using index IXPK_T_EMPLOYEES
add constraint FK_T_DEPARTMENTS foreign key (id_department)
references ALEX.T_DEPARTMENTS(id);
/
--демо-данные
insert into ALEX.T_DEPARTMENTS (ID, DEPINFO)
values (1, 'Отдел кадров');
insert into ALEX.T_DEPARTMENTS (ID, DEPINFO)
values (2, 'Информационный отдел');
insert into ALEX.T_DEPARTMENTS (ID, DEPINFO)
values (3, 'Бухгалтерия');

insert into ALEX.T_EMPLOYEES (ID, ID_DEPARTMENT, EMPINFO)
values(1, 1, 'Иванов');
insert into ALEX.T_EMPLOYEES (ID, ID_DEPARTMENT, EMPINFO)
values(2, 1, 'Борисов');
insert into ALEX.T_EMPLOYEES (ID, ID_DEPARTMENT, EMPINFO)
values(3, 2, 'Сергеев');
insert into ALEX.T_EMPLOYEES (ID, ID_DEPARTMENT, EMPINFO)
values(4, 3, 'Никитин');
insert into ALEX.T_EMPLOYEES (ID, ID_DEPARTMENT, EMPINFO)
values(5, 3, 'Александров');

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

--тип данных строки, возвращаемой GetEmployees
type rowGetEmployees is record(
l_empinfo ALEX.T_EMPLOYEES.EMPINFO%TYPE, --привязка к типу поля empinfo
l_depinfo ALEX.T_DEPARTMENTS.DEPINFO%TYPE
);

Это тип данных строки. Атрибут TYPE объявляет для переменной тип, идентичный указанному полю. Создаем второй тип:

type tblGetEmployees is table of rowGetEmployees;

Это таблица из строк типа rowGetEmployees. Переменную этого типа будет возвращать наша функция:

function GetEmployees
(prm_depID number default null)
return tblGetEmployees
pipelined;

Если параметр не передан, будем возвращать список всех сотрудников. Атрибут pipelined означает, что функция является конвейерной, результат возвращается клиенту немедленно при вызове директивы pipe row, поэтому оператор return необязателен. Фактически, по результирующему набору из запроса в теле функции проходит курсор, который при каждой итерации добавляет в рекордсет текущую строку.
Поместим типы данных и функцию в пакет. На выходе имеем

create or replace package ALEX.P_MY1 is
--тип данных строки, возвращаемой GetEmployees
type rowGetEmployees is record(
l_empinfo ALEX.T_EMPLOYEES.EMPINFO%TYPE, --привязка к типу поля empinfo
l_depinfo ALEX.T_DEPARTMENTS.DEPINFO%TYPE
);
--тип данных таблицы из строк rowGetEmployees
type tblGetEmployees is table of rowGetEmployees;
--
function GetEmployees
(prm_depID number default null)
return tblGetEmployees
pipelined;

create or replace package body ALEX.P_MY1 is
function GetEmployees
(prm_depID number default null)
return tblGetEmployees
pipelined
is
begin
if prm_depID is null then
for curr in
(
select emp.empinfo, dep.depinfo
from ALEX.T_DEPARTMENTS dep inner join
ALEX.T_EMPLOYEES emp on dep.id = emp.id_department
) loop
pipe row (curr);
end loop;
else
for curr in
(
select emp.empinfo, dep.depinfo
from ALEX.T_DEPARTMENTS dep inner join
ALEX.T_EMPLOYEES emp on dep.id = emp.id_department
where dep.id = prm_depID
) loop
pipe row (curr);
end loop;
end if;
end GetEmployees;

SQL> select * from TABLE(ALEX.P_MY1.GetEmployees);

SQL> select * from TABLE(ALEX.P_MY1.GetEmployees(1));

Как вызвать функцию в 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.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *