当前位置：首页 → 问答吧 → 一个极其诡异的问题

一个极其诡异的问题

时间：2011-09-08

来源：互联网

进入不了IF语句里面去, 想不通

代码:

[Copy to clipboard] [ - ]

CODE:

CREATE OR REPLACE FUNCTION SPCOMMISIONCOMPUTE
(
  SP_VERID       INTEGER
,SP_STATDATE    DATE
,SP_PID       VARCHAR2
,SP_DUEPAYDATE DATE
,SP_ATTACHDATE DATE
,SP_ISCTYPE    VARCHAR2
,SP_INSUREDAGE INT
,SP_PAYPERIOD VARCHAR2
,SP_PAYTERM    VARCHAR2
,SP_PAYTERMTAG INT
,SP_ORPHANTAG INT
,SP_PREMIUM    NUMBER
,SP_INSURANCEDUR NUMBER
,SP_BUSINESSTYPE VARCHAR2
,SP_HAPPENDATE DATE
,SP_MASTERTAG VARCHAR2
,SP_MOMEYIN_NUM  INT
,SP_POLICYNO    VARCHAR2
,SP_CNTRTYPE    VARCHAR2
) RETURN NUMBER AS
  SP_POLCODE VARCHAR2(3);
  SP_COMMISION NUMBER(12, 2);
BEGIN

  plog.debug('2222222222 SP_POLICYNO[' || SP_POLICYNO || ']');

  SP_POLCODE := SP_ISCTYPE;

[color=Red]  IF substr(SP_POLICYNO, 1, 26) = '20116530216227000005096220' THEN
plog.debug('11111111111111');
SP_POLCODE := substr(SP_POLICYNO, 11, 3);
  END IF;[/color]

  SP_COMMISION := spcommisioncompute_orig(sp_verid => sp_verid,
                                       sp_statdate => sp_statdate,
                                       sp_pid => sp_pid,
                                       sp_duepaydate => sp_duepaydate,
                                       sp_attachdate => sp_attachdate,
                                       sp_isctype => SP_POLCODE,
                                       sp_insuredage => sp_insuredage,
                                       sp_payperiod => sp_payperiod,
                                       sp_payterm => sp_payterm,
                                       sp_paytermtag => sp_paytermtag,
                                       sp_orphantag => sp_orphantag,
                                       sp_premium => sp_premium,
                                       sp_insurancedur => sp_insurancedur,
                                       sp_businesstype => sp_businesstype,
                                       sp_happendate => sp_happendate,
                                       sp_mastertag => sp_mastertag,
                                       sp_momeyin_num => sp_momeyin_num,
                                       sp_policyno => sp_policyno,
                                       sp_cntrtype => sp_cntrtype);

  RETURN SP_COMMISION;
EXCEPTION
  WHEN OTHERS THEN
plog.error('佣金计算错误');
plog.error(rpad('SP_VERID', 20, '.') || '[' || SP_VERID || ']');
plog.error(rpad('SP_STATDATE', 20, '.') || '[' || SP_STATDATE || ']');
plog.error(rpad('SP_PID', 20, '.') || '[' || SP_PID || ']');
plog.error(rpad('SP_DUEPAYDATE', 20, '.') || '[' || SP_DUEPAYDATE || ']');
plog.error(rpad('SP_ATTACHDATE', 20, '.') || '[' || SP_ATTACHDATE || ']');
plog.error(rpad('SP_ISCTYPE', 20, '.') || '[' || SP_ISCTYPE || ']');
plog.error(rpad('SP_POLCODE', 20, '.') || '[' || SP_POLCODE || ']');
plog.error(rpad('SP_INSUREDAGE', 20, '.') || '[' || SP_INSUREDAGE || ']');
plog.error(rpad('SP_PAYPERIOD', 20, '.') || '[' || SP_PAYPERIOD || ']');
plog.error(rpad('SP_PAYTERM', 20, '.') || '[' || SP_PAYTERM || ']');
plog.error(rpad('SP_PAYTERMTAG', 20, '.') || '[' || SP_PAYTERMTAG || ']');
plog.error(rpad('SP_ORPHANTAG', 20, '.') || '[' || SP_ORPHANTAG || ']');
plog.error(rpad('SP_PREMIUM', 20, '.') || '[' || SP_PREMIUM || ']');
plog.error(rpad('SP_INSURANCEDUR', 20, '.') || '[' || SP_INSURANCEDUR || ']');
plog.error(rpad('SP_BUSINESSTYPE', 20, '.') || '[' || SP_BUSINESSTYPE || ']');
plog.error(rpad('SP_HAPPENDATE', 20, '.') || '[' || SP_HAPPENDATE || ']');
plog.error(rpad('SP_MASTERTAG', 20, '.') || '[' || SP_MASTERTAG || ']');
plog.error(rpad('SP_MOMEYIN_NUM', 20, '.') || '[' || SP_MOMEYIN_NUM || ']');
plog.error(rpad('SP_POLICYNO', 20, '.') || '[' || SP_POLICYNO || ']');
plog.error(rpad('SP_CNTRTYPE', 20, '.') || '[' || SP_CNTRTYPE || ']');
RAISE;
END;

