判断概率区间1%-100%是否发生的存储过程

一、实现判断概率区间10%-100%是否发生，传入参数successRate设置概率（传入50表示50%），

返回参数resultStr中yes表示在该概率中，no不在概率中

代码：

DELIMITER //

DROP PROCEDURE IF EXISTS successRateProc;
CREATE PROCEDURE successRateProc(IN successRate VARCHAR(10),OUT resultStr VARCHAR(10))
BEGIN
DECLARE resultNum INT;
DECLARE fromNum INT DEFAULT 1;
DECLARE toNum INT DEFAULT 10;
SET resultNum = CONVERT((SELECT FLOOR(fromNum+Rand()*(toNum-fromNum+1))),UNSIGNED);
CASE successRate
WHEN '10%' THEN
IF resultNum = 1 THEN
SET resultStr='YES';
ELSE
SET resultStr='NO';
END IF;
WHEN '20%' THEN
IF (resultNum = 1) OR (resultNum = 2) THEN
SET resultStr='YES';
ELSE
SET resultStr='NO';
END IF;
WHEN '30%' THEN
IF (resultNum = 1) OR (resultNum = 2) OR (resultNum = 3) THEN
SET resultStr='YES';
ELSE
SET resultStr='NO';
END IF;
WHEN '40%' THEN
IF (resultNum = 1) OR (resultNum = 2) OR (resultNum = 3) OR (resultNum = 4) THEN
SET resultStr='YES';
ELSE
SET resultStr='NO';
END IF;
WHEN '50%' THEN
IF (resultNum = 1) OR (resultNum = 2) OR (resultNum = 3) OR (resultNum = 4) OR (resultNum = 5) THEN
SET resultStr='YES';
ELSE
SET resultStr='NO';
END IF;
WHEN '60%' THEN
IF (resultNum != 7) AND (resultNum != 8) AND (resultNum != 9) AND (resultNum != 10) THEN
SET resultStr='YES';
ELSE
SET resultStr='NO';
END IF;
WHEN '70%' THEN
IF (resultNum != 8) AND (resultNum != 9) AND (resultNum != 10) THEN
SET resultStr='YES';
ELSE
SET resultStr='NO';
END IF;
WHEN '80%' THEN
IF (resultNum != 9) AND (resultNum != 10) THEN
SET resultStr='YES';
ELSE
SET resultStr='NO';
END IF;
WHEN '90%' THEN
IF (resultNum != 10) THEN
SET resultStr='YES';
ELSE
SET resultStr='NO';
END IF;
WHEN '100%' THEN
SET resultStr='YES';
END CASE;
END
//

DELIMITER ;

测试：

CALL successRateProc('50%',@resultStr);

SELECT @resultStr;

二、实现判断概率区间1%-100%是否发生，传入参数successRate设置概率（传入50表示50%），

返回参数resultStr中yes表示在该概率中，no不在概率中

代码：

DELIMITER //
DROP PROCEDURE IF EXISTS successRateProc;
CREATE PROCEDURE successRateProc(IN successRate int,OUT resultStr VARCHAR(10))
BEGIN
DECLARE resultNum INT;
DECLARE fromNum INT DEFAULT 1;
DECLARE toNum INT DEFAULT 100;
SET resultNum = CONVERT((SELECT FLOOR(fromNum+Rand()*(toNum-fromNum+1))),UNSIGNED);
IF resultNum <= successRate THEN
SET resultStr='YES';
ELSE
SET resultStr='NO';
END IF;
END
//
DELIMITER ;

测试：

CALL successRateProc('80',@resultStr);

SELECT @resultStr;