Scheduled SQL script

Because a table needs to be synchronized, data needs to be synchronized once a day, but I am not proficient in SQL. I wrote a piece of code to test the timing function.

Creates a stored procedure for inserting data without output or output parameters.

Create or replace procedure temp_pro
Begin
Insert into demopa18 values ('3', 'cloduy', 'female, '23 ');
Commit;
End temp_pro;

Then, you need to set the command to call the Stored Procedure regularly, mainly using the dbms_job package in Oracle.

SQL> variable num number;
SQL> begin
2 dbms_job.submit (: num, 'temp _ Pro; ', sysdate,
3 'sysdate + 1/360 ');
4 commit;
5 end;
6/

First, define a num variable. This variable is used to store the task number, which is randomly generated. The stored procedure name is followed, sysdate is the next execution time, And 'sysdate + 123' is the interval. If it is null, the task will be deleted after execution. You cannot execute the task at a scheduled time. The specific format of scheduled execution is as follows:

Dbms_job.submit (job out binary_integer,

What in archar2,

Next_date in date,

Interval in varchar2,

No_parse in Boolean)

Where:

● Job: output variable, which is the number of the task in the task queue;

● What: the name of the task to be executed and its input parameters;

● Next_date: the time when the task is executed;

● Interval: The interval between tasks.

Such commands should be switched to the Command window instead of the SQL window.