字段内容按指定标识符拆分并多行显示

1. 自定义tabletype数据类型，用于function中返回的返回值类型

create or replace type tabletype as table of varchar2(32676);

2. 用于对传入的指定字段或值按p_split拆分，对拆分后的结果进行 行转列，得到多条行记录

create or replace function split (p_list clob, p_split in varchar2)
  return tabletype
 
  pipelined
 
is
  l_idx    pls_integer;
  v_list  varchar2 (32676) := to_char(p_list);
begin
  loop
      l_idx  := instr (v_list, p_split);
 
      if l_idx > 0
      then
        pipe row (substr (v_list, 1, l_idx - 1));
        v_list  := substr (v_list, l_idx + length (p_split));
      else
        pipe row (v_list);
        exit;
      end if;
  end loop;
end;

3. 将截止到当前运行时间前的TBL_CUSTOMER_CONTACT表中TICKET_CODE存储了多张票号的记录进行行转列操作后存储到新表TBL_CUSTOMER_CONTACT_BREAK_UP中 如TICKET_CODE='999-123,999-456,999-789'，最终转换成3条记录存储到新表中

CREATE TABLE TBL_CUSTOMER_CONTACT_BREAK_UP AS
SELECT T1.ID,
       T1.CONTACT_NO,
       T1.CONTACT_PHONE,
       T1.CUSTOMER_NAME,
       T1.DESCRIPTION,
       T1.OP_OPINION,
       T2.COLUMN_VALUE AS TICKET_CODE,
       T1.CREATE_BY,
       T1.CREATE_DATE
  FROM TBL_CUSTOMER_CONTACT T1, TABLE(SPLIT(T1.TICKET_CODE, ',')) T2
   WHERE T1.TICKET_CODE IS NOT NULL AND T1.TICKET_CODE LIKE '999-%';

4. 创建触发器（阅读者可忽略）

   功能：业务人工每天通过EXCEl在51系统中批量导入票号及对应快递单号的数据，由于一条记录可能会存在多张票号（TICKET_CODE存储多张票号，如'999-xxx,999-ccc,999-vvv'）导致用票号查询快递单号时只能模糊查询执行效率和返回时间太慢。添加触发器，每次新增或导入时，将单行记录的多张票号拆分成多条记录存储。票号查询快递单号时直接用TICKET_CODE精确查找提高查询效率。

CREATE OR REPLACE TRIGGER TRI_CUSTOMER_CONTACT_TICKET
  AFTER INSERT OR DELETE ON TBL_CUSTOMER_CONTACT
  REFERENCING NEW AS NEW OLD AS OLD
  FOR EACH ROW
DECLARE
BEGIN

  -- 插入记录

  IF INSERTING THEN
    IF :NEW.TICKET_CODE IS NOT NULL and :NEW.TICKET_CODE like '999-%' THEN
      INSERT INTO TBL_CUSTOMER_CONTACT_BREAK_UP
        (ID,
         CONTACT_NO,
         CONTACT_PHONE,
         CUSTOMER_NAME,
         DESCRIPTION,
         OP_OPINION,
         TICKET_CODE,
         CREATE_BY,
         CREATE_DATE)

        SELECT :NEW.ID,
               :NEW.CONTACT_NO,
               :NEW.CONTACT_PHONE,
               :NEW.CUSTOMER_NAME,
               :NEW.DESCRIPTION,
               :NEW.OP_OPINION,
               T1.COLUMN_VALUE AS TICKET_CODE,
               :NEW.Create_By,
               :NEW.create_date
          FROM table(split(:NEW.TICKET_CODE, ',')) T1;
    END IF;
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    -- Consider logging the error and then re-raise
    RAISE;
END TRI_CUSTOMER_CONTACT_TICKET;

6. 对表TBL_CUSTOMER_CONTACT_BREAK_UP中字段TICKET_CODE增加索引

create index IDX_CONTACT_BREAK_UP_TCODE on TBL_CUSTOMER_CONTACT_BREAK_UP (TICKET_CODE)
  tablespace TBSD_CUSR
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

登录时出现shared memory realm does not exist

解决办法：

1. 进入CMD命令行
2. 输入 sqlplus /nolog
3. conn / as sysdba
4. startup
5. 然后用sqlplus命令进入即可

时间差转时分秒

如果时间差小于一天，可用如下简写方法：

例如：计算2022/04/03 15:22:00 - 2022/04/03 14:04:01 = 01:17:59

with lead_time as
 (select to_date('2022/04/03 15:22:00', 'yyyy/mm/dd hh24:mi:ss') -
         to_date('2022/04/03 14:04:01', 'yyyy/mm/dd hh24:mi:ss') tim
    from dual)
select to_char(trunc(sysdate) + t.tim, 'hh24:mi:ss') from lead_time t;

---

