GHGL用到的sql，Oracle数据库

1.union  与  union all

union 会把两个集合中的相同项合并，union all 则不会

2.连字典表，查字典含义

		select BF.Bfdm,BF.Qybm,
		(select zdhy from gh_zd where zdlx = 'BFZT' and zddm = BF.Bfzt) as bfzt
		from 
		gh_fp_bfqd BF
		where BF.Fpdm = #{payTerm,jdbcType=VARCHAR}

3.日期型字段转换为字符串输出

to_char(字段,'yyyy-MM-dd HH24:mi:ss')

4.模糊匹配  like

like '%'||#{参数名,jdbcType=VARCHAR}||'%'

5.参数传递

		<if test="payResult != null and payResult != '7'.toString()">
		and BF.Zfjg = #{payResult,jdbcType=VARCHAR}
		</if>

6.批量更新，注意分段提交

  <update id="updateCBbf" parameterType="map">
	update gh_fp_bfqd set sfsd = '1',sdfpdm = #{fpdm,jdbcType=VARCHAR}
	where bfdm in
	<foreach collection="list" item="item" index="index" open="(" close=")" separator=",">
		#{item.bfdm,jdbcType=VARCHAR}
	</foreach>
  </update>

7.批量插入，注意分段提交

  <insert id="buildPayForm" parameterType="java.util.List">
	insert into gh_fp_bfqd
	(BFDM,JBFDM,FPDM,JFPDM,QYBM,BFJE,FY,CZRYDM,BFZT,BFLB,CBCS,SBXH,HXMXID,JFLSDM,BZ,SFGH,LY)
	select Seq_ywls.Nextval,A.* from (   
	<foreach collection="list" item="item" index="index" separator="union all">
    select 		
    	#{item.jbfdm ,jdbcType=VARCHAR},
		#{item.fpdm ,jdbcType=VARCHAR},
		#{item.jfpdm ,jdbcType=VARCHAR},
   		#{item.qybm ,jdbcType=VARCHAR}, 
    	#{item.bfje ,jdbcType=DECIMAL}, 
    	#{item.fy ,jdbcType=VARCHAR}, 
    	#{item.czrydm ,jdbcType=VARCHAR}, 
    	#{item.bfzt ,jdbcType=VARCHAR},
    	#{item.bflb ,jdbcType=VARCHAR},
    	#{item.cbcs ,jdbcType=VARCHAR},
    	#{item.sbxh ,jdbcType=VARCHAR},
    	#{item.hxmxid ,jdbcType=VARCHAR},
    	#{item.jflsdm ,jdbcType=VARCHAR},
    	#{item.bz ,jdbcType=VARCHAR},
    	#{item.sfgh ,jdbcType=VARCHAR},
    	#{item.ly ,jdbcType=VARCHAR}
    from dual
	</foreach> 
	) A
  </insert>

8.将null值转换为空

nvl(QY.Khmc,' ') as khmc