学步园

 insert into t_subid_basicfee
    select cc.subscriberid, cc.offprice
      from (select a.subscriberid,
                   b.favrulepri,
                   b.offprice,
                   rank() over(partition by a.subscriberid order by b.favrulepri desc) num
              from t_subrelationreads a, t_fav_cond_price b
             where a.subscriberid = NVL(b.subscriberid, a.subscriberid)
               and a.usertype = NVL(b.subtype, a.usertype)
               and a.mastermachine = NVL(b.mastermachine, a.mastermachine)
               and decode(b.seqoperator,
                          '=',
                          decode(sign(a.seqno - NVL(b.seqno, a.seqno)),0,1,0),
                          '<=',
                          decode(sign(a.seqno - NVL(b.seqno, a.seqno)),-1,1,0),
                          '>=',
                          decode(sign(a.seqno - NVL(b.seqno, a.seqno)),1,1,0),
                          1) = 1
               and a.customercode = NVL(b.customerid, a.customercode)
               and a.customertype = NVL(b.customertype, a.customertype)
               and a.customerclass = NVL(b.customerclass, a.customerclass)
               and b.productcode = '10000024') cc
     where cc.num = 1;

中间一段 decode(b.seqoperator,
                          '=',
                          decode(sign(a.seqno - NVL(b.seqno, a.seqno)),0,1,0),
                          '<=',
                          decode(sign(a.seqno - NVL(b.seqno, a.seqno)),-1,1,0),
                          '>=',
                          decode(sign(a.seqno - NVL(b.seqno, a.seqno)),1,1,0),
                          1) = 1

本来是可以用函数function代替的，如：

create or replace function F_COMPARE_VARCHAR
(srcstr  in varchar2, --源字符
 oper    in varchar2, --操作符
 deststr in varchar2 --目标字符
 ) return number as
  flag number;
begin
  if srcstr is null or oper is null or deststr is null then
    return 1;
  end if;

  execute immediate 'select case when ' || srcstr || oper || deststr ||
                    ' then 1 else 0 end' || chr(13) || 'from dual'
    into flag;
  return flag;
end F_COMPARE_VARCHAR;

但是这样的话，性能很差。主要是插入的时候。查询还凑合，搞不懂，有点。

这样看来，还是用自身带的函数性能好啊。