MySQL存储过程和临时表

MySQL创建存储过程

MySQL中，创建存储过程的基本形式如下：

CREATE PROCEDURE sp_name ([proc_parameter[,...]])  
        [characteristic ...] routine_body

其中，sp_name参数是存储过程的名称；proc_parameter表示存储过程的参数列表； characteristic参数指定存储过程的特性；routine_body参数是SQL代码的内容，可以用BEGIN…END来标志SQL代码的开始和结束。

proc_parameter

proc_parameter中的每个参数由3部分组成。这3部分分别是输入输出类型、参数名称和参数类型。其形式如下：

[ IN | OUT | INOUT ] param_name type

其中，IN表示输入参数；OUT表示输出参数； INOUT表示既可以是输入，也可以是输出； param_name参数是存储过程的参数名称；type参数指定存储过程的参数类型，该类型可以是MySQL数据库的任意数据类型。

characteristic

characteristic参数有多个取值。其取值说明如下：

LANGUAGE SQL：说明routine_body部分是由SQL语言的语句组成，这也是数据库系统默认的语言。
[NOT] DETERMINISTIC：指明存储过程的执行结果是否是确定的。DETERMINISTIC表示结果是确定的。每次执行存储过程时，相同的输入会得到相同的输出。NOT DETERMINISTIC表示结果是非确定的，相同的输入可能得到不同的输出。默认情况下，结果是非确定的。
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }：指明子程序使用SQL语句的限制。CONTAINS SQL表示子程序包含SQL语句，但不包含读或写数据的语句；NO SQL表示子程序中不包含SQL语句；READS SQL DATA表示子程序中包含读数据的语句；MODIFIES SQL DATA表示子程序中包含写数据的语句。默认情况下，系统会指定为CONTAINS SQL。
SQL SECURITY { DEFINER | INVOKER }：指明谁有权限来执行。DEFINER表示只有定义者自己才能够执行；INVOKER表示调用者可以执行。默认情况下，系统指定的权限是DEFINER。
COMMENT 'string'：注释信息。

【示例14-1】下面创建一个名为num_from_employee的存储过程。代码如下：

DROP PROCEDURE IF EXISTS num_from_employee ;

DELIMITER //
CREATE  PROCEDURE  num_from_employee 
(
    IN emp_id INT, 
    OUT count_num INT
)   READS SQL DATA  
BEGIN  

    SELECT  COUNT(*)  INTO  count_num  
    FROM  employee  
    WHERE  d_id=emp_id ;  

END //
DELIMITER ;

说明：MySQL中默认的语句结束符为分号（;）。存储过程中的SQL语句需要分号来结束。为了避免冲突，首先用"DELIMITER //"将MySQL的结束符设置为//。最后再用"DELIMITER ;"来将结束符恢复成分号。这与创建触发器时是一样的。

MySQL创建存储函数

在MySQL中，创建存储函数的基本形式如下：

CREATE FUNCTION sp_name ([func_parameter[,...]])  
        RETURNS type  
        [characteristic ...] routine_body

其中，sp_name参数是存储函数的名称；func_parameter表示存储函数的参数列表；RETURNS type指定返回值的类型；characteristic参数指定存储函数的特性，该参数的取值与存储过程中的取值是一样的；routine_body参数是SQL代码的内容，可以用BEGIN…END来标志SQL代码的开始和结束。

func_parameter

func_parameter可以由多个参数组成，其中每个参数由参数名称和参数类型组成，其形式如下：param_name type

其中，param_name参数是存储函数的参数名称；type参数指定存储函数的参数类型，该类型可以是MySQL数据库的任意数据类型。

【示例14-2】下面创建一个名为name_from_employee的存储函数。代码如下：

DELIMITER //

CREATE  FUNCTION  name_from_employee (emp_id INT )  
　　RETURNS VARCHAR(20)  
BEGIN  
　　RETURN  (SELECT  name  FROM  employee  WHERE  num=emp_id );  
END //

DELIMITER ;

MySQL临时表

