9.2.2 创建和执行存储过程

在SQL Server 2012中创建存储过程的方法有两种：一种是使用图形化的SQL Server Management Studio来创建存储过程，另一种是使用T-SQL语句来创建存储过程。第一种方法比较适合初学者，第二种方法比较适合SQL Server程序员。

1. 使用SQL Server Management Studio创建和执行存储过程

(1) 启动SQL Server Management Studio，在对象资源管理器中，连接到SQL Server数据库引擎实例，再展开该实例。

(2) 展开“数据库”，选择要创建存储过程的数据库，展开“可编程性”，右键单击“存储过程”，再选择“新建存储过程”，如图9-14所示。

(3) 在“查询”菜单上，选择“指定模板参数的值”（见图9-15（a））。在“指定模板参数的值”对话框中，“值”列包含参数的建议值。接受这些值或将其替换为新值，再单击“确定”按钮，如图9-15（b）所示。

(4) 在查询编辑器中，使用过程语句替换SELECT语句，如图9-16所示。

①若要测试语法，在“查询”菜单上选择“分析”。

②若要运行存储过程，请在“查询”菜单上选择“执行”。

③若要保存脚本，请在“文件”菜单上选择“保存”。接受该文件名或将其替换为新的名称，再选择“保存”。

(5) 运行存储过程，在工具栏上单击“新建查询”，在查询窗口中，输入执行语句，或者右键单击所需的用户定义存储过程，然后选择“执行存储过程”，如图9-17所示。

具体的执行结果如图9-18所示。

2. 用T-SQL语句创建和执行存储过程

创建存储过程的过程实际是进行预编译，保存DB系统表中的语法分析、有效格式，生成查询树。

1）创建存储过程的语句

SQL Server 2012中提供的创建存储过程的语句是CREATE PROCEDURE。由于SQL Server 2012中有两种存储过程，本章主要介绍T-SQL存储过程的语法。具体的语法格式如下：

CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ]

[ {@parameter [ type_schema_name. ] data_type }

[ VARYING ] [ = default ] [ OUT | OUTPUT ] [READONLY]

] [ ,…n ]

[ WITH ＜procedure_option＞ [ ,…n ] ]

[ FOR REPLICATION ]

AS { [ BEGIN ] sql_statement [;] […n ] [ END ] }

[;]

＜procedure_option＞::=

[ ENCRYPTION ]

[ RECOMPILE ]

[ EXECUTE AS Clause ]

参数说明如下：

(1) schema_name：为过程所属架构的名称。

(2) procedure_name：为新存储过程的名称。

过程名称必须遵循有关标识符的规则，并且在架构中必须唯一。强烈建议不在过程名称中使用前缀 sp_。此前缀由SQL Server使用，以指定系统存储过程。

(3) number：为可选的整数，用于对同名的过程进行分组，以便用一条DROP PROCEDURE语句即可将同组的过程一起除去。

(4) @parameter：为过程中的参数，通过将at符号(@)作为第一个字符来指定参数名称。参数名称必须符合有关标识符的规则。 每个过程的参数仅用于该过程本身；其他过程中可以使用相同的参数名称。在存储过程中可声明一个或多个参数，最多可声明2100个（注意：如果指定了 FOR REPLICATION，则无法声明参数）。

(5) [ type_schema_name.] data_type：为参数的数据类型。

注意：所有T-SQL数据类型都可以作为参数。可以使用用户定义的表类型创建表值参数。表值参数只能是INPUT参数，并且这些参数必须带有READONLY关键字。cursor数据类型只能是OUTPUT参数，并且必须带有VARYING关键字。

(6) VARYING：指定作为输出参数支持的结果集（由存储过程动态构造，内容可以变化），仅适用于游标参数。

(7) default：为参数的默认值。如果定义了默认值，则不必指定该参数的值即可执行过程。默认值必须是常量或NULL。如果过程将对该参数使用LIKE关键字，那么默认值中可以包含通配符（%、_、[] 和 [^]）。

(8) OUT | OUTPUT：表明参数是返回参数。使用OUTPUT参数可将信息返回给调用过程。除非是CLR过程；否则，text、ntext和image参数不能作为OUTPUT参数。OUTPUT参数可以为游标占位符，CLR过程除外。

(9) READONLY：只读，指示不能在过程的主体中更新或修改参数。如果参数类型为表值类型，则必须指定 READONLY。

