PLSQL 自定义表达式求值
来源：互联网 发布：linux怎么编程 编辑：程序博客网 时间：2024/04/26 11:18
/ * 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 varchar2isbegin 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 varchar2isbegin if I_STR is null then return ''''''; else return ''''||I_STR||''''; end if;end; end PKG_LIFE_PUBLIC_FORMULA;/- PLSQL 自定义表达式求值
- 自定义函数的表达式求值类
- 表达式求值
- 表达式求值
- 表达式求值
- 表达式求值
- 表达式求值
- 表达式求值
- 表达式求值
- 表达式求值
- 表达式求值
- 表达式求值
- 表达式求值
- 表达式求值
- 表达式求值
- 表达式求值
- 表达式求值
- 表达式求值
- sap视频教学
- 正则表达式学习笔记
- EditPlus 处理中文乱码
- WIN32编程模板
- 常用快捷键 win+r win+l
- PLSQL 自定义表达式求值
- Zigbee协议栈（CC2530开发板） 修改发射功率
- php中几种常见安全设置详解发布
- URL拼接的转码问题
- python字符串操作
- 中国人口金字塔
- CSS中属性的书写顺序
- MySQL学习笔记——备份和恢复
- Shiro框架Web环境下过滤器结构分析
