mysql实例 存储过程中创建与删除临时表

发布时间:2020-10-16编辑:脚本学堂
本文介绍下,一个在mysql的存储过程中删除临时表的例子,有需要的朋友参考下。

本代码演示:
在mysql的存储过程中创建、删除临时表的方法。

代码:

mysql> delimiter $$
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> delimiter ;
mysql> call myFunction(); //调用存储过程
+------------+------------+
| ascii_code | ascii_char |
+------------+------------+
|         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          |
+------------+------------+
128 rows in set (0.02 sec)

Query OK, 0 rows affected (0.28 sec)

mysql> drop procedure myFunction; //删除存储过程
Query OK, 0 rows affected (0.00 sec)