项目从Oracle数据迁移到GBase数据库时解决适配遇到的问题

红太狼 2021-11-10 20:36 455阅读 0赞

### 项目从Oracle数据迁移到GBase数据库时解决适配遇到的问题 ###

*  一，oracle中nvl()函数
 *  二，oracle数据库中sql语句中with as 的用法
 *  三，Oracle中的group by问题
 *  四，trunc()的用法
 *  五，批量插入
 *  （知识点）Oracle批量更新
 *  六，Oracle数据库和GBase数据库的差异
 *   *   *  6.1 <=
         *  6.2、groupby
         *  6.3、with别名as
         *  6.4、to\_char()
         *  6.5、批量插入
         *  6.6、sysdate和current
         *  6.7、多表连接
         *  6.8、当前时间now()和current
         *  6.9、trunc()函数
 *  七，多表连接
 *  八，XMLAGG函数
 *  九，to\_char()用法
 *  十，查看当前月份trunc( )函数
 *  十一，group by
 *  十二，查看当前星期的星期一的日期

# 一，oracle中nvl()函数 #

NVL（表达式1，表达式2）  
如果表达式1为空值，NVL返回值为表达式2的值，否则返回表达式1的值。该函数的目的是把一个空值（null）转换成一个实际的值。其表达式的值可以是数字型、字符型和日期型。但是表达式1和表达式2的数据类型必须为同一个类型。

# 二，oracle数据库中sql语句中with as 的用法 #

相当于建了个e临时表

with e as (select * from scott.emp e where e.empno=7499)
    select * from e;

# 三，Oracle中的group by问题 #

![在这里插入图片描述][20190803182745123.png]  
这条查询语句最后的group by在oracle数据库中可以那样写，但是在gbase中不可以，因为上面已经把happen\_time这个时间起了别名了，oracle数据库中，group by后面不能跟别名，gbase只能写别名，gbase只认识上面已经起了别名的名字。

适配gbase正确语句：

select to_char(happen_time,'yyyy-MM-dd') as happen_date,count(1) as cn from dy_work_reminder a 
          where a.dm_entry_person = '1'
          and to_char(a.happen_time,'yyyy-MM')='2019-06'
          and a.yxbz='Y' group by happen_date

# 四，trunc()的用法 #

在oracle中，trunc()的用法

1.select trunc(sysdate) from dual --2011-3-18 今天的日期为2011-3-18
    2.select trunc(sysdate, 'mm')  from  dual --2011-3-1 返回当月第一天.
    3.select trunc(sysdate,'yy') from dual --2011-1-1 返回当年第一天
    4.select trunc(sysdate,'dd') from dual --2011-3-18 返回当前年月日
    5.select trunc(sysdate,'yyyy') from dual --2011-1-1 返回当年第一天
    6.select trunc(sysdate,'d') from dual --2011-3-13 (星期天)返回当前星期的第一天
    7.select trunc(sysdate, 'hh') from dual  --2011-3-18 14:00:00 当前时间为14:41 
    8.select trunc(sysdate, 'mi') from dual --2011-3-18 14:41:00 TRUNC()函数没有秒的精确

Oracle获取当前月份的第一天的sql语句

select trunc(sysdate, 'mm') from dual;

Gbase获取当前月份的第一天的sql语句

select trunc(current,'month') from sysmaster:sysdual;

注意：oracle中的dual是虚拟表，而gbase中的sysmaster:sysdual表同样是gbase的虚表。

# 五，批量插入 #

**oracle批量插入**  
接口

int insterZqyjList(List<YzsylgkZqyj> zqyjList);

xml

