使用SQL*Plus生产CSV格式数据

oracle, 技术分享

使用SQL*Plus生产CSV格式数据

#工程师培训

#sqlplus#

因为Excel、很多BI工具、甚至一些数据库，都支持直接导入CSV(comma separated value)格式数据，所以一直以来，我们都有将Oracle数据库中表的数据行导出成为CSV格式的需求。从Oracle 12.2.0.1开始，SQL*Plus支持将查询输出为CSV格式。本文对此新特性进行演示。

演示环境

[SQL*Plus] CREATE TABLE BOOKS (
   BOOK_ID    NUMBER GENERATED ALWAYS AS IDENTITY,
   TITLE      VARCHAR2(250) NOT NULL,
   PAGES      NUMBER NOT NULL,
   AUTHOR     VARCHAR2(250) NOT NULL,
   CONDITION  VARCHAR2(4000),
   CONSTRAINT BOOKS_PK PRIMARY KEY (BOOK_ID),
   CONSTRAINT BOOKS_UQ UNIQUE (TITLE)
);
 
[SQL*Plus] INSERT INTO BOOKS (TITLE, PAGES, AUTHOR, CONDITION) VALUES ('The Hobbit', 322, 'J. R. R. Tolkien', 'Good condition');
[SQL*Plus] INSERT INTO BOOKS (TITLE, PAGES, AUTHOR, CONDITION) VALUES ('Harry Potter and the Philosopher''s Stone', 324, 'J.K. Rowling', 'New');
[SQL*Plus] INSERT INTO BOOKS (TITLE, PAGES, AUTHOR, CONDITION) VALUES (‘The Holy Bible’, 1328, ‘Crossway Bibles’, ‘Good condition, but “mandatory” pages are missing’);


使用新特性

Oracle 12.2.0.1 SQL*Plus中，SET命令引入了新的 MARKUP CSV用法：

[SQL*Plus] SET MARK[UP] CSV {ON|OFF} DELIMI[TER] character QUOTE {ON|OFF}

  • DELIMITER 默认是逗号(,)，也可以指定为你希望的字符
  • QUOTE 默认为OFF，打开此选项，列的输出值会用双引号(“)包围，如果值内部本身有双引号，会被Escape(“”)。如果值不是字符类型的，不会用引用QUOTE。

测试

使用QUOTE

[SQL*Plus] SET MARKUP CSV ON QUOTE ON
[SQL*Plus] SELECT * FROM BOOKS;
 
“BOOK_ID”,”TITLE”,”PAGES”,”AUTHOR”,”CONDITION”
1,”The Hobbit”,322,”J. R. R. Tolkien”,”Good condition”
2,”Harry Potter and the Philosopher’s Stone”,324,”J.K. Rowling”,”New”
3,”The Holy Bible”,1328,"Crossway Bibles”,”Good condition, but “”mandatory”” pages are missing”


要注意值内部的双引号是如何Escape的，也可以看到对于Number型的数据，并没有使用QUOTE。

指定不同的Delimiter

[SQL*Plus] SET MARKUP CSV ON DELIMITER | 
[SQL*Plus] SELECT * FROM BOOKS; 
 
“BOOK_ID”|”TITLE”|”PAGES”|”AUTHOR”|”CONDITION”
1|”The Hobbit”|322|”J. R. R. Tolkien”|”Good condition”
2|”Harry Potter and the Philosopher’s Stone”|324|”J.K. Rowling”|”New”
3|"The Holy Bible"|1328|"Crossway Bibles"|"Good condition, but ""mandatory"" pages are missing"



在命令行（Command Line）中使用

[oracle] sqlplus -S -M "csv on” / as sysdba @/tmp/get_books.sql | sed ‘/^$/d’
“BOOK_ID”,”TITLE”,”PAGES”,”AUTHOR”,”CONDITION”
1,”The Hobbit”,322,”J. R. R. Tolkien”,”Good condition”
2,”Harry Potter and the Philosopher’s Stone”,324,”J.K. Rowling”,”New”
3,"The Holy Bible",1328,"Crossway Bibles","Good condition, but ""mandatory"" pages are missing"


  • -S 参数使SQL*Plus运行在silent模式，no banner、no prompt、no echoing of commands
  • sed 命令删除空白行

其他方式

也可以使用SQLDeveloper或者SQLcl更轻松的完成，SQLcl好像在12.2.0.1版本之后会自动安装。

参考文档： ask-tom