第8章sqlserver.ppt

8.1.1存储过程的基本知识 SqlServer存储过程是一组预编译的T-SQL语句，主体构成是标准SQL命令，同时包括T-SQL的语句块、结构控制命令、变量、常量、运算符、表达式、流控语句。 8.1 存储过程 1.概念: 存储过程是独立于数据表之外的数据库对象,是SQL Server 服务器上的一组预编译的实现特定操作功能的T—SQL语句,用于完成某项任务,它们可以接受参数、输出参数、返回单个或多个结果集、返回状态值和参数值。存储过程独立于程序源代码，可单独修改，可被调用任意次，可以引用其他存储过程。它与特定数据库相关联,存储在SQL Server服务器上。 2.存储过程与其它编程语言中的过程的不同 储过程与其他编程语言或开发环境不同，存储过程是存储在数据库中的，它是数据库的一部份，存储过程在数据库服务器上运行，这样可以避免在客户端和服务端传送无用的数据。 （参考材料） 增强代码的重用性和共享性 每一个存储过程都是为了实现一个特定的功能而编写的模块，模块可以在系统中重复地调用，也可以被多个有访问权限的用户访问。所以，存储过程可以增强代码的重用性和共享性，加快应用系统的开发速度，减少工作量，提高开发的质量和效率 减少网络数据流量 存储过程是与数据库一起存放在服务器中并在服务器上运行的。应用系统调用存储过程时只有触发执行存储过程的命令和执行结束返回的结果在网络中传输。用户端不需要将数据库中的数据通过网络传输到本地进行计算，再将计算结果通过网络传送到服务器。所以，使用存储过程可以减少网络中数据流量。 加快系统运行速度 第一次执行后的存储过程会在缓冲区中创建查询树，第二次执行时就不用进行预编译，从而加快了系统运行速度。另外，由于存储过程是在服务器上运行，分担了用户端的数据处理工作，也加快了应用系统的处理速度。 加强系统安全性 SQL Server可以不授予用户某些表、视图的访问权限，但授予用户执行存储过程的权限，通过存储过程来对这些表或视图进行访问操作。这样，既可以保证用户能够通过存储过程操作数据库中的数据，又可以保证用户不能直接访问与存储过程相关的表，从而保证表中数据的安全性。 将游标程序建立为存储过程 存储过程中的局部变量和参数生命期,只在定义它的过程中起作用。 局部变量是用户自定义的变量;其有效范围是定义它的批、存储过程或触发器。 练习:创建一个存储过程,查询指定读者的借阅数量，并执行该存储过程。 输入参数带默认值(了解) CREATE PROCEDURE borrowed_books2 @name varchar(10) =‘%’ AS SELECT Readers.Rname, Readers.Rid, Books.Bid, Books.Btitle, Borrowinfo.BB_date, Borrowinfo.BG_date FROM Books INNER JOIN Borrowinfo ON Books.Bid = Borrowinfo.BBid INNER JOIN Readers ON Borrowinfo.BRid = Readers.Rid WHERE Readers.Rname = @name 调用: EXEC borrowed_books1 将实参值传给存储过程的方法： 1.直接将值传入。 EXEC borrowed_books1 刘超‘ 多个参数按序输入 2.利用变量传递。 DECLARE @tempname VARCHAR(8) SET @tempname=刘超 EXEC borrowed_books1 @tempname 注意与输出参数的区别 3.使用参数名进行传递。(可调整次序) EXEC borrowed_books1 @name=刘超‘ 当输入多个参数时,可以以任意的顺序进行参数传递,但此情况调用须都使用参数名 4.使用默认值:如果不给形参输入值,将会产生错误; 若参数有默认值时,不给形参输入值形参的值取默任值. 可用以下代码调用此存储过程： DECLARE @n INT EXEC readers_booknum 刘超,@n OUTPUT SELECT @n 声明输出参数时须用output,表明此参数为输出参数 调用存储过程时与输出参数对应的实参必须是已声明的变量,实参变量后也须用output，否则不传值。 练习:创建一个存储过程，用输出参数计算指定读者的借阅数量，并执行该存储过程。 重新编译存储过程(参考材料) 在某些情况下，可能需要改变数据库的逻辑结构（如：为表新增列），或者为表新增索引。为了使该存储过程