oracal 游标
一、
- 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;
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;
二、
- CREATEORREPLACEPROCEDURE 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 := 'SELECTCOUNT(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
- GROUPBY 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;
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;
三、
- CREATEORREPLACEPROCEDURE 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;