数据库存储过程.

1存储过程1.1存储过程概述1.2创建和执行存储过程1.3存储过程的返回值1.4管理存储过程1.5系统存储过程和扩展存储过程1.6存储过程的应用2【技能目标】理解存储过程的作用；学会创建和管理存储过程；学会根据实际需要设计存储过程。【知识目标】理解存储过程的概念和作用；了解存储过程的分类；掌握创建存储过程的方法；掌握执行存储过程的方法；掌握在存储过程中定义和使用输入、输出参数的方法；掌握存储过程返回值的用法；掌握查看、修改和删除存储过程的方法和步骤；了解系统存储过程和扩展存储过程；掌握存储过程的应用。31.1.1存储过程的概念存储过程(StoredPROCEDURE)是独立存在于表之外的数据库对象。它是一组完成特定功能的SQL语句集，经编译后存储在数据库中。存储过程可由应用程序通过一个调用来执行。同时，它可以接收和输出参数，返回执行存储过程的状态值，也可以嵌套调用。1.1存储过程概述4SQLServer中的存储过程与其他编程语言中的过程类似，原因有以下几点。(1)存储过程可以接受输入参数并以输出参数的形式将多个值返回至调用过程或批处理。(2)存储过程可以包含执行数据库操作(包括调用其他过程)的编程语句。(3)存储过程可以向调用过程或批处理返回状态值，以表明成功或失败(以及失败原因)。(4)存储过程可以使用EXECUTE语句调用执行。51.1.2存储过程的优点利用SQLServer2005创建一个应用程序时，可以选用两种方法：一是在本地存储Transact-SQL程序，创建应用程序向SQLServer服务器发送命令来对结果进行处理；二是使用存储过程，创建应用程序来调用存储过程执行。通常第二种方法更好一些，即在SQLServer中使用存储过程而不是在客户计算机上调用Transact-SQL编写的一段程序。6存储过程具有以下优点：(1)允许标准组件式编程。存储过程创建好后，就可以在程序中被多次调用，而不必重新编写该存储过程的Transact-SQL语句。而且数据库专业人员可随时对存储过程进行修改，且对应用程序源代码毫无影响，因为应用程序源代码只包含存储过程的调用语句，从而极大地提高了程序的可移植性。(2)执行速度快。存储过程在创建的时候就被编译和优化了。程序调用一次存储过程后，相关信息就保存在内存中，下次调用时可以直接执行，因而执行速度快。7(3)能够有效降低网络流量。使用存储过程可将调用数百行Transact-SQL语句的操作通过一条执行存储过程的语句来完成，而不需要在网络中发送数百行代码，因此有效地降低了网络流量。(4)提高数据库的安全性。数据库管理员通过对执行某一存储过程的权限进行限制，能够实现对相应数据访问权限的限制，避免非授权用户对数据的访问，保证数据的安全。81.1.3存储过程的种类SQLServer2005支持系统存储过程、用户自定义存储过程。1)系统存储过程系统存储过程是由系统创建的，它存储在master数据库中，并以sp_为前缀。系统存储过程主要从系统表中获取信息，从而为系统管理员管理SQLServer提供支持。通过系统存储过程，SQLServer中的许多管理性或信息性的活动(如了解数据库对象、数据库信息)都可以被顺利有效地完成。92)用户自定义存储过程用户自定义存储过程是在用户数据库中创建，完成特定数据库任务的存储过程。一般所说的存储过程就是指用户自定义存储过程。强烈建议用户自定义存储过程命名时不要以sp_作为前缀，因为SQLServer使用该前缀来标识系统存储过程。101.2.1创建存储过程1.重名的存储过程检测创建存储过程前要确保没有重名的存储过程。具体操作方法是：检查系统表sysobjects，若在系统表中有要创建的存储过程名，并且类型为“P”，说明该对象为存储过程，则应将已经存在的存储过程删除。创建存储过程之前可执行以下语句：1.2创建和执行存储过程11USE操作数据库IFEXISTS(SELECTnameFROMsysobjectsWHEREname='要创建的存储过程名'ANDtype='P')DROPPROCEDURE要创建的存储过程名122.创建存储过程语法在SQLServer2005中使用CREATEPROCEDURE语句创建存储过程。创建存储过程的语法格式如下：CREATEPROCEDUREPROCEDURE_name[;number][{@parameterdata_type}[=default][OUTPUT]][,...n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}][ASsql_statement]13参数说明如下：● PROCEDURE_name：存储过程的名称。● number：可选的整数，用来对同名的过程分组，以便用一条DROPPROCEDURE语句将同组的过程一起删除。● @parameter：过程中的参数。可以声明一个或多个参数，多个参数之间用逗号隔开。● data_type：参数的数据类型。14● default：参数的默认值。如果定义了默认值，则不必指定该参数的值即可执行过程。默认值必须是常量或NULL。● OUTPUT：参数是输出参数。该选项的值可以返回给EXEC[UTE]。● RECOMPILE|ENCRYPTION|RECOMPILE，ENCRYPTION：RECOMPILE表明该过程将在运行时重新编译；ENCRYPTION表示对创建的存储过程加密。● AS：指定过程要执行的操作。● sql_statement：过程中要包含的任意数目和类型的Transact-SQL语句。15【例1-1】在SCMS数据库中创建一个名为p_stu的存储过程，该存储过程返回所有的性别为“男”的学生信息。CREATEPROCEDUREp_stuASSELECT*FROMstudentWHEREssex='男'执行以上语句，p_stu存储过程即可创建。16存储过程一旦创建好了，可以在对象资源管理器窗口查看该存储过程的属性。具体操作步骤是：在“对象资源管理器”窗口中展开“数据库|SCMS|可编程性|存储过程”节点，可以看到当前数据库的全部存储过程，右击选中p_stu存储过程，在弹出的快捷菜单中，选择“属性”选项，打开“存储过程属性”对话框，如图1-1所示。可以查看存储过程相关信息，包括创建者、创建时间、是否加密等信息。17图1-1“存储过程属性”对话框181.2.2执行存储过程使用EXECUTE(也可以写为EXEC)语句调用执行存储过程。其语法格式如下：EXECPROCEDURE_name[@parameter=]{value|@variable[OUTPUT]|[DEFAULT]}[,…n]参数说明如下：● @parameter：表示该存储过程定义的参数。● value：提供给参数的值。如果参数名称没有指定，参数值必须以在存储过程中定义的顺序提供。19● @variable：用来存储参数或返回参数的变量。● OUTPUT：指定存储过程返回一个参数。该存储过程的匹配参数也必须使用关键字OUTPUT创建。● DEFAULT：参数有默认值。【例1-2】执行例1-1创建的存储过程。EXECp_stu执行结果如图1-2所示。20图1-2执行p_stu存储过程结果211.2.3存储过程的参数存储过程提供的参数，为存储过程的应用带来了许多灵活性。给存储过程设定参数的主要目的是通过参数向存储过程输入和输出信息来扩展存储过程的功能。存储过程的参数分为输入参数和输出参数。1.带输入参数的存储过程输入参数是指由调用程序向存储过程传递的参数。创建存储过程时需要定义输入参数的名称和类型，执行存储过程时需要给出相应的参数值。输入参数的语法格式如下：@parameterdata_type[=default]22【例1-3】创建一个存储过程p_stuNew，该过程根据给定的性别返回对应的student表中的记录。分析：在例1-1中，执行存储过程p_stu，只能返回男生的相关信息，而在存储过程p_stuNew中，根据要求，有一个输入参数，用来向存储过程传入指定的性别，实现按指定性别进行信息查询。CREATEPROCEDUREp_stuNew@ssexchar(2)ASSELECT*23FROMstudentWHEREssex=@ssex【例1-4】创建一个存储过程p_studentPara，该过程根据给定的班级号返回对应的student表中的记录。分析：根据要求，存储过程p_studentPara需要一个输入参数，用来向存储过程传入指定的班级号。CREATEPROCEDUREp_studentPara@classnovarchar(8)ASSELECT*FROMstudent24WHEREclassno=@classno【例1-5】创建一个存储过程p_studentParaNew，该存储过程可以根据给定的班级号和学生姓名返回对应的student表中的记录。分析：根据要求，存储过程p_studentParaNew需要两个输入参数，分别用来向存储过程传入用户指定的班级号和姓名。CREATEPROCEDUREp_studentParaNew@classnochar(8),@snamechar(10)AS25SELECT*FROMstudentWHEREclassno=@classnoANDsnameLIKE@sname262.使用默认值参数创建存储过程时，可以为输入参数提供一个默认值，默认值必须为常量或NULL。【例1-6】创建一个存储过程p_studentDefault，该过程根据给定的性别和班级号返回对应的student表中的记录，其中班级号默认值为09011011。分析：对于使用带默认值参数的存储过程，在执行存储过程时，若用户为参数指定值，就将指定值传给存储过程，若未指定值，则用默认值代替。在该例中，若用户执行时未指定班级号，则默认查询09011011班的学生信息。27CREATEPROCEDUREp_studentDefault@ssexvarchar(2),@classnovarchar(8)='09011011'ASSELECT*FROMstudentWHEREssex=@ssexANDclassno=@classno283.执行带参数的存储过程执行带参数的存储过程时，需要向存储过程传递参数。传递参数有两种方法：一种是顺序法，另一种是提示法。1)顺序法顺序法是在执行存储过程时按照输入参数的顺序直接给出参数的传递值。传递参数时，值的顺序必须与创建存储过程语句中定义的参数的顺序一致。对于使用默认值的参数可以用DEFAULT代替。29【例1-7】执行存储过程 p_stuNew，查询所有女生的信息。EXECp_stunew'女'【例1-8】执行存储过程p_studentParaNew，返回09011011班李锐同学的信息。EXECp_studentParaNew'09011011','李锐'30【例1-9】执行存储过程p_studentDefault，返回09011011班所有男生的信息。EXECp_studentDefault'男','09011011'或者EXECp_studentDefault'男',DEFAULT312)提示法提示法是在执行存储过程时传递参数采用如“@classno='09011011'”的形式。使用提示法传递参数时，各个参数的顺序可以任意排列。【例1-10】执行存储过程p_studentParaNew，返回09011011班李锐同学的信息。EXECp_studentParaNEW@classno='09011011',@sname='李锐'或者EXECp_studentParaNEW@sname='李锐',@classno='09011011'324.带输出参数的存储过程在创建存储过程时可以定义输出参数。执行存储过程时，可以将结果返回给输出参数。使用输出参数类似于使用返回值。但是输出参数具有两个重要的优势：一是可以使用输出参数从存