mysql stored procedure - yaokun123/php-wiki GitHub Wiki

mysql的存储过程

一、存储过程的介绍

SQL语句需要先编译然后执行，而存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。

存储过程是可编程的函数，在数据库中创建并保存，可以由SQL语句和控制结构组成。当想要在不同的应用程序或平台上执行相同的函数，或者封装特定功能时，存储过程是非常有用的。数据库中的存储过程可以看做是对编程中面向对象方法的模拟，它允许控制数据的访问方式。

存储过程是数据库的一个重要的功能，MySQL 5.0以前并不支持存储过程，这使得MySQL在应用上大打折扣。好在MySQL 5.0开始支持存储过程，这样即可以大大提高数据库的处理速度，同时也可以提高数据库编程的灵活性。

二、存储过程的优点

1.增强SQL语言的功能和灵活性：存储过程可以用控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。
2.标准组件式编程：存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改，对应用程序源代码毫无影响。
3.较快的执行速度：如果某一操作包含大量的Transaction-SQL代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询，优化器对其进行分析优化，并且给出最终被存储在系统表中的执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化，速度相对要慢一些。
4.减少网络流量：针对同一个数据库对象的操作（如查询、修改），如果这一操作所涉及的Transaction-SQL语句被组织进存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而大大减少网络流量并降低了网络负载。
5.作为一种安全机制来充分利用：通过对执行某一存储过程的权限进行限制，能够实现对相应的数据的访问权限的限制，避免了非授权用户对数据的访问，保证了数据的安全。

三、语法

CREATE PROCEDURE 过程名([IN|OUT|INOUT] 参数名数据类型[,[IN|OUT|INOUT] 参数名数据类型…]]) [特性 ...] 过程体

DELIMITER //
  CREATE PROCEDURE myproc(OUT s int)
    BEGIN
      SELECT COUNT(*) INTO s FROM students;
    END
    //
DELIMITER ;

分隔符

MySQL默认以";"为分隔符，如果没有声明分割符，则编译器会把存储过程当成SQL语句进行处理，因此编译过程会报错，所以要事先用“DELIMITER //”声明当前段分隔符，让编译器把两个"//"之间的内容当做存储过程的代码，不会执行这些代码；“DELIMITER ;”的意为把分隔符还原。

参数

存储过程可以有0个或多个参数，用于存储过程的定义。
MySQL存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT:

IN:参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值

mysql> delimiter $$
mysql> create procedure in_param(in p_in int)
    -> begin
    -> 　　select p_in;
    -> 　　set p_in=2;
    ->    select P_in;
    -> end$$
mysql> delimiter ;

mysql> set @p_in=1;

mysql> call in_param(@p_in);
+------+
| p_in |
+------+
|    1 |
+------+

+------+
| P_in |
+------+
|    2 |
+------+

mysql> select @p_in;
+-------+
| @p_in |
+-------+
|     1 |
+-------+
以上可以看出，p_in在存储过程中被修改，但并不影响@p_id的值，因为前者为局部变量、后者为全局变量。

OUT:该值可在存储过程内部被改变，并可返回。表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）

mysql> delimiter //
mysql> create procedure out_param(out p_out int)
    ->   begin
    ->     select p_out;
    ->     set p_out=2;
    ->     select p_out;
    ->   end
    -> //
mysql> delimiter ;

mysql> set @p_out=1;

mysql> call out_param(@p_out);
+-------+
| p_out |
+-------+
|  NULL |
+-------+
#因为out是向调用者输出参数，不接收输入的参数，所以存储过程里的p_out为null
+-------+
| p_out |
+-------+
|     2 |
+-------+

mysql> select @p_out;
+--------+
| @p_out |
+--------+
|      2 |
+--------+
#调用了out_param存储过程，输出参数，改变了p_out变量的值

INOUT:调用时指定，并且可被改变和返回。既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）

mysql> delimiter $$
mysql> create procedure inout_param(inout p_inout int)
    ->   begin
    ->     select p_inout;
    ->     set p_inout=2;
    ->     select p_inout;
    ->   end
    -> $$
mysql> delimiter ;

mysql> set @p_inout=1;

mysql> call inout_param(@p_inout);
+---------+
| p_inout |
+---------+
|       1 |
+---------+

+---------+
| p_inout |
+---------+
|       2 |
+---------+

mysql> select @p_inout;
+----------+
| @p_inout |
+----------+
|        2 |
+----------+
#调用了inout_param存储过程，接受了输入的参数，也输出参数，改变了变量

注意：如果过程没有参数，也必须在过程名后面写上小括号确保参数的名字不等于列的名字，否则在过程体中，参数名被当做列名来处理

建议: 输入值使用in参数；返回值使用out参数； inout参数就尽量的少用。

过程体

过程体的开始与结束使用BEGIN与END进行标识。

变量

语法：DECLARE 变量名1[,变量名2...] 数据类型 [默认值];
数据类型为MySQL的数据类型

变量赋值

语法：SET 变量名 = 变量值 [,变量名= 变量值 ...]

用户变量

用户变量一般以@开头
注意：滥用用户变量会导致程序难以理解及管理

四、创建存储过程

CREATE
    [DEFINER = { user | CURRENT_USER }]
　PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

proc_parameter:
    [ IN | OUT | INOUT ] param_name type

characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }

routine_body:
　　Valid SQL routine statement

