Calculate the SUM of multiple rows from one table and have it written to another table, weekly
I am not an experienced SQL coder and sort of learn as I go. I have a task that needs to be executed automatically on a weekly basis. There's a few parts to this job:
1) Calculate the SUM of multiple rows from a couple of columns.
select SUM(TOTAL_COST) from TABLE_1 and select SUM(TOTAL_HOURS) from TABLE_1
2) Write the 2 SUMs from TABLE_1 to 2 columns in TABLE_2.
Let's call these columns: SUM_OF_COST and SUM_OF_HOURS from Table_2
3) This job needs to be executed weekly.
BEGIN DBMS_SCHEDULER.SET_ATTRIBUTE ( name => 'update_job', attribute => 'repeat_interval', value => 'freq=weekly; byday=wed'); END; /
The codes above are just from my understanding of I THINK what would be used. I know I have to write the entire job from the first 2 steps above into a package? I'm assuming I would reference that package in the job (step 3). I need help with how I would go about writing this. Does the SUMs need to be written to another column before they can be written to TABLE_2 or can it calculate the SUM and then write the value to TABLE_2 without storing that value in TABLE_1? How do I piece together the code to do the calculations and then write to the second table? Any help would be greatly, greatly, appreciated. Thank you!
You are in right way.
You need to create a procedure that will be performed every week.
CREATE OR REPLACE PROCEDURE sum_weekly IS BEGIN INSERT INTO TABLE_2(SUM_OF_COST ,SUM_OF_HOURS ) (SELECT SUM(cost),SUM(hour) FROM TABLE_1) COMMIT; END;
More about insert select syntax.
Then you need create schedule:
BEGIN DBMS_SCHEDULER.CREATE_JOB ( job_name => 'my.sum_weekly.schedule', job_type => 'PLSQL_BLOCK', job_action => 'BEGIN sum_weekly(); END;', start_date => sysdate, repeat_interval => 'FREQ=WEEKLY', enabled => TRUE, comments => 'Gather table statistics from Table 1 to Table 2'); END;
Examples for repeat_interval.
FREQ=HOURLY;INTERVAL=4 every 4 hour FREQ=HOURLY;INTERVAL=4;BYMINUTE=10;BYSECOND=30 every 4 hours, 10 minutes, 30 seconds of; FREQ=YEARLY;BYYEARDAY=-276 every 31 th March; FREQ=YEARLY;BYMONTH=MAR;BYMONTHDAY=31 every 31 th March;
To check correct set interval
DECLARE next_run_date TIMESTAMP; BEGIN DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING ( 'FREQ=HOURLY;INTERVAL=4;BYMINUTE=10;BYSECOND=30' ,SYSTIMESTAMP ,NULL ,next_run_date) ; DBMS_OUTPUT.PUT_LINE ( 'next_run_date: ' || next_run_date ); END;
To see your schedules use following query:
SELECT job_name, state, enabled FROM user_scheduler_jobs;