Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to create job in SQL Server and Oracle". The content in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn how to create job in SQL Server and Oracle.

First of all, I would like to talk about ms server, because I am also a novice, so I first choose to use enterprise manager for task settings, and then export the designed task script, so that you can directly create a new task in other databases by executing the script (some of the task names and database names need to be modified appropriately). Here are the specific steps and the obtained task scripts:

1. Ensure that ms server's "SQL Server Agent Service" (i.e. agent service) is started

two。 Open Enterprise Manager-> Connect to the data server-> right-click "jobs" under the "SQL Server agent" node and select "New Job". On the detailed task page, we will see many setting options, including "General", "steps", "Plan", "Alerts", "notifications" and "goals". Here we can make very detailed task settings, because what I have to do is very simple. Is to execute a stored procedure in the database on time, so it simply sets the contents of the routine, steps and plan, in which we set the database operation and order we want to carry out through the steps, and then set the specific operation plan through the "plan". Compared to 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", choose to save the script to a file or open it directly through the editor, and then save it. We can see that the script automatically generated by the system looks quite complex because it sets all the setting parameters, and many default parameters may be omitted through the sql script, but we can clearly see several key steps in the creation of job. The sql script for the personal example is attached. The simplified 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)'

It is useful in the program

Let users customize some reminders or work tasks that are executed regularly.

So you may need to use the Job created by SQLServer.

SQL statements created due to current unfamiliar jobs

So just.

1. Work through the wizard of the management office of the enterprise manager

Create an assignment directly

two。 View the SQL statement for the job

The relevant SQL statements are as follows

-- 2007-8-16 script generated on ReturnCode INT SELECT 1458 from sa-- server: 192.168.0.36BEGIN TRANSACTION DECLARE @ JobID BINARY (16) DECLARE @ ReturnCode INT SELECT @ ReturnCode = 0 IF (SELECT COUNT (*) FROM msdb.dbo.syscategories WHERE name = N' [Uncategorized (Local)]')

< 1 EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]' -- 删除同名的警报（如果有的话）。 SELECT @JobID = job_id FROM msdb.dbo.sysjobs WHERE (name = N'WriteMsgOnTime') IF (@JobID IS NOT NULL) BEGIN -- 检查此作业是否为多重服务器作业 IF (EXISTS (SELECT * FROM msdb.dbo.sysjobservers WHERE (job_id = @JobID) AND (server_id 0))) BEGIN -- 已经存在，因而终止脚本 RAISERROR (N'无法导入作业"WriteMsgOnTime"，因为已经有相同名称的多重服务器作业。', 16, 1) GOTO QuitWithRollback END ELSE -- 删除［本地］作业 EXECUTE msdb.dbo.sp_delete_job @job_name = N'WriteMsgOnTime' SELECT @JobID = NULL END BEGIN -- 添加作业 EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'WriteMsgOnTime', @owner_login_name = N'sa', @description = N'没有可用的描述。', @category_name = N'[Uncategorized (Local)]', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0 IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback -- 添加作业步骤 EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'WriteMsg', @command = N'INSERT INTO Msg(MsgToUser, MsgTitle, MsgContent ,MsgDateTime )VALUES(''张三'',''注意休息'',''需要定时休息一下啊'',GETDATE() )', @database_name = N'TestDB', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2 IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1 IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback -- 添加作业调度 EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'5min', @enabled = 1, @freq_type = 4, @active_start_date = 20070816, @active_start_time = 0, @freq_interval = 1, @freq_subday_type = 4, @freq_subday_interval = 5, @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_end_date = 20070816, @active_end_time = 235959 IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback -- 添加目标服务器 EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)' IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback ENDCOMMIT TRANSACTION GOTO EndSave QuitWithRollback: IF (@@TRANCOUNT >

0) ROLLBACK TRANSACTION EndSave:

Create job in oracle

Let's take a look at how to create job in oracle. This time we directly use the sql script to create it. Some common sql statements are as follows:

First, view the tasks in the database

Select * from user_jobs

Delete the tasks specified in the database

Begin

Dbms_job.remove (50)

End

Create a task that is executed every 5 minutes

DECLARE

Jobid NUMBER

BEGIN

SYS.DBMS_JOB.SUBMIT (job = > jobid)

What = > 'BatchUpdateArchListTotalNum ();', / / stored procedures can omit parentheses but not semicolons if they have no parameters

Next_date = > sysdate

Interval = > 'sysdate+1/24/12')

COMMIT

END

Attached: SQL server task script:

USE [msdb] GO / * object: Job [update] script date: 05gamma 18:14:14 * / BEGIN TRANSACTION DECLARE @ ReturnCode INT SELECT @ ReturnCode = 0 / * object: JobCategory [Database Maintenance] script date: 05amp12amp2009 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, @ notify_level_eventlog=0, @ notify_level_email=0, @ notify_level_netsend=0, @ notify_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 contents of the Sys_PageInfo table] script date: 05U12 EXEC @ ReturnCode = msdb.dbo.sp_add_jobstep @ job_id=@jobId, @ step_name=N' procedure 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' Project 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: thank you for reading The above is the content of "how to create job in SQL Server and Oracle". After the study of this article, I believe you have a deeper understanding of how to create job in SQL Server and Oracle. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.