Study Notes: Oracle + procedure + job

Write is a log server that records a large amount of data: When a table is involved, it may need to record a large amount of data, so we have to consider using Table shards to record data,

I wrote a storage process to produce a table every day. A large amount of data was found online and I sorted it out myself.

 

 

Create or replace procedure createlogs_time
Authid CURRENT_USER
As
Tabname varchar (200 );
Begin
Select 'ex _ log' | to_char (sysdate, 'yyyymmdd') into tabname from dual;

-- Create Table tabname as select * From tbl_programme where 1! = 1;

Execute immediate 'create table' | tabname | 'tablespace manage as select * From tmon_monitor_log_exclog where 1! = 1 ';
Commit;

End;

 

Job is used to execute the stored procedure at every day.

 

Variable job2 number;
Begin
Dbms_job.submit (: job2, 'createlogs _ time; ', trunc (sysdate +) + 20/24, 'trunc (sysdate +) + 20/24 ');

Commit;
End;

 

Then we need to write the statistics of the table produced every day into the stored procedure.

Create or replace procedure updatec is

Type RC is ref cursor;
Rochelle cursor RC;
V_ SQL varchar2 (1000 );
V_tab_name varchar2 (30 );
Times varchar (20 );
Log_time varchar (40 );
Res_name varchar (40 );
Res_req_times varchar (40 );
Begin
Select to_char (sysdate, 'yyyymmdd') into times from dual;
Select to_char (sysdate, 'yyyy-mm-dd hh24: MI: ss') into log_time from dual;
-- V_tab_name: = 'ex _ log20090828 ';
Select 'ex _ log' | to_char (sysdate, 'yyyymmdd') into v_tab_name from dual;
V_ SQL: = 'select res_name, count (req_type) as ressum from '| v_tab_name | 'where req_type = ''finish '''group by res_name ';
Open l_cursor for v_ SQL;
Loop
Fetch l_cursor into res_name, res_req_times;
Exit when l_cursor % notfound;
Insert into tmon_monitor_count_exlog values (test. nextval, times, res_name, log_time, res_req_times, res_req_times );
End loop;
Close l_cursor;
End;

 

Job executes the stored procedure at every night

 

Variable job2 number;
Begin
Dbms_job.submit (: job2, 'updatec; ', trunc (sysdate +) + 20/24, 'trunc (sysdate +) + 100'); Commit;
End;