使用自定义类型

CREATE OR REPLACE PACKAGE BODY PK_TASK IS

TYPE TASKBYMETYPE IS RECORD(
EMP_ID SYS_WFW_TASK.HANDER_EMP_ID%TYPE,
UNHAND_COUNT NUMBER);

TYPE TASKBYMETYPELIST IS TABLE OF TASKBYMETYPE INDEX BY BINARY_INTEGER;

PROCEDURE SP_HANDBYME_SENDMAIL_TIMER IS
TASKBYMELIST TASKBYMETYPELIST;
v_mail_language VARCHAR2(40):='all';
v_sys_name VARCHAR2(10) :='HOL-WAS';
v_mailResult VARCHAR2(50);

BEGIN
SELECT T.HANDER_EMP_ID, COUNT(0) HANDER_COUNT
BULK COLLECT
INTO TASKBYMELIST
FROM (SELECT T.*
FROM SYS_WFW_TASK T
UNION ALL
SELECT S.*
FROM SYS_WFW_TASK_HIS S) T
INNER JOIN (SELECT *
FROM SYS_WFW_PROC
UNION ALL
SELECT *
FROM SYS_WFW_PROC_HIS) P
ON P.PROC_ID = T.PROC_ID
WHERE T.ENABLED = 1
AND T.TASK_STATUS_ID = 'TS0001'
GROUP BY T.HANDER_EMP_ID;

IF TASKBYMELIST.COUNT>0 THEN
FOR i IN TASKBYMELIST.FIRST .. TASKBYMELIST.LAST LOOP
--do something

COMMIT;

END LOOP;
END IF;

END;

END PK_TASK;