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

1、笔记记录笔记记录8.2.1 存储过程概述存储过程概述v在SQL Server 2000中，可以将某些需要多次调用的实现某个特定任务的代码段编写成一个过程，将其保存在数据库中，并由SQL Server服务器通过过程名来调用它们，这些过程就叫做存储过程。笔记记录笔记记录8.2.1 存储过程概述存储过程概述v 应该提倡多使用存储过程，原因有以下几点。（1）实现了模块化编程。（2）调用一次以后，相关信息就保存在内存中，下次调用时可以直接执行。（3）存储过程可以接受输入参数并可以返回输出值。（4）存储过程具有对数据库立即访问的功能。（5）使用存储过程可以加快程序的运行速度。（6）使用存储过程可以减少网络

2、流量。（7）使用存储过程可以提高数据库的安全性。笔记记录笔记记录8.2.1 存储过程概述存储过程概述v在SQL Server 2000中，存储过程分为系统测试、用户存储过程、临时存储过程、扩展存储过程及远程存储过程。1.系统存储过程是由系统自动创建的，主要存储在master数据库中，一般以sp_为前缀。系统存储过程完成的功能主要是从系统表中获取信息。笔记记录笔记记录8.2.1 存储过程概述存储过程概述2. 用户存储过程是由用户根据各自的应用需求进行创建，完成某一个特定功能的存储过程。名称前一般不加sp_。3. 临时存储过程属于用户存储过程。如果用户存储过程名前有#，则为临时存储过程，只能在一个

3、用户会话中使用。如在名字前有#，则表示为全局存储过程，可以再所有的用户会话中使用。笔记记录笔记记录8.2.1 存储过程概述存储过程概述4. 扩展存储过程是在SQL Server环境之外执行的动态链接库(DLL)，前缀为xp_。它们可以被加载到SQL Server系统中，并且按照存储过程的方式执行。5. 远程存储过程是从远程服务器上调用的存储过程，或是从连接到另一个服务器上的客户机上调用的存储过程，是非本地服务器上的存储过程。笔记记录笔记记录注意事项注意事项v 不能将创建存储过程的语句与其他SQL语句组合到单个批处理中；v 默认权限属于数据库所有者，并可以将权限授予其他用户；v 存储过程是数据库

4、对象，名称必须遵守标示符规则；v 只能在当前数据库中创建存储过程；v 一个存储过程的最大尺寸为128M。笔记记录笔记记录8.2.2 创建存储过程创建存储过程v在SQL Server 2000中，可以使用三种方法创建存储过程。v使用创建存储过程向导创建存储过程。v使用SQL Server 2000企业管理器创建存储过程。v使用Transact-SQL语句中的CREATE PROCEDURE命令创建存储过程。笔记记录笔记记录1使用创建存储过程向导创建存储使用创建存储过程向导创建存储过程过程v（1）在企业管理器中，选中某个SQL Server服务器，选择要创建存储过程的数据库，选择“工具”菜单中的“

5、向导”菜单项，单击向导中“数据库”选项左边的加号，选中“创建存储过程向导”选项。v（2）单击“确定”按钮。笔记记录笔记记录2使用使用SQL Server 2000企业管理企业管理器创建存储过程器创建存储过程v 使用SQL Server 2000企业管理器创建存储过程的步骤如下：v 在SQL Server 2000企业管理器中，选择指定的服务器和数据库，右击要创建存储过程的数据库，在弹出的快捷菜单中依次选择“新建”“存储过程”命令；或者右击数据库中的存储过程图标，从弹出的快捷菜单中选择“新建存储过程”命令。笔记记录笔记记录3使用使用Transact-SQL语句创建存语句创建存储过程储过程v可以使

6、用Transact-SQL语句中的CREATE PROCEDURE命令创建存储过程，只能在当前数据库中创建存储过程。笔记记录笔记记录3使用使用Transact-SQL语句创建存储过程语句创建存储过程v CREATE PROCEDURE的语法形式如下：v CREATE PROCEDURE procedure_name;number parameter data_type VARYING=defaultOUTPUT ,.n WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION FOR REPLICATION AS sql_statement .n 笔记记录笔

