GHGL用到的sql，Oracle数据库

来源：互联网发布：社会化新媒体矩阵编辑：程序博客网时间：2024/05/22 07:09

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 bfztfrom gh_fp_bfqd BFwhere 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

阅读全文

1 0