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;
但是这样的话,性能很差。主要是插入的时候。查询还凑合,搞不懂,有点。
这样看来,还是用自身带的函数性能好啊。