[高等教育]chapter 4 Store Procedure and Trigger.pptVIP

Chapter 4 存储过程与触发器 任课教师：张艳 Email: zy_helen@ ftp: 0 , zy_helen_std 存储过程 概述 存储过程就是作为可执行对象存放在数据库中的一个或多个SQL命令。存储过程可以交互式调用，可以在客户端应用程序代码中调用，在另一存储过程中调用或在触发器中调用。 存储过程可以传入参数和返回数值，从而增加可用性和灵活性。 存储过程还可以返回多个结果集和状态码 存储过程的优点 模块化编程—正如C、C++中常使用函数和子例程将代码分解成更小的块一样，使用存储过程可以得到同样的好处，只是存储过程存放在SQL Server中，可以在任何客户端应用程序代码中调用。 存储过程 存储过程的优点 基于函数限制访问表格—可以访问执行存储过程而不必拥有直接操作基础表的权限 减少网络通信流—存储过程可以包含多条SQL语句，但只要用一条语句来执行该存储过程，从而减少了客户端应用程序对服务器的调用次数和长度 快速执行—首次执行后，存储过程查询计划保存在内存中，后续执行时不再需要分析和优化代码 保证一致性—如果用户只通过存储过程修改数据，则可以消除偶然修改带来的问题 减少操作人员和编程人员的错误—由于传递信息少，因此执行复杂任务更容易，不易出现SQL错误 存储过程 存储过程的缺点（取决于环境） 编程语言功能差—T-SQL语言不是很强大的或高度结构化的语言 与编程环境集成不够—许多大型软件开发项目用工具进行版本处理、调试、复用等，这些工具可能不支持存储过程中的代码 移植性差—ANSI标准和SQL Server标准存在差异，许多DBMS厂家不支持新标准，导致存储过程移植性差 存储过程 生成与执行存储过程 格式： create proc [owner.] procedure_name [ ; number] [[([@parm_name datatype=default_value [output] [,…][)]] [with { recompile | encryption}] As SQL statements [return {integer_status_value]] 总是用RETURN语句指定存储过程结束和遇到错误条件时指定非0状态是一个好的编程习惯 存储过程 生成与执行存储过程 例1：生成返回书名与作者名的存储过程 Create Proc dbo.title_authors AS Select a.au_lname, a.au_fname, t.title From titles t Inner Join titleauthor ta On t.title_id=ta.title_id Right Outer Join authors a On ta.au_id=a.au_id Return 该存储过程中的查询涉及到三张表：titles、titleauthor和authors。t是表titles的别名、a是表authors的别名、ta是表titleauthor的别名。 存储过程 生成与执行存储过程 执行存储过程：要执行存储过程，只要调用过程名即可。 如果过程不是批命令中的第一条语句，则要在过程名前面加上关键字EXEC。这是因为存储过程名不是关键字，当语句在执行过程中遇到存储过程名时，会将其作为上一条语句的一部分处理，因此导致执行出错。 例2：执行存储过程title_authors： title_authors Select * from titles title_authors Select * from titles exec title_authors 存储过程 存储过程组 如果生成的存储过程在数据库中已经存在，则会收到错误信息。但SQL Server提供了相应的解决办法，它允许生成多个同名的存储过程，只要组号不同。默认组号为1。 要生成不同组号的存储过程，只要加上分号（；）和一个整数 例3：生成查询国籍为’CA’的作者名与书名的存储过程 Create Proc dbo.title_authors; 2 AS Select a.au_lname, a.au_fname, state, t.title From titles t Inner Join titleauthor ta On t.title_id=ta.title_id Right Outer Join authors a On ta.au_id=a.au_id Where a.state=‘CA’ Return 存储过程 删除存储过程 格式： Drop Proc Procedure_name 例如：删除存储过程title_authors Drop proc