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]
oracle—spool—时间变量
最新推荐文章于 2024-07-04 15:40:58 发布