存储过程课件

12.存储过程存储过程1存储过程综述2创建、执行、修改、删除简单存储过程3创建和执行含参数的存储过程4存储过程的重新编译5利用模板创建存储过程12.存储过程导言：存储过程例：查看某系（计算机系、商务技术系、机电系、人文系）的班级名称。

createviewxb_bjmcasselect班级名称from班级where系部代码=（select系部代码from系部where系部名称=‘计算机系’）12.存储过程1、存储过程概念存储过程是一种数据库对象，是为了实现某个特定任务，将一组预编译的SQL语句以一个存储单元的形式存储在服务器上，供用户调用。存储过程在第一次执行时进行编译，然后将编译好的代码保存在高速缓存中以便以后调用，这样可以提高代码的执行效率。1存储过程综述12.存储过程2、存储过程特点存储过程同其它编程语言中的过程相似，有如下特点：接收输入参数并以输出参数的形式将多个值返回至调用过程或批处理。包含执行数据库操作（包括调用其它过程）的编程语句。向调用过程或批处理返回状态值，以表明成功或失败以及失败原因。12.存储过程3、存储过程的优点：

1、安全机制：只给用户访问存储过程的权限，而不授予用户访问表和视图的权限。

2、改良了执行性能：在第一次执行后，会在SQLserver的缓冲区中创建查询树，以后执行无需编译。

3、减少网络流量：存储过程存在于服务器上，调用时，只需传递执行存储过程的执行命令和返回结果。

4、模块化的程序设计：增强了代码的可重用性，提高了开发效率。12.存储过程4、存储过程类型用户定义的存储过程：用户定义的存储过程是用户根据需要，为完成某一特定功能，在自己的普通数据库中创建的存储过程。系统存储过程：系统存储过程以sp_为前缀，主要用来从系统表中获取信息，为系统管理员管理SQLServer提供帮助，为用户查看数据库对象提供方便。比如用来查看数据库对象信息的系统存储过程sp_help。系统存储过程被系统安装在master数据库中扩展存储过程：扩展存储过程以xp_为前缀，它是关系数据库引擎的开放式数据服务层的一部分，其可以使用户在动态链接库(DLL)文件所包含的函数中实现逻辑功能，从而扩展了T-SQL的功能，并且可以象调用T-SQL过程那样从T-SQL语句调用这些函数。12.存储过程常见的系统存储过程Sp_help显示当前数据库中的相关信息Sp_helpdb显示当前数据库服务器中现有的数据库Sp_helpfile显示与指定数据库相关的物理存储文件Sp_helpserver显示当前可用的数据库服务器Sp_helpuser显示当前数据库服务器中的用户信息Sp_helptext显示文件信息Sp_attach_db加载一个数据库Sp_detach_db卸载一个数据库12.存储过程1.创建存储过程创建存储过程时，需要注意下列事项：

只能在当前数据库中创建存储过程。数据库的所有者可以创建存储过程，也可以授权其他用户创建存储过程。存储过程是数据库对象，其名称必须遵守标识符命名规则。

不能将CREATEPROCEDURE语句与其它SQL语句组合到单个批处理中。创建存储过程时，应指定所有输入参数和向调用过程或批处理返回的输出参数、执行数据库操作的编程语句和返回至调用过程或批处理以表明成功或失败的状态值。2创建、执行、修改、删除简单存储过程12.存储过程使用SQL语句创建不带参数的存储过程语法格式如下：

CREATEPROC[EDURE]procedure_name

[WITH

{RECOMPILE|ENCRYPTION}]

ASsql_statement[...n]procedure_name：新存储过程的名称。过程名必须符合标识符规则，且对于数据库及其所有者必须唯一。RECOMPILE：

SQL不会缓存该过程的计划，该过程将在运行时重新编译。ENCRYPTION：SQLServer加密使用ENCRYPTION可防止将过程作为SQLServer复制的一部分发布。12.存储过程例：在医生数据库中，创建一个查询存储过程dbo.ysghxm，要求该存储过程列出儿科系的医生工号和姓名。

（1）在创建本例存储过程时，可以先在查询编辑器中编写实现存储过程功能的T-SQL语句。代码如下：USE

医生数据库GOSELECT

医生工号,姓名FROM

医生表WHERE

科室代码=

(SELECT

科室代码FROM

科室表WHERE

科室名称='儿科')12.存储过程（2）调试该语句正确后，再创建存储过程。在查询编辑器中输入其完整程的代码如下：USE

医生数据库GOCREATE

PROCdbo.ysghxmASSELECT

医生工号,姓名

FROM

医生表WHERE

科室代码=