(10) FOR REPLICATION：表明复制创建该过程。使用FOR REPLICATION选项创建的过程可用于过程筛选器，且仅在复制过程中执行。如果指定了FOR REPLICATION，则无法声明参数。对于CLR过程，不能指定FOR REPLICATION。使用FOR REPLICATION创建的过程，要忽略RECOMPILE选项。

(11) { [ BEGIN ] sql_statement [;] [ …n ][ END ] }：构成过程主体的一条或多条T-SQL语句。可使用可选的 BEGIN 和 END 关键字将这些语句括起来。

(12) ENCRYPTION：指示 SQL Server 将 CREATE PROCEDURE 语句的原始文本转换为模糊格式。

(13) RECOMPILE：指示数据库引擎不缓存此过程的查询计划，强制在每次执行此过程时都对该过程进行编译。

(14) EXECUTE AS Clause：指定其中执行过程的安全上下文。

2）执行存储过程的语句

创建存储过程成功后，就要执行存储过程。执行存储过程实际上是进行编译、执行（依据查询树、统计信息、参数），确定访问路径、建立查询计划这一系列过程。SQL Server 2012中提供的执行存储过程的语句：使用EXECUTE或EXEC关键字。其具体的语法格式如下：

[ { EXEC | EXECUTE } ]

{

[ @return_status = ]

{ module_name [ ;number ] |@module_name_var }

[ [ @parameter = ] { value |@variable [ OUTPUT ] | [ DEFAULT ] } ]

[ ,…n ]

[ WITH ＜execute_option＞ [ , …n ] ]

}

[;]

＜execute_option＞::=

{

RECOMPILE

|{ RESULT SETS UNDEFINED }

|{ RESULT SETS NONE }

|{ RESULT SETS ( ＜result_sets_definition＞ [,…n ] ) }

}

＜result_sets_definition＞::=

{

(

{ column_name

data_type

[ COLLATE collation_name ]

[ NULL | NOT NULL ] }

[,…n ]

)

| AS OBJECT

[ db_name . [ schema_name ] . | schema_name . ]

{table_name | view_name | table_valued_function_name }

| AS TYPE [ schema_name.]table_type_name

| AS FOR XML

}

参数说明如下：

(1) @return_status：可选的整型变量，存储模块的返回状态。这个变量用于EXECUTE语句前，必须在批处理、存储过程或函数中声明过。

(2) module_name：为要调用的存储过程或标量值用户定义函数的完全限定或不完全限定名称。模块名称必须符合标识符规则。无论服务器的排序规则如何，扩展存储过程的名称总是区分大小写。

(3) ;number：可选整数，用于对同名的过程分组。该参数不能用于扩展存储过程。

(4) @module_name_var：为局部定义的变量名，代表模块名称。

(5) @parameter：为module_name的参数，与模块中定义的相同。参数名称前必须加上符号 (@)。与@parameter_name=value格式一起使用时，参数名称和常量不必按它们在模块中定义的顺序提供。但是，如果对参数使用了@parameter_name=value格式，则必须对所有后续参数都使用此格式。默认情况下，参数可为空值。

(6) value：传递给模块或传递给命令的参数值。如果参数名称没有指定，则参数值必须以模块中定义的顺序提供。如果在模块中定义了默认值，则用户执行该模块时可以不必指定参数。

(7) @variable：用于存储参数或返回参数的变量。

(8) OUTPUT：指定模块或命令字符串返回一个参数。该模块或命令字符串中的匹配参数必须已使用关键字OUTPUT创建。使用游标变量作为参数时使用该关键字。

①不能使用OUTPUT将常量传递给模块，返回参数需要变量名称。在执行过程之前，必须声明变量的数据类型并赋值。

②当对远程存储过程使用EXECUTE或对链接服务器执行传递命令时，OUTPUT参数不能是任何大型对象（LOB）数据类型。

③返回参数可以是LOB数据类型之外的任意数据类型。

(9) DEFAULT：根据模块的定义，提供参数的默认值。

(10) WITH ＜execute_option＞：可能的执行选项。不能在 INSERT…EXEC 语句中指定RESULT SETS选项。具体选项如表9-14所示。

(11) ＜result_sets_definition＞ ： 描 述 执 行 语 句 所 返 回 的 结 果集。 result_sets_definition 的子句的具体含义如表9-15所示。

