The implementation of the split function in Oracle and the example of function recursion.

Today, I saw a question raised by a netizen.

See the following table:
Indicator number calculation formula indicator value decomposition mark
A (B + C) + E? 1
B 10 0
C d + E? 1
D 30 0
E 5 0

The value of 0 is fixed, and the value of 1 is calculated based on the formula.

I think this problem is very interesting. It can be implemented through recursive function. Of course, the formula needs to be parsed. Here I use a self-built function similar to split to implement it. The following is the method --

Creation type tbl_str,
Create Function to_table,
Create a test table,
Create a formula symbol table expression,
Create Function f_test_get_value,

Currently, only +-*/() is supported. If you need other symbols, add them to the table expression.
Each character of the formula field in the test table must be separated by spaces, for example, (B + C) + E
The Code is as follows:

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

 

Create or replace type tbl_str as table of varchar2 (4000 );
/*************************************** ******************
/* Description: The word definition type, used for the to_table function.
/* 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: Same as the split Function
/* 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: calculates the returned value based on the formula in the test table.
/* 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
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;
/

 

 

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

Copy the above Code to the Command window for execution.
The test is as follows:
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

 

Test passed.

 

 

I hope the above example will help you.