利用存储过程录入连续的素数序列

表初始行：

序号	值	Note
1	2
2	3
3	5
4	7

存储过程：

DELIMITER $$

USE `db2020`$$

DROP PROCEDURE IF EXISTS `ins_prime`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `ins_prime`(IN allSize INT)
BEGIN
DECLARE i INT DEFAULT 4;
DECLARE j INT DEFAULT 2;
DECLARE num INT DEFAULT 11;
DECLARE d INT DEFAULT 1;
DECLARE p INT DEFAULT 3;
DECLARE sq INT DEFAULT 1;
WHILE i<allSize DO
	SET d=1;
	SET sq = SQRT(num);
	SET j = 2;
	WHILE(j<=sq AND d=1) DO
		SELECT val INTO p FROM prime WHERE id = j;
		IF ( MOD(num,p )=0 ) THEN
			SET d=0;
		END IF;
		SET j = j+1;
	END WHILE;	
	IF (d=1) THEN	
		SET i = i + 1;
		INSERT INTO prime (id,val) VALUES (i,num);
	END IF;
	SET num = num +2;
END WHILE;
    END$$

DELIMITER ;

CALL ins_prime(10303);

结果摘录：

序号	值	Note
5	11
6	13
7	17
8	19
9	23
10	29
11	31
12	37
13	41
14	43
15	47
16	53
17	59
18	61
19	67
20	71
21	73
22	79
23	83
24	89
25	97
26	101
27	103
28	107
29	109
30	113
31	127
32	131
33	137
34	139
35	149
36	151
37	157
38	163
39	167
40	173
41	179
42	181
43	191
44	193
45	197
46	199
47	211
48	223
49	227
50	229
51	233
52	239
53	241
54	251