Oracle整理
撰写时间： 2021-06-17 总共： 3033 字 转载请注明： BY-SA 4.0（除特别声明或转载文章外）
一些存储过程
获取表以指定符号分隔的列字段
Oracle 存在 WM_CONCAT() 可将某些字段以行转列的形式以逗号拼接
SELECT REPLACE(WM_CONCAT(A.COLUMN_NAME), ',', '||'',''||')
FROM ALL_COL_COMMENTS A
CREATE OR REPLACE FUNCTION GET_TABLE_COLUMS(IS_OWNER IN VARCHAR2,
IS_TABLE_NAME IN VARCHAR2,
IS_STR IN VARCHAR2 := ',')
RETURN VARCHAR2 IS
RESULT VARCHAR2(32767);
CV CLOB := '';
TYPE REF_CURSOR_TYPE IS REF CURSOR;
COLUMNS_CUR REF_CURSOR_TYPE;
VAL VARCHAR2(100);
V_OWNER VARCHAR2(100);
V_TABLE_NAME VARCHAR2(200);
V_R NUMBER := 0;
V_STR VARCHAR2(100);
BEGIN
V_OWNER := IS_OWNER;
V_TABLE_NAME := IS_TABLE_NAME;
V_STR := IS_STR;
OPEN COLUMNS_CUR FOR
SELECT B.COLUMN_NAME AS COLUMN_NAME
FROM ALL_TAB_COLUMNS B
WHERE B.TABLE_NAME = V_TABLE_NAME
AND B.OWNER = V_OWNER
ORDER BY B.COLUMN_ID;
LOOP
FETCH COLUMNS_CUR
INTO VAL;
EXIT WHEN COLUMNS_CUR%NOTFOUND;
IF MOD(V_R, 5) = 0
THEN
CV := CV || V_STR || CHR(10) || VAL;
V_R := 0;
ELSE
CV := CV || V_STR || VAL;
END IF;
V_R := V_R + 1;
END LOOP;
DBMS_OUTPUT.PUT_LINE(V_STR || ':' || LENGTH(V_STR));
CV := SUBSTR(CV, LENGTH(V_STR) + 2); -- 移除逗号和换行
RESULT := TO_CHAR(CV);
CLOSE COLUMNS_CUR;
CV := '';
RETURN(RESULT);
END GET_TABLE_COLUMS;
SQL_TO_CSV
CREATE OR REPLACE PROCEDURE SQL_TO_CSV(P_QUERY IN VARCHAR2, -- PLSQL文
P_DIR IN VARCHAR2, -- 导出的文件放置目录
P_FILENAME IN VARCHAR2 -- CSV名
) IS
L_OUTPUT UTL_FILE.FILE_TYPE;
L_THECURSOR INTEGER DEFAULT DBMS_SQL.OPEN_CURSOR;
L_COLUMNVALUE VARCHAR2(4000);
L_STATUS INTEGER;
L_COLCNT NUMBER := 0;
L_SEPARATOR VARCHAR2(1);
L_DESCTBL DBMS_SQL.DESC_TAB;
P_MAX_LINESIZE NUMBER := 32000;
BEGIN
--OPEN FILE
L_OUTPUT := UTL_FILE.FOPEN(P_DIR, P_FILENAME, 'W', P_MAX_LINESIZE);
--DEFINE DATE FORMAT
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT=''YYYY-MM-DD HH24:MI:SS''';
--OPEN CURSOR
DBMS_SQL.PARSE(L_THECURSOR, P_QUERY, DBMS_SQL.NATIVE);
DBMS_SQL.DESCRIBE_COLUMNS(L_THECURSOR, L_COLCNT, L_DESCTBL);
--DUMP TABLE COLUMN NAME
FOR I IN 1 .. L_COLCNT LOOP
UTL_FILE.PUT(L_OUTPUT,
L_SEPARATOR || '"' || L_DESCTBL(I).COL_NAME || '"'); --输出表字段
DBMS_SQL.DEFINE_COLUMN(L_THECURSOR, I, L_COLUMNVALUE, 4000);
L_SEPARATOR := ',';
END LOOP;
UTL_FILE.NEW_LINE(L_OUTPUT); --输出表字段
--EXECUTE THE QUERY STATEMENT
L_STATUS := DBMS_SQL.EXECUTE(L_THECURSOR);
--DUMP TABLE COLUMN VALUE
WHILE (DBMS_SQL.FETCH_ROWS(L_THECURSOR) > 0) LOOP
L_SEPARATOR := '';
FOR I IN 1 .. L_COLCNT LOOP
DBMS_SQL.COLUMN_VALUE(L_THECURSOR, I, L_COLUMNVALUE);
UTL_FILE.PUT(L_OUTPUT,
L_SEPARATOR || '"' ||
TRIM(BOTH ' ' FROM REPLACE(L_COLUMNVALUE, '"', '""')) || '"');
L_SEPARATOR := ',';
END LOOP;
UTL_FILE.NEW_LINE(L_OUTPUT);
END LOOP;
--CLOSE CURSOR
DBMS_SQL.CLOSE_CURSOR(L_THECURSOR);
--CLOSE FILE
UTL_FILE.FCLOSE(L_OUTPUT);
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
一些脚本
SPOOL TO FILE
set colsep ,
set feedback off
set heading off
set newp none
set pagesize 0
set linesize 200
set trimout on
spool c:/data/export.csv
select t.name||','||t.age||','||t.salary||','||t.email||','||t.title
from employee t
where t.age < 50
order by t.salary desc;
spool off
exit
获取日历
SELECT TO_CHAR(TO_DATE('19000101', 'YYYYMMDD') + ROWNUM - 1, 'YYYYMMDD') AS DATA_DATE
FROM DUAL
CONNECT BY ROWNUM <= TRUNC(TO_DATE('20210616', 'YYYYMMDD') + 1 -
TO_DATE('19000101', 'YYYYMMDD'));
WITH X0 AS
(SELECT TO_DATE('2020-01-01', 'yyyy-MM-dd') AS 年初,
TO_DATE('2020-02-29', 'yyyy-MM-dd') AS 年末
FROM DUAL),
X1 AS
(SELECT 年初 + LEVEL - 1 AS 日期
FROM X0
CONNECT BY LEVEL <= (年末 - 年初) + 1),
X2 AS
(SELECT 日期, TO_NUMBER(TO_CHAR(日期, 'd')) 周几 FROM X1)
SELECT 日期,
(CASE
WHEN 周几 = 1
OR 周几 = 7 THEN
'1'
ELSE
'0'
END) AS 工作日标志
FROM X2;
