storedprocs.java

来自「weblogic应用全实例」· Java 代码 · 共 100 行

//声明本类包含在包examples.jdbc.oracle中
package examples.jdbc.oracle;
//声明本类要引入的其他包和类
import java.sql.*;
import java.util.Properties;

/**
 * 这个实例演示Oracle存储过程的语法和用法。包括怎样创建、调用存储过程、函数以及怎样注册参数
 */
public class storedprocs {
    
    public static void main(String argv[])
    {//声明驱动变量、连接、声明和CallableStatement声明
      java.sql.Connection conn          = null;
      java.sql.Statement stmt1          = null;
      java.sql.Statement stmt2          = null;
      java.sql.CallableStatement cstmt1 = null;
      java.sql.CallableStatement cstmt2 = null;
      try {// 设置用户名、密码和服务器名
        Properties props = new Properties();
        props.put("user","scott");
        props.put("password","tiger");
        props.put("server","DEMO");
        // 实例驱动程序
        Driver myDriver = (Driver)   Class.forName("weblogic.jdbc.oci.Driver").newInstance();
        //建立连接
        conn = myDriver.connect("jdbc:weblogic:oracle", props);
        
        //创建SQL语句对象
        stmt1 = conn.createStatement();
        //执行存储过程
        stmt1.execute("CREATE OR REPLACE PROCEDURE proc_squareInt " +
                      "(field1 IN OUT INTEGER, " +
                      " field2 OUT INTEGER) IS " + 
                      "BEGIN field2 := field1 * field1; " +
                      "field1 := field1 * field1; END proc_squareInt;");
        //关闭
        stmt1.close();
        
        
        // JDBC规范定义的正确语法
        String sql = "{call proc_squareInt(?, ?)}";
        
        cstmt1 = conn.prepareCall(sql);
        
        // 注册输出参数
        cstmt1.registerOutParameter(2, java.sql.Types.INTEGER);
        for (int i = 0; i < 5; i++) {
            cstmt1.setInt(1, i);
            cstmt1.execute();
            System.out.println(i + " " + cstmt1.getInt(1) + " " + cstmt1.getInt(2));
        }
        cstmt1.close();
        
        // 创建一个函数
        stmt2 = conn.createStatement();
        stmt2.execute("CREATE OR REPLACE FUNCTION func_squareInt (field1 IN INTEGER) " +
                      "RETURN INTEGER IS " +
                      "BEGIN return field1 * field1; END func_squareInt;");
        stmt2.close();
        
        
        // 定义SQL语句
        sql = "{ ? = call func_squareInt(?)}";
        //执行
        cstmt2 = conn.prepareCall(sql);
        
        // 注册输出参数
        cstmt2.registerOutParameter(1, java.sql.Types.INTEGER);
        for (int i = 0; i < 5; i++) {
            cstmt2.setInt(2, i);
            cstmt2.execute();
            System.out.println(i + " " + cstmt2.getInt(1) + " " + cstmt2.getInt(2));
        }
        cstmt2.close();
      } catch (Exception e) {
      	//异常处理
          System.out.println("Exception was thrown: " + e.getMessage());
      } finally {
      	//关闭语句和连接
          try {
            if(stmt1 != null)
              stmt1.close();
            if(stmt2 != null)
              stmt2.close();
            if(cstmt1 != null)
              cstmt1.close();
            if(cstmt2 != null)
              cstmt2.close();
            if(conn != null)
              conn.close();
          } catch (SQLException sqle) {
          //异常处理
              System.out.println("SQLException was thrown: " + sqle.getMessage());
          }
      }
    }
}