过程:
封装了若干条语句.调用时,这些封装体执行.
函数:
是一个有返回值的过程.
过程是没有返回值的函数
存储过程:
把此过程存储在数据库中,因此称为存储过程
存储过程语法
# 创建语法:
create procedure procedureName()
begin
-- sql语句
end$
# 删除存储过程
drop procedure procedureName;
# 查看已有的procedure
show procedure status;
# 调用存储过程:
call procedureName();
存储过程1--创建简单的存储过程
# eg:
create procedure p1()
begin
select * from tableName;
end$
存储过程2--引入变量
# 存储过称是可以编程的,意味着可以使用变量,表达式,控制结构
# eg:
create procedure p2()
begin
declare age int default 18;
declare height int default 180;
select concat('年龄是',age,'身高是',height);
end$
存储过程3--变量运算
# 存储过称中,变量可以在sql语句中合法的运算,如+-*/,注意运算的结果是如何赋值给变量
# 语法--set 变量名 := expression
# eg:
create procedure p3()
begin
declare age int default 18;
declare height int default 180;
set age := age + 20;
select concat('20年后年龄是',age,'身高是',height);
end$
存储过称4--控制结构(if/else)
# 语法:
if
conditon
then
statement
else
statement
end;
# eg:
create procedure p4()
begin
declare age int default 18;
if age >= 18 then
select '已成年';
else
select '未成年';
end if;
end$
存储过称5--控制结构2(while)
# 三种控制结构:
顺序,选择,循环
# eg: 求1-100的和
create procedure p5()
begin
declare total int default 0;
declare num int default 0;
while num <= 100 do
set total := total + num;
set num := num + 1;
end while;
select total;
end$
存储过称6--控制结构3(case)
# 用if/else也能实现同样的效果
# eg:
create procedure p6()
begin
declare pos int default 0;
set pos := floor(5*rand());
case pos
when 1 then select 'fly';
when 2 then select 'sea';
else select 'run';
end case;
end$
存储过称7--控制结构4(repeat)
# 用while也能实现同样的效果
# eg:
create procedure p7()
begin
declare total int default 0;
declare i int default 0;
repeat
set i := i+1;
set total := total + i;
until i>=100
end repeat;
select total;
end$
存储过称8--传参
# 存储过程的括号里,可以声明参数,
# 语法:
[in/out/inout] 参数名 参数类型,不写时,默认为in
# 调用:
call p8(3,4)
# eg:
create procedure p8(width int , height int)
begin
select concat('你的面积是',width * height) as area;
if width > height then
select '宽大于高';
elseif width < height then
select '宽小于高';
else
select '宽等于高';
end if;
end$
存储过称9--in型参数
# 存储过程中可以接收到这个参数
# eg: 求1-n的和
create procedure p9(in n int)
begin
declare total int default 0;
declare num int default 0;
while num <= n do
set total := total + num;
set num := num + 1;
end while;
select total;
end$
存储过称10--out型参数
# 将存储过程中值输出
# 调用:
call p10(100,@argsName)
# eg: 求1-n的和
create procedure p10(in n int,out total int)
begin
declare num int default 0;
set total := 0;
while num <= n do
set total := total + num;
set num := num + 1;
end while;
select total;
end$
# 此处执行完成后,输入select @argsName即可得到对应的结果
存储过称11--inout型参数
# 调用:
set @age = 18
call p11(@age)
# eg:
create procedure p11(inout age int)
begin
set age := 1;
set age := age + 20;
end$
存储过称12--cursor 游标
# 一条sql,对应N条资源,取出资源的接口/句柄,就说游标
# 沿着游标,可以一次取出一行
# eg:
create procedure p12()
begin
declare row_gid int;
declare row_num int;
declare row_name varchar(20);
-- 这种方法不推荐使用
-- 定义一个游标
declare cursorName cursor for select gid,num,name from goods;
open cursorName;
-- 将显示的第一行值赋予对应的变量
fetch cursorName into row_gid,row_num,row_name;
select row_gid,row_num,row_name;
-- 将显示的第二行值赋予对应的变量
fetch cursorName into row_gid,row_num,row_name;
select row_gid,row_num,row_name;
-- 将显示的第三行值赋予对应的变量(如果只有两条数据,赋值三次会报错)
fetch cursorName into row_gid,row_num,row_name;
select row_gid,row_num,row_name;
close cursorName;
end$
存储过称13--cursor 游标(推荐使用)
# eg:
create procedure p13()
begin
declare row_gid int;
declare row_num int;
declare row_name varchar(20);
declare i int default 0;
-- 定义一个变量,用来计算显示了几条数据
declare cnt int default 0;
-- 定义一个游标
declare cursorName cursor for select gid,num,name from goods;
select count(*) into cnt from goods;
open cursorName;
repeat
set i := i +1;
fetch cursorName into row_gid,row_num,row_name;
select row_gid,row_num,row_name;
until i >= cnt
end repeat;
close cursorName;
end$
存储过称14--越界标志(continue handler for not found)
# 在MySQL cursor中,可以declare continue handler来操作一个越界标志
# 语法: declare continue handler for not found 语句;
# 表中只有三条数据,但是下面的代码执行后 ,会取出四行,最后一行显示两次
# 代码中有逻辑错误,分析:
当第四次执行 repeat 时,fetch-->没有数据,触发越界标志中的not found-->set you := 0-->continue-->执行fetch后面的sql语句(select row_gid,row_num,row_name;)-->所以,最后一行被取出两次
# eg:
create procedure p14()
begin
declare row_gid int;
declare row_num int;
declare row_name varchar(20);
declare you int default 1;
-- 定义一个游标
declare cursorName cursor for select gid,num,name from goods;
declare continue handler for not found set you := 0;
open cursorName;
repeat
fetch cursorName into row_gid,row_num,row_name;
select row_gid,row_num,row_name;
until you = 0
end repeat;
close cursorName;
end$
存储过称15--越界标志(exit handler for not found)
# 越界标志解决逻辑错误(不完善,没考虑到表为空的情况)
# 引入另一种越界标志:declare exit handler for not found 语句;
# eg:
create procedure p15()
begin
declare row_gid int;
declare row_num int;
declare row_name varchar(20);
declare you int default 1;
-- 定义一个游标
declare cursorName cursor for select gid,num,name from goods;
declare exit handler for not found set you := 0;
open cursorName;
repeat
fetch cursorName into row_gid,row_num,row_name;
select row_gid,row_num,row_name;
until you = 0
end repeat;
close cursorName;
end$
存储过称16--越界标志(undo handler)
# 除以上两种越界标志(continue,exit)外,还有undo handler
# continue是触发后,后面的语句继续执行
# exit是触发后,后面的语句不再执行
# undo是触发后,前面的语句撤销(MySQL暂不支持)
# 不改变越界标志,解决逻辑错误(完善)
# eg:
create procedure p16()
begin
declare row_gid int;
declare row_num int;
declare row_name varchar(20);
declare you int default 1;
-- 定义一个游标
declare cursorName cursor for select gid,num,name from goods;
declare exit handler for not found set you := 0;
open cursorName;
-- 1: 先取出一行数据,如果取出,则 not found未触发,
fetch cursorName into row_gid,row_num,row_name;
repeat
-- 2: 执行显示语句
select row_gid,row_num,row_name;
-- 3: 在进行fetch如果取不到,触发continue越界标志,继续向下执行,
-- 下面没有sql语句,所以不会再显示
fetch cursorName into row_gid,row_num,row_name;
until you = 0
end repeat;
close cursorName;
end$
