ismpsmp1@zxin10[/home/zxin10/task/mazongxiao]more test.shsqlplus zxdbm_ismp/zxin_smap <<EOF@/home/zxin10/task/mazongxiao/test.sql/column spf new_value spf noprintselect '/home/zxin10/task/mazongxiao/'||to_char(sysdate,'yyyymmdd')||'.txt' spf from dual;spool &spf@/home/zxin10/task/mazongxiao/unload.sqlspool offEOFismpsmp1@zxin10[/home/zxin10/task/mazongxiao]more test.sqldeclare 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.sqlselect * from ma_test1;ismpsmp1@zxin10[/home/zxin10/task/mazongxiao]
