以下代码仅仅记录整个过程。
1.在数据库中新建两个类型,分别是:
2.存储过程:
三、java调用主要代码片段:
1.在数据库中新建两个类型,分别是:
- create or replace type type_record is object(
- name varchar2(200),
- ymonth varchar2(2000)
- );
- create or replace type type_array is table of type_record;
2.存储过程:
- create or replace procedure proc_records(p_start in varchar, p_end in varchar, p_records out type_array)
- as
- type cur_type is ref cursor;
- startDate date;
- endDate date;
- querysql varchar2(4000);
- subquerysql varchar2(400);
- v_cursor cur_type;
- cursor r_cursor is select tr.ymonth,tr.name from tb_record tr where tr.id=-1;
- rec_purch r_cursor%rowtype;
- v_record type_record;
- v_status number;
- begin
- startDate:=to_date(p_start,'yyyymm');
- endDate:=to_date(p_end,'yyyymm');
- while startDate <= endDate loop
- begin
- subquerysql:='select ' || to_number(to_char(startDate,'yyyymm')) ||' as ymonth, tp.name from tb_service tp where exists (' ||
- 'select tr.name from tb_record tr where tr.ymonth=' || to_number(to_char(startDate,'yyyymm')) ||' and tr.name=tp.name)';
- querysql:='' || querysql || ' union all ' || subquerysql;
- startDate:=add_months(startDate, 1);
- end;
- end loop;
- p_records:=type_array();
- open v_cursor for substr(querysql, 11);
- loop
- fetch v_cursor into rec_purch;
- exit when v_cursor%notfound;
- p_records.extend;
- p_records(p_records.count):=type_record(rec_purch.name,rec_purch.ymonth||',');
- end loop;
- dbms_output.put_line(p_records.count);
- close v_cursor;
- end;
三、java调用主要代码片段:
- String call = "{ call proc_records(?,?,?,?)}";
- con = DBConnectionFactory.getConfigConnection();
- /**注意这里由于是从java.sql.Connection中执行prepareCall方法,可以直接拿到数据库驱动的实际Connection,如果使用其它框架如:hibernate、ibatis、dbcp之类的,需要先获取底层Connection,下面的代码才能正常的类型转换。
- */
- cstmt = (OracleCallableStatement)con.prepareCall(call);
- cstmt.setString(1, "201210");
- cstmt.setString(2, "201304");
- //注册返回参数为oracle的数组类型,注意类型名称要大写
- cstmt.registerOutParameter(3,
- leTypes.ARRAY, "TYPE_ARRAY");
- cstmt.execute();
- ARRAY array = cstmt.getARRAY(3);
- Datum[] datas = array.getOracleArray();
- if (datas.length > 0) {
- for (int i = 0; i < datas.length; i++) {
- if (datas[i] != null && ((STRUCT) datas[i]) != null) {
- //注意此处返回的Datum元素为byte类型,需要重新包装一下,如new String
- um[] dataAttr = ((STRUCT) datas[i]) .getOracleAttributes();
- System.out.println("column" + (i + 1) + ":" + new String(dataAttr[0].getBytes()) + ", "
- ew String(dataAttr[1].getBytes()));
- } else {
- System.out.println("datas[" + i + "] is null.");
- }
- }
- } else {
- System.out.println("this procedure is not result data...");
- }