(SELECT

科室代码

FROM

科室表WHERE

科室名称='儿科')GO（3）单击“分析”按钮，进行语法检查；语法无误后，单击“执行”按钮，创建该存储过程。12.存储过程2.执行存储过程对存储在服务器上的存储过程，可以使用EXECUTE命令或其名称执行它，其语法格式如下：

[EXEC[UTE]]procedure_name[number]}

如果存储过程是批处理中的第一条语句，EXECUTE命令可以省略，可以使用存储过程的名字执行该存储过程。例：在查询分析器中执行存储过程ysghxm，其代码清单如下：USESTUDENTGOEXECUTEysghxmGO12.存储过程3.查看存储过程存储过程创建好后，其名称保存在系统sysobjects中，其源代码保存在syscomments中，两表通过ID字段进行关联。可以使用如下三种方式查看存储过程相关信息，使用SQLServerManagementStudio直接使用系统表使用系统存储过程12.存储过程（1）使用SQLServerManagementStudio查看存储过程信息启动SQLServerManagementStudio，在“对象资源管理器”窗口中，依次展开数据库、医生数据库、可编程性、存储过程节点。在展开的存储过程节点中右键单击需要查看的存储过程，在弹出的快捷菜单中右键单击“属性”命令，打开“存储过程属性”窗口。12.存储过程（2）使用系统表查看医生数据库中名为ysghxm的存储过程的定义信息。代码如下：USE

医生数据库GOSELECT

TEXT

FROMSYSCOMMENTS

WHEREIDIN

(SELECTIDFROMSYSOBJECTS

WHERE

NAME

='ysghxm'

ANDXTYPE='P')GO12.存储过程使用sp_help查看存储过程的一般信息，包含存储过程的名称、拥有者、类型和创建时间，语法格式：sp_help存储过程名使用sp_helptext查看存储过程的定义信息，语法格式：sp_helptext存储过程名使用sp_depends查看存储过程的相关性，其语法格式：sp_depends存储过程名

（3）使用系统存储过程查看存储过程的相关信息12.存储过程4.修改存储过程修改存储过程的T-SQL语句为ALTERPROCEDURE，其语法格式为：ALTER

PROC[EDURE]procedure_name[;number][{@parameterdata_type}[VARYING][=default][OUTPUT]][,...n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}][FORREPLICATION]ASsql_statement[...n]12.存储过程例如：修改存储过程ysghxm，使该存储过程列出儿科的科室的医生工号和姓名。代码如下：USE

医生数据库GOALTER

PROCdbo.ysghxmAS

SELECT

医生工号,姓名FROM

医生表WHERE

科室代码=

(SELECT

科室代码

FROM

科室表WHERE

科室名称='口腔科')GO12.存储过程5.删除存储过程使用DROPPROCEDURE语句删除存储过程DROPPROCEDURE语句可以一次从当前数据库中将一个或多个存储过程或过程组删除，其语法格式如下：DROPPROCEDURE存储过程名称[,…n]例：删除存储过程ysghxm。代码如下：USE

医生数据库GODROP

PROCEDUREysghxmGO12.存储过程使用SQL语句创建带参数的存储过程语法格式如下：3创建和执行含参数的存储过程CREATE

PROC[EDURE]procedure_name[number][{@parameterdata_type}[VARYING][=default][OUTPUT]][,...n]

[WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]

ASsql_statement[...n]12.存储过程procedure_name：新建存储过程的名称，其名称必须符合标识符命名规则，且对于数据库及其所有者必须唯一。number：该参数是可选的整数，用来对同名的过程分组，以便用一条DROPPROCEDURE语句即可将同组的过程一起删除。例如，名为orders的应用程序使用的过程可以命名为orderproc1、orderproc2等。DROPPROCEDUREorderproc语句将删除整个组。parameter：存储过程中的输入和输出参数。data_type：参数的数据类型。Default：指参数的默认值，必须是常量或NULL。如果定义了默认值，不必指定该参数的值即可执行过程。12.存储过程OUTPUT：表明参数是返回参数。该选项的值可以返回给EXEC[UTE]。使用OUTPUT参数可将信息返回给调用过程。RECOMPILE：表明SQLServer不保存存储过程的计划，该过程将在运行时重新编译。ENCRYPTION：表示SQLServer加密syscomments表中包含CREATEPROCEDURE语句文本的条目。sql_statement：指存储过程中的任意数目和类型的Transact-SQL语句。

在存储过程中使用参数，可以扩展存储过程的功能。使用输入参数，可以将外部信息传入到存储过程；使用输出参数，可以将存储过程内的信息传出到外部。12.存储过程1．使用输入参数例：在医生数据库中，创建一个查询存储过程ysghxm_cs，要求该存储过程带一个输入参数，用于接收科室名称。执行该存储过程时，将根据输入的科室名称列出该科室的医生工号和姓名，代码如下：CREATE

