SQL-Server(存储过程学习笔记)分析

笔记记录8.2.1存储过程概述在SQLServer2023中，可以将某些需要屡次调用的实现某个特定任务的代码段编写成一个过程，将其保存在数据库中，并由SQLServer效劳器通过过程名来调用它们，这些过程就叫做存储过程。笔记记录8.2.1存储过程概述应当提倡多使用存储过程，缘由有以下几点。〔1〕实现了模块化编程。〔2〕调用一次以后，相关信息就保存在内存中，下次调用时可以直接执行。〔3〕存储过程可以承受输入参数并可以返回输出值。〔4〕存储过程具有对数据库立刻访问的功能。〔5〕使用存储过程可以加快程序的运行速度。〔6〕使用存储过程可以削减网络流量。〔7〕使用存储过程可以提高数据库的安全性。笔记记录8.2.1存储过程概述在SQLServer2023中，存储过程分为系统测试、用户存储过程、临时存储过程、扩展存储过程及远程存储过程。1.系统存储过程是由系统自动创立的，主要存储在master数据库中，一般以sp_为前缀。系统存储过程完成的功能主要是从系统表中猎取信息。笔记记录8.2.1存储过程概述2.用户存储过程是由用户依据各自的应用需求进展创立，完成某一个特定功能的存储过程。名称前一般不加‘sp_’。3.临时存储过程属于用户存储过程。假设用户存储过程名前有‘#’，则为临时存储过程，只能在一个用户会话中使用。如在名字前有‘##’，则表示为全局存储过程，可以再全部的用户会话中使用。笔记记录8.2.1存储过程概述4.扩展存储过程是在SQLServer环境之外执行的动态链接库(DLL)，前缀为xp_。它们可以被加载到SQLServer系统中，并且依据存储过程的方式执行。5.远程存储过程是从远程效劳器上调用的存储过程，或是从连接到另一个效劳器上的客户机上调用的存储过程，是非本地效劳器上的存储过程。笔记记录留意事项不能将创立存储过程的语句与其他SQL语句组合到单个批处理中；默认权限属于数据库全部者，并可以将权限授予其他用户；存储过程是数据库对象，名称必需遵守标示符规章；只能在当前数据库中创立存储过程；一个存储过程的最大尺寸为128M。笔记记录8.2.2创立存储过程在SQLServer2023中，可以使用三种方法创立存储过程。使用创立存储过程向导创立存储过程。使用SQLServer2023企业治理器创立存储过程。使用Transact-SQL语句中的CREATEPROCEDURE命令创立存储过程。笔记记录1．使用创立存储过程向导创立存储过程〔1〕在企业治理器中，选中某个SQLServer效劳器，选择要创立存储过程的数据库，选择“工具”菜单中的“向导”菜单项，单击向导中“数据库”选项左边的加号，选中“创立存储过程向导”选项。〔2〕单击“确定”按钮。笔记记录2．使用SQLServer2023企业治理器创立存储过程使用SQLServer2023企业治理器创立存储过程的步骤如下：在SQLServer2023企业治理器中，选择指定的效劳器和数据库，右击要创立存储过程的数据库，在弹出的快捷菜单中依次选择“新建”→“存储过程…”命令；或者右击数据库中的存储过程图标，从弹出的快捷菜单中选择“新建存储过程…”命令。笔记记录3．使用Transact-SQL语句创立存储过程可以使用Transact-SQL语句中的CREATEPROCEDURE命令创立存储过程，只能在当前数据库中创立存储过程。笔记记录3．使用Transact-SQL语句创立存储过程CREATEPROCEDURE的语法形式如下：CREATEPROC[EDURE]procedure_name[;number]

[{@parameterdata_type}

[VARYING][=default][OUTPUT]

][,...n]

WITH

{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]

[FORREPLICATION]

