数据库应用基础第七章存储过程课件

1第七章存储过程1第七章存储过程27.1存储过程概述7.2创建存储过程7.3修改和删除存储过程

本章主要内容2本章主要内容37.1存储过程概述

存储过程(storedprocedure)是存放在服务器上的预先编译好的SQL语句,用于完成某项任务，它可以接受参数、返回状态值和参数值，并且可以嵌套调用

37.1存储过程概述存储过程(storedproc4存储过程类型系统存储过程本地存储过程临时存储过程远程存储过程扩展存储过程本章主要内容4存储过程类型系统存储过程本章主要内容5系统存储过程：存储在master数据库中，由前缀sp标识作用：从系统表中获取信息，允许系统管理员在没有直接更新底层表的许可权下执行更新数据库中系统表的数据库管理工作。绝大部分的系统存储过程可以在任何数据库中执行5系统存储过程：存储在master数据库中，由前缀sp标识6本地存储过程：这是用户在独立的用户数据库中为了完成某一特定功能而编写的存储过程临时存储过程：它与临时表类似，通常又分为本地和全局临时存储过程两种，当临时存储过程为本地时，其名字以符号#开始，为全局时，以符号##开始6本地存储过程：这是用户在独立的用户数据库中为了完成某一特定7远程存储过程：远程存储过程在分布式查询中使用扩展存储过程：使SQL

Server可动态装载并执行DLL。这样用户可使用象C这样的编程语言创建自己的外部例程扩展存储过程由前缀xp标识7远程存储过程：远程存储过程在分布式查询中使用扩展存储过程：8存储过程的优点

提供了安全机制改进了执行性能减少了网络流量允许模块化程序设计8存储过程的优点提供了安全机制9

存储过程提供的安全机制可以让用户通过存储过程操作数据库中的数据，而不让用户直接操作于存储过程相关的表，从而保证数据库中数据的安全性9存储过程提供的安全机制可以让用户通过存储过程操作数10存储过程在第二次执行时，无需预编译，从而改进系统的执行性能存储过程是存放在服务器上的预先编译好的单条或多条SQL语句并在服务器上运行，用户无需在网络上发送上百个SQL语句，或是将众多数据从服务器下载至客户端后再进行处理，从而大大减少了网络负载

10存储过程在第二次执行时，无需预编译，从而改进系统的执行性11增强代码的可重用性，提高开发效率。存储过程可以视为为完成某特定功能而编写的功能模块，将来可以在其他的存储过程中引用该存储过程，从而实现代码的重用性，加快应用的开发速度，提高开发的质量和效率11增强代码的可重用性，提高开发效率。存储过程可以视为为完成12存储过程中包含的输入/出参数7.2创建存储过程7.2.1创建简单的存储过程

CREATEPROC[EDURE][owner.]procedure_name[;number][{@parameterdata_type}[VARYING][=default][OUTPUT]][，…n][WITH{RECOMPILE|ENCRYPTION| RECOMPILE,ENCRYPTION}][FORREPLICATION]ASsql_statements重新编译加密在存储过程中需执行的操作12存储过程中包含的输入/出参数7.2创建存储过程7.13例：在My_DB1上新建my_procedure1存储过程，以返回所有diagrm_id>=100和principal>=200的信息。USEMy_DB1goCREATEPROCdbo.my_procedure1ASSELECT*FROMdbo.sysdiagrmsWHEREdiagrm_id>=100

ANDprincipal>=200Go13例：在My_DB1上新建my_procedure1存储过14创建存储过程的步骤及注意事项不能将CREATE

PROCEDURE语句与其它SQL语句组合到单个批处理中创建存储过程是有权限的，其默认权限为dbo，其他用户若要获得创建存储过程的权限，要由dbo授权14创建存储过程的步骤及注意事项不能将CREATEPRO15存储过程是数据库对象，在命名用户自定义的存储过程时应避免使用sp前缀，以免和系统存储过程混淆尽量不要使用临时存储过程，以避免tempdb上造成的对系统表资源的争夺，从而影响系统的执行性能15存储过程是数据库对象，在命名用户自定义的存储过程时应避免16

存储过程最大尺寸被限制为128MB，存储过程最多允许嵌套至32级16存储过程最大尺寸被限制为128MB，存储过程17例：编写指令执行my_procedure1存储过程

USENorthwind EXECmy_procedure1 GO17例：编写指令执行my_procedure1存储过程18查看存储过程信息

sysobjectssyscommentssysdependssp_stored_procedures可以使用如下命令：18查看存储过程信息sysobjects可以使用如下命令：19例：编写SQL指令查看创建存储过程my_procedure1的相关代码信息

USENorthwindEXECsp_helptextmy_procedure1GO19例：编写SQL指令查看创建存储过程my_procedur207.2.2创建和执行含参数存储过程通过使用参数，可以多次使用同一存储过程并按指定要求查找数据库207.2.2创建和执行含参数存储过程通过使用参数，可以211.

创建带输入参数的存储过程输入参数是指由调用程序向存储过程传递的参数，为了定义接受输入参数的存储过程，需要在CREATEPROCEDURE语句中声明一个或多个变量作为参数。如：@parameter_namedatatype[=default]必须是常量或NULL211.创建带输入参数的存储过程输入参数是指由调用程序向22例：在Northwind库上创建存储过程my_procedure2的，其中定义了两个时间类型的输入参数和一个字符型输入参数，返回所有日期在两输入时间之间、目的地与输入的字符型参数相同的订单，其中字符型输入参数指定的默认值为’USA’。22例：在Northwind库上创建存储过程my_proce23USENorthwindgoCREATE

PROCdbo.my_procedure2@StartDateDateTime,@EndDateDateTime,@Countryvarchar(20)='USA'AS23USENorthwind24IF(@StartDateISNULL or @EndDateISNULL or @CountryISNULL)BEGIN

RAISERROR('NULLvalueareinvalid',5,5)RETURNEND

返回用户定义的错误信息并设系统标志24IF(@StartDateISNULL or返回25SELECT*FROMdbo.ordersWHEREOrderDateBETWEEN@StartDateAND@EndDateANDshipCountry=@Countrygo25SELECT*FROMdbo.orders262.执行带输入参数的存储过程使用参数名传送参数值按位置传送参数值两种方法由如下语句给出传递值@parameter_name=value不参照被传递的参数而直接给出参数的传递值262.执行带输入参数的存储过程使用参数名传送参数值两种27例：使用参数名传送参数值的方法，通过my_procedure2存储过程，返回所有日期在1997.7.1与1998.2.1日，目的地为’USA’的订单记录。

USENorthwind GO EXECmy_procedure2 @EndDate=’2/1/1998’, @StartDate=’7/1/1997’27例：使用参数名传送参数值的方法，通过my_procedu28例：使用按位置传送参数值的方法，通过my_procedure2存储过程，返回所有日期在1997.7.1与1998.2.1日，目的地为’USA’的订单记录。USENorthwindGOEXECmy_procedure2’7/1/1997’，’2/1/1998’,’USA’28例：使用按位置传送参数值的方法，通过my_procedu293.

创建带输出参数的存储过程在创建存储过程的语句中定义输出参数可以实现从存储过程中返回值为了使用输出参数。在CREATE

PROCEDURE中指定OUTPUT@parameter_name

datatype[=Default]

OUTPUT293.创建带输出参数的存储过程在创建存储过程的语句中定30例：在Northwind上创建my_procedure3存储过程，该存储过程在my_procedure2的基础上，使用输出参数返回符合要求的订单的条数。

USENorthwindGOCREATEPROCdbo.my_procedure3@StartDateDateTime,@EndDateDateTime,@Countryvarchar(20)='USA',@recordcountintOUTPUTAS30例：在Northwind上创建my_procedure331IF (@StartDateISNULLor @EndDateISNULLor @CountryISNULL)BEGINRAISERROR('NULvalueareinvalid',5,5)RETURNEND31IF (@StartDateISNULLor32SELECT*FROMdbo.ordersWHEREOrderDateBETWEEN@StartDateAND@EndDateANDShipCountry=@CountrySELECT@recordcount=@@ROWCOUNT用来返回受上一语句影响的行数的系统变量，在这里我们用它来返回符合条件订单的条数32SELECT*FROMdbo.orders用来334.

执行带输出参数的存储过程为了接收存储过程的返回值，必须声明作为输出的传递参数在EXECUTE语句中指定OUTPUT334.执行带输出参数的存储过程为了接收存储过程的返回值34例：执行my_procedure3存储过程，返回在1997.7.1与1998.2.1之间、目的地为’Germany’的记录的条数USENorthwindGODECLARE@recordnumberintEXECmy_procedure3'7/1/1997','2/1/1998','Germany',@recordnumberOUTPUTPRINT'Theordercountis:'+str(@recordnumber)34例：执行my_procedure3存储过程，返回在199355.存储过程的重编译处理

存储过程的处理SQLServer在创建存储过程时，需进行语法检查，若存在语法错误，将返回错误，并不创建该存储过程；若语法正确，则存储过程的文本将存储在syscomments系统表中355.存储过程的重编译处理存储过程的处理SQLSer36存储过程的重编译处理三种方法建立存储过程时设定重编译选项CREATEPROCEDURE[WITHRECOMPILE]在执行存储过程时设定重编译选项EXECUTE

PROCEDURE_NAME[PARAMETER][WITHRECOMPILE]通过系统存储过程设定重编译选项EXECsp_recompileOBJECT

36存储过程的重编译处理三种方法建立存储过程时设定重编译选377.3修改和删除存储过程7.3.1修改存储过程

ALTERPROCprocedure_name[;number][{@parameterdata_type}[VARYING][=default][OUTPUT]][,…n][WITH{RECOMPILE|ENCRYPTION| RECOMPILE,ENCRYTION}][FORREPLICATION]ASSql_statement[,..n]

377.3修改和删除存储过程7.3.1修改存储过程AL38例：修改存储过程my_procedure1,返回1998.1.1(含)以后的、目的地为美国的,CustomerID,OrderDate,ShipCountry,OrderID字段,并指定重编译和加密选项USENorthwindgoALTER

PROCdbo.my_procedure1WITH

RECOMPILE,ENCRYPTIONASSELECT

OrderID，CustomerID，OrderDate，ShipCountryFROMdbo.ordersWHEREOrderDate>='1/1/1998'ANDShipCountry='USA'38例：修改存储过程my_procedure1,返回1998397.3.2删除存储过程

DROPPROC[EDURE]procedure_name397.3.2删除存储过程DROPPROC[EDURE40第七章存储过程1第七章存储过程417.1存储过程概述7.2创建存储过程7.3修改和删除存储过程

本章主要内容2本章主要内容427.1存储过程概述

存储过程(storedprocedure)是存放在服务器上的预先编译好的SQL语句,用于完成某项任务，它可以接受参数、返回状态值和参数值，并且可以嵌套调用

37.1存储过程概述存储过程(storedproc43存储过程类型系统存储过程本地存储过程临时存储过程远程存储过程扩展存储过程本章主要内容4存储过程类型系统存储过程本章主要内容44系统存储过程：存储在master数据库中，由前缀sp标识作用：从系统表中获取信息，允许系统管理员在没有直接更新底层表的许可权下执行更新数据库中系统表的数据库管理工作。绝大部分的系统存储过程可以在任何数据库中执行5系统存储过程：存储在master数据库中，由前缀sp标识45本地存储过程：这是用户在独立的用户数据库中为了完成某一特定功能而编写的存储过程临时存储过程：它与临时表类似，通常又分为本地和全局临时存储过程两种，当临时存储过程为本地时，其名字以符号#开始，为全局时，以符号##开始6本地存储过程：这是用户在独立的用户数据库中为了完成某一特定46远程存储过程：远程存储过程在分布式查询中使用扩展存储过程：使SQL

Server可动态装载并执行DLL。这样用户可使用象C这样的编程语言创建自己的外部例程扩展存储过程由前缀xp标识7远程存储过程：远程存储过程在分布式查询中使用扩展存储过程：47存储过程的优点

提供了安全机制改进了执行性能减少了网络流量允许模块化程序设计8存储过程的优点提供了安全机制48

存储过程提供的安全机制可以让用户通过存储过程操作数据库中的数据，而不让用户直接操作于存储过程相关的表，从而保证数据库中数据的安全性9存储过程提供的安全机制可以让用户通过存储过程操作数49存储过程在第二次执行时，无需预编译，从而改进系统的执行性能存储过程是存放在服务器上的预先编译好的单条或多条SQL语句并在服务器上运行，用户无需在网络上发送上百个SQL语句，或是将众多数据从服务器下载至客户端后再进行处理，从而大大减少了网络负载

10存储过程在第二次执行时，无需预编译，从而改进系统的执行性50增强代码的可重用性，提高开发效率。存储过程可以视为为完成某特定功能而编写的功能模块，将来可以在其他的存储过程中引用该存储过程，从而实现代码的重用性，加快应用的开发速度，提高开发的质量和效率11增强代码的可重用性，提高开发效率。存储过程可以视为为完成51存储过程中包含的输入/出参数7.2创建存储过程7.2.1创建简单的存储过程

CREATEPROC[EDURE][owner.]procedure_name[;number][{@parameterdata_type}[VARYING][=default][OUTPUT]][，…n][WITH{RECOMPILE|ENCRYPTION| RECOMPILE,ENCRYPTION}][FORREPLICATION]ASsql_statements重新编译加密在存储过程中需执行的操作12存储过程中包含的输入/出参数7.2创建存储过程7.52例：在My_DB1上新建my_procedure1存储过程，以返回所有diagrm_id>=100和principal>=200的信息。USEMy_DB1goCREATEPROCdbo.my_procedure1ASSELECT*FROMdbo.sysdiagrmsWHEREdiagrm_id>=100

ANDprincipal>=200Go13例：在My_DB1上新建my_procedure1存储过53创建存储过程的步骤及注意事项不能将CREATE

PROCEDURE语句与其它SQL语句组合到单个批处理中创建存储过程是有权限的，其默认权限为dbo，其他用户若要获得创建存储过程的权限，要由dbo授权14创建存储过程的步骤及注意事项不能将CREATEPRO54存储过程是数据库对象，在命名用户自定义的存储过程时应避免使用sp前缀，以免和系统存储过程混淆尽量不要使用临时存储过程，以避免tempdb上造成的对系统表资源的争夺，从而影响系统的执行性能15存储过程是数据库对象，在命名用户自定义的存储过程时应避免55

存储过程最大尺寸被限制为128MB，存储过程最多允许嵌套至32级16存储过程最大尺寸被限制为128MB，存储过程56例：编写指令执行my_procedure1存储过程

USENorthwind EXECmy_procedure1 GO17例：编写指令执行my_procedure1存储过程57查看存储过程信息

sysobjectssyscommentssysdependssp_stored_procedures可以使用如下命令：18查看存储过程信息sysobjects可以使用如下命令：58例：编写SQL指令查看创建存储过程my_procedure1的相关代码信息

USENorthwindEXECsp_helptextmy_procedure1GO19例：编写SQL指令查看创建存储过程my_procedur597.2.2创建和执行含参数存储过程通过使用参数，可以多次使用同一存储过程并按指定要求查找数据库207.2.2创建和执行含参数存储过程通过使用参数，可以601.

创建带输入参数的存储过程输入参数是指由调用程序向存储过程传递的参数，为了定义接受输入参数的存储过程，需要在CREATEPROCEDURE语句中声明一个或多个变量作为参数。如：@parameter_namedatatype[=default]必须是常量或NULL211.创建带输入参数的存储过程输入参数是指由调用程序向61例：在Northwind库上创建存储过程my_procedure2的，其中定义了两个时间类型的输入参数和一个字符型输入参数，返回所有日期在两输入时间之间、目的地与输入的字符型参数相同的订单，其中字符型输入参数指定的默认值为’USA’。22例：在Northwind库上创建存储过程my_proce62USENorthwindgoCREATE

PROCdbo.my_procedure2@StartDateDateTime,@EndDateDateTime,@Countryvarchar(20)='USA'AS23USENorthwind63IF(@StartDateISNULL or @EndDateISNULL or @CountryISNULL)BEGIN

RAISERROR('NULLvalueareinvalid',5,5)RETURNEND

返回用户定义的错误信息并设系统标志24IF(@StartDateISNULL or返回64SELECT*FROMdbo.ordersWHEREOrderDateBETWEEN@StartDateAND@EndDateANDshipCountry=@Countrygo25SELECT*FROMdbo.orders652.执行带输入参数的存储过程使用参数名传送参数值按位置传送参数值两种方法由如下语句给出传递值@parameter_name=value不参照被传递的参数而直接给出参数的传递值262.执行带输入参数的存储过程使用参数名传送参数值两种66例：使用参数名传送参数值的方法，通过my_procedure2存储过程，返回所有日期在1997.7.1与1998.2.1日，目的地为’USA’的订单记录。

USENorthwind GO EXECmy_procedure2 @EndDate=’2/1/1998’, @StartDate=’7/1/1997’27例：使用参数名传送参数值的方法，通过my_procedu67例：使用按位置传送参数值的方法，通过my_procedure2存储过程，返回所有日期在1997.7.1与1998.2.1日，目的地为’USA’的订单记录。USENorthwindGOEXECmy_procedure2’7/1/1997’，’2/1/1998’,’USA’28例：使用按位置传送参数值的方法，通过my_procedu683.

创建带输出参数的存储过程在创建存储过程的语句中定义输出参数可以实现从存储过程中返回值为了使用输出参数。在CREATE

PROCEDURE中指定OUTPUT@parameter_name

datatype[=Default]

OUTPUT293.创建带输出参数的存储过程在创建存储过程的语句中定69例：在Northwind上创建my_procedure3存储过程，该存储过程在my_procedure2的基础上，使用输出参数返回符合要求的订单的条数。

USENorthwindGOCREATEPROCdbo.my_procedure3@StartDateDateTime,@EndDateDateTime,@Countryvarchar(20)='USA',@recordcountintOUTPUTAS30例：在Northwind上创建my_procedure370IF (@StartDateISNULLor @EndDateISNULLor @CountryISNULL)BEGINRAISERROR('NULvalueareinvalid',5,5)RETURNEND31IF (@StartDateISNULLor71SELECT*FROMdbo.ordersWHEREOrderDateBETWEEN@StartDateAND@EndDateANDShipCountry=@CountrySELECT@recordcount=@@ROWCOUNT用来返回受上一语句影响的行数的系统变量，在这里我们用它来返回符合条件订单的条数32SELECT*FROMdbo.orders用来724.

执行带输出参数的存储过程为了接收存储过程的返回值，必须声明作为输出的传递参数在EXECUTE语句中指定OUTPUT334.执行带输出参数的存储过程为了接收存储过程的返回值73例：执行my_procedure3存储过程，返回在1997.7.1与1998.2.1之间、目的地为’Germany’的记录的条数USENorthwindGODECLARE@recordnumberintEXEC