第8章-第1讲-存储过程课件

第9章存储过程和触发器

——存储过程的使用主讲人：白杨第25讲复习:使用批处理、脚本、注释标识符、常量、变量T-SQL系统内置函数流程控制语句begin…end语句if…else、ifexists语句while、break和continue语句waitfor语句return语句第9章存储过程和触发器

——存储过程的使用

学习目标认知目标：掌握存储过程的定义及优点了解常用的系统存储过程能力目标:掌握存储过程的创建和执行(重点)掌握存储过程中输入参数和输出参数的设置和使用方法。

(难点)一、存储过程的基本概念

——1.存储过程的含义及功能存储过程的含义是一组预先写好的能实现某种功能的T-SQL程序，是存储在服务器上的预编译集合。存储过程是一种独立的数据库对象，在服务器上创建和运行。类似于编程语言中的过程或函数。一、存储过程的基本概念

——2.存储过程的优点模块化编程执行速度快效率高存储过程在执行1次后，其执行计划就保存在内存中，以后在执行时就不必再进行编译和优化。减少网络流量由于存储过程是存在服务器上的一组T-SQL，在客户端调用时，可以只用一条调用语句即可执行。提供一种安全机制可以限制用户访问存储过程中涉及到的表的权限，只授予执行存储过程的权限，用户只能通过存储过程来访问表，从而保证了数据的安全性。一、存储过程的基本概念

——3.存储过程的类型系统存储过程是指由SQLServer提供的存储过程，可以作为命令执行。系统存储过程定义在系统数据库master中，其前缀是sp_。用户存储过程是由用户在当前数据库中创建的存储过程。最好不要以sp开头。若用户定义的存储过程与系统存储过程同名，用户定义的存储过程永远不会执行。二、创建和执行存储过程

——1.简单存储过程创建格式：createproc[edure]存储过程名

asSQL语句说明：createprocedure不能与别的T-SQL语句位于同一个批中。相应的执行格式：[exec]存储过程名二、创建和执行存储过程

——1.简单存储过程实例1：创建1个简单的存储过程p_cj1，查询所有学生的学号、姓名、课程编号和成绩信息，并按成绩降序排列。usexscjgocreate

procedurep_cj1

as

select学生基本信息表.学号,姓名,课程编号,成绩from学生基本信息表,成绩表

where学生基本信息表.学号=成绩表.学号

orderby4descgoexecp_cj1

--执行存储过程二、创建和执行存储过程

——2.使用输入参数创建格式：createproc[edure]存储过程名@形参数据类型[=默认值]，…nasSQL语句相应的执行格式：[execute]存储过程名[@实参=]值,…n输入参数的传递方式有两种：按位置传递：直接给出参数的值，实参与形参一一对应通过参数名传递：使用“参数名=参数值“的形式，参数可以任意顺序给出。输入形参：接收从外部传递给存储过程的数据。输入实参：将数据传递给存储过程。二、创建和执行存储过程

——2.使用输入参数实例2：创建1个带有输入参数的存储过程p_cj2，查询指定课程编号(作为输入参数)的学生成绩信息。create

procedurep_cj2@kchchar(3)=‘101’--有默认值的输入形参：接收外部传递的数据

as

select*from成绩表where课程编号=@课程信息表hgoexecp_cj2--（1）使用默认值执行存储过程execp_cj2‘102‘--（2）按位置传递参数execp_cj2@kch=‘103‘--（3）通过参数名传递参数输入实参：将数据传递给存储过程。二、创建和执行存储过程

——2.使用输入参数课堂练习1：创建并执行带输入参数的存储过程p_学生基本信息表，查询指定学号(作为输入参数)的学生姓名、课程编号、成绩。

create

procedurep_学生基本信息表@xhchar(6)

as

select姓名，课程编号，成绩

from学生基本信息表,

成绩表

where学生基本信息表.学号=成绩表.学号and学生基本信息表.学号=@xhgoexecp_学生基本信息表‘020102‘--（1）按位置传递参数execp_学生基本信息表@xh=‘020103‘--（2）通过参数名传递参数注意：因输入参数没有默认值，所以不能用“execp_学生基本信息表”二、创建和执行存储过程

——3.使用输出参数创建格式：createproc[edure]存储过程名@形参数据类型output，…nasSQL语句相应的执行格式：[execute]存储过程名@实参output,…n说明：输出实参和输出形参的名字可以相同，也可以不同。使用时，要先声明输入和输出实参变量。输出形参：将数据返回给调用它的程序。输出实参：从存储过程将数据带回。二、创建和执行存储过程

——3.使用输出参数实例3：创建1个带有输入参数和输出的存储过程p_kh，返回指定教师(作为输入参数)所授课程的课程编号(作为输出参数)。create

procedurep_kh@teacherchar(8),@kchchar(3)output

as

select@kch=课程编号from课程信息表where任课教师=@teachergodeclare@teachervarchar(8),@课程信息表hchar(3)set@teacher='赵怡'execp_kh@teacher,@kchoutputprint@teacher+‘教师所授课程的课程号为:’+@kch输入实参：将教师名传递给存储过程。输出实参：将带回课程号。输入形参输出形参：保存查询到的课程号输出形参：将数据返回给调用它的程序

课堂练习2：创建并执行带输入和输出参数的存储过程p_cj3，查询指定学号(作为输入参数)学生所选课程的课程名称和成绩(两个作为输出参数)。

create

procp_cj3@xhchar(6),@kcmchar(3)output,@cjint

output

as

select@kcm=课程信息表.课程名称,@cj=成绩from成绩表,课程信息表

where成绩表.课程编号=课程信息表.课程编号and学号=@xhgodeclare@xhchar(6),@kcmchar(3),@cjintset@xh='020101'execp_cj3@xh,@kcmoutput,@cjoutputprint@xh+‘学号所选修的课程是《’+@kcm+‘》。其成绩是’+cast(@cjasvarchar(5))二、创建和执行存储过程

——4.使用返回值语句格式：return整型表达式作用：用来显示存储过程的执行情况。相应的执行格式：[execute]@状态值=存储过程名二、创建和执行存储过程

——4.使用返回值实例4：创建并执行存储过程p_find，用于查找指定的学生，如果找到，则返回数字1，否则返回0。create

procedurep_find@findnamechar(8)

as

ifexists(select*from学生基本信息表

where姓名=@findname)

return1

else

return0go--执行存储过程declare@resultintexec@result=p_find‘陈伟‘if@result=1

print

‘有这个人！‘

else

print

‘没有这个人！‘三、修改存储过程语句格式：alterproc[edure]存储过程名[@形参数据类型[=默认值][output]，…n]asSQL语句说明：修改存储过程的定义后，原存储过程的权限设置仍有效如果采用先删除存储过程再重建同名存储过程的方法，那么在原来存储过程上设置的权限将会全部丢失。四、删除、查看存储过程删除存储过程：语句格式：dropproc[edure]存储过程名查看存储过程：sp_help存储过程名--显示存储过程的基本信息sp_helptext存储过程名--显示存储过程的源代码本课小结存储过程的含义、优点、类型创建存储过程：需要确定存储过程的三个组成部分：所有的输入参数、传给调用程序的输出