关系数据库与SQL Server 2005 教学课件 ppt 作者 龚小勇 第25讲 存储过程

1、第9章 存储过程和触发器 存储过程的使用,主讲人：段利文,第25讲,复习:,使用批处理、脚本、注释 标识符、常量、变量 T-SQL系统内置函数 流程控制语句 beginend语句 ifelse、if exists语句 while、break和continue语句 waitfor语句 return语句,第9章 存储过程和触发器 存储过程的使用 学习目标,认知目标： 掌握存储过程的定义及优点 了解常用的系统存储过程 能力目标: 掌握存储过程的创建和执行(重点) 掌握存储过程中输入参数和输出参数的设置和使用方法。 (难点),一、存储过程的基本概念 1.存储过程的含义及功能,存储过程的含义 是一组预先写好的能实现某种功能的T-SQL程序，是存储在服务器上的预编译集合。 存储过程是一种独立的数据库对象，在服务器上创建和运行。 类似于编程语言中的过程或函数。,一、存储过程的基本概念 2.存储过程的优点,模块化编程 执行速度快效率高 存储过程在执行1次后，其执行计划就保存在内存中，以后在执行时就不必再进行编译和优化。 减少网络流量 由于存储过程是存在服务器上的一组T-SQL，在客户端调用时，可以只用

2、一条调用语句即可执行。 提供一种安全机制 可以限制用户访问存储过程中涉及到的表的权限，只授予执行存储过程的权限，用户只能通过存储过程来访问表，从而保证了数据的安全性。,一、存储过程的基本概念 3.存储过程的类型,系统存储过程 是指由SQL Server提供的存储过程，可以作为命令执行。 系统存储过程定义在系统数据库master中，其前缀是sp_。 用户存储过程 是由用户在当前数据库中创建的存储过程。 最好不要以sp开头。若用户定义的存储过程与系统存储过程同名，用户定义的存储过程永远不会执行。,二、创建和执行存储过程 1.简单存储过程,创建格式： create procedure 存储过程名 as SQL语句 说明：create procedure不能与别的T-SQL语句位于同一个批中。 相应的执行格式： exec 存储过程名,二、创建和执行存储过程 1.简单存储过程,实例1：创建1个简单的存储过程p_cj1，查询所有学生的学号、姓名、课程号和成绩信息，并按成绩降序排列。 use xscj go create procedure p_cj1 as select xsqk.学号,姓名,课程

3、号,成绩 from xsqk, xs_kc where xsqk.学号=xs_kc.学号 order by 4 desc go exec p_cj1 -执行存储过程,二、创建和执行存储过程 2.使用输入参数,创建格式： create procedure 存储过程名 形参 数据类型 =默认值 ，n as SQL语句 相应的执行格式： execute 存储过程名 实参= 值 ,n 输入参数的传递方式有两种： 按位置传递：直接给出参数的值，实参与形参一一对应 通过参数名传递：使用“参数名=参数值“的形式，参数可以任意顺序给出。,输入形参：接收从外部传递给存储过程的数据。,输入实参：将数据传递给存储过程。,二、创建和执行存储过程 2.使用输入参数,实例2：创建1个带有输入参数的存储过程p_cj2，查询指定课程号(作为输入参数)的学生成绩信息。 create procedure p_cj2 kch char(3)=101 -有默认值的输入形参：接收外部传递的数据 as select * from xs_kc where 课程号= kch go exec p_cj2 -（1）使用默认值执行存储过程

4、 exec p_cj2 102 -（2）按位置传递参数 exec p_cj2 kch=103 -（3）通过参数名传递参数,二、创建和执行存储过程 2.使用输入参数,课堂练习1：创建并执行带输入参数的存储过程p_xsqk，查询指定学号(作为输入参数)的学生姓名、课程号、成绩。 create procedure p_xsqk xh char(6) as select 姓名，课程号，成绩 from xsqk , xs_kc where xsqk.学号=xs_kc.学号 and xsqk.学号= xh go exec p_xsqk 020102 -（1）按位置传递参数 exec p_xsqk xh=020103 -（2）通过参数名传递参数 注意：因输入参数没有默认值，所以不能用“exec p_xsqk”,二、创建和执行存储过程 3.使用输出参数,创建格式： create procedure 存储过程名 形参 数据类型 output ，n as SQL语句 相应的执行格式： execute 存储过程名 实参 output ,n 说明： 输出实参和输出形参的名字可以相同，也可以不同。 使用时，要先

