Oracle踩过的坑

/*字段为字符型 查询指定日期之间数据*/
SELECT
count( * )
FROM
COMP_OUTPUT_INVOICE
WHERE
to_date( invoice_date, 'yyyy-mm-dd' ) BETWEEN to_date( TO_CHAR( LAST_DAY( ADD_MONTHS( SYSDATE, - 2 )) + 1, 'YYYY-MM-DD' ), 'yyyy-mm-dd' )
AND to_date(
TO_CHAR( LAST_DAY( ADD_MONTHS( SYSDATE, - 1 )), 'YYYY-MM-DD' ),'yyyy-mm-dd')
/*字段为日期类型 查时间*/
select to_char(sysdate,'yyyy-mm-dd') from dual;

Oracle的循环插入,和MySQL写法不一样…

<!--oracle写法-->
<insert id="saveReportDataModifyLog" parameterType="list">
   insert into TAX_REPORT_DATA_MODIFY_LOG
   (ID,
   SPREAD_WORKBOOK_TAG_ID,
   SPREAD_SHEET_TAG_ID,
   SPREAD_CELL_TAG_ID,
   ORIGINAL,
   PRESENT,
   REAASON,
   CREATE_BY,
   COMPANY_ID,
   CREATION_DATE,
   USERNAME,
   TASK_ID
   )
   select DXHY_TAX_SEQ_GRASP_DATA.NEXTVAL id,A.*
   from (
   <foreach collection="list" item="item" index="index" separator="UNION ALL">
       select
       #{item.spreadWorkbookTagId},
       #{item.spreadSheetTagId},
       #{item.spreadCellTagId},
       #{item.original},
       #{item.present},
       #{item.reaason},
       #{item.createBy},
       #{item.companyId},
       #{item.creationDate},
       #{item.username},
       #{item.taskId}
       from dual
   </foreach>
   )A
</insert>
<!--MySQL写法-->
<insert id="addSupCity" parameterType="java.util.List">
   INSERT INTO T_OCL_SUPCITY
   (CITY_ID,CITY_CODE, CITY_NAME, AREA_DESC, SUP_ID, STAT)
   VALUES
   <foreach collection="list" item="item" index="index" separator=",">    
     (
       #{item.cityId},
       #{item.cityCode},
       #{item.cityName},
       #{item.areaDesc},
       #{item.supId},
       #{item.stat}
     )
   </foreach>
</insert>

Oracle不区分大小写,查询时间TIMESTAMP类型:

select to_char(t.creation_date,'yyyy-mm-dd HH24:mi:ss') from TAX_WORK_TASK t

Oracle传多个参数 Mybatis的写法 使用@Param注解

void deletetByTagId(@Param("paramList") List<String> list
 @Param("workbookTagId")String workbookTagId);
 
<delete id="deletetByTagId">
       delete
       from TAX_COMPANY_WORKBOOK_REL
       where company_id not in
       <foreach item="item" index="index" collection="paramList" open="(" separator="," close=")">
           #{item}
       </foreach>
       and WORKBOOK_TAG_ID = #{workbookTagId}
   </delete>


发表评论