java mysql 存储过程返回结果集_Java 调用存储过程 返回结果集

这里使用Oracle数据库的thin连接。

下面是存储过程SQL

1 createorreplaceprocedure proc3(stid in student.stuid%type, stname out student.sname%type, stphone out student.phonenumber%type, stuadd out student.saddress%type)

2 as countnumber number;

3 begin

4 selectcount(*) into countnumber from student where stuid=stid;

5 if countnumber=1then

6 select phonenumber into stphone from student where stuid=stid;

7 select saddress into stuadd from student where stuid=stid;

8 select sname into stname from student where stuid=stid;

9 else

10 dbms_output.put_line('返回值过多');

11 endif;

12 end;

调用存储过程时，要用CallabelStatement的prepareCall 方法。结构：{call 存储过程名(?,?,...)}

在设置参数的时候，输入参数用set，输出参数要registerOutParameter。取出输出参数的值可以直接用CallabelStatement的get方法

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Types;

public class Dao {

String driver="oracle.jdbc.driver.OracleDriver";

String url="jdbc:oracle:thin:@127.0.0.1:1521:orcl";

Connection conn=null;

CallableStatement cs=null;//PreparedStatement,Statement

ResultSet rs;

public void getConn(){

try {

Class.forName(driver);

conn = DriverManager.getConnection(url, "scott", "tiger");

} catch (ClassNotFoundException e) {

e.printStackTrace();

} catch (SQLException e) {

e.printStackTrace();

}

}

public void callProc(){

try {

cs = conn.prepareCall("{call proc3(?,?,?,?)}");

cs.setInt(1, 1);

cs.registerOutParameter(2, Types.VARCHAR);

cs.registerOutParameter(3, Types.VARCHAR);

cs.registerOutParameter(4, Types.VARCHAR);

cs.execute();

String name = cs.getString(2);

String phone = cs.getString(3);

String address = cs.getString(4);

System.out.println("Name:"+name+"\t Phone:"+phone+"\t Address:"+address);

} catch (SQLException e) {

e.printStackTrace();

}finally{

try {

if (cs!=null) cs.close();

if(conn!=null) conn.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

}

public static void main(String[] args) {

Dao dao = new Dao();

dao.getConn();

dao.callProc();

}

}

以上方法只支持返回个别数据的，不能像SQL返回结果集类型那样。其实，Oracle并不能直接用存储过程来返回结果集，需要借用包才能实现。看代码：

1 CREATEORREPLACE PACKAGE mypack IS

2 TYPE mycursor IS REF CURSOR;

3 PROCEDURE myproc(outcursor IN OUT mycursor);

4 END mypack;

这里建了一个包，其中有两个元素：mycursor游标和myproc存储过程。执行该语句之后要再定义这个包中的内容，代码如下：

1 CREATEORREPLACE PACKAGE BODY mypack IS

2 PROCEDURE myproc(

3 outcursor IN OUT mycursor

4 )

5 IS

6 BEGIN

7 OPEN outcursor FOR

8 SELECT*FROM Student WHERE ROWNUM<10;

9 RETURN;

10 END myproc;

11 END;

这里详细定义了mycursor和myproc的body。注意：CREATE PACKAGE和CREATE PACKAGE BODY不能一起执行，必须先后执行，否则会报错(用goto;连接是可以的)。OKay，包和存储过程定义好了，该写Java代码了：

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;

publicclass Dao {

String driver="oracle.jdbc.driver.OracleDriver";

String url="jdbc:oracle:thin:@127.0.0.1:1521:orcl";

Connection conn=null;

CallableStatement cs=null;//PreparedStatement,Statement

ResultSet rs;

publicvoid getConn(){

try {

Class.forName(driver);

conn = DriverManager.getConnection(url, "scott", "tiger");

} catch (ClassNotFoundException e) {

e.printStackTrace();

} catch (SQLException e) {

e.printStackTrace();

}

}

publicvoid callProc(){

try {

cs = conn.prepareCall("{call proc3(?,?,?,?)}");

cs.setInt(1, 1);

cs.registerOutParameter(2, Types.VARCHAR);

cs.registerOutParameter(3, Types.VARCHAR);

cs.registerOutParameter(4, Types.VARCHAR);

cs.execute();

String name = cs.getString(2);

String phone = cs.getString(3);

String address = cs.getString(4);

System.out.println("Name:"+name+"\t Phone:"+phone+"\t Address:"+address);

} catch (SQLException e) {

e.printStackTrace();

}

}

publicvoid callProcForResult(){

try {

cs = conn.prepareCall("{call mypack.myproc(?)}");

cs.registerOutParameter(1, OracleTypes.CURSOR);

cs.execute();

ResultSet rs = (ResultSet)cs.getObject(1);

while(rs!=null&& rs.next()){

System.out.println(new StringBuilder("ID:").append(rs.getInt(1)).append("\t Name:").append(rs.getString(2))

.append("\t Phone:").append(rs.getString(6)).append("\t Address:").append(rs.getString(7)).toString());

}

} catch (SQLException e) {

e.printStackTrace();

}

}

publicvoid closeConn(){

try {

if (cs!=null) cs.close();

if(conn!=null) conn.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

publicstaticvoid main(String[] args) {

Dao dao =new Dao();

dao.getConn(); //得到连接

dao.callProc(); //调用返回单属性的存储过程

dao.callProcForResult(); //调用返回结果集的存储过程

dao.closeConn(); //关闭连接

}

}