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_testas 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 test2 test test3 test test4 test test5 test test6 test test7 test test
本文来自博客，转载请标明出处：http://blog.csdn.net/zhaoweitco/archive/2010/04/13/5479200.aspx
