在 Oracle 数据库开发中,经常会遇到诸如行转列、单行变多行等需求,熟练使用 Oracle 自带的函数,实现这些功能会变得得心应手。

单行变多行

源字符串: A001/D/200198

SQL> SELECT 
    ROWNUM RN, 
    REGEXP_SUBSTR('A001/D/200198', '[^/]+', 1, ROWNUM) PARAM FROM DUAL 
CONNECT BY ROWNUM <= LENGTH('A001/D/200198') - LENGTH(REPLACE('A001/D/200198', '/', '')) + 1;   

        RN PARAM
---------- --------------------------
         1 A001
         2 D
         3 200198


SQL> CREATE TABLE T_CLOB(
  ID NUMBER PRIMARY KEY,
  C_CLOB CLOB
);

SQL> INSERT INTO T_CLOB(ID, C_CLOB) VALUES(1, 'A001/D/200198');
SQL> COMMIT;

SQL> SELECT
  ROWNUM,  
  ID,
  TO_CHAR(REGEXP_SUBSTR(C_CLOB, '[^/]+', 1, LEVEL)) AS PARAM
FROM (
  SELECT 
    ID, C_CLOB
  FROM T_CLOB
) T                        
CONNECT BY PRIOR ID = ID
AND LEVEL <= REGEXP_COUNT(C_CLOB, '[^/]+')
AND PRIOR DBMS_RANDOM.VALUE() IS NOT NULL;

    ROWNUM         ID PARAM
---------- ---------- --------------------
         1          1 A001
         2          1 D
         3          1 200198         


SQL> WITH T AS (
  SELECT 
    TO_CHAR(SYSDATE, 'YYYY-MM-DD')       C_DATE,
    '苹果, 橘子, 葡萄, 芒果, 荔枝, 香蕉' C_NAME
  FROM DUAL
)
SELECT 
  C_DATE,
  REGEXP_SUBSTR(C_NAME, '[^, ]+', 1, LEVEL) C_NAME,
  LEVEL
FROM T
CONNECT BY LEVEL <= REGEXP_COUNT(C_NAME, '[^, ]+')
AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL;

C_DATE     C_NAME                                            LEVEL
---------- -------------------------------------------- ----------
2020-02-28 苹果                                                  1
2020-02-28 橘子                                                  2
2020-02-28 葡萄                                                  3
2020-02-28 芒果                                                  4
2020-02-28 荔枝                                                  5
2020-02-28 香蕉                                                  6

6 rows selected

拆分字符串为多行

SQL> SELECT 
  REGEXP_SUBSTR('a,b,c', '[^,]+', 1, 1) R1, 
  REGEXP_SUBSTR('a,b,c', '[^,]+', 1, 2) R2, 
  REGEXP_SUBSTR('a,b,c', '[^,]+', 1, 3) R3 
FROM DUAL;

R1 R2 R3
-- -- --
a  b  c

行转列

DECODE 行转列

SQL> WITH FRUIT AS (
  SELECT 1 ID, '苹果' NAME, 1000 NUMS FROM DUAL  
  UNION ALL
  SELECT 2 ID, '苹果' NAME, 2000 NUMS FROM DUAL  
  UNION ALL
  SELECT 3 ID, '橘子' NAME, 5000 NUMS FROM DUAL
  UNION ALL   
  SELECT 4 ID, '葡萄' NAME, 3500 NUMS FROM DUAL
  UNION ALL  
  SELECT 5 ID, '芒果' NAME, 4200 NUMS FROM DUAL
)
SELECT 
  MAX(DECODE(NAME, '苹果', NUMS)) AS APPLE,
  MAX(DECODE(NAME, '橘子', NUMS)) AS ORANGE,
  MAX(DECODE(NAME, '葡萄', NUMS)) AS GRAPE,
  MAX(DECODE(NAME, '芒果', NUMS)) AS MANGO
FROM FRUIT;  

     APPLE     ORANGE      GRAPE      MANGO
---------- ---------- ---------- ----------
      2000       5000       3500       4200

PIVOT 行转列

  • 语法
pivot(聚合函数() for 列名 in (列值))
  • 将 "PARAM" 字段,进行行转列
SQL> SELECT 
  VC_IND_CODE, 
  VC_IND_SUBCODE, 
  VC_FUNDCODE 
FROM (
  SELECT 
    ROWNUM RN, 
    REGEXP_SUBSTR('A001/D/200198', '[^/]+', 1, ROWNUM) PARAM
  FROM DUAL
  CONNECT BY ROWNUM <= LENGTH('A001/D/200198') - LENGTH(REPLACE('A001/D/200198', '/', '')) + 1
) T PIVOT (MAX(PARAM) FOR RN IN (1 AS VC_IND_CODE, 2 AS VC_IND_SUBCODE, 3 AS VC_FUNDCODE)); 

VC_IND_CODE          VC_IND_SUBCODE       VC_FUNDCODE
-------------------- -------------------- ------------------
A001                 D                    200198
  • 将 "NAME" 字段,进行行转列
