数据库应用技术PPT 第8章

《数据库应用技术PPT 第8章》由会员分享，可在线阅读，更多相关《数据库应用技术PPT 第8章（38页珍藏版）》请在金锄头文库上搜索。

1、138,第8章 存储过程和触发器,8.1 存储过程概述,8.2 存储过程的创建和执行,8.3 管理存储过程,8.4 系统存储过程和扩展存储过程,8.5 触发器简介,8.6 创建触发器,8.7 管理触发器,8.8 触发器使用注意事项,238,8.1 存储过程概述,8.1.1 存储过程的概念 存储过程是一种数据库对象，是存放在服务器上的、预先编译好的T-SQL语 句的集合，它以一个名称存储在数据库中，并作为一个单元来处理。存储过程允许 用户声明变量和有条件地执行，允许包含程序流、逻辑以及对数据库进行查询，可 以接受输入参数和输出参数，还可以返回单个或多个结果。存储过程可以由应用程 序通过一个调用来

2、执行，它是封装重复性工作的一种有效方法，使用存储过程，可 以使数据库的管理工作变得更加容易。 8.1.2 存储过程的优点 存储过程具有以下优点： 1 增强代码的可重用性，提高开发效率 存储过程被创建以后，可以在程序中被多次调用，而不需要重新编写该存储过程，从而实现了代码的重用性，提高了开发效率，而且对存储过程的修改也不会影响到应用程序，从而极大地提高了程序的可移植性。 2 执行速度快 一般的T-SQL语句每次执行时都需要进行编译和优化，而存储过程是经过预编译的，在创建时就经过了语法检查和性能优化，在执行时不需要再重复这些步骤，因此使用存储过程可以提高执行速度。,338,8.1 存储过程概述,3

3、 可以减少网络流量 由于存储过程存储在服务器并在服务器上运行，因此调用存储过程时，在服务器和客户端之间的网络上传送的只是调用存储过程的语句和存储过程执行完毕返回的结果，用户不需要在网络中传送大量的T-SQL语句，或是将大量的数据从服务器下载到客户端再进行处理。因此使用存储过程可以大大地减少网络流量，降低网络负载。 4 可以提高数据的安全性 系统管理员可以只给用户授予访问存储过程的权限，而不授予访问存储过程所涉及的表或视图的权限，这样使用户只能通过存储过程来操作数据库中的数据，而不能直接操作有关的表，从而保证数据库中数据的安全性。 8.1.3 存储过程的分类 SQL Server支持的存储过程主

4、要有系统存储过程、本地存储过程、临时存储过 程和扩展存储过程。,438,8.2 存储过程的创建和执行,8.2.1 创建存储过程 1使用企业管理器创建存储过程 在企业管理器中创建存储过程的步骤如下： 打开企业管理器。展开要建立存储过程的数据库，右击【存储过程】节点，在快捷菜单中单击【新建存储过程】命令；还可以直接右击要建立存储过程的数据库，在快捷菜单中单击【新建】【存储过程】，如图8.2所示。 在【存储过程属性】对话框的【文本】框中输入创建存储过程的T-SQL语句。 可以单击【存储过程属性】对话框的【检查语法】按钮，以检查存储过程是否存在语法问题。 单击【存储过程属性】对话框的【确定】按钮。,5

5、38,8.2 存储过程的创建和执行,2 使用T-SQL语句创建存储过程 格式：CREATE PROCEDURE 存储过程名 参数 数据类型=默认值 OUTPUT ， WITH ENCRYPTION AS SQL语句块 功能：按要求建立存储过程。 说明： 此语句必须作为一个独立的批处理。 存储过程可以声明一个或多个参数，如果有参数，在调用存储过程时必须提供 每个参数的值（除非指定了该参数的默认值）。 “数据类型”用于指定参数的数据类型。 “默认值”用于指定参数的默认值，如果指定了默认值，在调用存储过程时不必 指定该参数的值，默认值必须是常量或NULL。 如果有“OUTPUT”，表示该参数是返回参

