用Oracle的DBMS_OUTPUT包产生CSV文件

关注

转载最新推荐文章于 2026-05-06 17:14:07 发布 1.3k 阅读 AI 写同款· GEO 优化 ›

FX2N-2AD模块实战：从硬件连接到FROM/TO指令编程详解本文详细解析FX2N-2AD模拟量输入模块的硬件连接与FROM/TO指令编程技巧，涵盖BFM缓冲存储器配置、梯形图程序设计及工程实战经验。通过实例演示如何实现精准信号采集与数据处理，特别针对PLC系统中的常见故障提供解决方案，助力工业自动化工程师高效完成设备调试与维护。阅读详情

试过很多产生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;
/