Oracle Stored Procedure instance memo

Source: Internet
Author: User

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;

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.