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;
 }