第8章存储过程的操作与管理 SQL SERVER2005入门与实例教程电子工业出版社2006
第8章 存储过程的操作与管理
存储过程概述 存储过程是为完成特定的功能而汇集在一起的一组SQL程序 语句,经编译后存储在数据库中的SQL程序。 在 SQL Server中使用存储过程而不使用存储在客户端计算 机本地的 Transact-SQL程序的优点包括: (1)存储过程已在服务器注册 2)存储过程具有安全特性(例如权限)和所有权链接,以及 可以附加到它们的证书。 (3)存储过程可以强制应用程序的安全性 (4)存储过程允许模块化程序设计 (5)存储过程是命名代码,允许延迟绑定 (6)存储过程可以减少网络通信流量 SQL SERVER2005入门与实例教程电子工业出版社2006
存储过程概述 存储过程是为完成特定的功能而汇集在一起的一组SQL程序 语句，经编译后存储在数据库中的SQL程序。 在 SQL Server 中使用存储过程而不使用存储在客户端计算 机本地的 Transact-SQL 程序的优点包括： （1）存储过程已在服务器注册。 （2）存储过程具有安全特性（例如权限）和所有权链接，以及 可以附加到它们的证书。 （3）存储过程可以强制应用程序的安全性。 （4）存储过程允许模块化程序设计。 （5）存储过程是命名代码，允许延迟绑定。 （6）存储过程可以减少网络通信流量
8.1创建存储过程 在 SQL Server中,可以使用两种方法创建存储过程: (1)使用创建存储过程模板创建存储过程; (2)利用 SQL Server管理平台创建存储过程。 当创建存储过程时,需要确定存储过程的三个组成部 分: (1)所有的输入参数以及传给调用者的输出参数 (2)被执行的针对数据库的操作语句,包括调用其他 存储过程的语句 (3)返回给调用者的状态值,以指明调用是成功还是 失败。 SQL SERVER2005入门与实例教程电子工业出版社2006
8.1 创建存储过程 在SQL Server中，可以使用两种方法创建存储过程： （1）使用创建存储过程模板创建存储过程； （2）利用SQL Server 管理平台创建存储过程。 当创建存储过程时，需要确定存储过程的三个组成部 分： （1）所有的输入参数以及传给调用者的输出参数。 （2）被执行的针对数据库的操作语句，包括调用其他 存储过程的语句。 （3）返回给调用者的状态值，以指明调用是成功还是 失败
8.1创建存储过程 CREatE ProcedUre的语法形式如下: CREATE(PROCPROCEDUREJ [schema name ] procedure name; number K@parameter[type schema name. data typel TVARYINGIdefault[[OUT[PUTILn WITHL.n] [FOR REPLICATION] AS k[[ n]l; procedure option>∷ TENCRYPTIONIRECOMPILE]EXECUTE AS Clause] sql statement>∷= I BEGIN] statements [ENDI] : :=EXTERNAL NAME assembly name class name. method_ name SQL SERVER2005入门与实例教程电子工业出版社2006
8.1 创建存储过程 •CREATE PROCEDURE的语法形式如下： CREATE {PROC|PROCEDURE} [schema_name.]procedure_name[;number] [{@parameter[type_schema_name.] data_type} [VARYING][=default][[OUT[PUT]][,...n] [WITH [,...n] [FOR REPLICATION] AS {[;][...n]|}[;] ::= [ENCRYPTION][RECOMPILE] EXECUTE_AS_Clause] ::= {[BEGIN] statements [END]} ::= EXTERNAL NAME assembly_name.class_name.method_name
8.1.1使用模板创建存储过程 1)在 SQL Server管理平台中,选择“视图(Vew)”菜单中的“模板资 源资源管理器( Template Explorer)’,出现“模板资源管理器( Template Explorer)”窗口,选择“存储过程”中的“创建存储过程”选项,如图8-1所 (2)在文本框中可以输入创建存储过程的 Transact SQL语句,单击“执行” 按钮,即可创建该在储讨程 Create bsste stored procedure template Drop stored procedure if it already exia SELECT ON INFORMATICN SCHEMA, ROUTINE ECIFIC NAME N" BELECT 8p1, 8p2 EXECUTE Schema Nr yanase, schema Name>.salt 图8-1创建存储过程模板 SQL SERVER2005入门与实例教程电子工业出版社2006
8.1.1 使用模板创建存储过程 （1）在SQL Server 管理平台中，选择“视图（View）”菜单中的“模板资 源资源管理器(Template Explorer)”，出现“模板资源管理器(Template Explorer)”窗口，选择“存储过程”中的“创建存储过程”选项，如图8-1所 示。 （2）在文本框中可以输入创建存储过程的Transact_SQL语句，单击“执行” 按钮，即可创建该存储过程。 图8-1 创建存储过程模板
8.1.2使用管理平台创建存储过程 在 SQL Server管理平台中,展开指定的服务器和数据库,然后展开程序,右击存储过程 选项,在弹出的快捷菜单中依次选择“新建→存储过程..”选项,如图8-2所示,出现创建存储 程窗口。 2)在文本框中可以输入创建存储过程的 Transact SQL语句,单击“执行”按钮,即可创建 该存储过程。 0日工0m00的的 立建查博0B出园B日B兰 20x 数 -shitc-p Edba Thig blook ot w:l: not be inc luded 10 deran⊥t⊥an - CreAte t无:<Cr 系统储过程 CREATE PRCCEDUREPEoceeure Nem, m一 国团口口 压U安 SET NO 已连 图8-2新建存储过程 SQL SERVER2005入门与实例教程电子工业出版社2006
8.1.2使用管理平台创建存储过程 （1）在SQL Server管理平台中，展开指定的服务器和数据库，然后展开程序，右击存储过程 选项，在弹出的快捷菜单中依次选择“新建→存储过程…”选项，如图8-2所示，出现创建存储 过程窗口。 （2）在文本框中可以输入创建存储过程的Transact_SQL语句，单击“执行”按钮，即可创建 该存储过程。 图8-2 新建存储过程
8.1.2使用管理平台创建存储过程 例8-1创建一个带有 SELECT语句的简单过程,该存储 过程返回所有员工姓名,Emai地址,电话等。该存储过 程不使用任何参数 程序清单如下。 USE adventureworks GO CREATE PROCEDURE au infor all AS SELECT lastname, firstname, emailaddress, phone FROM person contact GO SQL SERVER2005入门与实例教程电子工业出版社2006
8.1.2使用管理平台创建存储过程 例8-1 创建一个带有SELECT语句的简单过程，该存储 过程返回所有员工姓名，Email地址，电话等。该存储过 程不使用任何参数 程序清单如下。 USE adventureworks GO CREATE PROCEDURE au_infor_all AS SELECT lastname, firstname, emailaddress, phone FROM person.contact GO
8.1.2使用管理平台创建存储过程 例8-2创建一个存储过程,以简化对SC表的数据添加工 使得在执行该存储过程时,其参数值作为数据添加 到表中 程序清单如下 CREATE PROCEDURE [dbo][ pr1 sc ins] @Param1 char(10),@Param2 char(2),@Param3 real AS BEGIN insert into sc(sno, cno, score) values(@Param1,@Param2, @Param3) END SQL SERVER2005入门与实例教程电子工业出版社2006
8.1.2使用管理平台创建存储过程 例8-2 创建一个存储过程，以简化对sc表的数据添加工 作，使得在执行该存储过程时，其参数值作为数据添加 到表中。 程序清单如下： CREATE PROCEDURE [dbo].[ pr1_sc_ins] @Param1 char(10),@Param2 char(2),@Param3 real AS BEGIN insert into sc(sno,cno,score) values(@Param1,@Param2,@Param3) END
8.1.2使用管理平台创建存储过程 例8-3创建一个带有参数的简单存储过程,从视图中返回指定 的雇员(提供名和姓)及其职务和部门名称,该存储过程接受 与传递的参数精确匹配的值 程序清单如下。 USE AdventureWorks GO CREATE PROCEDURE GetEmployees @lastname varchar(40) @firstname varchar(20) AS SELECT LastName, FirstName, Job Title, Department FROM HumanResources. VEmployeeDepartment WHERE FirstName=@firstname AND LastName = @lastname GO SQL SERVER2005入门与实例教程电子工业出版社2006
8.1.2使用管理平台创建存储过程 例8-3 创建一个带有参数的简单存储过程，从视图中返回指定 的雇员（提供名和姓）及其职务和部门名称，该存储过程接受 与传递的参数精确匹配的值 程序清单如下。 USE AdventureWorks; GO CREATE PROCEDURE GetEmployees @lastname varchar(40), @firstname varchar(20) AS SELECT LastName, FirstName, JobTitle, Department FROM HumanResources.vEmployeeDepartment WHERE FirstName = @firstname AND LastName = @lastname; GO
8.1.2使用管理平台创建存储过程 例84下面的存储过程从表 person contact中返回指定的一些 员工姓名及其电话。该储过程对传递的参数进行模式匹配 如果没有提供参数,则使用预设的默认值(姓民以字母D开 头 程序清单如下 USE AdventureWorks GO CREATE PROCEDURE au infor2 @lastname varchar(40)=D%, @firstname varchar(20)=% AS SELECT firstname, lastname, phone FROM person contact WHERE firstname LIKE @firstname AND lastname LIKE @lastname GO SQL SERVER2005入门与实例教程电子工业出版社2006
8.1.2使用管理平台创建存储过程 例8-4下面的存储过程从表person.contact中返回指定的一些 员工姓名及其电话。该存储过程对传递的参数进行模式匹配。 如果没有提供参数，则使用预设的默认值（姓氏以字母D开 头） 程序清单如下。 USE AdventureWorks; GO CREATE PROCEDURE au_infor2 @lastname varchar(40) = 'D%', @firstname varchar(20) = '%' AS SELECT firstname, lastname, phone FROM person.contact WHERE firstname LIKE @firstname AND lastname LIKE @lastname GO