MySQL 存储过程和函数

• 判断某年份是否为闰年

  -- 判断年份是否为闰年
  CREATE PROCEDURE is_runyear(n int)
  BEGIN
  IF n <= 0  -- 参数不合法
  THEN SELECT '该年份不合法'　AS s;
  ELSE -- 参数合法
  IF (n%4=0&&n%100<>0||n%400=0) -- 判断闰年
  THEN SELECT '该年份为闰年' AS s;
  ELSE  -- 不是闰年
  SELECT '该年份为平年' AS s;
  END IF;
  END IF;
  END 

结果:

call IS_RUNYEAR(2000); -- 闰年
call IS_RUNYEAR(2100); -- 平年
call IS_RUNYEAR(-1); -- 不合法

• 阶乘函数

  -- 创建阶乘函数
  CREATE FUNCTION Factorial(n INT) RETURNS INT
  BEGIN
  DECLARE i int default 1;
  DECLARE result INT DEFAULT 1;
  WHILE i<= n do 
  SET result = result * i;
  SET i = i+1;
  END WHILE;
  RETURN result;
  END

结果:

select Factorial(5); -- 120

•  比较两个数并返回最小值

  -- 比较两个数返回最小值
  CREATE FUNCTION is_min (a INT, b INT) RETURNS INT
  BEGIN
  DECLARE s INT DEFAULT 0;
  IF a >= b then 
  SET s = b;
  ELSE
  SET s = a;
  END IF ;
  RETURN s;
  END

  结果: 

• SELECT is_min(5,6) -- 5

   

  1、 -- 求1!+2!+3!+4!+...n!
   

  -- 求1!+2!+3!+4!+...n!
  CREATE PROCEDURE sum_factorial(n int)
  BEGIN
  declare i int default 1;
  declare s int default 0;
  WHILE i<= n do 
  set s = s + Factorial(i);
  set i = i+1;
  end while;
  select s;
  END

  2、 阶乘函数
  

  CREATE FUNCTION Factorial(n int) RETURNS INT
  BEGIN
  DECLARE i int default 1;
  DECLARE result INT DEFAULT 1;
  WHILE i<= n do 
  SET result = result * i;
  SET i = i+1;
  END WHILE;
  RETURN result;
  END

  3、 循环1-n的和
  

  CREATE procedure sum_n1(n int ) returns int
  begin 
  declare i int default 1;
  declare s int default 0;
  while i <= n do
  set s = s + i;
  set i = i + 1;
  end while;
  select s;
  end

  CREATE procedure sum_n2(n int) returns int
  begin 
  declare i int default 1;
  declare s int default 0;
  repeat 
  set s = s + i;
  set i = i + 1;
  until i > n 
  end repeat;
  select s;
  END

  CREATE procedure sum_n3(n int ) returns int
  begin 
  declare i int default 1;
  declare s int default 0;
  aa:loop
  if i > n then 
  leave aa;
  end if;
  set s = s+i;
  set i = i+1;
  end loop;
  select s;
  end

  4.求表中身份证号最后一位是偶数的成员
  

  CREATE PROCEDURE select_lastcard_even()
  BEGIN
  select * from students where right(cardid,1)%2 = 0 AND
  lcase(right(cardid,1)) <> 'x';
  END

   5.随机15位数字

  BEGIN
  declare stemp varchar(20);
  declare stempcounts integer;
  set stemp = ROUND(ROUND(RAND(),counts)*pow(10,counts));
  if CHAR_LENGTH(stemp) < counts then 
  set stempcounts = counts - CHAR_LENGTH(stemp);
  set stemp = concat(stemp,right(concat(pow(10,stempcounts),''),stempcounts));
  end if;
  if CHAR_LENGTH(stemp) > counts then 
  set stemp = right(stemp,counts);
  end if;
  return stemp;
  end

   

  6.生产随机32位字符

• select REPLACE(UUID(),'-','');

   7.随机6位字母

• select concat(
  char(round((rand())*25)+97),
  char(round((rand())*25)+65),
  char(round((rand())*25)+65),
  char(round((rand())*25)+65),
  char(round((rand())*25)+65),
  char(round((rand())*25)+97)
  )

   8.赛事活动(开赛、进行中、完赛)

  begin 
    -- 定义变量
    declare id int;
    declare s datetime;
    declare e datetime;
  declare done int;
      -- 创建游标
      declare updatestate cursor for select mid,starttime,endtime from model where type>=10;
      declare continue handler for not found set done = 1;
      -- 打开游标
      open updatestate;
      aa:LOOP
          fetch updatestate into id,s,e;
      if done then 
              leave aa;
          end if;
              if current_timestamp()<s then 
                  update model set state  = 0 where mid = id; -- 赛事未开赛
              elseif CURRENT_TIMESTAMP()>=s && current_timestamp()<e then 
                  update model set state = 1 where mid = id; -- 赛事进行中
              else 
                  update model set state = 2 where mid = id; -- 赛事已结束
              end if;
      end loop aa;
    CLOSE updatestate;
  END

   9.已知生日求年龄

• BEGIN
  DECLARE age INT(10);
  IF birthday IS NULL THEN 
  RETURN '';
  END IF;
  SET age = YEAR(CURDATE()) - YEAR(birthday) - 1;
  IF MONTH(CURDATE()) = MONTH(birthday) THEN
  IF DAY(CURDATE()) > DAY(birthday) THEN 
  SET age = age + 1;
  END IF;
  ELSEIF MONTH(CURDATE()) > MONTH(birthday) THEN
  SET age = age +1;
  END IF;
  RETURN age;
  END