MySQL存储过程和自定义函数

MySQL存储过程和自定义函数

MySQL存储过程和自定义函数

1.1 存储程序

存储程序可以分为存储过程和函数MySQL存储过程和自定义函数，MySQL创建存储过程和函数的语句分别是 和 ，使用call语句来调用存储过程mysql复杂存储过程，只能输出变量返回值，函数可以在语句外调用（即通过引用函数名），也能返回标量值，存储过程也可以调用存储过程。

1.2 创建存储过程

create procedure procedureName （in p_parameter1 varchar(200),in p_parameter2 int,out p_outparameter int）
begin 
...
end;
# 如下为通过call调用存储过程并得到结果。
call procedureName('输入参数' '输入参数' '@输出参数名')；
select @输出参数名;

1.3 创建自定义函数

CREATE FUNCTION functionName ( [ <参数1> <类型1> [ , <参数2> <类型2>] ] … ) RETURNS <类型>
begin 
...
RETURN VALUE; -- 为自定义函数必须存在的
end
# functionName: 指定自定义函数的名称。自定义函数不能与存储过程具有相同的名称。
#  :用于指定自定义函数的参数。这里的参数只有名称和类型。
# RETURNS : 用于声明自定义函数返回值的数据类型。其中，用于指定返回值的数据类型。

1.3.1 示例

CREATE FUNCTION nextval (name VARCHAR(50)) RETURNS INT(11)
begin
  declare _cur INT; 
  declare _maxvalue BIGINT; -- 接收最大值
  declare _increment INT; -- 接收增长步数
  set _increment = (select increment_val from tbl_sequence where seq_name = name);
  set _maxvalue = (select maxvalue2 from tbl_sequence where seq_name = name);
  set _cur = (select current_val from tbl_sequence where seq_name = name);
  update tbl_sequence 
     set current_val = _cur + increment_val
  where seq_name = name;
  if (_cur + _increment >= _maxvalue) then
     update tbl_sequence
        set current_val = min_value
     where seq_name = name ;
  end if;
  return _cur + 1
end
# 调用自定义函数的格式为
select functionName(如果有入参就写入参值);

1.4 自定义函数和存储过程的区别 自定义函数不能拥有输出参数，这是因为自定义函数自身就是输出参数；而存储过程可以拥有输出参数。自定义函数中必须包含一条 语句，而这条特殊的 SQL 语句不允许包含于存储过程中。可以直接对自定义函数进行调用而不需要使用 CALL 语句，而对存储过程的调用需要使用 CALL 语句。 1.5 变量的使用

变量在存储过程和函数中都可以使用MySQL存储过程和自定义函数，变量的使用范围是begin …end程序中，变量的定义使用 关键字定义

-- 语法
declare varName type [default value];
-- 定义变量后，为变量赋值可以改变变量的默认值，MySQL使用set为变量赋值，具体的语法为
set varName=expr ...;
-- 赋值可以赋值为一个常数，也可以赋值为一个select 语句的查询结果，例如:
set varName =(select flowNo from t_flow_info where ...);
select flowNo into varName from t_flow_info where ...;

1.6 定义条件和处理程序

特定条件需要特定处理。这些条件可以联系到错误，以及子程序中的一般流程控制。定义条件是事先定义程序执行过程中遇到的问题，处理程序定义了在遇到这些问题时应当采取的处理方式，并且保证存储过程或函数在遇到警告或错误时能继续执行。这样可以增强存储程序处理问题的能力，避免程序异常停止运行。

1.6.1 定义条件

# 语法
DECLARE condition_name CONDITION FOR [condition_type]
[condition_type]: 
        SQLSTATE [VALUE] sqlstate_value|mysql_error_code
# 如上，condition_name表示条件的名称。condition_type表示条件的类型。sqlstate_value和mysql_error_code都可以表示MySQL的错误，sqlstate_value是长度为5的字符串类型错误代码，mysql_error_code为数值类型错误代码。例如：ERROR 1142(42000)中，sqlstate_value的值是42000，mysql_error_code的值是1142。

如上面这个定义语句指定了需要特殊处理的条件。它将一个名字和指定的错误条件关联起来。这个名字可以随后被用在定义处理程序中。

1.6.1.1 示例

# 使用sqlstate_value
DECLARE command_not_allowed CONDITION FOR SQLSTATE '42000';
# 使用mysql_error_code
DECLARE command_not_allowed CONDITION FOR 1148;

1.6.2 定义处理程序

# 语法
DECLARE handler_type HANDLER FOR condition_value[,...] sp_statement
handler_type:
  CONTINUE|EXIT
condition_value:
  SQLSTATE [VALUE] sqlstate_value|condition_name|SQLWARNING|NOT FOUND|SQLEXCEPTION|mysql_error_code
