oracle scheduled stored procedure

Oracle is a very popular relational database. In business, we often need to execute some stored procedures regularly to complete some automated operations and save operating costs. This article will introduce how to use Oracle to regularly execute stored procedures.

1. The basic concept of scheduled execution of stored procedures in Oracle
The scheduled execution of stored procedures in Oracle means that the system automatically executes some pre-written stored procedures within a certain time interval. This method is often used for regular data archiving, backup, cleanup and other tasks.

2. How to implement Oracle's scheduled execution of stored procedures
Oracle's scheduled execution of stored procedures can be achieved through Oracle's scheduler. The scheduler makes it easy to start a job at a specified point in time or interval and end it when the time is up.

1. Create a job
   In Oracle, you can create a job through PL/SQL, PL/SQL Developer, Toad and other tools.
   For example, create a job called "myjob", including executing the stored procedure "mystoredprocedure":

begin
dbms_scheduler.create_job
(job_name=>'myjob',
job_type=>'STORED_PROCEDURE',
job_action=>'mystoredprocedure');
end;

2. Scheduled execution
   After creating the job, what else needs to be done Time to perform this job? The Oracle scheduler can control the execution time of jobs in the following two ways:

(1) Time-based execution
In Oracle, you can use the dbms_scheduler package to create a schedule:

BEGIN
DBMS_SCHEDULER.CREATE_SCHEDULE (

      schedule_name          => 'weekends',  
      start_date             => SYSTIMESTAMP,  
      repeat_interval        => 'FREQ=WEEKLY;BYDAY=SAT,SUN;BYHOUR=0;BYMINUTE=0',  
      end_date               => NULL,  
      comments               => 'Weekend schedule - Saturday and Sunday every week at midnight.'  

);
END;

This code snippet can create a schedule that is executed at zero o'clock every day on weekends. Of course, different schedules can be created based on specific business needs.

In order for myjob to be executed at the following times, the schedule weekends logic needs to be applied to myjob:

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE

(  
  name                      => 'myjob',  
  attribute                 => 'repeat_interval',  
  value                     => 'weekends'  
);  

END;

Using this time-based scheduling method, the execution cycle of the job will be very fixed and set by the developer according to business needs.

(2) Event-based execution
Another way to implement scheduled execution of stored procedures is event-driven. For example, if a field in a data table changes, then the stored procedure needs to be driven. execution. In Oracle, you can create an event-based scheduled schedule through the following code:

BEGIN
DBMS_SCHEDULER.CREATE_JOB

(  
  job_name             => 'job_name',  
  job_type             => 'PLSQL_BLOCK',  
  job_action           => 'BEGIN my_procedure(:my_param); END;',  
  start_date           => SYSTIMESTAMP,  
  event_condition      => 'tab.col3 = ''Y''',  --这里可以选择需要监控的事件类型和字段  
  queue_spec           => 'queue_specification',  
  auto_drop            => FALSE,  
  enabled              => TRUE  
);  

END;

3. Execute storage Process
   In the above example, the role of the "myjob" job is to execute the stored procedure "mystoredprocedure". Here we can see that the scheduler itself does not run the stored procedure, it is only responsible for managing the execution of the job.

To successfully schedule a stored procedure, you must create a stored procedure and associate it with a job. Here I create a stored procedure named "mystoredprocedure" as an example:

CREATE OR REPLACE PROCEDURE "mystoredprocedure"
IS
BEGIN

INSERT INTO employees_audit  
  (employee_id,  
   salary,  
   entry_date)  
SELECT employee_id,  
       salary,  
       SYSDATE  
  FROM employees
  WHERE hire_date > SYSDATE - 1;  
       

END mystoredprocedure;

This example simply stores the new data information in the employees_audit table of the database. Developers can write their own stored procedures according to specific needs.

The program that regularly executes the stored procedure runs in the background. According to the time scheduling policy set by the developer, the job will be started at the specified time, and then the mystoredprocedure stored procedure will be executed.

3. Summary
Oracle's scheduled execution of stored procedures is an efficient solution for processing periodic tasks. This process can be easily implemented using Oracle's scheduler. Through the implementation method introduced in this article, developers can flexibly configure the running time, frequency and stored procedures that need to be executed. This can greatly shorten business processing time and improve processing efficiency.

The above is the detailed content of oracle scheduled stored procedure. For more information, please follow other related articles on the PHP Chinese website!