/ * test
begin
-- Call the function
pkg_pub_app_context.P_SET_APP_USER_ID(0);
:result := pkg_life_public_formula.f_execute_formula(i_formula_id => :i_formula_id, io_para_list => :io_para_list);
end;
--select * from t_formula f where f.formula_type = 31;
--i_formula_id: RISK_AMOUNT_FORMULA__PREM :=
-- minor_amt_rate1]*[period_prem]*decode(sign([premium_year]-(18-[validate_age])),-1,[premium_year],18-[validate_age])--io_para_list: [hold_amount=100001][coverage_year=0][policy_year=1][loan_rate=0][fix_increment=1][anni_balance=0][validate_age=2][calc_age=8][calc_rate=0][minor_amt_rate1=1][minor_amt_rate2=0][account_value=0][single_prem=2950.03][item_id=36674][period_prem=2950.03][premium_year=10]
*/
CREATE OR REPLACE PACKAGE PKG_LIFE_PUBLIC_FORMULA is
-- Purpose : 通用公式计算模块
-- Public type declarations
-- type <TypeName> is <Datatype>;
-- Public constant declarations
-- <ConstantName> constant <Datatype> := <Value>;
-- Public variable declarations
-- <VariableName> <Datatype>;
-- Public function and procedure declarations
-- function <FunctionName>(<Parameter> <Datatype>) return <Datatype>;
/*=====================以下程序是表达式计算公用程序,与业务无关========================*/
/*执行表达式计算*/
procedure P_EXECUTE_FORMULA(
I_FORMULA_ID in varchar2,/*表达式ID*/
IO_PARA_LIST in out varchar2,/*参数列表,用中括号将每一对参数括起来,例如 [Age=1][Real_Name='jason'][Birthday=to_date('2002-04-01'),'YYYY-MM-DD')] */
O_RETURN_VALUE out varchar2/*返回结果*/
);
/*执行表达式计算*/
function F_EXECUTE_FORMULA(
I_FORMULA_ID in varchar2,/*表达式ID*/
IO_PARA_LIST in out varchar2/*参数列表,用中括号将每一对参数括起来,例如 [Age=1][Real_Name='jason'][Birthday=to_date('2002-04-01'),'YYYY-MM-DD')] */
) return varchar2;
function test return varchar2;
/*将参数添加到参数列表中*/
function F_ADD_PARA_TO_PARA_LSIT(
I_PARA_LIST in varchar2,/*参数列表,用中括号将每一对参数括起来,例如 [Age=1][Real_Name='jason'][Birthday=to_date('2002-04-01'),'YYYY-MM-DD')] */
I_PARA_NAME in varchar2,/*参数名*/
I_PARA_VALUE in varchar2/*参数值*/
) return varchar2 ;
/*将日期类型数据转换成字符串,例如 日期 2001/1/1 --> to_date('2001/1/1','YYYY/MM/DD') */
function f_date_to_string(
I_DATE in date
) return varchar2;
/*将字符类型数据转换成字符串,例如 字符串 'abc'--> '''abc''' */
function f_str_to_string(
I_STR in varchar2
) return varchar2;
/*判断参数是否在参数列表中*/
function F_IS_PARA_IN_PARA_LIST(
I_PARA_LIST in varchar2,/*参数列表,用中括号将每一对参数括起来,例如 [Age=1][Real_Name='jason'][Birthday=to_date('2002-04-01'),'YYYY-MM-DD')] */
I_PARA_NAME in varchar2/*参数名*/
) return boolean;
/*从参数列表字符串中取值*/
function F_GET_VALUE_FROM_PARA_LIST(
I_PARA_LIST in varchar2,/*参数列表,用中括号将每一对参数括起来,例如 [Age=1][Real_Name='jason'][Birthday=to_date('2002-04-01'),'YYYY-MM-DD')] */
I_PARA_NAME in varchar2/*参数名*/
) return varchar2;
end PKG_LIFE_PUBLIC_FORMULA;
/
CREATE OR REPLACE PACKAGE BODY PKG_LIFE_PUBLIC_FORMULA is
-- Private type declarations
-- type <TypeName> is <Datatype>;
-- Private constant declarations
-- <ConstantName> constant <Datatype> := <Value>;
-- Private variable declarations
-- <VariableName> <Datatype>;
-- Function and procedure implementations
-- function <FunctionName>(<Parameter> <Datatype>) return <Datatype> is
-- <LocalVariable> <Datatype>;
-- begin
-- <Statement>;
-- return(<Result>);
-- end;
--begin
-- Initialization
-- <Statement>;
/*写日志*/
/*
procedure mlog(
I_LOG_DATA in varchar2
)
is
m_len number(10);
m_pos number(10);
m_temp_str varchar2(255);
m_max_line_length number(3);
begin
m_max_line_length:=110;
m_pos:=1;
m_len:=length(I_LOG_DATA);
loop
if m_pos>m_len then
exit;
end if;
if m_pos>1 then
--第二行起缩进
DBMS_OUTPUT.Put(' ');
end if;
if(m_len-m_pos+1>m_max_line_length) then
DBMS_OUTPUT.Put_Line(substr( I_LOG_DATA,m_pos,m_max_line_length));
else
DBMS_OUTPUT.Put_Line(substr( I_LOG_DATA,m_pos,m_len-m_pos+1));
end if;
m_pos:=m_pos+m_max_line_length;
end loop;
end;
*/
/*从参数列表字符串中取值*/
function F_GET_VALUE_FROM_PARA_LIST(
I_PARA_LIST in varchar2,/*参数列表,用中括号将每一对参数括起来,例如 [Age=1][Real_Name='jason'][Birthday=to_date('2002-04-01'),'YYYY-MM-DD')] */
I_PARA_NAME in varchar2/*参数名*/
) return varchar2
is
m_start_str varchar2(255);
m_start_pos number(10);
m_end_str varchar2(10);
m_end_pos varchar2(10);
m_para_value varchar2(100);
Para_Not_Found_Exception Exception;
begin
m_start_str:='['||I_PARA_NAME||'=';
m_end_str:=']';
m_start_pos:=instr(I_PARA_LIST,m_start_str,1,1);
if(m_start_pos=0) then
raise_application_error(-20001,'Para Not Found');
end if;
m_start_pos:=m_start_pos+length(m_start_str);
m_end_pos:=instr(I_PARA_LIST,m_end_str,m_start_pos,1);
if(m_end_pos=0) then
raise_application_error(-20001,'ParaNot Found');
end if;
m_para_value:=substr(I_PARA_LIST,m_start_pos,m_end_pos-m_start_pos);
-- mlog('F_GET_VALUE_FROM_PARA_LIST:'||I_PARA_LIST||'-->'||I_PARA_NAME||'='||m_para_value);
return m_para_value;
end F_GET_VALUE_FROM_PARA_LIST;
/*判断参数是否在参数列表中*/
function F_IS_PARA_IN_PARA_LIST(
I_PARA_LIST in varchar2,/*参数列表,用中括号将每一对参数括起来,例如 [Age=1][Real_Name='jason'][Birthday=to_date('2002-04-01'),'YYYY-MM-DD')] */
I_PARA_NAME in varchar2/*参数名*/
) return boolean
is
m_start_str varchar2(255);
m_start_pos number(10);
m_end_str varchar2(10);
m_end_pos varchar2(10);
m_para_value varchar2(100);
Para_Not_Found_Exception Exception;
m_found_flag boolean;
Invalid_Para_List_Format Exception;
begin
m_found_flag:=true;
if (I_PARA_LIST is null ) or (I_PARA_NAME is null) then
m_found_flag:=false;
return m_found_flag;
end if;
m_start_str:='['||I_PARA_NAME||'=';
m_end_str:=']';
m_start_pos:=instr(I_PARA_LIST,m_start_str,1,1);
if(m_start_pos=0) then
m_found_flag:=false;
return m_found_flag;
end if;
m_start_pos:=m_start_pos+length(m_start_str);
m_end_pos:=instr(I_PARA_LIST,m_end_str,m_start_pos,1);
if(m_end_pos=0) then
raise_application_error(-20001,'Invalid para List format');
end if;
-- mlog('F_IS_PARA_IN_PARA_LIST'||I_PARA_LIST||':'||I_PARA_NAME||'-->');
return m_found_flag;
end F_IS_PARA_IN_PARA_LIST;
/*将参数添加到参数列表中*/
function F_ADD_PARA_TO_PARA_LSIT(
I_PARA_LIST in varchar2,/*参数列表,用中括号将每一对参数括起来,例如 [Age=1][Real_Name='jason'][Birthday=to_date('2002-04-01'),'YYYY-MM-DD')] */
I_PARA_NAME in varchar2,/*参数名*/
I_PARA_VALUE in varchar2/*参数值*/
) return varchar2
is
Para_Already_In_List_Exception Exception;
m_new_para_list varchar2(4096);
begin
if F_IS_PARA_IN_PARA_LIST(I_PARA_LIST,I_PARA_NAME) then
raise_application_error(-20001,'Para Already In List,new para:'||I_PARA_NAME||',old para:'||I_PARA_LIST);
end if;
if I_PARA_LIST is null then
m_new_para_list:='['||I_PARA_NAME||'='||I_PARA_VALUE||']';
else
m_new_para_list:=I_PARA_LIST||'['||I_PARA_NAME||'='||I_PARA_VALUE||']';
end if;
p_log('F_ADD_PARA_TO_PARA_LSIT:'||I_PARA_NAME||'-->'||I_PARA_VALUE);
return m_new_para_list;
end F_ADD_PARA_TO_PARA_LSIT;
/*从从表达式中获取参数名,返回第一个找到参数*/
function F_GET_PARA_FROM_FORMULA(
I_FORMULA_BODY in varchar2/* 表达式内容,其中的参数用中括号括起来,例如: 100*[Age] */
) return varchar2
is
m_para_name varchar2(255);
m_start_pos number(10);
m_end_pos number(10);
Invalid_Para_List_Format Exception;
begin
m_start_pos:=instr(I_FORMULA_BODY,'[',1,1);
if m_start_pos=0 then
return '';
end if;
m_end_pos:=instr(I_FORMULA_BODY,']',m_start_pos,1);
if m_end_pos=0 then
raise_application_error(-20001,'Invalid para format');
end if;
m_para_name:=substr(I_FORMULA_BODY,m_start_pos+1,m_end_pos-m_start_pos-1);
p_log('F_GET_PARA_FROM_FORMULA:'||I_FORMULA_BODY||'-->'||m_para_name);
return m_para_name;
end F_GET_PARA_FROM_FORMULA;
/*将表达式中的参数替换成具体的值*/
function F_REPLACE_PARA_WITH_VALUE(
I_FORMULA_BODY in varchar2,/* 表达式内容,其中的参数用中括号括起来,例如: 100*[Age] */
I_PARA_NAME in varchar2, /*参数名*/
I_PARA_VALUE in varchar2 /*参数值*/
) return varchar2 /*返回替换后的表达式*/
is
m_para_name varchar2(255);
m_start_pos number(10);
Para_Not_Found_Exception EXCEPTION;
m_pre_str varchar2(1024);
m_post_str varchar2(1024);
begin
m_para_name:='['||I_PARA_NAME||']';
m_start_pos:=instr(I_FORMULA_BODY,m_para_name,1,1);
if m_start_pos=0 then
raise_application_error(-20001,'Para not found');
end if;
m_pre_str:=substr(I_FORMULA_BODY,1,m_start_pos-1);
m_post_str:=substr(I_FORMULA_BODY,m_start_pos+length(m_para_name));
p_log('F_REPLACE_PARA_WITH_VALUE:'||I_FORMULA_BODY||':'||I_PARA_NAME||'-->'||I_PARA_VALUE);
return m_pre_str||'('||I_PARA_VALUE||')'||m_post_str;
end F_REPLACE_PARA_WITH_VALUE;
/*根据表达式名获取表达式具体内容*/
function F_GET_FORMULA_BODY(
I_FORMULA_NAME in varchar2/* 表达式名 */
) return varchar2
is
CURSOR cur_formula is
select FORMULA_BODY from t_formula
where FORMULA_NAME=I_FORMULA_NAME;
m_formula_body varchar2(2048);
begin
-- mlog('F_GET_FORMULA_BODY:'||I_FORMULA_NAME||'-->'||'starting............');
open cur_formula;
fetch cur_formula into m_formula_body;
if cur_formula%NOTFOUND then
raise_application_error(-20001,'formula not found');
end if;
p_log('F_GET_FORMULA_BODY:'||I_FORMULA_NAME||'-->'||m_formula_body);
return m_formula_body;
end F_GET_FORMULA_BODY;
/*计算SQL类型的表达式,只能返回唯一的一条记录和唯一的一个字段
eg: select policy_code from t_policy where policy_id=123*/
function F_GET_SQL_FORMULA_VALUE(
I_SQL_FORMULA in varchar2/* 表达式名 */
) return varchar2
is
m_temp_value varchar2(1024);
begin
-- mlog('F_GET_SQL_FORMULA_VALUE'||I_SQL_FORMULA||'-->'||'starting..............');
EXECUTE IMMEDIATE I_SQL_FORMULA into m_temp_value;
p_log('F_GET_SQL_FORMULA_VALUE'||I_SQL_FORMULA||'-->'||m_temp_value);
return m_temp_value;
end F_GET_SQL_FORMULA_VALUE;
/*计算普通表达式值*/
function F_GET_NORMAL_FORMULA_VALUE(
I_NORMAL_FORMULA in varchar2/* 表达式名 */
) return varchar2
is
m_temp_value varchar2(1024);
m_temp_sql varchar2(2048);
begin
-- mlog('F_GET_NORMAL_FORMULA_VALUE:'||I_NORMAL_FORMULA||'starting...........');
m_temp_sql:='select '||I_NORMAL_FORMULA||' from dual';
p_log('execute sql:'||m_temp_sql);
EXECUTE IMMEDIATE m_temp_sql into m_temp_value;
p_log('F_GET_NORMAL_FORMULA_VALUE:'||I_NORMAL_FORMULA||'-->'||m_temp_value);
-- return round(m_temp_value,10);
-- change at taiping
return m_temp_value;
end F_GET_NORMAL_FORMULA_VALUE;
/*执行表达式计算*/
function F_EXECUTE_FORMULA(
I_FORMULA_ID in varchar2,/*表达式ID*/
IO_PARA_LIST in out varchar2/*参数列表,用中括号将每一对参数括起来,例如 [Age=1][Real_Name='jason'][Birthday=to_date('2002-04-01'),'YYYY-MM-DD')] */
) return varchar2
is
m_formula_body varchar2(2048);/*当前表达式内容*/
m_para_name varchar2(100);/*变量名*/
m_para_value varchar2(100);/*变量值*/
m_formula_value varchar2(100);/*表达式值*/
begin
p_log('F_EXECUTE_FORMULA:'||I_FORMULA_ID||':'||'starting..................');
if F_IS_PARA_IN_PARA_LIST(IO_PARA_LIST,I_FORMULA_ID) then
m_formula_value:= F_GET_VALUE_FROM_PARA_LIST(IO_PARA_LIST,I_FORMULA_ID);
else
/*从数据库中读取公式的具体内容*/
m_formula_body:=F_GET_FORMULA_BODY(I_FORMULA_ID);
/*从公式中提取参数*/
m_para_name:=f_Get_Para_From_Formula(m_formula_body);
loop
if m_para_name is null then
exit ;
end if;
/*如果参数已经在参数列表中,则直接从参数列表中取值,
否则递归调用本身,*/
if F_IS_PARA_IN_PARA_LIST(IO_PARA_LIST, m_para_name) then
/*从参数列表中取值*/
m_para_value:=F_GET_VALUE_FROM_PARA_LIST(IO_PARA_LIST,m_para_name);
else
/*递归计算表达式值*/
m_para_value:= F_EXECUTE_FORMULA(m_para_name,IO_PARA_LIST);
/*更新参数列表*/
-- IO_PARA_LIST:=F_ADD_PARA_TO_PARA_LSIT(IO_PARA_LIST,m_para_name,m_para_value);
end if;
if m_para_value is null then
P_ERROR(2005,m_para_name||' Para:'||IO_PARA_LIST);
end if;
/*将参数替换成具体的值*/
m_formula_body:=F_REPLACE_PARA_WITH_VALUE(m_formula_body,m_para_name,m_para_value);
/*从公式中提取参数*/
m_para_name:=f_Get_Para_From_Formula(m_formula_body);
end loop;
/*计算表达式值*/
m_formula_value:=F_GET_NORMAL_FORMULA_VALUE(m_formula_body);
IO_PARA_LIST:=F_ADD_PARA_TO_PARA_LSIT(IO_PARA_LIST,I_FORMULA_ID,m_formula_value);
end if;
p_log('F_EXECUTE_FORMULA:'||I_FORMULA_ID||':'||m_formula_value);
-- return round(m_formula_value,10);
return m_formula_value;
end F_EXECUTE_FORMULA;
/*执行表达式计算*/
procedure P_EXECUTE_FORMULA(
I_FORMULA_ID in varchar2,/*表达式ID*/
IO_PARA_LIST in out varchar2,/*参数列表,用中括号将每一对参数括起来,例如 [Age=1][Real_Name='jason'][Birthday=to_date('2002-04-01'),'YYYY-MM-DD')] */
O_RETURN_VALUE out varchar2/*返回结果*/
) is
begin
O_RETURN_VALUE:=F_EXECUTE_FORMULA(I_FORMULA_ID,IO_PARA_LIST);
if O_RETURN_VALUE is null then
--ERR 表达式结果为空
P_ERROR(2005,I_FORMULA_ID);
end if;
end P_EXECUTE_FORMULA;
function test return varchar2
is
m_para_list varchar2(4096);
m_temp_value varchar2(100);
begin
-- m_temp_value:=f_get_Prod_Policy_Year_Cancel((37868),(to_date('20020501','YYYYMMDD')));
--- m_para_list:='[ITEM_ID=37868][CACU_DATE=to_date(''20020501'',''YYYYMMDD'')]';
-- return f_get_normal_formula_value('power(3,3)');
-- return F_GET_PARA_FROM_FORMULA('[select] max(policy_code) from t_policy');
-- return F_REPLACE_PARA_WITH_VALUE('[name] hi','name','jason');
-- return F_GET_VALUE_FROM_PARA_LIST('[name=''jason''][age=23][birthday=to_date(''20020401'',''YYYYMMDD'')]','birthday');
m_para_list:='[ITEM_ID=37868][CACU_DATE=to_date(''20020501'',''YYYYMMDD'')]';
return F_EXECUTE_FORMULA('退保金公式3',m_para_list);
end test;
/*将日期类型数据转换成字符串,例如 日期 2001/1/1 --> to_date('2001/1/1','YYYY/MM/DD') */
function f_date_to_string(
I_DATE in date
) return varchar2
is
begin
if I_DATE is null then
return 'null';
end if;
return 'to_date('''||to_char(I_DATE,'YYYY-MM-DD')||''',''YYYY-MM-DD'')';
end;
/*将字符类型数据转换成字符串,例如 字符串 'abc'--> '''abc''' */
function f_str_to_string(
I_STR in varchar2
) return varchar2
is
begin
if I_STR is null then
return '''''';
else
return ''''||I_STR||'''';
end if;
end;
end PKG_LIFE_PUBLIC_FORMULA;
/