mysql存储过程读书笔记2(control and conditional logic) 1. if create procedure example3(stu_id int) begin ? declare stu_name varchar(30); ? select name into stu_name from student where student_id = stu_id; if stu_name is not null
mysql存储过程读书笔记2(control and conditional logic)
1. if
create procedure example3(stu_id int)
begin
? declare stu_name varchar(30);
?
select name into stu_name from student where student_id = stu_id;
if stu_name is not null then
? ?select stu_name;
end if;
end;
?
2. if ... else....
create procedure example4(stu_id int)
begin
declare stu_name varchar(30);
?
? select name into stu_name from student where student_id = stu_id;
?if stu_name is not null then
? select stu_name;
?else
? ?select 'student is not exist';
?end if;
end;
?
3. if ... elseif ... else ... end;
drop procedure if exists discounted_price;
create procedure discounted_price(normal_price numeric(8,2), out discount_price numeric(8,2))
begin
? if (normal_price) > 500 then
? set discount_price = normal_price * 0.8;
? elseif (normal_price > 400) then
? set discount_price = normal_price * 0.9;
else
set discount_price = normal_price;
end if;
end
?
4. loop
?
drop procedure if exists simple_loop;
create procedure simple_loop()
begin
?declare counter int;
?set counter = 0;
?
?my_simple_loop: loop
if counter = 10 then
?leave my_simple_loop;
end if;
set counter = counter + 1;
?end loop my_simple_loop;
?
?select ' now counter is 10';
end