Stored procedure + job regular execution of fixed SQL statements

When making a report, it needs to read a large amount of data and carry out complex calculations, which is often time-consuming, so the results are generally stored in a result table, and the report reads the result table directly, which will be very fast. At this time, in order to update the result table and get the latest data, you need to execute some SQL statements regularly. At this time, you can use the method of stored procedure + job to achieve the goal. The specific steps are as follows

Create a stored procedure:

Create or replace procedure datawarn.P_TEST is

Begin

Delete from test_table

Insert test_table select * from table1;-- sql block, that is, the sql statement you want to execute

Commit;-sql block

End

Execute the stored procedure (manually call the stored procedure once):

EXEC stored procedure name

Create a JOB:

Begin

Dbms_scheduler.create_job (

Job_name = > 'Jacks JOB,-- name

Job_type = > 'STORED_PROCEDURE'

Job_action = > 'stored Testament,-- stored procedure name

Start_date = > sysdate

Repeat_interval = > 'FREQ=MINUTELY; INTERVAL=10',-every ten minutes

Comments = > 'JOB description'

);

End

Ps: be careful when creating a job. Check it carefully and don't miswrite the information. If you report an error, creating it again will lead to unsuccessful creation. You can try to change the job name.

-- start

Begin

Dbms_scheduler.enable ('Jacks TEST')

End

Execution

Begin

Dbms_scheduler.run_job (job_name = > 'Jacks TESTESTERT force = > TRUE);-- true stands for synchronous execution

End

Stop it

Dbms_scheduler.stop_job (job_name = > 'Jacks TESTESTELER force = > TRUE)

-- DI JOB query

Select * from tsc_jobstatus