函数replace的相关问题

Question

我要从数据库查询一条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，编译后说无效，大家看看我的完整的程序，有多少错指出多少错吧。(*^__^*) ……
----功能描述：查询短期险保费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) astCursor           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;
\

Answer 1

问题一：替换后将结果交给自己，然后这么再次替换，会不会有问题？
没有问题问题二：因为sql语句最多是这四个参数，也可能少于这四个，替换函数找不到需要替换的字符串会不会出错？
不会出错

Answer 2

谁帮看下次程序有什么语法问题吗?业务上的就不用管了。能生成funtion，但是调用时说处于无效状态。
function图标上有个叉号。用plsql不知道怎么调试。。

Answer 3

我改成这样了，还是有语法错误
create or replace function getSISPrem(cContPlanCode in varchar2,cRiskCode in varchar2,cDutyCode in varchar2,cFlag in NUMBER,cInsuYear in NUMBER,cInsuYearFlag in varchar2,cAmnt in NUMBER,cMult in NUMBER)
return NUMBER  as
-------------------------------------------------------------------------------------------------------------
----功能描述：查询短期险保费function
----输入参数：cContPlanCode  产品编码
----          cRiskCode      险种编码
----          cDutyCode      责任编码
----          cFlag          查询标志位,若为2，则查询该产品下该险种下该责任保费
----                                    若为1，则查询该产品下该险种保费(所有责任)，忽略输入的责任编码
----                                    若为0，则查询该产品保费(所有险种和及其责任)，忽略输入的险种和责任编码
----          cInsuYear      保险期间(用以查询非固定保费)
----          cInsuYearFlag  保险期间类型(用以查询非固定保费)
----          cAmnt          保额(用以查询非固定保费)
----          cMult          份数(用以查询非固定保费)
---- by zhaojf 2011-09-02
-------------------------------------------------------------------------------------------------------------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);   --责任编码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.calcoade 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?',cInsuYear);   --此处传入参数,可能会出的问题就是没有替换完所有参数，而且算法定义select语句的参数必须按照规则首字母大写
            tCalSql := replace(trim(tCalSql),'?InsuYearFlag?',cInsuYearFlag);
            tCalSql := replace(trim(tCalSql),'?Amnt?',cAmnt);
            tCalSql := replace(trim(tCalSql),'?Mult?',cMult);
              
            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语句
            DBMS_SQL.CLOSE_CURSOR(tCursor);
        end if;
        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.calcoade into 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?',cInsuYear);   --此处传入参数,可能会出的问题就是没有替换完所有参数，而且算法定义select语句的参数必须按照规则首字母大写
                tCalSql := replace(trim(tCalSql),'?InsuYearFlag?',cInsuYearFlag);
                tCalSql := replace(trim(tCalSql),'?Amnt?',cAmnt);
                tCalSql := replace(trim(tCalSql),'?Mult?',cMult);
                
                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(t_RiskSQLSet);
        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.calcoade into 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?',cInsuYear);   --此处传入参数,可能会出的问题就是没有替换完所有参数，而且算法定义select语句的参数必须按照规则首字母大写
                     tCalSql := replace(trim(tCalSql),'?InsuYearFlag?',cInsuYearFlag);
                     tCalSql := replace(trim(tCalSql),'?Amnt?',cAmnt);
                     tCalSql := replace(trim(tCalSql),'?Mult?',cMult);
                     
                     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(t_RiskSQLSet);
        DBMS_SQL.CLOSE_CURSOR(t_ContPlanSQLSet);
        return tTotalPrem;  --返回产品保费
    end if;
    
end;
\

Answer 4

装个工具toad，调试蛮好用的。

Answer 5

游标使用问题，那位好心的告诉我，问题就解决了。
就省最后一个问题了。 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.calcoade into 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?',cInsuYear);   --此处传入参数,可能会出的问题就是没有替换完所有参数，而且算法定义select语句的参数必须按照规则首字母大写
                tCalSql := replace(trim(tCalSql),'?InsuYearFlag?',cInsuYearFlag);
                tCalSql := replace(trim(tCalSql),'?Amnt?',cAmnt);
                tCalSql := replace(trim(tCalSql),'?Mult?',cMult);
                
                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;

Answer 6

继续顶自己的帖子，语法问题全搞定了，剩下一个小问题。】            tCalSql := replace(trim(tCalSql),'?InsuYear?',cInsuYear);   --此处传入参数,可能会出的问题就是没有替换完所有参数，而且算法定义select语句的参数必须按照规则首字母大写
            tCalSql := replace(trim(tCalSql),'?InsuYearFlag?',cInsuYearFlag);
            tCalSql := replace(trim(tCalSql),'?Amnt?',cAmnt);
            tCalSql := replace(trim(tCalSql),'?Mult?',cMult);
            
            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语句我经过调试，tCalSql 语句是对的，然后执行，结果确返回的是0，DBMS_SQL.PARSE(tCursor, tCalSql, DBMS_SQL.native); 
            DBMS_SQL.DEFINE_COLUMN(tCursor,1,tPrem);  --将结果第一列绑定到变量t_temp_SumAmnt上
            tPrem := DBMS_SQL.EXECUTE(tCursor);  单步走这几句话也没错，结果就是不是想要的。感觉是没有执行sql语句。

Answer 7

楼主的代码只执行到EXECUTE还差后面的FETCH_ROWS和COLUMN_VALUE方法取出执，你只是执行了游标，但是还需要从游标中取出值能得到结果。