当前位置：首页 → 问答吧 → 函数replace的相关问题

函数replace的相关问题

时间：2011-09-02

来源：互联网

我要从数据库查询一条sql，替换里面的参数，然后执行这条sql。
写了个自定义的function ，替换这块有点不懂，问一下。

tCalSql := replace(trim(tCalSql),'?InsuYear?',tInsuYear);
tCalSql := replace(trim(tCalSql),'?InsuYearFlag?',tInsuYearFlag);
tCalSql := replace(trim(tCalSql),'?Amnt?',tAmnt);
tCalSql := replace(trim(tCalSql),'?Mult?',tMult);
tCalSql 是我查询出的sql语句，我担心的地方一是，替换后将结果交给自己，然后这么再次替换，会不会有问题？
第二，因为sql语句最多是这四个参数，也可能少于这四个，替换函数找不到需要替换的字符串会不会出错？我是一律全部替换。

最后就是第一次写function，编译后说无效，大家看看我的完整的程序，有多少错指出多少错吧。(*^__^*) ……
SQL code



----功能描述：查询短期险保费function
----输入参数：cContPlanCode  产品编码
----          cRiskCode      险种编码
----          cDutyCode      责任编码
----          cFlag          查询标志位,若为2，则查询该产品下该险种下该责任保费
----                                    若为1，则查询该产品下该险种保费(所有责任)，忽略输入的责任编码
----                                    若为0，则查询该产品保费(所有险种和及其责任)，忽略输入的险种和责任编码
---- by zhaojf 2011-09-02
create or replace function getSISPrem(cContPlanCode in varchar2,cRiskCode in varchar2,cDutyCode in varchar2,cFlag in INTEGER)
return NUMBER(12,2) as

tCursor           NUMBER;         --游标变量
tCalCode          VARCHAR2(6);    --算法编码
tCalSql           VARCHAR2(600);  --算法定义sql
tContPlantype     VARCHAR2(1);    --保费是否固定标志
tPrem             NUMBER(12,2);   --责任保费
tRiskPrem         NUMBER(12,2);   --险种保费
tTotalPrem        NUMBER(12,2);   --产品保费
tRiskCode         VARCHAR2(8);    --险种编码
tDutyCode         VARCHAR2(10);   --责任编码

tInsuYear         INTEGER;        --保险期间
tInsuYearFlag     VARCHAR2(1);    --保险期间类型
tAmnt             NUMBER(12,2);   --保额
tMult             NUMBER(20,5);   --份数

