第九章存储过程与触发器.

9.1存储过程9.2触发器目录第九章存储过程与触发器第九章存储过程与触发器存储过程由一组预先编辑好的SQL语句组成。将其放在服务器上，由用户通过指定存储过程的名称来执行。触发器是一种特殊类型的存储过程，它不是由用户直接调用的，而是当用户对数据进行操作（包括数据的INSERT、UPDATE或DELETE操作）时自动执行。本章主要介绍存储过程和触发器的基本概念及其创建、修改和使用等操作方法。9.1存储过程综述9.1.1存储过程的概念存储过程是一种数据库对象，是为了实现某个特定任务，将一组预编译的SQL语句以一个存储单元的形式存储在服务器上，供用户调用。存储过程在第一次执行时进行编译，然后将编译好的代码保存在高速缓存中便于以后调用，这样可以提高代码的执行效率。存储过程与其他编程语言中的过程相似。有如下特点：●接受输入参数并以输出参数的形式将多个值返回至调用过程或批处理。●包含执行数据库操作（包括调用其他过程）的编程语句。●向调用过程或批处理返回状态值，以表明成功或失败（以及失败原因）。9.1.2存储过程的类型在SQLServer中存储过程可以分为五类。即系统存储过程、本地存储过程、临时存储过程、远程存储过程和扩展存储过程。●系统存储过程：系统存储过程存储在master数据库中，并以“sp_”为前缀，主要用来从系统表中获取信息，为系统管理员管理SQLServer提供帮助，为用户查看数据库对象提供方便。比如用来查看数据库对象信息的系统存储过程sp_help。●本地存储过程：本地存储过程是用户根据需要，在自己的普通数据库中创建的存储过程。●临时存储过程：临时存储过程通常分为局部临时存储过程和全局临时存储过程。创建局部临时存储过程时，要以“#”作为过程名称的第一个字符。创建全局临时存储过程时，要以“##”作为过程名称的前两个字符。临时存储过程在连接到早期版本时很有用，这些早期版本不支持再次使用T-SQL语句或批处理执行计划。连接到SQLServer2005的应用程序应使用sp_executesql系统存储过程，而不使用临时存储过程。●远程存储过程：远程存储过程是SQLServer2005的一个传统功能，是指非本地服务器上的存储过程。现在只有在分布式查询中使用此存储过程。●扩展存储过程：扩展存储过程以“xp_”为前缀，它是关系数据库引擎的开放式数据服务层的一部分，可以使用户在动态数据库（DLL）文件所包含的函数中实现逻辑功能，从而扩展了T-SQL的功能，并且可以像调用T-SQL过程那样从T-SQL语句调用这些参数。下面主要介绍本地存储过程的创建、执行、修改、删除等操作。9.1.3创建、执行、修改、删除简单存储过程简单存储过程即不带参数的存储过程，下面介绍简单存储过程的创建及使用。1.创建简单存储过程在SQLServer中通常可以使用两种方法创建存储过程：一种是使用企业管理器创建存储过程。另一种是使用查询分析器执行SQL语句创建存储过程。创建存储过程时，需要注意下列事项：●只能在当前数据库中创建存储过程。●数据库的所有者可以创建存储过程，也可以授权其他用户创建存储过程。●存储过程是数据库对象，其名称必须遵守标识符命名规则。●不能将CREATEPROCEDURE语句与其他SQL语句组合到单个批处理中。●创建存储过程时，应指定所有输入参数和向调用过程或批处理返回的输出参数、执行数据库操作的编程语句和返回至调用过程或批处理以表明成功或失败的状态值。（1）使用对象资源管理器创建存储过程。下面举例来介绍如何使用对象资源管理器创建存储过程。【例9.1】在student数据库中，创建一个名为ST_CHAXUN_01的存储过程，该存储过程返回计算机系学生的“姓名”、“性别”、“出生日期”信息。操作步骤如下：①在“对象资源管理器”窗格中，展开“数据库”结点。②单击相应的数据库（这里选择student数据库）。依次展开“可编程性”、“存储过程”结点。右击“存储过程”结点，在弹出的快捷菜单中选择“新建存储过程”命令。③打开创建存储过程的初始界面，如图9-1所示。④将初始代码清除，输入存储过程文本，根据题意输入如下语句：SELECT姓名,性别,出生日期FROM学生WHERE系部代码='01'⑤输入完成后，单击“分析”按钮，检查语法是否正确。⑥如果没有任何错误，单击“执行”按钮，将在数据库中创建存储过程。图9-1创建存储过程的界面（2）使用SQL语句创建存储过程。在查询分析器中，用SQL语句创建存储过程的语法格式如下：CREATEPROC[EDURE]procedure_name[;number][{@parameterdata_type}[VARYING][=default][OUTPUT]][,…n][WITH{RECOMPLE|ENCRYPTION|RECOMPLE,ENCRYPTION}][FORREPLICATION]ASsql_statement[,…n]其中：●procedure_name是新建存储过程的名称，其名称必须遵守标识符命名规则，且对于数据库及其所有者必须唯一。●number是可选的整数，用来对同名的过程分组，以便用一条DROPPROCEDURE语句即可将同组的过程一起删除。例如，名为order的应用程序使用的过程可以命名为orderproc1、orderproc2、orderproc3。DROPPROCEDUREorderproc语句将删除整个组。如果名称中包含定界标识符，则数字不应该包含在标识符中，只应在存储过程名前后使用适当的定界符。●parameter是存储过程中的输入和输出参数。●data_type是参数的数据类型。●VARYING用于指定作为输出参数支持的结果集（由存储过程动态构造，内容可以变化）。该选项只适用于游标参数。●default是指参数的默认值，必须是常量或NULL。如果定义了默认值，不必指定该参数的值即可执行过程。●OUTPUT表明参数是返回参数。该选项的值可以返回给EXEC[UTE]。使用OUTPUT参数可将信息返回给调用过程。text、ntext和image参数可用做OUTPUT参数。使用OUTPUT关键字的输出参数可以是游标占位符。●RECOMPLE表明SQLServer不保存存储过程的计划，该过程将在运行时重新编译。在使用非典型值或临时值而不希望覆盖缓存在内存中的计划时，最好使用RECOMPLE选项。●ENCRYPTION表示SQLServer加密syscomments表中包含CREATEPROCEDURE语句文本的条目。●FORREPLICATION用于指定不能在订阅服务器上执行为复制创建的存储过程。使用该选项创建的存储过程可用做存储过程筛选，且只能在复制过程中执行。本选项不能和WITHRECOMPLE选项一起使用。●sql_statement是指存储过程中的任意数目和类型的T-SQL语句。【例9.2】在student数据库中，创建一个查询存储过程ST_PRO_BJ，该存储过程将返回计算机系的班级名称。代码如下：USEstudentGOCREATEPROCEDUREST_PRO_BJASSELECT班级名称FROM班级,系部WHERE系部.系部代码=班级.系部代码and系部.系部名称='计算机系'GO2.执行存储过程对存储在服务器上的存储过程，可以使用EXECUTE命令或其名称执行。其语法格式如下：[[EXEC[UTE]]{[@return_status=]{procedure_name[;number]|@procedure_name_var}[[@parameter=]{value|@variable[OUTPUT]|[DEFAULT]}[,…n][WITHRECOMPLE]其中：●如果存储过程是批处理中的第一条语句，EXECUTE命令可以省略，可以使用存储过程的名字执行该存储过程。●return_status是一个可选的整型变量，用来保存存储过程的名称。●@procedure_name_var是局部定义变量名，用来代表存储过程的名称。其他参数与存储过程命令中参数意义相同。【例9.3】在查询分析器中执行ST_PRO_BJ。代码如下：USEstudentEXECUTEST_PRO_BJGO其执行结果如图9-2所示。3.查看存储过程对用户建立存储过程，可以使用对象资源管理器或有关的系统存储过程查看该存储过程的定义。（1）使用对象资源管理器查看存储过程。操作步骤如下：①在“对象资源管理器”窗格中，展开“数据库”结点。②选择相应的数据库（这里选择student数据库）。依次展开“可编程性”、“存储过程”结点。选择“存储过程”结点，在右窗格中显示出当前数据库中所有的存储过程。③右击需要查看的存储过程，例如ST_PRO_BJ，在弹出图9-2执行存储过程返回的记录集合的快捷菜单中选择“修改”命令，打开存储过程ST_PRO_BJ的源代码界面，如图9-3所示。图9-3存储过程ST_PRO_BJ的源代码界面④在存储过程ST_PRO_BJ的源代码界面中，既可查看存储过程定义信息，又可以在文本框中对存储过程的定义进行修改。修改后，可以单击“执行”按钮，保存修改。（2）使用系统存储过程查看存储过程。在SQLServer中，根据不同需要，可以使用sp_helptext、sp_depends、sp_help等系统存储过程来查看存储过程的不同信息。每个查看存储过程的具体语法和作用如下：①使用sp_helptext查看存储过程的文本信息。其语法格式为：sp_helptext存储过程名②使用sp_depends查看存储过程的相关性。其语法格式为：sp_depends存储过程名③使用sp_help查看存储过程的一般信息。其语法格式为：sp_help存储过程名【例9.4】使用有关系统存储过程查看student数据库中名为ST_PRO_BJ的存储过程的定义、相关性以及一般信息。代码如下：USEstudentGOEXECsp_helptextST_PRO_BJEXECsp_dependsST_PRO_BJEXECsp_helpST_PRO_BJGO在查询分析器中输入并执行上述代码，返回的结果如图9-4所示。4.修改存储过程当存储过程所依赖的基本表发生变化或者根据需要，用户可以对存储过程的定义或者参数进行修改。更改通过执行CREATEPROCEDURE语句创建的过程，不会更改权限，也不影响相关的存储过程或触发器。修改存储过程可以使用ALTERPROCEDURE语句，其语法格式为：ALTERPROC[EDURE]procedure_name[;number][{@parameterdata_type}[VARYING][=default][OUTPUT]][,┄n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}][FORREPLICATION]ASSql_statement[,┄n]其中各个参数与创建存储过程命令中参数意义相同。【例9.5】修改存储过程ST_PRO_BJ，使该存储过程返回经济管理系的班级名称。代码如下：USEstudentGOALTERPROCDBO.ST_PROC_BJASSELECT班级名称FROM班级,系部WHERE系部.系部代码=班级.系部代码and系部.系部名称='经济管理系'GO5．删除存储过程当存储过程不再需要时，可以使用对象资源管理器或DROPPROCEDURE语句将其删除。（1）使用对象资源管理器删除存储过程操作步骤：在“对象资源管理器”窗格中，右击要删除的存储过程，在弹出的快捷菜单中选择“删除”命令，打开“删除对象”对话框，单击“确定”按钮，删除该存储过程。（2）使用DROPPROCEDURE语句删除存储过程：DROPPROCEDURE语句可以一次从当前数据库中将一个或多个存储过程或过程组删除。其语法格式如下：DROPPROCEDURE存储过程名[,…n]【例9.6】删除存储过程ST_CHAXUN_01。代码如下：USEstudentGODROPPROC