<insert id="insterZqyjList" parameterType="List">
      insert into YZSYLGK_ZQYJ (YJ_ID, ID, YJ_NR,
      TCR_NAME, TCRDW, CREATEDATE,
      MODIFYDATE, USER_ID, ORG_ID
      ) select a.* from(
      <foreach collection="list" item="item" index="index" separator="union" open="(" close=")">
        select #{item.yjId,jdbcType=VARCHAR}, #{item.id,jdbcType=VARCHAR}, #{item.yjNr,jdbcType=VARCHAR},
        #{item.tcrName,jdbcType=VARCHAR}, #{item.tcrdw,jdbcType=VARCHAR}, #{item.createdate,jdbcType=TIMESTAMP},
        #{item.modifydate,jdbcType=TIMESTAMP}, #{item.userId,jdbcType=VARCHAR}, #{item.orgId,jdbcType=VARCHAR} from dual
      </foreach>
      )a
    </insert>

**适配GBase写法**

<insert id="insterZqyjList" parameterType="List" databaseId="gbase">
      insert into YZSYLGK_ZQYJ (YJ_ID, ID, YJ_NR,
      TCR_NAME, TCRDW, CREATEDATE,
      MODIFYDATE, USER_ID, ORG_ID
      ) select a.* from(
      <foreach collection="list" item="item" index="index" separator="union">
        select '${item.yjId}' as YJ_ID, '${item.id}' as ID, '${item.yjNr}' as YJ_NR,
        '${item.tcrName}'as TCR_NAME, '${item.tcrdw}' as TCRDW,
        <choose>
          <when test="item.createdate ='' or item.createdate=null">
            get_datetime('${item.createdate}') as CREATEDATE
          </when>
          <otherwise>
            '' as CREATEDATE,
          </otherwise>
        </choose>
    
        <choose>
          <when test="item.modifydate ='' or item.modifydate=null">
            get_datetime('${item.modifydate}') as MODIFYDATE,
          </when>
          <otherwise>
            '' as MODIFYDATE,
          </otherwise>
        </choose>
        '${item.userId}' as USER_ID, '${item.orgId}' as ORG_ID  from dual
      </foreach>
      )a
    </insert>

Choose / when / otherwise是为了保证入参不为空，如果为空则前后插入的数量不匹配报错，如果为空则返回空字符串。

# （知识点）Oracle批量更新 #

xml写法如下

<update id="updateBatch" parameterType="java.util.List">
        <foreach collection="list" item="item" index="index" open="begin" close=";end;" separator=";">
            update T_CITY_INDEX t
            set
            t.city_name= #{item.cityName,jdbcType=VARCHAR} ,
            t.district_name= #{item.districtName,jdbcType=VARCHAR} ,
            where t.id = #{item.id,jdbcType=NUMERIC}
        </foreach>
    </update>

# 六，Oracle数据库和GBase数据库的差异 #

Oracle数据库和GBase数据库的差异如下：

### 6.1 <= ###

Oracle：\#\{starttime\}<=t.audit  
Gbase：\#\{starttime\}year to function(5)<=t.audit

### 6.2、groupby ###

Oracle：group by to\_char(sysdate,’yyyy-MM’)  
Gbase：group by 后面不能直接跟to\_char(sysdate,’yyyy-MM’)，需要先给  
to\_char起一个别名例如a，然后group b ya

### 6.3、with别名as ###

Oracle：在oracle数据库中可以使用with别名as的用法，相当于建了一张临时  
表，例如  
with tmp as(select \* from test)  
Selecta.test1,b.test2 from try a,tmp b where a.id=b.id  
GBase：在GBase中不可以使用with别名as的用法，只能这样写  
Selecta.test1,b.test2 from try a,(select \* from test) b where a.id=b.id

### 6.4、to\_char() ###

Oracle：select to\_char(a.id) id from…  
GBase：select ‘a.id’ id from…

### 6.5、批量插入 ###

Oracle：在foreach标签中接收yjId参数时是这样接收的  
\#\{item.yjId,jdbcType=VARCHAR\}  
GBase：在foreach标签中接受yjId参数时是这样接收的  
‘$\{item.yjId\}’ as YJ\_ID  
注意：这里有一对单引号

### 6.6、sysdate和current ###

Oracle：查询当前时间使用sysdate Select sysdate from dual;  
GBase：查询当前时间使用current Select current from sysmaster:sysdual;  
（sysmaster:sysdual是GBase的虚表）

### 6.7、多表连接 ###