注意：在执行过程中要根据存储过程类型的不同进行相关的限定。

3）执行系统存储过程

系统过程以前缀sp_开头。从逻辑意义上讲，因为这些过程出现在所有用户定义的数据库和系统定义的数据库中，所以可以从任何数据库执行这些过程，而不必完全限定过程名称。但是，建议使用sys架构名称对所有系统过程名称进行架构限定，以防止名称冲突。

4）执行用户定义存储过程

当执行用户定义的过程时，建议使用架构名称限定过程名称。这种做法使性能得到小幅提升，因为数据库引擎不必搜索多个架构。如果某个数据库在多个架构中具有同名过程，则可以防止执行错误的过程。

(1) 创建简单的不带参数的存储过程。

如果在教学管理系统中，教学秘书想要查询所有学生所学的各门课程的成绩。要求输出学生的学号、姓名、课程名和对应的课程的学习成绩。对于该要求，实际上就是要做一个内连接查询来完成。

【例9-16】使用带有复杂SELECT语句的存储过程：查询学生的考试成绩，编写语句。

解：该案例即为图9-16所示案例的T-SQL语句的写法。选择students数据库，新建一个查询，在查询窗口中输入以下语句：

CREATE PROCEDURE student_grade1

AS

BEGIN

SELECT s.sno,s.sname, cname,grade

FROM Student s INNER JOIN enroll sc

ON s.sno = sc.sno INNER JOIN course c

ON c.cno= sc.cno

END

执行：EXEC student_grade1

(2) 创建带输入参数的存储过程。

SQL Server 2012的存储过程可以使用两种类型的参数：输入参数和输出参数。参数用于在存储过程及应用程序之间交换数据，其中：输入参数允许用户将数据值传递到存储过程或函数；输出参数允许存储过程将数据值或游标变量传递给用户；每个存储过程向用户返回一个整数代码，如果存储过程没有显式设置返回代码的值，则返回代码为0。

存储过程的参数在创建时应在CREATE PROCEDURE和AS关键字之间定义，每个参数都要指定参数名和数据类型，参数名必须以@符号为前缀，可以为参数指定默认值；如果是输出参数，则应用OUTPUT关键字进行描述。各参数定义之间用逗号隔开。

输入参数，即指在存储过程中有一个条件，在执行存储过程时为这个条件指定值，通过存储过程返回相应的信息。使用输入参数可以向同一存储过程多次查找数据库。

例如，在教学管理系统中，某学生想要查询他的某门课程的成绩，由于学校的学生都有可能需要该功能，于是可以建立一个带参数的存储过程。以学生的姓名和课程名作为参数（前提是该课程的学生没有同名的），用于确认是哪个学生想查询哪一门课程的成绩。

思考案例：如果学习同一门课程的学生中有同名的学生，则该存储过程该如何实现？

【例9-17】使用带有输入参数的存储过程：查询某个学生某门课程的考试成绩，编写语句。

解：选择students数据库，新建一个查询，在查询窗口中输入以下语句。

CREATE PROCEDURE student_grade_param

@student_name char(10), @course_name char(20)

AS

SELECT sname,cname,grade

FROM student s INNER JOIN enroll sc

ON s.sno = sc.sno INNER JOIN course c

ON c.cno = sc.cno

WHERE sname =@student_name

AND cname =@course_name

存储过程建立完毕后，可以执行存储过程。执行带有输入参数的存储过程时，SQL Server 2012提供如下两种传递参数的方式。

①按位置传递。这种方式是在执行存储过程的语句中，直接给出参数的值。当有多个参数时，给出参数的顺序与创建存储过程语句中的参数的顺序一致，即参数传递的顺序就是参数定义的顺序。

②按参数名传递。这种方式是在执行存储过程的语句中，使用“参数名=参数值”的形式给出参数值。通过参数名传递参数的好处是，参数可以以任意顺序给出。

具体在执行例9-17存储过程时，新建一个查询窗口，输入执行语句。

a. 按参数位置传递值。

EXEC student_grade_param '胡峰', '数据库'

b. 按参数名传递值。

EXEC student_grade2 @student_name = '胡峰', @course_name='数据库'

执行结果如图9-19所示。

(3) 使用参数默认值创建存储过程。

