MYSQL---存储过程

MySQL 的存储过程是一种在数据库中存储复杂程序，以便外部程序调用的数据库对象，是为了完成特定功能的 SQL 语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数（需要时）来调用执行 。以下是根据你提供的分类，对 MySQL 存储过程的详细介绍：

存储过程基本语法

• 创建语法：使用CREATE PROCEDURE语句创建存储过程，一般格式为CREATE PROCEDURE 存储过程名([IN | OUT | INOUT 参数名 数据类型,...]) BEGIN SQL语句集 END 。其中，DELIMITER语句常用来更改命令结束符，因为存储过程中本身可能包含多个 SQL 语句，默认的;结束符会使客户端提前结束存储过程定义，例如先执行DELIMITER //，将结束符临时改为// ，在存储过程定义完后再执行DELIMITER ;改回默认结束符。
• 调用语法：使用CALL语句调用存储过程，如CALL 存储过程名([参数,...])。

存储过程变量

• 系统变量：由 MySQL 服务器提供，分为全局变量（影响整个服务器实例）和会话变量（仅影响当前会话）。查看全局变量可以使用SHOW GLOBAL VARIABLES ，查看会话变量可以使用SHOW SESSION VARIABLES ；获取变量值可以用SELECT @@global.变量名或SELECT @@session.变量名 ，例如SELECT @@global.autocommit 。
• 用户定义变量：用户自己定义的变量，以@开头，可以在 MySQL 客户端使用SET @变量名 = 值或SELECT 值 INTO @变量名的方式赋值，在存储过程中也能使用用户变量，如SET @count = 10; ，但滥用用户变量会使程序难以理解和管理。
• 局部变量：在存储过程内部使用DECLARE语句声明，作用域仅限于存储过程内部，例如DECLARE i INT DEFAULT 0; ，声明了一个初始值为 0 的整型局部变量i，且局部变量必须在存储过程中其他可执行语句之前声明。

存储过程控制结构

• if 判断：语法为IF search_condition THEN statement_list [ELSEIF search_condition THEN statement_list ...] [ELSE statement_list] END IF 。当search_condition条件成立时，执行THEN后的statement_list语句，若不成立则按顺序判断ELSEIF条件，都不满足则执行ELSE分支。比如根据学生成绩判断等级：

CREATE PROCEDURE proc_getGrade (stu_no VARCHAR(20), cour_no VARCHAR(10))
BEGINDECLARE stu_grade FLOAT;SELECT grade INTO stu_grade FROM grade WHERE student_no = stu_no AND course_no = cour_no;IF stu_grade >= 90 THENSELECT stu_grade, 'A';ELSEIF stu_grade < 90 AND stu_grade >= 80 THENSELECT stu_grade, 'B';ELSEIF stu_grade < 80 AND stu_grade >= 70 THENSELECT stu_grade, 'C';ELSEIF stu_grade < 70 AND stu_grade >= 60 THENSELECT stu_grade, 'D';ELSESELECT stu_grade, 'E';END IF;
END

• 参数（IN、OUT、INOUT）：
  • IN（输入参数）：用于调用者向存储过程传入值，可以是字面量或变量，在存储过程内部对该参数的修改不会影响外部传入的变量值。例如CREATE PROCEDURE proc_in(IN p_in INT) BEGIN SET @local_var = p_in; END 。
  • OUT（输出参数）：用于存储过程向调用者传出值，只能接收变量，在存储过程开始执行时，该参数初始值为NULL。如CREATE PROCEDURE proc_out(OUT p_out INT) BEGIN SET p_out = 10; END 。
  • INOUT（输入输出参数）：既可以接收调用者传入的值，也能将处理后的值传出给调用者，传入的值必须是变量。比如CREATE PROCEDURE proc_inout(INOUT p_inout INT) BEGIN SET p_inout = p_inout * 2; END 。
• case：语法有两种形式，一种是简单CASE语句 ，CASE 表达式 WHEN 值1 THEN 结果1 WHEN 值2 THEN 结果2 ... ELSE 结果n END；另一种是搜索CASE语句，CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 ... ELSE 结果n END 。例如：

SELECT CASE sva WHEN 1 THEN '男' ELSE '女' END as ssva FROM taname WHERE sva;

• 循环 - while：语法为WHILE search_condition DO statement_list END WHILE ，只要search_condition条件为真，就会重复执行statement_list语句块。例如：

CREATE PROCEDURE proc_while()
BEGINDECLARE i INT DEFAULT 1;DECLARE sum_num INT DEFAULT 0;WHILE i <= 10 DOSET sum_num = sum_num + i;SET i = i + 1;END WHILE;SELECT sum_num;
END

