贴一个步骤Oracle可变数组转化成Cursor输出1、建Type create or replace type t_array as table of varchar2(4000);2、package中 声明cursor type T_cur is ref cursor; 存储过程 procedure pro_StorageStatus(as_month varchar2, --查询月份 cur_1 out t_cur, --返回 as_OutMsg out varchar2, --传出参数 an_Res out int) is l_jg t_array := t_array(); ls_temp varchar2(4000); begin ... l_jg.extend; l_jg(l_jg.last) := ls_temp; ... open cur_1 for select * from table(cast (l_jg as t_array)); end;3、Java中调用 CallableStatement cs = conn.prepareCall("call pk_cxtj.pro_StorageStatus(?,?,?,?)"); cs.setString(1,month); cs.registerOutParameter(2,OracleTypes.CURSOR); cs.registerOutParameter(3, Types.VARCHAR); cs.registerOutParameter(4, Types.NUMERIC); try { cs.execute(); if (cs.getInt(4) < 0) { throw new SQLException(cs.getString(3)); } else { rs = (ResultSet)cs.getObject(2); ........ 4、Oracle中输出IndexTable,java中调用 OracleCallableStatement cs = null; cs = (OracleCallableStatement)conn.prepareCall("begin pk_cxtj.pro_StorageStatus(?,?,?,?); end;"); cs.setString(1, "2006-01-01"); //cs.registerOutParameter(2, OracleTypes.ARRAY,"T_ARRAY"); //传出参数 cs.registerIndexTableOutParameter(2,200,OracleTypes.VARCHAR,4000); cs.registerOutParameter(3, Types.VARCHAR); //传出参数sOutMsg cs.registerOutParameter(4, Types.NUMERIC); //传出参数iRes try { cs.execute(); if (cs.getInt(4) < 0) { throw new SQLException(cs.getString(3)); } String[ ] values =(String[])cs.getPlsqlIndexTable(2); ........5、oracle中输出可变数组,java中调用 OracleCallableStatement cs = null; cs = (OracleCallableStatement)conn.prepareCall("begin pk_cxtj.pro_StorageStatus(?,?,?,?); end;"); cs.setString(1, "2006-01-01"); cs.registerOutParameter(2, OracleTypes.ARRAY,"T_ARRAY"); //传出参数 //cs.registerIndexTableOutParameter(2,4000,OracleTypes.VARCHAR,4000); cs.registerOutParameter(3, Types.VARCHAR); //传出参数sOutMsg cs.registerOutParameter(4, Types.NUMERIC); //传出参数iRes try { cs.execute(); if (cs.getInt(4) < 0) { throw new SQLException(cs.getString(3)); } Array lll = (Array)cs.getArray(2); String[ ] values = (String[])lll.getArray(); for (int i = 0;i<values.length;i++){ System.out.println(values[i]); } ....
DECLARE TYPE ABC IS TABLE OF NUMBER; X ABC := ABC(4, 5, 6); BEGIN FOR i IN 1..x.count LOOP dbms_output.put_line(x(i)); END LOOP; END; / 输出: 4 5 6
用plsql的索引表,索引表元素没有限制,下表也可以为负值 declare type ename_table_type is table of emp.ename%type index by binary_integer; ename_type ename_table_type; begin select ename into ename_type(-2) from emp where empno=7788; select ename into ename_type(5) from emp where empno=7780; dbms_output.put_line(ename_type(-2)); dbms_output.put_line(ename_type(5)); end;
http://topic.csdn.net/u/20081006/16/f0a09bd5-a440-4a4c-a3fa-a835a353b40d.html
create or replace type t_array as table of varchar2(4000);2、package中 声明cursor type T_cur is ref cursor;
存储过程
procedure pro_StorageStatus(as_month varchar2, --查询月份
cur_1 out t_cur, --返回
as_OutMsg out varchar2, --传出参数
an_Res out int) is
l_jg t_array := t_array();
ls_temp varchar2(4000);
begin
...
l_jg.extend;
l_jg(l_jg.last) := ls_temp;
...
open cur_1 for select * from table(cast (l_jg as t_array));
end;3、Java中调用
CallableStatement cs = conn.prepareCall("call pk_cxtj.pro_StorageStatus(?,?,?,?)");
cs.setString(1,month);
cs.registerOutParameter(2,OracleTypes.CURSOR);
cs.registerOutParameter(3, Types.VARCHAR);
cs.registerOutParameter(4, Types.NUMERIC);
try {
cs.execute();
if (cs.getInt(4) < 0) {
throw new SQLException(cs.getString(3));
}
else {
rs = (ResultSet)cs.getObject(2);
........ 4、Oracle中输出IndexTable,java中调用
OracleCallableStatement cs = null;
cs = (OracleCallableStatement)conn.prepareCall("begin pk_cxtj.pro_StorageStatus(?,?,?,?); end;");
cs.setString(1, "2006-01-01");
//cs.registerOutParameter(2, OracleTypes.ARRAY,"T_ARRAY"); //传出参数
cs.registerIndexTableOutParameter(2,200,OracleTypes.VARCHAR,4000);
cs.registerOutParameter(3, Types.VARCHAR); //传出参数sOutMsg
cs.registerOutParameter(4, Types.NUMERIC); //传出参数iRes
try {
cs.execute();
if (cs.getInt(4) < 0) {
throw new SQLException(cs.getString(3));
}
String[ ] values =(String[])cs.getPlsqlIndexTable(2);
........5、oracle中输出可变数组,java中调用
OracleCallableStatement cs = null;
cs = (OracleCallableStatement)conn.prepareCall("begin pk_cxtj.pro_StorageStatus(?,?,?,?); end;");
cs.setString(1, "2006-01-01");
cs.registerOutParameter(2, OracleTypes.ARRAY,"T_ARRAY"); //传出参数
//cs.registerIndexTableOutParameter(2,4000,OracleTypes.VARCHAR,4000);
cs.registerOutParameter(3, Types.VARCHAR); //传出参数sOutMsg
cs.registerOutParameter(4, Types.NUMERIC); //传出参数iRes
try {
cs.execute();
if (cs.getInt(4) < 0) {
throw new SQLException(cs.getString(3));
}
Array lll = (Array)cs.getArray(2);
String[ ] values = (String[])lll.getArray();
for (int i = 0;i<values.length;i++){
System.out.println(values[i]);
}
....
TYPE ABC IS TABLE OF NUMBER;
X ABC := ABC(4, 5, 6);
BEGIN
FOR i IN 1..x.count LOOP
dbms_output.put_line(x(i));
END LOOP;
END;
/
输出:
4
5
6
declare
type ename_table_type is table of emp.ename%type index by binary_integer;
ename_type ename_table_type;
begin
select ename into ename_type(-2) from emp where empno=7788;
select ename into ename_type(5) from emp where empno=7780;
dbms_output.put_line(ename_type(-2));
dbms_output.put_line(ename_type(5));
end;
有一大堆例子