Plsql stored Procedures and timed tasks _ example

Environment: oracle11g, PLSQL11&12, Windows7

--Stored procedures

CREATE OR REPLACE PROCEDURE Test_proc is
STARTTIME TIMESTAMP;
Overtime TIMESTAMP;
Comparetime TIMESTAMP;
BEGIN
STARTTIME: = To_date (To_char (add_months (last_day) + 1,-2), ' sysdate '), ' yyyy-mm-dd ');
Overtime: = To_date (To_char (Add_months (Last_day),-1), ' sysdate '), ' yyyy-mm-dd ');
Comparetime: = STARTTIME + INTERVAL ' 1 ' MONTH;

INSERT into table1 (years,months,man,manp,woman,womanp,edumidd,edumiddp,eduhigh,eduhighp,edujuni,edujunip,eduunde ,

Eduundep,nationhan,nationhanp,nationnohan,nationnohanp,contracttype1,contracttype1p,contracttype2, Contracttype2p,contracttype3,

Contracttype3p,contracttype4,contracttype4p,channeltype1,channeltype1p,channeltype2,channeltype2p,channeltype3 , hanneltype3p,

Channeltype4,channeltype4p,channeltype5,channeltype5p,certtype1,certtype1p,certtype2,certtype2p,certtype3, Certtype3p,certtype4,

Certtype4p,allcount,areaid)

SELECT

To_char (Add_months (Last_day (sysdate) + 1,-2), ' yyyy '),

To_char (Add_months (Last_day (sysdate) + 1,-2), ' MM '),

SUM (Casewhen SEX = 0 Then 1 ELSE 0 END) as Mans,

Sum (case when SEX = 0 then 1 else 0 end)/COUNT (*) as MANP,
sum (case when SE X = 0 then 0 else 1 end) as WOMAN,
SUM (case when SEX = 0 Then 0 else 1&nbs P END)/COUNT (*) as WOMANP,
SUM (case when culture = 4 OR Culture = 7 then 1 else 0 end) as EDUM IDD,
SUM (case when culture = 4 OR culture = 7 then 1 else 0 end)/COUNT (*) as edumiddp,< br> sum (case when culture = 3 then 1  else 0 end) as Eduhigh,
sum (case when culture = 3 then 1 else 0 end)/COUNT (*) as EDUHIGHP,

   sum (Case when CULTURE = 1 then 1 else 0 end) as edujuni, 
SUM (CAS E when Culture = 1 then 1 else 0 end)/COUNT (*) as Edujunip,
SUM (Case when culture = 0 or Culture = 5 or Culture = 6 then  1 else 0 end) as Eduunde,
SUM (case when culture = 0 or Culture = 5 or culture = 6 then 1 else 0 end)/COUNT (*) as EDUUNDEP,
SUM (case when NATION = 1 then 1 else 0 end) as Nationhan,
SUM (case when NATION = 1 then 1 else&nb Sp;0 end)/COUNT (*) as NATIONHANP,

SUM (case if NATION = 1 then 0 ELSE 1 END) as Nationnohan,
SUM (Casewhen NATION = 1 then0else1end)/COUNT (*) as NATIONNOHANP,
SUM (Casewhen contracttype = 1 then1else0end) as CONTRACTTTYPE1,
SUM (Casewhen contracttype = 1 then1else0end)/COUNT (*) as contracttype1p,
SUM (Casewhen contracttype = 2 then1else0end) as CONTRACTTTYPE2,
SUM (Casewhen contracttype = 2 then1else0end)/COUNT (*) as contracttype2p,
SUM (Casewhen contracttype = 3 then1else0end) as CONTRACTTTYPE3,
SUM (Casewhen contracttype = 3 then1else0end)/COUNT (*) as contracttype3p,
SUM (Casewhen contracttype = 4 then1else0end) as CONTRACTTTYPE4,
SUM (Casewhen contracttype = 4 then1else0end)/COUNT (*) as contracttype4p,
SUM (Casewhen channeltype = 1 then1else0end) as CHANNELTYPE1,

SUM (case if Channeltype = 1 then 1 ELSE 0 END)/COUNT (*) as channeltype1p,
SUM (Casewhen channeltype = 2 then1else0end) as CHANNELTYPE2,
SUM (Casewhen channeltype = 2 then1else0end)/COUNT (*) as channeltype2p,
SUM (Casewhen channeltype = 3 then1else0end) as CHANNELTYPE3,
SUM (Casewhen channeltype = 3 then1else0end)/COUNT (*) as channeltype3p,
SUM (Casewhen channeltype = 4 then1else0end) as CHANNELTYPE4,
SUM (Casewhen channeltype = 4 then1else0end)/COUNT (*) as channeltype4p,
SUM (Casewhen channeltype = 5 then1else0end) as CHANNELTYPE5,
SUM (Casewhen channeltype = 5 then1else0end)/COUNT (*) as channeltype5p,
SUM (Casewhen certtype = 0 then1else0end) as CERTTYPE1,
SUM (Casewhen certtype = 0 then1else0end)/COUNT (*) as certtype1p,
SUM (Casewhen certtype = 1 then1else0end) as CERTTYPE2,
SUM (Casewhen certtype = 1 then1else0end)/COUNT (*) as certtype2p,
SUM (Casewhen certtype = 2 then1else0end) as CERTTYPE3,
SUM (Casewhen certtype = 2 then1else0end)/COUNT (*) as certtype3p,
SUM (Casewhen certtype = 5 then1else0end) as CERTTYPE4,
SUM (case If Certtype = 5 then 1 ELSE 0 END)/COUNT (*) as certtype4p,
COUNT (*) as Allcount,
Zonetablespace

From (SELECT * from table2 WHERE certstate = ' 1 ' and registertime <= overtime and Orgno are not NULL and Orgno <> ") T2
GROUP by Zonetablespace ORDER by Zonetablespace;

COMMIT;
EXCEPTION
When OTHERS Then
Dbms_output. Put_Line (' Exception happened, data was rollback! ');
ROLLBACK;
END Devmonthstat;

--Timer Dbms_jobs

Begin
Sys.dbms_job.submit (Job = job,
what = ' devmonthstat; ',
Next_date = to_date (' 01-05-2017 ', ' dd-mm-yyyy '),
Interval = ' trunc (last_day (sysdate)) +1 ');
Commit
End
/

Plsql stored Procedures and timed tasks _ example