ORACLE Stored Procedure instance [memorandum], oracle Stored Procedure

ORACLE Stored Procedure instance [memorandum], oracle Stored Procedure

Statistical report: the number of user logins (platform clicks) at the beginning of each month. The scheduled tasks count the number of logins, number of Logon accounts, and total number of accounts in the previous month.

Store the queried values in the RP_MONTH_CLICK table using the stored procedure.

Create or replace procedure update_RP_MONTH_CLICK as -- defines the variable date_v varchar (6); -- login_count_v INTEGER in year and month; -- number of login_accoun_count_v integers; -- number of Logon accounts accoun_count_v INTEGER; -- account total begin/* ------------------ get parameters assign values to the variable start ------------------- */-- year and month select case WHEN to_char (sysdate-1, 'yyymmm ') = to_char (sysdate, 'yyyymm') THEN to_char (sysdate, 'yyymmm') else to_char (sysdate-1, 'yyymmm') end into date_v from dual; -- select count (t. log_id) into login_count_v from SC _ACT_LOG t where t. act_type = 5 and TO_DATE (TO_CHAR (t. act_time, 'yyyymm'), 'yyyymm') = TO_DATE (TO_CHAR (SYSDATE, 'yyyymm'), 'yyyymm'); -- select count of Logon accounts (staff_id) into login_accoun_count_v from (select t. staff_id from SC _ACT_LOG t where t. act_type = 5 and TO_DATE (TO_CHAR (t. act_time, 'yyyymm'), 'yyyymm') = TO_DATE (TO_CHAR (SYSDATE, 'yyyymm'), 'yyyymm') group by t. staff_id); -- select count (t. staff_id) into accoun_count_v from HRM_STAFF t where t. status = 1;/* ------------------ get the end parameter */-- delete from RP_MONTH_CLICK where RMONTH = date_v; -- add insert into RP_MONTH_CLICK (ID, RMONTH, LOGIN_COUNT, LOGIN_ACCOUNT_COUNT, ACCOUNT_COUNT) values (SEQ_ SC _GLOBAL_PARAMETERS.NEXTVAL, date_v, login_count_v, login_accoun_count_v, accoun_count_v); commit; end;