Create a job in SQL Server and Oracle

First, let's talk about ms server. Because I am a newbie, I first chose to use the Enterprise Manager for task settings and then export the designed task script, in this way, you can directly execute scripts in other databases to create a task (you need to modify some of the task names and database names to be equivalent ), the following describes the specific operation steps and the obtained task script:

1. Ensure that "SQL server proxy service" (namely, agent service) of ms Server is started)

2. choose Enterprise Manager> connect to the data Server. Right-click jobs under the SQL Server agent node and select New Job. On the task details page, we will see a lot of configuration options, including "General", "Step", "plan", "alarm", "notification", and "target, here we can perform detailed task settings, because what I need to do is to execute a stored procedure in the database on time, so we simply set the general, step, and plan content, in which we set the database operations and sequence we want to perform through the steps, then, you can use the "Plan" method to set the specific operation scheme. Compared with writing SQL statements directly, it is easier and more suitable for beginners.

3. Right-click the job we just created, select "Script job as-> create to", save the Script TO a file or open it directly in the editor, and then save it. We can see that the script automatically generated by the system looks complicated, because it sets all the set parameters, and many default parameters may be omitted through the SQL script, however, we can clearly see several key steps for job creation. The SQL script for the personal example is provided below. The simplified code is as follows:
Copy codeThe Code is as follows:
DECLARE @ jobId BINARY (16)
-- Create the job
EXEC msdb. dbo. sp_add_job @ job_name = 'Update table of sys_pageinfo ',
@ Job_id = @ jobId OUTPUT
-- Create the step of the job
EXEC msdb. dbo. sp_add_jobstep @ job_id = @ jobId,
@ Step_name = 'Just one step ',
@ Subsystem = 'tsql ',
@ Command = 'exec dbo. batchupdatearchlisttotalnum ',
@ Database_name = n' Exchanger _ 2'
-- Create the schedual of the job
EXEC msdb. dbo. sp_add_jobschedule @ job_id = @ jobId,
@ Name = 'the schedual ',
@ Freq_type = 4,
@ Freq_interval = 1,
Active_start_time = 10000
-- Create the server of the job
EXEC msdb. dbo. sp_add_jobserver @ job_id = @ jobId, @ server_name = n' (local )'

Next, let's take a look at how jobs are created in oracle. This time, we directly use SQL scripts to create jobs. Some common SQL statements are as follows:
1. View tasks in the database
Select * from user_jobs
2. Delete the specified task in the database
Begin
Dbms_job.remove (50 );
End;
3. Create a task to be executed every 5 minutes
DECLARE
Jobid NUMBER;
BEGIN
SYS. DBMS_JOB.SUBMIT (job => jobid,
What => 'batchupdatearchlisttotalnum (); ', // if there is no parameter in the stored procedure, parentheses can be omitted, but semicolons cannot be omitted
Next_date => sysdate,
Interval => 'sysdate + 1/24/12 ');
COMMIT;
END;
Appendix: SQL server task script:
Copy codeThe Code is as follows:
USE [msdb]
GO
/***** Object: Job [update] script Date: 05/12/2009 18:14:14 ******/
BEGIN TRANSACTION
DECLARE @ ReturnCode INT
SELECT @ ReturnCode = 0
/***** Object: JobCategory [Database Maintenance] script Date: 05/12/2009 18:14:14 ******/
If not exists (SELECT name FROM msdb. dbo. syscategories WHERE name = n' Database Maintenance 'AND category_class = 1)
BEGIN
EXEC @ ReturnCode = msdb. dbo. sp_add_category @ class = n'job', @ type = n'local', @ name = n'database Maintenance'
IF (@ ERROR <> 0 OR @ ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @ jobId BINARY (16)
EXEC @ ReturnCode = msdb. dbo. sp_add_job @ job_name = n'job name ',
@ Enabled = 0,
@ Policy_level_eventlog = 0,
@ Policy_level_email = 0,
@ Policy_level_netsend = 0,
@ Policy_level_page = 0,
@ Delete_level = 0,
@ Description = n' no description. ',
@ Category_name = n' Database Maintenance ',
@ Owner_login_name = N 'sa ', @ job_id = @ jobId OUTPUT
IF (@ ERROR <> 0 OR @ ReturnCode <> 0) GOTO QuitWithRollback
/***** Object: Step [update the content of the Sys_PageInfo table] script Date: 05/12/2009 18:14:15 ******/
EXEC @ ReturnCode = msdb. dbo. sp_add_jobstep @ job_id = @ jobId, @ step_name = n' operation step name ',
@ Step_id = 1,
@ Cmdexec_success_code = 0,
@ On_success_action = 1,
@ On_success_step_id = 0,
@ On_fail_action = 2,
@ On_fail_step_id = 0,
@ Retry_attempts = 0,
@ Retry_interval = 0,
@ OS _run_priority = 0, @ subsystem = N 'tsql ',
@ Command = n' EXEC dbo. batchupdatearchlisttotalnum ',
@ Database_name = n' Exchanger _ 2 ',
@ Database_user_name = N 'dbo ',
@ Flags = 0
IF (@ ERROR <> 0 OR @ ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ ReturnCode = msdb. dbo. sp_update_job @ job_id = @ jobId, @ start_step_id = 1
IF (@ ERROR <> 0 OR @ ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ ReturnCode = msdb. dbo. sp_add_jobschedule @ job_id = @ jobId, @ name = n' scheduler name ',
@ Enabled = 1,
@ Freq_type = 4,
@ Freq_interval = 1,
@ Freq_subday_type = 1,
@ Freq_subday_interval = 1,
@ Freq_relative_interval = 0,
@ Freq_recurrence_factor = 0,
@ Active_start_date = 20090512,
@ Active_end_date = 99991231,
@ Active_start_time = 0,
Active_end_time = 235959
IF (@ ERROR <> 0 OR @ ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ ReturnCode = msdb. dbo. sp_add_jobserver @ job_id = @ jobId, @ server_name = n' (local )'
IF (@ ERROR <> 0 OR @ ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@ TRANCOUNT> 0) ROLLBACK TRANSACTION
EndSave: