《SQL Server 2008 数据库案例教程 教学课件 ppt 作者 于斌 第8章 存储过程和触发器》由会员分享，可在线阅读，更多相关《SQL Server 2008 数据库案例教程 教学课件 ppt 作者 于斌 第8章 存储过程和触发器（81页珍藏版）》请在金锄头文库上搜索。
1、第8章 存储过程和触发器,存储过程,触发器,ADO.NET存储技术与.NET构架下CLR集成,综合应用训练,8.1 存储过程,8.1.1 存储过程的分类 SQL Server 支持的存储过程可分为5类：系统存储过程、本地存储过程、临时存储过程、远程存储过程和扩展存储过程。在不同情况下需要执行不同的存储过程。 （1）系统存储过程。系统存储过程是由系统提供的存储过程，可以作为命令执行各种操作。系统存储过程定义在系统数据库master中，其前缀是sp_，它们为检索系统表的信息提供了方便快捷的方法。系统存储过程允许系统管理员执行修改系统表的数据库管理任务，可以在任何一个数据库中执行。 （2）用户存储过
2、程。本地存储过程是指在用户数据库中创建的存储过程，这种存储过程完成用户指定的数据库操作，其名称不能以sp_为前缀。SQL Server 2008中，本地存储过程可以使用T-SQL语言编写，也可以使用CLR方式编写。在本书中，T-SQL存储过程就称为存储过程。 存储过程：存储过程保存T-SQL语句集合，可以接受和返回用户提供的参数。存储过程中可以包含根据客户端应用程序提供的信息，在一个或多个表中插入新行所需的语句。存储过程也可以从数据库向客户端应用程序返回数据。 CLR存储过程：CLR存储过程是对Microsoft .NET Framework公共语言运行时（CLR）方法的引用，可以接受和返回用
3、户提供的参数。它们在“.NET Framework 程序集”中是作为类的公共静态方法实现的。,8.1.1 存储过程的分类,（3）临时存储过程。临时存储过程属于用户存储过程。如果用户存储过程的名称前面有一个“#”，该存储过程就称为局部临时存储过程，这种存储过程只能在一个用户会话中使用。 如果用户存储过程的名称前有两个“#”，该过程就是全局临时存储过程，这种存储过程可以在所有用户会话中使用。 （4）远程存储过程。远程存储过程指从远程服务器上调用的存储过程。 （5）扩展存储过程。在SQL Server环境之外执行的动态链接库称为扩展存储过程，其前缀是sp_。使用时需要先加载到SQL Server系统
4、中，并且按照使用存储过程的方法执行。,8.1.2 用户存储过程的创建与执行,1通过T-SQL命令创建存储过程 如果要通过SQL命令定义一个存储过程查询XSBOOK数据库中每位读者当前的借书情况，然后调用该存储过程，实现步骤如下 ： 定义如下存储过程 USE XSBOOK GO CREATE PROCEDURE readers_info AS SELECT DISTINCT XS.借书证号,姓名,BOOK.ISBN,书名,索书号 FROM XS, JY, BOOK WHERE XS.借书证号=JY.借书证号 AND BOOK.ISBN=JY.ISBN GO 说明：,8.1.2 用户存储过程的创建
5、与执行, 调用存储过程 EXEC readers_info 创建存储过程的语句是CREATE PROCEDURE或CREATE PROC，两者同义。 语法格式： CREATE PROC | PROCEDURE schema_name. procedure_name ; number /*定义过程名*/ parameter type_schema_name. data_type /*定义参数的类型*/ VARYING = default OUT | OUTPUT READONLY ,.n /*定义参数的属性*/ WITH RECOMPILE , ENCRYPTION /*定义存储过程的处理方式*
6、/ FOR REPLICATION AS ; /*执行的操作*/,8.1.2 用户存储过程的创建与执行,2执行存储过程 通过EXECUTE或EXEC命令可以执行一个已定义的存储过程，EXEC是EXECUTE的简写。语法格式： EXEC | EXECUTE return_status = module_name ;number | module_name_var parameter = value | variable OUTPUT | DEFAULT ,.n WITH RECOMPILE ;,8.1.2 用户存储过程的创建与执行,说明： （1）参数return_status为可选的整型变量，保
7、存存储过程的返回状态，EXECUTE语句使用该变量前，必须对其定义。 （2）参数module_name是要调用的存储过程或用户定义标量函数的完全限定或者不完全限定名称。number用于调用已定义的一组存储过程中的某一个。module_name_var表示局部定义的变量名，保存存储过程或用户定义函数的名称。 （3）parameter表示CREATE PROCEDURE或CREATE FUNCTION语句中定义的参数名，value为实参。如果省略parameter，则后面的实参顺序要与定义时参数的顺序一致。在使用parameter_name=value格式时，参数名称和实参不必按在存储过程或函数中
8、定义的顺序提供。但是，如果任何参数使用了parameter_name=value格式，则对后续的所有参数均必须使用该格式。variable表示局部变量，用于保存OUTPUT参数返回的值。DEFAULT关键字表示不提供实参，而是使用对应的默认值。,8.1.2 用户存储过程的创建与执行,（4）WITH RECOMPILE表示执行模块后，强制编译、使用和放弃新计划。 参数procedure_name 和number用于调用已定义的一组存储过程中的某一个，procedure_name代表了存储过程的组名，number用于指定组中的存储过程。定义存储过程组的目的是以便用一条DROP PROCEDURE
9、语句删除一组存储过程，对过程分组后，不能删除组中的单个过程。参数procedure_name_var代表存储过程名。parameter为CREATE PROCEDURE 语句中定义的参数名； value为存储过程的实参；variable为变量，用于保存OUTPUT参数返回的值。DEFAULT关键字表示不提供实参，而是使用对应的默认值。n表示实参可有多个。关键字WITH RECOMPILE指定强制编译。,8.1.2 用户存储过程的创建与执行,3举例 (1) 设计简单的存储过程 【例7.1】 利用XSBOOK数据库中的XS、BOOK和JYLS表，编写一无参存储过程用于查询每个读者的借阅历史，然后调
10、用该存储过程。 USE XSBOOK GO CREATE PROCEDURE history_info AS SELECT a.借书证号,姓名, b.ISBN,书名,索书号, 借书时间,还书时间 FROM XS a INNER JOIN JYLS b ON a.借书证号 = b.借书证号 INNER JOIN BOOK c ON b.ISBN= c.ISBN GO history_info存储过程可以通过以下方法执行： EXECUTE history_info 或 EXEC history_info 如果该过程是批处理中的第一条语句，则可使用： history_info,8.1.2 用户存储过
11、程的创建与执行,（2）使用带参数的存储过程 【例7.2】 创建存储过程，根据XSBOOK数据库的3个表查询指定读者当前的借书情况。 CREATE PROCEDURE reader_info lib_num char (8) AS SELECT a.借书证号,姓名,b.ISBN, 书名, 索书号 FROM XS a, JY b, BOOK c WHERE a.借书证号 = b.借书证号 AND b.ISBN=c.ISBN AND a.借书证号=lib_num GO reader_info存储过程有多种执行方式，如下所示： EXECUTE reader_info 10000002 执行结果如图7.
12、1所示。 以下命令的执行结果与上面相同： EXECUTE reader_info lib_num=10000002,8.1.2 用户存储过程的创建与执行,（3）使用带有通配符参数的存储过程 【例7.3】 利用XSBOOK数据库中XS、BOOK、JYLS表创建一存储过程book_inf，查询指定图书的借阅历史。该存储过程在参数中使用了模糊查询，如果没有提供参数，则使用预设的默认值。 CREATE PROCEDURE book_inf bname varchar(30) = %计算机% AS SELECT b.ISBN,书名,姓名,借书时间,还书时间 FROM XS a ,JYLS b,BOOK
13、c WHERE a.借书证号 =b.借书证号 AND b.ISBN= c.ISBN AND 书名 LIKE bname GO 执行存储过程： EXECUTE book_inf /*参数使用默认值*/ 或者： USE XSBOOK EXECUTE book_inf WEB% /*传递给bname的实参为WEB%*/,8.1.2 用户存储过程的创建与执行,（4）使用带OUTPUT参数的存储过程 【例7.4】 编写存储过程，统计指定图书在给定时间段内的借阅次数，存储过程中使用了输入和输出参数。 CREATE PROCEDURE bstatistics bname varchar(26),startd
14、ate date, enddate date, total int OUTPUT AS SELECT total=count(索书号) FROM JYLS a,BOOK b WHERE 书名 like bname AND a.ISBN= b.ISBN AND 借书时间=startdate AND 借书时间=enddate GO 在调用存储过程bstatistics时，存储过程定义时的形参名和调用时的变量名不一定要匹配，不过数据类型和参数位置必须匹配。不过数据类型和参数位置必须匹配。执行语句如下： DECLARE book_name char(26),total int SET book_nam
15、e=Web站点安全 EXECUTE bstatistics book_name, 2003-01-01,2003-12-08,total OUTPUT SELECT book_name, total GO,8.1.2 用户存储过程的创建与执行,（5）使用OUTPUT游标参数的存储过程 OUTPUT 游标参数用于返回存储过程的局部游标。 【例7.5】 在 XSBOOK数据库的XS表上声明并打开一个游标。 CREATE PROCEDURE reader_cursor reader_cur CURSOR VARYING OUTPUT AS SET reader_cur = CURSOR FORWARD_ONLY STATIC FOR SELECT 借书证号,姓名,专业,性别,出生时间,借书量 FROM XS OPEN reader_cur GO 在如下的批处理中，声明一个局部游标变量，执行上述存储过程过程并将游标赋值给局部游标变量，然后通过该游标变量读取记录。 DECLARE MyCursor CURSOR EXEC reader_cursor reader_cur = MyCursor OUTPUT FETCH NEXT FROM MyCursor WHILE (FETCH_STATUS = 0) FETCH NEXT FROM MyCursor CLOSE M
