数据库应用技术 第8章新.pptVIP

第8章 存储过程和触发器 8.1 存储过程概述 8.1.1 存储过程的概念 存储过程是一种数据库对象，是存放在服务器上的、预先编译好的T-SQL语 句的集合，它以一个名称存储在数据库中，并作为一个单元来处理。存储过程允许 用户声明变量和有条件地执行，允许包含程序流、逻辑以及对数据库进行查询，可 以接受输入参数和输出参数，还可以返回单个或多个结果。存储过程可以由应用程 序通过一个调用来执行，它是封装重复性工作的一种有效方法，使用存储过程，可 以使数据库的管理工作变得更加容易。 8.1.2 存储过程的优点 存储过程具有以下优点： 1． 增强代码的可重用性，提高开发效率 存储过程被创建以后，可以在程序中被多次调用，而不需要重新编写该存储过程，从而实现了代码的重用性，提高了开发效率，而且对存储过程的修改也不会影响到应用程序，从而极大地提高了程序的可移植性。 2． 执行速度快 一般的T-SQL语句每次执行时都需要进行编译和优化，而存储过程是经过预编译的，在创建时就经过了语法检查和性能优化，在执行时不需要再重复这些步骤，因此使用存储过程可以提高执行速度。 8.1 存储过程概述 3． 可以减少网络流量 由于存储过程存储在服务器并在服务器上运行，因此调用存储过程时，在服务器和客户端之间的网络上传送的只是调用存储过程的语句和存储过程执行完毕返回的结果，用户不需要在网络中传送大量的T-SQL语句，或是将大量的数据从服务器下载到客户端再进行处理。因此使用存储过程可以大大地减少网络流量，降低网络负载。 4． 可以提高数据的安全性 系统管理员可以只给用户授予访问存储过程的权限，而不授予访问存储过程所涉及的表或视图的权限，这样使用户只能通过存储过程来操作数据库中的数据，而不能直接操作有关的表，从而保证数据库中数据的安全性。 8.1.3 存储过程的分类 SQL Server支持的存储过程主要有系统存储过程、本地存储过程、临时存储过 程和扩展存储过程。 8.2 存储过程的创建和执行 8.2.1 创建存储过程 1．使用企业管理器创建存储过程 在企业管理器中创建存储过程的步骤如下： ⑴ 打开企业管理器。展开要建立存储过程的数据库，右击【存储过程】节点，在快捷菜单中单击【新建存储过程】命令；还可以直接右击要建立存储过程的数据库，在快捷菜单中单击【新建】→【存储过程】，如图8.2所示。 ⑵ 在【存储过程属性】对话框的【文本】框中输入创建存储过程的T-SQL语句。 ⑶ 可以单击【存储过程属性】对话框的【检查语法】按钮，以检查存储过程是否存在语法问题。 ⑷ 单击【存储过程属性】对话框的【确定】按钮。 8.2 存储过程的创建和执行 2． 使用T-SQL语句创建存储过程 格式：CREATE PROCEDURE 存储过程名 [@参数 数据类型[=默认值] [OUTPUT] [，…]] [WITH ENCRYPTION] AS SQL语句块 功能：按要求建立存储过程。 说明： ⑴ 此语句必须作为一个独立的批处理。 ⑵ 存储过程可以声明一个或多个参数，如果有参数，在调用存储过程时必须提供 每个参数的值（除非指定了该参数的默认值）。 ⑶ “数据类型”用于指定参数的数据类型。 ⑷ “默认值”用于指定参数的默认值，如果指定了默认值，在调用存储过程时不必 指定该参数的值，默认值必须是常量或NULL。 ⑸ 如果有“OUTPUT”，表示该参数是返回参数，使用“OUTPUT”可以将数据返回给调 用过程。 ⑹ 如果有“WITH ENCRYPTION”，表示对存储在系统表“syscomments”中的存储过程 文本进行加密，这样可以防止他人查看存储过程的定义文本。 ⑺ “SQL语句块”是指存储过程中包含的若干个SQL语句。 8.2 存储过程的创建和执行 【例8.1】 在“图书管理”数据库中建立一个名为“图书出版情况”的存储过程，该 存储过程从“图书”、“作者”和“出版社”表中检索所有图书的“图书名”、“作者名” 和相应的“出版社名”。 USE 图书管理 GO CREATE PROCEDURE 图书出版情况 AS SELECT 图书名,作者名,出版社名 FROM 图书 AS A INNER JOIN 作者 AS B ON A.作者号=B.作者号 INNER JOIN 出版社 AS C ON A.出版社号=C.出版社号 GO --执行存储过程 EXEC 图书出版情况 GO 8.2 存储过程的创建和执行 【例8.2】 在“图书管理”数据库中建立一个名为“作者查询”的存储过程，该存储过程 从“作者”、“图书”和