3.存储过程的创建和与使用

存储过程的参数有三种：

• IN：输入参数，也是默认模式。表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回。
• OUT：输出参数。该值可在存储过程内部被改变，并可返回。
• INOUT：输入输出参数。调用时指定，并且可被改变和返回。

use library;
drop procedure if exists P_LoanBook;
delimiter //
create procedure P_LoanBook(vloanno varchar(16),vbookno char(9),out vflag tinyint)
begin
-- 调用其他存储过程
call P_IsLoan(vloanno,vbookno,vflag);
-- if语句
if(vflag=0) then
    insert into Loan value(vbookno,vloanno,now());
    update Books set bstatus = 1 where bookNo = vbookno; 
elseif(vflag=1) then
         update Books set bstatus = 3 where bookNo = vbookno;
end if;
end//
delimiter ;

drop procedure if exists `GetCustomerLevel`;
delimiter //
create procedure GetCustomerLevel(in customerNumber int(11),out p_customerLevel varchar(10))
Begin
  declare money int(10) default 0;
  select `creditlimit` into money from `customers`
  where customerNumber = customers.`customerNumber`;
  -- 方法1: 使用if
  if(money>10000) then
       set p_customerLevel = 'PLATINUM';
  elseif(money>=5000&&money<=10000) then
           set p_customerLevel = 'GOLD';
  elseif(money<5000) then
           set p_customerLevel = 'SILVER';
  END IF;
  -- 方法2: 使用case
  case
      when (money > 10000) then 
          set p_customerLevel = 'PLATINUM';
      when (money>=5000&&money<=10000) then
          set p_customerLevel = 'GOLD';
      when (money<5000) then
          set p_customerLevel = 'SILVER';
  end case;
  select customerNumber,p_customerLevel from customers
  where customerNumber = customers.`customerNumber`;
End //
delimiter ;