oracle存储过程,test(测试)时传自定义类型参数问题

使用集合变量作为输入参数
CREATETYPEdeptno_table_typeISTABLEOFNUMBER(2);
/
CREATETYPEdname_table_typeISTABLEOFVARCHAR2(10);
/
CREATETYPEloc_table_typeISTABLEOFVARCHAR2(20);
/
CREATEORREPLACEPROCEDUREadd_department(
deptno_tabledeptno_table_type,
dname_tabledname_table_type,loc_tableloc_table_type)
IS
BEGIN
FORALLiIN1..deptno_table.COUNT
INSERTINTOdeptVALUES
(deptno_table(i),dname_table(i),loc_table(i));
EXCEPTION
WHENDUP_VAL_ON_INDEXTHEN
RAISE_APPLICATION_ERROR(-20012,'部门号不能重复');
END;
/
DECLARE
deptno_tabledeptno_table_type:=deptno_table_type(60,70,80);
dname_tabledname_table_type:=dname_table_type('计划处','质量处','技术处');
loc_tableloc_table_type:=loc_table_type('呼和浩特','包头','乌海');
BEGIN
add_department(deptno_table,dname_table,loc_table);
END;
/
18-18:使用集合变量作为输出参数
CREATETYPEename_table_typeISTABLEOFVARCHAR2(10);
/
CREATETYPEjob_table_typeISTABLEOFVARCHAR2(10);
/
CREATEORREPLACEPROCEDUREget_emp(
dnoNUMBER,ename_tableOUTename_table_type,
job_tableOUTjob_table_type)IS
BEGIN
SELECTename,jobBULKCOLLECTINTOename_table,job_table
FROMempWHEREdeptno=dno;
EXCEPTION
WHENNO_DATA_FOUNDTHEN
RAISE_APPLICATION_ERROR(-20010,'该部门不存在');
END;
/
DECLARE
ename_tableename_table_type;
job_tablejob_table_type;
BEGIN
get_emp(&dno,ename_table,job_table);
FORiIN1..ename_table.COUNTLOOP
dbms_output.put_line('姓名:'||ename_table(i)
||',岗位:'||job_table(i));
ENDLOOP;
END;

/

下面是存储过程代码:

declare

except_arrayarray_num:=array_num(90818041);

from_catalogidnumber:=90818040;

to_catalogidnumber:=91469311;

begin

--Calltheprocedure

directory_path_change(from_catalogid,to_catalogid,except_array);

end ;

SQL code ?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47

create or replace procedure directory_path_change(from_catalogId integer ,to_catalogId integer ,except_arrayarray_num) is ----把采购分类下的采购品平移到另一分类下,except_array除外的采购品 tmp_IDNUMBER(19); --需要转移的采购品ID catalog_treepathVARCHAR2(768 CHAR ); --目标分类的路径 inumber(10); --数组下标从1开始 isUpdatenumber(1); --是否执行更新0:不执行;1:执行 cursor directory_cursor is select id from corp_directorysd where d.catalog_id=from_catalogId; begin select treepath into catalog_treepath from corp_catalogs where id=to_catalogId; savepointp1; --设置回滚点 open directory_cursor; loop fetch directory_cursor into tmp_ID; exit when directory_cursor%NOTFOUND; DBMS_OUTPUT.PUT_LINE( '=============ID:' ); DBMS_OUTPUT.PUT_LINE(tmp_ID); i:=1; isUpdate:=0; for i in 1..except_array. count Loop ifexcept_array(i)=tmp_ID then isUpdate:=1; end if; end loop; ifisUpdate=1 then update corp_directorysd set d.catalog_id=to_catalogId,d.treepath=(catalog_treepath||d.id|| '#' ) where d.id=tmp_ID; end if; commit ; end loop; close directory_cursor; --发生异常时,数据回滚 exception when others then dbms_output.put_line(sqlerrm); rollback to savepointp1; end directory_path_change;