Oracle：可以使用（+）来连接各个表  
GBase：不支持（+）用法，可以使用左连接leftjoin…on…来作连接查询。

### 6.8、当前时间now()和current ###

Oracle：小于当前时间的写法：<=now()  
GBase：小于当前时间的写法：<=current

### 6.9、trunc()函数 ###

Oracle：trunc(sysdate)获取当前的日期  
GBase：trunc(current year to fraction(5))

# 七，多表连接 #

oracle的写法

select a.title,a.begintime,null as END_DATE ,a.compere,a.org_id,a.typeid,a.id,a.xindeandbiji,a.meeting_type,c.org_name,le.STATE,
        le.xdbgid,bj.STATE bjzt,bj.xdbgid bjid,d.ztdrlx,d.themetype,
        b.user_id,a.recuserid,a.hyjy_state,null as bmzt,null as fczs_id
        from meeting_main a,meeting_user b,g_organ c,meeting_ztdr d,LEARNING_NOTES le,LEARNING_NOTES bj
        where a.org_id=c.org_id
        and a.id = b.mainid
        and a.id = d.id
        and a.id = le.PROJECTID(+)
        and a.id = bj.PROJECTID(+)
        and le.type(+) = '0'
        and bj.type(+) = '1'
        and b.user_id = #{userId,jdbcType=VARCHAR}

gbase的（+）不适配，需要统统都改成left join 表名 on 条件

gbase的写法

select a.title,a.begintime,null as END_DATE ,a.compere,a.org_id,a.typeid,a.id,a.xindeandbiji,a.meeting_type,c.org_name,le.STATE,
        le.xdbgid,bj.STATE bjzt,bj.xdbgid bjid,d.ztdrlx,d.themetype,
        b.user_id,a.recuserid,a.hyjy_state,null as bmzt,null as fczs_id
        from
        meeting_main a left join meeting_user b on
        a.id = b.mainid left join g_organ c on
        a.org_id = c.org_id left join meeting_ztdr d on
        a.id = d.id left join LEARNING_NOTES le on
        a.id = le.PROJECTID left join LEARNING_NOTES bj on
        a.id = bj.PROJECTID
        where
        b.user_id = '1'

# 八，XMLAGG函数 #

oracle的写法

select substr(aorgid,0,length(aorgid)-1) aorgid,substr(aorg_name,0,length(aorg_name)-1) aorg_name from (
      	  select XMLAGG(XMLELEMENT(E,orgid || ',')).EXTRACT('//text()').getclobval() aorgid,
      	         XMLAGG(XMLELEMENT(E,org.org_name || ',')).EXTRACT('//text()').getclobval() aorg_name 
      	         from exam_exam_org b,g_organ org where b.orgid=org.org_id
      	   and b.exid=#{exid,jdbcType=VARCHAR}
      	) a

gbase的写法

select substr(aorgid,0,length(aorgid)-1) aorgid,substr(aorg_name,0,length(aorg_name)-1) aorg_name from (
          select WM_concat(orgid) aorgid,
                 WM_concat(org.org_name) aorg_name
                 from exam_exam_org b,g_organ org where b.orgid=org.org_id
           and b.exid=#{exid,jdbcType=VARCHAR}
        ) a

# 九，to\_char()用法 #

oracle的写法