6、数，使用“OUTPUT”可以将数据返回给调 用过程。 如果有“WITH ENCRYPTION”，表示对存储在系统表“syscomments”中的存储过程 文本进行加密，这样可以防止他人查看存储过程的定义文本。 “SQL语句块”是指存储过程中包含的若干个SQL语句。,638,8.2 存储过程的创建和执行,【例8.1】 在“图书管理”数据库中建立一个名为“图书出版情况”的存储过程，该 存储过程从“图书”、“作者”和“出版社”表中检索所有图书的“图书名”、“作者名” 和相应的“出版社名”。 USE 图书管理 GO CREATE PROCEDURE 图书出版情况 AS SELECT 图书名,作者名,出

7、版社名 FROM 图书 AS A INNER JOIN 作者 AS B ON A.作者号=B.作者号 INNER JOIN 出版社 AS C ON A.出版社号=C.出版社号 GO -执行存储过程 EXEC 图书出版情况 GO,738,8.2 存储过程的创建和执行,【例8.2】 在“图书管理”数据库中建立一个名为“作者查询”的存储过程，该存储过程 从“作者”、“图书”和“出版社”表中检索指定作者的“作者名”，以及该作者出版图书 的“图书名”和相应的“出版社名”。要求将作者姓名通过参数传递给存储过程。 USE 图书管理 GO CREATE PROCEDURE 作者查询 name varchar(

8、10) AS SELECT 作者名,图书名,出版社名 FROM 作者 AS A INNER JOIN 图书 AS B ON A.作者号=B.作者号 INNER JOIN 出版社 AS C ON B.出版社号=C.出版社号 WHERE 作者名=name GO -执行存储过程 EXEC 作者查询 任明 GO,838,8.2 存储过程的创建和执行,8.2.2 执行存储过程 存储过程创建成功以后，可以使用EXECUTE语句来执行。 格式：EXECUTE 存储过程名 参数=值 | 变量 OUTPUT ， 功能：执行指定的存储过程。 说明： EXECUTE语句可以用于执行系统存储过程、用户定义存储过程和扩

9、展存储过程，如果此语句是批处理中的第一条语句，则可以省略“EXECUTE”。 如果使用“参数=值”的形式传递参数，则所有参数都必须使用此格式，且可以按任意顺序提供参数，还可以省略已经提供默认值的参数；如果不是以“参数=值”的形式传递参数，则必须按照“CREATE PROCEDURE”语句给出的顺序来提供参数。 【例8.3】 执行例7.20建立的存储过程“图书出版情况” EXEC 图书出版情况 GO,938,8.3 管理存储过程,8.3.1 查看存储过程 1 使用企业管理器查看存储过程 使用企业管理器查看存储过程的定义 操作步骤如下： 打开企业管理器。 展开存储过程所在的数据库（例如展开“图书管

10、理”数据库），单击【存储过程】节点，则在右边窗格中会显示该数据库中所有的存储过程，如图8.4所示。 右击要查看源代码的存储过程（例如右击“图书出版情况”），在快捷菜单中单击【属性】命令，则会打开【存储过程属性】对话框，可在此对话框中查看存储过程的源代码。如图8.5所示。,1038,8.3 管理存储过程,2 使用系统存储过程查看存储过程 可以使用企业管理器查看存储过程，还可以使用系统存储过程查看存储过程的有关信息。 查看存储过程的定义 格式：sp_helptext 存储过程名 功能：查看存储过程的源代码。 【例8.5】 查看存储过程“图书出版情况”的定义。 USE 图书管理 -下一行语句不是批处

11、理中的第一条语句，因此必须加上“EXECUTE” EXEC sp_helptext 图书出版情况 GO 查看存储过程的参数 格式：sp_help 存储过程名 功能：查看存储过程的参数。 查看存储过程的相关性 格式：sp_depends 存储过程名 功能：查看存储过程的相关性。,1138,8.3 管理存储过程,8.3.2 修改存储过程 1 使用企业管理器修改存储过程 使用企业管理器修改存储过程的操作步骤与使用企业管理器查看存储过程的操作步骤相似： 打开企业管理器。 展开存储过程所在的数据库，单击【存储过程】节点，则在右边窗格中会显示该数据库中所有的存储过程，如图8.4所示。 右击要修改的存储过程

