SQL Server 2008数据库实用教程-第07章

第7章存储过程和触发器7.1存储过程

7.1存储过程是一组存储在数据库中的SQL子程序，用户可以直接调用执行相同操作的存储过程来保证数据的一致性。7.1.1存储过程的类型在MicrosoftSQLServer中有三种可用的存储过程，分别为

用户定义的存储过程、扩展存储过程、系统存储过程。7.1.1存储过程的类型

(1)用户定义的存储过程

存储过程是指封装了可重用代码的模块或例程。存储过程可以接受输入参数、向客户端返回表格或标量结果和消息、调用数据定义语言(DDL)和数据操作语言(DML)语句，然后返回输出参数。在SQLServer2008中，存储过程有两种类型：TransacT.SQL或CLR。①TransacT.SQL存储过程是指保存的TransacT.SQL语句集合，可以接受和返回用户提供的参数。②CLR存储过程是指对Microsoft.NETFramework公共语言运行时(CLR)方法的引用，可以接受和返回用户提供的参数。

(2)扩展存储过程

扩展存储过程允许您使用编程语言（例如C）创建自己的外部例程。扩展存储过程是指MicrosoftSQLServer的实例可以动态加载和运行的DLL。(3)系统存储过程它是由SQLserver系统提供的存储过程，定义在系统数据库中，并以sp_开头，存储过程允许具有执行系统存储过程权限的用户执行修改表的任务，且可以再所有数据库中执行。7.1.2用户存储过程的创建与执行

一、用户存储过程的创建

1.用T.SQL语句创建存储过程在T.SQL语句中，用户可以使用CREATEPROCEDURE来创建存储过程，其语法格式如下：CREATEPROCEDUREprocedure_name[WITH{RECOMPILE|ENCRYPTION|RECOMPILE},ENCRYPTION]ASSql_statement[,…n]

7.1.2用户存储过程的创建与执行【例7.1】下面创建从db_stu数据库中查询选课表SC中学生成绩信息的存储过程，其具体操作如下：①在查询分析器中输入如下语句。USEdb_stuGOCREATEPROCEDURESTUASSELECTStudent.Sno,Cno,GradeFROMStudent,SCWHEREStudent.Sno=SC.SnoGO7.1.2用户存储过程的创建与执行②在工具栏中单击按钮，完成存储过程的创建，此时在db_stu数据库的存储过程窗口可以看到新建的STU的存储过程，如下图7.1所示。7.1.2用户存储过程的创建与执行2.使用对象资源管理器创建存储过程在对象资源管理器下创建存储过程的具体操作步骤如下：

①在对象资源管理器中，连接到某个数据库引擎实例，再展开该实例。

②展开“数据库”、数据库db_stu以及”可编程性”。

③右键单击“存储过程”，再单击“新建存储过程”,如图7.2所示。7.1.2用户存储过程的创建与执行图7.2新建存储过程7.1.2用户存储过程的创建与执行④在“查询”菜单上，单击“指定模板参数的值”。如图7.3所示图7.3打开参数对话框7.1.2用户存储过程的创建与执行⑤在“指定模板参数的值”对话框中，“值”列包含参数的建议值。接受这些值或将其替换为新值，再单击“确定”。如图7.4所示。图7.4参数对话框7.1.2用户存储过程的创建与执行⑥在查询编辑器中，使用过程语句替换SELECT语句。如图7.5所示。图7.5输入搜索语句7.1.2用户存储过程的创建与执行⑦若要测试语法，请在“查询”菜单上，单击“分析”。⑧若要创建存储过程，请在“查询”菜单上，单击“执行”。如图7.6所示。图7.6创建存储过程7.1.2用户存储过程的创建与执行⑨若要保存脚本，请在“文件”菜单上，单击“保存”。接受该文件名或将其替换为新的名称，再单击“保存”。7.1.2用户存储过程的创建与执行二、用户存储过程的执行

已创建的存储过程可以使用EXECUTE（或简写EXEC命令执行），下面调用执行已创建的存储过程STU.其具体操作如下：①在查询分析器中输入如下语句：USEdb_stuGOEXECSTU

7.1.2用户存储过程的创建与执行②在工具栏中单击按钮，执行存储过程。另外，可以在将要执行的存储过程名字上点右键，选择“执行存储过程”，如图7.7所示。执行结果如图7.8所示。图7.7右键执行存储过程7.1.2用户存储过程的创建与执行图7.8存储过程执行结果7.1.3用户存储过程的编辑修改

