mysql-存储过程
时间：2021-07-01 10:21:17
帮助过：10人阅读
、数据存储：把多个sql语句封装成一个方法
delimiter // 修改结束符
create procedure proc()
begin
select * from student;
end
//
call 调用方法
变量的定义：
declare 变量名 type
default 默认值；
修改变量的值
set 变量名
= 值 或
Drop procedure proc2;
delimiter //
Create procedure proc2()
Begin
Declare var1
varchar(
10)
default null;
Declare var2
int default 0;
select name ,age
into var1, var2
from student
where id
=1;
Result consisted of more than one row(这种错误出现的原因是没指定id 的值，结果多余变量的个数)
Select var1;
Select var2;
End //
Delimiter ;
delimiter //
drop procedure if exists proc2;
Create procedure proc2()
Begin
Declare var1
varchar(
10)
default null;
declare var2
int default 0;
select name ,age
into var1, var2
from student
where id
=1;
Select var1
as 姓名, var2
as 年龄;
End //
Delimiter ;
1、存储过程：
Delimiter // 修改结束符
Drop procedure if exists pro1; 判断，如果存在pro1 则删除
Create procedure pro1() 创建
procedure 存储过程
Begin
End//
Delimiter ;
Call pro1;
2、游标：
语法：
(
声明游标
declare cursor_name
cursor for select_statement;
打开游标（在使用游标之前）
open cursor_name;
获取游标中的数据 into 变量
fetch cursor_name
into var_name1,var_name2,...;
关闭游标（在使用游标之后）
close cursor_name;
)
delimiter //
drop procedure if exists pro;
create procedure pro()
begin
declare var varchar(
20)
default null;
declare cursor_name
cursor for select name
from student
where id
=1;
open cursor_name;
fetch cursor_name
into var;
close cursor_name;
select var as 姓名;
end //
delimiter ;
call pro;
procedure 程序、
declare 声明、
delimiter //
Drop procedure if exists pro
//
create procedure pro()
begin
declare var1
char(
10)
default null;
declare var2
int default 0;
declare cursor1
cursor for select name ,age
from student
where id
=1;
open cursor1;
fetch cursor1
into var1, var2;
close cursor1;
select var1
as ‘姓名’, var2
as ‘年龄’;
end //
delimiter ;
call pro;
3、存储过程中的if判断语句
语法：
(
if .....
then ......;
else if ......
then ......;
else .......;
end if;
)
delimiter //
drop procedure if exists pro
//
Create procedure pro()
begin
declare var int;
if var is null then select ‘var is null‘ as 结果;
Else select var as 结果;
end if;
Set var=10;
If var is null then select ‘var is null‘ as 结果;
Else select var as 结果;
End if;
End//
Delimiter ;
Call pro;
4、存储过程中的case判断语句
语法：
(
case 要判断的变量
when ....
then .... ;
when ....
then .... ;
....;
else ....;
end case;
)
delimiter //
drop procedure if exists pro;
create procedure pro()
begin
declare var int default 0;
set var=5;
case var
when 1 then select * from student
where id
=var;
when 2 then select * from student
where id
=var;
when 3 then select * from student
where id
=var;
else select ‘no such case‘ as 结果;
end case;
end//
delimiter ;
call pro;
5、存储过程中的loop循环语句
语法：
(
loop sql_statement;
if ...
then leave
/iterate;
end if;
end loop;
)
delimiter //
drop procedure if exists pro
//
create procedure pro()
begin
declare var int default 0;
myloop:loop insert student
values(
null,
‘Rose‘,
var,
‘女‘);
if var >=100 then leave myloop;
end if;
set var=var+1;
end loop;
end//
delimiter ;
call pro;
注意：在sql语句中没有 “==”，直接用“
=”来作为判断等于的符号。
6、存储过程中的repeat循环语句
语法：
(
repeat_label:repeat sql_statement;
until ... end repeat repeat_label;
)
delimiter //
select * from student
//
drop procedure if exists pro;
create procedure pro()
begin
declare var int default 1;
my_repeat:repeat insert into student
values(
null,
‘Rose‘,
var,
‘女‘);
set var=var+1;
until var>100 end repeat my_repeat;
end//
delimiter ;
call pro;
select * from student;
7、存储过程中的while循环
语法：
(
while_label：while ...(循环条件)
do sql_statement;
end while while_label;
)
delimiter //
select * from student
//
drop procedure if exists pro
//
create procedure pro()
begin
declare var int default 719;
my_while:while var<=818
do delete from student
where id
= var;
set var=var+1;
end while my_while;
end//
delimiter ;
call pro;
delimiter //
drop procedure if exists pro
//
create procedure pro()
begin
declare var int default 1;
my_while:while var<=100
do insert into student
values(
null,
‘Rose‘,
var,
‘女‘);
set var=var+1;
end while my_while;
end//
delimiter ;
call pro;
8、查看存储过程
语法：
(
show {procedure/function} status
[like ‘pattern‘];
show create procedure pro_name;
)
show procedure status;
show create procedure pro;
9、修改存储过程
10、删除存储过程
语法：
(
drop {
procedure/function}
if exists pro_name;
)
drop procedure if exists pro;
11、存储过程出错处理
定义错误情况
declare condition_name condition
for condition_type;
mysql-存储过程
标签：sel label 默认值 delete int stat blog 循环 ...
