sql常用写法
sql语句中(+)
(+) 表示外连接: 如果条件的一边出现（+），则另一边的表就是主表，主表中的所有记录都会出现，即使附表中有的记录为空
内连接
select a.name,b.money from a inner join b on a.name=b.name;
左连接
select a.name,b.money from a,b where a.name=b.name(+);select a.name,b.money from a left join b on a.name=b.name;
右连接
select a.name,b.money from a,b where a.name(+)=b.name;select a.name,b.money from a right join b on a.name=b.name;
全连接
select a.name,b.money from a full join b on a.name=b.name;select a.name,b.money from a,b where a.name(+)=b.name(+)
merge into
eg:如果不存在类型为stimulation_calc的待遇，则插入数据
MERGE INTO market_bonus_type T1
USING dual T2
ON (T1.BONUS_TYPE = 'STIMULATION_CALC')
WHEN NOT MATCHED THEN
insert (T1.BONUS_TYPE, T1.DESCRIPTION, T1.BONUS_BASE_TYPE, T1.PRIVILEGE)
values ('STIMULATION_CALC', '开门红现金激励', '', 'N');复制
case when
eg:
update ses_entrust
set entrust_status = case when hs_instr('01C', entrust_status) > 0 then
(case when :recordNo > 0 then '2'
else 'C' end)
else entrust_status end
decode
Select decode（columnname，值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值复制
eg:
decode(:char_config_1131,'1',hs_nvl(a.current_amount, 0) + hs_nvl(a.real_buy_amount, 0) -hs_nvl(a.real_sell_amount, 0),
'2',hs_nvl(a.current_amount, 0) + hs_nvl(a.real_buy_amount, 0) - hs_nvl(a.real_sell_amount, 0)+ hs_nvl(a.uncome_buy_amount, 0) - hs_nvl(a.uncome_sell_amount, 0),
hs_nvl(a.current_amount, 0)) as current_amount
nvl() instr() concat()
eg:
select hs_nvl(sum(entrust_amount - withdraw_amount - business_amount),0) from ses_entrust
+ where hs_instr(',0,1,C,2,3,4,7,', hs_concat(',', entrust_status, ',')) > 0
+ and hs_instr(',0,Q,R,S,T,U,V,R1,', hs_concat(',', entrust_prop, ',')) > 0
+ and hs_instr(',6,9,', hs_concat(',', entrust_type, ',')) > 0 + and exchange_type = :exchangeType
+ and stock_code = :stockCode + and entrust_bs = '1'
sign
sign()函数根据某个值是0、正数还是负数，分别返回0、1、-1
trim
(trim(?) is null or instr(','||?||',',','||a.stock_type||',') > 0)
如果所传参数为空，则(可涉及为不更新或者不作为查询条件)，如果有值，则(则更新或者作为查询条件)