创建存储过程后，如果需要改变存储过程中的语句或参数，可以修改或重命名存储过程。编辑修改存储过程的步骤如下：①在对象资源管理器中，连接到某个数据库引擎实例，再展开该实例。②依次展开“数据库”、存储过程所属的数据库，例如：数据库db_stu以及“可编程性”。③展开“存储过程”，右键单击要修改的过程，再单击“修改”。如图7.9所示。7.1.3用户存储过程的编辑修改图7.9选中“修改”7.1.3用户存储过程的编辑修改④利用T.SQL语句中的ALTERPROCEDURE命令修改存储过程的文本。如图7.10所示。图7.10修改存储过程修改命令具体修改内容7.1.3用户存储过程的编辑修改⑤若要测试语法，请在“查询”菜单上，单击“分析”。⑥若要修改存储过程，请在“查询”菜单上，单击“执行”，结果如图7.11所示。⑦若要保存脚本，请在“文件”菜单上单击“另存为”。接受文件名或使用新名图7.11修改结果7.1.4用户存储过程的删除

当存储过程不再使用时，可以将其删除，存储过程的删除也有使用T.SQL语句和使用对象资源管理器两种方法。1.在T.SQL语句中，可以使用DROPPROCEDURE语句删除存储过程，其语法格式如下：DROPPROCEDURE{procedure_name}[,..n]例如，下面的语句用于删除存储过程procedurenameUSEdb_stuDROPPROCEDUREprocedurename

7.1.4用户存储过程的删除2.使用对象资源管理器删除存储过程，操作步骤如下：①在对象资源管理器中，连接到某个数据库引擎实例，再展开该实例。②依次展开“数据库”、存储过程所属的数据库例如db_stu以及“可编程性”。③展开“存储过程”，右键单击要删除的过程，再单击“删除”。④若要查看基于存储过程的对象，请单击“显示依赖关系”。如图7.12所示。⑤确认已选择了正确的存储过程，再单击“确定”。⑥从依赖对象和脚本中删除存储过程名称。图7.12“删除对象”对话框7.2触发器触发器是一种特殊的存储过程，用于改变指定表中的数据，它能自动执行并成为SQL语句的一部分。触发器创建在一个表的基础上，并和一个或多个数据的编辑操作相关联。在SQLServer2008中触发器分为两类：DML触发器和DDL触发器。7.2.1利用SQL语句创建触发器

1.用CREATETRIGGER命令创建AFTER触发器，其语法格式如下：CREATETRIGGERtrigger_nameON[table_name|view_name]AFTER[INSERT][,][UPDATE][,][DELETE]ASSql_statement[,…n]7.2.1利用SQL语句创建触发器2.用CREATETRIGGER命令创建INSTEADOF触发器，其语法格式如下：CREATETRIGGERtrigger_nameON[table_name|view_name]INSTEADOF[INSERT][,][UPDATE][,][DELETE]ASSql_statement[,…n]7.2.2利用sqlservermanagementstudio创建触发器

在sqlservermanagementstudio创建触发器的具体步骤如下：①在对象资源管理器中，登录服务器，并展开数据库，例如：db_stu,选择“表”，在展开项中选择表Student,点击它前面的+，在其展开项中选择“触发器”，右击，在弹出菜单中选择“新建触发器”，如图7.13所示。7.2.2利用sqlservermanagementstudio创建触发器图7.13选择“新建触发器”7.2.2利用sqlservermanagementstudio创建触发器②点击“新建触发器”，在出现的窗口中输入T.SQL语句，如图7.14所示。图7.14新建触发器7.2.2利用sqlservermanagementstudio创建触发器③输入完成后，点击菜单栏上的按钮，在该表的“触发器”下面就可以看到新建的触发器。7.2.3触发器的修改和删除

触发器建立好之后，根据具体的需要会对所建的触发器进行修改，如果不需要的话也要删除。7.2.3触发器的修改和删除一、触发器的修改

1.触发器的修改，步骤如下：

①展开数据库，找到“表”，在其中一个表的“触发器”项中选择需要修改的触发器的名字。

②点击右键，在弹出菜单中选择“修改”命令，在窗口右侧可以查看所选触发器的相关内容。如图7.14所示。

7.2.3触发器的修改和删除图7.14修改触发器7.2.3触发器的修改和删除2.使用ALTERTRIGGER命令修改触发器ALTERTRIGGER命令的语法格式如下：ALTERTRIGGERtrigger_nameON[table_name|view_name]FOR[INSERT][,][UPDATE][,][DELETE]ASSql_statement[,…n]7.2.3触发器