Oracle timed query data is inserted into a new table (job+ stored procedure)

CREATE TABLE Egmas_count_data
(
Times date NOT NULL,
COUNT number (+) is not NULL,
System_name VARCHAR2 (+) NOT NULL,
Operation_index VARCHAR2 (+) NOT NULL
);
--ADD comments to the columns
Comment on column egmas_count_data. Times
is ' date ';
Comment on column egmas_count_data. COUNT
is ' statistic ';
Comment on column egmas_count_data. System_name
Is ' system code ';
Comment on column egmas_count_data. Operation_index
Is ' Business operation indicator name ';

Stored procedures:

Create or replace procedure OrderCount is

Begin

INSERT into Egmas_count_data (day, Count,system_name,operation_index)

Select To_date (To_char (sysdate-1, ' yyyy-mm-dd '), ' Yyyy-mm-dd '), (select COUNT (1) from t_orders WHERE order_resource = ' 13 ‘

and To_char (Created_tm, ' yyyy-mm-dd ') = To_char (sysdate-1, ' yyyy-mm-dd ')), ' Egmas ', ' Android Phone order volume (Hong Kong, Macao and Taiwan) '

from dual;

INSERT into Egmas_count_data (day, Count,system_name,operation_index)

Select To_date (To_char (sysdate-1, ' yyyy-mm-dd '), ' Yyyy-mm-dd '), (select COUNT (1) from t_orders WHERE order_resource = ' 14 ‘

and To_char (Created_tm, ' yyyy-mm-dd ') = To_char (sysdate-1, ' yyyy-mm-dd ')), ' Egmas ', ' iphone order volume (Hong Kong, Macao and Taiwan) '

from dual;

INSERT into Egmas_count_data (day, Count,system_name,operation_index)

Select To_date (To_char (sysdate-1, ' yyyy-mm-dd '), ' Yyyy-mm-dd '), (select COUNT (1) from T_orders WHERE

To_char (Created_tm, ' yyyy-mm-dd ') = To_char (sysdate-1, ' yyyy-mm-dd ')), ' Egmas ', ' Speed Express order Volume (Hong Kong, Macao and Taiwan) '

from dual;

Commit

End OrderCount;

/

Set Job execution Time:

Variable jobno number;

Begin

Dbms_job.submit (: Jobno, ' ordercount; ', Sysdate, ' TRUNC (sysdate+1) +1/24 ');

End

/

Start Job:

Begin

Dbms_job.run (44);

Commit

End

/

To see if the job exists:

Select Job, Next_date, next_sec, failures, broken from user_jobs;

Oracle timed query data is inserted into a new table (job+ stored procedure)