单元5数据库的存储过程和触发器.

高等教育出版社目录单元1SQLSERVER2008基础单元2创建数据库和表单元3数据库的查询和视图单元4数据库的索引、默认值和规则单元5数据库的存储过程和触发器单元6数据库的日常管理与维护单元7数据库的安全管理单元8SQLServer数据库应用程序开发单元9数据库在电子商务中的应用单元5数据库的存储过程和触发器通过本单元的学习，掌握存储过程的创建和执行；存储过程的管理；触发器的创建、修改和删除，并能熟练使用INSERT触发器、UPDATE触发器、DELETE触发器。单元5数据库的存储过程和触发器1.理解存储过程、触发器概念和类型。2.掌握存储过程的操作和应用范围。3.掌握触发器的创建和管理。1.了解存储过程的概念和使用存储过程的优点。2.掌握使用对象资源管理器和T-SQL命令管理存储过程的创建、执行、修改、删除。3.掌握参数存储过程的使用。4.掌握使用对象资源管理器和T-SQL命令来管理触发器的创建、删除。5.正确应用触发器。单元5数据库的存储过程和触发器任务5.1创建和管理存储过程任务5.2创建和管理触发器任务5.1创建和管理存储过程当开发数据库的时候，会有一些反复使用的功能，为了方便使用，可以将某些功能的语句独立保存起来，创建为存储过程，在需要的时候进行调用。本任务就是创建和管理存储过程。1.了解存储过程的概念、使用存储过程的优点以及存储过程的类型。2.掌握使用对象资源管理器和T-SQL命令进行存储过程的创建、执行、修改和删除。3.掌握带参数存储过程的使用。任务5.1创建和管理存储过程活动5.1.1使用对象资源管理器创建简单存储过程活动5.1.2使用T-SQL命令创建带参数存储过程活动5.1.3执行存储过程活动5.1.4修改存储过程活动5.1.5重命名存储过程活动5.1.6删除存储过程活动5.1.1使用对象资源管理器创建简单存储过程在SQLServer2008中创建存储过程有两种方法，一种是在对象资源管理器中使用图形化界面方式创建，另一种是通过T-SQL命令创建。本活动主要讲解用对象资源管理器创建的方法。案例1在图书信息管理数据库中创建“查询医药类图书”存储过程，从“图书信息”表中查询“医药”类图书的信息。【实现步骤】第1步：在对象资源管理器中依次展开“数据库”→“图书信息管理”→“可编程性”，右键单击“存储过程”，选择“新建存储过程”命令，这时SQLServer就会打开存储过程的模板，使用者可在此模板的基础上编辑存储过程如下。CREATEPROCEDURE查询医药类图书--CREATEPROCEDURE创建存储过程ASBEGINSELECT*FROM图书信息WHERE类别='医药'SETNOCOUNTON;--需返回受影响记录的行数END活动5.1.2使用T-SQL命令创建带参数存储过程在前面的案例1中我们学习了使用对象资源管理器创建“查询医药类图书”的简单存储过程，接下来，我们来学习使用T-SQL语句来创建带参数的存储过程。案例2在图书信息管理数据库中创建“按类别查询图书”存储过程，在运行存储过程时输入参数，参数为要查询的图书类别，根据参数查询指定类别的图书，该存储过程命名为“按类别查询图书”。【实现步骤】第1步：在对象资源管理器中单击””按钮，输入如下的T-SQL命令：CREATEPROCEDURE按类别查询图书@图书类别VARCHAR(10)--声明参数@图书类别，类型是VARCHAR(10)ASBEGINSELECT*FROM图书信息WHERE类别=@图书类别SETNOCOUNTON;ENDGO活动5.1.3执行存储过程当存储过程创建好后，要实现其功能，还需要执行存储过程。案例3在对象资源管理器中执行案例1中建立的简单存储过程“查询医药类图书”。【实现步骤】单击展开“图书信息管理”数据库的“存储过程”，右键单击“查询医药类图书”存储过程，选择“执行存储过程(E)...”命令，如图5.1.2所示。在打开的“执行过程”对话框，单击“确定”按钮，即可执行该存储过程。案例4使用T-SQL命令执行案例2建立的、带参数的存储过程“按类别查询图书”【实现步骤】在对象资源管理器单击按钮，输入执行存储过程语句如下：EXEC按类别查询图书@图书类别='医药'说明：“@图书类别”是变量名称，在执行存储过程中赋值为“医药”，即表示查询“医药”类图书信息。单击工具栏按钮，执行命令。活动5.1.4修改存储过程案例5使用对象资源管理器修改存储过程“查询医药类图书”，将查询结果只显示“书名,定价,类别”三列的值。【实现步骤】第1步：单击展开“图书信息管理”数据库的“存储过程”节点，在“查询医药类图书”存储过程上单击右键，选择“修改”命令。第2步：在显示的代码中将“SELECT*FROM图书信息WHERE类别＝'医药'”修改为“SELECT书名，定价，类别FROM图书信息WHERE类别＝'医药'”。第3步：单击“执行”按钮，完成存储过程的修改。案例6使用T-SQL命令修改存储过程“按类别查询图书”，将查询结果只显示“书名,定价,类别”三列的值。【实现步骤】单击按钮，打开查询编辑器，输入代码如下，单击“执行”按钮完成存储过程的修改。ALTERPROCEDURE[dbo].[查询医药类图书]--ALTERPROCEDURE修改存储过程ASBEGINSELECT书名,定价,类别FROM图书信息WHERE类别＝'医药'SETNOCOUNTON;--需返回受影响记录的行数END活动5.1.5重命名存储过程案例7使用T-SQL命令重命名存储过程“按类别查询图书”为“按类别查询书籍”【实现步骤】单击按钮，在查询编辑器中输入以下语句：SP_RENAME按类别查询图书,按类别查询书籍单击“执行”按钮，完成存储过程的重命名。活动5.1.6删除存储过程案例8使用T-SQL命令删除存储过程“查询医药类图书”【实现步骤】单击按钮，在查询编辑器输入以下代码：DROPPROCEDURE查询医药类图书单击“执行”按钮，将删除“查询医药类图书”存储过程。任务5.1创建和管理存储过程1.存储过程概念存储过程是一组完成特定功能的SQL语句集，经过编译后存储在数据库中。也可以把存储过程看成是以数据库对象形式存储在SQLServer服务端的一段程序或函数。存储过程中可以包含SQL语句、程序流和逻辑等，可以接收参数、输出参数、返回单个或多个结果。存储过程可以由应用程序通过一个调用来执行，它是封装重复性工作的一种有效方法，使用存储过程，可以使数据库的管理工作变得更加容易。任务5.1创建和管理存储过程2.存储过程类型SQLServer存储过程可以分为五类：系统存储过程、用户存储过程、扩展存储过程、临时存储过程和远程存储过程。系统存储过程存储在SQLServer的master数据库中，一般以“sp_”作为前缀。在上一单元中我们使用过SP_BINDEFAULT、SP_UNBINDEFAULT、SP_BINDRULE、SP_UNBINDRULE等都是系统存储过程。本单元重点介绍的是用户存储过程的创建和使用。任务5.1创建和管理存储过程3.简单存储过程简单存储过程是指不带参数的存储过程。存储过程可以使用对象资源管理或使用T-SQL的CREATEPROCEDURE语句进行创建，并可用ALTERPROCEDURE语句进行修改。存储过程定义包括两个主要组成部分:过程名称及其参数的说明和过程的主体。4.参数存储过程参数和变量能够让存储过程动态化。输入参数使得运行过程的用户可以向存储过程传递值，产生特定于请求的结果集。输出参数查询返回的标准结果集上扩展了存储过程的输出，可以通过OUTPUT参数将值返回至调用程序。任务5.1创建和管理存储过程【体验活动】1.打开“学生成绩管理”数据库，创建“查询优秀成绩”和“查询不及格成绩”两个存储过程,分别从“学生成绩”表中查询成绩优秀（大于85分）和不及格成绩（小于60分）学生的学号、课程号、成绩。2.打开“学生成绩管理”数据库，创建名为“按专业查询”的带参数存储过程，参数名为“@所学专业”，“@所学专业”参数指定专业，例如“计算机应用”，则存储过程根据参数，查询“学生信息”表中“计算机应用”专业学生的信息。任务5.2创建和管理触发器本任务主要学习SQLServer触发器的相关知识，具体包括了触发器的概念、触发器的类型、以及使用对象资源管理器和T-SQL命令管理触发器。1.认识触发器对象类型。2.掌握使用对象资源管理器管理触发器。3.掌握使用T-SQL命令管理触发器。任务5.2创建和管理触发器活动5.2.1在对象资源管理器中创建INSERT触发器活动5.2.2利用T-SQL命令创建DELETE触发器活动5.2.3创建UPDATE触发器活动5.2.4对象资源管理器修改、禁用、启用、删除触发器活动5.2.5利用T-SQL命令修改、禁用、启用、删除触发器活动5.2.1在对象资源管理器中创建INSERT触发器INSERT触发器就是指当在表中插入记录时，该触发器就会自动触发。我们可以利用INSERT触发器自动执行这个特点，将需要在插入记录时执行的命令写在触发器中。案例1利用对象资源管理器在图书信息管理数据库的“出版社”表中创建“添加新出版社”触发器，当为“出版社”表插入一条新记录时候触发，触发器提示“插入新出版社的信息！”活动5.2.1在对象资源管理器中创建INSERT触发器【实现步骤】第1步：在对象资源管理器中依次展开“数据库”→“图书信息管理”→“表”→“出版社”，右键单击“触发器”，选择“新建触发器”，打开触发器编辑模板进行如下代码的编写：USE图书信息管理GOCREATETRIGGER添加新出版社ON出版社AFTERINSERT--AFTERINSERT为插入记录后触发ASBEGINPRINT('插入新出版社的信息！');--输出提示信息ENDGO活动5.2.1在对象资源管理器中创建INSERT触发器第2步：单击按钮。这样就给“出版社”表创建了INSERT触发器。当向“出版社”表插入数据时将激发触发器，并给出提示信息，同时数据插入表中。第3步：验证该案例。在查询编辑器中运行如下语句：INSERTINTO出版社VALUES(’汕头大学出版社’,’8885454’,’汕头市’)说明：由于出版社号为标识列，其值由系统自动生成，故在VALUES值列中不出现该字段的值。执行上述操作后将返回提示信息“插入新出版社的信息！”，同时将新记录插入“出版社”表中。可通过以下代码查看，发现记录已插入表中。活动5.2.2利用T-SQL命令创建DELETE触发器除了在对象资源管理器中创建触发器，还可以利用T-SQL命令来创建触发器。下面利用T-SQL命令创建DELETE触发器。案例2利用T-SQL命令在图书信息管理数据库的“出版社”表中创建“删除出版社”触发器，当删除“出版社”表中的记录时，触发器将保护数据，中止删除“出版社”操作并提示。【实现步骤】（1）单击打开查询编辑器，编写代码如下：脚本中“INSTEADOFDELETE”表示当删除记录时触发器触发,并取消删除操作；“PRINT('不允许对出版社进行删除！操作取消。')”显示“不允许对出版社进行删除！操作取消。”的提示信息。USE图书信息管理GOCREATETRIGGER删除出版社ON出版社INSTEADOFDELETE--INSTEADOFDELETE不执行删除操作ASPRINT('不允许对出版社进行删除！操作取消。')GO活动5.2.3创建UPDATE触发器在带有UPDATE触发器的表上执行UPDATE语句时，将触发UPDATE触发器。案例3利用T-SQL命令在图书信息管理数据库的“出版社”表中创建“更新出版社”触发器，当对表执行“更新”操作时候触发，不允许用户对“出版社”记录进行修改，并提示。【实现步骤】打开查询编辑器编写代码如下：执行该代码后，将在“出版社”表创建了一个UPDATE触发器，当对“出版社”表中的数据进行修改时将激发UPDATE触发器，不允许对数据进行修改。USE图书信息管理GOCREATETRIGGER更新出