Mysql存储过程编写说明

1 背景

因为构造数据有点复杂，为了方便和快速的生成数据，根据业务规则，通过Mysql存储过程对数据库数据进行处理，本次记录主要说明涉及的语法格式注意事项和函数的使用。

2 脚本和说明

2.1 脚本

DELIMITER //
	DROP PROCEDURE IF EXISTS `eculist` //
	CREATE DEFINER=CURRENT_USER PROCEDURE `ecuList`(IN vininfo varchar(17),OUT result VARCHAR(1000))
		BEGIN
			DECLARE ecu_code VARCHAR(10);
			declare diagnosticPartNumber VARCHAR(10); -- 存放诊断零件号前10位
			declare diagnosticPartNumberVersion VARCHAR(2);  -- 存放诊断零件号后2位，即版本号的ASCII值
			declare softNumber VARCHAR(2); -- 软件数量，十六进制
			declare softList VARCHAR(100); -- 软件序列号列表
			DECLARE done INT DEFAULT 0;
			
			DECLARE ecu_codes CURSOR FOR 
			select t.node_address from vehicle_portrait_hardware_info t WHERE t.vin=vininfo;

			DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;  
			set result = '执行结果';

			OPEN ecu_codes;
			ecu_codes_loop: LOOP
						
				FETCH ecu_codes INTO ecu_code; #要和声明的变量对应上不能少写，done除外	
				
						IF done=1 THEN LEAVE ecu_codes_loop;	 #如果done的值为1，即遍历结束，结束循环                						
						END IF;#注意 if语句需要添加END IF结束IF
						
						-- 获取诊断零件号，前10位
						select SUBSTRING(t.diagnostic_part_number,1,10) into diagnosticPartNumber from table1 t where t.vin=vininfo and t.node_address =ecu_code;
						-- 获取诊断零件号，后2位，即版本号，获取后需转为ACSII编码
						select ASCII(SUBSTRING(t.diagnostic_part_number,11)) into diagnosticPartNumberVersion from table1 t where t.vin=vininfo and t.node_address =ecu_code;

						-- 获取软件零件号，跟硬件号有关，一个ECU一个硬件号，可以挂接多个软件号
						select LPAD(count(*),2,0) into softNumber from table2 t where t.vin=vininfo and t.node_address =ecu_code and t.hardware_part_number=partNumber and t.hardware_part_version=partVersion;
						select GROUP_CONCAT(CONCAT(t.part_number,'2020',ASCII(t.part_version)) SEPARATOR '') into softList from table2 t where t.vin=vininfo and t.node_address =ecu_code and t.hardware_part_number=partNumber and t.hardware_part_version=partVersion; --多列数据合并到一行
					
					--将结果追加到输出变量中
					select CONCAT(result,char(10),'"',ecu_code,'0E8062ED20','F1A0',diagnosticPartNumber,'2020',diagnosticPartNumberVersion,'F1AE',softNumber,softList,'",') INTO result;
		
			END LOOP ecu_codes_loop;
			CLOSE ecu_codes;

	END
	//
DELIMITER ;

调用

CALL ecuList('VINCODE1234000000',@result);
select @result;

2.2 说明

DELIMITER // 和 DELIMITER ;

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

DROP PROCEDURE IF EXISTS `eculist’

因为Mysql在创建存储过程中没有Oracle数据库create or replace 语法（Mysql在创建VIEW时可以使用create or replace 语法），为了支持脚本的重复执行，可以在存储过程脚本中添加 如果存在就删除的判断

CREATE DEFINER=CURRENT_USER PROCEDURE ‘eculist’ (IN vininfo varchar(17),OUT result VARCHAR(1000))

• DEFINER = user 选项，即指定此对象的定义者是谁，若不显式指定，则创建此对象的用户就是定义者。
• 对于视图、函数及存储过程还可以指定 SQL SECURITY 属性，其值可以为 DEFINER（定义者） 或 INVOKER（调用者），表示在执行过程中，使用谁的权限来执行。DEFINER 表示按定义者拥有的权限来执行，INVOKER表示用调用者的权限来执行
• 默认情况下，SQL SECURITY 属性为 DEFINER 。其值为 DEFINER 时，数据库中必须存在 DEFINER 指定的定义者用户，并且该定义者用户拥有对应的操作权限及引用的相关对象的权限，执行者只需拥有调用权限就能成功执行。当 SQL SECURITY 属性为 INVOKER 时，则需要执行者有调用权限并且有引用的相关对象的权限，才能成功执行。
• 存储过程根据需要可能会有输入、输出、输入输出参数，如果有多个参数用","分割开。MySQL存储过程的参数用在存储过程的定义，共有三种参数类型：IN,OUT,INOUT
  IN参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值
  OUT:该值可在存储过程内部被改变，并可返回
  INOUT:调用时指定，并且可被改变和返回

BEGIN与END

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

DECLARE ecu_code VARCHAR(10);
DECLARE done INT DEFAULT 0;

• 变量声明，语法：DECLARE 变量名1[,变量名2…] 数据类型 [默认值];

• 变量赋值，语法：SET 变量名 = 变量值 [,变量名= 变量值 …]

• Oracle提供了%TYPE定义方式，使一个变量的数据类型与另一个已经定义了的变量（尤其是表的某一列）的数据类型相一致，在Mysql中没有%TYPE定义方式

游标部分

游标声明

DECLARE ecu_codes CURSOR FOR 
			select t.node_address from vehicle_portrait_hardware_info t WHERE t.vin=vininfo;

每个游标必须使用不同的declare continue handler for not found set done=1来控制游标的结束。

declare continue handler for not found set done=1

打开游标和关闭游标

OPEN ecu_codes;
CLOSE ecu_codes;

如果游标到了末尾，结束LOOP循环，不写这句话，数据会多一条

IF done=1 THEN LEAVE ecu_codes_loop;	

ecu_codes_loop: LOOP;
END LOOP ecu_codes_loop;

开始和结束循环，ecu_codes_loop为lableName可以不写，即

LOOP;
END LOOP;

select GROUP_CONCAT(CONCAT(t.part_number,‘2020’,ASCII(t.part_version))
SEPARATOR ‘’) into softList from table2 t …;

• GROUP_CONCAT，用通俗的话讲，就是把分组之后，没有参与分组的这一列的数据拼接起来，本来是多条数据，变成了一条
• group_concat([distinct] 字段名 [order by 排序字段 asc/desc] [separator ‘分隔符’])

select CONCAT(result,char(10),‘"’,ecu_code,diagnosticPartNumber,‘2020’,diagnosticPartNumberVersion,softNumber,softList,‘",’)
INTO result;

• mysql的字符串拼接，不能用‘+’号，必须用concat函数
• char(10) 为换行
• ‘"’ 输出引号