oracle 数组获得记录,Oracle 存储过程返回数组记录集

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

存储过程：

--创建返回对象类型

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.调试时一些参考资料：