脚本内容：业务部门需要定期从某库取出质保数据：
cat zhibaodata.sh #!/bin/sh export DISPLAY=:0 if [ -f ~/.bash_profile ]; then . ~/.bash_profile fi sh /home/oracle/.bash_profile Sql_Directory=/u01/temp/scripts/zhibaotable sqlplus -s system/uiu @${Sql_Directory}/zhibaocreatetable.sh
cat zhibaocreatetable.sh set serveroutput on declare v_date varchar2(8); v_table1 varchar2(32); v_table2 varchar2(32); v_table3 varchar2(40); v_table4 varchar2(32); v_table5 varchar2(32); v_table6 varchar2(32); v_table7 varchar2(32); v_table8 varchar2(32); v_table9 varchar2(32); v_table10 varchar2(32); v_table11 varchar2(32); v_table12 varchar2(32); v_sqlt varchar2(2000); v_sql1 varchar2(2000); v_sql2 varchar2(2000); v_sql3 varchar2(2000); v_sql4 varchar2(2000); v_sql5 varchar2(2000); v_sql6 varchar2(2000); v_sql7 varchar2(2000); v_sql8 varchar2(2000); v_sql9 varchar2(2000); v_sql10 varchar2(2000); v_sql11 varchar2(2000); v_sql12 varchar2(2000); v_sqlw varchar2(2000); v_username varchar2(20); v_userpasswd varchar2(20); begin select to_char(sysdate-10,'yyyymmdd') into v_date from dual; v_username := 'lsp'||v_date; v_userpasswd := 'sp'||v_date||'*'; v_table1 := 'user.SCCOUNT'||v_date; v_table2 := 'user.SOUNT' ||v_date; v_table3 := 'user.RESTLIT'||v_date; v_table4 := 'useJNEE'||v_date; v_table5 := 'user._FEE'||v_date; v_table6 := 'user.N_SPLIT'||v_date; v_table7 := 'useITAL_PC'||v_date; v_table8 := 'user._CAPITAL_SD_PC'||v_date; v_table9 := 'user.STEREST'||v_date; v_table10 := 'user.SJM_NTEREST'||v_date; v_table11 := 'user.SJM_FEE'||v_date; v_table12 := 'user.STAL_DFCH'||v_date; v_sqlt := 'create database link neworclau connect to '|| v_username ||' identified by '|| v_userpasswd || ' using '''||'audit'||''' '; v_sql1 := 'create table '||v_table1||' as select * from CCOUNT@neworclau ' ; v_sql2 := 'create table '||v_table2||' as select * from SMOUNT@neworclau ' ; v_sql3 := 'create table '||v_table3||' as select * from SJTEREST_SPLIT@neworclau ' ; v_sql4 := 'create table '||v_table4||' as select * from SJEE@neworclau ' ; v_sql5 := 'create table '||v_table5||' as select * from SJMFEE@neworclau ' ; v_sql6 := 'create table '||v_table6||' as select * from SJN_SPLIT@neworclau ' ; v_sql7 := 'create table '||v_table7||' as select * from SAPITAL_PC@neworclau ' ; v_sql8 := 'create table '||v_table8||' as select * from SJL_SD_PC@neworclau ' ; v_sql9 := 'create table '||v_table9||' as select * from SJTEREST@neworclau ' ; v_sql10 := 'create table '||v_table10||' as select * from SFCH_INTEREST@neworclau ' ; v_sql11 := 'create table '||v_table11||' as select * from SJM__FEE@neworclau ' ; v_sql12 := 'create table '||v_table12||' as select * from SJPITAL_DFCH@neworclau ' ; v_sqlw := 'drop database link neworclau ' ; dbms_output.put_line(v_sqlt); execute immediate v_sqlt; dbms_output.put_line(v_sql1); execute immediate v_sql1; dbms_output.put_line(v_sql2); execute immediate v_sql2; dbms_output.put_line(v_sql3); execute immediate v_sql3; dbms_output.put_line(v_sql4); execute immediate v_sql4; dbms_output.put_line(v_sql5); execute immediate v_sql5; dbms_output.put_line(v_sql6); execute immediate v_sql6; dbms_output.put_line(v_sql7); execute immediate v_sql7; dbms_output.put_line(v_sql8); execute immediate v_sql8; dbms_output.put_line(v_sql9); execute immediate v_sql9; dbms_output.put_line(v_sql10); execute immediate v_sql10; dbms_output.put_line(v_sql11); execute immediate v_sql11; dbms_output.put_line(v_sql12); execute immediate v_sql12; dbms_output.put_line(v_sqlw); execute immediate v_sqlw; end; / exit;
