MySQl 存储过程

引言

存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。 存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。 存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用，也就是下图语法分析的过程：

也就是说存储过程是 SQL 语句与控制语句的预编译集合，以一个名称存储并作为一个单元处理。

创建存储过程

DELIMITER //
 CREATE PROCEDURE 存储过程名([参数类型 参数名 参数数据类型],...)
   BEGIN
   ...
   END //
DELIMITER ;

delimiter 更改结束符：

DELIMITER //

• 第一个命令是 ’ DELIMITER // '，它与存储过程语法无关。 DELIMITER语句将标准结束符分号 ’ ; ’ 更改为 ’ // '。
• 更改结束符的原因是： 存储过程是作为整体传递给服务器， 如果分号为结束符，由于存储过程主体语句是由SQL 语句组成的可能会有很多分号，就不能完整的执行整个存储过程。
• 这里DELIMITER后可以跟任意字符，一般使用 ’ $$ ’ ;

参数：

[参数类型 参数名 参数数据类型]

• 参数类型 IN、OUT、INOUT：

  • IN： 默认模式，在存储过程中定义IN参数时，调用程序必须将参数传递给存储过程。
    注： 另外， IN参数的值被保护。这意味着即使在存储过程中更改了IN参数的值，在存储过程结束后仍保留其原始值。换句话说，存储过程只使用IN参数的副本。
  • OUT： 可以在存储过程中更改OUT参数的值，并将其更改后新值传递回调用程序。
    注：存储过程在启动时无法访问OUT参数的初始值。
  • INOUT： 是IN和OUT参数的组合。 既可以传递参数，并且存储过程可以修改INOUT参数并将新值传递回调用程序。

变量：

DECLARE 变量名 变量数据类型 DEFAULT 默认值;

• DECLARE： 在DECLARE关键字后面要指定变量名。变量名必须遵循MySQL表列名称的命名规则。
• 指定变量的数据类型及其大小；当声明一个变量时，它的初始值为NULL；但是可以使用DEFAULT关键字为变量分配默认值。
• SET： 变量值可以通过 SET 改变；如： SET 变量名 = 值；
• 变量 作用域：
  变量有自己的 作用域 ，它用来定义它的生命周期。
  • 如果在存储过程中声明一个变量，那么当达到存储过程的 END 语句时，它 将超出范围，因此在其它代码块中无法访问。
  • 如果您在任意 BEGIN END 块内声明一个变量，那么如果达到END，它将超 出范围。 可以在不同的作用域中声明具有相同名称的两个或多个变量，因为变量仅在自己的作用域中有效。 但是，在不同范围内声明具有相同名称的变量不是很好的编程习惯。
  • 以@符号开头的变量是用户变量。
    注：
  1. 用户变量名一般以@开头
  2. 滥用用户变量会导致程序难以理解及管理

控制语句：

最外层的 BEGIN 和 END之间的部分称为存储过程的主体。将 SQL语句与控制语句放在主体中以处理业务逻辑。

条件语句：

• IF 语句用法：
  类似于C语言中的 if ，else if， else
  IF：

  IF expression THEN 
     statements;
  END IF;
  

  IF ELSE ：

  IF expression THEN
     statements;
  ELSE
     else-statements;
  END IF;
  

  IF ELSE IF ELSE：

  IF expression THEN
     statements;
  ELSEIF elseif-expression THEN
     elseif-statements;
  ...  
  ELSE
     else-statements;
  END IF;
  

• CASE 语句用法：
  类似于C语言中的 switch case

  CASE  case_expression
     WHEN when_expression_1 THEN commands
     WHEN when_expression_2 THEN commands
     ...
     ELSE commands
  END CASE;
  

循环语句：

• WHILE 语句用法：
  类似于C语言中的 while

  WHILE expression DO
     statements
  END WHILE;`
  

• REPEAT 语句用法：

  类似于C语言中的 do while

  REPEAT
  	statements;
  UNTIL expression
  END REPEAT;
  

• LOOP 语句用法：
  loop 循环不需要初始条件，这点和 while 循环相似，同时和 repeat 循环一样不需要结束条件

  loop_label:  LOOP
  ...
   END LOOP;  
  

  控制循环：

  • LEAVE 用于立即退出循环，而无需等待检查条件。LEAVE类似于C语言的 break 。
  • ITERATE 允许您跳过剩下的整个代码并开始新的迭代。ITERATE语句类似于C语言的 continue 。

例子：

DELIMITER //
 CREATE PROCEDURE test1 (IN require tinyint, IN usernames varchar(16)) 
   BEGIN
	   IF require = 1 THEN
	       	SELECT * FROM user WHERE username = usernames;
	   ELSE IF require = 2 THEN
	        DELETE  FROM user WHERE username = usernames;
	   ELSE IF require = 3 THEN
	       	INSERT INTO user(username) VALUES (usernames);
	   END IF;
   END //
DELIMITER ;