SQL> WITH FRUIT AS (
  SELECT 1 ID, '苹果' NAME, 1000 NUMS FROM DUAL  
  UNION ALL
  SELECT 2 ID, '苹果' NAME, 2000 NUMS FROM DUAL  
  UNION ALL
  SELECT 3 ID, '橘子' NAME, 5000 NUMS FROM DUAL
  UNION ALL   
  SELECT 4 ID, '葡萄' NAME, 3500 NUMS FROM DUAL
  UNION ALL  
  SELECT 5 ID, '芒果' NAME, 4200 NUMS FROM DUAL
)
SELECT * FROM FRUIT;  

        ID NAME       NUMS
---------- ---- ----------
         1 苹果       1000
         2 苹果       2000
         3 橘子       5000
         4 葡萄       3500
         5 芒果       4200
SQL> WITH FRUIT AS (
  SELECT 1 ID, '苹果' NAME, 1000 NUMS FROM DUAL  
  UNION ALL
  SELECT 2 ID, '苹果' NAME, 2000 NUMS FROM DUAL  
  UNION ALL
  SELECT 3 ID, '橘子' NAME, 5000 NUMS FROM DUAL
  UNION ALL   
  SELECT 4 ID, '葡萄' NAME, 3500 NUMS FROM DUAL
  UNION ALL  
  SELECT 5 ID, '芒果' NAME, 4200 NUMS FROM DUAL
)
SELECT * FROM (SELECT NAME, NUMS FROM FRUIT) PIVOT (SUM(NUMS) 
FOR NAME IN ('苹果' AS APPLE, '橘子' ORANGE, '葡萄' GRAPE, '芒果' MANGO)); 
    APPLE     ORANGE      GRAPE      MANGO
--------- ---------- ---------- ----------
     3000       5000       3500       4200

UNPOVIOT 列转行

SQL> WITH FRUIT AS (
  SELECT 1 ID, '苹果' NAME, 1000 Q1, 2000 Q2, 3300 Q3, 5000 Q4 FROM DUAL
  UNION ALL
  SELECT 2 ID, '橘子' NAME, 3000 Q1, 3000 Q2, 3200 Q3, 1500 Q4 FROM DUAL
  UNION ALL
  SELECT 3 ID, '香蕉' NAME, 2500 Q1, 3500 Q2, 2200 Q3, 2500 Q4 FROM DUAL
  UNION ALL
  SELECT 4 ID, '葡萄' NAME, 1500 Q1, 2500 Q2, 1200 Q3, 3500 Q4 FROM DUAL
)
SELECT * FROM FRUIT;

        ID NAME         Q1         Q2         Q3         Q4
---------- ---- ---------- ---------- ---------- ----------
         1 苹果       1000       2000       3300       5000
         2 橘子       3000       3000       3200       1500
         3 香蕉       2500       3500       2200       2500
         4 葡萄       1500       2500       1200       3500
SQL> WITH FRUIT AS (
  SELECT 1 ID, '苹果' NAME, 1000 Q1, 2000 Q2, 3300 Q3, 5000 Q4 FROM DUAL
  UNION ALL
  SELECT 2 ID, '橘子' NAME, 3000 Q1, 3000 Q2, 3200 Q3, 1500 Q4 FROM DUAL
  UNION ALL
  SELECT 3 ID, '香蕉' NAME, 2500 Q1, 3500 Q2, 2200 Q3, 2500 Q4 FROM DUAL
  UNION ALL
  SELECT 4 ID, '葡萄' NAME, 1500 Q1, 2500 Q2, 1200 Q3, 3500 Q4 FROM DUAL
)
SELECT ID , NAME, QUARTER, SALES FROM FRUIT UNPIVOT (SALES FOR QUARTER IN (Q1, Q2, Q3, Q4));  

        ID NAME QU      SALES
---------- ---- -- ----------
         1 苹果 Q1       1000
         1 苹果 Q2       2000
         1 苹果 Q3       3300
         1 苹果 Q4       5000
         2 橘子 Q1       3000
         2 橘子 Q2       3000
         2 橘子 Q3       3200
         2 橘子 Q4       1500
         3 香蕉 Q1       2500
         3 香蕉 Q2       3500
         3 香蕉 Q3       2200
         3 香蕉 Q4       2500
         4 葡萄 Q1       1500
         4 葡萄 Q2       2500
         4 葡萄 Q3       1200
         4 葡萄 Q4       3500

层次查询

自上而下检索

select * from table_name start with condition connect by prior id = parent_id;
SQL> SELECT 
  EMPNO, ENAME, JOB, MGR, SAL, DEPTNO, LEVEL
FROM EMP
  START WITH EMPNO = 7698   --根节点
  CONNECT BY PRIOR EMPNO = MGR
ORDER BY LEVEL, SAL;

     EMPNO ENAME      JOB              MGR        SAL     DEPTNO      LEVEL
---------- ---------- --------- ---------- ---------- ---------- ----------
      7698 BLAKE      MANAGER         7839       2850         30          1
      7900 JAMES      CLERK           7698        950         30          2
      7521 WARD       SALESMAN        7698       1250         30          2
      7654 MARTIN     SALESMAN        7698       1250         30          2
      7844 TURNER     SALESMAN        7698       1500         30          2
      7499 ALLEN      SALESMAN        7698       1600         30          2

