oracle—spool—时间变量

来源：互联网发布：深圳立诺软件编辑：程序博客网时间：2024/05/18 02:42

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]