《sqlserver创建存储过程和触发器》由会员分享，可在线阅读，更多相关《sqlserver创建存储过程和触发器（53页珍藏版）》请在金锄头文库上搜索。
1、第 7 章 创建存储过程和触发器,存储过程概述 创建存储过程 执行存储过程 查看、重命名和删除存储过程 创建带有参数的存储过程 触发器概述 DML触发器的创建和应用 DDL触发器的创建和应用 嵌套触发器和递归触发器 查看、修改和删除触发器,存储过程概述,任务演示：小张的学校为进一步提高师资质量，开展了网上评教活动，通过学生反映老师的授课情况。活动结束后，教务部门将通过学生对老师的评教信息进行汇总，再对每位老师进行综合评定。这些信息将记录在数据库的 tblRemarks 表中，教务部门根据网上的数据开展工作。他们开始汇总每位老师的评价信息，该 tblRemarks 表是以学生评教ID为主键的，中
2、间存储的是同学对老师的不同信息。该表内容如下：,管理任务：从 tblRemarks 表中可以看，教务处如果直接对其汇总工作量很大，因为他们必须逐一统计教师的信息。为减轻负担，小张以 tblRemarks表收集的数据为基础，以每位老师作为一条记录进行存储，以便汇总。你能想到什么方案呢？小张想到了如下几种方案： 1、直接对 tblRemarks 表进行修改。（好与坏？） 2、创建一个用户定义函数，以实现参数化的视图功能；（好与坏？） 3、创建一个存储过程，以实现对现有的评教信息进行汇总。（好与坏？）,小张选择了第三种方案，创建了spStatRemarks存储过程，在过程中创建了tblStatRem
3、arks表，并将其插入统计后的评教信息。该表以教师ID为主键，以实现各位教师评分记录的单一性。,存储过程：为完成特定的功能而汇集在一起的一组SQL程序语句，经编译后存储在数据库中的SQL程序。,提问：在程序开发中，有时需要编写数百行T-SQL 语句来访问数据库中的数据，这些代码在程序中不仅破坏了程序的可读性，而且为将来应用程序的修改和维护带来很多不便。有没有种方法能封装这些语句，使其作为一个集合出现呢？,存储过程的类型：在 Microsoft SQL Server 2005中有多种可用的存储过程。本节简要介绍每种存储过程。,1、用户定义的存储过程,存储过程是指封装了可重用代码的模块或例程。存储
4、过程可以接受输入参数、向客户端返回表格或标量结果和消息、调用数据定义语言 （DDL） 和数据操作语言 （DML） 语句，然后返回输出参数。,2、系统存储过程：,SQL Server 中的许多管理活动都是通过一种特殊的存储过程执行的，这种存储过程被称为系统存储过程。例如，sys.sp_changedbowner 就是一个系统存储过程。,3、扩展存储过程：,SQL Server 支持在 SQL Server 和外部程序之间提供一个接口以实现各种维护活动的系统存储过程。这些扩展存储程序使用 xp_ 前缀。,存储过程的优点：在 SQL Server 中使用存储过程而不使用存储在客户端计算机本地的 Tr
5、ansact-SQL 程序的优点包括： （1）存储过程已在服务器注册。（2）存储过程具有安全特性（例如权限）和所有权链接，以及可以附加到它们的证书。,（3）存储过程可以强制应用程序的安全性。（4）存储过程允许模块化程序设计。 （5）存储过程是命名代码，允许延迟绑定。（6）存储过程可以减少网络通信流量。,创建存储过程：,在SQL Server中，可以使用三种方法创建存储过程： （1）使用 T-SQL 语句创建存储过程； （2）使用创建存储过程模板创建存储过程； （3）利用SQL Server 管理平台创建存储过程。,使用 T-SQL 语句创建存储过程：存储过程是使用 CREATE PROCEDU
6、RE 语句创建的。它们只能创建在当前的数据库中，但临时的存储过程除外，它们创建在 tempdb 数据库中。,使用 T-SQL 语句创建存储过程的语法：,CREATE PROC|PROCEDURE schema_name.procedure_name;numberparameter type_schema_name. data_typeVARYING =default OUTPUT,.nWITH ,.nFOR REPLICATION AS;.n|; :=ENCRYPTION RECOMPILE EXECUTE_AS_Clause,- 创建名为Production.LongLeadProducts
7、 的存储过程， - 实现在Production.Product 表中查询制造时间在一天以上的所有产品的名称及产品号的功能 CREATE PROCEDURE Production.LongLeadProducts ASSELECT Name, ProductNumberFROM Production.ProductWHERE DaysToManufacture = 1 GO,举例9：在 Production 架构中创建名为 LongLeadProducts 的存储过程，该过程返回制造时间在一天以上的所有产品的行集。,使用创建存储过程模板创建存储过程：在SQL Server 管理平台中，选择工具栏
8、中的模板资源资源管理器，出现模板资源管理器窗口，选择存储过程（stored procedure）中的创建存储过程选项，如图所示。在文本框中可以输入创建存储过程的Transact_SQL语句，单击【执行】按钮，即可创建该存储过程。,利用SQL Server 管理平台创建存储过程：在SQL Server管理平台中，展开指定的服务器和数据库，然后展开可编程性，右单击存储过程选项，在弹出的快捷菜单中选择新建存储过程选项，如图所示，出现创建存储过程窗口。,在文本框中输入创建存储过程的 Transact_SQL 语句，单击【执行】按钮，即可创建该存储过程。,举例10：在 adventureworks 数据
9、库中创建一个带有SELECT语句的简单过程（ au_infor_all ），该存储过程返回所有员工姓名，Email地址，电话。该存储过程不使用任何参数。 程序清单如下： USE adventureworks GO CREATE PROCEDURE au_infor_all AS SELECT lastname, firstname, emailaddress, phone FROM person.contact GO,创建存储过程的准则：,限定存储过程所引用的对象名称,每个任务创建一个存储过程,创建，测试存储过程，并对其进行故障诊断,存储过程名称避免使用 sp_ 前缀,对所有存储过程使用相同的
10、连接设置,尽可能减少临时存储过程的使用,执行存储过程：,可以使用 Transact-SQL EXECUTE 语句来运行存储过程。执行存储过程必须具有执行存储过程的权限许可，才可以直接执行存储过程，执行存储过程可使用EXECUTE命令来执行，语法形式如下：EXECUTE return_status= procedure_name;number|procedure_name_var parameter=value|variableOUTPUT|DEFAULT ,.n WITH RECOMPILE ,针对例9：调用执行存储过程的示例： 调用 LongLeadProducts 存储过程。,EXECUT
11、E Production.LongLeadProducts,针对例10：执行存储过程au_infor_all。,EXECUTE（EXEC） au_infor_all,查看、修改、重命名和删除存储过程：,使用SQL Server管理平台查看用户创建的存储过程； 使用系统存储过程来查看用户创建的存储过程 。,使用SQL Server管理平台查看用户创建的存储过程：,在SQL 平台中，展开指定的服务器和数据库，选择并依次展开可编程性存储过程，然后右击要查看的存储过程名称，如图所示，从弹出的快捷菜单中，选择编写存储过程脚本为CREATE到新查询编辑器窗口，则可以看到存储过程的源代码。,使用系统存储过程
12、来查看用户创建的存储过程 ：,可供使用的系统存储过程及其语法形式如下：sp_help，用于显示存储过程的参数及其数据类型。 其语法为：sp_help objname= name,sp_helptext，用于显示存储过程的源代码。 其语法为：sp_helptext objname= namesp_depends，用于显示和存储过程相关的数据库对象。 其语法为：sp_depends objname=object,sp_stored_procedures，用于返回当前数据库中的存储过程列表。 其语法为：sp_stored_proceduressp_name=name ,sp_owner=owner
13、,sp_qualifier = qualifier,修改存储过程： 存储过程可以根据用户的要求或者基表定义的改变而改变。使用ALTER PROCEDURE 语句可以更改先前通过执行 CREATE PROCEDURE 语句创建的过程，但不会更改权限，也不影响相关的存储过程或触发器。,修改存储过程语法形式如下：ALTER PROCEDURE procedure_name;numberparameter data_typeVARYING=defaultOUTPUT,.n WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION FOR REPLICATIONAS
14、sql_statement .n ,举例11：创建了一个名为 proc_person 的存储过程，该存储过程包含姓名和 Email 地址信息。然后，用 ALTER PROCEDURE 重新定义了该存储过程，使之只包含姓名信息，并使用 ENCRYPTION 关键字使之无法通过查看syscomments表来查看存储过程的内容。 程序清单如下： USE adventureworks GO /*创建一个存储过程，该存储过程包含姓名和Email地址信息*/ CREATE PROCEDURE proc_person AS SELECT firstname, lastname, emailaddress F
15、ROM person.contact ORDER BY lastname, firstname GO,下面对该存储过程进行重新定义。使之只包含姓名信息，并使用 ENCRYPTION 关键字使之无法通过查看 syscomments 表来查看存储过程的内容。 程序清单如下： ALTER PROCEDURE proc_person WITH ENCRYPTION AS SELECT firstname, lastname FROM person.contact ORDER BY lastname, firstname GO,重命名存储过程： 1、修改存储过程的名称可以使用系统存储过程 sp_rena
16、me，其语法为：sp_rename 原存储过程名称，新存储过程名称 2、通过SQL Server管理平台也可以修改存储过程的名称。在SQL 管理平台中，右击要操作的存储过程名称，从弹出的快捷菜单中选择重命名选项，当存储过程名称变成可输入状态时，就可以直接修改该存储过程的名称。删除存储过程： 1、删除存储过程可以使用DROP命令，DROP命令可以将一个或者多个存储过程或者存储过程组从当前数据库中删除，其语法形式如下：drop procedure procedure,n 2、利用SQL 管理平台删除存储过程。在SQL Server管理平台中，右击要删除的存储过程，从弹出的快捷菜单中选择删除选项，则会弹出删除对象对话框，在该对话框中，单击“确定”按钮，即可完成删除操作。,