如果时间差大于一天并且要求将天数加到小时上，使用如下写法：

例如：计算2022/04/05 15:22:00 - 2022/04/03 14:04:01 = 49:17:59

with lead_time as
 (select to_date('2022/04/05 15:22:00', 'yyyy/mm/dd hh24:mi:ss') -
         to_date('2022/04/03 14:04:01', 'yyyy/mm/dd hh24:mi:ss') tim
    from dual)
select decode(sign(t.tim), -1, '-', '') ||
       (trunc(abs(t.tim)) * 24 +
        to_char(trunc(sysdate) + abs(t.tim), 'hh24')) ||
       to_char(trunc(sysdate) + abs(t.tim), ':mi:ss')
  from lead_time t;

---

如果日期差可以为负数，使用如下写法：

例如：计算2022/04/02 15:22:00 - 2022/04/03 14:04:01 = -22:42:01

with lead_time as
 (select to_date('2022/04/02 15:22:00', 'yyyy/mm/dd hh24:mi:ss') -
         to_date('2022/04/03 14:04:01', 'yyyy/mm/dd hh24:mi:ss') tim
    from dual)
select decode(sign(t.tim), -1, '-', '') ||
       (trunc(abs(t.tim)) * 24 +
        to_char(trunc(sysdate) + abs(t.tim), 'hh24')) ||
       to_char(trunc(sysdate) + abs(t.tim), ':mi:ss')
  from lead_time t;

也可以将上述某个sql语句封装到函数中使用

create or replace function lead_time(p_date_num number) return varchar2 is
  v_res varchar2(50);
begin

  select trunc(p_date_num) * 24 +
         to_char(trunc(sysdate) + abs(p_date_num), 'hh24') ||
         to_char(trunc(sysdate) + abs(p_date_num), ':mi:ss')
    into v_res
    from dual;
  return v_res;
end;

通过函数调用

select lead_time(to_date('2022/03/05 16:29:00', 'yyyy/mm/dd hh24:mi:ss') -
                 to_date('2022/03/05 14:27:01', 'yyyy/mm/dd hh24:mi:ss'))
  from dual;

修改表字段顺序

1. 查询表ID

select object_id
from all_objects
where owner = '当前用户名'
and object_name = '表名';

2. 查询表字段序号

select obj#, col#, name
from sys.col$
where obj# = '第一步的object_id' order by col#;

3. 修改字段顺序

update sys.col$ set col#=3 where obj#='第一步的object_id' and name='要修改的字段名称';

shared memory realm does not exist

解决方法：cmd中输入sqlplus /nolog,然后输入conn / as sysdba,再输入startup

表空间查询

---查看所有表空间信息及使用情况
SELECT A.TABLESPACE_NAME 表空间名,

  A.BYTES 总容量,

  B.BYTES 已使用,

  C.BYTES 剩余,

  (B.BYTES * 100) / A.BYTES 使用百分比,

  (C.BYTES * 100) / A.BYTES 空余百分比

  FROM SYS.SM$TS_AVAIL A, SYS.SM$TS_USED B, SYS.SM$TS_FREE C

  WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME

    AND A.TABLESPACE_NAME = C.TABLESPACE_NAME;

---查看表空间信息
select d.tablespace_name,
       d.file_name,
       d.autoextensible,
       d.bytes,
       d.maxbytes,
       d.status
  from dba_data_files d
 where d.tablespace_name = 'TBSD_REPT';

---查看表空间使用情况
select upper(f.tablespace_name) "表空间名",
       d.tot_grootte_mb "表空间大小(M)",
       d.tot_grootte_mb - f.total_bytes "(已使用空间M)",
       to_char(round((d.tot_grootte_mb - f.total_bytes) / d.tot_grootte_mb * 100,
                     2),
               '990.99') "使用比",
       f.total_bytes "(空闲空间M) ",
       f.max_bytes "(最大块M) "
  from (select tablespace_name,
               round(sum(bytes) / (1024 * 1024), 2) total_bytes,
               round(max(bytes) / (1024 * 1024), 2) max_bytes
          from sys.dba_free_space
         group by tablespace_name) f,
       (select tablespace_name,
               round(sum(bytes) / (1024 * 1024), 2) tot_grootte_mb
          from sys.dba_data_files
         group by tablespace_name) d
 where d.tablespace_name = f.tablespace_name
   and d.tablespace_name = 'TBSD_REPT' --表空间名
 order by 4 desc;

进程查询

活动进程查询

活动进程详细信息

SELECT a.sid, a.SERIAL#, sql_text, spid

FROM v$session a, v$process p, v$sqlarea q

WHERE a.PADDR = p.ADDR

  AND a.SQL_ID = q.SQL_ID

  AND a.STATUS = 'ACTIVE'

死锁进程查询

死锁进程详细信息