执行带参数的存储过程时，如果没有指定参数，则系统运行就会出错；如果希望不给出参数时也能够正确运行，则可以通过设置参数默认值来实现。参数默认值可以是 NULL 值或其他的值。这种情况下，如果未提供参数，则 SQL Server 将根据存储过程的其他语句执行存储过程，不会显示错误信息。过程定义还可指定当不给出参数时要采取的其他某种措施。

存储过程在执行后都会返回一个整形值。如果执行成功，则返回0；否则返回－1到－99之间的随机数，也可以使用RETURN语句来指定一个存储过程的返回值。

例如，在教学系统中，每个学生都想查询他自己所学课程的成绩信息，学生的唯一标识是学号，则可以创建一个以学号为参数的存储过程进行信息的查询。可以给学号一个初始的默认值NULL。

【例9-18】使用带有默认值NULL参数的存储过程查询某个学生的所有课程的考试成绩，编写语句。

解：注意在实际应用系统中，重名的存储过程系统不让定义。为了能够顺利地定义新的存储过程，我们会先检查系统里有没有同名的存储过程，如果有，则将原来的同名的存储过程删除，采用新的定义。

USE students

GO

--检测是否有同名存储过程，如果有就删除该存储过程

IF OBJECT_ID(N'dbo.student_grade_param_default ', N'P')IS NOT NULL

DROP PROCEDURE dbo.student_grade_param_default ;

GO

CREATE PROCEDURE student_grade_param_default

@student_no char(10)=NULL

AS

IF @student_no IS NULL

BEGIN

PRINT '必须指定一个学生的学号！'

RETURN

END

ELSE

BEGIN

SELECT s.sno,sname,cname,grade

FROM student s ,enroll sc,course c

WHERE s.sno = sc.sno AND c.cno = sc.cno

AND s.sno =@student_no

END

GO

执行该存储过程时由于有默认值，所以可以不输入参数的值。执行结果如图9-20所示。

执行存储过程时也可以重新制定参数的值，执行结果如图9-21所示。

(4) 创建带有输入和输出参数的存储过程。

在存储过程中，除了定义输入参数外，还可以定义输出参数。通过定义输出参数，可以从存储过程中返回一个或多个值。为了使用输出参数，必须在CREATE PROCEDURE语句和EXECUTE语句中指定关键字OUTPUT。执行存储过程时，如果忽略OUTPUT关键字，则存储过程仍会执行但不返回值。

例如，在教学管理系统中，老师想要知道他所带课程的考试情况，想知道所带课程的考试平均分和最高分。对于该需求，由于课程的唯一标识是课程编号，所以建立该存储过程要一个输入型参数课程编号，然后两个输出型参数用于返回计算出的该门课程的平均分和最高分。

【例9-19】使用带有输入/输出参数的存储过程：计算某门课程考试的平均成绩和最高成绩，编写语句。

解：语句如下。

USE students

GO

CREATE PROCEDURE student_grade_param_inout

@course_no char(10),

@MAXgrade float OUTPUT,

@AVGgrade float OUTPUT

AS

SELECT @MAXgrade=max(grade),@AVGgrade=avg(grade)

FROM enroll sc

WHERE sc.cno=@course_no

GO

执行存储过程时，为了接收这个存储过程的返回值，需要两个变量来存放返回参数的值。执行例9-19存储过程，新建一个查询窗口，输入执行语句。由于该存储过程带有输出参数，所以在执行过程中要先定义两个参数，然后执行存储过程student_grade_param_inout，最后打印结果信息。语句如下：

declare @MAXgrade float

declare @AVGgrade float

EXEC student_grade_param_inout 'c2',@MAXgrade output ,@AVGgrade output

print'该门课程的平均成绩为：'+RTRIM(CAST(@MAXgrade as char(10)))

print'该门课程的最高成绩为：'+RTRIM(CAST(@AVGgrade as char(10)))

具体的执行结果如图9-22所示。

【例9-20】编写使用带有默认值输入参数、输出参数的存储过程：查询某个系的学生人数，默认查询计算机系的学生。

解：程序如下。

USE students

GO

IF OBJECT_ID(N'dbo.student_dept_stucount ', N'P')IS NOT NULL

DROP PROCEDURE dbo.student_dept_stucount ;

GO

CREATE PROCEDURE student_dept_stucount

@sdept char(10)='计算机' ,

@sount int output

AS

SELECT @sount=COUNT(sno) FROM student

GROUP BY department

HAVING department=@sdept

