《SQLServer数据库应用与维护》-第16讲 存储过程1

第6章内容回顾1.常量与变量（重点）2.运算符与表达式（重点）3.程序流程控制语句

（难点）4.函数（难点）5.事务、锁（难点）上章内容提问提问：2、如何定义函数？3、如何调用函数？4、事务有哪些特性？5、常见的锁有？1、函数根据创建者的不同分为哪两类？

第七章存储过程与触发器第七章

存储过程与触发器目的与要求：掌握根据需要创建、修改、删除存储过程和触发器。能够在实际应用开发时能够灵活运用存储过程与触发器，以提高开发效率。重点与难点：存储过程的创建管理与应用（重难点）触发器的创建管理与应用（重难点）第七章

存储过程与触发器7.1存储过程7.2触发器主要内容：第16讲

课程导入任务展示：

由于学生的信息变化或学生转学等原因，学生的数据需要进行相应的处理。教务系统决定定期处理一批无效学生记录。【例如】：学号为“2011101”的孙雷同学办理退学手续，系统要求删除该学生记录的同时，删除其选课记录。学生表学号姓名…2011101孙雷…2011102王明…2011103张丽丽…选课表学号课程号成绩2011101C1952011101C2802011101C385解决方法：你能想到什么方案呢？第16讲

存储过程17.1存储过程7.1.1存储过程的定义7.1.2存储过程的优点7.1.3存储过程的分类7.1.4常用的系统存储过程7.1.5创建与调用存储过程7.1.6管理存储过程★★7.1存储过程7.1

新知识点导入提问1：“编写100行T-SQL语句访问数据库”？

可读性？修改？7.1.1存储过程的定义7.1.1

存储过程的定义存储过程（StoredProcedure）：是在大型数据库系统中，一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数来执行它，以实现某个任务。存储过程有系统存储过程和用户存储过程“存储过程到底是什么？！”7.1.1

存储过程的定义存储过程是SQL语句和可选控制流语句的预编译集合，存储在数据库中，可由应用程序通过一个调用执行，而且允许用户声明变量、有条件执行以及其他强大的编程功能。“存储过程到底是什么？！”（1）存储过程（PROCEDURE）类似于C语言中的函数；（2）用来执行管理任务或应用复杂的业务规则；（3）存储过程可以带参数，也可以返回结果；（4）存储过程可以包含数据操纵语句、变量、逻辑控制语句等。7.1.2存储过程的优点7.1.2

存储过程的优点1、直接对学生表和选课表进行修改。（好与坏？）方法：删除某学生记录的同时，删除其选课记录分两步：假如学生相关的数据表有100多张，怎么办！！！！假如有10位学生的数据要删除，怎么办！！！deletefrom

选课表where

学号='2013020001'delete

from

学生表where

学号='2013020001'2、创建一个存储过程，以实现对上述的修改。（好与坏？）假如已经建好了存储过程：del_student调用：execdel_student‘2013020001’7.1.2

存储过程的优点可以出于任何使用SQL语句的目的来使用存储过程。它具有以下优点：（1）允许模块化程序设计：可以在单个存储过程中执行一系列SQL语句，可以从自己的存储过程内引用其他存储过程，这可以简化一系列复杂语句。（2）执行速度更快：存储过程在创建时即在服务器上进行编译，所以执行起来比单个SQL语句快。（3）减少网络流通量：减少网络通信的负担。（4）提高系统安全性。7.1.3存储过程的分类7.1.3

存储过程的分类在SQLServer中存储过程分为两类：系统提供的存储过程和用户自定义存储过程（1）系统存储过程系统存储过程由系统定义，存放在master数据库中，它类似C语言中的系统函数，系统存储过程的名称都以“sp_”开头或”xp_”开头。（2）用户自定义存储过程用户自定义存储过程由用户在自己的数据库中创建的存储过程，它类似C语言中的用户自定义函数。7.1.4常用的系统存储过程7.1.4

常用的系统存储过程系统存储过程存储在master数据库中，并以sp_为前缀，主要用来从系统表中获取信息，为系统管理员管理SQLServer提供帮助，为用户查看数据库对象提供方便。比如用来查看数据库对象信息的系统存储过程sp_help、显示存储过程和其它对象的文本的存储过程sp_helptext等。7.1.4

常用的系统存储过程系统存储过程说明sp_databases列出服务器上的所有数据库sp_helpdb报告有关指定数据库或所有数据库的信息sp_renamedb更改数据库的名称sp_tables返回当前环境下可查询的对象的列表sp_columns返回某个表列的信息sp_help查看某个表的所有信息sp_helpconstraint查看某个表的约束sp_helpindex查看某个表的索引sp_stored_procedures列出当前环境中的所有存储过程sp_password添加或修改登录帐户的密码sp_helptext显示默认值、未加密的存储过程、用户定义的存储过程、触发器或视图的实际文本7.1.4

常用的系统存储过程调用常用的系统存储过程，可以使用EXEC，如：EXEC

sp_databasesEXEC

sp_renamedb

'db1','db2'USE

示例数据库GOEXEC

sp_tables

EXEC

sp_columns

学生表EXEC

sp_help

学生表EXEC

sp_helpconstraint

学生表EXEC

sp_helpindex

学生表EXEC

sp_helptext

'v_student'

EXEC

sp_stored_procedures

