用Oracle的DBMS_OUTPUT包产生CSV文件

最新推荐文章于 2022-04-11 11:50:59 发布

suncrafted

最新推荐文章于 2022-04-11 11:50:59 发布

阅读量1.2k

点赞数

分类专栏： ORACLE 文章标签： csv oracle output null table date

ORACLE 专栏收录该内容

71 篇文章

订阅专栏

本文介绍了一种使用Oracle数据库的DBMS_OUTPUT包生成CSV文件的方法。通过创建特定的PL/SQL包，可以方便地从数据库表中导出数据到CSV格式，并提供了插入和删除记录的过程。示例展示了如何设置并使用该包来生成包含大量记录的CSV文件。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

试过很多产生CSV文件的方法后，发现用Oracle的DBMS_OUTPUT就可以实现，例子代码如下：

1 创建一个数据库表

Drop TABLE VEHICLE;
Create TABLE VEHICLE (
MAKE VARCHAR2(256) NOT NULL,
MODEL VARCHAR2(256),
REGISTRATION_NO NUMBER(15) NOT NULL primary key,
AGE NUMBER(2,1) NOT NULL,
CATEGORY VARCHAR(1) NOT NULL,
MILAGE NUMBER(15,2) NOT NULL,
LAST_SERVICE_DATE DATE NOT NULL
);

2.创建PL/SQL包

-- Package Header Definition
CREATE OR REPLACE PACKAGE ISS_CSV IS
PROCEDURE WCSV(
inRegStart IN NUMBER,
inRegStop IN NUMBER,
inFSep IN VARCHAR2 :=',',
inRSep IN VARCHAR2 :='/n'
);
PROCEDURE AddRec
(startNum IN NUMBER,
endNum IN NUMBER
);
PROCEDURE DelRec
(startNum IN NUMBER,
endNum IN NUMBER
);
--PROCEDURE CreateSampTab(tabName IN VARCHAR2);
END;
/

-- Package Body Definition
CREATE OR REPLACE PACKAGE BODY ISS_CSV
IS PROCEDURE WCSV(
inRegStart IN NUMBER,
inRegStop IN NUMBER,
inFSep IN VARCHAR2,
inRSep IN VARCHAR2
) IS
cursor p_cursor is SELECT MAKE||inFSep||MODEL||inFSep||REGISTRATION_NO||inFSep||AGE||inFSep||CATEGORY||
inFSep||MILAGE||inFSep||LAST_SERVICE_DATE||inRSep strRet FROM vehicle WHERE registration_no>=inRegStart
and registration_no<=inRegStop;
c1rec p_cursor%rowtype;
p_errorcode NUMBER :=0;
start_time TIMESTAMP;
right_now TIMESTAMP;
F1 UTL_FILE.FILE_TYPE;
BEGIN
F1 := UTL_FILE.FOPEN('c:/temp','THOMAS.CSV','W',32767);
start_time := SYSTIMESTAMP;
-- OPEN p_cursor;
for c1rec in p_cursor loop
   UTL_FILE.PUT_LINE(F1,c1rec.strRet);
end loop;
UTL_FILE.FCLOSE(F1);
-- CLOSE p_cursor;
right_now := SYSTIMESTAMP;
dbms_output.put_line(to_char(right_now - start_time));
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN;
WHEN UTL_FILE.INVALID_PATH THEN
DBMS_OUTPUT.PUT_LINE('INVALID PATH');
WHEN UTL_FILE.INVALID_MODE THEN
DBMS_OUTPUT.PUT_LINE('INVALID MODE');
WHEN UTL_FILE.INVALID_FILEHANDLE THEN
DBMS_OUTPUT.PUT_LINE('INVALID FILEHANDLE');
WHEN UTL_FILE.INVALID_OPERATION THEN
DBMS_OUTPUT.PUT_LINE('INVALID OPERATION');
WHEN UTL_FILE.READ_ERROR THEN
DBMS_OUTPUT.PUT_LINE('READ ERROR');
WHEN UTL_FILE.WRITE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('WRITE ERROR');
WHEN UTL_FILE.INTERNAL_ERROR THEN
DBMS_OUTPUT.PUT_LINE('INTERNAL ERROR');
WHEN OTHERS THEN
   DBMS_OUTPUT.PUT_LINE(SQLERRM);
   p_errorcode:= SQLCODE;
   dbms_output.put_line('Error, Rollback'||to_char(p_errorcode));
END;

-- Insert Procedure --
PROCEDURE AddRec
(startNum IN NUMBER
,endNum IN NUMBER
) IS
i number:=startNum;
j number:=endNum;
BEGIN
dbms_output.put_line(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') );
While i<=j Loop
INSERT INTO VEHICLE (REGISTRATION_NO,MAKE,MODEL,AGE,CATEGORY,MILAGE,LAST_SERVICE_DATE)
VALUES(i,'Oracle 10g R2 -Key'||i,'Thomas Kyte Oracle',3,'A',1000,SYSDATE);
i:=i+1;
End Loop;
commit;
dbms_output.put_line(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') );
END;

-- Delete Procedure --
PROCEDURE DelRec
( startNum IN NUMBER
, endNum IN NUMBER
) IS
i number:=startNum;
j number:=endNum;
BEGIN
dbms_output.put_line(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') );
Delete From VEHICLE Where REGISTRATION_NO>=i and REGISTRATION_NO<=j;
dbms_output.put_line(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') );
Commit;
END;

--Create Sample Table --
--PROCEDURE CreateSampTab(tabName IN VARCHAR2
--) IS
--BEGIN
--Drop TABLE VEHICLE;
--Create TABLE VEHICLE (
--MAKE VARCHAR2(256) NOT NULL,
--MODEL VARCHAR2(256),
--REGISTRATION_NO NUMBER(15) NOT NULL primary key,
--AGE NUMBER(2,1) NOT NULL,
--CATEGORY VARCHAR(1) NOT NULL,
--MILAGE NUMBER(15,2) NOT NULL,
--LAST_SERVICE_DATE DATE NOT NULL
--);
--EXCEPTION
--WHEN OTHERS THEN
--    dbms_output.put_line('Error When creating Sample Table');
--END;
END;
/