首先，临时表只在当前连接可见，当关闭连接时，Mysql会自动删除表并释放所有空间。因此在不同的连接中可以创建同名的临时表，并且操作属于本连接的临时表。

创建临时表的语法与创建表语法类似，不同之处是增加关键字TEMPORARY，如：

CREATE TEMPORARY TABLE 表名 (…. )

临时表使用有一些限制条件：

　　* 临时表在 memory、myisam、merge或者innodb上使用，并且不支持mysql cluster簇)；

show tables语句不会列出临时表，在information_schema中也不存在临时表信息；show create table可以查看临时表；

　　* 不能使用rename来重命名临时表。但是可以alter table rename代替：

　mysql>ALTER TABLE orig_name RENAME new_name;

　　* 可以复制临时表得到一个新的临时表，如：

mysql>create temporary table new_table select * from old_table;

　　* 但在同一个query语句中，相同的临时表只能出现一次。如：

可以使用：mysql> select * from temp_tb;

但不能使用：mysql> select * from temp_tb, temp_tb as t;

错误信息： ERROR 1137 (HY000): Can't reopen table: 'temp_tb'

　　同样相同临时表不能在存储函数中出现多次，如果在一个存储函数里，用不同的别名查找一个临时表多次，或者在这个存储函数里用不同的语句查找，都会出现这个错误。

　　* 但不同的临时表可以出现在同一个query语句中，如临时表temp_tb1, temp_tb2：

Mysql> select * from temp_tb1, temp_tb2;

* 临时表可以手动删除：

DROP TEMPORARY TABLE IF EXISTS temp_tb;

在创建临时表时声明类型为HEAP，则Mysql会在内存中创建该临时表，即内存表：如：

CREATE TEMPORARY TABLE 表名 (。。。。) TYPE = HEAP

因为HEAP表存储在内存中，你对它运行的查询可能比磁盘上的临时表快些。如：

mysql> create temporary table temp_tb type='heap' select * from temptb;

CREATE TEMPORARY TABLE `temp_tb` (

  `id` int(10) unsigned NOT NULL DEFAULT '0',

  `Name` char(20) NOT NULL,

  `Age` tinyint(4) NOT NULL

) ENGINE=MEMORY DEFAULT CHARSET=gbk

临时表和内存表

临时表主要是为了放一些中间大结果集的一些子集，内存表可以放一些经常频繁使用的数据。

* 临时表：表建在内存里，数据在内存里
* 内存表：表建在磁盘里，数据在内存里

        临时表和内存表所使用内存大小可以通过My.cnf中的max_heap_table_size、tmp_table_size指定：
              [mysqld]
              max_heap_table_size=1024M   #内存表容量
              tmp_table_size=1024M              #临时表容量

当数据超过临时表的最大值设定时，自动转为磁盘表，此时因需要进行IO操作，性能会大大下降，而内存表不会，内存表满后，则会提示数据满错误。

show tables 命令不会显示临时表。

以下是对内存表和临时表之间区别的总结：

内存表：

        1．缺省存储引擎为MEMORY
        2．可以通过参数max_heap_table_size来设定内存表大小
        3．到达max_heap_table_size设定的内存上限后将报错
        4．表定义保存在磁盘上，数据和索引保存在内存中
        5．不能包含TEXT、BLOB等字段
   临时表：

        1．缺省存储引擎为MySQL服务器默认引擎，引擎类型只能是：memory（heap）、myisam、merge、innodb（memory临时表由于表的增大可能会转变为myisam临时表）
        2．可以通过参数 tmp_table_size 来设定临时表大小。
        3．到达tmp_table_size设定的内存上限后将在磁盘上创建临时文件
        4．表定义和数据都保存在内存中
        5．可以包含TEXT, BLOB等字段

临时表一般比较少用，通常是在应用程序中动态创建或者由MySQL内部根据SQL执行计划需要自己创建。

内存表则大多作为Cache来使用，特别在没有第三方cache使用时。如今随着memcache、NoSQL的流行，越来越少选择使用内存表。