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
