ORACLE資料庫SQL語句的執行過程

標籤：style   blog   http   color   io   os   使用   java   ar   

         

首先是最簡單連結資料庫的過程，JDBC標準的連結的過程：

1. 載入JDBC驅動程式,一般來說載入驅動程式，採用的都是利用Class。forName（）反射來載入驅動

Oracle: Class.forName("oracle.jdbc.driver.OracleDriver");

SQLServer: Class.forName(“com.microsoft.jdbc.sqlserver.SQLServerDriver");

MySql: Class.forName(“com.mysql.jdbc.Driver");

2. 定義URL，主要是為了指定資料庫主機名稱，連接埠 以及期望連結的資料庫名稱。

Oracle: URL = "jdbc:oracle:thin:@localhost:1521:dataBaseName" ;

SQLServer: URL = "jdbc:microsoft:sqlserver://127.0.0.1:1433;DatabaseName=dataBaseName";

MySql: URL = “jdbc:mysql://localhost:3306/dataBaseName”

3. 建立串連

有了資料庫 URL 使用者名稱 密碼 就可以和資料庫建立串連，串連的類為 Connection

Connection conn = DriverManager.getConnection(URL地址,使用者名稱,密碼);

4 建立Statement對象，Statement對象用來向資料庫發送查詢和命令.它由Connection的createStatement()方法建立

Statement stmt = conn.createStatement();

5 執行查詢或更新，有了statement對象就可以使用它的executeQuery()方法發送SQL查詢

    String sql = “select * from table”;

    ResultSet 結果集對象 statement對象的executeQuery方法返回的是一個結果集對象

    ResultSet rs = stmt . executeQuery(sql);

    Statement對象的常用方法:

    executeQuery( ): 執行SQL查詢 並在ResultSet中返回資料, ResultSet可能為空白,但不會為null.

    executeUpdate( ): 執行update , insert , delete 命令,返回受影響的行數,可以為0. 它還支援DDL命令,如create table , drop table , alter table .

6. 處理結果

處理結果可以使用ResultSet的next 方法在表中移動 每次一行ResultSet 的next 方法返回 一個布爾值 TRUE 表示下面還有資料，在每一行結果中我們可以使用ResultSet對象的 getXXX(“欄位名”)擷取該行中欄位的資料 有getString(“”) ; getInt(“”); 也可以使用 getXXX(索引號):這裡索引號從1開始.

例: while(rs.next()){

String a = rs.getString(“ID”);

}

7 關閉串連

Connection.close(); 關閉串連

關閉串連會關閉對應的Statement和ResultSet 對象

寫到這個地方，的說使用PreparedStatement 執行先行編譯的SQL語句 這樣可以比使用拼字串更安全

String sql = “select * from table where a=? and b = ?” 其中a 為字元型資料 b 為整數

PreparedStatement pstmt = conn . preparedStatemengt(sql);

pstmt.setString(1,”www”); 第一個問號值為 WWW

pstmt.setInt(2,5); 第二個問號值為 5

ResultSet rs = pstmt.executeQuery( );

 

public static Connection getConnection(){    Connection conn = null;try{     Class.forName("oracle.jdbc.driver.OracleDriver");//載入硬體驅動     String dbUrl = "jdbc:oracle:thin:@localhost:1521:DataName";//定義URL

     String username = "test";    String password = "123456";    conn = DriverManager.getConnection(dbUrl, username, password);//建立連結   }catch(ClassNotFoundException e){           e.printStackTrace();   }catch(SQLException e){           e.printStackTrace();   }       return conn;}

在Spring-JDBC的架構中有一個模板類：JdbcTemplate，直接把這些過程封裝起來，例如：

@Override    public Account read(String username) {        String sql = "SELECT * From account WHERE username = ?";        Object object = null;        try {            object = jdbcTemplate.queryForObject(sql,                    new Object[] { username }, accountRowMap);        } catch (EmptyResultDataAccessException e) {            return null;        }        return (Account)object ;    }

accountRowMap 實現RowMapper介面，就是6上面說的處理結果。

 

大的方面可以可以說：載入驅動（動機），定義URL（確定目標），建立連結（確定關係），建立statement對象（得到對象），執行查詢（開始交往），處理結果（交往成績），關閉連結（完成過程）。具體的java代碼如下：

 

這個是比較大的方面，具體來說，我們關注執行過程，這段時間發生的事情比較的複雜，具體的例如：SQL語句在資料庫中是怎麼處理的，執行的順序是什嗎？

在ORACLE資料庫系統架構下，SQL語句由使用者進程產生，然後傳到相對應的服務端進程，之後由伺服器處理序執行該SQL語句，如果是SELECT語句，伺服器處理序還需要將執行結果回傳給使用者進程。

首先是使用者產生的SQL語句，傳到相對應的服務端的進程，然後採用SQL的執行順序。

SQL語句的執行過程一般如下：

解析（PARSE）—— 綁定（BIND）——執行（EXECUTE）——提取（FETCH 只有SELECT才需要這步）

解析

伺服器處理序接收到一個SQL語句時，首先要將其轉換成執行這個SQL語句的最有效步驟，這些步驟被稱為執行計畫。

Step 1：檢查共用池中是否有之前解析相同的SQL語句後所儲存的SQL文本、解析樹和執行計畫。如果能從共用池的緩衝庫中找到之前解析過產生的執行計畫，則SQL語句則不需要再次解析，便可以直接由庫緩衝得到之前所產生的執行計畫，從而直接跳到綁定或執行階段，這種解析稱作軟解析。

但是如果在共用池的庫緩衝中找不到對應的執行計畫，則必須繼續解析SQL、產生執行計畫，這種解析稱作硬解析

在緩衝池解析過的SQL，會有一個對應的雜湊值與之對應，你可以通過V$SQL視圖來查詢，請看下面一個例子：

SQL>SELECT * FROM SCOTT.DEPT WHERE DEPTNO =10;SQL>SELECT * FROM SCOTT.DEPT WHERE DEPTNO =20;SQL> SELECT HASH_VALUE , ADDRESS, EXECUTIONS ,SQL_TEXT2 FROM V$SQL3 WHERE SQL_TEXT LIKE ‘SELECT * FROM SCOTT.DEPT WHERE DEPTNO%‘4 ;HASH_VALUE ADDRESS EXECUTIONS SQL_TEXT---------- -------- ---------- --------------------------------------------------------------------------------442836625 27EE4B7C 1 SELECT * FROM SCOTT.DEPT WHERE DEPTNO =204215405494 27EEA3BC 1 SELECT * FROM SCOTT.DEPT WHERE DEPTNO =10

下面我們先清空共用池緩衝的執行計畫，然後使用綁定變數，查看執行計畫的變換

SQL> ALTER SYSTEM FLUSH SHARED_POOL;System alteredSQL> VARIABLE deptno NUMBER;SQL> EXECUTE :deptno := 10;PL/SQL procedure successfully completeddeptno---------10SQL> SELECT * FROM SCOTT.DEPT WHERE DEPTNO=:deptno;DEPTNO DNAME LOC------ -------------- -------------10 ACCOUNTING NEW YORKSQL> EXECUTE :deptno :=20;PL/SQL procedure successfully completeddeptno---------20SQL> SELECT * FROM SCOTT.DEPT WHERE DEPTNO=:deptno;DEPTNO DNAME LOC------ -------------- -------------20 RESEARCH DALLASSQL> SELECT HASH_VALUE , ADDRESS, EXECUTIONS ,SQL_TEXT2 FROM V$SQL3 WHERE SQL_TEXT LIKE ‘ SELECT * FROM SCOTT.DEPT WHERE DEPTNO%‘;HASH_VALUE ADDRESS EXECUTIONS SQL_TEXT---------- -------- ---------- 3669302979 27D2BA1C 2 SELECT * FROM SCOTT.DEPT WHERE DEPTNO=:deptno

Step 2：文法分析，分析SQL語句的文法是否符合規範，衡量語句中各運算式的意義

Step 3：檢查是否存在語義錯誤和許可權。語義分析，檢查語句中設計的所有資料庫物件是否存在，且使用者有相應的許可權。

Step 4：視圖轉換和運算式轉換 將涉及視圖的查詢語句轉換為相應的對基表查詢語句。將複雜運算式轉化較為簡單的等效串連運算式。

Step 5：決定最佳執行計畫。最佳化器會產生多個執行計畫，在按統計資訊帶入，找出執行成本最小的執行計畫，作為執行此SQL語句的執行計畫

Step 6：將SQL文本、解析樹、執行計畫緩衝到庫緩衝，存放地址以及SQL語句的雜湊值。

綁定

如果SQL語句中使用了綁定變數，掃描綁定變數的聲明，給綁定變數賦值。則此時將變數值帶入執行計畫。

執行

此階段按照執行計畫執行SQL，產生執行結果。不同類型的SQL語句，執行過程也不同。

SELECT查詢

檢查所需的資料區塊是否已經在緩衝區快取中，如果已經在緩衝區快取中，直接讀取器內容即可。這種讀取方式稱為邏輯讀取。如果所需資料不在緩衝區快取中，則伺服器處理序需要先掃描資料區塊，讀取相應資料區塊到緩衝區快取，這種讀取方式稱為物理讀。和邏輯讀相比較，它更加耗費CPU和IO資源。

修改操作（INSERT、UPDATE、DELETE）

Step 1：檢查所需的資料庫是否已經被讀取到緩衝區快取中。如果已經存在緩衝區快取，則執行Step 3

Step 2：若所需的資料庫並不在緩衝區快取中，則伺服器將資料區塊從資料檔案讀取到緩衝區快取中

Step 3：對想要修改的表取得的資料行鎖定（Row Exclusive Lock），之後對所需要修改的資料行取得獨佔鎖

Step 4：將撤銷資料的Redo記錄複製到日誌緩衝區，產生資料行的撤銷資料，將資料行修改的Redo記錄複製到日誌緩衝區，修改資料行。

Step 5: 產生資料修改的撤銷資料

Step 6：複製資料修改的Redo記錄到日誌緩衝區

Step 7：修改資料行的內容，如果之前的緩衝為乾淨緩衝，則此時將變為髒緩衝。

提取

提取只有SELECT查詢語句才有的步驟。擷取查詢的記錄行，必要的時候對查詢結果排序。

在http://www.cnblogs.com/kerrycode/p/3356845.html上面修改

ORACLE資料庫SQL語句的執行過程