mysql 存储过程 if and_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