oracle ,mybtis 批量操作，时间转换和一些简单应用（持续更新）

一 ，批量添加 并去重
dao
Integer  batchInsert(@Param("list")  List<Class> list);
xml
<insert id="batchInsert" parameterType="java.util.List">
 INSERT INTO table(column1,column2,column3)
   <foreach open="("  collection="list"  item="item"  index="index" separator="union"   close=")">
   SELECT
   #{item.column1,javaType=VARCHAR},
   #{item.column2,javaType=VARCHAR},
   #{item.column3,javaType=VARCHAR}
   FROM  dual
   WHERE   NOT  EXISTS(
                                       SELECT *  FROM  table
                                       WHERE  column1=#{item.column1,javaType=VARCHAR}
                                       )
   </foreach>
</insert>
oracle 3中批量插入的方法
(1),
begin
  insert into tableName(column1, column2, column3...) values(value1,value2,value3...);
  insert into tableName(column1, column2, column3...) values(value1,value2,value3...);
  insert into tableName(column1, column2, column3...) values(value1,value2,value3...);
  ...
end;
(2),
insert into tableName(column1, column2, column3...) values(value1,value2,value3...);
insert into tableName(column1, column2, column3...) values(value1,value2,value3...);
insert into tableName(column1, column2, column3...) values(value1,value2,value3...);
(3),
insert into tableName(column1(主键),column2,column3...)
  select tableNames_seq.nextval,,column2,column3... from (
  select value1 column2,value2 column3,value3 column4 from dual
  union
  select value1 column2,value2 column3,value3 column4 from dual
  union
  select value1 column2,value2 column3,value3 column4 from dual
  union
  select value1 column2,value2 column3,value3 column4 from dual
)
(4), 第三种加上去重复

insert into tableName(column1, column2, column3...) 
select  value1,value2,value3 from dual where not exists(
select * from tableName where column1= value1
)

二 ，行转列
xml
<select id="selectColumn" parameterType="Class" resultType="lang.util.String">
  SELECT  to_char(wm_concat(column)) from table
//当oracle使用wm_concat()长度超过系统限制解决办法
select XMLAGG(XMLELEMENT(E, code || ',')).EXTRACT('//text()').getclobval()  as codes from table
</select>

三，获取按时间倒叙，最后的值
xml
select * from (
select name , total , row_number() over(partition by name order by addtime desc) rn
where table) t
where   t.rn=1
四，批量修改
dao
void  update(@Param("list")List<String> list);
xml
<update id="update">
       <foreach open="begin"  collection="list"  item="item"  index="index" separator=";"   close=";end;">
       UPDATE    table   SET  column=value
       WHERE     column1    IN(#{item})
     </foreach>
</update

五，多选
select * table from where
 instr("1,2,3,4",type)  相当于like 。但无法区分 11 和 1

六，查询5300及5300以下的所有
//根据父级查询子集
    SELECT * FROM  sys_code WHERE  tenant='5300' 
start with code='5300' 
connect by prior  code = supr_code 
//根据子集查询级别最高的父级
 SELECT code  FROM (
  SELECT code  FROM  sys_code WHERE  tenant='5300' 
start with code='5300' 
connect by prior  supr_code =code 
 ORDER BY lvl ) a
 WHERE ROWNUM = 1

七，Oracle取查询结果数据的第一条记录SQL：
select * from (select * from <table> order by <key>) where rownum=1;
select * from (select * from <table> order by <key> desc) where rownum=1;

八：oracle  日期转换
date  ——> String：
select to_char(sysdate,'yyyy-MM-dd HH24:mi:ss') from dual;
String ——>date ：
select to_date('2005-01-01 13:14:20','yyyy-MM-dd HH24:mi:ss') from dual; 
timestamp ——>date ：
 select to_date(to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss') from dual
date ——>timestamp：
 select to_timestamp(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss') from dual
String ——>timestamp：
 select to_timestamp('2005-01-01 13:14:20','yyyy-mm-dd hh24:mi:ss.ff')) from dual

九：oracle  交集，并集，差s集
交集   union all    union (去重复)
并集   intersect
差集   minue   前者去除两者的交集的数据

十：获取某个时间段每天
select to_date('2018-09-01','yyyy-mm-dd') +rownum -1  day from dual
connect  by  rownum<=  (to_date('2018-09-11','yyyy-mm-dd')+1-to_date('2018-09-01','yyyy-mm-dd'))
2018/9/1
...
2018/9/11


十一：获取父子结构的路径
select Q1.NAME ,Q2.ZY_PATH  from (
              select  '-1' id, '0' pid,'-1' name from dual
              union 
              select  '1' id, '-1' pid,'1' name from dual
              union 
              select  '2' id, '-1' pid,'2' name from dual
              union 
              select  '11' id, '1' pid,'11' name from dual
                                                      ) Q1
LEFT JOIN (SELECT id,pid,name,LEVEL,SYS_CONNECT_BY_PATH(NAME, '\') ZY_PATH
                  FROM  ( select  '-1' id, '0' pid,'-1' name from dual
                          union 
                          select  '1' id, '-1' pid,'1' name from dual
                          union 
                          select  '2' id, '-1' pid,'2' name from dual
                          union 
                          select  '11' id, '1' pid,'11' name from dual
                        )
START WITH ID = '-1' CONNECT BY PRIOR ID = PID) Q2 ON Q1.ID = Q2.ID

NAME   ZY_PATH
-1     \-1
1      \-1\1
11     \-1\1\11
2      \-1\2