日志表的数据:

[Copy to clipboard] [ - ]

CODE:

SP_CNTRTYPE.........[G]
SP_POLICYNO.........[20116530216227000005096220]
SP_MOMEYIN_NUM......[1]
SP_MASTERTAG........[S]
SP_HAPPENDATE.......[2011/06/30 00:00:00]
SP_BUSINESSTYPE.....[01]
SP_INSURANCEDUR.....[1]
SP_PREMIUM..........[40320]
SP_ORPHANTAG........[0]
SP_PAYTERMTAG.......[1]
SP_PAYTERM..........[00]
SP_PAYPERIOD........[W]
SP_INSUREDAGE.......[36]
SP_POLCODE..........[]
SP_ISCTYPE..........[纫滥]
SP_ATTACHDATE.......[2011/07/01 00:00:00]
SP_DUEPAYDATE.......[2011/07/01 00:00:00]
SP_PID..............[65302100000315]
SP_STATDATE.........[2011/08/01 00:00:00]
SP_VERID............[20051]
佣金计算错误
2222222222 SP_POLICYNO[20116530216227000005096220]
2222222222 SP_POLICYNO[20116530216277000005336270]
2222222222 SP_POLICYNO[20116530216227000007326220]
2222222222 SP_POLICYNO[20116530217047000007207040]
2222222222 SP_POLICYNO[20116530217047000007206320]
2222222222 SP_POLICYNO[20116530217047000007047040]
2222222222 SP_POLICYNO[20116530217047000007176320]
2222222222 SP_POLICYNO[20116530217047000007046320]
2222222222 SP_POLICYNO[20116530217047000007177040]
2222222222 SP_POLICYNO[20116530217047000006946320]
2222222222 SP_POLICYNO[20116530217047000006947040]

[ 本帖最后由 KevinLee39 于 2011-9-8 12:04 编辑 ]

作者: KevinLee39 发布时间: 2011-09-08

SP_POLCODE VARCHAR2(3); SP_ISCTYPE..........[纫滥] SP_POLCODE := SP_ISCTYPE;
SP_POLCODE只有3bytes，SP_ISCTYPE 4 bytes异常。

作者: 昨夜袜子发布时间: 2011-09-08

SP_ISCTYPE这也不一定是4bytes要看字符集。但是异常肯定就是这里出的。

作者: 昨夜袜子发布时间: 2011-09-08

QUOTE:原帖由昨夜袜子于 2011-9-8 12:19 发表
SP_POLCODE VARCHAR2(3); SP_ISCTYPE..........[纫滥] SP_POLCODE := SP_ISCTYPE;
SP_POLCODE只有3bytes，SP_ISCTYPE 4 bytes异常。

就是发现这里ec程序传入的参数有问题, 所以在外面套了一个过程, 要是这个判断成功, 则修改SP_POLCODE

作者: KevinLee39 发布时间: 2011-09-08

哦, 知道了, 是在赋值这里就出现异常了, 后面的IF就没有运行.

我晕啊. 呵呵.

作者: KevinLee39 发布时间: 2011-09-08