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

createorreplaceproceduredirectory_path_change(from_catalogIdinteger,to_catalogIdinteger,except_arrayarray_num)is ----把采购分类下的采购品平移到另一分类下,except_array除外的采购品 tmp_IDNUMBER(19);--需要转移的采购品ID catalog_treepathVARCHAR2(768CHAR);--目标分类的路径 inumber(10);--数组下标从1开始 isUpdatenumber(1);--是否执行更新0:不执行;1:执行 cursordirectory_cursoris selectidfromcorp_directorysdwhered.catalog_id=from_catalogId; begin selecttreepathintocatalog_treepathfromcorp_catalogswhereid=to_catalogId; savepointp1;--设置回滚点 opendirectory_cursor; loop fetchdirectory_cursorinto tmp_ID; exitwhendirectory_cursor%NOTFOUND; DBMS_OUTPUT.PUT_LINE('=============ID:'); DBMS_OUTPUT.PUT_LINE(tmp_ID); i:=1; isUpdate:=0; foriin1..except_array.countLoop ifexcept_array(i)=tmp_IDthen isUpdate:=1; endif; endloop; ifisUpdate=1then updatecorp_directorysdsetd.catalog_id=to_catalogId,d.treepath=(catalog_treepath||d.id||'#')whered.id=tmp_ID; endif; commit; endloop; closedirectory_cursor; --发生异常时,数据回滚 exception whenothersthen dbms_output.put_line(sqlerrm); rollbacktosavepointp1; enddirectory_path_change;