ORACLE 常用技巧（持续更新ing）

1、爬树：

WITH T AS (
SELECT '00' AS CODE_CHILD, '-1' AS CODE_PARENT FROM DUAL
UNION
SELECT '01' AS CODE_CHILD, '00' AS CODE_PARENT FROM DUAL
UNION
SELECT '02' AS CODE_CHILD, '01' AS CODE_PARENT FROM DUAL
UNION
SELECT '03' AS CODE_CHILD, '01' AS CODE_PARENT FROM DUAL
UNION
SELECT '04' AS CODE_CHILD, '03' AS CODE_PARENT FROM DUAL
UNION
SELECT '05' AS CODE_CHILD, '03' AS CODE_PARENT FROM DUAL
UNION
SELECT '06' AS CODE_CHILD, '03' AS CODE_PARENT FROM DUAL
UNION
SELECT '07' AS CODE_CHILD, '03' AS CODE_PARENT FROM DUAL
)

SELECT * FROM T
START WITH T.CODE_CHILD='03'
CONNECT BY PRIOR T.CODE_CHILD=T.CODE_PARENT --向下爬：找出所有子节点
--CONNECT BY PRIOR T.CODE_PARENT=T.CODE_CHILD --向上爬：找出所有父节点
;

 

2、将有分隔符的字符串转换为列表

A、程序块

DECLARE
	P_CODES VARCHAR2(500);

BEGIN
	P_CODES := '1,2,3,4,5,6';

	FOR ACT IN (SELECT REGEXP_SUBSTR(P_CODES, '[^,]+', 1, LEVEL) ACTION_CODE
						  FROM DUAL
						CONNECT BY LEVEL <=
								   LENGTH(P_CODES) -
								   LENGTH(REPLACE(P_CODES, ',', '')) + 1) LOOP
		DBMS_OUTPUT.PUT_LINE(ACT.ACTION_CODE);
	END LOOP;


	NULL;
END;

B、sql语句

WITH T AS
 (SELECT '1,2,3,4,5,6' AS P_CODES FROM DUAL)
SELECT REGEXP_SUBSTR(T.P_CODES, '[^,]+', 1, LEVEL) P_CODE
  FROM T
CONNECT BY LEVEL <=
		   LENGTH(T.P_CODES) - LENGTH(REPLACE(T.P_CODES, ',', '')) + 1;

 3、ORACLE换行符：字符串后拼CHR(10)   SELECT 'TESTING'||CHR(10)||"TESTING2" FROM DUAL;

 

4、导出数据文件相关脚本：
a)新建sql文件（本例的sql文件为test.sql）
test.sql的内容格式如下：
set pagesize 0
set echo off
set feed off
set term off
set heading off
set trims off
set colsep '[分隔符]'
set linesize 500
set pagesize 2000
spool [输出的文件路径]
[sql语句]
spool off

例子：

set pagesize 0
set echo off
set feed off
set term off
set heading off
set trims off
set colsep '|'
set linesize 500
set pagesize 2000
spool D:\log\dbtest\test.txt
select * from t_cum_group;
spool off

b)在sqlplus命令窗下输入：@[test.sql的所在文件路径]，例子：
@D:\log\dbtest\test.sql