Examples of Oracle Cursors

• CREATE OR REPLACE PROCEDURE prc_wap_activeusers (RETCODE out VARCHAR2)
• /***********************************************************
• * Function: WAP indicator--Active user Statistics (batch submission)
• * Parameters: RETCODE (return code: 0000 Success)
• *
• * Date Created: 2013-01-16
• * Version: 1.0
• * Modify Person:
• * Modified Time:
• **********************************************************/
• Is
• Ret_code VARCHAR2 (6); --Error message code
• Ret_msg VARCHAR2 (200); --Error message
• V_last_month VARCHAR2 (8); --Last month's share
• V_first_day_month VARCHAR2 (10); -The first day of last month
• V_end_day_month VARCHAR2 (10); -The second day of last month
• V_count number; --Count variables
• V_commitnum CONSTANT number :=1000000;--One commit record (default 1 million)
• BEGIN
• --Program Start
• Dbms_output. Put_Line (' prc_wap_activeusers BEGIN: ' | |
• To_char (sysdate, ' Yyyy-mm-dd HH24:MI:SS '));
• Ret_code := ' 0000 '; --Return encoding initialization
• V_count := 0; --Counter initialization
• --Declaring cursors
• DECLARE
• Type Cur_data_type is RECORD--defines the dynamic cursor data type
• (
• Phone_num VARCHAR2 (20),--Phone number
• Login_times VARCHAR2 (2000)); --Number of visits
• Vrecord Cur_data_type; --Defining data types
• 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; --Residual: 1 million submitted once
• 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 := ' Stored procedure prc_wap_activeusers execution error! ' ||  CHR (10) | |
• ' Error code: ' | | SQLCODE | | CHR (10) | | ' Error message: ' | |
• SUBSTR (SQLERRM, 1, 128);
• GOTO Toend;
• END;
• END;
• --Return to program operation results
• < < Toend > >  
• RETCODE := ret_code;
• IF (ret_code = ' 0000 ') Then
• Ret_msg := ' run successfully! '  ;
• COMMIT;
• ELSE
• Ret_msg := ' run failed! ' ||  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;