oracle存储过程返回游标 java_Java jdbc调用oracle存储过程返回游标例子

1.创建测试表

create table users(

userid int primary key,

username varchar2(20),

userpwd  varchar2(20)

);

insert into users values(1,'test','test');

insert into users values(2,'test','test');

insert into users values(3,'test','test');

insert into users values(4,'test','test');

insert into users values(5,'test','test');

insert into users values(6,'test','test');

insert into users values(7,'test','test');

2.创建存储过程

创建包

create or replace package pkg_test

as

type cursor0 is ref cursor;

procedure get(p_id int,p_rc out cursor0);

end pkg_test;

创建包的内容

create or replace package body pkg_test as

procedure get(p_id int, p_rc out cursor0) is

begin

dbms_output.put_line(p_id);

open p_rc for

select userid, username, userpwd from users;

end get;

end pkg_test;

3.java调用

package com.wei;

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

/***

*

* 测试 jdbc 连接 oracle 存储过程 返回游标

*

* @author wei

*

*/

public class Main {

public static void main(String[] args) {

try {

DriverManager.registerDriver(new oracle.jdbc.OracleDriver());

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

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

String username = "test";

String password = "admin";

Connection conn = DriverManager.getConnection(url, username,

password);

String sql = "{call pkg_test.get(?,?)}";

CallableStatement cstmt = conn.prepareCall(sql);

cstmt.setInt(1, 1);

cstmt.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);

cstmt.execute();

ResultSet rs = (ResultSet) cstmt.getObject(2);

while (rs.next()) {

System.out.println(rs.getInt(1) + "/t" + rs.getString(2) + "/t"

+ rs.getString(3));

}

} catch (SQLException e) {

e.printStackTrace();

} catch (ClassNotFoundException e) {

e.printStackTrace();

}

}

}

输出结果：

1 test test

2 test test

3 test test

4 test test

5 test test

6 test test

7 test test