oracle 关于package的简单入门

先建一张表

-- 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[i] = v.get(i);
}
}

} catch (Exception e) {
e.printStackTrace();
throw new BUSSException(e.getMessage());
} finally {
AccessCommonUtils.closeAll(conn, rs, stmt);
}

return dbData;
}