在 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 PROCEDURE P_EMP_CURSOR(I_EMPNO IN VARCHAR2, O_RETURN OUT SYS_REFCURSOR) IS /**************************************************************** * 名 称：P_EMP_CURSOR * 功能 描述：存储过程游标输出参数 *****************************************************************/ V_EMPNO VARCHAR2(100); V_CNT NUMBER; BEGIN EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT=''YYYY-MM-DD'''; V_EMPNO := I_EMPNO; EXECUTE IMMEDIATE 'TRUNCATE TABLE EMP_C'; SELECT COUNT(1) INTO V_CNT FROM EMP WHERE EMPNO = V_EMPNO AND ROWNUM = 1; IF V_CNT = 1 THEN INSERT INTO EMP_C SELECT * FROM EMP WHERE EMPNO = V_EMPNO; END IF; COMMIT; OPEN O_RETURN FOR SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO FROM EMP_C; EXCEPTION WHEN OTHERS THEN ROLLBACK; END P_EMP_CURSOR;
- 调用
CREATE OR REPLACE PROCEDURE P_EMP_CURSOR_REF(I_STARTDATE IN VARCHAR2, I_ENDDATE IN VARCHAR2, O_ERRCODE OUT INTEGER, O_ERRMSG OUT VARCHAR2) IS /**************************************************************** * 名 称：P_EMP_CURSOR_REF * 功能 描述：存储过程游标输出参数的调用 *****************************************************************/ V_STARTDATE DATE; -- 开始日期 V_ENDDATE DATE; -- 结束日期 V_EMPNO NUMBER; V_CURSOR SYS_REFCURSOR; V_TABLE EMP_REF%ROWTYPE; BEGIN EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT=''YYYY-MM-DD'''; V_STARTDATE := TO_DATE(I_STARTDATE, 'YYYY-MM-DD'); V_ENDDATE := TO_DATE(I_ENDDATE, 'YYYY-MM-DD'); V_EMPNO := 7369; O_ERRCODE := 0; O_ERRMSG := '运行成功'; EXECUTE IMMEDIATE 'TRUNCATE TABLE EMP_REF'; -- 调用存储过程 P_EMP_CURSOR(V_EMPNO, V_CURSOR); -- 处理游标数据 LOOP FETCH V_CURSOR INTO V_TABLE; EXIT WHEN V_CURSOR%NOTFOUND; INSERT INTO EMP_REF( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( V_TABLE.EMPNO, V_TABLE.ENAME, V_TABLE.JOB, V_TABLE.MGR, V_TABLE.HIREDATE, V_TABLE.SAL, V_TABLE.COMM, V_TABLE.DEPTNO ); END LOOP; COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; O_ERRCODE := SQLCODE; O_ERRMSG := SUBSTR('未处理异常:' || SQLERRM || '。' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, 1, 1000); END P_EMP_CURSOR_REF;
函数返回游标类型
--声明函数
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
每月月末日期序列
# 获取月末日期 SQL> SELECT ADD_MONTHS(LAST_DAY(DATE'2021-01-01'), + ROWNUM - 1) AS LASTDATE FROM DUAL CONNECT BY ROWNUM <= MONTHS_BETWEEN(DATE'2022-01-01', DATE'2021-01-01'); DATE ---------- 2021-01-31 2021-02-28 2021-03-31 2021-04-30 2021-05-31 2021-06-30 2021-07-31 2021-08-31 2021-09-30 2021-10-31 2021-11-30 2021-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) | 本月末 |
| 10 | TRUNC(DATE, 'd') | 本周第一天 |
- 本周以来日期
# 本周以来日期以星期一为第一天 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
- 本周第一天
SQL> select to_char(trunc(sysdate, 'd'), 'YYYY-MM-DD') as "本周第一天", to_char(sysdate, 'YYYY-MM-DD') as "当前日期" from dual; 本周第一天 当前日期 ---------- ---------- 2022-10-16 2022-10-19
空值处理函数
| 序号 | 函数 | 描述 |
| 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
EXISTS 和 NOT EXISTS 的改写
- EXISTS
exists 改写成 inner join
select * from emp a where exists ( select 1 from emp b where a.empno = b.empno and b.deptno = 20 ); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ---------- --------- ----- ----------- --------- --------- ------ 7369 SMITH CLERK 7902 1980/12/17 800.00 20 7566 JONES MANAGER 7839 1981/4/2 2975.00 20 7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20 7876 ADAMS CLERK 7788 1987/5/23 1100.00 20 7902 FORD ANALYST 7566 1981/12/3 3000.00 20
select a.* from emp a inner join emp b on a.empno = b.empno and b.deptno = 20; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ---------- --------- ----- ----------- --------- --------- ------ 7369 SMITH CLERK 7902 1980/12/17 800.00 20 7566 JONES MANAGER 7839 1981/4/2 2975.00 20 7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20 7876 ADAMS CLERK 7788 1987/5/23 1100.00 20 7902 FORD ANALYST 7566 1981/12/3 3000.00 20
- NOT EXISTS
not exists 改写成 left outer join + is null
select * from emp a where not exists ( select 1 from emp b where a.empno = b.empno and b.deptno = 20 ); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ---------- --------- ----- ----------- --------- --------- ------ 7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30 7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30 7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30 7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30 7782 CLARK MANAGER 7839 1981/6/9 2450.00 10 7839 KING PRESIDENT 1981/11/17 5000.00 10 7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30 7900 JAMES CLERK 7698 1981/12/3 950.00 30 7934 MILLER CLERK 7782 1982/1/23 1300.00 10
select a.* from emp a left outer join emp b on a.empno = b.empno and b.deptno = 20 where b.empno is null; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ---------- --------- ----- ----------- --------- --------- ------ 7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30 7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30 7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30 7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30 7782 CLARK MANAGER 7839 1981/6/9 2450.00 10 7839 KING PRESIDENT 1981/11/17 5000.00 10 7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30 7900 JAMES CLERK 7698 1981/12/3 950.00 30 7934 MILLER CLERK 7782 1982/1/23 1300.00 10
原创文章，转载请注明出处：http://www.opcoder.cn/article/13/