Insert data in a while loop : Insert « Procedure Function « MySQL Tutorial





mysql>
mysql>
mysql> delimiter $$
mysql>
mysql> CREATE PROCEDURE myFunction()
    -> BEGIN
    ->         DECLARE i INT DEFAULT 1;
    ->
    ->         CREATE TEMPORARY TABLE ascii_chart
    ->             (ascii_code int, ascii_char CHAR(1));
    ->
    ->         WHILE (i<=128) DO
    ->                INSERT INTO ascii_chart VALUES(i,CHAR(i));
    ->                SET i=i+1;
    ->         END WHILE;
    ->
    ->         select * from ascii_chart;
    ->
    ->         drop table ascii_chart;
    ->
    -> END$$
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> delimiter ;
mysql>
mysql> call myFunction();
+------------+------------+
| ascii_code | ascii_char |
+------------+------------+
|         38 | &          |
|         39 | '          |
|         40 | (          |
|         41 | )          |
|         42 | *          |
|         43 | +          |
|         44 | ,          |
|         45 | -          |
|         46 | .          |
|         47 | /          |
|         48 | 0          |
|         49 | 1          |
|         50 | 2          |
|         51 | 3          |
|         52 | 4          |
|         53 | 5          |
|         54 | 6          |
|         55 | 7          |
|         56 | 8          |
|         57 | 9          |
|         58 | :          |
|         59 | ;          |
|         60 | <          |
|         61 | =          |
|         62 | >          |
|         63 | ?          |
|         64 | @          |
|         65 | A          |
|         66 | B          |
|         67 | C          |
|         68 | D          |
|         69 | E          |
|         70 | F          |
|         71 | G          |
|         72 | H          |
|         73 | I          |
|         74 | J          |
|         75 | K          |
|         76 | L          |
|         77 | M          |
|         78 | N          |
|         79 | O          |
|         80 | P          |
|         81 | Q          |
|         82 | R          |
|         83 | S          |
|         84 | T          |
|         85 | U          |
|         86 | V          |
|         87 | W          |
|         88 | X          |
|         89 | Y          |
|         90 | Z          |
|         91 | [          |
|         92 | \          |
|         93 | ]          |
|         94 | ^          |
|         95 | _          |
|         96 | `          |
|         97 | a          |
|         98 | b          |
|         99 | c          |
|        100 | d          |
|        101 | e          |
|        102 | f          |
|        103 | g          |
|        104 | h          |
|        105 | i          |
|        106 | j          |
|        107 | k          |
|        108 | l          |
|        109 | m          |
|        110 | n          |
|        111 | o          |
|        112 | p          |
|        113 | q          |
|        114 | r          |
|        115 | s          |
|        116 | t          |
|        117 | u          |
|        118 | v          |
|        119 | w          |
|        120 | x          |
|        121 | y          |
|        122 | z          |
|        123 | {          |
|        124 | |          |
|        125 | }          |
|        126 | ~          |
|        127 |           |
|        128 |           |
+------------+------------+
128 rows in set (0.02 sec)

Query OK, 0 rows affected (0.67 sec)

mysql>
mysql> drop procedure myFunction;
Query OK, 0 rows affected (0.00 sec)










11.38.Insert
11.38.1.Example of an INSERT using a procedure variable
11.38.2.Insert data into a table in a procedure
11.38.3.Insert data in a while loop