sql sever 数据库 入门-存储过程

第5章数据库对象的操作返回本章首页上一页下一页5.5存储过程5.5.1创建存储过程5.5.2执行存储过程5.5.3查看和修改存储过程5.5.4重命名和删除存储过程第5章数据库对象的操作返回本章首页上一页下一页存储过程的概念SQLServer提供了一种方法，它可以将一些固定的操作集中起来由SQLServer数据库服务器来完成，以实现某个任务，这种方法就是存储过程。在SQLServer中存储过程分为两类：即系统提供的存储过程和用户自定义的存储过程。第5章数据库对象的操作返回本章首页上一页下一页5.5.1创建存储过程在SQLServer中，可以使用三种方法创建存储过程：①使用创建存储过程向导创建存储过程。②利用SQLServer企业管理器创建存储过程。③使用Transact-SQL语句中的CREATEPROCEDURE命令创建存储过程。第5章数据库对象的操作返回本章首页上一页下一页创建存储过程时，需要确定存储过程的三个组成部分：①所有的输入参数以及传给调用者的输出参数。②被执行的针对数据库的操作语句，包括调用其它存储过程的语句。③返回给调用者的状态值，以指明调用是成功还是失败。第5章数据库对象的操作返回本章首页上一页下一页1.使用创建存储过程向导创建存储过程在企业管理器中，选择工具菜单中的向导选项，选择“创建存储过程向导”（如图3-1所示），则出现欢迎使用创建存储过程向导对话框，如图5-71所示。根据图5-71--图5-76提示可完成创建存储过程。图3-1新建SQLServer组第5章数据库对象的操作返回本章首页上一页下一页图5-71欢迎使用创建存储过程向导对话框第5章数据库对象的操作返回本章首页上一页下一页图5-72选择数据库对话框第5章数据库对象的操作返回本章首页上一页下一页图5-73选择数据库对象对话框第5章数据库对象的操作返回本章首页上一页下一页图5-74完成创建存储过程向导对话框第5章数据库对象的操作返回本章首页上一页下一页图5-75编辑存储过程属性对话框第5章数据库对象的操作返回本章首页上一页下一页图5-76编辑存储过程SQL对话框第5章数据库对象的操作返回本章首页上一页下一页2.使用SQLServer企业管理器创建存储过程（１）在SQLServer企业管理器中，选择指定的服务器和数据库，用右键单击要创建存储过程的数据库，在弹出的快捷菜单中选择“新建”选项，再选择下一级菜单中的“存储过程…”选项，如图5-77所示；或者用右键单击存储过程图标，从弹出的快捷菜单中选择“新建存储过程…”选项，如图5-78所示。均会出现创建存储过程对话框，如图5-79所示。（２）在文本框中可以输入创建存储过程的T_SQL语句，单击“检查语法”，则可以检查语法是否正确；单击“确定”按钮，即可保存该存储过程。如果要设置权限，单击“权限…”按钮，如图5-80所示。第5章数据库对象的操作返回本章首页上一页下一页图5-77选择新建存储过程对话框（1）第5章数据库对象的操作返回本章首页上一页下一页图5-78选择新建存储过程对话框（2）第5章数据库对象的操作返回本章首页上一页下一页图5-79新建存储过程对话框第5章数据库对象的操作返回本章首页上一页下一页图5-80设置权限对话框第5章数据库对象的操作返回本章首页上一页下一页3.使用Transact-SQL语句中的CREATEPROCEDURE命令创建存储过程创建存储过程前，应该考虑下列几个事项：①不能将CREATEPROCEDURE语句与其它SQL语句组合到单个批处理中。②创建存储过程的权限默认属于数据库所有者，该所有者可将此权限授予其他用户。③存储过程是数据库对象，其名称必须遵守标识符规则。④只能在当前数据库中创建存储过程。⑤一个存储过程的最大尺寸为128M。第5章数据库对象的操作返回本章首页上一页下一页使用CREATEPROCEDURE创建存储过程的语法形式如下：CREATEPROC[EDURE]procedure_name[;number][{@parameterdata_type}[VARYING][=default][OUTPUT]][,...n]WITH[{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}][FORREPLICATION]ASsql_statement[...n]第5章数据库对象的操作返回本章首页上一页下一页用CREATEPROCEDURE创建存储过程的语法参数的意义如下：procedure_name：用于指定要创建的存储过程的名称。number：该参数是可选的整数，它用来对同名的存储过程分组，以便用一条DROPPROCEDURE语句即可将同组的过程一起除去。@parameter：过程中的参数。在CREATEPROCEDURE语句中可以声明一个或多个参数。data_type：用于指定参数的数据类型。VARYING：用于指定作为输出OUTPUT参数支持的结果集。Default：用于指定参数的默认值。OUTPUT：表明该参数是一个返回参数。第5章数据库对象的操作返回本章首页上一页下一页RECOMPILE：表明SQLServer不会保存该存储过程的执行计划。ENCRYPTION：表示SQLServer加密了syscomments表，该表的text字段是包含CREATEPROCEDURE语句的存储过程文本。FORREPLICATION：用于指定不能在订阅服务器上执行为复制创建的存储过程。AS：用于指定该存储过程要执行的操作。sql_statement：是存储过程中要包含的任意数目和类型的Transact-SQL语句。第5章数据库对象的操作返回本章首页上一页下一页5.5.2执行存储过程直接执行存储过程可以使用EXECUTE命令来执行，其语法形式如下：[[EXEC[UTE]]{[@return_status=]{procedure_name[;number]|@procedure_name_var}[[@parameter=]{value|@variable[OUTPUT]|[DEFAULT]}[,...n]][WITHRECOMPILE]}第5章数据库对象的操作返回本章首页上一页下一页例子5-27：使用EXECUTE命令传递单个参数，它执行showind存储过程，以titles为参数值。showind存储过程需要参数(@tabname)，它是一个表的名称。其程序清单如下：EXECshowindtitles当然，在执行过程中变量可以显式命名：EXECshowind@tabname=titles如果这是isql脚本或批处理中第一个语句，则EXEC语句可以省略：showindtitles或者showind@tabname=titles第5章数据库对象的操作返回本章首页上一页下一页5.5.3查看和修改存储过程查看存储过程存储过程被创建之后，它的名字就存储在系统表sysobjects中，它的源代码存放在系统表syscomments中。可以使用使用企业管理器或系统存储过程来查看用户创建的存储过程。第5章数据库对象的操作返回本章首页上一页下一页（1）使用企业管理器查看用户创建的存储过程在企业管理器中，打开指定的服务器和数据库项，选择要创建存储过程的数据库，单击存储过程文件夹，此时在右边的页框中显示该数据库的所有存储过程。用右键单击要查看的存储过程，从弹出的快捷菜单中选择属性选项，此时便可以看到存储过程的源代码。第5章数据库对象的操作返回本章首页上一页下一页（2）使用系统存储过程来查看用户创建的存储过程可供使用的系统存储过程及其语法形式如下：sp_help：用于显示存储过程的参数及其数据类型sp_help[[@objname=]name]参数name为要查看的存储过程的名称。sp_helptext：用于显示存储过程的源代码sp_helptext[[@objname=]name]参数name为要查看的存储过程的名称。sp_depends：用于显示和存储过程相关的数据库对象sp_depends[@objname=]’object’参数object为要查看依赖关系的存储过程的名称。sp_stored_procedures：用于返回当前数据库中的存储过程列表第5章数据库对象的操作返回本章首页上一页下一页2.修改存储过程存储过程可以根据用户的要求或者基表定义的改变而改变。使用ALTERPROCEDURE语句可以更改先前通过执行CREATEPROCEDURE语句创建的过程，但不会更改权限，也不影响相关的存储过程或触发器。其语法形式如下：ALTERPROC[EDURE]procedure_name[;number][{@parameterdata_type}[VARYING][=default][OUTPUT]][,...n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}][FORREPLICATION]ASsql_statement[...n]第5章数据库对象的操作返回本章首页上一页下一页例子5-29：创建了一个名为Oakland_authors的过程，默认情况下，该过程包含所有来自加利福尼亚州奥克兰市的作者。随后授予了权限。然后，当该过程需更改为能够检索所有来自加利福尼亚州的作者时，用ALTERPROCEDURE重新定义了该存储过程。其程序清单如下：第5章数据库对象的操作返回本章首页上一页下一页5.5.4重命名和删除存储过程1.重命名存储过程修改存储过程的名称可以使用系统存储过程sp_rename，其语法形式如下：sp_rename原存储过程名称，新存储过程名称另外，通过企业管理器也可以修改存储过程的名称。第5章数据库对象的操作返回本章首页上一页下一页2.删除存储过程删除存储过程可以使用DROP命令，DROP命令可以将一个或者多个存储过程或者存储过程组从当前数据库中删除，其语法形式如下：dropprocedure{procedure}[,…n]当然，利用企业管理器也可以很方便地删除存储过程。