页数：44页文件大小：1.26 MB来源： 空间数据库与SQLppt课件
本课件讲解存储过程的概念与类型，演示带参数与返回值的存储过程创建、执行、修改和删除，随后介绍触发器的作用与分类，给出DML触发器与DDL触发器的完整创建与应用示例。
7.1存储过程7.2触发器
在SQL Server 2008中，使用T-SQL语句编写存储过程。存储过程可以接收输入参数、返回表格或标量结果和消息，调用“数据定义语言（DDL）”和“数据操作语言（DML）”语句，然后返回输出参数。使用存储过程的优点如下：
（3）用户存储过程。在SQL Server 2008中，用户存储过程可以使用T-SQL语言编写，也可以使用CLR方式编写。在本书中，T-SQL存储过程就称为存储过程。①存储过程：存储过程保存T-SQL语句集合，可以接收和返回用户提供的参数。存储过程中可以包含根据客户端应用程序提供的信息，以及在一个或多个表中插入新行所需的语句。
存储过程也可以从数据库向客户端应用程序返回数据。例如，电子商务Web应用程序可能根据联机用户指定的搜索条件，使用存储过程返回有关特定产品的信息。②CLR存储过程：CLR存储过程是对Microsoft .NET Framework公共语言运行时（CLR）方法的引用，可以接收和返回用户提供的参数。
它们在“.NET Framework程序集”中是作为类的公共静态方法实现的。简单地说，CLR存储过程就是可以使用Microsoft Visual Studio 2008环境下的语言作为脚本编写的、可以对Microsoft .NETFramework公共语言运行时（CLR）方法进行引用的存储过程。
编写CLR存储过程需要有C#语言的基础，本书将在附录D中具体介绍编写CLR存储过程和CLR触发器的方法。
1．使用T-SQL命令创建存储过程创建存储过程的语句是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 <procedure_option>] [ ,...n ][ FOR REPLICATION ]AS {<sql_statement> [;][ ...n ]| EXTERNAL NAME asse MBly_name.class_name.method_name}[;]其中，<procedure_option> ::=[ ENCRYPTION ][ RECOMPILE ]
2．存储过程的执行通过EXECUTE或EXEC命令可以执行一个已定义的存储过程，EXEC是EXECUTE的简写。
语法格式：[ { EXEC | EXECUTE } ]{ [ @return_status = ]{ module_name [ ;number] | @module_name_var }[ [ @parameter = ] { value| @variable [ OUTPUT ]| [ DEFAULT ]}][ ,...n ][ WITH RECOMPILE ]}[;]
3．举例（1）设计简单的存储过程。
【例7.1】返回081101号学生
返回081101号学生的成绩情况。该存储过程不使用任何参数。 USE PXSCJ GO CREATE PROCEDURE student_info ASSELECT *FROM CJBWHERE学号= '081101' GO 存储过程定义后，执行存储过程student_info： EXECUTE student_info如果该存储过程是批处理中的第一条语句，则可使用student_info执行结果如下：（2）使用带参数的存储过程。
【例7.2】从PXSCJ数据库
从PXSCJ数据库的三个表中查询某人指定课程的成绩和学分。该存储过程接收与传递参数精确匹配的值。 USE PXSCJ GO CREATE PROCEDURE student_info1 @name char (8), @cname char(16) ASSELECT a.学号,姓名,课程名,成绩, t.学分FROM XSB a INNER JOIN CJB b ON a.学号= b.学号INNER JOIN KCB t ON b.课程号= t.课程号WHERE a.姓名=@name and t.课程名=@cname GO执行存储过程student_info1：EXECUTE student_info1 '王林', '计算机基础'执行结果如下：以下命令的执行结果与上面的相同：EXECUTE student_info1 @name='王林', @cname='计算机基础'或者DECLARE @proc char(20)SET @proc= 'student_info1'EXECUTE @proc @name='王林', @cname='计算机基础'
（3）使用带OUPUT参数的存储过程。
【例7.3】创建一个存储过程
创建一个存储过程do_insert，作用是向XSB表中插入一行数据。创建另外一个存储过程do_action，在其中调用第一个存储过程，并根据条件处理该行数据，处理后输出相应的信息。第一个存储过程： CREATE PROCEDURE dbo.do_insert ASINSERT INTO XSB VALUES('091201', '陶伟', 1, '1990-03-05', '软件工程',50,NULL);第二个存储过程：CREATE PROCEDURE do_action @X bit, @STR CHAR(8) OUTPUTASBEGINEXEC do_insertIF @X=0BEGINUPDATE XSB SET姓名='刘英',性别=0 WHERE学号='091201'SET @STR='修改成功'ENDELSEIF @X=1BEGINDELETE FROM XSB WHERE学号='091201'SET @STR='删除成功'ENDEND
接下来执行存储过程do_action来查看结果：DECLARE @str char(8)EXEC dbo.do_action 0, @str OUTPUTSELECT @str;执行结果如下：
（4）使用带有通配符参数的存储过程。
【例7.4】从三个表的连接中
从三个表的连接中返回指定学生的学号、姓名、所选课程名称及该课程的成绩。该存储过程在参数中使用了模式匹配，如果没有提供参数，则使用预设的默认值。 CREATE PROCEDURE st_info @name varchar(30) = '李%' ASSELECT a.学号,a.姓名,c.课程名,b.成绩FROM XSB a INNER JOIN CJB b ON a.学号=b.学号INNER JOIN KCB c ON c.课程号= b.课程号WHERE姓名LIKE @name GO 执行存储过程： EXECUTE st_info或者EXECUTE st_info '王%'（5）使用OUTPUT游标参数的存储过程。OUTPUT游标参数用于返回存储过程的局部游标。
【例7.5】在PXSCJ数据库
在PXSCJ数据库的XSB表上声明并打开一个游标。 CREATE PROCEDURE st_cursor @st_cursor cursor VARYING OUTPUTASSET @st_cursor = CURSOR FORWARD_ ONLY STATIC FORSELECT *FROM XSBOPEN @st_cursor在如下的批处理中，声明一个局部游标变量，执行上述存储过程，并将游标赋值给局部游标变量，然后通过该游标变量读取记录。
DECLARE @MyCursor cursorEXEC st_cursor @st_cursor = @MyCursor OUTPUTFETCH NEXT FROM @MyCursorWHILE (@@FETCH_STATUS = 0)BEGINFETCH NEXT FROM @MyCursorENDCLOSE @MyCursorDEALLOCATE @MyCursor
（6）使用WITH ENCRYPTION选项。WITH ENCRYPTION子句用于对用户隐藏存储过程的文本。
【例7.6】创建加密过程
创建加密过程，使用sp_helptext系统存储过程获取关于加密过程的信息，然后尝试直接从syscomments表中获取关于该过程的信息。 CREATE PROCEDURE encrypt_this WITH ENCRYPTIONASSELECT *FROM XSB通过系统存储过程sp_helptext可显示规则、默认值、未加密的存储过程、用户定义函数、触发器或视图的文本。执行如下语句： EXEC sp_helptext encrypt_this结果集为提示信息“对象'encrypt_this'的文本已加密”。使用ALTER PROCEDURE命令可修改已存在的存储过程并保留以前赋予的许可。
语法格式：ALTER { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number][ { @parameter [ type_schema_name. ] data_type }[ VARYING ] [ = default ] [ OUT[PUT]]][ ,...n ][ WITH <procedure_option>] [ ,...n ][ FOR REPLICATION ]AS {<sql_statement> [;][ ...n ]| EXTERNAL NAME asse MBly_name.class_name.method_name}[;]
【例7.7】对例
对例7.2中创建的存储过程student_info1进行修改，将第一个参数改成学生的学号。 USE PXSCJ GO ALTER PROCEDURE student_info1@number char(6),@cname char(16) ASSELECT学号,课程名,成绩FROM CJB, KCBWHERE CJB.学号=@number AND KCB.课程名=@cname GO【例7.8】创建名
创建名为select_students的存储过程，在默认情况下，该存储过程可查询所有学生信息，随后授予权限。当该存储过程需更改为能检索计算机专业的学生信息时，用ALTER PROCEDURE重新定义该存储过程。创建select_students存储过程。 CREATE PROCEDURE select_studentsASSELECT *FROM XSBORDER BY学号 GO 修改存储过程select_students。 ALTER PROCEDURE select_students WITH ENCRYPTIONASSELECT *FROM XSBWHERE专业= '计算机' ORDER BY学号 GO当不再使用一个存储过程时，就要把它从数据库中删除。使用DROPPROCEDURE语句可永久地删除存储过程。在此之前，必须确认该存储过程没有任何依赖关系。语法格式：DROP { PROC | PROCEDURE } { [ schema_name. ] procedure } [ ,...n ]
【例7.9】删除PXSCJ数据库中
删除PXSCJ数据库中的student_info存储过程。 USE PXSCJ GO IF EXISTS(SELECT name FROM sysobjects WHERE name='student_info') DROP PROCEDURE student_info（1）创建存储过程。
例如，如果要通过图形向导方式定义一个存储过程来查询PXSCJ数据库中每个同学各门功课的成绩，那么其主要步骤如下：启动SQL Server Management Studio，在对象资源管理器中展开“数据库→PXSCJ”，选择其中的“可编程性”，右击“存储过程”，在弹出的快捷菜单中选择“新建存储过程”菜单项，打开“存储过程脚本编辑”窗口，如图7.1所示。
在该窗口中输入要创建的存储过程的代码，输入完成后单击“执行”按钮，若执行成功则创建完成。图7.1创建存储过程
（2）执行存储过程。在PXSCJ数据库的“存储过程”目录下选择要执行的存储过程，例如student_info1，右击鼠标，选择“执行存储过程”菜单项。在弹出的“执行过程”窗口中会列出存储过程的参数形式，如果“输出参数”栏为“否”，则表示该参数为输入参数，用户需要设置输入参数的值，在“值”一栏中输入即可，如图7.2所示。
单击“确定”按钮，SSMS的结果显示窗口将列出存储过程运行的结果。图7.2执行存储过程
（3）修改存储过程。在“存储过程”目录下选择要修改的存储过程，右击鼠标，在弹出的快捷菜单中选择“修改”菜单项，打开“存储过程脚本编辑”窗口，在该窗口中修改相关的T-SQL语句。修改完成后，执行修改后的脚本，若执行成功，则修改了存储过程。（4）删除存储过程。
选择要删除的存储过程，右击鼠标，在弹出的快捷菜单中选择“删除”菜单项，根据提示删除该存储过程。
7.2.1触发器的类型在SQL Server 2008中，按照触发事件的不同可以将触发器分为两大类：DML触发器和DDL触发器。
1．创建DML触发器语法格式：CREATE TRIGGER [ schema_name . ]trigger_nameON { table | view }[ WITH ENCRYPTION ]{ FOR |AFTER | INSTEAD OF }{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }[ WITH APPEND ][ NOT FOR REPLICATION ]AS { sql_statement [ ; ] [ ...n ]| EXTERNAL NAME asse MBly_name.class_name.method_name}
【例7.10】创建一个表
创建一个表table1，其中只有一列a。在表上创建一个触发器，每次插入操作时，将变量@str的值设为“TRIGGER IS WORKING”并显示。 USE PXSCJ GO CREATE TABLE table1(a int) GO CREATE TRIGGER table1_insert ON table1 AFTER INSERTASBEGINDECLARE @str char(50) SET @str='TRIGGER IS WORKING' PRINT @strEND向table1中插入一行数据：INSERT INTO table1 VALUES(10)执行结果如下：
【例7.11】创建触发器
创建触发器，当向CJB表中插入一个学生的成绩时，将XSB表中该学生的总学分加上添加的课程的学分。 CREATE TRIGGER cjb_insert ON CJB AFTER INSERTASBEGINDECLARE @num char(6), @kc_num char(3) DECLARE @xf int SELECT @num=学号, @kc_num=课程号from inserted SELECT @xf=学分FROM KCB WHERE课程号=@kc_num UPDATE XSB SET总学分=总学分+@xf WHERE学号=@num PRINT '修改成功'END（4）创建UPDATE触发器。UPDATE触发器在对触发器表执行UPDATE语句后触发。在执行UPDATE触发器时，将触发器表的原记录保存到deleted临时表中，将修改后的记录保存到inserted临时表中。
【例7.12】创建触发器
创建触发器，当修改XSB表中的学号时，同时也要将CJB表中的学号修改成相应的学号（假设XSB表和CJB表之间没有定义外键约束）。 CREATE TRIGGER xsb_update ON XSB AFTER UPDATEASBEGINDECLARE @old_num char(6), @new_num char(6) SELECT @old_num=学号FROM deleted SELECT @new_num=学号FROM inserted UPDATE CJB SET学号=@new_num WHERE学号=@old_numEND接着修改XSB表中的一行数据，并查看触发器执行结果：UPDATE XSB SET学号='081120' WHERE学号='081101'GOSELECT * FROM CJB WHERE学号='081120'执行结果如下：
（5）创建DELETE触发器。
【例7.13】在删除
在删除XSB表中的一条学生记录时将CJB表中该学生的相应记录也删除。 CREATE TRIGGER xsb_delete ON XSB AFTER DELETEASBEGINDELETE FROM CJBWHERE学号IN(SELECT学号FROM deleted)END【例7.14】在
在KCB表中创建UPDATE和DELETE触发器，当修改或删除KCB表中的“课程号”字段时，同时修改或删除CJB表中的该课程号。 CREATE TRIGGER kcb_trig ON KCB AFTER UPDATE, DELETEASBEGINIF (UPDATE(课程号)) UPDATE CJB SET课程号=(SELECT课程号FROM inserted)WHERE课程号=(SELECT课程号FROM deleted) ELSEDELETE FROM CJBWHERE课程号IN(SELECT课程号FROMdeleted)END（6）创建INSTEAD OF触发器。AFTER触发器是在触发语句执行后触发的，与AFTER触发器不同的是，INSTEAD OF触发器触发时只执行触发器内部的SQL语句，而不执行激活该触发器的SQL语句。一个表或视图中只能有一个INSTEAD OF触发器。
【例7.15】创建表
创建表table2，值包含一列a，在表中创建INSTEAD OF INSERT触发器，当向表中插入记录时显示相应消息。 USE PXSCJ GO CREATE TABLE table2(a int) GO CREATE TRIGGER table2_insert ON table2 INSTEAD OF INSERTASPRINT 'INSTEAD OF TRIGGER IS WORKING'向表中插入一行数据： INSERT INTO table2 VALUES(10)执行结果如下：【例7.16】在PXSCJ数据库中
在PXSCJ数据库中创建视图stu_view，包含学生学号、专业、课程号、成绩。该视图依赖于表XSB和CJB，是不可更新视图。可以在视图上创建INSTEAD OF触发器，当向视图中插入数据时分别向表XSB和CJB插入数据，从而实现向视图插入数据的功能。首先创建视图： CREATE VIEW stu_view ASSELECT XSB.学号,专业,课程号,成绩FROM XSB, CJBWHERE XSB.学号=CJB.学号创建INSTEAD OF触发器：CREATE TRIGGER InsteadTrigON stu_viewINSTEAD OF INSERTASBEGINDECLARE @XH char(6), @XM char(8),@ZY char(12), @KCH char(3), @CJ intSET @XM='佚名'SELECT @XH=学号, @ZY=专业, @KCH=课程号, @CJ=成绩FROM insertedINSERT INTO XSB(学号,姓名,专业)VALUES(@XH, @XM, @ZY)INSERT INTO CJB VALUES(@XH, @KCH, @CJ)END
向视图插入一行数据：INSERT INTO stu_view VALUES('091102', '计算机', '101', 85 )查看数据是否插入：SELECT * FROM stu_view WHERE学号= '091102'执行结果如下：查看与视图关联的XSB表的情况：SELECT * FROM XSB WHERE学号= '091102'执行结果如下：
2．创建DDL触发器语法格式：CREATE TRIGGER trigger_nameON { ALL SERVER | DATABASE }[ WITH ENCRYPTION ]{ FOR | AFTER } { event_type | event_group } [ ,...n ]AS {sql_statement [ ; ] [ ...n ]| EXTERNAL NAME asse MBly_name.class_name.method_name}
【例7.17】创建PXSCJ数据库作用域
创建PXSCJ数据库作用域的DDL触发器，当删除一个表时，提示禁止该操作，然后回滚删除表的操作。 USE PXSCJ GO CREATE TRIGGER safety ON DATABASEAFTER DROP_TABLEASPRINT '不能删除该表' ROLLBACK TRANSACTION尝试删除表table1： DROP TABLE table1执行结果如下：【例7.18】创建服务器作用域
创建服务器作用域的DDL触发器，当删除一个数据库时，提示禁止该操作并回滚删除数据库的操作。 CREATE TRIGGER safety_server ON ALL SERVERAFTER DROP_DATABASEASPRINT '不能删除该数据库' ROLLBACK TRANSACTION【例7.19】修改PXSCJ数据库中
修改PXSCJ数据库中在XSB表上定义的触发器xsb_delete，将其修改为UPDATE触发器。 USE PXSCJ GO ALTER TRIGGER xsb_delete ON XSBFOR UPDATEASPRINT '执行的操作是修改'触发器本身是存在于表中的，因此，当表被删除时，表中的触发器也将一起被删除。删除触发器使用DROP TRIGGER语句。
语法格式：DROP TRIGGER schema_name.trigger_name [ ,...n ] [ ; ]DROP TRIGGER trigger_name [ ,...n ] ON { DATABASE | ALL SERVER }[ ; ]
【例7.20】删除
删除DML触发器xsb_delete。 IF EXISTS (SELECT name FROM sysobjects WHERE name = 'xsb_delete') DROP TRIGGER xsb_delete【例7.21】删除
删除DDL触发器safety。 DROP TRIGGER safety ON DATABASE1．创建触发器
2．修改触发器DML触发器能够使用界面方式修改，DDL触发器则不可以。进入对象资源管理器，修改触发器的步骤与创建的步骤相同，在对象资源管理器中选择要修改的触发器，右击鼠标，在弹出的快捷菜单中选择“修改”菜单项，打开“触发器脚本编辑”窗口，在该窗口中可以进行触发器的修改，修改后单击“执行”按钮重新执行即可；
但是被设置成“WITH ENCRYPTION”的触发器是不能被修改的。3．删除触发器
下载地址：{{downurl}}
{{{a}}}
{{#b}}{{{b}}}
{{/b}} {{/title}} {{#nolog}}登陆注册
{{/nolog}} {{#djsx}}点我刷新
{{/djsx}} {{#gocz}} {{/gocz}} {{#ffqr}} {{/ffqr}}