# 如上，handler_type为错误处理方式，参数取2个值：CONTINUE、EXIT,CONTINUE表示遇到错误不处理，继续执行；EXIT表示遇到错误马上退出。
# condition_value表示错误类型，可以有以下取值：
   # SQLSTATE [VALUE]sqlstate_value: 包含5个字符的字符串错误值。
   # condition_name: 表示DECLARE CONDITION定义的错误条件名称。
   # SQLWARNING: 匹配所有以01开头的SQLSTATE错误代码。
   # NOT FOUND: 匹配所有以02开头的SQLSTATE错误代码。
   # SQLEXCEPTION: 匹配所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE错误代码。
   # mysql_error_code: 匹配数值类型错误代码。
# sp_statement: 程序语句段，表示在遇到定义的错误时，需要执行的存储过程或函数。

1.6.2.1 示例

# 捕获sqlstate_value 如果遇到sqlstate_value的值为42S02,执行CONTINUE操作，并且输出'NO_SUCH_TABLE'信息。
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info='NO_SUCH_TABLE';
# 捕获mysql_error_code 如果遇到mysql_error_code值为1146，执行CONTINUE操作，并且输出'NO_SUCH_TABLE'信息。
DECLARE CONTINUE HANDLER FOR 1146 SET @info='NO_SUCH_TABLE';
# 先定义条件，然后调用 这里定义了no_such_table条件，遇到1146错误就执行CONTINUE操作。
DECLARE no_such_table CONDITION FOR 1146;
DECLARE CONTINUE HANDLER FOR no_such_table SET @info='NO_SUCH_TABLE';
# 使用SQLWARNING SQLWARNING捕获所有以01开头的sqlstate_value值，然后执行EXIT操作，并且输出ERROR信息
DECLARE EXIT HANDLER FOR SQLWARNING SET @info='ERROR';
# 使用NOT FOUND  NOT FOUND捕获所有以02开头的sqlstate_value值，然后执行EXIT操作，并且输出NO_SUCH_TABLE信息。
DECLARE EXIT HANDLER FOR NOT FOUND SET @info='NO_SUCH_TABLE';
# 使用SQLEXCEPTION SQLEXCEPTION捕获所有没有被SQLWARNING或NOT FOUND捕获的sqlstate_value值，然后执行EXIT操作，并且输出ERROR信息。
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info='ERROR';

# 1. 建表
CREATE TABLE A(a int ,primary key(a));
# 2. 创建存储过程
CREATE PROCEDURE demo()
    BEGIN
      DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1;
      SET @x = 1;
      INSERT INTO A VALUES(1);
      SET @x = 2;
      INSERT INTO a VALUES(1);
      SET @x = 3;
    END
# 3. 调用存储过程    
CALL demo();
# 4. 获取存储过程的返回值
SELECT @x;
# 如上面的调用顺序，@x是1一个用户变量，执行结果@x = 3，这表明MySQL被执行到程序的末尾。如果DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1;这一行不存在，第2个INSERT因PRIMARY KEY强制而失败之后,MySQL可能已经采取默认（EXIT）路径，并且SELECT @x可能已经返回2。

1.7 光标的使用

查询语句可能返回多条记录，如果数据量非常大，需要在存储过程或者自定义函数中使用光标来逐条读取查询结果集中的记录。应用程序可能根据需要滚动或浏览其中的数据。光标必须在声明处理程序之前被声明mysql复杂存储过程，并且变量和条件还必须在声明光标或处理程序之前被声明。

1.7.1 声明光标

-- 语法
DECLARE cursor_name CURSOR FOR select_statement
-- 示例
DECLARE cursor_fruit CURSOR FOR SELECT f_name,f_price FROM fruits;

1.7.2 打开光标

-- 语法
OPEN cursor_name;
-- 示例
OPEN cursor_fruit;

1.7.3 使用光标

-- 语法
FETCH crusor_name INTO var_name[,var_name]...{参数名称}
# 其中，cursor_name参数表示光标的名称，var_name参数表示将光标中的SELECT语句查询出来的信息存入该参数中，var_name必须在声明光标之前就定义好。
-- 示例
FETCH cursor_fruit INTO fruit_name,fruit_price;

1.7.4 关闭光标

-- 语法
CLOSE cursor_name;
# 关闭先前打开的光标，如果未被明确的关闭，光标在它被声明复合语句的末尾被关闭。
-- 示例
CLOSE cursor_fruit;

1.8 流程控制的使用

流程控制语句用来根据条件控制语句的执行。MySQL中的用来构造控制流程的语句有：IF语句、CASE语句、LOOP语句、LEAVE语句、语句和语句。每个流程中可能包含一个单独语句，或者是使用BEGIN…END构造的复合语句，构造可以被嵌套。

1.8.1 IF语句

IF语句包含多个条件判断。根据判断的结果为TRUE或FALSE执行相应的语句。

IF expr_condition THEN statement_list
   [ELSEIF expr_condition THEN statement_list]...
   [ELSE statement_list]
END IF
# IF实现了一个基本的条件构造。如果expr_condition的值为TRUE，相应的SQL语句列表被执行，如果没有expr_condition匹配，则ELSE子句里的语句列表被执行。statement_list可以包含一个或多个语句。

