存储过程(SQL2008)

存储过程存储过程概述创建存储过程查看、修改、删除存储过程存储过程综合举例8.1存储过程概述1.存储过程的定义存储过程：是为了能实现某种功能预先编写好的一组T-SQL语句（该语句可包含几乎所有的T-SQL语句，如：数据存取语句、流程控制语句、错误处理语句等等），经编译优化后以特定的名称存储在数据库服务器中。当需要完成该功能时，用户只需通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行（在服务器端执行）该存储过程，就可以完成相应的功能。2.使用存储过程的优点如果不使用存储过程，那么当在客户端输入SQL语句之后，它会发送到数据库服务器端，由服务器进行下列操作：2.使用存储过程的优点（续）如果使用了存储过程，当再次执行前面的SQL语句时，不需要重新执行语法分析、语义检查和优化分析，直接调用以前生成好的可执行代码，显然这样的执行效率要高，如下图所示：2.使用存储过程的优点（续）可以在程序中被多次调用，而不必重新编写。还可随时对存储过程进行修改经编译和优化后存储在数据库服务器中，提高执行效率在服务器端执行，降低客户机和服务器之间的通信量保证数据库安全，有利于集中控制，方便维护3.存储过程的分类系统存储过程#由系统创建、管理和使用的存储过程。主要存储在master数据库，以sp_为前缀用户只能对其调用，不能对其修改或删除通过系统存储过程能够得到系统信息或为数据库系统管理员管理SQLServer提供支持扩展存储过程#以xp_为前缀。扩展存储过程允许使用其它编程语言（诸如C或C++或C#等）语言创建自己的外部存储过程，其内容并不存储在SQL2005中，而是以DLL（动态链接库）的形式单独存放，它允许用户使用DLL访问SQLServer用户定义的存储过程由用户为完成某一特定功能而编写的存储过程常见的系统存储过程：系统存储过程说明sp_databases列出服务器上的所有数据库sp_helpdb报告有关指定数据库或所有数据库的信息sp_renamedb更改数据库的名称sp_tables返回当前环境下可查询的对象的列表sp_columns返回某个表列的信息sp_help查看某个表的所有信息sp_helpconstraint查看某个表的约束sp_helpindex查看某个表的索引sp_stored_procedures列出当前环境中的所有存储过程sp_helptext显示默认值、未加密的存储过程、用户定义的存储过程、触发器或视图的实际文本安全管理类系统存储过程sp_addlogin创建新的SQLServer登录，使用户可以连接使用SQLServer身份验证的SQLServer实例sp_droplogin删除SQLServer登录，以阻止使用该登录名访问SQLServersp_helplogins提供有关每个数据库中的登录及相关用户的信息sp_grantlogin使WindowsNT用户或组帐户可以使用Windows身份验证连接到SQLServersp_denylogin阻止WindowsNT用户或组连接到SQLServersp_grantdbaccess为SQLServer登录或WindowsNT用户或组在当前数据库中添加一个安全帐户，并使其能够被授予在数据库中执行活动的权限sp_revokedbaccess从当前数据库中删除安全帐户sp_defaultdb更改登录的默认数据库sp_password添加或更改SQLServer登录的密码常见的扩展存储过程：常用的扩展存储过程：xp_cmdshell作用：它可以完成DOS命令下的一些操作，诸如创建文件夹、列出文件等格式：EXECxp_cmdshellDOS命令[NO_OUTPUT]举例：USEmasterGO--创建文件夹H:\prodexecxp_cmdshell'mkdirH:\prod',NO_OUTPUTexecxp_cmdshell'dirH:\prod\'--查看文件8.2创建存储过程创建存储过程的方法方法1：用T-SQL语句创建存储过程方法2：用SSMS创建存储过程1、用T-SQL语句创建存储过程的格式为：CREATEPROC[EDURE]存储过程名[{@参数名参数类型}[=默认值][OUTPUT],.......,{@参数n数据类型}[=默认值][OUTPUT]]AS{SQL语句或语句块}存储过程名：强烈建议存储过程名不要以sp_为开头参数名必须以“@”为前缀，在CREATEPROCEDURE语句中可以声明一个或多个参数。OUTPUT：表明该参数是返回参数即输出参数。它也可以简写为OUT，且在调用该存储过程时该参数后必须也带OUTPUT关键字。否则没有返回值。SQL语句块：完整的SQL语句块由声明部分和执行部分组成参数说明：声明部分部分((DECLARE)：变量、常量、游标、异常等执行部分：SQL语句、流程控制语句例：创建一个不带参数的存储过程，列出学生表中年龄大于20岁的所有学生信息。USEStudentGOCREATEPROCEDUREStudentListAS/*用AS引出存储过程的内容*/SELECT*FROMStudentWHEREAge20执行该存储过程的语句为：EXECStudentList例：创建一个带输入参数的存储过程InsertRecord，该存储过程的功能是向学生数据库的student中插入一条记录，新记录的值由参数提供。USE学生数据库GOCREATEPROCEDUREInsertRecord（@snochar(6),@snchar(20),@agenumeric(5),@sexchar(2),@deptchar(10)）ASINSERTINTOstudentVALUES(@sno,@sn,@age,@sex,@dept)GO可以用下面三种方式的调用上页存储过程：（1）EXECInsertRecord@sno='S1',@sn='王大利',@sex='男',@age=18,@dept='计算机系'/*赋值式的调用*/（2）EXECInsertRecord'S1','王大利','男',18,'计算机系'/*使用参数列表调用*/（3）EXECInsertRecord'S1','王大利','男',@age=18,@dept='计算机系'/*上面两种的混合调用*/例：创建一个具有参数默认值的存储过程InsertRecordDefa，该存储过程的功能是向学生数据库的student中插入一条记录，新记录的值由参数提供，如果未提供系别dept的值时，由参数的默认值计算机系代替代替。执行该存储过程的语句为：EXECInsertRecordDefa'S10','高平','女',18USE学生数据库goCREATEPROCEDUREInsertRecordDefa(@snochar(6),@snchar(20),@sexchar(2),@agenumeric(5),@deptchar(10)＝'计算机系')ASINSERTINTOstudentVALUES(@sno,@sn,@sex,@age,@dept)GO例：定义带输出参数的的存储过程Query_Study，该存储过程的功能是从学生数据库的学生情况表中根据学号查询某一同学的姓名和所在系。执行该存储过程的语句为：DECLARE@snamechar(20)/*定义输出*/DECLARE@deptchar(10)/*定义输出*/EXECUTEQuery_Study'020101',@snameOUTPUT,@deptOUTPUTselect'姓名1'=@sname,'系别'=@deptUSE学生数据库GOCREATEPROCEDUREQuery_Study(@snochar(6),@snamechar(20)OUTPUT,@deptchar(10)OUTPUT)ASSELECT@sname=姓名,@dept=所在系FROM学生情况表WHERE学号=@snoGO例：执行spAdd存储过程，输入参数由value1和value2提供，输出参数valueTotal。执行该存储过程的语句为：CREATEPROCEDUREspAdd@Value1INT,@Value2INT,@ResultValueINTOUTPUTASSELECT@ResultValue=@Value1+@Value2GODECLARE@valueTotalINTEXECspAdd100,200,@valueTotalOUTPUTPRINT'100'+'与'+'200'+'的和等于:'+CONVERT(CHAR(5),@valueTotal)GO例：带有带有RETURNRETURN命令命令的存储过程举例。执行该存储过程的语句为：CREATEPROCEDUREReturn_ValASDECLARE@XINTSELECT@X=AVG(成绩)From成绩表Return@XGODECLARE@YINTEXEC@Y=Return_ValPRINT@YGO注意：用RETURN命令可以让存储过程立即终止，并向调用过程返回一个整数值。CREATEPROCEDUREJudgeOneInput(@myInputINT)ASIF@myInput100RETURN0ELSERETURN-101执行存储过程：DECLARE@ResultOfJudgeINTEXEC@ResultOfJudge=JudgeOneInput200PRINT@ResultOfJudgeEXEC@ResultOfJudge=JudgeOneInput10PRINT@ResultOfJudge执行结果如下：执行结果如下：00--101101例：编写一个存储过程，当输入值大于100则返回0，小于等于100则返回-101。②用SSMS创建存储过程的步骤：stept1：在SSMS中，展开需要创建存储过程的数据库→展开可编程性→右击存储过程，选择“新建存储过程”，如下图所示，出现创建存储过程窗口。stept2：在文本框中输入创建存储过程的Transact_SQL语句，单击“执行”按钮，即可创建该存储过程。8.3查看、修改、删除存储过程查看、修改、删除存储过程的方法方法方法11：用用TT--SQLSQL语句语句查看存储过程修改存储过程删除存储过程方法方法22：用SSMS查看存储过程修改存储过程删除存储过程用T-SQL语句查看存储过程存储过程被创建以后，它的名字存储在系统表sysobjects中；它的源代码存放在系统表syscomments中。可以通过SQLServer提供的系统存储过程来查看关于用户创建的存储过程信息。sp_help存储过程名：输出存储过程的参数和数据类型。sp_helptext存储过程名：输出未加密的存储过程的源代码。但如果在创建存储过程时使用了WITHENCRYPTION选项，那么就无法查看到存储过程的源代码。sp_depends存储过程名：输出和存储过程相关的数据库对象。sp_rename原存储过程名,新存储过程名：重命名存储过程sp_stored_procedures：用于返回当前数据库中的存储过程列表，用T-SQL语句修改存储过程的格式ALTERPROC[EDURE]存储过程名[{@参数名参数类型}[=默认值][OUTPUT],.......,{@参数n数据类型}[=默认值][OUTPUT]]]AS{SQL语句或语句块}例例：把下列存储过程改为查找年龄大于：把下列存储过程改为查找年龄大于2222的学生信息。的学生信息。USEStudentGOCREATEPROCEDUREStudentListAS/*用AS引出存储过程的内容*/SELECT*FROMStudentWHEREAge20USEStudentGOALTERALTERPROCEDUREStudentListAS/*用AS引出存储过程的内容*/SELECT*FRO