PROCysghxm_cs@ksmcvarchar(30)ASSELECT

医生工号,姓名FROM

医生表WHERE

科室代码=

(SELECT

科室代码FROM

科室表WHERE

科室名称=@ksmc)12.存储过程执行带参数的存储过程，可以采用以下两种方式：按位置传递：在调用存储过程时，直接给出参数值。如果多于一个参数，给出的参数值要与定义的参数的顺序一致。如：执行存储过程st_bjmc，查看“商务技术系”的班级名称，代码如下：EXECst_bjmc‘商务技术系’使用参数名称传递：在调用存储过程时，按“参数名=参数值”的形式给出参数值。采用此方式，参数如果多于一个时，给出的参数顺序可以与定义的参数顺序不一致。如：执行存储过程st_bjmc，查看“经济管理系”的班级名称，代码如下：EXECst_bjmc@xbmc=‘经济管理系’

12.存储过程1.使用输入参数--设置参数默认值

对存储过程ysghxm_cs进行修改，实现默认显示骨科的医生工号和班级的功能。代码如下：ALTER

PROCysghxm_cs@ksmcvarchar(30)=

'骨科'ASSELECT

医生工号,姓名FROM

医生表WHERE

科室代码=

(SELECT

科室代码FROM

科室表WHERE

科室名称=@ksmc)12.存储过程对存储在服务器上的存储过程，可以使用EXECUTE命令或其名称执行它，其语法格式如下：

[EXEC[UTE]]{[@return_status=]procedure_name[number]}[[@parameter=]{value|@variable[OUTPUT]|[DEFAULT]][,...n]@return_status：是一个可选的整型变量，用来保存存储过程的返回状态。@parameter：存储过程的参数。

1.使用输入参数--执行存储过程例如：执行存储过程：EXECysghxm_cs骨科EXECysghxm_cs@ksmc=骨科12.存储过程2.使用输出参数在医生数据库中，创建一个查询存储过程ysrs，要求该存储过程带一个输出参数，用于返回总人数，一般情况下，输出参数的数据类型要与它接收的确定值的类型一致。执行该存储过程时，将把姓张的医生人数传递出来。代码如下：USE

医生数据库GOCREATE

PROCdbo.ysrs@zrstinyint

OUTPUTAS

SELECT@zrs=count(医生工号)

FROM

医生表

WHERE

姓名like

'张%'GO12.存储过程2.使用输出参数执行带有输出参数的存储过程时，需要声明变量接收存储过程的返回值。在使用该变量时，还必须为这个变量加上OUTPUT声明，一般情况下，声明的变量的数据类型要与存储过程的输出参数的数据类型一致。执行st_kcpjf的代码如下：USE

医生数据库GODECLARE@zrstinyintEXECysrs@zrsOUTPUTPRINT

'fsfdsfds'+str(@zrs)12.存储过程3.使用多个参数创建一个判断存储过程ecfg，要求该存储过程带两个输入参数和一个输出参数CREATE

PROCdbo.ecfg@aint,@bint

,@cint,@x2decimal

output,@x1decimal

outputasdeclare@ddecimalset@d=@b*@b-4*@a*@cif@d>=0begin

set@x2=

-(@b*@b-sqrt(@b*@b-4*@a*@c))/(2*@a)

set@x1=

-(@b*@b+sqrt(@b*@b-4*@a*@c))/(2*@a)endelsebegin

set@x2=-@b*@b/(2*@a)

set@x1=-@b*@b/(2*@a)end12.存储过程/*执行存储过程*/DECLARE@x1decimalDECLARE@x2decimalEXECecfga1,4,4,@x1output,@x2outputselect@x1,@x212.存储过程

存储过程第一次执行后，其被编译的代码将驻留在高速缓存中，当用户再次执行该存储过程时，SQLServer将其从缓存中调出执行。有时，在我们使用了一次存储过程后，可能会因为某些原因，必须向表中新增加数据列或者为表新添加索引，从而改变了数据库的逻辑结构。这时，如果调用缓存中的存储过程，需要对它进行重新编译，使存储过程能够得到优化。SQLServer提供三种重新编译存储过程的方法：1、在建立存储过程时设定重新编译创建存储过程时，在其定义中指定WITHRECOMPILE选项，使SQLServer在每次执行存储过程时都要重新编译。4存储过程的重新编译12.存储过程2、在执行存储过程时设定重编译在执行存储过程时指定WITHRECOMPILE选项，可强制对存储过程进行重新编译