5、声明输入和输出实参变量。,输出形参：将数据返回给调用它的程序。,输出实参：从存储过程将数据带回。,二、创建和执行存储过程 3.使用输出参数,实例3：创建1个带有输入参数和输出的存储过程p_kh，返回指定教师(作为输入参数)所授课程的课程号(作为输出参数)。 create procedure p_kh teacher char(8) , kch char(3) output as select kch = 课程号 from kc where 授课教师= teacher go declare teacher varchar(8), kch char(3) set teacher=赵怡 exec p_kh teacher, kch output print teacher + 教师所受课程的课程号为: + kch,输入实参：将教师名传递给存储过程。,输出实参：将带回课程号。,输入形参,输出形参：保存查询到的课程号,输出形参：将数据返回给调用它的程序,课堂练习2：创建并执行带输入和输出参数的存储过程p_cj3，查询指定学号(作为输入参数)学生所选课程的课程名和成绩(两个作为输出参数)。 cre

6、ate proc p_cj3 xh char(6), kcm char(3) output, cj int output as select kcm=kc.课程名, cj=成绩 from kc_xs , kc where kc_xs.课程号=kc.课程号 and 学号=xh go declare xh char(6), kcm char(3) , cj int set xh=020101 exec p_cj3 xh , kcm output , cj output print xh+ 学号所选修的课程是 + kcm + 。其成绩是 + cast(cj as varchar(5),二、创建和执行存储过程 4.使用返回值,语句格式： return 整型表达式 作用：用来显示存储过程的执行情况。 相应的执行格式： execute 状态值=存储过程名,二、创建和执行存储过程 4.使用返回值,实例4：创建并执行存储过程p_find，用于查找指定的学生，如果找到，则返回数字1，否则返回0。 create procedure p_find findname char(8) as if exists

7、(select * from xsqk where 姓名=findname) return 1 else return 0,go -执行存储过程 declare result int exec result=p_find 陈伟 if result =1 print 有这个人！ else print 没有这个人！,三、修改存储过程,语句格式： alter procedure 存储过程名 形参 数据类型 =默认值 output ，n as SQL语句 说明： 修改存储过程的定义后，原存储过程的权限设置仍有效 如果采用先删除存储过程再重建同名存储过程的方法，那么在原来存储过程上设置的权限将会全部丢失。,四、删除、查看存储过程,删除存储过程： 语句格式：drop procedure 存储过程名 查看存储过程： sp_help 存储过程名 -显示存储过程的基本信息 sp_helptext 存储过程名 -显示存储过程的源代码,本课小结,存储过程的含义、优点、类型 创建存储过程：需要确定存储过程的三个组成部分： 所有的输入参数、传给调用程序的输出参数。 被执行的针对数据库操作的语句，包括调用其它存储过程的语句。 返回给调用程序的状态值，以指明调用是成功或失败 执行存储过程 修改、删除、查看存储过程,课余作业布置,课外练习： P213 1、2、3 创建并执行带输入和输出参数的存储过程p_cj3，查询指定学号(作为输入参数)学生所选课程的课程名和成绩(两个作为输出参数),调用存储过程后，显示“XX学号选修的课程名为XX,其成绩是：XX”。 上机准备：作业上机调试。,

《关系数据库与SQL Server 2005 教学课件 ppt 作者 龚小勇 第25讲 存储过程》由会员E****分享，可在线阅读，更多相关《关系数据库与SQL Server 2005 教学课件 ppt 作者 龚小勇 第25讲 存储过程》请在金锄头文库上搜索。