假脱机多个文件 [英] Spooling multiple files

问题描述

我有一份报告，需要为 n 个供应商导出到 csv 文件.我有一种感觉，我需要不止一次地运行它，所以我想尽可能地自动化它.我为一个供应商编写了所需的 sql plus，我想知道如何编写脚本来为每个供应商运行.我将供应商列表存储在 db 的表中，但知道我不能将 spool 放在 pl/sql 块中，我想知道如何遍历每个供应商以创建他们的文件.

I have a report that I need to export to a csv file for n number of vendors. i have a feeling that I will need to run this more than once so I would like to automate it as much as possible. I wrote the sql plus needed to for one vendor, I'm wonder how can script it to run for each vendor. I have the vendor list stored in the table in the db, but knowing I can't put spool in an pl/sql block, I'm wonder how I can loop through each vendor to create their file.

推荐答案

我做过类似的事情，让一个脚本生成一个辅助脚本，然后执行该脚本.近似示例:

I've done similar things by having one script generate a secondary script and then execute that script. Approximate example:

set serveroutput on
set termout off
spool temp_script.sql
Begin
    for r in (select * from vendors) loop
        dbms_output.put_line('spool '||r.vendor_name||'.csv');
        dbms_output.put_line('data is: '||r.data);
        /*feel free to put other output commands here */
        dbms_output.put_line('spool off');
    end loop;
end;
spool off
@temp_script.sql

这篇关于假脱机多个文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！