函数返回数据集合例子
CREATE OR REPLACE TYPE ictpm.objemp AS OBJECT (
menuId VARCHAR2(200 CHAR),--节点id
menuCount NUMBER(4) --节点数
);
/
CREATE OR REPLACE TYPE ictpm.tabemp AS TABLE OF ictpm.objemp;
/
CREATE OR REPLACE FUNCTION ictpm.F_O_GET_FIRSTPAGE_COUNT(USERID VARCHAR2)
RETURN ictpm.tabemp PIPELINED
IS
/******************************************************************************
NAME: 查询ICT首页待处理任务总数函数
PURPOSE: 减少访问数据库次数，提高查询性能
REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 2012-02-16 wangsongtao 1. ICT项目管理系统
NOTES:
userId：用户row_id
******************************************************************************/
TOTALCOUNT NUMBER := 0;--总数
TEMPNUMBER NUMBER;--临时数
WORKUNITID VARCHAR2(20 CHAR);--用户所属单位
WORKUNITKIND VARCHAR2(30 CHAR); --用户单位类别
USERNAME VARCHAR2(30 CHAR); --用户名称
v objemp; --返回结果集
CURSOR funcList IS (SELECT T.WEB_PAGE --权限集合
FROM TH_FUNCTION T, TH_USER_ROLE_REL UR, TH_ROLE_FUNC_REL RF
WHERE T.ROW_ID = RF.FUNC_ID
AND RF.ROLE_ID = UR.ROLE_ID
AND UR.USER_ID = userId
UNION
SELECT T.WEB_PAGE
FROM TH_FUNCTION T, TH_USER_FUNC_REL UF
WHERE UF.USER_ID = userId
AND T.ROW_ID = UF.FUNC_ID);
BEGIN
SELECT USR.WORK_UNIT_ID,USR.WORK_UNIT_KIND,USR.USER_NAME --获取用户所属单位
INTO WORKUNITID,WORKUNITKIND,USERNAME
FROM TH_USER USR
WHERE USR.ROW_ID = userId;
--遍历所有用户权限
FOR ROWVAL IN FUNCLIST LOOP
IF ROWVAL.WEB_PAGE = '/view/ictpm/mywork/main.jsp?linkTo=/view/mywork/projectfirst-list_need_amend.action'
THEN --需我送审的项目
SELECT COUNT(1) INTO TEMPNUMBER
FROM ICTPM_PROJECTINFO T
WHERE T.STATE = '0' AND T.INPUTPEOPLE = userId;
TOTALCOUNT := TEMPNUMBER + TOTALCOUNT;
v:= objemp('reqMyTask-reqModifyPro',TEMPNUMBER);
PIPE ROW (v);
ELSIF ROWVAL.WEB_PAGE = '/view/ictpm/mywork/main.jsp?linkTo=/view/mywork/projectfirst-list_need_audit.action'
THEN --需我审核的项目
SELECT COUNT(1) INTO TEMPNUMBER
FROM ICTPM_PROJECTINFO T
WHERE T.STATE = '1' AND T.PROJECTMANAGER = userId;
TOTALCOUNT := TEMPNUMBER + TOTALCOUNT;
v:= objemp('reqMyTask-reqAuditPro',TEMPNUMBER);
PIPE ROW (v);
ELSIF ROWVAL.WEB_PAGE = '/view/ictpm/mywork/main.jsp?linkTo=/view/mywork/projectfirst-list_need_submit.action'
THEN --需我提交的协调任务单
SELECT COUNT(1) INTO TEMPNUMBER
FROM ICTPM_WORKORDER ER
WHERE ER.STATE = '0' AND ER.INPUTPEOPLE = userId;
TOTALCOUNT := TEMPNUMBER + TOTALCOUNT;
v:= objemp('reqMyTask-reqSubmitWorkOrder',TEMPNUMBER);
PIPE ROW (v);
ELSIF ROWVAL.WEB_PAGE = '/view/ictpm/mywork/main.jsp?linkTo=/view/mywork/projectfirst-list_need_distribute.action'
THEN --需我派发的协调任务单
SELECT COUNT(1) TOTALCOUNT INTO TEMPNUMBER
FROM ICTPM_WORKORDER ER
WHERE EXISTS (SELECT 1
FROM ICTPM_PROJECTINFO INFO
WHERE ER.PROJECTID = INFO.ID
AND ER.STATE = '1'
AND INFO.PROJECTMANAGER = userId);
TOTALCOUNT := TEMPNUMBER + TOTALCOUNT;
v:= objemp('reqMyTask-reqSendWorkOrder',TEMPNUMBER);
PIPE ROW (v);
ELSIF ROWVAL.WEB_PAGE = '/view/ictpm/mywork/main.jsp?linkTo=/view/mywork/projectfirst-list_need_feedback.action'
THEN --需我反馈的协调任务单
SELECT COUNT(1) TOTALCOUNT INTO TEMPNUMBER
FROM ICTPM_WORKORDER ER
WHERE ER.STATE IN ('2','6','7') AND ER.RECEIVEUNITID = WORKUNITID;
TOTALCOUNT := TEMPNUMBER + TOTALCOUNT;
v:= objemp('reqMyTask-reqFeedbackWorkOrder',TEMPNUMBER);
PIPE ROW (v);
ELSIF ROWVAL.WEB_PAGE = '/view/ictpm/mywork/main.jsp?linkTo=/view/mywork/projectfirst-list_need_feedback_affirm.action'
THEN --需我确认的协调任务单反馈
SELECT COUNT(1) TOTALCOUNT INTO TEMPNUMBER
FROM ICTPM_WORKORDER ER
WHERE EXISTS (SELECT 1
FROM ICTPM_PROJECTINFO INFO
WHERE ER.PROJECTID = INFO.ID
AND ER.STATE = '8'
AND INFO.PROJECTMANAGER = userId);
TOTALCOUNT := TEMPNUMBER + TOTALCOUNT;
v:= objemp('reqMyTask-reqFeedbackComfirmWorkOrder',TEMPNUMBER);
PIPE ROW (v);
ELSIF ROWVAL.WEB_PAGE = '/view/ictpm/mywork/main.jsp?linkTo=/view/mywork/projectfirst-teamorder_need_submit.action'
THEN --需我提交的团队建设通知单
SELECT COUNT(1) AS TOTALCOUNT INTO TEMPNUMBER
FROM ICTPM_TEAMORDER T
WHERE T.STATE = '0' AND T.INPUTPEOPLE = userId;
TOTALCOUNT := TEMPNUMBER + TOTALCOUNT;
v:= objemp('reqMyTask-reqSubmitTeamBuild',TEMPNUMBER);
PIPE ROW (v);
ELSIF ROWVAL.WEB_PAGE = '/view/ictpm/mywork/main.jsp?linkTo=/view/mywork/projectfirst-teamorder_need_distribute.action'
THEN --需我派发的团队建设通知单
SELECT COUNT(1) TOTALCOUNT INTO TEMPNUMBER
FROM ICTPM_TEAMORDER T
WHERE EXISTS (SELECT 1 FROM ICTPM_PROJECTINFO INFO
WHERE INFO.ID = T.PROJECTID AND T.STATE = '1' AND INFO.PROJECTMANAGER = userId);
TOTALCOUNT := TEMPNUMBER + TOTALCOUNT;
v:= objemp('reqMyTask-reqSendTeamBuild',TEMPNUMBER);
PIPE ROW (v);
ELSIF ROWVAL.WEB_PAGE = '/view/ictpm/mywork/main.jsp?linkTo=/view/mywork/projectfirst-teamorder_need_feedback.action'
THEN --需我反馈的团队建设通知单
SELECT COUNT(1) TOTALCOUNT INTO TEMPNUMBER
FROM ICTPM_TEAMORDER T
WHERE T.STATE = '2' AND T.RECEIVERID = WORKUNITID;
TOTALCOUNT := TEMPNUMBER + TOTALCOUNT;
v:= objemp('reqMyTask-reqFeedBackTeamBuild',TEMPNUMBER);
PIPE ROW (v);
ELSIF ROWVAL.WEB_PAGE = '/view/ictpm/mywork/main.jsp?linkTo=/view/mywork/projectfirst-list_minutes_need_submit.action'
THEN --需我提交的会议纪要
SELECT COUNT(1) TOTALCOUNT INTO TEMPNUMBER
FROM ICTPM_SETUPMEET T
WHERE T.STATE = '0' AND T.INPUTPEOPLE = userId;
TOTALCOUNT := TEMPNUMBER + TOTALCOUNT;
v:= objemp('reqMyTask-reqSubmitMeetingContent',TEMPNUMBER);
PIPE ROW (v);
ELSIF ROWVAL.WEB_PAGE = '/view/ictpm/mywork/main.jsp?linkTo=/view/mywork/projectfirst-list_minutes_need_audit.action'
THEN --需我审核的会议纪要
SELECT COUNT(1) TOTALCOUNT INTO TEMPNUMBER
FROM ICTPM_SETUPMEET T
WHERE EXISTS (SELECT 1
FROM ICTPM_PROJECTINFO INFO
WHERE INFO.ID = T.PROJECTID
AND T.STATE = '1'
AND INFO.PROJECTMANAGER = userId);
TOTALCOUNT := TEMPNUMBER + TOTALCOUNT;
v:= objemp('reqMyTask-reqAuditMeetingContent',TEMPNUMBER);
PIPE ROW (v);
ELSIF ROWVAL.WEB_PAGE = '/view/ictpm/mywork/main.jsp?linkTo=/view/mywork/projectfirst-list_taskorder_need_audit.action'
THEN --需我审核的实施任务单
SELECT COUNT(1) TOTALCOUNT INTO TEMPNUMBER
FROM ICTPM_PROJECTINFO INFO
WHERE EXISTS (SELECT 1
FROM ICTPM_TASKORDER ER
WHERE INFO.ID = ER.PROJECTID
AND ER.TASKSTATE = '0'
AND INFO.STATE = '2'
AND INFO.PROJECTMANAGER = userId);
TOTALCOUNT := TEMPNUMBER + TOTALCOUNT;
v:= objemp('reqMyTask-reqAuditTaskOrder',TEMPNUMBER);
PIPE ROW (v);
ELSIF ROWVAL.WEB_PAGE = '/view/ictpm/mywork/main.jsp?linkTo=/view/mywork/projectfirst-list_taskorder_need_distribute.action'
THEN --需我派发的实施任务单
SELECT COUNT(1) TOTALCOUNT INTO TEMPNUMBER
FROM ICTPM_PROJECTINFO INFO
WHERE EXISTS (SELECT 1
FROM ICTPM_TASKORDER ER
WHERE INFO.ID = ER.PROJECTID
AND ER.TASKSTATE = '1'
AND INFO.STATE = '2'
AND INFO.PROJECTMANAGER = userId);
TOTALCOUNT := TEMPNUMBER + TOTALCOUNT;
v:= objemp('reqMyTask-reqSendTaskOrder',TEMPNUMBER);
PIPE ROW (v);
ELSIF ROWVAL.WEB_PAGE = '/view/ictpm/mywork/main.jsp?linkTo=/view/mywork/projectfirst-list_taskorder_need_receive.action'
THEN --需我接收的实施任务单
SELECT COUNT(1) TOTALCOUNT INTO TEMPNUMBER
FROM ICTPM_TASKORDER ER
WHERE EXISTS (SELECT 1
FROM ICTPM_PROJECTINFO INFO
WHERE INFO.ID = ER.PROJECTID
AND ER.RECEIVEUNITID = WORKUNITID
AND WORKUNITKIND <> '10330001'
AND ER.TASKSTATE IN ('2','5')
AND INFO.STATE = '2'
AND INFO.PROJECTMANAGER = userId);
TOTALCOUNT := TEMPNUMBER + TOTALCOUNT;
v:= objemp('reqMyTask-reqReciveTaskOrder',TEMPNUMBER);
PIPE ROW (v);
ELSIF ROWVAL.WEB_PAGE = '/view/ictpm/mywork/main.jsp?linkTo=/view/mywork/projectfirst-list_taskorder_need_distribute_transpond.action'
THEN --需我转派的实施任务单
SELECT COUNT(1) TOTALCOUNT INTO TEMPNUMBER
FROM ICTPM_TASKORDER ER
WHERE ER.TASKSTATE IN ('3','4')
AND ER.RECEIVERID = userId;
TOTALCOUNT := TEMPNUMBER + TOTALCOUNT;
v:= objemp('reqMyTask-reqTransformTaskOrder',TEMPNUMBER);
PIPE ROW (v);
ELSIF ROWVAL.WEB_PAGE = '/view/ictpm/mywork/main.jsp?linkTo=/view/mywork/projectfirst-list_taskorder_need_feedback.action'
THEN --需我反馈的实施任务单
SELECT COUNT(1) TOTALCOUNT INTO TEMPNUMBER
FROM ICTPM_TASKORDER ER
WHERE ER.TASKSTATE IN ('3','6')
AND ER.RECEIVERID = userId;
TOTALCOUNT := TEMPNUMBER + TOTALCOUNT;
v:= objemp('reqMyTask-reqFeedBackTaskOrder',TEMPNUMBER);
PIPE ROW (v);
ELSIF ROWVAL.WEB_PAGE = '/view/ictpm/mywork/main.jsp?linkTo=/view/mywork/projectfirst-list_taskorder_need_feedback_expire.action'
THEN --需我反馈的到期实施任务单
SELECT COUNT(1) TOTALCOUNT INTO TEMPNUMBER
FROM ICTPM_TASKORDER T
WHERE T.RECEIVERID = userId
AND T.TASKSTATE IN ('j', 'a', 'b', 'c', 'd', 'e', 'f', 'g')
AND EXISTS (SELECT 1
FROM ICTPM_TASKORDERFEEDBACK TOB
WHERE TOB.TASKID = T.ID
AND TOB.STATE IN ('0', '1', '2'));
TOTALCOUNT := TEMPNUMBER + TOTALCOUNT;
v:= objemp('reqMyTask-reqNotFeedBackTaskOrder',TEMPNUMBER);
PIPE ROW (v);
ELSIF ROWVAL.WEB_PAGE = '/view/ictpm/mywork/main.jsp?linkTo=/view/mywork/projectfirst-list_problem_need_feedback.action'
THEN --需我反馈的问题
SELECT COUNT(1) TOTALCOUNT INTO TEMPNUMBER
FROM ICTPM_PROBLEM P
WHERE EXISTS (SELECT 1
FROM ICTPM_TEAMORDERFEEDBACKDETAIL T
WHERE T.TEAM = P.ACCEPTTEAM
AND P.STATE IN ('0', '1')
AND T.UNITID = WORKUNITID
AND T.NAME = USERNAME);
TOTALCOUNT := TEMPNUMBER + TOTALCOUNT;
v:= objemp('reqMyTask-reqFeedBackQuestionDeal',TEMPNUMBER);
PIPE ROW (v);
ELSIF ROWVAL.WEB_PAGE = '/view/ictpm/mywork/main.jsp?linkTo=/view/mywork/projectfirst-list_problem_need_affirm.action'
THEN --需我确认的问题
SELECT COUNT(1) TOTALCOUNT INTO TEMPNUMBER
FROM ICTPM_PROBLEM P
WHERE P.INPUTPEOPLE = userId
AND P.STATE IN ('0','1','2');
TOTALCOUNT := TEMPNUMBER + TOTALCOUNT;
v:= objemp('reqMyTask-reqComfirmQuestionDeal',TEMPNUMBER);
PIPE ROW (v);
ELSIF ROWVAL.WEB_PAGE = '/view/ictpm/mywork/main.jsp?linkTo=/view/mywork/projectfirst-list_need_feedbacklist.action'
THEN --需我反馈的催单
SELECT COUNT(1) TOTALCOUNT INTO TEMPNUMBER
FROM ICTPM_REMINDORDER T
WHERE T.RECEIVERID = userId
AND T.STATE IN ('2','3');
TOTALCOUNT := TEMPNUMBER + TOTALCOUNT;
v:= objemp('reqMyTask-reqFeedBackReminder',TEMPNUMBER);
PIPE ROW (v);
ELSIF ROWVAL.WEB_PAGE = '/view/ictpm/mywork/main.jsp?linkTo=/view/mywork/projectfirst-list_need_audit_listExamineChange.action'
THEN --需我审批的验收通知单变更
SELECT COUNT(1) TOTALCOUNT INTO TEMPNUMBER
FROM ICTPM_EXAMINE_CHANGE C
WHERE C.APPROVESTS = '1'
AND EXISTS (SELECT 1 FROM ICTPM_EXAMINE E
WHERE EXISTS (SELECT 1 FROM ICTPM_PROJECTINFO INFO
WHERE INFO.ID = E.PROJECTID
AND INFO.PROJECTMANAGER = userId));
TOTALCOUNT := TEMPNUMBER + TOTALCOUNT;
v:= objemp('reqMyTask-reqAuditCheckInfoChangeOrder',TEMPNUMBER);
PIPE ROW (v);
ELSIF ROWVAL.WEB_PAGE = '/view/ictpm/mywork/main.jsp?linkTo=/view/mywork/projectfirst-list_need_audit_listTeamorderfbdChange.action'
THEN --需我审批的项目成员变更
SELECT COUNT(CID) TOTALCOUNT INTO TEMPNUMBER
FROM (SELECT T.ID CID
FROM ICTPM_TEAMORDERFBD_CHANGE T
LEFT JOIN ICTPM_TEAMORDERORGARCHITECTURE X ON X.ID = T.TEAM
LEFT JOIN ICTPM_PROJECTINFO Y ON Y.ID = X.PROJECTID
WHERE T.APPROVESTS = '1'
AND Y.PROJECTMANAGER = userId
UNION
SELECT T.ID CID
FROM ICTPM_TEAMORDERFBD_CHANGE T
LEFT JOIN ICTPM_TEAMORDERORGARCHITECTURE X ON X.ID = T.TEAMNEW
LEFT JOIN ICTPM_PROJECTINFO Y ON Y.ID = X.PROJECTID
WHERE T.APPROVESTS = '1'
AND Y.PROJECTMANAGER = userId
AND T.CREATEORDEL = '2');
TOTALCOUNT := TEMPNUMBER + TOTALCOUNT;
v:= objemp('reqMyTask-reqAuditProMemberChange',TEMPNUMBER);
PIPE ROW (v);
ELSIF ROWVAL.WEB_PAGE = '/view/ictpm/mywork/main.jsp?linkTo=/view/mywork/projectfirst-list_need_Projects_meeting.action'
THEN --需我审核的项目协调会
SELECT COUNT(1) TOTAL INTO TEMPNUMBER
FROM ICTPM_CONCERTMEET T
WHERE EXISTS (SELECT 1
FROM ICTPM_PROJECTINFO INFO
WHERE INFO.STATE = '2'
AND INFO.PROJECTMANAGER = userId
AND T.STATE = '1'
AND INFO.ID = T.PROJECTID);
TOTALCOUNT := TEMPNUMBER + TOTALCOUNT;
v:= objemp('reqMyTask-reqAuditProMeeting',TEMPNUMBER);
PIPE ROW (v);
ELSIF ROWVAL.WEB_PAGE = '/view/ictpm/mywork/main.jsp?linkTo=/view/mywork/projectfirst-list_need_acceptExamTaskorderList.action'
THEN --需我接收的验收通知单
SELECT COUNT(1) TOTAL INTO TEMPNUMBER
FROM ICTPM_EXAMINE E
WHERE E.RECEIVEPEOPLEID = userId
AND E.STATE = '4';
TOTALCOUNT := TEMPNUMBER + TOTALCOUNT;
v:= objemp('reqMyTask-reqReciveCheckOrder',TEMPNUMBER);
PIPE ROW (v);
ELSIF ROWVAL.WEB_PAGE = '/view/ictpm/mywork/main.jsp?linkTo=/view/mywork/projectfirst-list_need_submit_acceptExamTaskorderList.action'
THEN --需我提交的验收通知单
SELECT COUNT(1) TOTAL INTO TEMPNUMBER
FROM ICTPM_EXAMINE E
WHERE E.RECEIVEPEOPLEID = userId
AND E.STATE IN ('5','7');
TOTALCOUNT := TEMPNUMBER + TOTALCOUNT;
v:= objemp('reqMyTask-reqSubmitCheckOrder',TEMPNUMBER);
PIPE ROW (v);
ELSIF ROWVAL.WEB_PAGE = '/view/ictpm/mywork/main.jsp?linkTo=/view/mywork/projectfirst-list_need_examAuditTaskorderList.action'
THEN --需我审核的验收通知单
SELECT COUNT(1) TOTAL INTO TEMPNUMBER
FROM ICTPM_EXAMINE E
WHERE E.STATE = '6'
AND EXISTS
(SELECT 1 FROM ICTPM_TASKORDER ER
WHERE ER.ID = E.TASKID
AND EXISTS
(SELECT 1 FROM ICTPM_PROJECTINFO INFO
WHERE INFO.ID = ER.PROJECTID
AND INFO.PROJECTMANAGER = userId));
TOTALCOUNT := TEMPNUMBER + TOTALCOUNT;
v:= objemp('reqMyTask-reqAuditCheckOrder',TEMPNUMBER);
PIPE ROW (v);
END IF;
END LOOP;
RETURN;
END F_O_GET_FIRSTPAGE_COUNT;
/
