采用bulk collect可以将查询结果一次性地加载到collections中
--创建一个属性
CREATE TYPE "SECURITY_APPLICATION_IDS" IS TABLE OF VARCHAR2(200);
--函数
CREATE OR REPLACE FUNCTION "F_GET_ABOLISH_APPLICATION_IDS"
RETURN SECURITY_APPLICATION_IDS as
appIds SECURITY_APPLICATION_IDS;
BEGIN
select decode(level,
1,
'TYPE_01',
2,
'TYPE_02',
3,
'TYPE_03',
4,
'ETYPE_4',
5,
'TYPE_5') bulk collect
into appIds
from DUAL
connect by level <= 5;
return appIds;
EXCEPTION
WHEN OTHERS THEN
RETURN appIds;
END F_GET_ABOLISH_APPLICATION_IDS;
--用法
select * from table(F_GET_ABOLISH_APPLICATION_IDS)