Oracle 存储过程JAVA调用存储过程 游标使用

打开 PLSQL 创建一张：如果你没有PLSQL  找我

-- Create table
create table T_QUN_STUDY
(
  T_ID       NUMBER not null,
  T_NAME     VARCHAR2(50),
  T_PASSWORD VARCHAR2(50)
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints
alter table T_QUN_STUDY
  add primary key (T_ID)
  using index
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

 

 

写的存储过程包和主体:

CREATE OR REPLACE PACKAGE BODY QUN_STUDY IS
  ------------------------------
  --插入新数据--
  --author:xiaohe--
  --date:2009-11-21--
  PROCEDURE INSERT_T_QUN_STUDY(P_ID       IN NUMBER,
                               P_NAME     IN VARCHAR2,
                               P_PASSWORD IN VARCHAR2) IS
  BEGIN
    INSERT INTO T_QUN_STUDY
      (T_ID, T_NAME, T_PASSWORD)
    VALUES
      (P_ID, P_NAME, P_PASSWORD);
  END;
  ----------------------------
  --删除信息--
  --author:xiaohe--
  --date:2009-11-21 --
  PROCEDURE DEL_T_QUN_STUDY(P_ID IN NUMBER, X_OUT_RECORD OUT NUMBER) IS
  BEGIN
    DELETE T_QUN_STUDY TQS WHERE TQS.T_ID = P_ID;
    X_OUT_RECORD := 0;
  EXCEPTION
    WHEN OTHERS THEN
      X_OUT_RECORD := -1;
  END;
  -------------------------------
  --查询表所有信息--
  ----
  ----
  PROCEDURE QUERY_ALL_INFO(X_OUT_RECORD OUT NUMBER,
                           X_OUT_CURSOR OUT SYS_REFCURSOR) IS
  BEGIN
    OPEN X_OUT_CURSOR FOR
      SELECT * FROM T_QUN_STUDY;
    X_OUT_RECORD := 0;
  EXCEPTION
    WHEN OTHERS THEN
      X_OUT_RECORD := -1;
  END;

END QUN_STUDY;

 

 

JAVA 代码调用:

 

package com.procedure.core;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

import oracle.jdbc.OracleTypes;

public class ConnectionDataBase {
 private String name = "scott";
 private String password = "tiger";
 private String url = "jdbc:oracle:thin:@localhost:1521:ORCL";
 private String driverClassName = "oracle.jdbc.driver.OracleDriver";

 /**
  * 返回数据库 连接
  *
  * @return
  */
 public Connection getConn() {
  Connection conn = null;
  try {
   Class.forName(driverClassName);
   conn = DriverManager.getConnection(url, name, password);

  } catch (Exception e) {
   e.printStackTrace();
  }
  return conn;
 }

 public static void main(String[] agrs) throws SQLException {
  ConnectionDataBase cdb = new ConnectionDataBase();
  /**
   * 添加 无输出参数
   */
  // Connection conn = cdb.getConn();
  // CallableStatement call = conn
  // .prepareCall("{call QUN_STUDY.INSERT_T_QUN_STUDY(?,?,?)}");
  // call.setInt(1, 4);
  // call.setString(2, "蒙-混混");
  // call.setString(3, "76544");
  // boolean flag = call.execute();
  // System.out.println(flag);
  /**
   * 删除 得到输出参数
   */
  // Connection conn = cdb.getConn();
  // CallableStatement call = conn
  // .prepareCall("{call QUN_STUDY.DEL_T_QUN_STUDY(?,?)}");
  // call.setInt(1, 4);
  // call.registerOutParameter(2, Types.INTEGER);
  // call.execute();
  // Integer result = call.getInt(2);
  // System.out.println("执行结果0为正常  -1为不正常" + result);
  /**
   * 使用游标查询 所有的信息
   */
  Connection conn = cdb.getConn();
  /**
   * 得到 存储过程对象
   */
  CallableStatement call = conn
    .prepareCall("{call QUN_STUDY.QUERY_ALL_INFO(?,?)}");
  /**
   * 注册 整型
   */
  call.registerOutParameter(1, Types.INTEGER);
  /**
   * 注册游标类型
   */
  call.registerOutParameter(2, OracleTypes.CURSOR);
  call.execute();
  /**
   * 获取第一个注册的参数
   */
  Integer result = call.getInt(1);
  /**
   * 将游标 类型 转换为 结果集
   */
  ResultSet rs = (ResultSet) call.getObject(2);
  while (rs.next()) {
   System.out.println("id:" + rs.getInt(1));
   System.out.println("name:" + rs.getString(2));
   System.out.println("password:" + rs.getString(3));
  }

 }
}

OK  基本就已经搞定了

如果大家有不明白 或者想共同学习的可以加群:2987841

也可以联系我本人QQ：86780004