IF val IS NULL 
   THEN SELECT 'val IS NULL';
   ELSE SELECT 'val IS NOT NULL';
END IF

1.8.2 CASE语句

# 第一种语法
CASE case_expr
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list]...
    [ELSE statement_list]
END CASE
# 如上，其中，case_expr参数表示条件判断的表达式，决定了那一个WHEN子句会被执行；when_value参数表示表达式可能的值，如果某个when_value表达式和case_expr表达式结果相同，则执行对应THEN关键字后的statement_list中的语句。
# 第二种语法
CASE 
    WHEN expr_condition THEN statement_list
    [WHEN expr_condition THEN statement_list]...
    [ELSE statement_list]
END CASE
# 如上，其中expr_condition参数表示条件判断语句；statement_list参数表示不同条件的执行语句。该语句中，WHEN语句将被逐个执行，直到某个expr_condition表达式为真，则执行对应THEN关键字后面的statement_list语句。如果没有条件匹配，ELSE子句里的语句将被执行。

# 第一种语法示例
CASE val
  WHEN 1 THEN SELECT 'val is 1';
  WHEN 2 THEN SELECT 'val is 2';
  ELSE SELECT 'val is not 1 or 2';
END CASE
# 第二种语法示例
CASE 
   WHEN val is NULL THEN SELECT 'val is NULL';
   WHEN val < 0 THEN SELECT 'val is less then 0';
   WHEN val > 0 THEN SELECT 'val is greater then 0';
   ELSE SELECT 'val is 0';
END CASE   

1.8.3 LOOP语句

LOOP循环语句用来重复执行某些语句，与IF和CASE语句相比，LOOP只是创建一个循环操作的过程，并不进行条件判断。LOOP内的语句一直重复执行直到循环被退出。使用LEAVE子句跳出循环过程。

[loop_label:] LOOP
   statement_list
END LOOP[loop_label]
# loop_label表示LOOP语句的标注名称，可以省略。statement_list参数表示需要循环执行的语句。

DECLARE id INT DEFAULT 0;
add_loop:LOOP
   SET id = id + 1
     IF id >= 10 THEN LEAVE add_loop;
     END IF;
END LOOP add_loop;
# 该示例循环执行id + 1的操作，当id值小于10时，循环重复执行；当id值大于或者等于10时，使用LEAVE语句退出循环。LOOP循环都以END LOOP结束。

1.8.4 LEAVE语句

LEAVE语句用来退出任何被标注的流程控制构造。语法如下

LEAVE label
# label表示参数表示循环的标志。LEAVE和BEGIN...END或循环一起被使用。

add_num:LOOP
   SELECT @count = @count = 1;
   IF @count = 50 THEN LEAVE add_num;
END LOOP add_num;

1.8.5 语句

语句将执行顺序转到语句段开头处。语法如下

ITERATE label
# ITERATE只可以出现在LOOP、REPEAT和WHILE语句内，ITERATE的意思为再次循环，label参数表示循环的标志。

CREATE PROCEDURE demo()
BEGIN
  DECLARE p INT DEFAULT 0;
  my_loop:LOOP
    SET p = p + 1；
    IF p < 10 THEN ITERATE my_loop;
    ELSEIF P > 20 THEN LEAVE my_loop;
    END IF;
    SELECT 'p is between 10 and 20';
END LOOP my_loop;
END 
# p默认为0，如果p的值小于10时，重复执行p = p + 1操作，当p大于等于10并且小于20时，打印消息'p is between 10 and 20'；当p大于20时，退出循环。

1.8.6 语句

语句创建一个带条件判断的循环过程，每次语句执行完毕之后，会对条件表达式进行判断，如果表达式为真，则循环结束；否则重复执行循环中的语句。的语法如下：

[repeat_label:] REPEAT
   statement_list
UNTIL expr_condition
END REPEAT [repeat_label]
# repeat_label为REPEAT语句的标注名称，该参数可以省略；statement_list可以一直执行直到expr_condition为真。

DECLARE id INT DEFAULT 0;
REPEAT
  SET id = id + 1;
  UNTIL id >= 10
END REPEAT;
# 当id小于10的时候，循环可以一直执行加1的操作，当id等于10，循环退出。

1.8.7 WHILE语句

WHILE语句创建一个带条件判断的循环过程，与不同，WHILE语句在执行时，先对指定的表达式进行判断mysql复杂存储过程，如果为真，则执行循环内的语句，否则退出循环。语法如下

[while_label:] WHILE expr_condition DO
    statement_list
END WHILE [while_label]
# while_label为WHILE语句的标注名称；expr_condition为进行判断的表达式，如果表达式结果为真，statement_list将被执行。直至expr_condition为假，退出循环。

DECLARE i INT DEFAULT 0;
WHILE i < 100 DO
   SET i = i + 1;
END WHILE;