1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
DROP PROCEDURE IF EXISTS generateUserInfo;
CREATE PROCEDURE generateUserInfo(in n int)
BEGIN
	DECLARE k INT;
	declare cityVar VARCHAR(100) DEFAULT '深圳';
	declare genderVar char(1) default '女'; -- 变量声明必须要在最前面
	SET k = 2;
	WHILE k <= n DO
		CASE k % 2 
		WHEN 0 THEN
			SET genderVar = '女'; -- 必须有分号
		ELSE 
			SET genderVar = '男';
		END CASE;
		CASE
				k % 2 
		WHEN 0 THEN
				SET cityVar = '深圳';
		WHEN 1 THEN
			SET cityVar = '广州';
		WHEN 2 THEN
				SET cityVar = '武汉';
		WHEN 3 THEN
				SET cityVar = '北京';
		WHEN 4 THEN
				SET cityVar = '上海';
		WHEN 5 THEN
				SET cityVar = '孝感';
		ELSE 
				SET cityVar = '中山';
		END case; -- 必须要有case
		INSERT INTO user_info_t ( id, user_id, user_ext_id, nickname, email, gender, birthday, score, reg_time, last_login_time, vip, city )
		VALUES
			(k,k,k,concat( 'user', k ),'yc@qq.com',genderVar,
			'2020-10-03 00:00:00',0,'2017-10-01 00:00:00','2017-10-24 21:30:36',1,cityVar);
		SET k = k + 1;
	END WHILE;
END;

DELETE  FROM 	user_info_t  WHERE	id >= 2;
-- 函数使用select调用, 过程使用call调用
CALL generateUserInfo(100);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
DELETE  FROM 	user_t  WHERE	id >= 2;
-- pg可以支持sql片段的运行
DO $$
DECLARE k integer := 2;
begin 
	WHILE k <= 100 
	loop
				INSERT INTO user_t ( id, NAME, PASSWORD, create_time, update_tinme, create_by, update_by )
		VALUES
			( k, concat( 'user', k ), NULL, now(), NULL, 'System Admin', NULL );
		k = k + 1
  end loop
END $$;