Oracle工作中用到的小知识点

1. sum（1）的作用
通过case判断接单超时的置为1，然后sum相加，得出
接单超时数量

select 
sum(case
     when w.sign_date > w.sign_date_ask then
      1
     else
      0
   end) as receiveOutTime
from order w

2. update select的作用

update t1 set (t1.staff_id,t1.staff_name)=
(select t2.staff_id,t2.staff_name from t2  where t2.id=t1.id)
where id=222

3.PL/SQL developer快速生成插入语句和查询语句

https://blog.csdn.net/CZT56CSDN/article/details/80963154

4. 查询blob字段内容
查询web_element表中web_str_blob字段中包含专业关键字的记录

select a.web_code,
utl_raw.cast_to_varchar2(DBMS_LOB.SUBSTR(a.web_str_blob,60,dbms_lob.instr(a.web_str_blob,utl_raw.cast_to_raw('专业')))),rowid 
from web_element a
 where  dbms_lob.instr(a.web_str_blob,utl_raw.cast_to_raw('专业'),1,1)>0 order by id desc;

5. 创建分区表

create table ZW_FUNCTIONAL_STATISTICS
(
  id             NUMBER,
  staff_id       VARCHAR2(30),
  action_name    VARCHAR2(100),
  handle_time    VARCHAR2(30),
  status         VARCHAR2(30),
  create_time    DATE,
  month_id       VARCHAR2(8) default TO_CHAR(SYSDATE, 'mm')
)
partition by range (MONTH_ID)
(
  partition ZW_FUNCTIONAL_STATISTICS_01 values less than ('02')
    tablespace TB_PUBLIC
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition ZW_FUNCTIONAL_STATISTICS_02 values less than ('03')
    tablespace TB_PUBLIC
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition ZW_FUNCTIONAL_STATISTICS_03 values less than ('04')
    tablespace TB_PUBLIC
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition ZW_FUNCTIONAL_STATISTICS_04 values less than ('05')
    tablespace TB_PUBLIC
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition ZW_FUNCTIONAL_STATISTICS_05 values less than ('06')
    tablespace TB_PUBLIC
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition ZW_FUNCTIONAL_STATISTICS_06 values less than ('07')
    tablespace TB_PUBLIC
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition ZW_FUNCTIONAL_STATISTICS_07 values less than ('08')
    tablespace TB_PUBLIC
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition ZW_FUNCTIONAL_STATISTICS_08 values less than ('09')
    tablespace TB_PUBLIC
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition ZW_FUNCTIONAL_STATISTICS_09 values less than ('10')
    tablespace TB_PUBLIC
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition ZW_FUNCTIONAL_STATISTICS_10 values less than ('11')
    tablespace TB_PUBLIC
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition ZW_FUNCTIONAL_STATISTICS_11 values less than ('12')
    tablespace TB_PUBLIC
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition ZW_FUNCTIONAL_STATISTICS_12 values less than ('13')
    tablespace TB_PUBLIC
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 1M
      next 1M
      minextents 1
      maxextents unlimited
    )
);

6. oracle时间相减
oracle 两个时间相减默认的是天数
oracle 两个时间相减默认的是天数24 为相差的小时数
oracle 两个时间相减默认的是天数2460 为相差的分钟数
oracle 两个时间相减默认的是天数246060 为相差的秒数

7.oracle 对单引号进行转义 使用单引号对单引号进行转义

-->错的
SELECT 's's's' from dual     
-- s's's
SELECT 's'||'''s'''||'s' from dual  ;
-->  s's's
SELECT 's''s''s' FROM dual; 

oracle中的 q’ 转义字符用法
https://blog.csdn.net/home_zhang/article/details/100135894

select q'[it's my hero]' from dual;
--> it's my hero

8.Oracle 查询搜索字符串在哪些存储过程中包含

select *
from all_source
 where OWNER = 'testuser1'
and TEXT like '%您有超时未执行%

oracle 查询存过 被哪个定时任务调用

select * from dba_jobs a where a.what like '%大写的存过名%'

9.数据库base64加解密
加密

SELECT
UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW('1466')))
FROM
    DUAL;

解密

select utl_raw.cast_to_varchar2(utl_encode.base64_decode(utl_raw.cast_to_raw('VGFuZHgyMDE4Kg==')))
 from dual

10. oracle解锁

select object_id,session_id,locked_mode from v$locked_object;

select t2.username,t2.sid,t2.serial#,t2.logon_time 
　　from v$locked_object t1,v$session t2 
　　where t1.session_id=t2.sid order by t2.logon_time;
　　
alter system kill session '8591,35017'

11.oracle如何拆分以逗号分隔的字符串为多行?

https://blog.csdn.net/sofeien/article/details/80534557

12. oracle时间相减之差转为 天时分格式

SELECT 
   (EXTRACT(DAY FROM (to_date('2012-03-30 00:00:00','YYYY-MM-DD HH24:MI:ss')-to_timestamp('2012-03-29 00:00:00','YYYY-MM-DD HH24:MI:ss')) DAY TO SECOND )
   || '天'
   ||EXTRACT(HOUR FROM (to_date('2012-03-30 00:00:00','YYYY-MM-DD HH24:MI:ss')-to_date('2012-03-29 00:00:00','YYYY-MM-DD HH24:MI:ss')) DAY TO SECOND )
   || '时'
   || EXTRACT(MINUTE FROM (to_date('2012-03-30 23:23:15','YYYY-MM-DD HH24:MI:ss')-to_date('2012-03-29 00:23:00','YYYY-MM-DD HH24:MI:ss')) DAY TO SECOND )
   || '分'
   || EXTRACT(SECOND FROM (to_date('2012-03-30 23:23:15','YYYY-MM-DD HH24:MI:ss')-to_date('2012-03-29 00:23:00','YYYY-MM-DD HH24:MI:ss')) DAY TO SECOND )
   || '秒')  as timespan_
FROM DUAL

https://blog.csdn.net/yzy85/article/details/80566651