zhchx0827
- 浏览: 191773 次
- 性别:
- 来自: 上海
-
文章分类
最新评论
-
小灯笼：
分布式消息队列高效部署及插件集群开发信息数据监控、分析实战（R ...
RabbitMQ入门学习——Work queues 工作队列 -
快乐的小六：
分布式消息队列高效部署及插件集群开发信息数据监控、分析实战（R ...
RabbitMQ入门学习——Work queues 工作队列 -
wyzxzws：
写得够详细～！
linux下java开发环境搭建 -
easy_jing01：
帮了我一个大忙，谢谢啦
jquery---------ajax+validate表单异步提交验证
CREATE OR REPLACE PROCEDURE PRC_STATISTICS_RTX(
--参数IN表示输入参数，OUT表示输出参数，类型可以使用任意Oracle中的合法类型。
CHECKYEAR IN VARCHAR2,
CHECKQUARTER IN VARCHAR2) AS
-----------------定义变量-------------------------
KPI_USER_ID NUMBER(11); --当前遍历的员工(被评价人)
KPI_PRAISE_ID NUMBER(11); --评价人
KPI_CONTACT_TIME NUMBER(11); --联系次数
KPI_DIRECTOR VARCHAR2(50); --直接主管,组织结构多负责人，所以可能有多个直接主管
KPI_DEPT_MANAGER VARCHAR2(50); --部门主管,组织结构多负责人，所以可能有多个部门主管
KPI_SUBORDINATE VARCHAR2(1000); --直接下属员工 (组长查询自己的组员，经理及总监查询小组负责人)
KPI_VARIABLE VARCHAR2(20); --每次遍历游标时，临时存放数据
D_QUESTION NUMBER(11); --主管的问卷ID
E_QUESTION NUMBER(11); --普通员工的问卷ID
--主管考核维度 直接定义，免去每次for循环来遍历
D_CHECK_LEVEL1 NUMBER(11); --直接主管
D_CHECK_LEVEL2 NUMBER(11); --直接下属
D_CHECK_LEVEL3 NUMBER(11); --同部门
D_CHECK_LEVEL4 NUMBER(11); --合作部门
--主管考核维度对应的上限人数
D_CHECK_NUM1 NUMBER(11); --直接主管
D_CHECK_NUM2 NUMBER(11); --直接下属
D_CHECK_NUM3 NUMBER(11); --同部门
D_CHECK_NUM4 NUMBER(11); --合作部门
--普通员工考核维度
E_CHECK_LEVEL1 NUMBER(11); --直接主管
E_CHECK_LEVEL2 NUMBER(11); --部门主管
E_CHECK_LEVEL3 NUMBER(11); --同部门
E_CHECK_LEVEL4 NUMBER(11); --合作部门
--普通员工考核维度对应的上限人数
E_CHECK_NUM1 NUMBER(11); --直接主管
E_CHECK_NUM2 NUMBER(11); --部门主管
E_CHECK_NUM3 NUMBER(11); --同部门
E_CHECK_NUM4 NUMBER(11); --合作部门
-----------------------定义数组---------------------
--考核维度表
-- checklevel m_kpi_360check_level%rowtype; --定义一个变量（每一行的数据）
TYPE CHECKLEVELTBL IS TABLE OF M_KPI_360CHECK_LEVEL%ROWTYPE INDEX BY BINARY_INTEGER; --用变量定义一个表结构
V_CHECKLEVEL CHECKLEVELTBL; --用新的表结构定义一个变量（当做数组使用）
--问卷
-- question t_360check_questions%rowtype;
TYPE QUESTIONTBL IS TABLE OF T_360CHECK_QUESTIONS%ROWTYPE INDEX BY BINARY_INTEGER;
V_QUESTION QUESTIONTBL;
--需要考核的员工
-- userextend t_usr_extend%rowtype;
TYPE USEREXTENDTBL IS TABLE OF T_USR_EXTEND%ROWTYPE INDEX BY BINARY_INTEGER;
V_EXTEND USEREXTENDTBL;
--员工的直接主管
TYPE DIRECTORTBL IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER; --用变量定义一个表结构
V_DIRECTOR DIRECTORTBL;
--RTX及邮件统计的发送人，接收人和联系次数
TYPE RTXCONTACT IS TABLE OF W_ECM_IM_LOG%ROWTYPE INDEX BY BINARY_INTEGER; --用变量定义一个表结构
V_RTXCONTACT RTXCONTACT;
---------------定义游标--------------------------
CURSOR CURSOR_1(NO NUMBER) IS
SELECT O.MANAGER_IDS
FROM M_ORGANIZATION O
WHERE O.PARENT_ID IN
(SELECT ORG_ID FROM M_EMPLOYEE E WHERE E.USER_ID = NO);
BEGIN
------------存储过程开始执行，记录日志--------------
WRITE_LOG('PROCEDURE:TEST3',
'START',
'存储过程TEST3开始执行：统计员工RTX及邮件联系人，生成绩效考核答卷表---------开始执行');
COMMIT;
SELECT * BULK COLLECT INTO V_CHECKLEVEL FROM M_KPI_360CHECK_LEVEL T; --考核维度
SELECT * BULK COLLECT
INTO V_QUESTION
FROM T_360CHECK_QUESTIONS
WHERE CHECK_YEAR = CHECKYEAR
AND CHECK_QUARTER = CHECKQUARTER; -- 问卷
SELECT T.* BULK COLLECT
INTO V_EXTEND
FROM T_USR_EXTEND T
JOIN M_EMPLOYEE E
ON T.USER_ID = E.USER_ID
WHERE T.IS_CHECK = '1'
AND E.STATUS = '1'; --参与考核员工
-----遍历考核维度，依次获得不同员工级别不同考核维度对应的维度ID-----
FOR I IN 1 .. V_CHECKLEVEL.COUNT LOOP
--主管
IF V_CHECKLEVEL(I).EMP_LEVEL = 0 THEN
CASE
WHEN V_CHECKLEVEL(I).APPRAISE_LEVEL = 1 THEN
--直接主管
D_CHECK_LEVEL1 := V_CHECKLEVEL(I).CHECK_LEVEL_ID;
D_CHECK_NUM1 := V_CHECKLEVEL(I).UPPER_LIMIT_NUM;
WHEN V_CHECKLEVEL(I).APPRAISE_LEVEL = 2 THEN
--直接下属
D_CHECK_LEVEL2 := V_CHECKLEVEL(I).CHECK_LEVEL_ID;
D_CHECK_NUM2 := V_CHECKLEVEL(I).UPPER_LIMIT_NUM;
WHEN V_CHECKLEVEL(I).APPRAISE_LEVEL = 3 THEN
--同部门员工
D_CHECK_LEVEL3 := V_CHECKLEVEL(I).CHECK_LEVEL_ID;
D_CHECK_NUM3 := V_CHECKLEVEL(I).UPPER_LIMIT_NUM;
WHEN V_CHECKLEVEL(I).APPRAISE_LEVEL = 4 THEN
--合作部门员工
D_CHECK_LEVEL4 := V_CHECKLEVEL(I).CHECK_LEVEL_ID;
D_CHECK_NUM4 := V_CHECKLEVEL(I).UPPER_LIMIT_NUM;
END CASE;
END IF;
--普通员工
IF V_CHECKLEVEL(I).EMP_LEVEL = 1 THEN
CASE
WHEN V_CHECKLEVEL(I).APPRAISE_LEVEL = 1 THEN
--直接主管
E_CHECK_LEVEL1 := V_CHECKLEVEL(I).CHECK_LEVEL_ID;
E_CHECK_NUM1 := V_CHECKLEVEL(I).UPPER_LIMIT_NUM;
WHEN V_CHECKLEVEL(I).APPRAISE_LEVEL = 5 THEN
--部门主管（二级主管）
E_CHECK_LEVEL2 := V_CHECKLEVEL(I).CHECK_LEVEL_ID;
E_CHECK_NUM2 := V_CHECKLEVEL(I).UPPER_LIMIT_NUM;
WHEN V_CHECKLEVEL(I).APPRAISE_LEVEL = 3 THEN
--同部门员工
E_CHECK_LEVEL3 := V_CHECKLEVEL(I).CHECK_LEVEL_ID;
E_CHECK_NUM3 := V_CHECKLEVEL(I).UPPER_LIMIT_NUM;
WHEN V_CHECKLEVEL(I).APPRAISE_LEVEL = 4 THEN
--合作部门员工
E_CHECK_LEVEL4 := V_CHECKLEVEL(I).CHECK_LEVEL_ID;
E_CHECK_NUM4 := V_CHECKLEVEL(I).UPPER_LIMIT_NUM;
END CASE;
END IF;
END LOOP;
--遍历问卷表，获得不同级别员工对应的问卷ID
FOR I IN 1 .. V_QUESTION.COUNT LOOP
--主管
IF V_QUESTION(I).EMP_LEVEL = 0 THEN
D_QUESTION := V_QUESTION(I).QID;
END IF;
--普通员工
IF V_QUESTION(I).EMP_LEVEL = 1 THEN
E_QUESTION := V_QUESTION(I).QID;
END IF;
END LOOP;
--把数据插入临时表中
------RTX和邮件的联系次数之和，插入临时表-----------
INSERT INTO W_ECM_IM_LOG
SELECT A.IM_FROM_SENDER, A.PRAISE, SUM(NUM) AS NUM
FROM (SELECT T.IM_FROM_SENDER,
REGEXP_SUBSTR(IM_TO_RECEIVERS, '[^,]+', 1, L) AS PRAISE,
SUM(NUM) AS NUM
FROM (SELECT T.IM_FROM_SENDER,
T.IM_TO_RECEIVERS,
COUNT(*) AS NUM
FROM T_ECM_IM_LOG T
GROUP BY T.IM_FROM_SENDER, T.IM_TO_RECEIVERS) T,
(SELECT LEVEL L FROM DUAL CONNECT BY LEVEL <= 100)
WHERE L <= LENGTH(IM_TO_RECEIVERS) -
LENGTH(REPLACE(IM_TO_RECEIVERS, ',')) + 1
GROUP BY IM_FROM_SENDER,
REGEXP_SUBSTR(IM_TO_RECEIVERS, '[^,]+', 1, L)
UNION ALL
SELECT U1.DOMAIN_USER AS IM_FROM_SENDER,
U2.DOMAIN_USER AS PRAISE,
A.NUM
FROM (SELECT T.MAIL_FROM_SENDERS,
T.MAIL_TO_RECEIVERS,
COUNT(*) AS NUM
FROM T_ECM_MAIL_LOG T
GROUP BY T.MAIL_FROM_SENDERS, T.MAIL_TO_RECEIVERS) A,
M_USERS U1,
M_USERS U2
WHERE A.MAIL_FROM_SENDERS = U1.REG_IP
AND A.MAIL_TO_RECEIVERS = U2.REG_IP
AND U1.DOMAIN_USER IS NOT NULL
AND U2.DOMAIN_USER IS NOT NULL) A
GROUP BY IM_FROM_SENDER, PRAISE;
--------------------------------开始遍历员工-----------------------------------------------
--遍历所有参与考核的员工
FOR I IN 1 .. V_EXTEND.COUNT LOOP
KPI_USER_ID := V_EXTEND(I).USER_ID; --当前遍历的员工
------主管--------------------
IF V_EXTEND(I).EMP_LEVEL = 0 THEN
--查找直接主管
SELECT O.MANAGER_IDS BULK COLLECT
INTO V_DIRECTOR
FROM M_ORGANIZATION O
WHERE O.MANAGER_IDS IS NOT NULL
START WITH O.ORG_ID IN (SELECT ORG_ID
FROM M_EMPLOYEE E
WHERE E.USER_ID = KPI_USER_ID)
CONNECT BY ORG_ID = PRIOR PARENT_ID
ORDER BY O.TYPE DESC;
IF V_DIRECTOR.COUNT >= 2 THEN
KPI_DIRECTOR := V_DIRECTOR(2);
SELECT A.* BULK COLLECT
INTO V_RTXCONTACT
FROM (SELECT U1.USER_ID AS ACCEPT, U2.USER_ID AS PRAISE, T.NUM
FROM W_ECM_IM_LOG T, M_USERS U1, M_USERS U2
WHERE T.ACCEPT = U1.DOMAIN_USER
AND T.PRAISE = U2.DOMAIN_USER
AND U1.USER_ID = KPI_USER_ID
AND ',' || KPI_DIRECTOR || ',' LIKE
'%,' || U2.USER_ID || ',%'
ORDER BY NUM DESC) A
WHERE ROWNUM <= D_CHECK_NUM1; --组织结构多负责人
FOR J IN 1 .. V_RTXCONTACT.COUNT LOOP
INSERT INTO T_360CHECK_ANSWER_TMP
(CA_ID,
ACCEPT_APPRAISE_USER,
APPRAISE_USER,
CHECK_LEVEL_ID,
QID,
STATUS,
CONTACT_TIMES)
VALUES
(OA_SEQ_BASE.NEXTVAL,
KPI_USER_ID,
V_RTXCONTACT(J).PRAISE,
D_CHECK_LEVEL1,
D_QUESTION,
0,
V_RTXCONTACT(J).NUM);
END LOOP;
END IF;
----------查找直接下属-------------
KPI_SUBORDINATE := '';
--该游标查询自己下级小组负责人，所以查询组长的直接下属时为空
OPEN CURSOR_1(KPI_USER_ID);
FETCH CURSOR_1
INTO KPI_SUBORDINATE;
--如果游标返回结果为空，则当前遍历员工是组长，查询自己相同小组下的员工作为自己的直接下属
IF CURSOR_1%NOTFOUND THEN
SELECT A.* BULK COLLECT
INTO V_RTXCONTACT
FROM (SELECT U1.USER_ID AS ACCEPT, U2.USER_ID AS PRAISE, T.NUM
FROM W_ECM_IM_LOG T, M_USERS U1, M_USERS U2, M_EMPLOYEE E
WHERE T.ACCEPT = U1.DOMAIN_USER
AND T.PRAISE = U2.DOMAIN_USER
AND E.ORG_ID IN
(SELECT ORG_ID
FROM M_EMPLOYEE
WHERE USER_ID = KPI_USER_ID)
AND U1.USER_ID = KPI_USER_ID
AND E.USER_ID = U2.USER_ID
ORDER BY T.NUM DESC) A
WHERE ROWNUM <= D_CHECK_NUM2;
FOR J IN 1 .. V_RTXCONTACT.COUNT LOOP
--直接下属拼接成字符串，查询同部门员工时，这部分员工不包括在内
KPI_SUBORDINATE := KPI_SUBORDINATE || ',' || V_RTXCONTACT(J)
.PRAISE;
INSERT INTO T_360CHECK_ANSWER_TMP
(CA_ID,
ACCEPT_APPRAISE_USER,
APPRAISE_USER,
CHECK_LEVEL_ID,
QID,
STATUS,
CONTACT_TIMES)
VALUES
(OA_SEQ_BASE.NEXTVAL,
KPI_USER_ID,
V_RTXCONTACT(J).PRAISE,
D_CHECK_LEVEL2,
D_QUESTION,
0,
V_RTXCONTACT(J).NUM);
END LOOP;
ELSE
LOOP
FETCH CURSOR_1
INTO KPI_VARIABLE;
KPI_SUBORDINATE := KPI_SUBORDINATE || ',' || KPI_VARIABLE;
EXIT WHEN CURSOR_1%NOTFOUND;
END LOOP;
KPI_SUBORDINATE := KPI_SUBORDINATE || ',';
SELECT A.* BULK COLLECT
INTO V_RTXCONTACT
FROM (SELECT U1.USER_ID AS ACCEPT, U2.USER_ID AS PRAISE, T.NUM
FROM W_ECM_IM_LOG T, M_USERS U1, M_USERS U2
WHERE T.ACCEPT = U1.DOMAIN_USER
AND T.PRAISE = U2.DOMAIN_USER
AND U1.USER_ID = KPI_USER_ID
AND ',' || KPI_SUBORDINATE || ',' LIKE
'%,' || U2.USER_ID || ',%'
ORDER BY T.NUM DESC) A
WHERE ROWNUM <= D_CHECK_NUM2;
FOR J IN 1 .. V_RTXCONTACT.COUNT LOOP
INSERT INTO T_360CHECK_ANSWER_TMP
(CA_ID,
ACCEPT_APPRAISE_USER,
APPRAISE_USER,
CHECK_LEVEL_ID,
QID,
STATUS,
CONTACT_TIMES)
VALUES
(OA_SEQ_BASE.NEXTVAL,
KPI_USER_ID,
V_RTXCONTACT(J).PRAISE,
D_CHECK_LEVEL2,
D_QUESTION,
0,
V_RTXCONTACT(J).NUM);
END LOOP;
END IF;
CLOSE CURSOR_1;
---------------------查找同部门同事--------------------
SELECT A.* BULK COLLECT
INTO V_RTXCONTACT
FROM (
SELECT U1.USER_ID AS ACCEPT, U2.USER_ID AS PRAISE, T.NUM
FROM W_ECM_IM_LOG T, M_USERS U1, M_USERS U2, M_EMPLOYEE E1
WHERE T.ACCEPT = U1.DOMAIN_USER
AND T.PRAISE = U2.DOMAIN_USER
AND U2.USER_ID = E1.USER_ID
AND U1.USER_ID = KPI_USER_ID
AND E1.DEPT_ID IN
(SELECT DEPT_ID
FROM M_EMPLOYEE
WHERE USER_ID = KPI_USER_ID)
AND ',' || KPI_DIRECTOR || ',' NOT LIKE --除去直接主管
',%' || E1.USER_ID || ',%'
AND ',' || KPI_SUBORDINATE || ',' NOT LIKE --除去直接下属
',%' || E1.USER_ID || ',%'
ORDER BY T.NUM DESC) A
WHERE ROWNUM <= D_CHECK_NUM3;
IF V_DIRECTOR.COUNT >= 1 THEN
FOR J IN 1 .. V_RTXCONTACT.COUNT LOOP
INSERT INTO T_360CHECK_ANSWER_TMP
(CA_ID,
ACCEPT_APPRAISE_USER,
APPRAISE_USER,
CHECK_LEVEL_ID,
QID,
STATUS,
CONTACT_TIMES)
VALUES
(OA_SEQ_BASE.NEXTVAL,
KPI_USER_ID,
V_RTXCONTACT(J).PRAISE,
D_CHECK_LEVEL3,
D_QUESTION,
0,
V_RTXCONTACT(J).NUM);
END LOOP;
END IF;
----------------------查找不同部门同事------------------
SELECT A.* BULK COLLECT
INTO V_RTXCONTACT
FROM (
SELECT U1.USER_ID AS ACCEPT, U2.USER_ID AS PRAISE, T.NUM
FROM W_ECM_IM_LOG T, M_USERS U1, M_USERS U2, M_EMPLOYEE E1
WHERE T.ACCEPT = U1.DOMAIN_USER
AND T.PRAISE = U2.DOMAIN_USER
AND U2.USER_ID = E1.USER_ID
AND U1.USER_ID = KPI_USER_ID
AND E1.DEPT_ID NOT IN
(SELECT DEPT_ID
FROM M_EMPLOYEE
WHERE USER_ID = KPI_USER_ID)
AND ',' || KPI_DIRECTOR || ',' NOT LIKE
',%' || E1.USER_ID || ',%'
AND ',' || KPI_SUBORDINATE || ',' NOT LIKE
',%' || E1.USER_ID || ',%'
ORDER BY T.NUM DESC) A
WHERE ROWNUM <= D_CHECK_NUM4;
IF V_DIRECTOR.COUNT >= 1 THEN
FOR J IN 1 .. V_RTXCONTACT.COUNT LOOP
INSERT INTO T_360CHECK_ANSWER_TMP
(CA_ID,
ACCEPT_APPRAISE_USER,
APPRAISE_USER,
CHECK_LEVEL_ID,
QID,
STATUS,
CONTACT_TIMES)
VALUES
(OA_SEQ_BASE.NEXTVAL,
KPI_USER_ID,
V_RTXCONTACT(J).PRAISE,
D_CHECK_LEVEL4,
D_QUESTION,
0,
V_RTXCONTACT(J).NUM);
END LOOP;
END IF;
END IF;
----------------普通员工---------------
IF V_EXTEND(I).EMP_LEVEL = '1' THEN
--递归查询，既包括直接主管，又包括部门主管
SELECT O.MANAGER_IDS BULK COLLECT
INTO V_DIRECTOR
FROM M_ORGANIZATION O
WHERE O.MANAGER_IDS IS NOT NULL
START WITH O.ORG_ID IN (SELECT ORG_ID
FROM M_EMPLOYEE E
WHERE E.USER_ID = KPI_USER_ID)
CONNECT BY ORG_ID = PRIOR PARENT_ID
ORDER BY O.TYPE DESC;
---------直接主管-------
IF V_DIRECTOR.COUNT >= 1 THEN
KPI_DIRECTOR := V_DIRECTOR(1);
--和直接主管之间RTX联系次数
SELECT A.* BULK COLLECT
INTO V_RTXCONTACT
FROM (SELECT U1.USER_ID AS ACCEPT, U2.USER_ID AS PRAISE, T.NUM
FROM W_ECM_IM_LOG T, M_USERS U1, M_USERS U2
WHERE T.ACCEPT = U1.DOMAIN_USER
AND T.PRAISE = U2.DOMAIN_USER
AND U1.USER_ID = KPI_USER_ID
AND ',' || KPI_DIRECTOR || ',' LIKE
'%,' || U2.USER_ID || ',%'
ORDER BY NUM DESC) A
WHERE ROWNUM <= E_CHECK_NUM1; --组织结构多负责人
FOR J IN 1 .. V_RTXCONTACT.COUNT LOOP
INSERT INTO T_360CHECK_ANSWER_TMP
(CA_ID,
ACCEPT_APPRAISE_USER,
APPRAISE_USER,
CHECK_LEVEL_ID,
QID,
STATUS,
CONTACT_TIMES)
VALUES
(OA_SEQ_BASE.NEXTVAL,
KPI_USER_ID,
V_RTXCONTACT(J).PRAISE,
E_CHECK_LEVEL1,
E_QUESTION,
0,
V_RTXCONTACT(J).NUM);
END LOOP;
END IF;
------部门主管-----
IF V_DIRECTOR.COUNT >= 2 THEN
KPI_DEPT_MANAGER := V_DIRECTOR(2);
--和部门主管之间RTX联系次数
SELECT A.* BULK COLLECT
INTO V_RTXCONTACT
FROM (SELECT U1.USER_ID AS ACCEPT, U2.USER_ID AS PRAISE, T.NUM
FROM W_ECM_IM_LOG T, M_USERS U1, M_USERS U2
WHERE T.ACCEPT = U1.DOMAIN_USER
AND T.PRAISE = U2.DOMAIN_USER
AND U1.USER_ID = KPI_USER_ID
AND ',' || KPI_DEPT_MANAGER || ',' LIKE
'%,' || U2.USER_ID || ',%'
ORDER BY NUM DESC) A
WHERE ROWNUM <= E_CHECK_LEVEL2; --组织结构多负责人
FOR J IN 1 .. V_RTXCONTACT.COUNT LOOP
INSERT INTO T_360CHECK_ANSWER_TMP
(CA_ID,
ACCEPT_APPRAISE_USER,
APPRAISE_USER,
CHECK_LEVEL_ID,
QID,
STATUS,
CONTACT_TIMES)
VALUES
(OA_SEQ_BASE.NEXTVAL,
KPI_USER_ID,
V_RTXCONTACT(J).PRAISE,
E_CHECK_LEVEL2,
E_QUESTION,
0,
V_RTXCONTACT(J).NUM);
END LOOP;
END IF;
-------------部门内同事-------
SELECT A.* BULK COLLECT
INTO V_RTXCONTACT
FROM (
SELECT U1.USER_ID AS ACCEPT, U2.USER_ID AS PRAISE, T.NUM
FROM W_ECM_IM_LOG T, M_USERS U1, M_USERS U2, M_EMPLOYEE E1
WHERE T.ACCEPT = U1.DOMAIN_USER
AND T.PRAISE = U2.DOMAIN_USER
AND U2.USER_ID = E1.USER_ID
AND U1.USER_ID = KPI_USER_ID
AND E1.DEPT_ID IN
(SELECT DEPT_ID
FROM M_EMPLOYEE
WHERE USER_ID = KPI_USER_ID)
AND ',' || KPI_DIRECTOR || ',' NOT LIKE
',%' || E1.USER_ID || ',%'
AND ',' || KPI_DEPT_MANAGER || ',' NOT LIKE
',%' || E1.USER_ID || ',%'
ORDER BY T.NUM DESC) A
WHERE ROWNUM <= E_CHECK_NUM3;
IF V_DIRECTOR.COUNT >= 1 THEN
FOR J IN 1 .. V_RTXCONTACT.COUNT LOOP
INSERT INTO T_360CHECK_ANSWER_TMP
(CA_ID,
ACCEPT_APPRAISE_USER,
APPRAISE_USER,
CHECK_LEVEL_ID,
QID,
STATUS,
CONTACT_TIMES)
VALUES
(OA_SEQ_BASE.NEXTVAL,
KPI_USER_ID,
V_RTXCONTACT(J).PRAISE,
E_CHECK_LEVEL3,
E_QUESTION,
0,
V_RTXCONTACT(J).NUM);
END LOOP;
END IF;
-------------部门间同事-------
SELECT A.* BULK COLLECT
INTO V_RTXCONTACT
FROM (
SELECT U1.USER_ID AS ACCEPT, U2.USER_ID AS PRAISE, T.NUM
FROM W_ECM_IM_LOG T, M_USERS U1, M_USERS U2, M_EMPLOYEE E1
WHERE T.ACCEPT = U1.DOMAIN_USER
AND T.PRAISE = U2.DOMAIN_USER
AND U2.USER_ID = E1.USER_ID
AND U1.USER_ID = KPI_USER_ID
AND E1.DEPT_ID NOT IN
(SELECT DEPT_ID
FROM M_EMPLOYEE
WHERE USER_ID = KPI_USER_ID)
AND ',' || KPI_DIRECTOR || ',' NOT LIKE
',%' || E1.USER_ID || ',%'
AND ',' || KPI_DEPT_MANAGER || ',' NOT LIKE
',%' || E1.USER_ID || ',%'
ORDER BY T.NUM DESC) A
WHERE ROWNUM <= E_CHECK_NUM4;
IF V_DIRECTOR.COUNT >= 1 THEN
FOR J IN 1 .. V_RTXCONTACT.COUNT LOOP
INSERT INTO T_360CHECK_ANSWER_TMP
(CA_ID,
ACCEPT_APPRAISE_USER,
APPRAISE_USER,
CHECK_LEVEL_ID,
QID,
STATUS,
CONTACT_TIMES)
VALUES
(OA_SEQ_BASE.NEXTVAL,
KPI_USER_ID,
V_RTXCONTACT(J).PRAISE,
E_CHECK_LEVEL4,
E_QUESTION,
0,
V_RTXCONTACT(J).NUM);
END LOOP;
END IF;
END IF;
END LOOP;
--------------------RTX和邮件统计结束，记录日志----------------------------
WRITE_LOG('PROCEDURE:TEST3',
'END',
'存储过程TEST3开始执行：统计员工RTX及邮件联系人，生成绩效考核答卷表------执行结束');
COMMIT;
EXCEPTION
--捕获所有的异常（类似JAVA中的Exception）
WHEN OTHERS THEN
ROLLBACK;
--插入异常日志(方法必须有返回值，这里调用另一个存储过程，记录日志)
WRITE_LOG('PROCEDURE:TEST3',
'ERROR',
SUBSTR(SQLERRM, 0, 3000) ||
SUBSTR(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, 0, 1000));
END PRC_STATISTICS_RTX;
分享到：
发表评论
相关推荐
本文实例讲述了Python使用cx_Oracle调用Oracle存储过程的方法。分享给大家供大家参考，具体如下： 这里主要测试在Python中通过cx_Oracle调用PL/SQL。 首先，在数据库端创建简单的存储过程。 create or replace ...
oracle 存储过程实例 oracle存储过程实例
oracle 存储过程导出excel oracle 存储过程导出excel oracle 存储过程导出excel oracle 存储过程导出excel oracle 存储过程导出excel
oracle 存储过程 unwrap 图形解密 工具
可以将SQL Server存储过程转为oracle存储过程的工具
ibatis调用oracle存储过程分页
帆软报表调用Oracle存储过程如果存储过程定义中没有参数，但是设计器中缺弹出一个storeParameter1参数的解决插件
4、开发指南：《Oracle存储过程入门指南&100+种真实业务场景存储过程实例.pdf》，可以帮助初学者系统学习。 5、资源包括“通用存储过程整理” 和 “真实业务存储过程整理” 两部分，通用适合各种系统，可以无缝隙...
oracle存储过程加密破解 0
使用java实现oracle存储过程。 共有3个小例子。实现的功能 1、无返回值的存储过程 如 insert 2、有返回值的存储过程（非列表）select id from tab 3、返回列表 如：select * from tab 顺便鄙视下csdn，作为一个it...
oracle存储过程学习经典入门 非常好的 初学者必看
oracle 存储过程 函数 dblink 绝对对工作和平时学习有价值的资料。针对个人具体情况做修改即可使用
Oracle存储过程中使用临时表 会话级临时表 事务级临时表
oracle 存储过程批量提交
oracle存储过程笔记oracle存储过程笔记oracle存储过程笔记oracle存储过程笔记oracle存储过程笔记oracle存储过程笔记oracle存储过程笔记oracle存储过程笔记oracle存储过程笔记oracle存储过程笔记
行业内Oracle存储过程最基本的开法规范，适合oracle入门小白学习
oracle 存储过程实例 oracle 存储过程实例 oracle 存储过程实例 oracle 存储过程实例
linux系统中使用shell脚本对oracle存储过程进行编译
oracle存储过程+日期+定时任务Job oracle存储过程+日期+定时任务Job oracle存储过程+日期+定时任务Job