Oracle 常用函数/语法总结

case
  when ba.org is not null and ba.org = 'GD' then
   'GD'
  else
   'OL'
end "ORDER_SOURCE"

select decode(1,2,3,4,5,1,7) r from dual;--返回值是7

decode(1,2,3,4) 返回值是4 没有满足的情况取默认值 4
decode(1,1,3,4) 返回值是3

decode(crk.risklevel,
              1,
              'C1',
              2,
              'C2',
              3,
              'C3',
              4,
              'C4',
              5,
              'C5',
              'C1') "CUSTOMER_RISK_LEVEL"

where bsb.bankid = decode(t.tradechannel, 0, t.bankid, cc.bankid)

 where t.serialno = ba.serno(+)
   and t.custaccount = cc.custaccount(+)
   and t.account = c.account
   and t.issueid = i.issueid

to_date(bs.sysvalue, 'yyyymmdd')

to_number(rc.bancsno) "CUSTOMER_ID"

to_char(t.tradedate, 'YYYYMMDD') "ESTABLISH_DATE"

nvl(fp.price, p.offerprice) "NAV"

 trunc(i.recorddate) >= trunc(to_date(bs.sysvalue, 'yyyymmdd'))

 select sign(1012030123-12312412) from dual;  -- 结果返回1

nvl(to_char(case
             when t.attr = 0 and f.islockperiodissue != 1 and
                    t.trancode not in (0, 1, 2) then
                null
               else
                decode(ba.fudate, null, t.tradedate, ba.fudate)
             end,
             'HHmmss'),
     to_char(t.tradedate, 'hhmmss')) "ESTABLISH_TIME"

where t.serialno = ba.serno(+)
   and t.custaccount = cc.custaccount(+)
   and t.account = c.account
   and t.issueid = i.issueid
   and i.serialcode = f.serialcode(+)
   and bs.syscode = 'sys.date'
   and t.account = crk.account(+)
   and bsb.bankid = decode(t.tradechannel, 0, t.bankid, cc.bankid)
   and not ((t.trancode = 0 and t.state = 0 and
        trunc(i.recorddate) >= trunc(to_date(bs.sysvalue, 'yyyymmdd')) and
        i.state in (1, 4)) or
        (t.trancode = 2 and t.payamtdate is not null and t.state = 0 and
        trunc(t.payamtdate) <> trunc(to_date(bs.sysvalue, 'yyyymmdd'))))

decode(t.memo, '', '0000', '9999') "RETURN_CODE"