oracle—spool—时间变量

ismpsmp1@zxin10[/home/zxin10/task/mazongxiao]more test.sh
sqlplus  zxdbm_ismp/zxin_smap <<EOF
@/home/zxin10/task/mazongxiao/test.sql
/
column spf new_value spf noprint
select '/home/zxin10/task/mazongxiao/'||to_char(sysdate,'yyyymmdd')||'.txt'  spf from dual;
spool &spf
@/home/zxin10/task/mazongxiao/unload.sql
spool off
EOF
ismpsmp1@zxin10[/home/zxin10/task/mazongxiao]more test.sql
declare
 dd varchar(8);
 num int;
   begin
 select to_char(sysdate-1,'yyyymmdd')into dd from dual;
 select count(1) into num from user_tables where table_name='MA_TEST1';
   if num>0 then
 execute immediate 'drop table ma_test1';
   end if;
 execute immediate 'create table ma_test1 as (select col001,count(*) cc from test where col001 = '''||dd||''' group by col001)';
   end;
ismpsmp1@zxin10[/home/zxin10/task/mazongxiao]more unload.sql
select * from ma_test1;
ismpsmp1@zxin10[/home/zxin10/task/mazongxiao]