java jdbc调用存储过程

数据库：oracle，eclipse4.2

下面来创建一个非常简单的表，如下：

create table Student
(
       stu_Id varchar(20),
       stu_Name varchar(20)
);

 

一.无返回值的存储过程，注，在运行这些存储过程的时候，最好把注释去掉
CREATE OR REPLACE PROCEDURE stu_add
(
       pStuId IN VARCHAR2,--这里不要写varchar(20),不然会编译不过
       pStuName IN VARCHAR2
)
AS
       --可以声明局部变量
BEGIN
   INSERT INTO HYQ.B_ID (stu_ID,stu_NAME) VALUES (pStuId, pStuName);
END TESTA;

java代码如下：

public static void main(String[] args) {
  Connection conn = null;
  try {
   Class.forName("oracle.jdbc.driver.OracleDriver");
   conn = DriverManager.getConnection("jdbc:oracle:thin:@130.233.1.160:1521:ORCL", "scott", "tiger");
   CallableStatement proc = null;
   proc = conn.prepareCall("{call stu_add(?,?)}");
   proc.setString(1, "666");
   proc.setString(2, "shenkang");
   proc.execute();
  } catch (Exception e) {
   e.printStackTrace();
  }finally{
   if(conn != null){
    try {
     conn.close();
    } catch (SQLException e) {
     e.printStackTrace();
    }
   }
  }
 }

二.有返回值的存储过程

CREATE OR REPLACE PROCEDURE stu_selectGetStuName
(
        pStuId IN VARCHAR2,
        pStuName OUT VARCHAR2
)
IS
BEGIN
   SELECT stu_Name INTO pStuName FROM student WHERE stu_id=pStuId;
END;

java代码

public static void main(String[] args) {
  Connection conn = null;
  try {
   Class.forName("oracle.jdbc.driver.OracleDriver");
   conn = DriverManager.getConnection("jdbc:oracle:thin:@130.233.1.160:1521:ORCL", "scott", "tiger");
   CallableStatement proc = null;
   proc = conn.prepareCall("{call stu_selectGetStuName(?,?)}");
   proc.setString(1, "666");
   proc.registerOutParameter(2, Types.VARCHAR;
   proc.execute();
   String stuName = proc.getString(2);
   System.out.println(stuName);
  } catch (Exception e) {
   e.printStackTrace();
  }finally{
   if(conn != null){
    try {
     conn.close();
    } catch (SQLException e) {
     e.printStackTrace();
    }
   }
  }
 }

三.返回结果集的存储过程

--建程序包
CREATE OR REPLACE PACKAGE TESTPACKAGE  AS

 TYPE Test_CURSOR IS REF CURSOR;

end TESTPACKAGE;

--建立返回集合的存储过程

CREATE OR REPLACE PROCEDURE stu_selectList(p_CURSOR out TESTPACKAGE.Test_CURSOR) IS
BEGIN
    OPEN p_CURSOR FOR SELECT * FROM Student;
END;

java代码：

public static void main(String[] args) {
  Connection conn = null;
  CallableStatement proc = null;
  ResultSet rs = null;
  try {
   Class.forName("oracle.jdbc.driver.OracleDriver");
   conn = DriverManager.getConnection("jdbc:oracle:thin:@130.233.1.160:1521:ORCL", "scott", "tiger");
   
   proc = conn.prepareCall("{call stu_selectList(?)}");
   proc.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
   proc.execute();
   rs = (ResultSet)proc.getObject(1);
   while(rs.next()){
    System.out.println("学生编号：" + rs.getString(1) + "学生姓名是：" + rs.getString(2));
   }
  } catch (Exception e) {
   e.printStackTrace();
  }finally{
   if(conn != null){
    try {
     conn.close();
    } catch (SQLException e) {
     e.printStackTrace();
    }
   }
  }
 }

这都是本人测试通过的，有什么不懂的，欢迎交流。下次更新spring+mybatis调用存储过程。

然后提个小问题，是针对二和三，在pl/sql中如何调用返回参数的存储过程和返回集合的存储过程。谢谢大家。

爱生活，爱分享，爱康宝。