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

,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,笔记记录,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,单击此处编辑母版标题样式,笔记记录,笔记记录,8.2,.1,存储过程概述,在SQL Server 2023中，可以将某些需要屡次调用的实现某个特定任务的代码段编写成一个过程，将其保存在数据库中，并由SQL Server效劳器通过过程名来调用它们，这些过程就叫做存储过程。,笔记记录,8.2,.1,存储过程概述,应当提倡多使用存储过程，缘由有以下几点。,1实现了模块化编程。,2调用一次以后，相关信息就保存在内存中，下次调用时可以直接执行。,3存储过程可以承受输入参数并可以返回输出值。,4存储过程具有对数据库立刻访问的功能。,5使用存储过程可以加快程序的运行速度。,6使用存储过程可以削减网络流量。,7使用存储过程可以提高数据库的安全性。,笔记记录,8.2,.1,存储过程概述,在SQL Server 2023中，存储过程分为系统测试、用户存储过程、临时存储过程、扩展存储过程及远程存储过程。,1.系统存储过程是由系统自动创立的，主要存储在master数据库中，一般以sp_为前缀。系统存储过程完成的功能主要是从系统表中猎取信息。,笔记记录,8.2,.1,存储过程概述,2.用户存储过程是由用户依据各自的应用需求进展创立，完成某一个特定功能的存储过程。名称前一般不加sp_。,3.临时存储过程属于用户存储过程。假设用户存储过程名前有#，则为临时存储过程，只能在一个用户会话中使用。如在名字前有#，则表示为全局存储过程，可以再全部的用户会话中使用。,笔记记录,8.2,.1,存储过程概述,4.扩展存储过程是在SQL Server环境之外执行的动态链接库(DLL)，前缀为xp_。它们可以被加载到SQL Server系统中，并且依据存储过程的方式执行。,5.远程存储过程是从远程效劳器上调用的存储过程，或是从连接到另一个效劳器上的客户机上调用的存储过程，是非本地效劳器上的存储过程。,笔记记录,留意事项,不能将创立存储过程的语句与其他SQL语句组合到单个批处理中；,默认权限属于数据库全部者，并可以将权限授予其他用户；,存储过程是数据库对象，名称必需遵守标示符规章；,只能在当前数据库中创立存储过程；,一个存储过程的最大尺寸为128M。,笔记记录,8.2.2 创立存储过程,在SQL Server 2023中，可以使用三种方法创立存储过程。,使用创立存储过程向导创立存储过程。,使用SQL Server 2023企业治理器创立存储过程。,使用Transact-SQL语句中的CREATE PROCEDURE命令创立存储过程。,笔记记录,1使用创立存储过程向导创立存储过程,1在企业治理器中，选中某个SQL Server效劳器，选择要创立存储过程的数据库，选择“工具”菜单中的“向导”菜单项，单击向导中“数据库”选项左边的加号，选中“创立存储过程向导”选项。,2单击“确定”按钮。,笔记记录,2使用SQL Server 2023企业治理器创立存储过程,使用SQL Server 2023企业治理器创立存储过程的步骤如下：,在SQL Server 2023企业治理器中，选择指定的效劳器和数据库，右击要创立存储过程的数据库，在弹出的快捷菜单中依次选择“新建”“存储过程”命令；或者右击数据库中的存储过程图标，从弹出的快捷菜单中选择“新建存储过程”命令。,笔记记录,3使用Transact-SQL语句创立存储过程,可以使用Transact-SQL语句中的CREATE PROCEDURE命令创立存储过程，只能在当前数据库中创立存储过程。,笔记记录,3使用Transact-SQL语句创立存储过程,CREATE PROCEDURE,的语法形式如下：,CREATE PROCEDURE procedure_name;number parameter data_type VARYING=defaultOUTPUT ,.n WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION FOR REPLICATION AS sql_statement .n,笔记记录,其中各参数的含义如下：,procedure_name：用于指定所要创立存储过程的名称。,number：该参数是可选的整数，它用来对同名的存储过程分组，以便用一条DROP PROCEDURE语句即可将同组的过程一起除去。,3使用Transact-SQL语句创立存储过程,笔记记录,parameter：,过程中的参数。在,CREATE PROCEDURE,语句中可以声明一个或多个参数。,data_type：,用于指定参数的数据类型。,VARYING：,用于指定作为输出,OUTPUT,参数支持的结果集。仅适用于游标参数。,3使用Transact-SQL语句创立存储过程,笔记记录,default：用于指定参数的默认值。,OUTPUT：说明该参数是一个返回参数。,RECOMPILE：说明SQL Server 2023不会保存该存储过程的执行准备，该存储过程每执行一次都要重新编译。,3使用Transact-SQL语句创立存储过程,笔记记录,ENCRYPTION：表示对存储过程文本进展加密。,FOR REPLICATION：用于指定该存储过程只能在数据复制时使用。本选项不能和WITH RECOMPILE选项一起使用。,3使用Transact-SQL语句创立存储过程,笔记记录,AS：,用于指定该存储过程要执行的操作。,sql_statement：,是存储过程中包含的任意数目和类型的,Transact-SQL,语句。,3使用Transact-SQL语句创立存储过程,笔记记录,例9-1 创立一个GetInfo，用于猎取全部学生信息。,If exists,(SELECT name FROM sysobjects,WHERE name=GetInfo AND type=p),DROP PROCEDURE GetInfo,GO,CREATE PROCEDURE GetInfo,AS,SELECT*FROM 学生表,GO,3使用Transact-SQL语句创立存储过程,笔记记录,3使用Transact-SQL语句创立存储过程,例9-2 创立一个StuInfo，用于猎取指定学生的信息。,IF EXISTS,GO,CREATE RPOCEDURE StuInfo,StuId varchar(8),AS,SELECT*FROM 学生表,WHERE 学号=StuId,GO,笔记记录,3使用Transact-SQL语句创立存储过程,例9-4 创立GetScore，猎取全部课程的平均成绩、最高成绩、最低成绩。并返回结果。,IF EXISTS GO,CREATE PROCEDURE GetScore,kcID varchar(6),AVGScore int OUTPUT,MAXScore int OUTPUT,MINScore int OUTPUT,AS,SELECT AVGScore=AVG(Grade),MAXScore=,MAX(Grade)，MINScore=MIN(Grade),FROM SC,WHERE Cno=kcID,笔记记录,8.2.3 治理存储过程,1查看存储过程,存储过程被创立之后，它的名字就存储在系统表sysobjects中，它的源代码存放在系统表syscomments中。可以使用企业治理器或系统存储过程来查看用户创立的存储过程。,笔记记录,1,查看存储过程,在企业治理器中查看用户创立的存储过程的方法如下。,1在企业治理器中，翻开指定的效劳器和数据库项，并单击存储过程文件夹，此时在右边的窗格中就会显示出数据库中的全部存储过程。,笔记记录,1,查看存储过程,2右击要查看的存储过程，从弹出的快捷菜单中选择“属性”命令，会弹出“存储过程属性”对话框。,笔记记录,1,查看存储过程,也可以使用系统存储过程来查看用户创立的存储过程。可供使用的系统存储过程及其语法形式如下：,1sp_help：用于显示存储过程的参数及其数据类型,sp_help objname=name,参数name为要查看的存储过程的名称。,笔记记录,1,查看存储过程,2sp_helptext：用于显示存储过程的源代码,sp_helptext objname=name,参数name为要查看的存储过程的名称。,笔记记录,1,查看存储过程,3sp_depends：用于显示和存储过程相关的数据库对象,sp_depends objname=object,参数object为要查看依靠关系的存储过程的名称。,笔记记录,1,查看存储过程,4sp_stored_procedures：用于返回当前数据库中的存储过程列表,sp_stored_proceduressp_name=”name”,sp_owner=”owner”,sp_qualifier=”qualifier”,笔记记录,1,查看存储过程,其中，sp_name=”name”用于指定返回名目信息的过程名；sp_owner=”owner”用于指定过程全部者的名称；sp_qualifier=”qualifier”用于指定过程限定符的名称。,笔记记录,2,修改存储过程定义,在企业治理器中，单击名目树中的存储过程图标，在窗口的右侧右击要修改的存储过程，从弹出的快捷菜单中选择“属性”命令，则会消逝存储过程属性对话框。,笔记记录,2,修改存储过程定义,其语法形式如下：,ALTER PROCEDURE procedure_name;number,parameterdata_type,VARYING=defaultOUTPUT,.n,WITH,RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION,FOR REPLICATION,AS,sql_statement .n,笔记记录,3,重命名存储过程,在企业治理器中，右击要操作的存储过程名称，从弹出的快捷菜单中选择“重命名”命令，或者双击存储过程名称，当存储过程名称变成可输入状态时，就可以直接修改该存储过程的名称了。,笔记记录,3,重命名存储过程,也可以使用系统存储过程,sp_rename,修改存储过程的名称，其语法形式如下：,sp_rename,原存储过程名称，新存储过程名称,笔记记录,8.2,.,4,执行存储过程,在SQL Server 2023中可以使用EXECUTE命令来直接执行存储过程，语法形式如下：,EXECUTE return_status=procedure_name;number|procedure_name_var parameter=value|variableOUTPUT|DEFAULT ,.n WITH RECOMPILE,笔记记录,8.2,.,4,执行存储过程,其中各选项的含义如下：,EXECUTE：执行存储过程的命令关键字，假设此语句是批处理中的第一条语句，可以省略此关键字。,return_status：是一个可选的整型变量，保存存储过程的返回状态。这个变量在使用前，必需在批处理、存储过程或函数中声明过。,笔记记录,8.2,.,4,执行存储过程,procedure_name：,指定执行的存储过程的名称。,;,number：,用来指定该存储过程与其他同名存储过程同组时的标识号。,procedure_name_var：,是局部定义变量名，代表存储过程名称。,笔记记录,8.2,.,4,执行存储过程,parameter：是在创立存储过程时定义的过程参数。调用时向存储过程所传递的参数值由value参数或variable变量供给，或者使用DEFAULT关键字指定使用该参数的默认值，OUTPUT参数说明指定参数为返回参数。,WITH RECOMPILE：指定在执行存储过程时重新编译执行准备。,笔记记录,一个实例,创立一个存储过程sortScore，用于猎取给定学号的某门课程考试成绩是否及格的信