自下而上检索

select * from table_name start with condition connect by id = prior parent_id;
SQL> SELECT 
  EMPNO, ENAME, JOB, MGR, SAL, DEPTNO, LEVEL
FROM EMP 
  START WITH EMPNO = 7698 
  CONNECT BY EMPNO = PRIOR MGR
ORDER BY LEVEL, SAL;

     EMPNO ENAME      JOB              MGR        SAL     DEPTNO      LEVEL
---------- ---------- --------- ---------- ---------- ---------- ----------
      7698 BLAKE      MANAGER         7839       2850         30          1
      7839 KING       PRESIDENT                  5000         10          2

存储过程游标输出参数

--声明存储过程
CREATE OR REPLACE PROCEDURE P_INDEX_PROFIT_RUN(I_INDCODE      IN  VARCHAR2,
                                               I_STARTDATE    IN  VARCHAR2,
                                               I_ENDDATE      IN  VARCHAR2,
                                               O_ERRCODE      OUT NUMBER,
                                               O_ERRMSG       OUT VARCHAR2,
                                               O_RETURN_VALUE OUT SYS_REFCURSOR) IS

--将数据写入游标变量
OPEN O_RETURN_VALUE FOR
SELECT 
  TO_DATE(TO_CHAR(SYSDATE, 'YYYY-MM-DD'), 'YYYY-MM-DD') D_DATE,
FROM DUAL; 

函数返回游标类型

--声明函数
CREATE OR REPLACE FUNCTION F_INDEX_REALTIME_RUN(I_INDCODE   IN  VARCHAR2,
                                                I_STARTDATE IN  VARCHAR2,
                                                I_ENDDATE   IN  VARCHAR2) 
RETURN SYS_REFCURSOR IS

--定义游标变量
V_CURSOR       SYS_REFCURSOR;

--将返回值写入游标变量
OPEN V_CURSOR FOR 
SELECT 
  TO_DATE(TO_CHAR(SYSDATE, 'YYYY-MM-DD'), 'YYYY-MM-DD') D_DATE,
FROM DUAL;

--返回
RETURN V_CURSOR;

构造日期序列

oracle connect by 函数可用于生成日期序列

生成连续的日期序列

# 获取一年日期序列
SQL> SELECT 
  TRUNC(SYSDATE, 'YYYY') + ROWNUM - 1 "DATE" FROM DUAL 
