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