[TOC] ### 循環插入數據 創建存儲過程 `CREATE PROCEDURE batchInsert() ` ``` DROP PROCEDURE IF EXISTS batchInsert; CREATE PROCEDURE batchInsert() BEGIN DECLARE num int; DECLARE user_id VARCHAR(200); DECLARE user_login VARCHAR(200); DECLARE tnum VARCHAR(200); SET num = 105; WHILE num <= 1000 DO SET tnum = num; SET user_id = CONCAT('569505695',CAST(tnum AS CHAR(20))); SET user_login = CONCAT('user175',CAST(tnum AS CHAR(20))); insert into hs_user (`USER_ID`, `USER_LOGIN`, `USER_NAME`, `USER_STATUS`, `USER_PWD`, `USER_PWD_TYPE`) VALUES (user_id, user_login, user_login, 1, 'c4ca4238a0b923820dcc509a6f75849b', 1); insert into hs_user_tree (`TREE_ID`,`DEPT_ID`, `USER_ID`, `USER_NAME`, `USER_ORDER_ID`) VALUES (UUID(), '5695102', user_id, user_login, '1000'); SET num = num + 1; END WHILE; END; ``` 調用 存儲過程 `CALL` ``` CALL batchInsert(); ``` ### 定義變量關鍵詞`DECLARE`: ``` DECLARE num int; ``` ### 循環關鍵詞`WHILE num <= 1000 DO` ``` WHILE num <= 1000 DO END WHILE; ``` ### 拼接字符串關鍵詞`CONCAT ` ``` CONCAT('569505695',CAST(tnum AS CHAR(20))); ``` ### 轉化整形為字符串關鍵詞`CAST`： ``` CAST(tnum AS CHAR(20))); ```