[begin_label:] BEGIN
　　[statement_list]
　　　　……
END [end_label]

示例

mysql> delimiter $$　　#将语句的结束符号从分号;临时改为两个$$(可以是自定义)

mysql> CREATE PROCEDURE delete_matches(IN p_playerno INTEGER)
    -> BEGIN
    -> 　　DELETE FROM MATCHES
    ->    WHERE playerno = p_playerno;
    -> END$$
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;　　#将语句的结束符号恢复为分号

解析：默认情况下，存储过程和默认数据库相关联，如果想指定存储过程创建在某个特定的数据库下，那么在过程名前面加数据库名做前缀；

在定义过程时，使用DELIMITER $$ 命令将语句的结束符号从分号 ; 临时改为两个$$，使得过程体中使用的分号被直接传递到服务器，而不会被客户端（如mysql）解释。

五、调用存储过程

call sp_name[(传参)];

mysql> select * from MATCHES;
+---------+--------+----------+-----+------+
| MATCHNO | TEAMNO | PLAYERNO | WON | LOST |
+---------+--------+----------+-----+------+
|       1 |      1 |        6 |   3 |    1 |
|       7 |      1 |       57 |   3 |    0 |
|       8 |      1 |        8 |   0 |    3 |
|       9 |      2 |       27 |   3 |    2 |
|      11 |      2 |      112 |   2 |    3 |
+---------+--------+----------+-----+------+
rows in set (0.00 sec)

mysql> call delete_matches(57);
Query OK, 1 row affected (0.03 sec)

mysql> select * from MATCHES;
+---------+--------+----------+-----+------+
| MATCHNO | TEAMNO | PLAYERNO | WON | LOST |
+---------+--------+----------+-----+------+
|       1 |      1 |        6 |   3 |    1 |
|       8 |      1 |        8 |   0 |    3 |
|       9 |      2 |       27 |   3 |    2 |
|      11 |      2 |      112 |   2 |    3 |
+---------+--------+----------+-----+------+
rows in set (0.00 sec)

解析: 在存储过程中设置了需要传参的变量p_playerno，调用存储过程的时候，通过传参将57赋值给p_playerno，然后进行存储过程里的SQL操作。

六、存储过程体

存储过程体包含了在过程调用时必须执行的语句，例如：dml、ddl语句，if-then-else和while-do语句、声明变量的declare语句等

过程体格式：以begin开始，以end结束(可嵌套)

BEGIN
　　BEGIN
　　　　BEGIN
　　　　　　statements; 
　　　　END
　　END
END

注意：每个嵌套块及其中的每条语句，必须以分号结束，表示过程体结束的begin-end块(又叫做复合语句compound statement)，则不需要分号。

七、为语句块贴标签

[begin_label:] BEGIN
　　[statement_list]
END [end_label]

例如：

label1: BEGIN
　　label2: BEGIN
　　　　label3: BEGIN
　　　　　　statements; 
　　　　END label3 ;
　　END label2;
END label1

标签有两个作用：

1.增强代码的可读性
2.在某些语句(例如:leave和iterate语句)，需要用到标签

八、入门示例

CREATE DEFINER=`root`@`%` PROCEDURE `get_next_indexed_biz_company`(out next_biz_company int)
BEGIN
  #定义一个变量名current_biz_company_id类型为bigint
	declare current_biz_company_id bigint;
	#定义一个变量名new_biz_company_id类型为bigint
	declare new_biz_company_id bigint;
	
	#开始事务
	start transaction;
		#这是一条sql语句,作用就是取出tb_company_param表中的param_name='tb_biz_company.index.id'的param_value值
		#并赋值给current_biz_company_id变量
		select param_value into current_biz_company_id from tb_company_param where param_name = 'tb_biz_company.index.id' for update;
		
		#这就是个判断啦
		if(current_biz_company_id is null) then 
			set current_biz_company_id = 0;
		end if;
		
		#这又来一条sql语句，作用是取出tb_biz_company表中的company_id>current_biz_company_id的第一条company_id值
		#并赋值给new_biz_company_id变量
		select company_id into new_biz_company_id from tb_biz_company where company_id > current_biz_company_id order by company_id limit 0,1;
		
		#这还是一条sql语句,作用是更新tb_company_param表的param_name = 'tb_biz_company.index.id'的param_value字段
		#将param_value设置为new_biz_company_id变量的值
		update tb_company_param set param_value = new_biz_company_id where param_name = 'tb_biz_company.index.id';
		
		#将new_biz_company_id赋值给传入函数的参数next_biz_company，并返回（因为设置的参数类型为OUT）
		set next_biz_company = new_biz_company_id;
		
		#展示变量new_biz_company_id的值
		select new_biz_company_id;
	
	#提交事务
	commit;
	
	#这就是mysql的存储过程简单示例，简单吧

END

注意：这个是跟数据表相关联的，请设置好相关数据表

tb_company_param(param_name(tb_biz_company.index.id)/param_value)
tb_biz_company(comapny_id)

BEGIN
	DECLARE i INT DEFAULT 1;
    START TRANSACTION;
    WHILE i <= 4000000 DO
    	INSERT INTO test1 (id,name,sex,email) VALUES (i,concat('javacode',i),if(mod(i,2),1,2),concat('javacode',i,'@163.com'));
        SET i = i + 1;
        IF i%10000=0 THEN
        	COMMIT;
            START TRANSACTION;
        END IF;
    END WHILE;