Oracle 存储过程返回数组记录集

 

刚学，耗了两天调试，寒一个。

1. 存储过程：

--创建返回对象类型
CREATE TYPE T_DEPT_NUM_O1 AS OBJECT(
dept_id NUMBER ,
num NUMBER
);
/
--创建临时表
CREATE type T_DEPT_NUM_T1 as table of T_DEPT_NUM_O1;
/
--创建包
CREATE OR REPLACE PACKAGE KPI_DEPT_EMP_COUNT1 AS



PROCEDURE EmployeeCountProcedure1
(
emp_count out T_DEPT_NUM_T1 --定义返回为临时表类型
);
end KPI_DEPT_EMP_COUNT1;
/
SHOW ERRORS
/
--创建包体
CREATE OR REPLACE PACKAGE BODY KPI_DEPT_EMP_COUNT1
AS
PROCEDURE EmployeeCountProcedure1 (
emp_count out T_DEPT_NUM_T1
)
IS
deptid number;
i number default 1;
empcount number;

--取得所有有效的部门
cursor c_emp is select sys_org_dept.f_id from sys_org_dept left join sys_org_element on sys_org_element.f_id = sys_org_dept.f_id
where f_isavailable=1 and f_isbusiness = 1;

BEGIN
emp_count := T_DEPT_NUM_T1(); --实例对象
open c_emp;
loop
fetch c_emp into deptid;
if c_emp%notfound then

DBMS_OUTPUT.PUT('======not found=======');
end if;
exit when c_emp%notfound;
--递归查询部门下的所有人数
select to_number(count(*)) into empcount from sys_org_element
where sys_org_element.f_orgtype=8 and f_isavailable=1 and f_isbusiness = 1
start with f_id = deptid connect by prior f_id = f_parentdeptid
;
emp_count.extend; --扩展临时表 表里面放自定义对象
emp_count(i) := T_DEPT_NUM_O1(deptid,empcount);

DBMS_OUTPUT.PUT('the gains that DEPT_ID:');
DBMS_OUTPUT.PUT(deptid);
DBMS_OUTPUT.PUT(' NUM:');
DBMS_OUTPUT.PUT(empcount);
dbms_output.new_line();

i := i + 1;
end loop;
close c_emp;　

END EmployeeCountProcedure1;
END KPI_DEPT_EMP_COUNT1;
/
SHOW ERRORS
/
 

2.sqlplus验证

SET SERVEROUTPUT ON;
--查看结果
declare

emp_count T_DEPT_NUM_T1 := T_DEPT_NUM_T1();
begin
KPI_DEPT_EMP_COUNT1.EmployeeCountProcedure1(emp_count);
end;
/
SHOW ERRORS
/

3.spring中调用存储过程

private class DepartmentEmployeeCountProcedure extends StoredProcedure {

public DepartmentEmployeeCountProcedure(DataSource ds) {
setDataSource(ds);
setJdbcTemplate(new JdbcTemplate(ds));
getJdbcTemplate().setNativeJdbcExtractor(nativeJdbcExtractor);
setSql(PROC_NAME);

declareParameter(new SqlOutParameter("emp_count",Types.ARRAY, "T_DEPT_NUM_T1"));
compile();
}

public Map execute() {
Map out = new HashMap();
try {
Map result = new HashMap();
result = execute(new HashMap());
ARRAY arr = (ARRAY) result.get("emp_count");
ResultSet rs = arr.getResultSet();
while (rs.next()){
STRUCT struct = (STRUCT)rs.getObject(2);
Object[] obs = struct.getAttributes();
out.put(obs[0].toString(),new Double(((BigDecimal)obs[1]).doubleValue()));
System.out.println(obs[0] + " " +obs[0].getClass());
System.out.println(obs[1] + " " +obs[1].getClass());
}
} catch (CannotGetJdbcConnectionException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}

return out;
}

}

4.调试过程出现的错误：

• 内部错误: Image is a collection image, expecting ADT
  有人说是Oracle9的bug，删除所有类型并将使用的类型重新改名就好了，可是我照做了也还是不行，后来添加了方法： setJdbcTemplate(new JdbcTemplate(ds));
  getJdbcTemplate().setNativeJdbcExtractor(nativeJdbcExtractor);
  并修改了返回类型为：Types.ARRAY（原来使用 OracleTypes.STRUCT）就行了。不过这时候我的使用类型名称也已经改掉了。
• java.lang.AbstractMethodError: oracle.jdbc.driver.OracleConnection.getTypeMap()Ljava/util/Map;
  不知道原因的错误，出现在定义数据库类型时。定义数据库返回类型的变量如下：
  SqlReturnType param = new SqlReturnType() {
  
  public Object getTypeValue(CallableStatement cs, int paramIndex,
  int sqlType, String typeName) throws SQLException {
  Connection con = cs.getConnection();
  oracle.jdbc.driver.OracleConnection connection = (oracle.jdbc.driver.OracleConnection)con;
  
  Dictionary d = connection.getTypeMap();
  // if((con instanceof oracle.jdbc.driver.OracleConnection)){
  // con = nativeJdbcExtractor.getNativeConnection(con);
  // System.out.println("=================================");
  // }
  // Map typeMap = con.getTypeMap();
  d.put(typeName, PersonCount.class);
  Object o = cs.getObject(paramIndex);
  return o;
  }
  
  };
  在不管是使用Connection还是使用OracleConnection，在调用getTypeMap()时总会出现此错误。使用nativeJdbcExtractor获得的连接不会出现此错误，不过好像类型转换无效，取到的结果还是原来的ARRAY里面包含STRUCT对象。

5.调试时一些参考资料：

• 创建表的语法：http://www.adp-gmbh.ch/ora/sql/create_table.html
• 获得原始连接：http://blog.matrix.org.cn/page/houor/20060331
• spring论坛的经验：http://forum.springframework.org/archive/index.php/t-9580.html 和
  http://forum.springframework.org/archive/index.php/t-10042.html 和
  http://forum.springframework.org/showthread.php?t=10042
• 插入结构对象的例子：http://javaalmanac.com/egs/java.sql/InsertObjectOraclePreparedStatement.html
• oracle官方的一些实例代码：http://www.oracle.com/technology/global/cn/sample_code/tech/pl_sql/index.html