SQL SERVER数据库的作业的脚本及存储过程

简介

SQL SERVER是一种关系型数据库管理系统。在SQL SERVER中，作业(Job)是一种可执行的对象，可以用于自动化管理数据库以及其他相关任务。作业可以包括多个步骤，每个步骤都是由一些SQL语句、操作系统命令或是操作文本文件等组成。存储过程(Stored Procedure)则是一种预先编译好的SQL语句的集合，其可以作为一种可执行的对象来使用。

作业脚本

新建作业

我们可以使用以下代码来新建一个作业，代码中先定义了一个作业对象，然后分别设置了作业名称、类别等相关属性，最后将作业添加至SQL Server代理中。

USE [msdb]
GO
DECLARE @jobId BINARY(16)
EXEC  msdb.dbo.sp_add_job @job_name=N'MyJob',




@enabled=1,




@notify_level_eventlog=0,




@notify_level_email=2,




@notify_level_netsend=0,




@notify_level_page=0,




@delete_level=0,




@description=N'This is a job to backup databases',




@category_name=N'[Uncategorized (Local)]',




@owner_login_name=N'sa', @job_id = @jobId OUTPUT
SELECT @jobId

添加步骤

接下来我们需要为作业添加步骤，代码如下所示，使用msdb.dbo.sp_add_jobstep存储过程向作业添加步骤。

EXEC msdb.dbo.sp_add_jobstep 
    @job_name=N'MyJob',
    @step_name=N'Step1',
    @step_id=1,
    @cmdexec_success_code=0,
    @on_success_action=1,
    @on_fail_action=2,
    @retry_attempts=0,
    @retry_interval=0,
    @os_run_priority=0, @subsystem=N'TSQL',
    @command=N'exec sp_BackupDatabase',
    @database_name=N'master',
    @output_file_name=N'C:\MyJob.txt',
    @flags=0

可以看到，步骤的内容是“exec sp_BackupDatabase”，意思是执行存储过程sp_BackupDatabase。存储过程的内容不在本文范围内。

启动作业

完成创建作业、添加步骤后，我们需要启动作业，运行下面的代码即可启动(Start)作业(MyJob)。

USE [msdb]
GO
EXEC msdb.dbo.sp_start_job N'MyJob'
GO

存储过程

创建存储过程

我们可以使用以下代码来创建一个存储过程。其中，CREATE PROCEDURE是定义存储过程的语句，@param1和@param2是传入存储过程的参数，AS下面是存储过程的具体内容。存储过程SimpleSelect会将表test中的前100行数据查询出来并返回。

CREATE PROCEDURE SimpleSelect
    @param1 int,
    @param2 varchar(50)
AS
    SELECT TOP 100 *
        FROM test
    RETURN 0

调用存储过程

调用存储过程的示例如下，使用EXECUTE语句调用存储过程，并传入参数。存储过程SimpleSelect的返回值为0。

DECLARE @return_value int
EXEC @return_value = SimpleSelect
    @param1 = 0,
    @param2 = 'Test'
SELECT 'Return Value' = @return_value
GO

修改存储过程

如果想对已有的存储过程进行修改，可以使用ALTER PROCEDURE语句。

ALTER PROCEDURE SimpleSelect
    @param1 int,
    @param2 varchar(50)
AS
    SELECT TOP 200 *
        FROM test
    RETURN 0

可以看到，我们将查询表test的行数从原先的100行修改为200行。

删除存储过程

最后，当需要删除存储过程时，可以使用DROP语句进行删除，如下所示：

DROP PROCEDURE SimpleSelect

总结

本文详细介绍了SQL SERVER中作业的脚本及存储过程的相关内容。通过本文的介绍，我们了解了如何创建、修改、启动、删除作业，并掌握了如何创建、调用、修改、删除存储过程的具体方法。