begin
    --变量初始化
    tPrem:=0;
    tRiskPrem:=0;
    tTotalPrem:=0;
    
    if (tFlag = 2) then    --根据责任算保费
        select a.ContPlantype into tContPlantype from sldplanrisk a where a.contplancode = cContPlanCode and a.riskcode = cRiskCode and a.dutycode = cDutyCode;
        --如果保费固定，则直接查询
        if(tContPlantype='0') then
            select a.Prem into tPrem from sldplanrisk a where a.contplancode = cContPlanCode and a.riskcode = cRiskCode and a.dutycode = cDutyCode;
        --如果保费不固定，则查询算法计算
        else
            select a.insuyear,a.insuyearflag,a.amnt,a.mult,a.calcoade into tInsuYear,tInsuYearFlag,tAmnt,tMult,tCalCode from sldplanrisk a where a.contplancode = cContPlanCode and a.riskcode = cRiskCode and a.dutycode = cDutyCode;
            select a.calsql into tCalSql from lmcalmode a where a.calcoade = tCalCode;
            tCalSql := replace(trim(tCalSql),'?InsuYear?',tInsuYear);   --此处传入参数,可能会出的问题就是没有替换完所有参数，而且算法定义select语句的参数必须按照规则首字母大写
            tCalSql := replace(trim(tCalSql),'?InsuYearFlag?',tInsuYearFlag);
            tCalSql := replace(trim(tCalSql),'?Amnt?',tAmnt);
            tCalSql := replace(trim(tCalSql),'?Mult?',tMult);
              
            tCursor := DBMS_SQL.OPEN_CURSOR;
            DBMS_SQL.PARSE(tCursor, tCalSql, DBMS_SQL.native); 
            DBMS_SQL.DEFINE_COLUMN(tCursor,1,tPrem);  --将结果第一列绑定到变量t_temp_SumAmnt上
            tPrem := DBMS_SQL.EXECUTE(tCursor);       --执行该条SQL语句
        end if;
        DBMS_SQL.CLOSE_CURSOR(tCursor);
        return tPrem;  --返回责任保费
    end if;
    
    if (tFlag = 1) then    --根据险种算保费(该险种下所有责任)
        tCursor := DBMS_SQL.OPEN_CURSOR;
        --迭代所有责任
        cursor t_RiskSQLSet is select a.dutycode, a.contplantype from sldplanrisk a where a.contplancode = cContPlanCode and a.riskcode = cRiskCode;
        for t_RiskSQL in t_RiskSQLSet loop
            --如果保费固定，则直接查询
            if(t_RiskSQL.tContPlantype='0') then
                select a.Prem into tPrem from sldplanrisk a where a.contplancode = cContPlanCode and a.riskcode = cRiskCode and a.dutycode = t_RiskSQL.dutycode;
            --如果保费不固定，则查询算法计算
            else
                select a.insuyear,a.insuyearflag,a.amnt,a.mult,a.calcoade into tInsuYear,tInsuYearFlag,tAmnt,tMult,tCalCode from sldplanrisk a where a.contplancode = cContPlanCode and a.riskcode = cRiskCode and a.dutycode = t_RiskSQL.dutycode;
                select a.calsql into tCalSql from lmcalmode a where a.calcoade = t_RiskSQL.dutycode;
                tCalSql := replace(trim(tCalSql),'?InsuYear?',tInsuYear);   --此处传入参数,可能会出的问题就是没有替换完所有参数，而且算法定义select语句的参数必须按照规则首字母大写
                tCalSql := replace(trim(tCalSql),'?InsuYearFlag?',tInsuYearFlag);
                tCalSql := replace(trim(tCalSql),'?Amnt?',tAmnt);
                tCalSql := replace(trim(tCalSql),'?Mult?',tMult);
                
                DBMS_SQL.PARSE(tCursor, tCalSql, DBMS_SQL.native); 
                DBMS_SQL.DEFINE_COLUMN(tCursor,1,tPrem);  --将结果第一列绑定到变量t_temp_SumAmnt上
                tPrem := DBMS_SQL.EXECUTE(tCursor);       --执行该条SQL语句
            end if;
            --将责任保费累加
            tRiskPrem = tRiskPrem + tPrem;
        end loop;
        DBMS_SQL.CLOSE_CURSOR(tCursor);
        return tRiskPrem;  --返回险种保费
    end  if;
    
    if (tFlag = 0) then    --根据款式算保费(该产品下所有险种、责任)
        tCursor := DBMS_SQL.OPEN_CURSOR;
        --迭代所有险种
        cursor t_ContPlanSQLSet is select a.riskcode from sldplanrisk a where a.contplancode = cContPlanCode; 
        for t_ContPlanSQL in t_ContPlanSQLSet loop
            --迭代所有责任
            cursor t_RiskSQLSet is select a.dutycode, contplantype from sldplanrisk a where a.contplancode = cContPlanCode and a.riskcode = t_ContPlanSQL.riskcode;
            for t_RiskSQL in t_RiskSQLSet loop
                 --如果保费固定
                 if(t_RiskSQL.tContPlantype='0') then
                     select a.Prem into tPrem from sldplanrisk a where a.contplancode = cContPlanCode and a.riskcode = t_ContPlanSQL.riskcode and a.dutycode = t_RiskSQL.dutycode;
                 --如果保费不固定，则查询算法计算
                 else
                     select a.insuyear,a.insuyearflag,a.amnt,a.mult,a.calcoade into tInsuYear,tInsuYearFlag,tAmnt,tMult,tCalCode from sldplanrisk a where a.contplancode = cContPlanCode and a.riskcode = t_ContPlanSQL.riskcode and a.dutycode = t_RiskSQL.dutycode;
                     select a.calsql into tCalSql from lmcalmode a where a.calcoade = t_RiskSQL.dutycode;
                     tCalSql := replace(trim(tCalSql),'?InsuYear?',tInsuYear);   --此处传入参数,可能会出的问题就是没有替换完所有参数，而且算法定义select语句的参数必须按照规则首字母大写
                     tCalSql := replace(trim(tCalSql),'?InsuYearFlag?',tInsuYearFlag);
                     tCalSql := replace(trim(tCalSql),'?Amnt?',tAmnt);
                     tCalSql := replace(trim(tCalSql),'?Mult?',tMult);
                     
                     DBMS_SQL.PARSE(tCursor, tCalSql, DBMS_SQL.native); 
                     DBMS_SQL.DEFINE_COLUMN(tCursor,1,tPrem);  --将结果第一列绑定到变量t_temp_SumAmnt上
                     tPrem := DBMS_SQL.EXECUTE(tCursor);       --执行该条SQL语句
                 end if;
                 tRiskPrem = tRiskPrem + tPrem;  --将责任保费累加
            end loop;
            tTotalPrem = tTotalPrem + tRiskPrem; --将险种保费累加
        end loop;
        DBMS_SQL.CLOSE_CURSOR(tCursor);
        return tTotalPrem;  --返回产品保费
    end if;
    
end;
\