Mysql 存储过程

存储过程

[ IN | OUT | INOUT ] param_name type
1. IN表示输入参数
2. OUT表示输出参数
3. INOUT表示既可以是输入，也可以是输出
4. param_name参数是存储过程的参数名称
5. type参数指定存储过程的参数类型，该类型可以是MySQL数据库的任意数据类型。

characteristic参数有多个取值。其取值说明如下：
1. LANGUAGE SQL：说明routine_body部分是由SQL语言的语句组成，这也是数据库系统默认的语言。
2. [NOT] DETERMINISTIC：指明存储过程的执行结果是否是确定的。DETERMINISTIC表示结果是确定的。每次执行存储过程时，相同的输入会得到相同的输出。NOT DETERMINISTIC表示结果是非确定的，相同的输入可能得到不同的输出。
默认情况下，结果是非确定的

{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }：指明子程序使用SQL语句的限制。
1. CONTAINS SQL表示子程序包含SQL语句，但不包含读或写数据的语句
2. NO SQL表示子程序中不包含SQL语句
3. READS SQL DATA表示子程序中包含读数据的语句
4. MODIFIES SQL DATA表示子程序中包含写数据的语句
默认情况下，系统会指定为CONTAINS SQL

SQL SECURITY { DEFINER | INVOKER }：指明谁有权限来执行。
1. DEFINER表示只有定义者自己才能够执行
2. INVOKER表示调用者可以执行
默认情况下，系统指定的权限是DEFINER

例子：

CREATE DEFINER = `root`@`%` PROCEDURE `NewProc`(IN `webid` int,OUT `total` int)
    READS SQL DATA
BEGIN
    select count(*) into total from record where record.domain_id = webid;
END;

运行参数：1，@total

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

存储函数

CREATE DEFINER = `root`@`%` FUNCTION `NewProc`(`webid` int)
 RETURNS int(11)
    READS SQL DATA
BEGIN
    RETURN (select count(*) from record where record.domain_id = webid);
END;

运行参数：1

变量声明赋值

CREATE DEFINER = `root`@`%` FUNCTION `NewProc`(`webid` int)
 RETURNS int(11)
    NO SQL
BEGIN
    DECLARE my_var int default 10;
    set my_var=1;
    return my_var;
END;

定义异常处理

定义条件和处理程序是事先定义程序执行过程中可能遇到的问题。并且可以在处理程序中定义解决这些问题的办法。这种方式可以提前预测可能出现的问题，并提出解决办法。这样可以增强程序处理问题的能力，避免程序异常停止。

DECLARE  condition_name  CONDITION  FOR  condition_value  
condition_value:  
      SQLSTATE [VALUE] sqlstate_value | mysql_error_code 

condition_name参数表示条件的名称；
condition_value参数表示条件的类型；
例如ERROR 1146 (42S02)中，sqlstate_value值是42S02，mysql_error_code值是1146

handler_type参数指明错误的处理方式，该参数有3个取值。
这3个取值分别是CONTINUE、EXIT和UNDO。
1. CONTINUE表示遇到错误不进行处理，继续向下执行
2. EXIT表示遇到错误后马上退出
3. UNDO表示遇到错误后撤回之前的操作，MySQL中暂时还不支持这种处理方式

CREATE DEFINER = `root`@`%` FUNCTION `NewProc`(`webid` int)
 RETURNS int(11)
    NO SQL
BEGIN
    DECLARE can_not_find CONDITION FOR 1146;  
    DECLARE CONTINUE HANDLER FOR can_not_find SET @info='CAN NOT FIND'; 
    return ''; 
END;

语句跳转

CREATE DEFINER = `root`@`%` FUNCTION `NewProc`(`flag` int)
 RETURNS int(11)
BEGIN
    DECLARE my_var INT DEFAULT 10;
    IF flag = 1 THEN set my_var = 2;
    ELSEIF flag = 2 THEN set my_var = 3;
    ELSE set my_var = 0;
    end if;
    RETURN my_var;
END;

循环语句

CREATE DEFINER = `root`@`%` FUNCTION `NewProc`(`flag` int)
 RETURNS int(11)
BEGIN
    DECLARE my_var INT DEFAULT 10;
    IF flag = 1 THEN set my_var = 2;
    ELSEIF flag = 2 THEN set my_var = 3;
    ELSE set my_var = 0;
    end if;
    RETURN my_var;
END;