ORACLE数据库SQL语句的执行过程

y3trew

首先是最简单链接数据库的过程，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_TEXT

2 FROM V$SQL

3 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 =20

4215405494 27EEA3BC 1 SELECT * FROM SCOTT.DEPT WHERE DEPTNO =10





下面我们先清空共享池缓存的执行计划，然后使用绑定变量，查看执行计划的变换


SQL> ALTER SYSTEM FLUSH SHARED_POOL;

System altered

SQL> VARIABLE deptno NUMBER;

SQL> EXECUTE :deptno := 10;

PL/SQL procedure successfully completed

deptno

---------

10

SQL> SELECT * FROM SCOTT.DEPT WHERE DEPTNO=:deptno;

DEPTNO DNAME LOC

------ -------------- -------------

10 ACCOUNTING NEW YORK

SQL> EXECUTE :deptno :=20;

PL/SQL procedure successfully completed

deptno

---------

20

SQL> SELECT * FROM SCOTT.DEPT WHERE DEPTNO=:deptno;

DEPTNO DNAME LOC

------ -------------- -------------

20 RESEARCH DALLAS

SQL> SELECT HASH_VALUE , ADDRESS, EXECUTIONS ,SQL_TEXT

2 FROM V$SQL

3 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查询语句才有的步骤。获取查询的记录行，必要的时候对查询结果排序。

总结来看，就是有四步：

首先是解析：目的是确定执行计划，找到最优的执行方案。分为软解析（已经有解析完成的缓存，解析过的SQL会有一个hash值与之相对应）和硬解析（语法分析，语义分析，权限检查，视图转换和表达式转换，决定最优的执行计划，缓存SQL文本，解析树，执行计划）

绑定：SQL语句中使用了绑定变量，扫描绑定变量的声明，给绑定变量赋值

执行：不同的操作执行过程不一样，查询操作（缓冲区缓存（可以设置），逻辑读取，物理读取），修改操作（首先确定是否在缓冲区缓存中，如果不在将数据弄到数据库缓存中，对修改的数据取得数据行锁定，取得独占锁，日志缓冲区交互，数据修改，变为脏缓冲）

提取：只有select语句才有，获得查询的结果，必要的时候对结果进行排序。