Oracle中自定义函数、无参数、输入参数、输出参数等各种用法(二)
_type IS TABLE OF VARCHAR2(10);
/
CREATE OR REPLACE FUNCTION get_name
(dno NUMBER) RETURN ename_table_type IS
ename_table ename_table_type;
BEGIN
SELECT ename BULK COLLECT INTO ename_table FROM emp WHERE deptno=dno;
RETURN ename_table;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20099,'该部门不存在');
END;
/
DECLARE
ename_table ename_table_type;
BEGIN
ename_table:=get_name(&dno);
dbms_output.put_line('姓名：'||ename_table(i));
END LOOP;
END;
/
-- 19-17:删除函数
DROP FUNCTION get_name;
-- 19-18:显示编译错误
SHOW ERRORS
-- 19-19:确定函数状态
SELECT object_name FROM user_objects WHERE status='INVALID' AND object_type='FUNCTION';
-- 19-20:编译函数
ALTER FUNCTION get_info COMPILE;
-- 19-21:查看函数代码
SELECT text FROM user_source WHERE name='GET_INFO';