刚学,耗了两天调试,寒一个。
- 存储过程:
--创建返回对象类型
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