Oracle Stored Procedure instance memo
Oracle Stored Procedure instance memo
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
-- Define variables
Date_v varchar (6); -- year and month
Login_count_v INTEGER; -- number of logons
Login_accoun_count_v INTEGER; -- number of Logon accounts
Accoun_count_v INTEGER; -- total number of accounts
Begin
/* ------------------ Assign a value to the variable start --------------------- */when getting the parameter using the into keyword ---------------------*/
-- Year and month
Select case WHEN to_char (sysdate-1, 'yyyymmm ') = to_char (sysdate, 'yyyymmm') THEN to_char (sysdate, 'yyyymmm ')
Else to_char (sysdate-1, 'yyymmm ') end into date_v from dual;
-- Logon count
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, 'yyymmm'), 'yyymmm') = TO_DATE (TO_CHAR (SYSDATE, 'yyymmm'), 'yyymmm ');
-- Number of Logon accounts
Select count (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, 'yyymmm'), 'yyymmm') = TO_DATE (TO_CHAR (SYSDATE, 'yyymmm'), 'yyymmm ')
Group by t. staff_id
);
-- Total number of accounts
Select count (t. staff_id) into accoun_count_v from HRM_STAFF t where t. status = 1;
/* ------------------ Get the parameter end ---------------------*/
-- Delete first
Delete from RP_MONTH_CLICK where RMONTH = date_v;
-- Add again
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;