IF @sount IS NULL

print RTRIM(@sdept)+'没有学生！'

ELSE

BEGIN

print RTRIM(@sdept)+'系的学生数为：'+CONVERT(varchar(6),@sount)

END

GO

执行存储过程时，为了接收这个存储过程的返回值，需要一个变量来存放返回参数的值。执行例9-20存储过程，新建一个查询窗口，输入执行语句。在执行过程中要先定义一个参数，而输入参数已经有默认值，单独说明输出参数即可；然后执行存储过程student_ dept_stucount；最后打印结果信息。语句如下：

declare @count int

EXEC student_dept_stucount @sount =@count output

具体的执行结果如图9-23所示。

(5) 利用存储过程执行DML操作。

在教学管理系统中，一条条地输入学生的所学课程的成绩信息是比较麻烦的过程，要求创建一个存储过程，以简化对成绩表（enroll）的数据添加工作。当执行该存储过程时，其参数值（@student_no,@course_no,@ grade）可作为数据添加到表中（其数据类型为char(10) ,char(10) , numeric(9, 0) ）。

【例9-21】编写存储过程，进行数据的插入操作：插入学生的课程成绩。

解：语句如下。

CREATE PROCEDURE enroll_grade_insert

@student_no char(10) ,

@course_no char(10) ,

@ grade numeric(9, 0)

AS

BEGIN

INSERT INTO enroll (sno,cno,grade)

VALUES(@student_no,@course_no,@ grade)

END

执行存储过程可以通过以下方法执行：

EXECUTE enroll_grade_insert '95012','c1',85

当然，在执行过程中变量可以显式命名：

EXEC enroll_grade_insert @student_no = '95012', @course_no ='c1', ,@ grade =85

具体的执行结果如图9-24所示。

再次查看成绩表，发现该成绩已经插入，如图9-25所示。

(6) 游标和存储过程的综合应用。

T-SQL 存储过程只能将 cursor 数据类型用于 OUTPUT 参数。如果为某个参数指定了cursor数据类型，则必须指定VARYING和OUTPUT参数。如果为某个参数指定了VARYING关键字，则数据类型必须是cursor，并且必须指定OUTPUT关键字。

例如，在教学管理系统中，班主任想要查询所有学生的所有课程的成绩信息。由于学校的学生都有可能需要该功能，所以我们可以建立一个带参数的存储过程，输入学生的学号。前面已经建立的例9-17和例9-19都有类似的功能，但是该存储过程只能查询单一的学生的信息，如果现在要了解全部或其中的一部分学生的信息，那么需要处理多行数据，这时就要用到游标。

【例9-22】查询不同系的学生的各门课的成绩，编写程序。

解：程序如下。

USE students

GO

IF OBJECT_ID(N'dbo.student_grade_department ', N'P')IS NOT NULL

DROP PROCEDURE dbo.student_grade_department ;

GO

CREATE PROCEDURE dbo.student_grade_department

@sdept char(10)='计算机' ,

@department_gradeCursor cursor VARYING OUTPUT

AS

SET NOCOUNT ON;

SET @department_gradeCursor = CURSOR

forward_only STATIC FOR

SELECT s.sno,sname,cname,Grade

FROM student s ,enroll sc,course c

WHERE s.sno = sc.sno AND c.cno = sc.cno AND s.department =@sdept

OPEN @department_gradeCursor;

GO

含有游标的存储过程建立完毕后，接下来要调用存储过程。首先要声明一个游标变量，由于游标的查询结果集里有四个字段，所以还要声明四个和对应字段类型相同的变量，以便在打开游标后接受里面的对应数据。然后将游标里的数据一条条地取出来进行打印输出。最后，游标处理完毕后关闭游标，释放资源。程序如下：

declare @exec_cur cursor

declare @sno char(10), @sname char(10), @cname char(10), @grade

numeric(9,0)

exec student_grade_department @department_gradeCursor =@exec_cur output;

fetch next FROM @exec_cur into @sno, @sname, @cname, @grade;

while (@@fetch_status = 0)

begin

fetch next from @exec_cur into @sno, @sname, @cname,@grade;

print '学号: ' +@sno + ', 姓名: ' +@sname + ', 课程名: ' +@cname+ ', 成绩: '+convert(char, @grade);

end

close @exec_cur;

deallocate @exec_cur;

具体的执行结果如图9-26所示。