oracle 关于package的简单入门

ysapzxabzn

　　先建一张表
　　-- Create table
create table SIMPLE_TABLE
(
F1 NUMBER
)
　　创建一个包头
　　create or replace package glq_test as
procedure example(p_val in number);
end glq_test;
　　创建一个包体
　　create or replace package body glq_test as
procedure example(p_val in number) is
begin
insert into simple_table values (p_val);
end example;
end glq_test;
　　调用sql: call glq_test.example(5);
　　--复杂一点的包和包体,可以用java调用，可以传入参数和游标.
　　1.
　　create or replace package glq_test as
　　TYPE cursor_type IS REF CURSOR;
　　--定义要传入的游标
　　procedure security_info_data(p_sec_long_id IN SECURITY_INFO.SEC_LONG_ID%TYPE,
rc OUT cursor_type);
end glq_test;
　　2.
　　create or replace package body glq_test as
procedure security_info_data(p_sec_long_id IN SECURITY_INFO.SEC_LONG_ID%TYPE,
rc OUT cursor_type) is
begin
　　--最简单的给游标一些值
open rc for 'select * from trad_stat_1_dl d where d.sec_long_id=' || p_sec_long_id;
end security_info_data;
end glq_test;
　　3.怎么样在java中调用。
　　public String[][] getDBByPackage(String long_id) throws APCException,
Exception {
　　Connection conn = null;
ResultSet rs = null;
CallableStatement stmt = null;
String[][] dbData = null;
try {
conn = ConnectionPoolHandler.getHandler().getConnection();
// 调用ORACLE包
stmt = conn.prepareCall("{call glq_test.security_info_data(?,?)}");
// 注册输出参数
stmt.setString(1, long_id);
stmt.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);
stmt.executeQuery();
rs = (ResultSet) stmt.getObject(2);
　　if (rs != null) {
Vector<String[]> v = new Vector<String[]>();
　　int fieldCount = rs.getMetaData().getColumnCount();
String[] value = new String[fieldCount];
　　for (int j = 1; j <= fieldCount; j++) {
String name = rs.getMetaData().getColumnName(j);
value[j - 1] = name;
}
v.add(value);
　　while (rs.next()) {
value = new String[fieldCount];
for (int n = 0; n < fieldCount; n++) {
value[n] = rs.getString(n + 1);
}
v.add(value);
}
dbData = new String[v.size()][fieldCount];
　　for (int i = 0; i < v.size(); i++) {
dbData = v.get(i);
}
}
　　} catch (Exception e) {
e.printStackTrace();
throw new BUSSException(e.getMessage());
} finally {
AccessCommonUtils.closeAll(conn, rs, stmt);
}
　　return dbData;
}