第10章 存储过程-SQL Server 数据库教程(第2版)-赵清华大学出版

第10章 存储过程
10.1 存储过程概述 10.2 存储过程的创建 10.3 存储过程的使用 10.4 存储过程的管理 10.5 综合训练
SQL Server 数据库教程 (第2版)
(SQL Server 2012)
1
10.1 存储过程概述
存储过程(Stored Procedure)是一组完成特定功能的T-SQL语 句集合，预编译后放在数据库服务器端，用户通过指定存储过 程的名称并给出参数(如果该存储过程带有参数)来执行存储过程 。本章介绍存储过程的特点和类型，存储过程的创建和执行， 存储过程的参数，存储过程的管理等内容。
(SQL Server 2012)
7
•
10.2.2 通过T-SQL 语句创建存储过程
T-SQL创建存储过程的语句是CREATE PROCEDURE，其语法格 式如下：
语法格式：
CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ] /*定义过程名*/
1．用户存储过程
用户存储过程是用户数据库中创建的存储过程，完成用户 指定的数据库操作，其名称不能以sp_为前缀。用户存储过程包 括T-SQL存储过程和CLR存储过程。
（1）T-SQL存储过程
T-SQL 存储过程是指保存的 T-SQL 语句集合，可以接受和 返回用户提供的参数，本书将T-SQL存储过程简称为存储过程。
[ { @parameter [ type_schema_name. ] data_type } /*定义参数的类型*/ [ VARYING ] [ = default ] [ OUT | OUTPUT ] [READONLY] ][ ,...n ]
/*定义参数的属性*/ [ WITH {[ RECOMPILE ] [,] [ ENCRYPTION ] }]
SQL Server 数据库教程 (第2版)
(SQL Server 2012)
6
•
10.2.1 通过图形界面方式创建存储过程
（4）右单击“存储过程”选项， 在弹出的快捷菜单中选择 “刷新”命令，下方出现 dbo.sco_avg存储过程，至此 完成存储过程的创建，如图 10.4所示。
SQL Server 数据库教程 (第2版)
● sql_statement：包含在过程中的一个或多个 T-SQL 语句。
SQL Server 数据库教程 (第2版)
(SQL Server 2012)
9
10.2.2 通过T-SQL 语句创建存储过程
【例10.2】在stsc 数据库上，设计一个存储过程stu_score，用 于查找全部学生的成绩状况。
● number；可选整数，用于对同名的过程分组。
● @parameter：存储过程中的形参(形式参数的简称)，可以声明一个或多 个形参，将@用作第一个字符来指定形参名称，且必须符合有关标识符的规则 ，执行存储过程应提供相应的实参(实际参数的简称)，除非定义了该参数的默 认值。
● data_type：形参的数据类型，所有数据类型都可用作形参数据类型。
数据库”节点，选中“stsc”数据库，展开
该数据库节点，展开“可编程性”节点
，右单击“存储过程”选项，在弹出的
快捷菜单中选择“新建存储过程”命令
，出现存储过程脚本编辑窗口，如图
10.1所示
SQL Server 数据库教程 (第2版)
(SQL Server 2012)
4
•
10.2.1 通过图形界面方式创建存储过程
（2）出现存储过程模板，如图10.2所示。
SQL Server 数据库教程 (第2版)
(SQL Server 2012)
5
10.2.1 通过图形界面方式创建存储过程
（3）在该窗口中输入要创建的存储过程语句，输入完成后单击 按钮，系统提示“命令已成功完成”，如图10.3所示。
CREATE PROCEDURE sco_avg AS BEGIN SET NOCOUNT ON SELECT AVG(grade) AS '102课程的平均分' FROM score WHERE cno='102' END GO
● VARYING：指定作为输出参数支持的结果集。
● default：参数的默认值，如果定义了 default 值，则无需指定相应的实 参即可执行过程。
● READONLY：指示不能在过程的主体中更新或修改参数。
● RECOMPILE：指示每次运行该过程，将重新编译。
● OUTPUT：指示参数是输出参数，此选项的值可以返回给调用 EXECUTE 的语句。
SQL Server 数据库教程 (第2版)
(SQL Server 2012)
3
10.2 存储过程的创建
10.2.1 通过图形界面方式创建存 储过程
【例10.1】通过图形界面方式创建 存储过程sco_avg，用于求102课程的平 均分。
（1）启动SQL Server Management
Studio，在对象资源管理器中，展开“
SQL Server 数据库教程 (第2版)
(SQL Server 2012)
2
•
10.1 存储过程概述
（2）CLR存储过程 CLR 存储过程是指对 Microsoft .NET Framework 公共语 言运行时 (CLR) 方法的引用，可以接受和返回用户提供的参 数。 2．系统存储过程 系统存储过程是由系统提供的存储过程，可以作为命令 执行各种操作。系统存储过程定义在系统数据库master中， 其前缀是sp_，它们为检索系统表的信息提供了方便快捷的 方法。系统存储过程允许系统管理员执行修改系统表的数据 库管理任务，可以在任何一个数据库中执行。 3．扩展存储过程 扩展存储过程允许使用编程语言（例如 C）创建自己的 外部例程，使用时需要先加载到SQL Server系统中，并且按 照使用存储过程的方法执行。
/*定义存储过程的处理方式*/
[ FOR REPLICATION ] AS <sql_statement> [;]
说明：
/*执行的操作*/
SQL Server 数据库教程 (第2版)
(L Server 2012)
8
10.2.2 通过T-SQL 语句创建存储过程
● procedure_name：定义的存储过程的名称。