7、记记录v其中各参数的含义如下：vprocedure_name：用于指定所要创建存储过程的名称。vnumber：该参数是可选的整数，它用来对同名的存储过程分组，以便用一条DROP PROCEDURE语句即可将同组的过程一起除去。3使用使用Transact-SQL语句创建存储过程语句创建存储过程笔记记录笔记记录vparameter：过程中的参数。在CREATE PROCEDURE语句中可以声明一个或多个参数。vdata_type：用于指定参数的数据类型。vVARYING：用于指定作为输出OUTPUT参数支持的结果集。仅适用于游标参数。3使用使用Transact-SQL语句创建存储过程语句创建存储过

8、程笔记记录笔记记录vdefault：用于指定参数的默认值。vOUTPUT：表明该参数是一个返回参数。vRECOMPILE：表明SQL Server 2000不会保存该存储过程的执行计划，该存储过程每执行一次都要重新编译。3使用使用Transact-SQL语句创建存储过程语句创建存储过程笔记记录笔记记录vENCRYPTION：表示对存储过程文本进行加密。vFOR REPLICATION：用于指定该存储过程只能在数据复制时使用。本选项不能和WITH RECOMPILE选项一起使用。3使用使用Transact-SQL语句创建存储过程语句创建存储过程笔记记录笔记记录vAS：用于指定该存储过程要执行的操

9、作。vsql_statement：是存储过程中包含的任意数目和类型的Transact-SQL语句。3使用使用Transact-SQL语句创建存储过程语句创建存储过程笔记记录笔记记录v例9-1 创建一个GetInfo，用于获取所有学生信息。If exists (SELECT name FROM sysobjects WHERE name=GetInfo AND type=p ) DROP PROCEDURE GetInfoGOCREATE PROCEDURE GetInfoAS SELECT * FROM 学生表GO3使用使用Transact-SQL语句创建存储过程语句创建存储过程笔记记录笔记记

10、录3使用使用Transact-SQL语句创建存储过程语句创建存储过程v例9-2 创建一个StuInfo，用于获取指定学生的信息。IF EXISTSGOCREATE RPOCEDURE StuInfo StuId varchar(8)AS SELECT * FROM 学生表 WHERE 学号=StuIdGO笔记记录笔记记录3使用使用Transact-SQL语句创建存储过程语句创建存储过程v 例9-4 创建GetScore，获取所有课程的平均成绩、最高成绩、最低成绩。并返回结果。IF EXISTS GOCREATE PROCEDURE GetScore kcID varchar(6), AVGSc

11、ore int OUTPUT, MAXScore int OUTPUT,MINScore int OUTPUTAS SELECT AVGScore=AVG(Grade),MAXScore= MAX(Grade)，MINScore=MIN(Grade) FROM SC WHERE Cno=kcID笔记记录笔记记录8.2.3 管理存储过程管理存储过程v1查看存储过程v存储过程被创建之后，它的名字就存储在系统表sysobjects中，它的源代码存放在系统表syscomments中。可以使用企业管理器或系统存储过程来查看用户创建的存储过程。笔记记录笔记记录1查看存储过程查看存储过程v在企业管理器中查看

12、用户创建的存储过程的方法如下。v（1）在企业管理器中，打开指定的服务器和数据库项，并单击存储过程文件夹，此时在右边的窗格中就会显示出数据库中的所有存储过程。笔记记录笔记记录1查看存储过程查看存储过程v（2）右击要查看的存储过程，从弹出的快捷菜单中选择“属性”命令，会弹出“存储过程属性”对话框。笔记记录笔记记录1查看存储过程查看存储过程v也可以使用系统存储过程来查看用户创建的存储过程。可供使用的系统存储过程及其语法形式如下：v（1）sp_help：用于显示存储过程的参数及其数据类型vsp_help objname= namev参数name为要查看的存储过程的名称。笔记记录笔记记录1查看存储过程查