SELECT (

  SELECT username

  FROM v$session

  WHERE sid = a.sid

  ) AS blocker, a.sid, 'is blocking'

  , (

    SELECT username

    FROM v$session

    WHERE sid = b.sid

  ) AS blockee, b.sid

FROM v$lock a, v$lock b

WHERE a.block = 1

  AND b.request > 0

  AND a.id1 = b.id1

  AND a.id2 = b.id2;

影响性能的进程查询

SELECT sl.sid, s.serial#, sl.opname, s.program, sl.sql_id

  , TRUNC(sl.time_remaining / 60) || ':' || MOD(sl.time_remaining, 60) AS remaining

  , ROUND(sl.sofar / sl.totalwork * 100, 2) AS progress_pct

FROM v$session s, v$session_longops sl

WHERE s.sid = sl.sid

  AND s.serial# = sl.serial#

  AND sl.totalwork > 0

  AND sl.sofar <> sl.totalwork

/* 可以根据上面SQL_ID查出运行的SQL语句 */

SELECT *

FROM v$sql

WHERE sql_id = 'SQL_ID';

目录查询

select owner, DIRECTORY_NAME,DIRECTORY_PATH from dba_directories;

时间查询

时间差

• 天

  ROUND(TO_NUMBER(END_DATE - START_DATE))
  

• 小时

  ROUND(TO_NUMBER(END_DATE - START_DATE))
  

• 分钟

  ROUND(TO_NUMBER(END_DATE - START_DATE) * 24 * 60)
  

• 秒

  ROUND(TO_NUMBER(END_DATE - START_DATE) * 24 * 60 * 60)
  

• 毫秒

  ROUND(TO_NUMBER(END_DATE - START_DATE) * 24 * 60 * 60 * 60)
  

• 转为timestamp

  select sysdate - cast(to_date('2013-3-21 14:50:10', 'yyyy-mm-dd hh24:mi:ss') as timestamp) 时差 from dual;
  

• 时间差表示为 xx时:xx分:xx秒