ASsql_statement[...n]笔记记录其中各参数的含义如下：procedure_name：用于指定所要创立存储过程的名称。number：该参数是可选的整数，它用来对同名的存储过程分组，以便用一条DROPPROCEDURE语句即可将同组的过程一起除去。3．使用Transact-SQL语句创立存储过程笔记记录@parameter：过程中的参数。在CREATEPROCEDURE语句中可以声明一个或多个参数。data_type：用于指定参数的数据类型。VARYING：用于指定作为输出OUTPUT参数支持的结果集。仅适用于游标参数。3．使用Transact-SQL语句创立存储过程笔记记录default：用于指定参数的默认值。OUTPUT：说明该参数是一个返回参数。RECOMPILE：说明SQLServer2023不会保存该存储过程的执行准备，该存储过程每执行一次都要重新编译。3．使用Transact-SQL语句创立存储过程笔记记录ENCRYPTION：表示对存储过程文本进展加密。FORREPLICATION：用于指定该存储过程只能在数据复制时使用。本选项不能和WITHRECOMPILE选项一起使用。3．使用Transact-SQL语句创立存储过程笔记记录AS：用于指定该存储过程要执行的操作。sql_statement：是存储过程中包含的任意数目和类型的Transact-SQL语句。3．使用Transact-SQL语句创立存储过程笔记记录例9-1创立一个GetInfo，用于猎取全部学生信息。Ifexists(SELECTnameFROMsysobjectsWHEREname=‘GetInfo’ANDtype=‘p’)DROPPROCEDUREGetInfoGOCREATEPROCEDUREGetInfoASSELECT*FROM学生表GO3．使用Transact-SQL语句创立存储过程笔记记录3．使用Transact-SQL语句创立存储过程例9-2创立一个StuInfo，用于猎取指定学生的信息。IFEXISTS……GOCREATERPOCEDUREStuInfo@StuIdvarchar(8)ASSELECT*FROM学生表WHERE学号=@StuIdGO笔记记录3．使用Transact-SQL语句创立存储过程例9-4创立GetScore，猎取全部课程的平均成绩、最高成绩、最低成绩。并返回结果。IFEXISTS……GOCREATEPROCEDUREGetScore@kcIDvarchar(6),@AVGScoreintOUTPUT,@MAXScoreintOUTPUT,@MINScoreintOUTPUTASSELECT@AVGScore=AVG(Grade),@MAXScore=MAX(Grade)，@MINScore=MIN(Grade)FROMSCWHERECno=@kcID笔记记录8.2.3治理存储过程1．查看存储过程存储过程被创立之后，它的名字就存储在系统表sysobjects中，它的源代码存放在系统表syscomments中。可以使用企业治理器或系统存储过程来查看用户创立的存储过程。笔记记录1．查看存储过程在企业治理器中查看用户创立的存储过程的方法如下。〔1〕在企业治理器中，翻开指定的效劳器和数据库项，并单击存储过程文件夹，此时在右边的窗格中就会显示出数据库中的全部存储过程。笔记记录1．查看存储过程〔2〕右击要查看的存储过程，从弹出的快捷菜单中选择“属性”命令，会弹出“存储过程属性”对话框。笔记记录1．查看存储过程也可以使用系统存储过程来查看用户创立的存储过程。可供使用的系统存储过程及其语法形式如下：〔1〕sp_help：用于显示存储过程的参数及其数据类型sp_help[[@objname=]name]参数name为要查看的存储过程的名称。笔记记录1．查看存储过程〔2〕sp_helptext：用于显示存储过程的源代码sp_helptext[[@objname=]name]参数name为要查看的存储过程的名称。笔记记录1．查看存储过程〔3〕sp_depends：用于显示和存储过程相关的数据库对象sp_depends[@objname=]’object’参数object为要查看依靠关系的存储过程的名称。笔记记录1．查看存储过程〔4〕sp_stored_procedures：用于返回当前数据库中的存储过程列表sp_stored_procedures[[@sp_name=]”name”][,[@sp_owner=]”owner”][,[@sp_qualifier=]”qualifier”]笔记记录1．查看存储过程其中，[@sp_name=]”name”用于指定返回名目信息的过程名；[@sp_owner=]”owner”用于指定过程全部者的名称；[@sp_qualifier=]”qualifier”用于指定过程限定符的名称。笔记记录2．修改存储过程定义在企业治理器中，单击名目树中的存储过程图标，在窗口的右侧右击要修改的存储过程，从弹出的快捷菜单中选择“属性”命令，则会消逝存储过程属性对话框。笔记记录2．修改存储过程定义其语法形式如下：ALTERPROC[EDURE]procedure_name[;number][{@parameterdata_type}[VARYING][=default][OUTPUT]][,...n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}][FORREPLICATION]ASsql_statement[...n]笔记记录3．重命名存储过程在企业治理器中，右击要操作的存储过程名称，从弹出的快捷菜单中选择“重命名”命令，或者双击存储过程名称，当存储过程名称变成可输入状态时，就可以直接修改该存储过程的名称了。笔记记录3．重命名存储过程也可以使用系统存储过程sp_rename修改存储过程的名称，其语法形式如下：sp_rename原存储过程名称，新存储过程名称笔记记录8.2.4

执行存储过程在SQLServer2023中可以使用EXECUTE命令来直接执行存储过程，语法形式如下：[[EXEC[UTE]]

{

[@return_status=]

{procedure_name[;number]|@procedure_name_var}

[[@parameter=]{value|@variable[OUTPUT]|[DEFAULT]}

[,...n]

[WITHRECOMPILE]笔记记录8.2.4

执行存储过程其中各选项的含义如下：EXECUTE：执行存储过程的命令关键字，假设此语句是批处理中的第一条语句，可以省略此关键字。@return_status：是一个可选的整型变量，保存存储过程的返回状态。这个变量在使用前，必需在批处理、存储过程或函数中声明过。笔记记录8.2.4执行存储过程procedure_name：指定执行的存储过程的名称。[;number]：用来指定该存储过程与其他同名存储过程同组时的标识号。@procedure_name_var：是局部定义变量名，代表存储过程名称。笔记记录8.2.4执行存储过程@parameter：是在创立存储过程时定义的过程参数。调用时向存储过程所传递的参数值由value参数或@variable变量供给，或者使用DEFAULT关键字指定使用该参数的默认值，OUTPUT参数说明指定参数为返回参数。WITHRECOMPILE：指定在执行存储过程时重新编译执行准备。笔记记录一个实例创立一个存储过程sortScore，用于猎取给定学号的某门课程考试成绩是否及格的信息。创立存储过程：CreateproceduresortScore@kechengchar(2),@xuehaochar(9)As declare@fenshuint select@fenshu=grade fromsc笔记记录 wherecno=@kechengandsno=@xuehao if@fenshu<60 print‘不及格’ elseprint‘及格’执行过程：ExecsortScore4,202315125笔记记录8.2.4执行存储过程例9-8