SELECT
                to_char(a.id) id,
                a.SCOREPROJECT,
                b.USERID,
                to_char(b.score) score,
                to_char(a.SCORESUM) scoresum
            FROM
                score_ruler a,
                ( SELECT USERID, SCOREPROJECTID, sum( score ) score FROM SCORE_LOG WHERE USERID = #{userid,jdbcType=VARCHAR} GROUP BY USERID, SCOREPROJECTID ) b
            WHERE
                a.ID = b.SCOREPROJECTID ( + )

gbase的写法

SELECT
                'a.id' id,
                a.SCOREPROJECT,
                b.USERID,
                'b.score' score,
                'a.SCORESUM' scoresum
            FROM
                score_ruler a,
                ( SELECT USERID, SCOREPROJECTID, sum( score ) score FROM SCORE_LOG WHERE USERID = #{userid,jdbcType=VARCHAR} GROUP BY USERID, SCOREPROJECTID ) b
            WHERE
                a.ID = b.SCOREPROJECTID ( + )

# 十，查看当前月份trunc( )函数 #

oracle的写法

`select trunc(sysdate,'mm') from dual;`

gbase的写法

`select trunc(current year to fraction(5),'month') from dual;`

# 十一，group by #

oracle写法

<select id="selectPartyAge" parameterType="java.lang.String"
                resultType="cn.com.qianlong.light.vo.dy.DyTjCxVo">
            SELECT BB.name,nvl(AA.value,'0')value
            FROM
            (SELECT
            CASE
            WHEN TO_CHAR(z.ybdybeginDate,'YYYYMMDD') <= '19490930' THEN '1949之前'
            WHEN TO_CHAR(z.ybdybeginDate,'YYYYMMDD') >= '19491001'
            AND TO_CHAR(z.ybdybeginDate,'YYYYMMDD')  <= '19660430' THEN '1949~1966'
            WHEN TO_CHAR(z.ybdybeginDate,'YYYYMMDD') >= '19660501'
            AND TO_CHAR(z.ybdybeginDate,'YYYYMMDD')  <= '19761031' THEN '1966~1976'
            WHEN TO_CHAR(z.ybdybeginDate,'YYYYMMDD') >= '19761101'
            AND TO_CHAR(z.ybdybeginDate,'YYYYMMDD')  <= '19781231' THEN '1976~1978'
            WHEN TO_CHAR(z.ybdybeginDate,'YYYYMMDD') >= '19790101'
            AND TO_CHAR(z.ybdybeginDate,'YYYYMMDD')  <= '20021031' THEN '1979~2002'
            WHEN TO_CHAR(z.ybdybeginDate,'YYYYMMDD') >= '20021101'
            AND TO_CHAR(z.ybdybeginDate,'YYYYMMDD')  <= '20121031' THEN '2002~2012'
            WHEN TO_CHAR(z.ybdybeginDate,'YYYYMMDD') >= '20121101' THEN '2012之后'
            END as name , COUNT(z.ybdybeginDate) AS value
            FROM (
            SELECT a.user_id, a.ybdybeginDate, a.partyorganId FROM DY_INFO a,G_ORGAN b
            where
            a.PARTYORGANID like  CONCAT('%',CONCAT(#{partyorganid,jdbcType=VARCHAR},'%'))
            and a.status  IN (1,4)
            AND a.userType IN (4, 5)
            AND
            a.PARTYORGANID=b.ORG_ID
            and
            b.VALIDFLAG = 1
            ) z
            GROUP BY CASE
            WHEN TO_CHAR(z.ybdybeginDate,'YYYYMMDD') <= '19490930' THEN '1949之前'
            WHEN TO_CHAR(z.ybdybeginDate,'YYYYMMDD') >= '19491001'
            AND TO_CHAR(z.ybdybeginDate,'YYYYMMDD')  <= '19660430' THEN '1949~1966'
            WHEN TO_CHAR(z.ybdybeginDate,'YYYYMMDD') >= '19660501'
            AND TO_CHAR(z.ybdybeginDate,'YYYYMMDD')  <= '19761031' THEN '1966~1976'
            WHEN TO_CHAR(z.ybdybeginDate,'YYYYMMDD') >= '19761101'
            AND TO_CHAR(z.ybdybeginDate,'YYYYMMDD')  <= '19781231' THEN '1976~1978'
            WHEN TO_CHAR(z.ybdybeginDate,'YYYYMMDD') >= '19790101'
            AND TO_CHAR(z.ybdybeginDate,'YYYYMMDD')  <= '20021031' THEN '1979~2002'
            WHEN TO_CHAR(z.ybdybeginDate,'YYYYMMDD') >= '20021101'
            AND TO_CHAR(z.ybdybeginDate,'YYYYMMDD')  <= '20121031' THEN '2002~2012'
            WHEN TO_CHAR(z.ybdybeginDate,'YYYYMMDD') >= '20121101' THEN '2012之后'
            END
            )AA RIGHT JOIN (
            select '1949之前' as name, '1' as xh from dual
            union
            select '1949~1966' as name, '2' as xh from dual
            union
            select '1966~1976' as name, '3' as xh from dual
            union
            select '1976~1978' as name, '4' as xh from dual
            union
            select '1979~2002' as name, '5' as xh from dual
            union
            select '2002~2012' as name, '6' as xh from dual
            union
            select '2012之后' as name, '7' as xh from dual
            )BB on AA.name=BB.name
            ORDER BY BB.xh
        </select>

gbase写法

<select id="selectPartyAge" parameterType="java.lang.String"
                resultType="cn.com.qianlong.light.vo.dy.DyTjCxVo">
            SELECT BB.name,nvl(AA.value,'0')value
            FROM
            (SELECT
            CASE
            WHEN TO_CHAR(z.ybdybeginDate,'YYYYMMDD') <= '19490930' THEN '1949之前'
            WHEN TO_CHAR(z.ybdybeginDate,'YYYYMMDD') >= '19491001'
            AND TO_CHAR(z.ybdybeginDate,'YYYYMMDD')  <= '19660430' THEN '1949~1966'
            WHEN TO_CHAR(z.ybdybeginDate,'YYYYMMDD') >= '19660501'
            AND TO_CHAR(z.ybdybeginDate,'YYYYMMDD')  <= '19761031' THEN '1966~1976'
            WHEN TO_CHAR(z.ybdybeginDate,'YYYYMMDD') >= '19761101'
            AND TO_CHAR(z.ybdybeginDate,'YYYYMMDD')  <= '19781231' THEN '1976~1978'
            WHEN TO_CHAR(z.ybdybeginDate,'YYYYMMDD') >= '19790101'
            AND TO_CHAR(z.ybdybeginDate,'YYYYMMDD')  <= '20021031' THEN '1979~2002'
            WHEN TO_CHAR(z.ybdybeginDate,'YYYYMMDD') >= '20021101'
            AND TO_CHAR(z.ybdybeginDate,'YYYYMMDD')  <= '20121031' THEN '2002~2012'
            WHEN TO_CHAR(z.ybdybeginDate,'YYYYMMDD') >= '20121101' THEN '2012之后'
            END as dd , COUNT(z.ybdybeginDate) AS value
            FROM (
            SELECT a.user_id, a.ybdybeginDate, a.partyorganId FROM DY_INFO a,G_ORGAN b
            where
            a.PARTYORGANID like  CONCAT('%',CONCAT(#{partyorganid,jdbcType=VARCHAR},'%'))
            and a.status  IN (1,4)
            AND a.userType IN (4, 5)
            AND
            a.PARTYORGANID=b.ORG_ID
            and
            b.VALIDFLAG = 1
            ) z
            GROUP BY dd
            )AA RIGHT JOIN (
            select '1949之前' as name, '1' as xh from dual
            union
            select '1949~1966' as name, '2' as xh from dual
            union
            select '1966~1976' as name, '3' as xh from dual
            union
            select '1976~1978' as name, '4' as xh from dual
            union
            select '1979~2002' as name, '5' as xh from dual
            union
            select '2002~2012' as name, '6' as xh from dual
            union
            select '2012之后' as name, '7' as xh from dual
            )BB on AA.name=BB.name
            ORDER BY BB.xh
        </select>

# 十二，查看当前星期的星期一的日期 #

oracle写法

select trunc(sysdate,'iw') from dual;

gbase写法

select trunc(current,'day')+1 from dual;

参考gbase官方文档  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDAwMTk2NQ_size_16_color_FFFFFF_t_70]

[20190803182745123.png]: /images/20211109/f90c13a626304fdd87931f77b270501a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDAwMTk2NQ_size_16_color_FFFFFF_t_70]: /images/20211109/ba1a83e8db624b969eb233d50b7f7eaf.png