MYSQL存储过程实例
存储过程：存储过程的创建：
CREATE PROCEDURE sp_name (])
routine_body
CREATE FUNCTION sp_name (])
RETURNS type
routine_body
proc_parameter:
[ IN | OUT | INOUT ] param_name type
func_parameter:
param_name type
type:
Any valid MySQL data type
characteristic:
LANGUAGE SQL
| 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} sp_name
显示存储过程或函数状态：
SHOW {PROCEDURE | FUNCTION} STATUS
存储过程的调用：
CALL sp_name(])
BEGIN ... END复合语句：
BEGIN
END
DECLARE语句：
DECLARE仅被用在BEGIN ... END复合语句里，并且必须在复合语句的开头，在任何其它语句之前。
光标必须在声明处理程序之前被声明，并且变量和条件必须在声明光标或处理程序之前被声明。
DECLARE var_name[,...] type
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)
页:
[1]