MySql 存储过程示例

MySql 存储过程示例

DROP PROCEDURE IF EXISTS auto_full_percent;

-- 定时计算用户的资料完整度
CREATE PROCEDURE auto_full_percent()
BEGIN
    DECLARE done boolean DEFAULT 0;
    DECLARE full_percent int DEFAULT 0; -- 声明一个局部变量
    DECLARE var_uuid int; -- 声明一个局部变量
    DECLARE var_phone VARCHAR(30); -- 声明一个局部变量
    DECLARE var_email VARCHAR(30); -- 声明一个局部变量
    DECLARE accontCursor CURSOR        -- 声明一个游标
   FOR
    SELECT uuid,phone,email
   FROM u_user_account;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
   OPEN accontCursor;          -- 打开游标
    -- 遍历所有的行
   REPEAT
           FETCH accontCursor INTO var_uuid,var_phone,var_email;  -- 获取查询变量

     IF (var_phone is not null)
     THEN
      SET full_percent = full_percent + 5;     
     END IF;
     
     IF (var_email is not null)
      THEN
      SET full_percent = full_percent + 2;
     END IF;

   SELECT var_uuid,var_phone,var_email, full_percent FROM dual;
    SET full_percent = 0;
   UNTIL done END REPEAT;           -- 结束循环
   CLOSE accontCursor;  -- 关闭游标
END;