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)