thomas01042003
- 浏览: 15271 次
- 性别:
- 来自: 北京
-
最新评论
-
thomas01042003：
Oracle函数返回Table集合
在实际的应用中，为 ...
oracle 数据库函数 返回数据集合
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;
/
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;
/
分享到：
发表评论
-
Cognos优化
2012-05-20 22:15 722Cognos优化 ===================== ... -
删除一个表中重复的记录，并保存一条记录
2012-04-27 10:25 606SELECT * FROM TABLE_1 A WHER ... -
通过START WITH 和CONNECT BY PRIOR遍历上级父节点（上级目录）
2012-04-20 16:51 1493向上遍历父节点 SELECT * FROM (SELECT * ... -
Oracle递归树select...start with... connect by ...prior 理解
2012-04-16 15:09 681Oracle树查询的最重要的 ... -
Oracle中CASE WHEN的用法实例
2012-04-09 14:55 740实例演示： ... -
捕获存储过程中自定义异常
2012-03-27 17:31 1109--通过sqlcode , sqlerrm 这两个内置变量来查 ... -
关于数据库周的概念
2012-03-26 14:51 714数据库中默认的周的概念是从上周日到本周六，例如：2012年3月 ... -
Oracle9i中使用SYS_CONNECT_BY_PATH进行行列转换
2012-03-20 14:13 843有表： SQL> SELECT deptno, enam ... -
oracle 数据库函数 返回数据集合
2012-02-23 18:36 1470关于oracle的plsql Pipelined pipe r ... -
EXISTS、IN与JOIN
2012-02-16 21:42 608EXISTS、IN与JOIN，都可以用来实现形如“查询A表中在 ... -
DBLINK访问LOB问题
2012-01-30 16:52 1030DBLINK访问LOB问题(2008-02-15 15:19: ...
相关推荐
以上只是SQL函数集合中的一部分，实际中还有更多的函数，如位运算函数、窗口函数等，每个都有自己独特的用途。掌握并熟练运用这些函数，能有效提升SQL查询的效率和灵活性。在实际工作中，可以根据需求灵活选择和组合...
当数据集合中的某些元素还没有对应的DOM元素时，D3会通过进入模式创建新的DOM元素。例如，如果你有一个数组，其中包含五个元素，而SVG画布上目前只有三个元素，D3会为剩下的两个元素创建新的SVG元素，并将它们绑定...
C语言的函数集合是其核心特性之一，这些函数提供了处理内存分配、输入/输出、数据操作等多种功能。在这个压缩包文件中，我们看到了一系列关于C语言内存管理函数的例子，它们是C语言程序设计中的基础且关键的部分。 ...
Java Native Interface（JNI）是Java平台的标准组成部分，它允许...这就是一个简单的JNI返回对象数组的例子。通过这种方式，我们可以灵活地在Java和本地代码之间传递复杂的数据结构，极大地扩展了Java应用程序的能力。
本资料集“matlab数理统计和数据分析及优化求解：35 matlab多元变量无约束条件下函数最小值”显然是针对这个主题的教程或示例集合。下面我们将详细探讨这一领域中的关键知识点。 1. **多元变量函数**：在数学中，...
Excel函数集合及VBA范例是提升Excel技能的两个重要方面。Excel函数是电子表格软件中的强大工具，能够帮助用户快速、准确地处理大量数据。而VBA（Visual Basic for Applications）则是Excel中的编程语言，允许用户...
### 数据库函数实例的小例子 有助于初学者更好的理解存储过程的操作 #### 背景介绍 在数据库领域，存储过程是一种预编译的SQL代码集合，它可以被当作一个单独的对象来调用，从而提高数据库应用程序的性能和复用性。...
调用函数时，我们可以传递参数给函数，然后使用函数返回的结果。例如： ```c int result = add(3, 5); // 调用函数 printf("结果是: %d\n", result); // 输出结果 ``` 在这里，我们调用了`add`函数并将结果...
如果`c`是字母或数字，函数返回非零（通常是1），表示TRUE；否则返回0，表示FALSE。`isalnum`可以看作是`isalpha`和`isdigit`的组合，它同时检测字符是否属于这两类。 示例代码： ```c #include int main() { ...
在C语言中，数组是一种非常重要的数据结构，用于存储同类型的数据集合。本文将深入探讨如何在函数中使用数组作为参数以及如何通过函数返回数组。 首先，我们要了解函数通过数组作为参数的情况。在C语言中，当我们...
函数可以返回一个值，返回类型定义了这个值的数据类型。如上例所示，`int`是返回类型，表示函数将返回一个整数。如果函数不返回值，可以使用`void`作为返回类型。 三、参数列表 参数列表是函数头的一部分，定义了...
Oracle管道函数是一种特殊类型的函数，它能够返回一个数据集合，并且能够在函数执行的过程中逐步返回结果，而不仅仅是最后的结果。这种特性使得管道函数非常适合于处理大量数据或者需要逐步展示处理进度的场景。 ##...
例如，TODAY函数返回当前日期，而DATE函数可组合年、月、日创建自定义日期。 4. **文本函数**：LEFT、RIGHT、MID、FIND、SEARCH、CONCATENATE、TEXT等用于处理文本数据。LEFT函数返回字符串左侧的字符，而...
例如，Collections.sort()方法接受一个Comparator实例，这是一个高阶函数的例子，因为它接受一个函数作为参数来定义元素的排序顺序。 6. **函数式编程的优点**： - 更少的副作用：函数式编程倾向于避免改变状态，...
1. **SUM函数**：返回指定集合的成员之和。例如，`SUM([Measures].[Sales Amount], [Product].[Category].[All])`将返回所有产品类别销售金额的总和。 2. **CURRENTMEMBER函数**：返回当前上下文中的成员。例如，`...
在本教程中，我们将深入探讨`split()`函数的用法，以及它如何与Python的集合操作相结合，帮助我们高效地查找数据。 首先，让我们了解`split()`函数的基本语法。`str.split([sep[, maxsplit]])`接受两个可选参数：...
- **成员函数**：如`Member_Name`返回成员的名称，`Member_Value`获取成员的值。 - **集合函数**：如`Filter`根据条件筛选集合，`Intersect`和`Union`用于集合的交集和并集操作。 - **其他函数**：还包括计算函数...
在`select`调用返回后，程序检查是否有新的连接请求，或者已连接的客户端是否有数据可读或需要发送数据。通过这种方式，程序能够有效地管理多个并发连接，而不必为每个连接创建单独的线程或进程。 ### 总结 通过...
函数是C语言中的一个关键元素，它是一组执行特定任务的语句集合。函数定义了输入（参数）和输出（返回值），可以被其他部分的代码多次调用。在C语言中，函数的定义包括函数名、返回类型、参数列表以及函数体。例如，...
Stream API是Java 8的另一个重要创新，它提供了对数据集合进行声明性处理的能力，就像SQL查询数据库一样。Stream可以看作是从数据源（如集合、数组）生成的一系列元素序列，支持过滤、映射、查找、归约等操作。例如...