在sqlplus中使用动态CSV循环时 [英] While looping with dynamic CSV in sqlplus

问题描述

我是sqlplus的新手，我需要帮助.我在下面进行选择，我想添加一个While循环以为该期间的每一天生成CSV文件.

I'm new at sqlplus and I need a help. I have the select below, and I want to add a While looping to generate a CSV file for each day of the period.

有人可以帮助我吗?

谢谢！

set pagesize 0
set colsep '|'
set echo off
set feedback off
set linesize 1000
set trimspool on
set headsep off

define start_date = '01/01/2004'
define end_date = '02/01/2004'
define start_csv = TO_CHAR(TO_DATE('&start_date.','DD/MM/YYYY'), 'YYYY-MM-DD')
define end_csv = TO_CHAR(TO_DATE('&start_date.','DD/MM/YYYY'), 'YYYY-MM-DD')

spool 'C:\Extracted_&start_csv._&end_csv..csv'

SELECT <MY SELECT>
FROM <MY TABLES>
WHERE DATE BETWEEN '&start_date.' AND '&end_date.'

spool OFF;

SET echo ON
SET feedback ON
SET headsep ON

这是我在PL/SQL版本中的第一次尝试:

EDIT 1: Here my first attempt in PL/SQL version:

DECLARE
    -- I pretend to use this variable later, that's why I sent the value to ACTUAL_DATE to run the looping
    START_DATE DATE := '01/01/2004';
    END_DATE DATE := '31/12/2009';
    ACTUAL_DATE DATE := START_DATE;
    DT VARCHAR2(10);

BEGIN
    WHILE ACTUAL_DATE <= END_DATE LOOP
        DT := TO_CHAR(TO_DATE(ACTUAL_DATE, 'DD/MM/YYYY'), 'YYYY-MM-DD');

        --sqlplus only
        --SPOOL 'C:\EXTRACTED_&DT..CSV'

        SELECT <MY SELECT>
        FROM <MY TABLES>
        WHERE DATE = '&ACTUAL_DATE.'

        --SPOOL OFF;
        ACTUAL_DATE := ACTUAL_DATE + 1;
    END LOOP;
END;
/

推荐答案

您可以使用SQL代码生成脚本.例如，以下SQL语句将生成一个SQL * Plus脚本，用于对每天创建的DBA_OBJECTS进行后台处理以分隔.csv文件:

You could use SQL code to generate the script. For example, the following SQL statement will generate a SQL*Plus script for spooling DBA_OBJECTS created on a day by day basis to separate .csv files:

with head as (select 0 ord, 'set echo off linesize 200 pagesize 2000 heading off feedback off' cmd from dual)
, main as
(
select rownum ord, cmd 
from 
(
select 'spool c:\temp\csv_'||to_char(trunc(created),'YYYY_MM_DD')||'.csv'
|| chr(13)||chr(10) ||
'select owner || '','' || object_type|| '','' || object_name from dba_objects where trunc(created) = to_date(''' || to_char(trunc(created),'YYYY_MM_DD') || ''', ''YYYY_MM_DD''' || ') order by owner, object_type, object_name ; '
|| chr(13)||chr(10) ||
'spool off ' 
|| chr(13)||chr(10) 
|| chr(13)||chr(10) as cmd
from DBA_objects
where created between sysdate-15 and sysdate
group by trunc(created)
order by trunc(created)
)
)
, script as (select * from head union select * from main)
select cmd from script order by ord
;

这篇关于在sqlplus中使用动态CSV循环时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！