MYSQL存储过程实例

存储过程：

存储过程的创建：

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

    [characteristic ...] routine_body



CREATE FUNCTION sp_name ([func_parameter[,...]])

    RETURNS type

    [characteristic ...] routine_body



    proc_parameter:

    [ IN | OUT | INOUT ] param_name type

   

    func_parameter:

    param_name type



type:

    Any valid MySQL data type



characteristic:

    LANGUAGE SQL

  | [NOT] DETERMINISTIC

  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }

  | SQL SECURITY { DEFINER | INVOKER }

  | COMMENT 'string'



routine_body:

    Valid SQL procedure statement or statements





显示创建的存储过程或函数：

SHOW CREATE {PROCEDURE | FUNCTION} sp_name



删除存储过程或函数：

DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name



显示存储过程或函数状态：

SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']

存储过程的调用：

CALL sp_name([parameter[,...]])



BEGIN ... END复合语句：

[begin_label:] BEGIN

    [statement_list]

END [end_label]



DECLARE语句：

DECLARE仅被用在BEGIN ... END复合语句里，并且必须在复合语句的开头，在任何其它语句之前。

光标必须在声明处理程序之前被声明，并且变量和条件必须在声明光标或处理程序之前被声明。

DECLARE var_name[,...] type [DEFAULT value]



SELECT ... INTO语句：

SELECT col_name[,...] INTO var_name[,...] table_expr

这个SELECT语法把选定的列直接存储到变量。因此，只有单一的行可以被取回。

SELECT id,data INTO x,y FROM test.t1 LIMIT 1;



注意：
mysql> delimiter //

mysql> create function hello(s char(20)) returns char(50)

    -> return concat('Hello, ',s,'!');

    -> //

ERROR 1418 (HY000): This routine has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_routine_creators variable)

以下错误需要设置log_bin_trust_routine_creators变量的值
Mysql> set GLOBAL log_bin_trust_routine_creators=1;



实例1：

mysql> select * from a;              

+--------+------+

| a      | b    |

+--------+------+

| falcon | 223  |

| cs     | 500  |

+--------+------+

mysql> delimiter //

mysql> create procedure proc_sum_a(OUT sum INT)

    -> begin

    -> select sum(b) from a;

    -> end

    -> //

Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;

mysql> call proc_sum_a(@a);

+--------+

| sum(b) |

+--------+

|    723 |

+--------+

1 row in set (0.02 sec)



Query OK, 0 rows affected (0.02 sec)



实例2：

mysql> select * from a;

+----------+------+

| a        | b    |

+----------+------+

| falcon   | 223  |

| cs       | 500  |

| cs       | 500  |

| Falcon.C | 1508 |

+----------+------+

4 rows in set (0.00 sec)

mysql> create procedure proc_char_a (OUT str CHAR)  

    -> begin

    -> select * from a;

    -> end

    -> //

Query OK, 0 rows affected (0.00 sec)

mysql> call proc_varchar_a(@str) //

+----------+------+

| a        | b    |

+----------+------+

| falcon   | 223  |

| cs       | 500  |

| cs       | 500  |

| Falcon.C | 1508 |

+----------+------+

4 rows in set (0.00 sec)



实例3：

mysql>delimiter //

mysql> create function fun_hello(s char(20)) returns char(50)

    -> return concat('Hello, ',s,'!');

    -> //

Query OK, 0 rows affected (0.00 sec)



mysql> delimiter ;

mysql> select fun_hello('Falcon.C');

+-----------------------+

| fun_hello('Falcon.C') |

+-----------------------+

| Hello, Falcon.C!      |

+-----------------------+

1 row in set (0.00 sec)