ORACLE 存储过程动态游标例子

来源：互联网发布：网络知识大赛编辑：程序博客网时间：2024/04/30 00:55

http://blog.csdn.net/dreamthen/article/details/8509177

一、

[html] view plain copy

CREATE OR REPLACE PROCEDURE PRC_WAP_ACTIVEUSERS(RETCODE OUT VARCHAR2)
/***********************************************************
* 功能：WAP指标--活跃用户统计（分批提交）
* 参数：RETCODE（返回编码：0000成功）
* 作者：
* 创建时间：2013-01-16
* 版本：1.0
* 修改人：
* 修改时间：
**********************************************************/
IS
RET_CODE VARCHAR2(6); --错误信息代码
RET_MSG VARCHAR2(200); --错误信息
V_LAST_MONTH VARCHAR2(8); --上个月份
V_FIRST_DAY_MONTH VARCHAR2(10); --上个月第一天
V_END_DAY_MONTH VARCHAR2(10); --上个月第二天
V_COUNT NUMBER; --计数变量
V_COMMITNUM CONSTANT NUMBER :=1000000;--一次提交记录数（默认一百万）
BEGIN
--程序开始
DBMS_OUTPUT.PUT_LINE('PRC_WAP_ACTIVEUSERS BEGIN : ' ||
TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
RET_CODE := '0000'; --返回编码初始化
V_COUNT := 0; --计数器初始化
--申明游标
DECLARE
TYPE CUR_DATA_TYPE IS RECORD --定义动态游标数据类型
(
PHONE_NUM VARCHAR2(20), --电话号码
LOGIN_TIMES VARCHAR2(2000)); --访问次数
VRECORD CUR_DATA_TYPE; --定义数据类型
BEGIN
SELECT TO_CHAR(ADD_MONTHS(LAST_DAY(SYSDATE), -1), 'YYYYMM')
INTO V_LAST_MONTH
FROM DUAL;
SELECT TO_CHAR(ADD_MONTHS(LAST_DAY(SYSDATE) + 1, -2), 'YYYYMMDD')
INTO V_FIRST_DAY_MONTH
FROM DUAL;
SELECT TO_CHAR(ADD_MONTHS(LAST_DAY(SYSDATE), -1), 'YYYYMMDD')
INTO V_END_DAY_MONTH
FROM DUAL;
FOR VRECORD IN (SELECT TEL AS PHONE_NUM, COUNT(T.TEL) AS LOGIN_TIMES
FROM TEMP_BOSS_BIP1A021 T
WHERE T.DAY_ID BETWEEN V_FIRST_DAY_MONTH AND
V_END_DAY_MONTH HAVING COUNT(T.TEL) >= 2
GROUP BY T.TEL) LOOP
INSERT INTO T_BIP1A021
(PHONE_NUM, LOGIN_TIMES, LOGIN_MONTH)
VALUES
(VRECORD.PHONE_NUM, VRECORD.LOGIN_TIMES, V_LAST_MONTH);
IF ((MOD(V_COUNT, V_COMMITNUM)) = 0) THEN
COMMIT; --求余：一百万提交一次
END IF;
V_COUNT := V_COUNT + 1;
END LOOP;
COMMIT;
DBMS_OUTPUT.PUT_LINE('PRC_WAP_ACTIVEUSERS END: ' ||
TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
EXCEPTION
WHEN OTHERS THEN
BEGIN
ROLLBACK;
RET_CODE := '0001';
RET_MSG := '存储过程 PRC_WAP_ACTIVEUSERS 执行错误!' || CHR(10) ||
'错误代码：' || SQLCODE || CHR(10) || '错误信息：' ||
SUBSTR(SQLERRM, 1, 128);
GOTO TOEND;
END;
END;
--返回程序运作结果
<<TOEND>>
RETCODE := RET_CODE;
IF (RET_CODE = '0000') THEN
RET_MSG := '运行成功!';
COMMIT;
ELSE
RET_MSG := '运行失败!' || RET_MSG;
ROLLBACK;
END IF;
DBMS_OUTPUT.PUT_LINE(RET_MSG);
DBMS_OUTPUT.PUT_LINE('END TIME : ' ||
TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
END PRC_WAP_ACTIVEUSERS;

二、

[sql] view plain copy

CREATE OR REPLACE PROCEDURE PRC_WAP_ACTIVEUSERS_BATCH(RETCODE OUT VARCHAR2,
V_TABLE IN VARCHAR2)
/***********************************************************
* 功能：WAP指标--活跃用户统计（分批提交） *
* 参数：RETCODE（返回编码），V_TABLE（插入表） *
* 作者：ZENGMS *
* 创建时间：2013-01-16 *
* 版本：1.0 *
* 修改人： *
* 修改时间： *
**********************************************************/
IS
RET_CODE VARCHAR2(6); --错误信息代码
RET_MSG VARCHAR2(200); --错误信息
EXEC_SQL VARCHAR2(2000); --SQL语句
V_LAST_MONTH VARCHAR2(8); --上个月份
V_FIRST_DAY_MONTH VARCHAR2(10); --上个月第一天
V_END_DAY_MONTH VARCHAR2(10); --上个月第二天
V_TOTALNUM NUMBER; --总记录数
V_COMMITNUM NUMBER; --一次提交数量
V_RUNTIME NUMBER; --运行次数
V_NUM NUMBER; --循环变量
BEGIN
--程序开始
DBMS_OUTPUT.PUT_LINE('PRC_WAP_ACTIVEUSERS_BATCH BEGIN : ' ||
TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
RET_CODE := '0000';
V_COMMITNUM := 1000000; --一百万提交一次
BEGIN
SELECT TO_CHAR(ADD_MONTHS(LAST_DAY(SYSDATE), -1), 'YYYYMM')
INTO V_LAST_MONTH
FROM DUAL;
SELECT TO_CHAR(ADD_MONTHS(LAST_DAY(SYSDATE) + 1, -2), 'YYYYMMDD')
INTO V_FIRST_DAY_MONTH
FROM DUAL;
SELECT TO_CHAR(ADD_MONTHS(LAST_DAY(SYSDATE), -1), 'YYYYMMDD')
INTO V_END_DAY_MONTH
FROM DUAL;
EXEC_SQL := 'SELECT COUNT(1) FROM(SELECT TEL AS PHONE_NUM, COUNT(T.TEL)
AS LOGIN_TIMES,''' || V_LAST_MONTH ||
''' AS LOGIN_MONTH
FROM TEMP_BOSS_BIP1A021 T
WHERE T.DAY_ID BETWEEN ''' ||
V_FIRST_DAY_MONTH || ''' AND ''' || V_END_DAY_MONTH ||
''' HAVING COUNT(T.TEL) >= 2 GROUP BY T.TEL)';
EXECUTE IMMEDIATE EXEC_SQL INTO V_TOTALNUM; --获取总记录数
--计算循环次数
V_RUNTIME := V_TOTALNUM MOD V_COMMITNUM;
IF (V_RUNTIME > 0) THEN
V_RUNTIME := 1 + TRUNC(V_TOTALNUM / V_COMMITNUM);
END IF;
IF (V_RUNTIME = 0) THEN
V_RUNTIME := 0 + TRUNC(V_TOTALNUM / V_COMMITNUM);
END IF;
--分批提交数据库
FOR V_NUM IN 1 .. V_RUNTIME LOOP
EXEC_SQL := 'INSERT INTO ' || V_TABLE || '
(PHONE_NUM, LOGIN_TIMES, LOGIN_MONTH, ROWNUMS)
SELECT PHONE_NUM, LOGIN_TIMES, LOGIN_MONTH, ROWNUM AS ROWNUMS
FROM (SELECT TEL AS PHONE_NUM,
COUNT(T.TEL) AS LOGIN_TIMES,''' ||
V_LAST_MONTH ||
''' AS LOGIN_MONTH
FROM TEMP_BOSS_BIP1A021 T
WHERE T.DAY_ID BETWEEN ''' ||
V_FIRST_DAY_MONTH || ''' AND ''' || V_END_DAY_MONTH ||
''' HAVING
COUNT(T.TEL) >= 2
GROUP BY T.TEL) WHERE ROWNUM>' ||
(V_NUM - 1) * V_COMMITNUM || ' AND ROWNUM <=' ||
V_NUM * V_COMMITNUM;
EXECUTE IMMEDIATE EXEC_SQL;
COMMIT; --提交
END LOOP;
DBMS_OUTPUT.PUT_LINE('PRC_WAP_ACTIVEUSERS_BATCH END: ' ||
TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
EXCEPTION
WHEN OTHERS THEN
BEGIN
ROLLBACK;
RET_CODE := '0001';
RET_MSG := '存储过程PRC_WAP_ACTIVEUSERS_BATCH执行错误!' || CHR(10) ||
'错误代码：' || SQLCODE || CHR(10) || '错误信息：' ||
SUBSTR(SQLERRM, 1, 128);
GOTO TOEND;
END;
END;
--返回程序运作结果
RETCODE := RET_CODE;
<<TOEND>>
IF (RET_CODE = '0000') THEN
RET_MSG := '运行成功!';
COMMIT;
ELSE
RET_MSG := '运行失败!' || RET_MSG;
END IF;
DBMS_OUTPUT.PUT_LINE(RET_MSG);
DBMS_OUTPUT.PUT_LINE('END TIME : ' ||
TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
END PRC_WAP_ACTIVEUSERS_BATCH;

三、

[sql] view plain copy

CREATE OR REPLACE PROCEDURE PRC_BS_ASS_MODIFYRESULT
(ASSESS_TIME IN VARCHAR2, --时间
OBJECT_ID IN VARCHAR2, --对象ID
KPI_ID IN VARCHAR2, --指标ID
MOD_DATA IN VARCHAR2, --修改后的数据
O_INFO OUT VARCHAR2 --返回执行信息
) AS
TYPE MYCURSOR IS REF CURSOR; --定义游标类型
CUR MYCURSOR; --定义游标
TYPE CUR_DATA_TYPE IS RECORD --定义游标数据类型
(
RESULT_ID VARCHAR2(20), --系统（人工）结果ID
RULE_CONTENT VARCHAR2(2000) --解析后的规则
);
VRECORD CUR_DATA_TYPE; --定义数据类型
RET_CODE VARCHAR2(6); --错误信息代码
RET_MSG VARCHAR2(200); --错误信息
EXEC_SQL VARCHAR2(1500); --SQL语句
BEGIN
--程序开始
DBMS_OUTPUT.PUT_LINE('START TIME : ' ||
TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24:mi:ss'));
RET_CODE := '000000';
--修改数据明细
BEGIN
DBMS_OUTPUT.PUT_LINE('修改数据明细 BEGIN: ' ||
TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24:mi:ss'));
EXEC_SQL := 'UPDATE BS_ASS_RESULTLIST_INFO
SET KPI_VAL = ' || MOD_DATA || '
WHERE ASSESS_TIME = ''' || ASSESS_TIME || '''
AND KPI_ID = ''' || KPI_ID || '''
AND OBJECT_ID = ''' || OBJECT_ID || '''';
EXECUTE IMMEDIATE EXEC_SQL;
DBMS_OUTPUT.PUT_LINE('修改数据明细 END: ' ||
TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24:mi:ss'));
EXCEPTION
WHEN OTHERS THEN
BEGIN
RET_CODE := '000001';
RET_MSG := '修改数据明细时信息错误!' || CHR(10) || '错误代码：' || SQLCODE ||
CHR(10) || '错误信息：' || SUBSTR(SQLERRM, 1, 128);
GOTO TOEND;
END;
END;
--借助动态游标，计算修改数据明细项
BEGIN
DBMS_OUTPUT.PUT_LINE('计算修改明细 BEGIN: ' ||
TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24:mi:ss'));
EXEC_SQL := 'SELECT RESULT_ID , RULE_CONTENT
FROM BS_ASS_RESULTLIST_INFO
WHERE ASSESS_TIME = ''' || ASSESS_TIME || '''
AND KPI_ID = ''' || KPI_ID || '''
AND OBJECT_ID = ''' || OBJECT_ID || '''';
OPEN CUR FOR EXEC_SQL;
LOOP
FETCH CUR
INTO VRECORD;
EXIT WHEN CUR%NOTFOUND;
EXEC_SQL := 'UPDATE BS_ASS_RESULTLIST_INFO
SET (ASS_MARK,
FINAL_MARK) = (SELECT (' ||
VRECORD.RULE_CONTENT || ') * A.KPI_COEFFICIENT,(' ||
VRECORD.RULE_CONTENT || ') * A.KPI_COEFFICIENT * A.REFER_COEFFICIENT
FROM BS_ASS_RESULTLIST_INFO A
WHERE A.RESULT_ID = ''' || VRECORD.RESULT_ID || ''')
WHERE RESULT_ID = ''' || VRECORD.RESULT_ID || '''';
EXECUTE IMMEDIATE EXEC_SQL;
END LOOP;
DBMS_OUTPUT.PUT_LINE('计算修改明细 END: ' ||
TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24:mi:ss'));
EXCEPTION
WHEN OTHERS THEN
BEGIN
RET_CODE := '000002';
RET_MSG := '计算修改明细时信息错误!' || CHR(10) || '错误代码：' || SQLCODE ||
CHR(10) || '错误信息：' || SUBSTR(SQLERRM, 1, 128);
GOTO TOEND;
END;
END;
--返回程序运行结果
<<TOEND>>
IF (RET_CODE = '000000') THEN
RET_MSG := '运行成功!';
O_INFO := 'SUCCESS';
COMMIT;
ELSE
RET_MSG := '运行失败!' || RET_MSG;
O_INFO := 'FALSE';
ROLLBACK;
END IF;
DBMS_OUTPUT.PUT_LINE(RET_MSG);
DBMS_OUTPUT.PUT_LINE('END TIME : ' ||
TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24:mi:ss'));
END PRC_BS_ASS_MODIFYRESULT;

0 0