mysql存储过程的删除_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)