select sysdate,

  trunc((sysdate - to_date('2013-3-21 14:50:10', 'yyyy-mm-dd hh24:mi:ss')) * 24),

  trunc(mod((sysdate - to_date('2013-3-21 14:50:10', 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60, 60)),

  trunc(mod((sysdate - to_date('2013-3-21 14:50:10', 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60 * 60, 60))

  from dual;

查询某个时间维度的数据

• 今天数据

SELECT COUNT(1) FROM T_CALL_RECORDS WHERE TO_CHAR(T_RKSJ,'YYYY-MM-DD')=TO_CHAR(SYSDATE,'YYYY-MM-DD')；

• 昨天数据

SELECT COUNT(1) FROM T_CALL_RECORDS WHERE TO_CHAR(T_RKSJ,'YYYY-MM-DD')=TO_CHAR(SYSDATE-1,'YYYY-MM-DD')；

• 本周数据

SELECT COUNT(1) FROM T_CALL_RECORDS WHERE T_RKSJ >= TRUNC(NEXT_DAY(SYSDATE-8,1)+1) AND T_RKSJ < TRUNC(NEXT_DAY(SYSDATE-8,1)+7)+1；

• 上周数据

SELECT COUNT(1) FROM T_CALL_RECORDS WHERE T_RKSJ >= TRUNC(NEXT_DAY(SYSDATE-8,1)-6) AND T_RKSJ < TRUNC(NEXT_DAY(SYSDATE-8,1)+1)；

• 本月数据

SELECT COUNT(1) FROM T_CALL_RECORDS WHERE TO_CHAR(T_RKSJ,'YYYY-MM')=TO_CHAR(SYSDATE,'YYYY-MM')；

• 上月数据

SELECT COUNT(1) FROM T_CALL_RECORDS WHERE TO_CHAR(T_RKSJ,'YYYY-MM')=TO_CHAR(ADD_MONTHS(SYSDATE,-1),'YYYY-MM')；

• 5分钟内数据

select * from table_name where create_time >= sysdate - 5/(24*60)
---或between and
select * from table_name where create_time between sysdate - 5/(24*60) and sysdate
---列举部分写法如下
---sysdate+1：加1天；sysdate+1/24：加1小时；sysdate+1/(24*60)：加1分钟；sysdate+1/(24*60*60)：加1秒钟；其他关系型数据库大致相同，如mysql可以把sysdate替换成now()

首字母转换

首字母小写

SELECT *

  FROM TBL_PARAMETER

WHERE DESC_EN IS NOT NULL

  AND REGEXP_LIKE(SUBSTR(DESC_EN, 1, 1), '^([a-z]+)$');

首字母大写

SELECT substr(upper(DESC_EN),1,1)||substr(DESC_EN,2,length(DESC_EN)-1)

  FROM TBL_PARAMETER;

首字母大写 其余小写

select NLS_INITCAP('ab cDe'),

  NLS_INITCAP('a c b d e', 'NLS_LANGUAGE=AMERICAN')

  from dual;

trunc与to_char

trunc取得是天（可比较），而to_char取得是数值（可计算）；但trunc(date)与to_char(date)具有相似功能，区别如下：

trunc(sysdate,'cc')   --取当世纪的第一天     
to_char(sysdate,'cc') --取当世纪数值

trunc(sysdate,'yyyy') --取当年的第一天       
to_char(sysdate,'yyyy') --取当年数值

trunc(sysdate,'iyyy') --取上年的最后一天     
to_char(sysdate,'iyyy') --取当年数值

trunc(sysdate,'Q')    --取当季第一天
to_char(sysdate,'Q')  --取当季数值

trunc(sysdate,'mm')   --取当月第一天         
to_char(sysdate,'mm') --取当月数值

trunc(sysdate,'ww')   --取当周第一天(周二)   
to_char(sysdate,'ww') --取当周数值(第几周)

trunc(sysdate,'iw')   --取当周第一天(周一)   
to_char(sysdate,'iw') --取当周数值(第几周)
 
--当季
select  to_char(sysdate,'Q') from dual
select  trunc(sysdate,'Q')  from dual

--当月
select  trunc(sysdate,'mm')  from dual
select  to_char(sysdate,'mm') from dual

-- 当天
select  to_char(sysdate,'dd') from dual
select  trunc(sysdate,'dd') from dual

锁

锁查询

死锁查询

/*

查看到死锁的Table和操作用户的一些信息

注意:本功能慎重使用,有一定的破坏性,该SQL可以断开客户机和服务器的连接

*/

SELECT LOCK_INFO.OWNER || '.' || LOCK_INFO.OBJ_NAME AS OBJ_NAME, LOCK_INFO.SUBOBJ_NAME AS SUBOBJ_NAME, SESS_INFO.MACHINE AS MACHINE

  , LOCK_INFO.SESSION_ID AS SESSION_ID, SESS_INFO.SERIAL# AS SERIAL#, LOCK_INFO.ORA_USERNAME AS ORA_USERNAME, LOCK_INFO.OS_USERNAME AS OS_USERNAME, LOCK_INFO.PROCESS AS PROCESS

  , LOCK_INFO.OBJ_ID AS OBJ_ID, LOCK_INFO.OBJ_TYPE AS OBJ_TYPE, SESS_INFO.LOGON_TIME AS LOGON_TIME, SESS_INFO.PROGRAM AS PROGRAM, SESS_INFO.STATUS AS STATUS

  , SESS_INFO.LOCKWAIT AS LOCKWAIT, SESS_INFO.ACTION AS ACTION, SESS_INFO.CLIENT_INFO AS CLIENT_INFO

FROM (

  SELECT obj.OWNER AS OWNER, obj.OBJECT_NAME AS OBJ_NAME, obj.SUBOBJECT_NAME AS SUBOBJ_NAME, obj.OBJECT_ID AS OBJ_ID, obj.OBJECT_TYPE AS OBJ_TYPE

    , lock_obj.SESSION_ID AS SESSION_ID, lock_obj.ORACLE_USERNAME AS ORA_USERNAME, lock_obj.OS_USER_NAME AS OS_USERNAME, lock_obj.PROCESS AS PROCESS

  FROM (

    SELECT *

    FROM all_objects

    WHERE object_id IN (

      SELECT object_id

      FROM v$locked_object

    )

  ) obj, v$locked_object lock_obj

  WHERE obj.object_id = lock_obj.object_id

) LOCK_INFO, (

  SELECT SID, SERIAL#, LOCKWAIT, STATUS, PROGRAM

    , ACTION, CLIENT_INFO, LOGON_TIME, MACHINE

  FROM v$session

) SESS_INFO

WHERE LOCK_INFO.SESSION_ID = SESS_INFO.SID;

/* 根据查询结果执行以下SQl解锁 */
alter system kill session '445,19653';---'session_id,serial'

触发器

• 禁用某个表的触发器： alter table tableName disable all triggers;

• 启用某个表的触发器： alter table tableName enable all triggers;

• 禁用指定的触发器： alter trigger triggerName disable;

• 启用指定的触发器： alter trigger triggerName enbale;

• 查看某个表对应的触发器名称： select trigger_name from all_triggers where table_name='XXX';

• 根据触发器名称查询对应的触发器内容： select text from all_source where type='TRIGGER' AND name='TR_XXX';

• 查看所有触发器： Select object_name From user_objects Where object_type='TRIGGER'; 或 Select * From user_triggers;

正则匹配

去掉括号中内容

select regexp_replace(字段, '[\(|（].+[\)|）]', '')  from tablename;