MySQL loops the data into the table

Today, I studied MySQL and tried to insert 1000 rows of data into a standard in an experiment similar to that in Oracle.

beginfor i in 1..1000 loopinsert ..end loop;end;/

But anonymous blocks are not supported in MySQL

Baidu a bit, the method is as follows:

Set delimiter first

Delimiter: Tells the interpreter if the command is over, and whether MySQL can execute the
By default, delimiter is '; ' But when we write procedure, if it is the default setting, then one encounters '; ', MySQL is going to execute, which is what we don't want to see

So we manually set delimiter to//

//intset num=1while1000dointo per2(name) values(concat("fan"set num=num+1while//

where Concat ("fan", num), is equivalent to Oracle in fan| | I stitching effect, but MySQL does not support such stitching

Then we're going to call this procedure before we insert the data.

([email protected]) [fandb]> call per2();    -> //Query OK, 1 row affected (0.39 sec)

Because I did not change the delimiter ' back, so I entered '; ' After not executed, also need//

View procedure in the library

([email protected]) [fandb]> select name from mysql.proc where db=‘fandb‘ and type=‘procedure‘;+------+| name |+------+| per2 |+------+1 row in set (0.00 sec)

View Create code

([email protected]) [fandb]> Show CREATE PROCEDURE per2\g***************************1.Row *************************** Procedure:per2 sql_mode:strict_trans_tables,no_engine_substitution Create procedure:create definer= ' mysql ' @ ' localhost ' Procedure ' per2 ' () beginDeclarenum int;Setnum=1; whileNum < +  DoInsert intoPER2 (name) VALUES (concat ("Fan", num));Setnum=num+1;End  while;EndCharacter_set_client:utf8collation_connection:utf8_general_ci Database Collation:utf8_general_ci1Rowinch Set(0.00Sec

Other ways to use

While End While:

MySQL > DELIMITER//MySQL >CREATE  PROCEDURE proc4(), begin, declare var int ;-Set var=0;-> while var<6  DoInsert intoT VALUES (var);->Set var=var+1;->End  while;->End;->//

? While condition do– loop body Endwhile

? Repeat end repeat:
It checks the results after performing the operation, while the while is the import line check before execution.

mysql > DELIMITER //mysql > CREATE PROCEDURE proc5 ()-> begin-> declare v int;-> set v=0;-> repeat-> insert into t values(v);-> set v=v+1;-> until v>=5-> end repeat;-> end;-> //

? repeat– cyclic body until cyclic condition endrepeat;

Loop endloop:
Loop loops do not require initial conditions, which are similar to while loops and do not require an end bar as in the repeat loop
Pieces, the meaning of leave astonished sentence is to leave the loop.

MySQL > DELIMITER//mysql > CREATE PROCEDURE proc6 ()-> begin -> Declare v int;-> Set v=0;-> loop_lable: LOOP -> INSERT into T values (v);-> Set v=v+1;-> if v >=5   Then-> Leave loop_lable;-> End if;-> end loop;-> end;-> // 

LABLES Marking:

The label can be used before begin repeat while the ring astonished sentence, astonished sentence designator just can be used in front of the legal astonished sentence.
You can jump out of the loop so that the Run command reaches the final step of the compound astonished sentence.

MySQL loops the data into the table