使用集合变量作为输入参数
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;
/
下面是存储过程代码:
SQL code
