4.1.1创建和执行存储过程在线视频

大家好

欢迎来到数据库技术应用在线开放课程

今天我们要完成的任务是创建和执行存储过程

共分成三个子任务

分别是子任务一

创建存储过程实现开卡时对卡号和金额的约束

如果卡号已经存在

提示该卡号已经被注册

如果金额小于一元

则提示开户金额不能低于一元

成功开卡提示

开卡成功并将开卡数据插入到表当中

第二个子任务调用存储过程

第三个子任务删除存储过程

那么本节课的目标是让大家理解存储过程的意义

掌握存储过程定义的方法

掌握存储过程管理的方法

让我们一起进入这是技能学习环节

首先我们看一下存储过程的优点

第一他是在服务器端运行执行速度快

第二执行一次后规划就驻留在高速缓冲存储器当中

提高了系统的性能

第三确保了数据库的安全性

所以在我们前期所做的增删改查的基础上

我们要增加数据库的安全性

提高他的查询效率

那么我们需要借用到存储过程来实现

那怎么来创建存储过程

我们来看一下它的语法create procedure存储过程名称

括号是他所带的参数

这个参数可以是输入参数也可以是输出参数

也可以是输入输出参数

所以用in out inout来控制参数的类型

那么存储过程体式存储过程的主体

我们需要用到begin end 把他框住

delimiter是修改结束标志

我们在做存储过程的时候

里面包含很多的我们前面所学到的

增删改查语句等等简单的语句格式

所以要把它整合成存储过程

我们需要有特殊的结束符

但是结束之后

你还需要把他变更成他原有的分号结束

第三存储过程还涉及到复杂的编程

我们接下来需要补充一些数据库编程的知识

比如说局部变量 说到变量 大家都不陌生

我们知道变量要先声明后使用它的语法格式是declear

变量类型就可以对变量进行声明

也就是我们做开辟空间的这样的操作

比如说declear str1,str2,varchar6

我们声明了两个变量str1,str2开辟的空间

按照变长字符类型最大为6开辟了空间

那么我们的局部变量

他只能在刚刚说的begin end的语句块当中进行声明

接下来变量的赋值

声明完之后

我们就可以对它进行赋值了

赋值的方法

第一个set

变量名等于表达式

比如说set num等于1

str1等于hello

给这两个变量做了一个赋值操作

我们还可以用select into 这样的语法

把选定的列值直接存储在我们的变量当中

select列名

into 变量名

比如说select userID fullname into 用户编号

用户姓名这两个变量里

from account where fullname等于李昊华

就可以实现查询的同时

把我们查到的结果存在了那两个变量当中

接下来流程控制语句在程序设计当中

流程分成顺序结构

选择分支结构和循环结构

所以在这里说一下

在数据库编程当中

我们也可以沿用选择分支结构和循环结构

当然也包括顺序结构

那如果大家用到选择分支结构的话

我们仍然用if语句来实现

在mysql当中

它的语法是这样的

if条件1 then语句序列1

else if条件2 then语句序列2

else语句序列n

根据你分支的个数来选择我们用哪一个子句

最后记得endif结束

那么循环结构用什么来控制

用while条件DO程序段

end while来控制我们重复执行的循环语句

那么这些知识有了之后

我们存储过程就可以创建了

创建完存储过程

我们想查看一下当前服务器上面有哪些存储过程

我们沿用前面的知识show database查看数据库

show table查看我们的表

那现在我们要查看存储过程的话

用到的就是show procedure status

可以查看到我们存储过程的状态

接下来存储过程做好之后

他是一个静态的对象

存在了我们的服务器上面

如果你想让它发挥作用的话

我们需要用call来进行一个调用

方法就是call存储过程名称

括号把参数列表一写

我们就可以实现实参和形参的一个传递

调用存储过程

那么最后一个存储过程如果不需要了

我们可以用drop procedure存储过程名称

进行一个存储过程的删除

那接下来我们来进入任务实施环节

我们来分析一下这个任务

需要画一个简单的流程图

实现开户时对卡号和金额的约束

也就说我们首先要做一个卡号的判断

卡号是否存在卡号如果存在

那么我们提示该卡号已经被注册

那如果卡号不存在

我们还要做一个判断看他的金额是否大于一

如果金额大于一那么开户成功数据可以实现插入

那么相反如果金额不大于1

那提示一下开户金额不能低于一元

也就是说我们用到了两层的两次的判断

那么实施代码我们就可以写出来了

首先作为存储过程需要给他变更一下结束符 delimter

用我们的两个$符号进行一个结束

那么存储过程结束之后

大家记得要把它再变更成我们的分号

那我们就可以创建存储过程了

create procedure

后面是我们要放的参数列表

在这个问题当中

我们涉及到了六个参数

其中五个是我们的输入参数

大家看都有一个in 的标识

还有一个是输出参数较接近

是我们的结果

这个前面标识了out通过in 和 out

大家就可以看到它们分别代表是输入参数和输出参数

所以在传递参数的时候需要对应上这个对应关系

接下来是存储过程体begin

begin下面要进行我们刚才所分析的流程图当中的判断了

If exist seelect cardID from cardinfo where cardID=cardID 1

那么这里面实现就是如果存在卡号的话

我该怎么办接下来

then set结果等于该卡号已经被注册

有一个提示性的结果出现

Else if如果说这个卡号没被注册

我还要再做个判断吧

balance小于1满足条件不

如果说她的balance小于1

那我还要做一个提示

then set jg等于开户金额不能低于一元

else相反

也就是说他开户金额也满足条件的情况下

else我要做什么

Begin end要做我的数据的真正的插入了

插入用到的语句就是insert into values

跟咱们前面要求的是一样的

你需要把你的字段列表和值列表一要匹配上

并且能做了一个set结果等于开户成功

也就是提示用户开卡成功了

那么所有的if都写完了

那么接下来我要按着endif进行结束

也就是选择分支结构我们要有个结尾

那整个存储过程结束之后

后面有个end

然后用我们俩个$符号进行一个结束

最后记得变更我们的结束符为分号

这样我们的存储过程就写完了

接下来看第二个子任务就要调用存储过程

刚刚咱们说了存储过程创建好之后

他作为一个对象放在我们的服务器上了

但是如果你想让它有效果

需要进行一个调用

用到我们的call进行调用

那前面咱们说了

通过流程图分析他有三个分支

所以我在调用的时候专门写了三个调用的语句

测试他的三个分支包括卡号已经存在

包括余额不足

包括开卡成功三个分支分别做了一个测试

测试完之后

大家注意

我们需要用select * from ucardinfo查看一下数据

看看我们前面做的是否有问题

第三将删除存储过程

那如果说这个存储过程不需要了

我要把它进删除很简单

drop procedure后面跟上存储过程的名称

createID就可以删除了

那如果说你要删除多个存储过程

直接在后面罗列名称

逗号隔开也可以实现

那么今天的拓展任务是员工管理数据库存储过程的创建

感谢大家的聆听