13、看存储过程v（2）sp_helptext：用于显示存储过程的源代码vsp_helptext objname= namev参数name为要查看的存储过程的名称。笔记记录笔记记录1查看存储过程查看存储过程v（3）sp_depends：用于显示和存储过程相关的数据库对象vsp_depends objname=objectv参数object为要查看依赖关系的存储过程的名称。笔记记录笔记记录1查看存储过程查看存储过程v（4）sp_stored_procedures：用于返回当前数据库中的存储过程列表vsp_stored_proceduressp_name=namev,sp_owner=ownerv,sp

14、_qualifier = qualifier笔记记录笔记记录1查看存储过程查看存储过程v其中，sp_name = name 用于指定返回目录信息的过程名；sp_owner = owner 用于指定过程所有者的名称；sp_qualifier = qualifier 用于指定过程限定符的名称。笔记记录笔记记录2修改存储过程定义修改存储过程定义v在企业管理器中，单击目录树中的存储过程图标，在窗口的右侧右击要修改的存储过程，从弹出的快捷菜单中选择“属性”命令，则会出现存储过程属性对话框。笔记记录笔记记录2修改存储过程定义修改存储过程定义v 其语法形式如下：v ALTER PROCEDURE proce

15、dure_name;numberv parameterdata_typev VARYING=defaultOUTPUT,.nv WITHv RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONv FOR REPLICATIONv ASv sql_statement .n 笔记记录笔记记录3重命名存储过程重命名存储过程v在企业管理器中，右击要操作的存储过程名称，从弹出的快捷菜单中选择“重命名”命令，或者双击存储过程名称，当存储过程名称变成可输入状态时，就可以直接修改该存储过程的名称了。笔记记录笔记记录3重命名存储过程重命名存储过程v也可以使用系统存储过程sp_ren

16、ame修改存储过程的名称，其语法形式如下：vsp_rename 原存储过程名称，新存储过程名称笔记记录笔记记录8.2.4 执行存储过程执行存储过程v 在SQL Server 2000中可以使用EXECUTE命令来直接执行存储过程，语法形式如下：v EXECUTE return_status= procedure_name;number|procedure_name_var parameter=value|variableOUTPUT|DEFAULT ,.n WITH RECOMPILE 笔记记录笔记记录8.2.4 执行存储过程执行存储过程v其中各选项的含义如下：vEXECUTE：执行存储过程的

17、命令关键字，如果此语句是批处理中的第一条语句，可以省略此关键字。vreturn_status：是一个可选的整型变量，保存存储过程的返回状态。这个变量在使用前，必须在批处理、存储过程或函数中声明过。笔记记录笔记记录8.2.4 执行存储过程执行存储过程vprocedure_name：指定执行的存储过程的名称。v;number：用来指定该存储过程与其他同名存储过程同组时的标识号。vprocedure_name_var：是局部定义变量名，代表存储过程名称。笔记记录笔记记录8.2.4 执行存储过程执行存储过程vparameter：是在创建存储过程时定义的过程参数。调用时向存储过程所传递的参数值由valu

18、e参数或variable变量提供，或者使用DEFAULT关键字指定使用该参数的默认值，OUTPUT参数说明指定参数为返回参数。vWITH RECOMPILE：指定在执行存储过程时重新编译执行计划。笔记记录笔记记录一个实例一个实例v创建一个存储过程sortScore，用于获取给定学号的某门课程考试成绩是否及格的信息。v创建存储过程：Create procedure sortScore kecheng char(2),xuehao char(9)Asdeclare fenshu intselect fenshu = gradefrom sc笔记记录笔记记录where cno = kecheng and sno = xuehaoif fenshu 60print 不及格else print 及格v执行过程：Exec sortScore 4, 200515125笔记记录笔记记录8.2.4 执行存储过程执行存储过程