import java.sql.Connection;

 import java.sql.DriverManager;

 import java.sql.ResultSet;

 import java.sql.SQLException;

 import java.sql.Statement;

 public class JdbcUpdate {

     /**

      * @param args

      */

     public static void main(String[] args) {

         try {

             //加载驱动

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

             //获取连接

             Connection con = DriverManager.getConnection("jdbc:oracle:thin:@192.168.16.111:1521:orcl","scott","tiger");

             //获得句柄

             Statement sta = con.createStatement();

             //SQL查询命令

             String sqlFind = "select * from empbak";

             //从emp中复制一个新表为empbak

             String sqlCreTable = "create table empbak as select * from emp";

             //NVL (expr1, expr2)：expr1为NULL，返回expr2；不为NULL，返回expr1。注意两者的类型要一

             String sqlUpdate = "update  empbak set sal = sal+200,comm = nvl(comm+100,100) where sal > (select avg(sal) from empbak )";

             //复制表

             int temp = sta.executeUpdate(sqlCreTable);

             //初始查询

             //发送SQL语句，如果发送的是select,使用executeQuery；如果发送的是INSERT,DELETE,UPDATE，使用executeUpdate

             ResultSet rs1 = sta.executeQuery(sqlFind);

             while(rs1.next()){

                 System.out.print(rs1.getInt("empno")+"\t");

                 System.out.print(rs1.getString("ename")+"\t");

                 System.out.print(rs1.getString("job")+"\t");

                 System.out.print(rs1.getInt("mgr")+"\t");

                 System.out.print(rs1.getString("hiredate")+"\t");

                 System.out.print(rs1.getFloat("sal")+"\t");

                 System.out.print(rs1.getFloat("comm")+"\t");

                 System.out.println();

             }

             System.out.println("********************更新以后*********************");

             int temp1 = sta.executeUpdate(sqlUpdate);

             ResultSet rs = sta.executeQuery(sqlFind);

             while(rs.next()){

                 System.out.print(rs.getInt("empno")+"\t");

                 System.out.print(rs.getString("ename")+"\t");

                 System.out.print(rs.getString("job")+"\t");

                 System.out.print(rs.getInt("mgr")+"\t");

                 System.out.print(rs.getString("hiredate")+"\t");

                 System.out.print(rs.getFloat("sal")+"\t");

                 System.out.print(rs.getFloat("comm")+"\t");

                 System.out.println();

             }

             rs.close();

             sta.close();

             con.close();

         } catch (ClassNotFoundException e) {

             // TODO 自动生成的 catch 块

             e.printStackTrace();

         } catch (SQLException e) {

             // TODO 自动生成的 catch 块

             e.printStackTrace();

         }

     }

 }