Mysql存储过程知识，案例
时间：2022-03-10 17:25
Mysql存储过程知识，案例：
?
create procedure delete_setting(in
p_settingid integer)
begin
delete
from setting
where settingid=p_settingid;
end
select `name` from mysql.proc where db = ‘your_db_name‘
and `type` = ‘PROCEDURE‘
show procedure status;
show create procedure proc_name;
show create function
func_name;
局部变量
create procedure test3(out number1 integer)
begin
declare number2 integer default
100;
set number1=number2;
end
call test(@number)
select @number#
默认值为标量子查询
create procedure test2(out ta integer)
begin
declare tt integer default
(select count(*) from `setting`);
set ta=tt;
end
流程控制
IF
create procedure getMax(in
p1 integer,in
p2 integer,out p3 integer)
begin
if
p1>p2 then
set p3 = p1;
elseif p1<p2 then
set p3 = p2;
else
set p3=0;
end if;
end
fibonnaci
create procedure fibonnaci(inout n1 integer,inout n2 integer,inout n3 integer)
begin
set n3=n1+n2;
if
n3>1000 then
set n3 = n3-1000;
end if;
set n1=n2;
set n2=n3;
end
create procedure largest(out t char(30))
begin
if
(select count(*) from product)>(select count(*) from productcategory) then
set t="product";
elseif (select count(*) from product)<(select count(*) from productcategory) then
set t="productcategory";
else
set t = "equal";
end if;
end
create procedure small_exit(out p1 integer,out p2 integer)
begin
set p1 = 1;
set p2 = 2;
block1:begin
leave block1;
set p2 = 3;
end;
set p1=4;
end
WHILE 计算两个相差的年月日
create procedure age(
in
start_date date,
in
end_date date,
out years integer,
out monthes integer,
out days integer
)
begin
declare next_date,previous_date date;
set years = 0;
set previous_date=start_date;
set next_date =start_date+interval 1 year;
while
next_date<end_date do
set years = years+1;
set previous_date = next_date;
set next_date = next_date+ interval 1 year;
end while;
set monthes = 0;
set next_date = previous_date + interval 1 month;
while
next_date<end_date do
set monthes = monthes +1;
set previous_date=next_date;
set next_date = previous_date+interval 1 month;
end while;
end
————————————————————————
调用存储过程
建表
create table players_with_parents
(playerno integer not null
primary key,
father_playerno integer,
mother_playerno integer);
alter table players_with_parents add constraint fk_1
foreign key (father_playerno) references player_with_parents (playerno);
alter table players_with_parents add
foreign key (mother_playerno) references player_with_parents(playerno);
增加外键约束；
insert into players_with_parents values(9,null,null),(8,null,null),(7,null,null),(6,null,null),(5,null,null)
,(4,8,9),(3,6,7),(2,4,5),(1,2,3)
create procedure total_number_of_parents(in_playerno integer,inout number integer)
begin
declare v_father,v_mother integer;
set v_father =
(select father_playerno from players_with_parents where playerno=in_playerno);
set v_mother =
(select mother_playerno from players_with_parents where playerno=in_playerno);
if
v_father is not null
then
call total_number_of_parents(v_father,number);
set number = number+1;
end if;
if
v_mother is not null
then
call total_number_of_parents(v_mother,number);
set number = number+1;
end if;
end
set max_sp_recursion_depth=100;
select into
create procedure total_setting(out p1 integer)
begin
select count(*) into p1 from `setting`;
end
create procedure get_setting(out v_name varchar(60),out v_value varchar(60))
begin
select `name`,`value` into v_name,v_value from setting limit 0,1;
end
create procedure showErr(out p integer)
begin
set p=1;
insert into `setting` (`settingid`,`name`)values(12,‘vname‘);
set p=2;
end
create procedure showErr(out p integer,out error integer)
begin
declare continue
handler for
sqlstat ‘23000‘
set error = ‘200000‘;
declare continue
handler for
sqlstat ‘23001‘;
set error = ‘200001‘;
set error = ‘000000‘;
set p=1;
insert into `setting` (`settingid`,`name`)values(12,‘vname‘);
set p=2;
end
declare continue
handler for
1062 set error=‘23000‘;
declare continue
handler for
1136 set error=‘21S01‘;
01 SQLWARING
02 NOT FOUND
SQLEXCEPTION 01,02以外的
create procedure addDate(out error char(5))
begin
declare continue
handler for
sqlstate ‘23000‘
set error=‘23000‘;
declare continue
handler for
sqlstate ‘21S01‘
set error=‘21S01‘;
set error=‘00000‘;
insert into `setting` values(15,‘test‘,‘testValue‘,10);
end
declare non_unique condition for
sqlstate ‘23000‘;
ddeclare continue
handler for
non_unique set error=‘23000‘;
同一块中，相同的错误，不能有两个或多个处理程序；
带有游标的存储过程
declare c_players cursor for
select playerno from players;
create procedure number_of_players(out number integer)
begin
declare a_playerno integer;
declare found boolean default
true;
declare c_players cursor for
select playerno from players;
declare continue
handler for
not found set found=false;
set number=0;
open c_players;
fetch c_players into a_playerno;
while
found do
set number =number+1;
fetch c_players into a_playerno;
end while
close c_players;
end
create procedure number_of_setting(out number integer)
begin
declare a_settingid integer;
declare found boolean default
true;
declare c_settingids cursor for
select settingid from setting;
declare continue
handler for
not found set found=false;
set number=0;
open c_settingids;
fetch c_settingids into a_settingid;
while
found do
set number=number+1;
fetch c_settingids into a_settingid;
end while;
close c_settingids;
end
create procedure delete_older_than_30()
begin
declare v_age,v_playerno,v_years,v_months,v_days integer;
declare v_birth_date date;
declare found boolean default
true;
declare c_players cursor for
select playerno,birth_date from players;
declare continue
handler for
not found set found=false;
open c_players;
fetch c_players into v_playerno,v_birth_date;
while
found do
call age(v_birth_date,now(),v_years,v_months,v_days);
if
v_years>30 then
delete
from penalties where playerno=v_playerno;
end if;
fetch c_players into v_playno,v_birth_date;
end while;
close c_players;
end
commit,rollback,start transaction;
create function
test(t1 integer,t2 integer) returns integer
begin
return
t1+t2;
end |
Mysql存储过程知识，案例,布布扣,bubuko.com
