oracle相关操作语句

• 分割字符串

SUBSTR(str,pos): 由中，选出所有从第pos位置开始的字元。

SUBSTR(str,pos,len): 由str中的第pos位置开始，选出接下去的len个字符

• 创建存储过程

  -- 态势监控内的统计，10分钟定时运行一次
  create or replace procedure PROC_NAME is
    
  begin
     //业务逻辑
  
  end PROC_TSJK_STAT;

• 创建定时器

  begin
    sys.dbms_scheduler.create_job(job_name            => 'wjz.JOB_NAME', --取名
                                  job_type            => 'STORED_PROCEDURE',  -- 定时类型，这里是定时运行存储过程 
                                  job_action          => 'PROC_NAME',  -- 要定时执行的存储过程
                                  start_date          => to_date('20-12-2018 00:00:00', 'dd-mm-yyyy hh24:mi:ss'), --开始时间
                                  repeat_interval     => 'Freq=Minutely;Interval=10;ByHour=00;ByMinute=00', -- 定时时间设置
                                  end_date            => to_date(null), -- 结束时间
                                  job_class           => 'DEFAULT_JOB_CLASS', -- 日志类型
                                  enabled             => true, -- 是否立即执行
                                  auto_drop           => false, -- Job执行完成后是否删除job
                                  comments            => '每10分钟执行一次');  -- 说明
  end;

• oracle时间戳和日期相互转换

    -- 时间转毫秒
    SELECT TO_NUMBER(TO_DATE('2014-07-28 17:12:45', 'YYYY-MM-DD HH24:MI:SS') -      
                  TO_DATE('1970-01-01 8:0:0', 'YYYY-MM-DD HH24:MI:SS')) * 24 * 60 * 60 * 1000      
   FROM DUAL;
   
    -- 毫秒转时间
   SELECT TO_CHAR(1406538765000 / (1000 * 60 * 60 * 24) +      
          TO_DATE('1970-01-01 08:00:00', 'YYYY-MM-DD HH:MI:SS'), 'YYYY-MM-DD HH24:MI:SS') AS CDATE      
   FROM DUAL;

• oracle时间加一天一年一分钟一小时

  select to_date('02-22-2008 10:30:30','mm-dd-yyyy hh24:mi:ss') today, 
  to_date('02-22-2008 10:30:30','mm-dd-yyyy hh24:mi:ss')+1 next_day 
  from dual;
  
  TODAY? NEXT_DAY
  
  ------------------------- -------------------------
  
  02-22-08 10:30:30 02-23-08 10:30:30
  
  
  Add an hour.
  
  select to_date('02-22-08 10:30:30','mm-dd-yy hh24:mi:ss') today,
  to_date('02-22-08 10:30:30','mm-dd-yy hh24:mi:ss')+ 1/24 next_hour
  from dual;
  
  TODAY? NEXT_HOUR
  
  ------------------------ ------------------------
  
  02-22-08 10:30:30 02-22-08 11:30:30
  
  
  Add a minute.
  
  select to_date('02-22-08 10:30:30','mm-dd-yy hh24:mi:ss') today,
  to_date('02-22-08 10:30:30','mm-dd-yy hh24:mi:ss')+ 1/(24*60) next_min
  from dual;
  
  TODAY? NEXT_MIN
  
  ------------------------ ------------------------
  
  02-22-08 10:30:30 02-22-08 10:31:30
  
  
  Add a second.
  
  select to_date('02-22-08 10:30:30','mm-dd-yy hh24:mi:ss') today,
  to_date('02-22-08 10:30:30','mm-dd-yy hh24:mi:ss')+ 1/(24*60*60) next_sec
  from dual;
  
  TODAY? NEXT_SEC
  
  ------------------------ ------------------------
  
  02-22-08 10:30:30 02-22-08 10:30:31
  
  -- 加一年
  add_months(sysdate,12);

• oracle获取当年天数，当月天数

  --当年
  SELECT ADD_MONTHS(TRUNC(to_date('2011-11-23','yyyy-mm-dd'), 'YYYY'), 12) - TRUNC(to_date('2011-11-23','yyyy-mm-dd'), 'YYYY') days FROM DUAL
  
  --当月
  select to_char(last_day(to_date('2011-11-23','yyyy-mm-dd')),'dd') from dual

• 将分组统计出来的总数新增一列置于每行后面

  select d.*,sum(count) over (partition by null) as sum from 数据表

• oracle使用jdbc查出来是大写，用实体类转小写

  //1、用jdbc的query指定实体类查询
  getBaseJdbcDao().query(sql, new BeanPropertyRowMapper<>(实体.class));
  //2、将sql上使用双引号的别名处理
  peopleSqlSb.append(" select d.name \"nation\" from ( ");

• 行转列列转行

  //列转行 1
  select trim(substr(txt,instr(txt,',',1,LEVEL)+1,instr(txt,',',1,LEVEL+1) - instr(txt,',',1,LEVEL) -1)) as flie_name
  from (select ','||'aa,bb,cc'||',' txt from dual)
  connect by LEVEL <= length(REPLACE('aa,bb,cc',',',''))+1
  
  //列转行 2
  SELECT *
    FROM (WITH temp AS (SELECT '404145732,404145692,' text FROM dual t)
           SELECT regexp_substr(text,
                                '[^,]+',
                                1,
                                rn) id
             FROM temp t1,
                  (SELECT LEVEL rn
                     FROM dual
                   CONNECT BY LEVEL <= (SELECT length(text) - length(REPLACE(text,
                                                                             ',',
                                                                             ''))
                                          FROM temp)) t)
  
  
  //列转行 3
  SELECT regexp_substr('110,112,113,114',
                       '[^,]+',
                       1,
                       LEVEL)
    FROM dual
  CONNECT BY regexp_substr('110,112,113,114',
                           '[^,]+',
                           1,
                           LEVEL) IS NOT NULL;
  
  //行转列逗号分隔 ,主要使用wm_concat(name)和group by lable_id
  select wm_concat(name),lable_id from 表 group by lable_id