shell指令碼操作oracle資料庫__Python

         資料幫浦到暫存資料表(預存程序test（num）執行該操作），在將表中資料寫入到txt檔案，最後將txt打包zip

原先一直和代碼打交道，做任何事都考慮的是使用程式碼完成，所以一開始就寫了份代碼：

        1：讀取配置設定檔（裡面寫了要操作的號）excel              

             

emptyimport jxl.Cell;import jxl.Sheet;import jxl.Workbook;import java.io.File;import java.util.ArrayList;import java.util.List;//dmuserpublic class load_excl {    List lis = new ArrayList();    public List findlist(String str) {        try {            Workbook book = Workbook.getWorkbook(new File(str));            // 獲得第一個工作表對象            Sheet sheet = book.getSheet(0);            // 得到第一列第一行的儲存格            System.out.println("資料添加到列表begin");            for (int i = 0; i < 10; i++) {                int stat = 3;                Cell cell1 = sheet.getCell(0, i );               // Cell cell2 = sheet.getCell(3, i + 2);                String result = cell1.getContents();               // String result2 = cell2.getContents();                if (result == null || result == "" || "".equals(result)) {                    break;                }                lis.add(result);            }            System.out.println("資料添加到列表end");            book.close();        } catch (Exception e) {            System.out.println(e);        }        return lis;    }    public static void main(String[] args) {        load_excl a=new load_excl();             List abc=  a.findlist("D://batchno.xls");             for (String s : abc) {            System.out.println(s);        }    }}

         2：執行預存程序

       

import java.sql.CallableStatement;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;/** * @Author: Smile [smile_dip@163.com] * @Description: * @Date; 2017/9/9 14:54 */public class oracle125 {public  void  add(String li){    String driver = "oracle.jdbc.driver.OracleDriver";    String url = "jdbc:oracle:thin:@ip:1521:ods";    String user = "使用者名稱";    String pwd = "密碼";    Connection conn = null;    CallableStatement cs = null;    try {        Class.forName(driver);        conn = DriverManager.getConnection(url, user, pwd);        //P_119():預存程序        cs = conn.prepareCall("{ call P_119(?) }");        cs.setString(1, li);        cs.execute();    } catch (SQLException e) {        e.printStackTrace();    } catch (Exception e) {        e.printStackTrace();    } finally {        try {            if (cs != null) {                cs.close();            }            if (conn != null) {                conn.close();            }        } catch (SQLException e) {        }    }}}

         3：執行shell指令碼

emptyimport ch.ethz.ssh2.ChannelCondition;import ch.ethz.ssh2.Connection;import ch.ethz.ssh2.Session;import ch.ethz.ssh2.StreamGobbler;import org.apache.commons.io.IOUtils;import java.io.IOException;import java.io.InputStream;import java.nio.charset.Charset;/** * @Author: Smile [smile_dip@163.com] * @Description: * @Date; 2017/9/9 11:52 */public class RemoteShellExecutor {    private Connection conn;      /** 遠程機器IP */      private String ip;      /** 使用者名稱 */      private String osUsername;  /** 密碼 */    private String password;    private String charset = Charset.defaultCharset().toString();      private static final int TIME_OUT = 1000 * 5 * 60;    /**      * 建構函式        * @param ip        * @param usr      * @param pasword      */           public RemoteShellExecutor(String ip, String usr, String pasword) {                this.ip = ip;               this.osUsername = usr;                this.password = pasword;             }              /**      * 登入      * @return     * @throws IOException      */             private boolean login() throws IOException {                 conn = new Connection(ip);                  conn.connect();                  return conn.authenticateWithPassword(osUsername, password);             }            /**    * 執行指令碼      *     * @param cmds     * @return      * @throws Exception     */              public int exec(String cmds) throws Exception {                 InputStream stdOut = null;               InputStream stdErr = null;                String outStr = "";                 String outErr = "";                 int ret = -1;                  try {                     if (login()) {                            // Open a new {@link Session} on this connection                            Session session = conn.openSession();                              // Execute a command on the remote machine.                              session.execCommand(cmds);                            stdOut = new StreamGobbler(session.getStdout());                           outStr = processStream(stdOut, charset);                             stdErr = new StreamGobbler(session.getStderr());                             outErr = processStream(stdErr, charset);                             session.waitForCondition(ChannelCondition.EXIT_STATUS, TIME_OUT);                             System.out.println("outStr=" + outStr);                             System.out.println("outErr=" + outErr);                              ret = session.getExitStatus();                        } else {                              throw new Exception("登入遠程機器失敗" + ip); // 自訂異常類 實現略                         }                      } finally {                         if (conn != null) {                                conn.close();                              }                         IOUtils.closeQuietly(stdOut);                         IOUtils.closeQuietly(stdErr);                     }                 return ret;            }              /**       * @param in       * @param charset        * @return      * @throws IOException       * @throws UnsupportedEncodingException       */            private String processStream(InputStream in, String charset) throws Exception {                byte[] buf = new byte[1024];               StringBuilder sb = new StringBuilder();                 while (in.read(buf) != -1) {                         sb.append(new String(buf, charset));                      }                return sb.toString();             }         }

         4：整體調用迴圈

import java.io.BufferedReader;import java.io.InputStreamReader;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.DriverManager;import java.util.ArrayList;import java.util.List;/** * @Author: Smile [smile_dip@163.com] * @Description: * @Date; 2017/9/9 14:15 */public class shelltest {    public static void main(String[] args) throws Exception {        //串連oracle      //  OracleConnect con=new OracleConnect();        // write your code here        //讀取 oracle 中的批次號        load_excl excl =new load_excl();        oracle125 orcl125 =new oracle125();        List lis=new ArrayList();        lis=excl.findlist("D://batchno.xls");        //迴圈批次號        for (String li : lis) {            // 設定執行完的狀態            System.out.println("當前批次號："+li);            //串連oracle,執行預存程序            System.out.println("資料入庫");            orcl125.add(li);            System.out.println("資料end");            //執行shell指令碼            Thread.sleep(10*1000);            RemoteShellExecutor executor = new RemoteShellExecutor("ip", "使用者名稱", " 密碼");            // 執行myTest.sh 參數為java Know dummy            String  abc="/picclife/tools/ciitc/119.sh "+li;            System.out.println(executor.exec(abc));            System.out.println(li+"批次完成");       /*     try {                String shpath="/home/felven/word2vec/demo-classes.sh";                Process ps = Runtime.getRuntime().exec(shpath);                int exitValue= ps.waitFor();                if(0!=exitValue){                    isOK="no";                    System.err.println("call shell failed.errorcodeis:"+exitValue);                    //停止繼續迴圈執行                    break;                }                BufferedReader br = new BufferedReader(new InputStreamReader(ps.getInputStream()));                StringBuffer sb = new StringBuffer();                String line;                while ((line = br.readLine()) != null) {                    sb.append(line).append("\n");                }                String result = sb.toString();                System.out.println(result);            }            catch (Exception e) {                e.printStackTrace();            }            */        }    }}

         

其實所有的對資料的操作不考慮空間，時間等因素都可以通過預存程序+shell指令碼完成

    下面介紹使用shell+oracle預存程序結合完成需求

        oracle：遊標（類似list），迴圈（while，for），觸發器（攔截器），預存程序（類 中的main），函數（函數），判斷（if）===》Java語言

                    綜上所有對資料的操作都在預存程序中完成，打包寫入txt等檔案用shell完成

我們可能用到的情境：

1：迴圈執行整個指令碼

2：shell指令碼調用預存程序

3：shell指令碼調用打包程式

4：shell指令碼驗證資料檔案個數是否正確

5：驗證表是否存在

6：shell指令碼步驟寫人log檔案

7：如何判斷預存程序是否執行正確

8：驗證檔案的大小是否超限

9：對shell指令碼傳參數

##################shell 迴圈 文本 資料 如果資料等於abc結束######################


while read var1
 do


if [ $var1 -eq 'abc' ]
then
   break
fi


done < batchno.txt


########################shell執行預存程序############################


sqlplus -s 使用者/密碼@//ip:連接埠/service_name <<EOF 
set echo off;
 
set serveroutput on;
 
set timing on;
 
 //test()是預存程序名
exec test(1);  
 
commit;
 
exit;
 
EOF




########################shell讀取oracle中的資料存到BATC,做變數 #################

BATC=`sqlplus -s circ_user/user_circ <<EOF
set heading off;
SELECT T.BATC FROM   INTFUSER.abc T WHERE  ROW
NUM = 1;
exit;
EOF`

############擷取多變數#####

ftp_v=` sqlplus -s prip_user/prip_user<<EOF
set heading off
SELECT T.SFTP_IP,
       T.SFTP_USER,
       T.SFTP_PASSWORD
FROM   PRIP_INTF_INFO T;
exit
EOF
`
ftp_ip=`echo $ftp_v | awk -F' ' '{print $1}'`
ftp_user=`echo $ftp_v | awk -F' ' '{print $2}'`
ftp_passwd=`echo $ftp_v | awk -F' ' '{print $3}'`

####如果使用的時候直接使用字串

也可以將sql的語句變數拼接起來，例如使用ftp上傳的時候

v_ftp=` sqlplus -s prip_user/prip_user<<EOF
set heading off
SELECT
       T.SFTP_USER||','||
       T.SFTP_PASSWORD ||' sftp://'||
       T.SFTP_IP
FROM   PRIP_INTF_INFO T;
exit
EOF

`結果： v_ftp：rbsx000100,eqsU3w sftp://10.110.41.150

lftp -u $v_ftp<<EOF
lcd $LOCAL_PATH    --上傳檔案的儲存位置
put $FTILE_NAME    --檔案的名
bye    --結束ftp的命令
EOF

########################調用打包程式#######################################
####調用另一個指令碼#####
./test2.sh

####打包#######

#!/bin/sh
#進入路徑
cd /picclife/tools/cross
#刪除要存放檔案夾下的全部檔案
rm -rf /picclife/tools/cross/circ/*

##########資料從oracle暫存資料表到檔案######################


/oraexpdata/sqluldr2_linux64_10204.bin 使用者名稱/密碼 query="SELECT  * FROM tablename" charset=UTF8  file="/picclife/tools/cross/F_"$BATC"_00%b.txt"   rows=
10000000 batch=yes  field=0x7c


############資料檔案打包#######################


##檔案名稱##
file="H_"$BATCHNO"_001.zip"
##要打包到#####
cd /picclife/tools/cross/bak
##########打包######################
7za a -tzip  -r  $pici /picclife/tools/cross/circ/000100




####################shell指令碼壓縮檔txt檔案個數（檔案的個數 可能會有 a_123_1.txt a_123_2.txt 檔案視為1個），寫入日誌############


## awk -F_ 對結果的每一行資料按_分割 {print$3}：擷取第三個欄位 uniq：去重  wc：行數 字數 位元組數 （wc寫在前面+檔案位置/名稱，wc寫在檔案後面要先開啟或讀取檔案例如more test.txt wc） awk 按空格分awk -F: '/root/' /etc/passwd  搜尋/etc/passwd有root關鍵字的所有行  最後結果輸出到log_10G.out檔案




echo `unzip -v /picclife/tools/ciitc/bak/H_$BATCHNO_001.zip| grep ".txt"|awk -F_ '{print$3}'|uniq|wc|awk '{print $1}'` >>/picclife/tools/ciitc/log_10G.out


#######################驗證表是否存在###################################


tab=`sqlplus -s querydep/querydeptwo <<EOF
set heading off;
set feedback off;
set pagesize 0;
set verify off;
set echo off;
select count(*) from user_tables where table_name = 'BS_BATCHNO';
exit;
EOF`
##不存在則建立表################
if [ $tab -eq 0 ]
then  
echo "NOT EXISTS"
sqlplus -s querydep/querydeptwo <<EOF
set heading off;
set feedback off;
set pagesize 0;
set verify off;
set echo off;
create table BS_BatchNo(
       BatchNo varchar2(20) not null,
       TransactionNo varchar2(24) not null
);
exit;
EOF;
fi;
#################建立表也可以建立相同的表結構,不包含資料################


create table abc AS select * from b1 where 1=0


################驗證檔案的大小是否超限  超過10G  或者小於0G（-size -0G），   ############################


find /picclife/tools/cross/bak/ -size +10G -exec ls -ld {} \; >>/picclife/tools/ciitc/log_10G.out




###########如何判斷預存程序是否執行正確#########################################


其實預存程序是可以往表寫資料的，如果預存程序報錯，就插入一條錯誤記錄檔進入表

在shell中查詢錯誤資料量大於0，就終止，break 跳出迴圈，就能結束繼續操作

#也可以 在指令碼出錯的時候終止指令碼的執行  exit ，在迴圈和if都可用exit停止

[oracle@ODSDEVDB09 ~]$ more ./test1.sh

#!/bin/sh

 while read va

do

 if [ $va == 100 ]

then 

 exit

fi

echo $va

done < abc

[oracle@ODSDEVDB09 ~]$ ./test1.sh 

abc

tt

[oracle@ODSDEVDB09 ~]$ cat -n abc

     1 abc

     2 tt

     3 100

     4 abcd

     5 zxy

shell裡面流程同代碼中的流程，先迴圈讀取文本需要處理的號，在找到對應的預存程序，將資料裝入txt，最後打包，並校正正確性

######################對shell指令碼傳參數#############################

[oracle@ODSDEVDB09 ~]$ cat test2.sh

#!/bin/bash

echo "first :" $1

echo "second: " $2

[oracle@ODSDEVDB09 ~]$ ./test2.sh

first :

second: 

[oracle@ODSDEVDB09 ~]$ ./test2.sh abc 123

first : abc

second:  123

###########################shell的文本替換###################################

sed 是一種線上編輯器，它一次處理一行內容。處理時，把當前處理的行儲存在臨時緩衝區中，接著用sed命令處理緩衝區中的內容，處理完成後，把緩衝區的內容送往螢幕。接著處理下一行，這樣不斷重複，直到檔案末尾。檔案內容並沒有 改變，除非你使用重新導向儲存輸出

  sed ‘s/  / g’ file  （實際上文本木有改）    全部替換

[oracle@ODSDEVDB09 ~]$ cat abc   
bc
dd hello heolhello
hellofdhellogdayhello
zxyizxyasdwuzxaszxysqswqzxy
am ddddddddddddddddabdfea
a bb aa  cc
hello word

[oracle@ODSDEVDB09 ~]$ sed  's/hello/world/g' abc
bc
dd world heolworld
worldfdworldgdayworld
zxyizxyasdwuzxaszxysqswqzxy
am ddddddddddddddddabdfea
a bb aa  cc
world word

[oracle@ODSDEVDB09 ~]$ cat abc
bc
dd hello heol hello
hellofdhellogdayhello
zxyizxyasdwuzxaszxysqswqzxy
am ddddddddddddddddabdfea
a bb aa  cc
hello word

#################################################

vim :s    ：文本替換命令，需要使用vim 線上開啟檔案，在做處理

vim abc
bc
dd jjllo jjol jjllo
bc
dd jjllo jjol jjllo
hellofdhellogdayhello
ttittasdwuzxasttsqswqtt
am ddddddddddddddddabdfea
a ss aa  cc
hello word

：g/tt/s//hello/g                  //全部替換

bc

dd jjllo jjol jjllo
bc
dd jjllo jjol jjllo
hellofdhellogdayhello
helloihelloasdwuzxashellosqswqhello
am ddddddddddddddddabdfea
a ss aa  cc
hello word

: 1 ,$ s/hello/2017/g              //全部替換

bc
dd jjllo jjol jjllo
bc
dd jjllo jjol jjllo
2017fd2017gday2017
2017i2017asdwuzxas2017sqswq2017
am ddddddddddddddddabdfea
a ss aa  cc
2017 word

find /a/b/c -name '*.txt'| xargs cat| wc -l           //擷取目錄檔案夾c下txt檔案的總行數

xargs：擷取管道的參數，以流的形式 傳遞給下一個參數