12、，在快捷菜单中单击【属性】命令，则会打开【存储过程属性】对话框，如图8.5所示。 在【存储过程属性】对话框的【文本】框中修改存储过程的源代码。 可以单击【存储过程属性】对话框的【检查语法】按钮，以检查存储过程是否存在语法问题。 单击【存储过程属性】对话框的【确定】按钮。,1238,8.3 管理存储过程,2使用T-SQL语句修改存储过程 格式：ALTER PROCEDURE 存储过程名 参数 数据类型=默认值 OUTPUT ， WITH ENCRYPTION AS SQL语句块 【例8.8】修改例7.21建立的存储过程“作者查询”，要求对此存储过程进行加密，其他不变。 ALTER PROCEDU

13、RE 作者查询 name varchar(10) WITH ENCRYPTION AS SELECT 作者名,图书名,出版社名 FROM 作者 AS A INNER JOIN 图书 AS B ON A.作者号=B.作者号 INNER JOIN 出版社 AS C ON B.出版社号=C.出版社号 WHERE 作者名=name GO EXEC 作者查询 任明 GO,1338,8.3 管理存储过程,8.3.3 重命名存储过程 1 使用企业管理器重命名存储过程 打开企业管理器。 展开存储过程所在的数据库（例如展开“图书管理”数据库），单击【存储过程】节点，则在右边窗格中会显示该数据库中所有的存储过程，

14、如图8.4所示。 两次单击要修改名称的存储过程名，或右击要修改名称的存储过程名，并在快捷菜单中单击【重命名】命令。 输入新的存储过程名，回车后会出现【重命名】对话框，单击【是】按钮。 2 使用系统存储过程重命名存储过程 修改存储过程的名称可以使用系统存储过程sp_rename。 格式：sp_rename 存储过程原名，存储过程新名 功能：将存储过程的原名更改为新名。 【例8.9】 将存储过程“图书出版情况”更名为“图书情况”。 USE 图书管理 GO sp_rename 图书出版情况,图书情况 GO,1438,8.3 管理存储过程,8.3.4 删除存储过程 1 使用企业管理器删除存储过程 打开

15、企业管理器。 展开存储过程所在的数据库（例如展开“图书管理”数据库），单击【存储过程】节点，则在右边窗格中会显示该数据库中所有的存储过程。 右击要删除的存储过程，在快捷菜单中单击【删除】命令，则会出现【除去对象】对话框，在【除去对象】对话框中单击【全部除去】按钮。 2 使用T-SQL语句删除存储过程 格式：DROP PROCEDURE 存储过程名， 功能：删除指定的存储过程。 【例8.10】使用T-SQL命令删除存储过程“图书情况”。 USE 图书管理 GO DROP PROCEDURE 图书情况 GO,1538,8.4 系统存储过程和扩展存储过程,8.4.1 系统存储过程 SQL Serve

16、r提供了大约650个系统存储过程，它们可以帮助用户从系统表中读取 信息和管理数据库，并执行更新系统表的其他任务。系统存储过程大部分以“sp_”开 头。常用的系统存储过程如表8-1所示。,1638,8.4 系统存储过程和扩展存储过程,8.4.2 扩展存储过程 扩展存储过程扩展了SQL Server的功能，使其能够完成数据库服务器通常无法完 成的工作。扩展存储过程是使用C语言等编程语言编写的外部程序，以动态链接库 （DLL）的形式存储在服务器上，SQL Server可以动态地装载并执行它们。对于普 通用户来说，执行扩展存储过程和执行普通存储过程的方法完全一样，可以向扩展 存储过程传递参数，扩展存储过程也可以返回结果或状态。常用的扩展存储过程如 表8-2所示。,1738,8.5 触发器简介,8.