oracle、sqlserver和mysql都能创建自动执行的作业调度，定期完成固定工作，比如定期备份等都可以通过作业调度的方式实现。三种数据库作业调度都可以通过sql语句和内部命令的方式来创建、运行和停止。

oracle作业：

1创建存储过程

CREATE OR REPLACE

PROCEDURE PRO_TEST_JOB

AS

BEGIN

DBMS_OUTPUT.PUT_LINE('Navicat for Oracle');

END;

2、创建作业：

declare jobtest number;

BEGIN

dbms_job.submit(jobtest,'PRO_TEST_JOB();',sysdate,'sysdate +1/1440',false);

commit;

-- 单独运行job

dbms_job.run(1); --和select * from user_jobs; 中的job值对应，看what对应的过程

END;

3、停止job

dbms_job.broken(1,true);

commit;

end;

删除job

dbms_job.remove(1);

dbms_job.what(1,'sp_fact_charge_code;');

--修改某个job下一次运行时间

dbms_job.next_date(1,sysdate);

sqlserver使用sql创建作业:

BEGIN 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'access911_每2周备份一次')

IF (@JobID IS NOT NULL)

BEGIN

-- 检查此作业是否为多重服务器作业

IF (EXISTS (SELECT *

FROM msdb.dbo.sysjobservers

WHERE (job_id = @JobID) AND (server_id <> 0)))

BEGIN

-- 已经存在，因而终止脚本

RAISERROR (N'无法导入作业“access911_每2周备份一次”，因为已经有相同名称的多重服务器作业。', 16, 1)

GOTO QuitWithRollback

END

ELSE

-- 删除［本地］作业

EXECUTE msdb.dbo.sp_delete_job @job_name = N'access911_每2周备份一次'

SELECT @JobID = NULL

END

BEGIN

-- 添加作业

EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'access911_每2周备份一次', @owner_login_name = N'Access911\access911', @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'2周备份', @command = N'EXECUTE cg_DoBackupJob ''a9SupperDatabase'',''a9SupperDatabase'',1

', @database_name = N'master', @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'diaodu', @enabled = 1, @freq_type = 8, @active_start_date = 20061009, @active_start_time = 0, @freq_interval = 64, @freq_subday_type = 1, @freq_subday_interval = 0, @freq_relative_interval = 0, @freq_recurrence_factor = 2, @active_end_date = 99991231, @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

END

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

mysql创建作业:

CREATE EVENT NOT EXISTS STAT

ON SCHEDULE EVERY 1 MONTH STARTS DATE_ADD(DATE_ADD(DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY), INTERVAL 1 MONTH),INTERVAL 1 HOUR)

ON COMPLETION PRESERVE ENABLE

DO

BEGIN

CALL STAT();

END;

关闭开启事件:

alter event event_name on completion preserver [enable | disable]

删除事件;

drop event event_name;