CONNECT BY ROWNUM < (ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'), 12) - TRUNC(SYSDATE, 'YYYY') + 1);

DATE
----------
2018-01-01
2018-01-02
2018-01-03
2018-01-04
2018-01-05
2018-01-06
2018-01-07  
# 获取指定日期范围内的日期序列

SQL> SELECT 
  DATE '2018-01-01' + ROWNUM - 1 "DATE" FROM DUAL 
CONNECT BY ROWNUM <= DATE'2018-01-05' - DATE'2018-01-01' + 1;

DATE
----------
2018-01-01
2018-01-02
2018-01-03
2018-01-04
2018-01-05  

生成特定的日期序列

# 获取月末日期

SQL> SELECT 
  LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'),  ROWNUM - 1)) "DATE"  
FROM DUAL CONNECT BY ROWNUM < (
  MONTHS_BETWEEN(TO_DATE(TO_CHAR(SYSDATE, 'YYYY')||'12-31', 'yyyy-mm-dd') + 1, TRUNC(SYSDATE, 'YYYY')) + 1
);

DATE
----------
2018-01-31
2018-02-28
2018-03-31
2018-04-30
2018-05-31
2018-06-30
2018-07-31
2018-08-31
2018-09-30
2018-10-31
2018-11-30
2018-12-31

存储过程异常日志记录

对于 Oracle 的存储过程,可以使用输出参数来输出异常信息,也可以使用数据表来记录异常信息,为了便于统计,最好是用数据表来记录异常信息,并且记录异常的这个功能最好是通用的。

  • 创建存储过程异常日志表
SQL> conn scott/tiger;

SQL> create table pub_proc_err_log (
  vc_log_id      varchar2(50) default sys_guid(),
  vc_owner       varchar2(50),
  vc_module_name varchar2(50),
  vc_proc_name   varchar2(50),
  d_err_date     date,
  vc_sqlcode     varchar2(50),
  vc_sqlerrm     varchar2(100),
  vc_err_content varchar2(1000),
  d_updatetime   date default sysdate
)
;
comment on table pub_proc_err_log is '存储过程异常日志表';
comment on column pub_proc_err_log.vc_log_id is '唯一标识';
comment on column pub_proc_err_log.vc_owner is '所属用户';
comment on column pub_proc_err_log.vc_module_name is '模块名称';
comment on column pub_proc_err_log.vc_proc_name is '存储过程名称';
comment on column pub_proc_err_log.d_err_date is '报错日期';
comment on column pub_proc_err_log.vc_sqlcode is 'SQLCODE';
comment on column pub_proc_err_log.vc_sqlerrm is 'SQLERRM';
comment on column pub_proc_err_log.vc_err_content is '报错的具体信息';
comment on column pub_proc_err_log.d_updatetime is '更新时间';
  • 创建主键、索引
SQL> alter table pub_proc_err_log add constraint pk_pub_proc_err_log primary key(vc_log_id);

SQL> create index idx_pub_proc_err_log on pub_proc_err_log(d_err_date, vc_proc_name);
  • 登录 SYS 用户,为存储过程异常日志表创建公有同义词并授权
SQL> conn / as sysdba

SQL> create public synonym pub_proc_err_log for scott.pub_proc_err_log;

SQL> grant select, insert, update, delete on pub_proc_err_log to public;
  • 创建异常日志记录存储过程
SQL> conn scott/tiger;

CREATE OR REPLACE PROCEDURE P_RECORD_PROC_ERR_LOG(I_USER_NAME   IN VARCHAR2,
                                                  I_MODULE_NAME IN VARCHAR2,
                                                  I_PROC_NAME   IN VARCHAR2,
                                                  I_SQLCODE     IN INTEGER,
                                                  I_SQLERRM     IN VARCHAR2,
                                                  I_ERR_LINE    IN VARCHAR2) IS
  /*****************************************************************************\
  *                           xxxxxx
  *           COPYRIGHT (C) 2019, xxxxxx有限公司
  * ===========================================================================
  *
  * 名     称:P_RECORD_PROC_ERR_LOG
  * 功能 描述:异常日志记录存储过程
  *
  * 参     数:传入 -> 模块名称、存储过程名称、SQLCODE、SQL_ERRM、报错的具体行
  *            传出 -> 无
  *
  * 修订 记录:
  * 版本号   编辑时间      编辑人           修改描述
  * 1.0.0    2019-01-21    xxxxxx           创建此存储过程
  \*****************************************************************************/
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN  
  INSERT INTO PUB_PROC_ERR_LOG (
    VC_OWNER,
    VC_MODULE_NAME,
    VC_PROC_NAME,
    D_ERR_DATE,
    VC_SQLCODE,
    VC_SQLERRM,
    VC_ERR_CONTENT
  )
  SELECT 
    I_USER_NAME,
    I_MODULE_NAME, 
    I_PROC_NAME,
    TO_CHAR(SYSDATE, 'YYYY-MM-DD'),   
    I_SQLCODE,    
    I_SQLERRM,    
    I_ERR_LINE 
  FROM DUAL;

  COMMIT;

END P_RECORD_PROC_ERR_LOG;
  • 登录 SYS 用户,为异常日志记录存储过程创建公有同义词并授权
SQL> conn / as sysdba

SQL> create public synonym p_record_proc_err_log for scott.p_record_proc_err_log;

SQL> grant execute on p_record_proc_err_log to public;

创建同义词后,其它用户就不需要以 用户名. 的方式访问了。

  • 在其它存储过程的异常处理部分调用异常日志记录存储过程,记录日志
CREATE OR REPLACE PROCEDURE P_TEST(I_PARAM1 IN VARCHAR2,
                                   I_PARAM2 IN VARCHAR2) IS
BEGIN

  SELECT USERNAME INTO V_USERNAME FROM USER_USERS;

  NULL;

  COMMIT;

EXCEPTION
  WHEN OTHERS THEN
    P_RECORD_PROC_ERR_LOG (
      V_USERNAME
      'PROCEDURE',
      'P_TEST',
      SQLCODE,
      SQLERRM,
      SUBSTR('未处理异常:' || SQLERRM || '。' ||
                        DBMS_UTILITY.FORMAT_ERROR_BACKTRACE,
                        1,
                        1000)
    ); 
    ROLLBACK;

END P_TEST;

存储过程执行状态记录

  • 创建存储过程日志记录表
CREATE TABLE MPUB_F_PROC_LOGS (
  VC_ID          VARCHAR2(100) DEFAULT SYS_GUID() NOT NULL,
  VC_OWNER       VARCHAR2(50),
  VC_MODULE_NAME VARCHAR2(50) DEFAULT 'PROCEDURE',
  VC_PROC_NAME   VARCHAR2(50),
  D_STARTDATE    DATE,
  D_ENDATE       DATE,
  C_PARAMS       CLOB,
  C_PARAMS2      CLOB,
  VC_SQLCODE     VARCHAR2(50),
  VC_SQLERRM     VARCHAR2(2000),
  VC_ERR_CONTENT VARCHAR2(2000),
  T_STARTTIME    TIMESTAMP(6),
  T_ENDTIME      TIMESTAMP(6),
  F_ELAPSEDTIME  INTEGER,
  VC_STATUS      VARCHAR2(2),
  D_UPDATETIME   DATE DEFAULT SYSDATE
);

COMMENT ON TABLE MPUB_F_PROC_LOGS
  IS '数据处理日志记录表';
COMMENT ON COLUMN MPUB_F_PROC_LOGS.VC_ID
  IS '唯一标识';
COMMENT ON COLUMN MPUB_F_PROC_LOGS.VC_OWNER
  IS '所属用户';
COMMENT ON COLUMN MPUB_F_PROC_LOGS.VC_MODULE_NAME
  IS '模块名称';
COMMENT ON COLUMN MPUB_F_PROC_LOGS.VC_PROC_NAME
  IS '模块代码';
COMMENT ON COLUMN MPUB_F_PROC_LOGS.D_STARTDATE
  IS '模块参数(开始日期)';
COMMENT ON COLUMN MPUB_F_PROC_LOGS.D_ENDATE
  IS '模块参数(结束日期)';
COMMENT ON COLUMN MPUB_F_PROC_LOGS.C_PARAMS
  IS '模块参数(组合代码、计划代码...)';
COMMENT ON COLUMN MPUB_F_PROC_LOGS.C_PARAMS2
  IS '预留模块参数,可为空';
COMMENT ON COLUMN MPUB_F_PROC_LOGS.VC_SQLCODE
  IS 'SQLCODE';
COMMENT ON COLUMN MPUB_F_PROC_LOGS.VC_SQLERRM
  IS 'SQLERRM';
COMMENT ON COLUMN MPUB_F_PROC_LOGS.VC_ERR_CONTENT
  IS '异常信息';
COMMENT ON COLUMN MPUB_F_PROC_LOGS.T_STARTTIME
  IS '程序开始时间';
COMMENT ON COLUMN MPUB_F_PROC_LOGS.T_ENDTIME
  IS '程序结束时间';
COMMENT ON COLUMN MPUB_F_PROC_LOGS.F_ELAPSEDTIME
  IS '总耗时(毫秒)';
COMMENT ON COLUMN MPUB_F_PROC_LOGS.VC_STATUS
  IS '运行状态(1 正在运行;0 结束;-1 异常)';
COMMENT ON COLUMN MPUB_F_PROC_LOGS.D_UPDATETIME
  IS '更新时间';

ALTER TABLE MPUB_F_PROC_LOGS
  ADD CONSTRAINT PK_MPUB_F_PROC_LOGS PRIMARY KEY (VC_ID);
  • 创建用于记录时间的函数
CREATE OR REPLACE FUNCTION F_TIMESTAMP_DIFF(I_STARTTIME IN TIMESTAMP,
                                            I_ENDTIME   IN TIMESTAMP)
  RETURN INTEGER AS
  /****************************************************************\
  *                           xxxxxx
  *           COPYRIGHT (C) 2020, xxxxxx
  * ===============================================================
  *
  * 名     称:F_TIMESTAMP_DIFF
  * 功能 描述:存储过程运行时间
  *
  * 参     数:I_START_TIME    开始时间
  *            I_END_TIME      结束时间
  *
  * 修订 记录:
  * 版本号   编辑时间      编辑人           修改描述
  * 1.0.0    2020-03-10    xxx              创建此函数
  \****************************************************************/
  STR       VARCHAR2(50);
  MISECOND  INTEGER;       --毫秒
  SECONDS   INTEGER;       --秒
  MINUTES   INTEGER;       --分
  HOURS     INTEGER;       --小时
  DAYS      INTEGER;       --天
BEGIN
  STR       := TO_CHAR(I_ENDTIME - I_STARTTIME);
  MISECOND  := TO_NUMBER(SUBSTR(STR, INSTR(STR, ' ') + 10, 3));
  SECONDS   := TO_NUMBER(SUBSTR(STR, INSTR(STR, ' ') + 7, 2));
  MINUTES   := TO_NUMBER(SUBSTR(STR, INSTR(STR, ' ') + 4, 2));
  HOURS     := TO_NUMBER(SUBSTR(STR, INSTR(STR, ' ') + 1, 2));
  DAYS      := TO_NUMBER(SUBSTR(STR, 1, INSTR(STR, ' ')));

  RETURN DAYS*24*60*60*1000 + HOURS*60*60*1000 + MINUTES*60*1000 + SECONDS*1000 + MISECOND;

END;
  • 创建日志记录存储过程
CREATE OR REPLACE PROCEDURE P_MPUB_F_PROC_LOGS(I_ID          IN VARCHAR2,
                                               I_OWNER       IN VARCHAR2 DEFAULT NULL,
                                               I_MODULE_NAME IN VARCHAR2 DEFAULT NULL,
                                               I_PROC_NAME   IN VARCHAR2 DEFAULT NULL,
                                               I_STARTDATE   IN DATE     DEFAULT NULL,
                                               I_ENDDATE     IN DATE     DEFAULT NULL,
                                               I_SQLCODE     IN NUMBER   DEFAULT NULL,
                                               I_SQLERRM     IN VARCHAR2 DEFAULT NULL,
                                               I_ERR_CONTENT IN VARCHAR2 DEFAULT NULL,
                                               I_PARAMS      IN CLOB     DEFAULT NULL,
                                               I_PARAMS2     IN CLOB     DEFAULT NULL,
                                               I_STATUS      IN VARCHAR2) IS
  /****************************************************************\
  *                           xxxxxx
  *           COPYRIGHT (C) 2020, xxxxx
  * ===============================================================
  *
  * 名     称:P_MPUB_F_PROC_LOGS
  * 功能 描述:存储过程日期记录
  *
  * 参     数:I_USER_NAME     用户名
  *            I_MODULE_NAME   模块分类,默认为 'PROCEDURE'
  *            I_PROC_NAME     模块名称
  *            I_STARTDATE     模块参数(开始日期)
  *            I_ENDDATE       模块参数(结束日期)
  *            I_PARAMS        模块参数(组合代码、计划代码...)
  *            I_PARAMS2       预留模块参数,可为空
  *            I_SQLCODE       SQLCODE
  *            I_SQLERRM       SQLERRM
  *            I_ERR_CONTENT   异常信息
  *            I_STATUS        模块运行状态(1 正在运行;0 结束;-1 异常)
  *
  * 修订 记录:
  * 版本号   编辑时间      编辑人           修改描述
  * 1.0.0    2020-03-10    xxx              创建此存储过程
  \****************************************************************/
  V_STARTTIME   TIMESTAMP;   --开始时间
  V_ENDTIME     TIMESTAMP;   --结束时间
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN

  --程序运行中...
  IF I_STATUS = '1' THEN

    SELECT SYSTIMESTAMP INTO V_STARTTIME FROM DUAL;

    INSERT INTO MPUB_F_PROC_LOGS (
      VC_ID,
      VC_OWNER,
      VC_MODULE_NAME,
      VC_PROC_NAME,
      D_STARTDATE,
      D_ENDATE,
      VC_SQLCODE,
      VC_SQLERRM,
      VC_ERR_CONTENT,
      C_PARAMS,
      C_PARAMS2,
      T_STARTTIME,
      T_ENDTIME,
      F_ELAPSEDTIME,
      VC_STATUS,
      D_UPDATETIME
    )
    SELECT
      I_ID,
      I_OWNER,
      I_MODULE_NAME,
      I_PROC_NAME,
      I_STARTDATE,
      I_ENDDATE,
      NULL,
      NULL,
      NULL,
      I_PARAMS,
      I_PARAMS2,
      V_STARTTIME,
      NULL,
      NULL,
      I_STATUS,
      SYSDATE
    FROM DUAL;

  --程序结束...
  ELSIF I_STATUS = '0' THEN

    SELECT SYSTIMESTAMP INTO V_ENDTIME FROM DUAL;
    SELECT T_STARTTIME INTO V_STARTTIME FROM MPUB_F_PROC_LOGS WHERE VC_ID = I_ID;

    UPDATE MPUB_F_PROC_LOGS S
    SET S.VC_SQLCODE     = I_SQLCODE,
        S.VC_SQLERRM     = I_SQLERRM,
        S.T_ENDTIME     = V_ENDTIME,
        S.F_ELAPSEDTIME  = F_TIMESTAMP_DIFF(V_ENDTIME, V_STARTTIME),
        S.VC_STATUS      = I_STATUS
    WHERE S.VC_ID = I_ID;

  --程序异常...
  ELSIF I_STATUS = '-1' THEN

    UPDATE MPUB_F_PROC_LOGS S
    SET S.VC_SQLCODE     = I_SQLCODE,
        S.VC_SQLERRM     = I_SQLERRM,
        S.VC_ERR_CONTENT = I_ERR_CONTENT,
        S.VC_STATUS      = I_STATUS
    WHERE S.VC_ID = I_ID;

  END IF;

  COMMIT;

EXCEPTION
 WHEN OTHERS THEN
  ROLLBACK;

END P_MPUB_F_PROC_LOGS;
  • 主存储过程中调用日志记录存储过程
PROCEDURE P_TEST(I_STARTDATE IN  VARCHAR2,
                 I_ENDDATE   IN  VARCHAR2,
                 I_PARAM     IN  CLOB,
                 O_ERRCODE   OUT INTEGER,
                 O_ERRMSG    OUT VARCHAR2) IS
  /*****************************************************************************\
  *                           xxxxxx
  *           COPYRIGHT (C) 2020, xxxxxx
  * ===========================================================================
  *
  * 名     称:P_TEST
  * 功能 描述:xxxxx
  *
  * 参     数:传入 -> xxxxx
  *            传出 -> xxxxx
  *
  * 修订 记录:
  * 版本号   编辑时间      编辑人           修改描述
  * 1.0.0    2020-02-28    Li.chaoxiang     创建此存储过程
  \*****************************************************************************/
  V_STARTDATE DATE;          --开始日期
  V_ENDDATE   DATE;          --结束日期
  V_OWNER     VARCHAR2(20);  --所属用户
  V_ID        VARCHAR2(100); --唯一标识
BEGIN
  EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT=''YYYY-MM-DD''';
  O_ERRCODE   := 0;
  O_ERRMSG    := '运行成功';
  V_STARTDATE := TO_DATE(I_STARTDATE, 'YYYY-MM-DD');
  V_ENDDATE   := TO_DATE(I_ENDDATE, 'YYYY-MM-DD');

  --获取所属用户
  SELECT USERNAME INTO V_OWNER FROM USER_USERS;

  WHILE (V_LOOPDATE <= V_ENDDATE) LOOP

   --获取唯一标识
   SELECT SYS_GUID() INTO V_ID FROM DUAL;

   --程序开始,写入日志信息
   P_MPUB_F_PROC_LOGS(I_ID          => V_ID,
                      I_OWNER       => V_OWNER,
                      I_MODULE_NAME => 'PROCEDURE',
                      I_PROC_NAME   => 'P_TEST',
                      I_STARTDATE   => V_STARTDATE,
                      I_ENDDATE     => V_ENDDATE,
                      I_PARAMS      => V_FUNDCODE,
                      I_STATUS      => '1');


   --程序结束,更新状态
   P_MPUB_F_PROC_LOGS(I_ID      => V_ID,
                      I_SQLCODE => O_ERRCODE,
                      I_SQLERRM => O_ERRMSG,
                      I_STATUS  => '0');

   V_LOOPDATE := V_LOOPDATE + 1;

  END LOOP;

  COMMIT;

EXCEPTION
  WHEN OTHERS THEN
   --程序异常,写入日志信息
   P_MPUB_F_PROC_LOGS(I_ID          => V_ID,
                      I_SQLCODE     => SQLCODE,
                      I_SQLERRM     => SQLERRM,
                      I_ERR_CONTENT => SUBSTR('未处理异常:' || SQLERRM || '。' ||
                                              DBMS_UTILITY.FORMAT_ERROR_BACKTRACE,
                                              1,
                                              1000),
                      I_STATUS      => '-1');
   ROLLBACK;
   O_ERRCODE := SQLCODE;
   O_ERRMSG  := SUBSTR('未处理异常:' || SQLERRM || '。' ||
                       DBMS_UTILITY.FORMAT_ERROR_BACKTRACE,
                       1,
                       1000);

END P_TEST;

删除重复记录

要删除一张表中重复的记录,目前遇到的效率最高的方法就是通过 ROWID 来解决。

  • 构造数据
SQL> create table t_emp as select * from emp;

SQL> insert into t_emp select * from emp where rownum <= 5;

SQL> COMMIT;
  • 删除重复记录
SQL> delete from t_emp t1 
where t1.rowid > (
  select 
    min(t2.rowid)
  from t_emp t2
  where t1.empno = t2.empno /* 根据能唯一区分一行记录的字段进行匹配 */
);

SQL> COMMIT;

随机数

  • 从表中随机取记录
SQL> select * from (
  select * from emp order by dbms_random.random
) where rownum < 3;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7900 JAMES      CLERK      7698 1981/12/3      950.00               30
 7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30

表示从 EMP 表中随机取 2 条记录。

  • 产生随机数
# 产生一个任意大小的随机数
SQL> select dbms_random.random from dual;

    RANDOM
----------
 777926139


# 产生一个100以内的随机数
SQL> select abs(mod(dbms_random.random, 100)) from dual;

ABS(MOD(DBMS_RANDOM.RANDOM,100))
--------------------------------
                              25


# 产生一个100~1000之间的随机数
SQL> select trunc(100 + 900 * dbms_random.value) from dual;

TRUNC(100+900*DBMS_RANDOM.VALUE)
--------------------------------
                             269


# 产生一个0~1之间的随机数
SQL> select dbms_random.value from dual;

     VALUE
----------
0.48788019


# 产生一个10~20之间的随机数
SQL> select dbms_random.value(10, 20) from dual;

DBMS_RANDOM.VALUE(10,20)
------------------------
        10.6273692301178
  • 产生随机字符串
SQL> select dbms_random.string('P', 30) from dual;

DBMS_RANDOM.STRING('P',30)
--------------------------------------------------------------------------------
Y(b}<[;ne/S L1"5AD?YmxrT5mF6't

第一个参数 P 表示 printable,即字符串由任意可打印字符构成
第二个参数表示返回字符串的长度

'u','U' 表示大写字母字符串
'l','L' 表示小写字母字符串
'a','A' 表示大小写混合的字符串
'x','X' 表示大写字母加数字字符串
'p','P' 表示任何的可打印字符
  • 生成带分隔符(-)的 GUID 自定义函数
CREATE OR REPLACE FUNCTION MY_GUID RETURN VARCHAR2 IS     
  GUID   VARCHAR(36);     
  TEMP   VARCHAR(32); 
BEGIN     
  TEMP   := SYS_GUID();     
  GUID   := SUBSTR(TEMP, 1,  8) || '-' || 
            SUBSTR(TEMP, 9,  4) || '-' ||
            SUBSTR(TEMP, 13, 4) || '-' ||
            SUBSTR(TEMP, 17, 4) || '-' ||
            SUBSTR(TEMP, 21);     
  RETURN GUID; 
END;


SQL> select my_guid() from dual;

MY_GUID()
--------------------------------------------------------------------------------
E1229C82-26F1-4D0A-8E3A-38ABEB6C3A4A

日期函数

序号 函数 描述
1 TRUNC(DATE, 'iw') 本周以来日期
2 TRUNC(DATE, 'MM') 本月以来日期
3 TRUNC(DATE, 'Q') 本季以来日期
4 TRUNC(DATE, 'YYYY') 今年以来日期
5 DATE - 6 最近一周日期
6 ADD_MONTHS(DATE, -1) + 1 最近一月日期
7 ADD_MONTHS(DATE, -12) + 1 最近一年日期
8 LAST_DAY(ADD_MONTHS(DATE, -1)) 上月末
9 LAST_DAY(DATE) 本月末
  • 本周以来日期
# 本周以来日期以星期一为第一天

SQL> select 
  to_char(trunc(sysdate, 'iw') + 1, 'YYYY-MM-DD') as "本周以来日期",  
  to_char(sysdate, 'YYYY-MM-DD')                  as "当前日期"
from dual;

本周以来日期 当前日期
---------- ----------
2019-12-23 2019-12-25
  • 本月以来日期
SQL> select 
  to_char(trunc(sysdate, 'MM'), 'YYYY-MM-DD')  as "本月以来日期",  
  to_char(sysdate, 'YYYY-MM-DD')               as "当前日期"
from dual;

本月以来日期 当前日期
---------- ----------
2019-12-01 2019-12-25
  • 本季以来日期
SQL> select 
  to_char(trunc(sysdate, 'Q'), 'YYYY-MM-DD')  as "本季以来日期",  
  to_char(sysdate, 'YYYY-MM-DD')              as "当前日期"
from dual;

本季以来日期 当前日期
---------- ----------
2019-10-01 2019-12-25
  • 今年以来日期
SQL> select 
  to_char(trunc(sysdate, 'YYYY'), 'YYYY-MM-DD')  as "今年以来日期",  
  to_char(sysdate, 'YYYY-MM-DD')                 as "当前日期"
from dual;

今年以来日期 当前日期
---------- ----------
2019-01-01 2019-12-25
  • 最近一周日期
SQL> select 
  to_char((sysdate - 6), 'YYYY-MM-DD')  as "最近一周日期",  
  to_char(sysdate, 'YYYY-MM-DD')        as "当前日期"
from dual;

最近一周日期 当前日期
---------- ----------
2019-12-19 2019-12-25
  • 最近一月日期
SQL> select 
  to_char(add_months(sysdate, -1) + 1, 'YYYY-MM-DD')  as "最近一月日期",  
  to_char(sysdate, 'YYYY-MM-DD')                      as "当前日期"
from dual;

最近一月日期 当前日期
---------- ----------
2019-11-26 2019-12-25
  • 最近一年日期
SQL> select 
  to_char(add_months(sysdate, -12) + 1, 'YYYY-MM-DD')  as "最近一年日期",  
  to_char(sysdate, 'YYYY-MM-DD')                       as "当前日期"
from dual;

最近一年日期 当前日期
---------- ----------
2018-12-26 2019-12-25
  • 上月末
SQL> select 
  to_char(last_day(add_months(sysdate, -1)), 'YYYY-MM-DD')  as "上月末日期",  
  to_char(sysdate, 'YYYY-MM-DD')                            as "当前日期"
from dual;

上月末日期 当前日期
---------- ----------
2019-11-30 2019-12-25
  • 本月末
SQL> select 
  to_char(last_day(sysdate), 'YYYY-MM-DD')  as "本月末日期",  
  to_char(sysdate, 'YYYY-MM-DD')            as "当前日期"
from dual;

本月末日期 当前日期
---------- ----------
2019-12-31 2019-12-25

空值处理函数

序号 函数 描述
1 NVL(exp1, exp2) 如果 exp1 为空,则返回 exp2;否则返回 exp1
2 NVL2(exp1, exp2, exp3) 如果 exp1 为空,则返回 exp3;否则返回 exp2
3 NULLIF(exp1, exp2) 如果 exp1 等于 exp2,则返回空;否则返回 exp1
4 COALESCE(exp1, exp2 ... expn) 返回列表中第1个不为空的值,如果全部元素为空,则返回空
  • NVL
SQL> select nvl(1, 2) from dual;

  NVL(1,2)
----------
         1


SQL> select nvl(null, 2) from dual;

NVL(NULL,2)
-----------
          2
  • NVL2
SQL> select nvl2(2, 3, 4) from dual;

NVL2(2,3,4)
-----------
          3


SQL> select nvl2(null, 3, 4) from dual;

NVL2(NULL,3,4)
--------------
             4
  • NULLIF
SQL> select nullif(3, 2) from dual;

NULLIF(3,2)
-----------
          3


SQL> select nullif(3, 3) from dual;

NULLIF(3,3)
-----------
  • COALESCE
SQL> select coalesce(null, 1, null, 2, 3) from dual;

COALESCE(NULL,1,NULL,2,3)
-------------------------
                        1


SQL> select coalesce(null, null, null, 2, 3) from dual;

COALESCE(NULL,NULL,NULL,2,3)
----------------------------
                           2


SQL> select nvl(coalesce(null, null), 3) from dual;

NVL(COALESCE(NULL,NULL),3)
--------------------------
                         3

原创文章,转载请注明出处:http://www.opcoder.cn/article/13/