游标 工作记录
来源：互联网 发布：plsql 执行sql文件 编辑：程序博客网 时间：2024/06/02 20:39
/* Formatted on 2010/12/27 上午 09:18:24 (QP5 v5.115.810.9015) */
CREATE OR REPLACE PROCEDURE ESS.ESS_total
IS
DT DATE DEFAULT TO_DATE ('200911', 'YYYYMM') ;
CURSOR STATISTICS_CURSOR (
V_DATE DATE
)
IS
SELECT DISTINCT
GRADE5_DEPTNAME,
GRADE5_DEPTNO,
AVG_EMPS_D,
AVG_EMFS_FOLDING_D,
AVG_EMPS_I,
AVG_EMPS_COUNT,
AMOUNT,
APPROVED_AMOUNT,
SUG_AMOUNT,
CIP_TO_ESS_AMOUNT,
CIP_AMOUNT,
EPATENT_AMOUNT,
SIGMA_AMOUNT,
FOLDING_AMOUNT,
ROUND (CIP_TO_ESS_FOLDING_AMOUNT / AVG_EMPS_COUNT, 4)
AS CIP_TO_ESS_AVG_FOLDING_AMOUNT,
ROUND (SUG_FOLDING_AMOUNT / AVG_EMPS_COUNT, 4)
AS AVG_SUG_FOLDING_AMOUNT,
OTH_FOLDING_AMOUNT,
ROUND (OTH_FOLDING_AMOUNT / AVG_EMPS_COUNT, 4)
AS OTH_AVG_FOLDING_AMOUNT,
DECODE (SCORE_SUGGESTION_AMOUNT,
0, 0,
ROUND (SCORE_SUGGESTION / SCORE_SUGGESTION_AMOUNT, 4))
AS AVG_SCORE_SUGGESTION,
DECODE (
SCORE_PROCESS_EXEC_AMOUNT,
0,
0,
ROUND (SCORE_PROCESS_EXEC / SCORE_PROCESS_EXEC_AMOUNT, 4)
)
AS AVG_SCORE_PROCESS,
ROUND (DECODE (AMOUNT, 0, 0, APPROVED_AMOUNT / AMOUNT), 4)
AS EXECUTED_RATE,
BENEFIT,
ROUND (
DECODE (
SUG_AMOUNT,
0,
0,
(SUG_FOLDING_AMOUNT - CIP_TO_ESS_FOLDING_AMOUNT)
/ AVG_EMPS_COUNT
* SCORE_SUGGESTION
/ SCORE_SUGGESTION_AMOUNT
+ ROUND (CIP_TO_ESS_FOLDING_AMOUNT / AVG_EMPS_COUNT, 4)
+ DECODE (
SCORE_PROCESS_EXEC_AMOUNT,
0,
0,
FOLDING_AMOUNT_EXEC
/ AVG_EMPS_COUNT
* SCORE_PROCESS_EXEC
/ SCORE_PROCESS_EXEC_AMOUNT
* 0.2
)
),
6
)
AS THREE_SUGGESTION_ATTR_SCORE,
ROUND (FOLDING_AMOUNT_EXEC / AVG_EMPS_COUNT, 4)
AS AVG_FOLDING_AMOUNT_EXEC,
SCORE_PROCESS_EXEC,
SCORE_PROCESS_EXEC_AMOUNT,
YYYMM,
YYYYQ,
YYYMMB,
YYYYQB
FROM (SELECT D.DEPTNO AS GRADE5_DEPTNO,
D.DEPTNAME AS GRADE5_DEPTNAME,
NVL (A.AMOUNT, 0) AS AMOUNT,
NVL (A.SUG_AMOUNT, 0) AS SUG_AMOUNT,
CIP_AMOUNT,
EPATENT_AMOUNT,
SIGMA_AMOUNT,
NVL (A.CIP_TO_ESS_AMOUNT, 0) AS CIP_TO_ESS_AMOUNT,
NVL (A.CIP_TO_ESS_FOLDING_AMOUNT, 0)
AS CIP_TO_ESS_FOLDING_AMOUNT,
NVL (A.SUG_FOLDING_AMOUNT, 0) AS SUG_FOLDING_AMOUNT,
NVL (A.OTH_FOLDING_AMOUNT, 0) AS OTH_FOLDING_AMOUNT,
NVL (A.APPROVED_AMOUNT, 0) AS APPROVED_AMOUNT,
NVL (SCORE_SUGGESTION_AMOUNT, 0)
AS SCORE_SUGGESTION_AMOUNT,
NVL (A.FOLDING_AMOUNT_EXEC, 0) FOLDING_AMOUNT_EXEC,
NVL (A.SCORE_PROCESS_EXEC, 0) AS SCORE_PROCESS_EXEC,
NVL (SCORE_PROCESS_EXEC_AMOUNT, 0)
AS SCORE_PROCESS_EXEC_AMOUNT,
NVL (A.FOLDING_AMOUNT, 0) AS FOLDING_AMOUNT,
NVL (A.SCORE_SUGGESTION, 0) AS SCORE_SUGGESTION,
NVL (A.BENEFIT, 0) AS BENEFIT,
AVG_EMPS_D,
AVG_EMFS_FOLDING_D,
AVG_EMPS_I,
AVG_EMPS_COUNT,
RANK () OVER (ORDER BY NVL (BENEFIT, 0) DESC)
BENEFIT_RANK,
YYYMM,
YYYYQ,
YYYMMB,
YYYYQB
FROM ( SELECT GRADE5_DEPTNO,
ROUND (SUM (AMOUNT), 2) AMOUNT,
ROUND(SUM(DECODE (AP,
'SUGGESTION',
APPROVED_AMOUNT,
0)))
AS SUG_AMOUNT,
ROUND (
SUM(DECODE (AP,
'SUGGESTION',
FOLDING_AMOUNT,
0)),
2
)
AS SUG_FOLDING_AMOUNT,
ROUND (
SUM(DECODE (STATUS,
'S', APPROVED_AMOUNT,
0)),
2
)
AS CIP_TO_ESS_AMOUNT,
ROUND (
SUM(DECODE (STATUS,
'S', FOLDING_AMOUNT,
0)),
2
)
AS CIP_TO_ESS_FOLDING_AMOUNT,
ROUND(SUM(DECODE (AP,
'CIP', APPROVED_AMOUNT,
0)))
AS CIP_AMOUNT,
ROUND(SUM(DECODE (AP,
'EPATENT',
APPROVED_AMOUNT,
0)))
AS EPATENT_AMOUNT,
ROUND(SUM(DECODE (AP,
'6SIGMA',
APPROVED_AMOUNT,
0)))
AS SIGMA_AMOUNT,
ROUND (
SUM(DECODE (AP,
'SUGGESTION', 0,
'SUG_PROCESS', 0,
FOLDING_AMOUNT)),
2
)
AS OTH_FOLDING_AMOUNT,
ROUND (SUM (APPROVED_AMOUNT), 2)
APPROVED_AMOUNT,
DECODE (SUM (SCORE_SUGGESTION_AMOUNT),
0, 1,
SUM (SCORE_SUGGESTION_AMOUNT))
SCORE_SUGGESTION_AMOUNT,
ROUND (SUM (FOLDING_AMOUNT), 2)
FOLDING_AMOUNT,
SUM (SCORE_SUGGESTION) AS SCORE_SUGGESTION,
ROUND (
SUM(DECODE (AP,
'SUG_PROCESS',
FOLDING_AMOUNT,
0)),
4
)
AS FOLDING_AMOUNT_EXEC,
ROUND (
SUM(DECODE (AP,
'SUG_PROCESS',
SCORE_PROCESS,
0)),
4
)
AS SCORE_PROCESS_EXEC,
ROUND (
SUM(DECODE (AP,
'SUG_PROCESS',
SCORE_PROCESS_AMOUNT,
0)),
4
)
AS SCORE_PROCESS_EXEC_AMOUNT,
ROUND (SUM (BENEFIT), 4) AS BENEFIT
FROM ESS_DEPARTMENT_REPORT R
WHERE EXISTS
(SELECT DEPTNO
FROM ESS_DEPARTMENT_CURRENT_EMPS_V
WHERE DEPTNO = R.GRADE5_DEPTNO
AND EMPS_COUNT > 0
AND YYYMMB =
TO_CHAR (V_DATE,
'YYYYMM'))
AND ESS_DATE <= SYSDATE
AND MONTH = TO_CHAR (V_DATE, 'YYYYMM')
GROUP BY GRADE5_DEPTNO) A,
( SELECT DEPTNO,
MIN (DEPTNAME) AS DEPTNAME,
ROUND (SUM (EMPS_D) / COUNT ( * ), 2)
AVG_EMPS_D,
ROUND (SUM (EMFS_FOLDING_D) / COUNT ( * ),
2)
AVG_EMFS_FOLDING_D,
ROUND (SUM (EMPS_I) / COUNT ( * ), 2)
AVG_EMPS_I,
ROUND (SUM (EMPS_COUNT) / COUNT ( * ), 2)
AS AVG_EMPS_COUNT,
MIN (YYYMM) AS YYYMM,
MIN (YYYYQ) AS YYYYQ,
MIN (YYYMMB) AS YYYMMB,
MIN (YYYYQB) AS YYYYQB
FROM ESS_DEPARTMENT_CURRENT_EMPS_V
WHERE YYYMMB = TO_CHAR (V_DATE, 'YYYYMM')
AND EMPS_COUNT > 0
GROUP BY DEPTNO) D
WHERE A.GRADE5_DEPTNO(+) = D.DEPTNO) X;
BEGIN
LOOP
FOR c IN STATISTICS_CURSOR (DT)
LOOP
INSERT INTO ESS_DATA_STATISTICS (GRADE5_DEPTNAME,
GRADE5_DEPTNO,
AVG_EMPS_D,
AVG_EMFS_FOLDING_D,
AVG_EMPS_I,
AVG_EMPS_COUNT,
AMOUNT,
APPROVED_AMOUNT,
SUG_AMOUNT,
CIP_TO_ESS_AMOUNT,
CIP_AMOUNT,
EPATENT_AMOUNT,
SIGMA_AMOUNT,
FOLDING_AMOUNT,
CIP_TO_ESS_AVG_FOLDING_AMOUNT,
AVG_SUG_FOLDING_AMOUNT,
OTH_FOLDING_AMOUNT,
OTH_AVG_FOLDING_AMOUNT,
AVG_SCORE_SUGGESTION,
AVG_SCORE_PROCESS,
EXECUTED_RATE,
BENEFIT,
THREE_SUGGESTION_ATTR_SCORE,
AVG_FOLDING_AMOUNT_EXEC,
SCORE_PROCESS_EXEC,
SCORE_PROCESS_EXEC_AMOUNT,
YYYMM,
YYYYQ,
YYYMMB,
YYYYQB)
VALUES (c.GRADE5_DEPTNAME,
c.GRADE5_DEPTNO,
c.AVG_EMPS_D,
c.AVG_EMFS_FOLDING_D,
c.AVG_EMPS_I,
c.AVG_EMPS_COUNT,
c.AMOUNT,
c.APPROVED_AMOUNT,
c.SUG_AMOUNT,
c.CIP_TO_ESS_AMOUNT,
c.CIP_AMOUNT,
c.EPATENT_AMOUNT,
c.SIGMA_AMOUNT,
c.FOLDING_AMOUNT,
c.CIP_TO_ESS_AVG_FOLDING_AMOUNT,
c.AVG_SUG_FOLDING_AMOUNT,
c.OTH_FOLDING_AMOUNT,
c.OTH_AVG_FOLDING_AMOUNT,
c.AVG_SCORE_SUGGESTION,
c.AVG_SCORE_PROCESS,
c.EXECUTED_RATE,
c.BENEFIT,
c.THREE_SUGGESTION_ATTR_SCORE,
c.AVG_FOLDING_AMOUNT_EXEC,
c.SCORE_PROCESS_EXEC,
c.SCORE_PROCESS_EXEC_AMOUNT,
c.YYYMM,
c.YYYYQ,
c.YYYMMB,
c.YYYYQB);
END LOOP;
-- CLOSE STATISTICS_CURSOR;
DT := ADD_MONTHS (DT, 1);
IF DT > ADD_MONTHS (SYSDATE, -1)
THEN
EXIT;
END IF;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
WHEN OTHERS
THEN
-- Consider logging the error and then re-raise
RAISE;
END ESS_total;
/
//第二：
CREATE OR REPLACE PROCEDURE ESS_total
IS
V_GRADE5_DEPTNAME VARCHAR2 (500);
V_GRADE5_DEPTNO VARCHAR2 (100);
V_AVG_EMPS_D NUMBER;
V_AVG_EMFS_FOLDING_D NUMBER;
V_AVG_EMPS_I NUMBER;
V_AVG_EMPS_COUNT NUMBER;
V_AMOUNT NUMBER;
V_APPROVED_AMOUNT NUMBER;
V_SUG_AMOUNT NUMBER;
V_CIP_TO_ESS_AMOUNT NUMBER;
V_CIP_AMOUNT NUMBER;
V_EPATENT_AMOUNT NUMBER;
V_SIGMA_AMOUNT NUMBER;
V_FOLDING_AMOUNT NUMBER;
V_CIP_TO_ESS_AVG_FOLDING_AM NUMBER;
V_AVG_SUG_FOLDING_AMOUNT NUMBER;
V_OTH_FOLDING_AMOUNT NUMBER;
V_OTH_AVG_FOLDING_AMOUNT NUMBER;
V_AVG_SCORE_SUGGESTION NUMBER;
V_AVG_SCORE_PROCESS NUMBER;
V_EXECUTED_RATE NUMBER;
V_BENEFIT NUMBER;
V_THREE_SUGGESTION_ATTR_SCORE NUMBER;
V_AVG_FOLDING_AMOUNT_EXEC NUMBER;
V_SCORE_PROCESS_EXEC NUMBER;
V_SCORE_PROCESS_EXEC_AMOUNT NUMBER;
V_YYYMM NUMBER;
V_YYYYQ NUMBER;
V_YYYMMB NUMBER;
V_YYYYQB NUMBER;
DT DATE DEFAULT TO_DATE ('200911', 'YYYYMM') ;
CURSOR STATISTICS_CURSOR(V_DATE DATE)
IS
SELECT DISTINCT
GRADE5_DEPTNAME,
GRADE5_DEPTNO,
AVG_EMPS_D,
AVG_EMFS_FOLDING_D,
AVG_EMPS_I,
AVG_EMPS_COUNT,
AMOUNT,
APPROVED_AMOUNT,
SUG_AMOUNT,
CIP_TO_ESS_AMOUNT,
CIP_AMOUNT,
EPATENT_AMOUNT,
SIGMA_AMOUNT,
FOLDING_AMOUNT,
ROUND (CIP_TO_ESS_FOLDING_AMOUNT / AVG_EMPS_COUNT, 4)
AS CIP_TO_ESS_AVG_FOLDING_AMOUNT,
ROUND (SUG_FOLDING_AMOUNT / AVG_EMPS_COUNT, 4)
AS AVG_SUG_FOLDING_AMOUNT,
OTH_FOLDING_AMOUNT,
ROUND (OTH_FOLDING_AMOUNT / AVG_EMPS_COUNT, 4)
AS OTH_AVG_FOLDING_AMOUNT,
DECODE (SCORE_SUGGESTION_AMOUNT,
0, 0,
ROUND (SCORE_SUGGESTION / SCORE_SUGGESTION_AMOUNT, 4))
AS AVG_SCORE_SUGGESTION,
DECODE (
SCORE_PROCESS_EXEC_AMOUNT,
0,
0,
ROUND (SCORE_PROCESS_EXEC / SCORE_PROCESS_EXEC_AMOUNT, 4)
)
AS AVG_SCORE_PROCESS,
ROUND (DECODE (AMOUNT, 0, 0, APPROVED_AMOUNT / AMOUNT), 4)
AS EXECUTED_RATE,
BENEFIT,
ROUND (
DECODE (
SUG_AMOUNT,
0,
0,
(SUG_FOLDING_AMOUNT - CIP_TO_ESS_FOLDING_AMOUNT)
/ AVG_EMPS_COUNT
* SCORE_SUGGESTION
/ SCORE_SUGGESTION_AMOUNT
+ ROUND (CIP_TO_ESS_FOLDING_AMOUNT / AVG_EMPS_COUNT, 4)
+ DECODE (
SCORE_PROCESS_EXEC_AMOUNT,
0,
0,
FOLDING_AMOUNT_EXEC
/ AVG_EMPS_COUNT
* SCORE_PROCESS_EXEC
/ SCORE_PROCESS_EXEC_AMOUNT
* 0.2
)
),
6
)
AS THREE_SUGGESTION_ATTR_SCORE,
ROUND (FOLDING_AMOUNT_EXEC / AVG_EMPS_COUNT, 4)
AS AVG_FOLDING_AMOUNT_EXEC,
SCORE_PROCESS_EXEC,
SCORE_PROCESS_EXEC_AMOUNT,
YYYMM,
YYYYQ,
YYYMMB,
YYYYQB
FROM (SELECT D.DEPTNO AS GRADE5_DEPTNO,
D.DEPTNAME AS GRADE5_DEPTNAME,
NVL (A.AMOUNT, 0) AS AMOUNT,
NVL (A.SUG_AMOUNT, 0) AS SUG_AMOUNT,
CIP_AMOUNT,
EPATENT_AMOUNT,
SIGMA_AMOUNT,
NVL (A.CIP_TO_ESS_AMOUNT, 0) AS CIP_TO_ESS_AMOUNT,
NVL (A.CIP_TO_ESS_FOLDING_AMOUNT, 0)
AS CIP_TO_ESS_FOLDING_AMOUNT,
NVL (A.SUG_FOLDING_AMOUNT, 0) AS SUG_FOLDING_AMOUNT,
NVL (A.OTH_FOLDING_AMOUNT, 0) AS OTH_FOLDING_AMOUNT,
NVL (A.APPROVED_AMOUNT, 0) AS APPROVED_AMOUNT,
NVL (SCORE_SUGGESTION_AMOUNT, 0)
AS SCORE_SUGGESTION_AMOUNT,
NVL (A.FOLDING_AMOUNT_EXEC, 0) FOLDING_AMOUNT_EXEC,
NVL (A.SCORE_PROCESS_EXEC, 0) AS SCORE_PROCESS_EXEC,
NVL (SCORE_PROCESS_EXEC_AMOUNT, 0)
AS SCORE_PROCESS_EXEC_AMOUNT,
NVL (A.FOLDING_AMOUNT, 0) AS FOLDING_AMOUNT,
NVL (A.SCORE_SUGGESTION, 0) AS SCORE_SUGGESTION,
NVL (A.BENEFIT, 0) AS BENEFIT,
AVG_EMPS_D,
AVG_EMFS_FOLDING_D,
AVG_EMPS_I,
AVG_EMPS_COUNT,
RANK () OVER (ORDER BY NVL (BENEFIT, 0) DESC)
BENEFIT_RANK,
YYYMM,
YYYYQ,
YYYMMB,
YYYYQB
FROM ( SELECT GRADE5_DEPTNO,
ROUND (SUM (AMOUNT), 2) AMOUNT,
ROUND(SUM(DECODE (AP,
'SUGGESTION',
APPROVED_AMOUNT,
0)))
AS SUG_AMOUNT,
ROUND (
SUM(DECODE (AP,
'SUGGESTION',
FOLDING_AMOUNT,
0)),
2
)
AS SUG_FOLDING_AMOUNT,
ROUND (
SUM(DECODE (STATUS,
'S', APPROVED_AMOUNT,
0)),
2
)
AS CIP_TO_ESS_AMOUNT,
ROUND (
SUM(DECODE (STATUS,
'S', FOLDING_AMOUNT,
0)),
2
)
AS CIP_TO_ESS_FOLDING_AMOUNT,
ROUND(SUM(DECODE (AP,
'CIP', APPROVED_AMOUNT,
0)))
AS CIP_AMOUNT,
ROUND(SUM(DECODE (AP,
'EPATENT',
APPROVED_AMOUNT,
0)))
AS EPATENT_AMOUNT,
ROUND(SUM(DECODE (AP,
'6SIGMA',
APPROVED_AMOUNT,
0)))
AS SIGMA_AMOUNT,
ROUND (
SUM(DECODE (AP,
'SUGGESTION', 0,
'SUG_PROCESS', 0,
FOLDING_AMOUNT)),
2
)
AS OTH_FOLDING_AMOUNT,
ROUND (SUM (APPROVED_AMOUNT), 2)
APPROVED_AMOUNT,
DECODE (SUM (SCORE_SUGGESTION_AMOUNT),
0, 1,
SUM (SCORE_SUGGESTION_AMOUNT))
SCORE_SUGGESTION_AMOUNT,
ROUND (SUM (FOLDING_AMOUNT), 2)
FOLDING_AMOUNT,
SUM (SCORE_SUGGESTION) AS SCORE_SUGGESTION,
ROUND (
SUM(DECODE (AP,
'SUG_PROCESS',
FOLDING_AMOUNT,
0)),
4
)
AS FOLDING_AMOUNT_EXEC,
ROUND (
SUM(DECODE (AP,
'SUG_PROCESS',
SCORE_PROCESS,
0)),
4
)
AS SCORE_PROCESS_EXEC,
ROUND (
SUM(DECODE (AP,
'SUG_PROCESS',
SCORE_PROCESS_AMOUNT,
0)),
4
)
AS SCORE_PROCESS_EXEC_AMOUNT,
ROUND (SUM (BENEFIT), 4) AS BENEFIT
FROM ESS_DEPARTMENT_REPORT R
WHERE EXISTS
(SELECT DEPTNO
FROM ESS_DEPARTMENT_CURRENT_EMPS_V
WHERE DEPTNO = R.GRADE5_DEPTNO
AND EMPS_COUNT > 0
AND YYYMMB =
TO_CHAR (V_DATE,
'YYYYMM'))
AND ESS_DATE <= SYSDATE
AND MONTH = TO_CHAR (V_DATE, 'YYYYMM')
GROUP BY GRADE5_DEPTNO) A,
( SELECT DEPTNO,
MIN (DEPTNAME) AS DEPTNAME,
ROUND (SUM (EMPS_D) / COUNT ( * ), 2)
AVG_EMPS_D,
ROUND (SUM (EMFS_FOLDING_D) / COUNT ( * ),
2)
AVG_EMFS_FOLDING_D,
ROUND (SUM (EMPS_I) / COUNT ( * ), 2)
AVG_EMPS_I,
ROUND (SUM (EMPS_COUNT) / COUNT ( * ), 2)
AS AVG_EMPS_COUNT,
MIN (YYYMM) AS YYYMM,
MIN (YYYYQ) AS YYYYQ,
MIN (YYYMMB) AS YYYMMB,
MIN (YYYYQB) AS YYYYQB
FROM ESS_DEPARTMENT_CURRENT_EMPS_V
WHERE YYYMMB = TO_CHAR (V_DATE, 'YYYYMM')
AND EMPS_COUNT > 0
GROUP BY DEPTNO) D
WHERE A.GRADE5_DEPTNO(+) = D.DEPTNO) X;
BEGIN
LOOP
OPEN STATISTICS_CURSOR(DT);
LOOP
FETCH STATISTICS_CURSOR
INTO
V_GRADE5_DEPTNAME, V_GRADE5_DEPTNO, V_AVG_EMPS_D, V_AVG_EMFS_FOLDING_D, V_AVG_EMPS_I, V_AVG_EMPS_COUNT, V_AMOUNT, V_APPROVED_AMOUNT, V_SUG_AMOUNT, V_CIP_TO_ESS_AMOUNT, V_CIP_AMOUNT, V_EPATENT_AMOUNT, V_SIGMA_AMOUNT, V_FOLDING_AMOUNT, V_CIP_TO_ESS_AVG_FOLDING_AM, V_AVG_SUG_FOLDING_AMOUNT, V_OTH_FOLDING_AMOUNT, V_OTH_AVG_FOLDING_AMOUNT, V_AVG_SCORE_SUGGESTION, V_AVG_SCORE_PROCESS, V_EXECUTED_RATE, V_BENEFIT, V_THREE_SUGGESTION_ATTR_SCORE, V_AVG_FOLDING_AMOUNT_EXEC, V_SCORE_PROCESS_EXEC, V_SCORE_PROCESS_EXEC_AMOUNT,V_YYYMM,V_YYYYQ,V_YYYMMB,V_YYYYQB;
EXIT WHEN STATISTICS_CURSOR%NOTFOUND;
INSERT INTO ESS_DATA_STATISTICS (GRADE5_DEPTNAME,
GRADE5_DEPTNO,
AVG_EMPS_D,
AVG_EMFS_FOLDING_D,
AVG_EMPS_I,
AVG_EMPS_COUNT,
AMOUNT,
APPROVED_AMOUNT,
SUG_AMOUNT,
CIP_TO_ESS_AMOUNT,
CIP_AMOUNT,
EPATENT_AMOUNT,
SIGMA_AMOUNT,
FOLDING_AMOUNT,
CIP_TO_ESS_AVG_FOLDING_AMOUNT,
AVG_SUG_FOLDING_AMOUNT,
OTH_FOLDING_AMOUNT,
OTH_AVG_FOLDING_AMOUNT,
AVG_SCORE_SUGGESTION,
AVG_SCORE_PROCESS,
EXECUTED_RATE,
BENEFIT,
THREE_SUGGESTION_ATTR_SCORE,
AVG_FOLDING_AMOUNT_EXEC,
SCORE_PROCESS_EXEC,
SCORE_PROCESS_EXEC_AMOUNT,YYYMM,YYYYQ,YYYMMB,YYYYQB)
VALUES (V_GRADE5_DEPTNAME,
V_GRADE5_DEPTNO,
V_AVG_EMPS_D,
V_AVG_EMFS_FOLDING_D,
V_AVG_EMPS_I,
V_AVG_EMPS_COUNT,
V_AMOUNT,
V_APPROVED_AMOUNT,
V_SUG_AMOUNT,
V_CIP_TO_ESS_AMOUNT,
V_CIP_AMOUNT,
V_EPATENT_AMOUNT,
V_SIGMA_AMOUNT,
V_FOLDING_AMOUNT,
V_CIP_TO_ESS_AVG_FOLDING_AM,
V_AVG_SUG_FOLDING_AMOUNT,
V_OTH_FOLDING_AMOUNT,
V_OTH_AVG_FOLDING_AMOUNT,
V_AVG_SCORE_SUGGESTION,
V_AVG_SCORE_PROCESS,
V_EXECUTED_RATE,
V_BENEFIT,
V_THREE_SUGGESTION_ATTR_SCORE,
V_AVG_FOLDING_AMOUNT_EXEC,
V_SCORE_PROCESS_EXEC,
V_SCORE_PROCESS_EXEC_AMOUNT,V_YYYMM,V_YYYYQ,V_YYYMMB,V_YYYYQB);
END LOOP;
CLOSE STATISTICS_CURSOR;
DT := ADD_MONTHS (DT, 1);
IF DT > ADD_MONTHS (SYSDATE, -1)
THEN
EXIT;
END IF;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
WHEN OTHERS
THEN
-- Consider logging the error and then re-raise
RAISE;
END ESS_total;
/
- 游标 工作记录
- Oracle游标-提取记录、游标循环
- 工作记录
- 工作记录
- 工作记录
- 工作记录
- 工作记录
- 工作记录
- 工作记录
- 工作记录
- 工作记录
- 工作记录
- 工作记录
- 工作记录
- 工作记录
- 工作记录
- 工作记录
- 工作记录
- 转载——JAVA学习提高之－－－－安装多个JDK版本的问题
- C/C++程序编译步骤详解
- linux 2.6.18-20 local exploit
- 投票知识1
- MyEclipse安装svn插件
- 游标 工作记录
- Windows下使用gvim格式化xml文件
- DELPHI 常用虚拟键：VK_
- 英语单词
- 标准BT.656并行数据结构
- Run OS commands via sql injection in web applications (under construction)
- Android单元测试
- 打开win7服务
- Redis (一) 安装
