当我们需要从oracle 数据库中获取一个数据的集合(例如:所有用户信息),我们通过存储过程可以这样完成。这里做一个简单的样例,供大家一起学习参考。
实验环境: win xp sp3 , oracle 10.1.0.2.0 , pl/sql developer 9.0.2 , jdk1.6.29
1. 创建数据库表
create table PERSON
(
id VARCHAR2(50) not null primary key ,
uname VARCHAR2(50),
upass VARCHAR2(50),
age NUMBER(3)
);
向表中添加实验数据:
insert into PERSON (ID, UNAME, UPASS, AGE)
values ('1', 'douhaoa888888', 'sd', 20);
insert into PERSON (ID, UNAME, UPASS, AGE)
values ('2', 'douhaoa888888', 'dsd', 50);
2. 定义存储过程
注意: 在 pl/sql developer 环境中执行下列语句时,package 与 package body 要分别放在对应的窗口内执行,否则报错。
窗口位置为:文件 -->新建 -->程序窗口-->package :
create or replace package testlist2
is
type namelist is REF CURSOR;
procedure getPersonNames2 (personNames out namelist);
end;
窗口位置为:文件 -->新建 -->程序窗口-->package body :
create or replace package body testlist2 is
procedure getPersonNames2 (personNames out namelist)
as
begin
open personNames for select uname,upass,age from person;
end getPersonNames2;
end;
3. 创建 jdbc 程序
创建一个 java 项目,在 classpath中加入 ojdbc14.jar , 这个包可以在 oracle 安装目录的 oracle\product\10.1.0\Db_1\jdbc\lib 这个下面找到。
package com.solitary.db.oracle.oracleproc;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;
/**
* 功能描述 :测试 调用 oracle 存储过程
*
*/
public class OracleProc2 {
private static final String DBURL = "jdbc:oracle:thin:@localhost:1521:orcl";
private static final String DBUSER = "xxxx";
private static final String DBPASSWORD = "xxxx";
private static final String DBDRIVER = "oracle.jdbc.driver.OracleDriver";
public static Connection getConnection (){
Connection conn = null ;
try {
Class.forName(DBDRIVER).newInstance();
conn = DriverManager.getConnection(DBURL, DBUSER, DBPASSWORD);
} catch (Exception e) {
}
return conn ;
}
/**
*
* 功能描述: 调用所有用户信息的存储过程
*/
public static void getPersonList2(){
CallableStatement cstmt = null ;
ResultSet rs = null ;
try {
cstmt = getConnection().prepareCall("{ call testlist2.getPersonNames2(?)}");
cstmt.registerOutParameter(1, OracleTypes.CURSOR);
cstmt.execute();
rs = ((OracleCallableStatement)cstmt).getCursor(1);
while ( rs.next()){
System.out.println(rs.getString("uname") + " " + rs.getString("upass") + " " + rs.getInt("age"));
}
} catch (SQLException e) {
}
}
public static void main(String[] args) {
getPersonList2();
}
}
把上面代码中的数据库用户名和密码改成你本机的信息,运行一下,就可以看到已经可以得到返回结果了。
4. 关于 ref cursor
ref cursor 是抽象类型,不能定义变量,必须派生出新的类型才能使用。
弱类型派生: type mytype is ref cursor ;
不指定return type,能和任何类型的CURSOR变量匹配。
强类型派生: type mytype is ref cursor return emp%rowtype;
指定retrun type,CURSOR变量的类型必须和return type一致。