PL/SQL 将 Oracle 表假脱机到一个 csv 文件

PL/SQL spool Oracle tables to one csv file

提问by user2173818

Is it possible to spool multiple Oracle tables to one csv file (in different wooksheets)? I so, how?

是否可以将多个 Oracle 表假脱机到一个 csv 文件（在不同的工作表中）？我是这样，怎么办？

Thanks :)

谢谢 ：）

回答by Ed Gibbs

Here's how to spool one table to one CSV, using SQL*Plus. As noted, you need a separate CSV for each table/query - that's how CSV's and Excel work.

下面介绍了如何使用 SQL*Plus 将一张表假脱机到一个 CSV。如前所述，每个表/查询都需要一个单独的 CSV - 这就是 CSV 和 Excel 的工作方式。

This example spools some of the tables from the Oracle HR.Employeessample table. Here's what the table looks like:

此示例从 OracleHR.Employees示例表中假脱机处理一些表。这是表的样子：

SQL> desc hr.employees
 Name            Null?    Type
 --------------- -------- -------------
 EMPLOYEE_ID     NOT NULL NUMBER(6)
 FIRST_NAME               VARCHAR2(20)
 LAST_NAME       NOT NULL VARCHAR2(25)
 EMAIL           NOT NULL VARCHAR2(25)
 PHONE_NUMBER             VARCHAR2(20)
 HIRE_DATE       NOT NULL DATE
 JOB_ID          NOT NULL VARCHAR2(10)
 SALARY                   NUMBER(8,2)
 COMMISSION_PCT           NUMBER(2,2)
 MANAGER_ID               NUMBER(6)
 DEPARTMENT_ID            NUMBER(4)

Here's a SQL script that spools some of the HRcolumns. It includes numeric, char and date values:

这是一个用于假脱机某些HR列的 SQL 脚本。它包括数字、字符和日期值：

-- SpoolEmployeesToCSV.sql
SET ECHO OFF
SET TERMOUT OFF
SET TRIMSPOOL ON
SET PAGESIZE 0
SET LINESIZE 2000
SET FEEDBACK OFF
SPOOL c:\Business\HREmployees.csv

SELECT
  TO_CHAR(Employee_ID) || ',' ||
  '"' || REPLACE(First_Name, '"', '""') || '"' ||
  ',"' || REPLACE(Last_Name, '"', '""') || '",' ||
  ',' || TO_CHAR(Hire_Date, 'MM/DD/YYYY') ||
  ',' || TO_CHAR(Salary)
FROM HR.Employees

SPOOL OFF

This script happens to be named SpoolEmployeesToCSV.sql. To call it from SQL*Plus just go like this:

这个脚本恰好被命名为SpoolEmployeesToCSV.sql. 要从 SQL*Plus 调用它，就像这样：

SQL>@SpoolEmployeesToCSV.sql

That's all. You may need to experiment with different date and number formats, but the main thing about spooling to a file is all of the SETcommands at the top of the script, plus the fact that allof the commands are in the script - if you try to execute them directly from the SQL*Plus command line they'll end up in the spooled output.

就这样。您可能需要尝试不同的日期和数字格式，但将假脱机到文件的主要内容是SET脚本顶部的所有命令，以及所有命令都在脚本中的事实- 如果您尝试直接从 SQL*Plus 命令行执行它们，它们最终会出现在假脱机输出中。

[Edit] One more thing - the SET LINESIZE 2000. You have to set the value larger than your expected line size. It can be a lot larger because the SET TRIMSPOOL ONwill chop each line down to size.

[编辑] 还有一件事 - SET LINESIZE 2000. 您必须将值设置为大于预期的行大小。它可以大很多，因为SET TRIMSPOOL ON它将把每一行都缩小到一定的大小。