oracle函数中使用递归,关于Oracle中split函数的实现以及函数递归的举例。

今天看到网友提出了这么一个问题。

看下面这张表：

指标编号  计算公式  指标值  可分解标志

A      (B+C)+E      ?        1

B                  10        0

C        D+E        ?        1

D                  30        0

E                   5        0

标志为0的指标值是固定的，标志为1的指标值根据公式去计算。

我觉得这个问题很有意思

，利用函数递归即可实现，当然需要对公式进行解析，这里我使用了类似于split的自建函数来实现。下面是方法——

创建类型tbl_str,

创建函数to_table,

创建测试表Test,

创建公式符号表expression,

创建函数f_test_get_value,

目前仅支持+ - * / ( )，如果需要别的符号自己加在表expression中.

Test表中的Formula字段每个字符都要用空格分开，例如 ( B + C ) + E

代码如下：

/*************************************************************************************/

create or replace type tbl_str as table of varchar2(4000);

/*********************************************************

/* Description:字定义类型，用于to_table函数

/* Author:He Yixiang

*********************************************************/

/

create or replace function to_table(pv_str varchar2,pv_split varchar2) return tbl_str

as

ltab tbl_str := tbl_str();

pos integer := 0;

ls varchar2(4000) := pv_str;

/*********************************************************

/* Description:同Split函数

/* Author:He Yixiang

*********************************************************/

begin

pos := instr(ls,pv_split);

while pos > 0 loop

ltab.extend;

ltab(ltab.count) := substr(ls,1,pos - 1);

ls := substr(ls,pos + length(pv_split));

pos := instr(ls,pv_split);

end loop;

ltab.extend;

ltab(ltab.count) := ls;

return ltab;

end;

/

create table test(id char(1),formula varchar2(100),value number,flag char(1));

insert into test select 'A','( B + C ) + E',null,1 from dual;

insert into test select 'B',null,10,0 from dual;

insert into test select 'C','D + E',null,1 from dual;

insert into test select 'D',null,30,0 from dual;

insert into test select 'E',null,5,0 from dual;

commit;

create table expression (exp_value char(1));

insert into expression (exp_value) values ('+');

insert into expression (exp_value) values ('-');

insert into expression (exp_value) values ('*');

insert into expression (exp_value) values ('/');

insert into expression (exp_value) values ('(');

insert into expression (exp_value) values (')');

commit;

create or replace function f_test_get_value(pc_id in char) return number is

Result number;

c_flag char(1);

type curtype is ref cursor;

cur curtype;

c_para char(1);

v_sqlstr varchar2(4000);

n_count number;

/*********************************************************

/* Description:根据表test中的公式Formula计算返回Value值

/* Author:He Yixiang

*********************************************************/

begin

select flag into c_flag from test where id=pc_id;

if c_flag='0' then

select value into Result from test where id=pc_id;

else

open cur for

select column_value from table(cast(to_table((select formula from test where id=pc_id),' ') as tbl_str));

v_sqlstr:='select ';

loop

fetch cur into c_para;

exit when cur%notfound;

select count(*) into n_count from expression where exp_value=c_para;

if n_count>0 then

v_sqlstr:=v_sqlstr||c_para;

else

v_sqlstr:=v_sqlstr||f_get_value(c_para);

end if;

end loop;

v_sqlstr:=v_sqlstr||' from dual';

execute immediate v_sqlstr into Result;

close cur;

end if;

return(Result);

end f_test_get_value;

/

/*************************************************************************************/

将上面代码拷贝到Command窗口执行即可。

测试如下：SQL> select * from test;

ID FORMULA                             VALUE FLAG

-- ------------------------------ ---------- ----

A  ( B + C ) + E                             1

B                                         10 0

C  D + E                                     1

D                                         30 0

E                                          5 0

SQL> select f_test_get_value('D') from dual;

F_TEST_GET_VALUE('D')

---------------------

30

SQL> select f_test_get_value('C') from dual;

F_TEST_GET_VALUE('C')

---------------------

35

SQL> select f_test_get_value('A') from dual;

F_TEST_GET_VALUE('A')

---------------------

50

测试通过。

希望上面的例子能给大家带来帮助。