• 循环 - repeat：语法是REPEAT statement_list UNTIL search_condition END REPEAT ，先执行一次循环体statement_list，然后判断UNTIL后的search_condition条件，不满足则继续执行循环体，直到条件满足为止。例如：

CREATE PROCEDURE p11()
BEGINDECLARE i INT DEFAULT 0;DECLARE total INT DEFAULT 0;REPEAT SET i = i + 1; SET total = total + i;UNTIL i >= 100 END REPEAT;SELECT total;
END

• 循环 - loop：LOOP本身是一个简单的循环结构，配合LEAVE语句（类似于其他语言中的break）可以在满足条件时退出循环。可以给LOOP设置标签，方便在多重循环中指定退出哪一层循环。示例：

DELIMITER //
CREATE PROCEDURE my_test4()
BEGIN DECLARE i INT DEFAULT 100; loop_label: LOOP INSERT INTO xmcc_product VALUES(CONCAT('1000',i),CONCAT('手机',i),i*10,i*100); SET i=i+1; IF i >= 105 THEN LEAVE loop_label; END IF; END LOOP; 
END ;// 
DELIMITER ;
CALL my_test4();

存储过程游标（cursor）

游标用于处理存储过程中的结果集，它是一种数据缓冲区的思想，用来存放 SQL 语句执行的结果，类似于指向数据结构堆栈中的指针，每次只能取出一条数据进行操作。使用步骤如下：

1. 声明游标：使用DECLARE语句声明，且必须在变量声明之后，例如DECLARE curl CURSOR FOR SELECT p_name FROM xmcc_product; 。
2. 打开游标：使用OPEN语句打开已声明的游标，如OPEN curl; 。
3. 取值：使用FETCH语句将游标中的数据取出赋值给变量，FETCH 游标名 INTO val1,val2,... 。
4. 关闭游标：使用CLOSE语句关闭游标，释放资源，如CLOSE curl; 。
   例如：

DELIMITER //
CREATE PROCEDURE my_test8()
BEGINDECLARE result VARCHAR(100) DEFAULT '';DECLARE product_name VARCHAR(10);DECLARE done INT DEFAULT FALSE; DECLARE curl CURSOR FOR SELECT p_name FROM xmcc_product;DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; OPEN curl;WHILE(NOT done) DOFETCH curl INTO product_name; SET result:=CONCAT(result,',',product_name);END WHILE;CLOSE curl;SELECT result;
END//
DELIMITER ;

存储过程条件处理程序（handler）

用于在存储过程执行过程中捕获特定的错误或条件，并执行相应的处理操作。语法为DECLARE handler_type HANDLER FOR condition_value [, condition_value ...] statement 。其中，handler_type可以是CONTINUE（出现条件时继续执行后续语句）或EXIT（出现条件时退出存储过程）等；condition_value可以是SQLEXCEPTION（捕获 SQL 异常）、NOT FOUND（查询未找到结果）等 。例如：

DELIMITER//
CREATE PROCEDURE my_test7()
BEGINDECLARE i INT DEFAULT 0;DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET i:=-1;START TRANSACTION;UPDATE xmcc_product SET p_name='苹果' WHERE p_id=10000;INSERT INTO xmcc_product VALUES(10000,'主键冲突',10,100); IF i=-1 THENSELECT '出现异常，事务回滚';ROLLBACK; ELSECOMMIT; END IF;
END//
DELIMITER ;
CALL my_test7();

存储函数

存储函数也是存储在数据库中的可执行代码块，与存储过程类似，但主要区别在于存储函数必须返回一个值，并且可以在 SQL 表达式中直接调用，而存储过程一般通过CALL语句调用。创建存储函数使用CREATE FUNCTION语句，例如：

CREATE FUNCTION func_get_grade(score DECIMAL(5,2)) RETURNS CHAR(1)
DETERMINISTIC
BEGINDECLARE grade CHAR(1);IF score >= 90 THENSET grade = 'A';ELSEIF score >= 80 THENSET grade = 'B';ELSEIF score >= 70 THENSET grade = 'C';ELSEIF score >= 60 THENSET grade = 'D';ELSESET grade = 'E';END IF;RETURN grade;
END

可以在查询中像使用内置函数一样使用它，如SELECT func_get_grade(85); 。

MySQL 存储过程提供了强大的数据处理能力，能实现复杂的业务逻辑，提高数据库操作的效率和安全性，但同时也需要合理使用，避免过度复杂导致维护困难。