一、存储过程的定义
过程:procedure 封装了若干条语句,调用时,这些封装体执行
方法:function 有返回值得过程
把若干条sql封装起来,起个名字,方便调用,并存储在数据库中————存储过程。
创建存储过程语法:
delimiter $
create procedure 存储过程名()
begin
//sql code;
end$
delitmiter ;
查看已经存在的存储过程: show procedure status;
调用存储过程:call 存储过程名();
删除存储过程:drop procedure 存储过程名;
二、存储过程编辑基础
1、声明变量
declare 变量名 数据类型 [default 默认值]
例子:
delimiter $
create procedure see()
begin
declare age int default 10 ;
declare name varchar(20) default 'whl' ;
set age:= age + 8;
select concat( "姓名:",name,",年龄:",age) ;
end$
delimiter ;
注意:不要用show等关键字作为存储过程名
2、控制与计算
存储过程的参数:存储过程的括号中可以声明参数
语法是 [ in/out/inout ] 参数名 参数类型
delimiter $
create procedure see(name varchar(20),age int)
begin
select concat("你好,",name) as title;
if age > 18 then
select "你已经成年" as result;
elseif age = 18 then
select "你刚成年" asresult;
else
select "你未成年" as result;
end if;
end$
delimiter ;
3、存储过程的控制结构 :顺序(代码执行顺序),选择(if/else),循环
delimiter $
create procedure sum_number()
begin
declare total int default 0;
declare number int default 0;
while number < 100 do
set number := number + 1;
set total := total + number;
end while;
select total as result;
end$
delimiter ;
参数使用:in ,out
delimiter $
create procedure sum_number(in num int,out total int)
begin
declare number int default 0;
set total := 0;
while number < num do
set number := number + 1;
set total := total + number;
end while;
end$
delimiter ;
call sum_number(100,@sumary); //声明变量
select @sumary;
参数使用:inout
delimiter $
create procedure sum_number(inout num int)
begin
declare rnum int default num;
declare number int default 0;
set num := 0;
while number < rnum do
set number := number + 1;
set num := num+ number;
end while;
end$
delimiter ;
set @sumary := 100; //声明变量
call sum_number(@sumary);
select @sumary;
注意:声明 但未初始化的变量 都是null 有null参与的结果 为null
4、case 结构的用法
例子:一个获取随机数的存储过程 rand() 获取随机数 floor() 保存整数部分
delimiter $
create procedure rand_number()
begin
declare rand_number int default 0;
set rand_number := floor(5*rand());
case rand_number
when 1 then select "开出的是1" as result;
when 2 then select "开出的是2" as result;
when 3 then select "开出的是3" as result;
when 4 then select "开出的是4" as result;
when 5 then select "开出的是5" as result; // when 后面也可以是判断条件 例如: rand_number > 1
else select concat("开出的是:",rand_number); //要有else 否则会报错:ERROR 1339 (20000): Case not found for CASE statement
end case;
end$
delimiter ;
repeat的例子:
delimiter $
create procedure sum_100()
begin
declare sumary int default 0;
declare num int default 1;
repeat
set sumary := sumary + num;
set num := num + 1;
until num > 100 end repeat;
select sumary as result;
end$
delimiter ;
5、游标的概念
cursor 游标,游标的标志
1条sql对应多个结果集的资源,取出资源的接口/句柄,就是游标
沿着游标,可以一次取出1行
declare 声明:declare 游标名 cursor for select select_statement;
open 打开:open 游标名
fetch 取值:fetch 游标名 into var1,var2 [...]
close 关闭:close 游标名
例子:
delimiter $
create procedure study_cursor()
begin
declare row_gid int default 0;
declare row_name varchar(10) ;
declare row_num int default 0;
declare key int default 0;
declare good_cursor cursor for select gid,name,num from good;
open good_cursor;
fetch good_cursor into row_gid,row_name,row_num;
select concat(row_name,row_num,row_gid) as row;
close good_cursor;
end$
delimiter ;
注意:
1、定义的游标变量和游标中使用的变量,必须定义在变量之后 否则会有:ERROR 1337 (42000): Variable or condition declaration after cursor or handler declaration
2、游标赋值使用的变量 不能和sql语句中的字段同名
使用游标循环取数据:
delimiter $
create procedure study_cursor()
begin
declare row_gid int default 0;
declare row_name varchar(10) ;
declare row_num int default 0;
declare row_key int default 0;
declare good_cursor cursor for select gid,name,num from good;
declare continue handler for not found set row_key := 1; // 在进入循环前赋值 判断游标中是否有数据 如果没有数据就触发handler
open good_cursor;
fetch good_cursor into row_gid,row_name,row_num;
while row_key = 0 do
select row_name,row_num,row_gid;
fetch good_cursor into row_gid,row_name,row_num;
end while;
close good_cursor;
end$
delimiter ;
continue 和 exit 的区别:declare continue/exit handler for not found set row_key := 1;使用continue声明的handler,在游标获取数据找不到数据时,会跳过当前的语句向下执行
使用exit声明的handler,在游标获取数据找不到数据时,后面的语句不再执行