--列出当前系统中的数据库--修改数据库的名称(单用户访问)--当前数据库中查询的对象的列表--返回当前环境下可查询的对象的列表--返回某个表列的信息--查看表”学生表”的信息--查看表”学生表”的约束--查看表”学生表”的索引--查看表”学生表”具体创建的语句--查看当前数据库中的存储过程上机实验任务一实验7-1第一题：【系统存储过程的使用】20分钟7.1.5创建与调用存储过程7.1.5

创建与调用存储过程在SQLServer中，可以使用三种方法创建存储过程：①使用创建存储过程向导创建存储过程。②利用SQLServer对象管理器创建存储过程。③使用Transact-SQL语句中的CREATEPROCEDURE命令创建存储过程。1、创建存储过程7.1.5

创建与调用存储过程下面介绍CREATEPROCEDURE命令的使用。定义存储过程的语法：CreateProc[edure]<存储过程名>[<@参数名1>…]

as<表达式>SQL语句1、创建存储过程7.1.5

创建与调用存储过程例1：学号为“2011101”的孙雷同学办理退学手续，系统要求删除该学生记录的同时，删除其选课记录。Create

Procedure

del_student

as

BEGIN

delete

from

选课表

where

学号='2011101'

delete

from

学生表

where

学号='2011101'ENDCreate

Procedure

del_student

@sno

nvarchar(50)as

BEGIN

delete

from

选课表

where

学号=@sno

delete

from

学生表

where

学号=@snoEND1、创建存储过程7.1.5

创建与调用存储过程调用存储过程的语法：直接执行存储过程可以使用EXECUTE命令来执行，其语法形式如下：2、调用存储过程exec[ute]<存储过程名>[<@参数名1>…]执行存储过程：要执行存储过程，只要调用存储过程名即可。如果过程不是批命令中的第一条语句，则要在过程名前面加上关键字EXEC。这是因为存储过程名不是关键字，当语句在执行过程中遇到存储过程名时，会将其作为上一条语句的一部分处理，因此导致执行出错。7.1.5

创建与调用存储过程2、调用存储过程例2：执行存储过程del_studentdel_student对Select

*

from

学生表del_student

Select

*

from

学生表exec

del_student

对错，因为del_student不是SQL定义的关键字，所以分析器将其解释成了student表的别名，从而导致执行结果出错。修改为7.1.5

创建与调用存储过程3、存储过程的实训任务创建存储过程

Create

Proc[edure]

存储过程名

@参数1

数据类型=默认值OUTPUT,

@参数n

数据类型=默认值OUTPUT

AS

SQL语句任务1：没有参数的存储过程任务2：有输入参数的存储过程任务3：有参数默认值的存储过程任务4：有输出参数的存储过程的创建任务5：有输出参数的存储过程的执行和C语言的函数一样，参数可选；参数分为输入参数、输出参数；输入参数允许有默认值；OUTPUT：表明该参数是一个返回参数。7.1.5

创建与调用存储过程例3：创建一个没有参数的存储过程。任务1：没有参数的存储过程create

proc

test1

as

begin

--SQL代码省略end调用该存储过程exec

test1

7.1.5

创建与调用存储过程例4：创建一个没有参数的存储过程proc_ItemInfo，查询物料的基本信息。任务1：没有参数的存储过程create

procedure

proc_ItemInfoas

select

*

from

物料go调用该存储过程execproc_ItemInfo上机实验任务二实验7-1第二题：【存储过程基础练习】8分钟1、创建一个无参数存储过程p1并调用该存储过程，要求查询以下信息：客户编号、客户名称、产品编号、产品名称、价格，购买日期、购买数量。7.1.5

创建与调用存储过程存储过程的参数分两种：输入参数、输出参数。输入参数：用于向存储过程传入值，类似C语言的按值传递；输出参数：用于在调用存储过程后，返回结果，类似C语言的按引用传递。7.1.5

创建与调用存储过程例5：创建一个有参数的存储过程任务2：有输入参数的存储过程create

proc

test2@value1

int,@value2

intasbegin--SQL代码省略end调用该存储过程exec

test2

1000,2000--或：exec

test2

@value1

=1000,

@value2=2000上机实验任务三实验7-1第二题：【存储过程基础练习】8分钟2、创建一个带参数的存储过程p2并调用该存储过程，要求该存储过程根据传入的员工编号，在员工人事表中查询该员工的信息。7.1.5

创建与调用存储过程例6：设置参数的默认值任务3：有参数默认值的存储过程Create

Proc

test3@value1

int=100,

@value2

int=400asbegin

--SQL代码省略end调用该存储过程exec

test3--都采用默认值--或：exec

test3

300,500--都不采用默认值exec

test3

300--value2采用默认值exec

test3

,300--value1采用默认值×exec

test3

@value2

=500①默认值放在参数的数据类型后面，而不是放在参数变量的后面。②为了调用的方便，推荐将默认参数放置在参数列表的最后。上机实验任务四实验7-1第二题：【存储过程基础练习】8分钟3、创建一个带参数默认值的存储过程p3，并调用该存储过程，要求该存储过程根据查询指定薪水范围的员工信息。默认值：最低薪水：3500，最高薪水：50007.1.5

创建与调用存储过程例7：创建带有输出参数的存储过程任务4：有输出参数的存储过程如果调用批命令将变量作为参数传入存储过程，而参数在存储过程中被修改，则修改不会传给调用该存储过程的命令，除非生成和执行存储过程时对参数指定OUTPUT关键字。create

proc

test4@value

varchar(50),

@value_out

varchar(50)

OUTPUTasselect

@value_out

=valueX

from

table1

where

value=@value