myql深度学习9-存储函数_length(longitude::varchar)-优快云博客

本文链接：https://blog.youkuaiyun.com/yuezheyue123/article/details/104200434

1、存储函数和存储过程很类似
创建存储函数,其中参数func_parameter的表现形式

[in | out | inout param_name type]

create function func_name ([func_parameter])
returns type
    begin
        return(routine_body);
    end

例如：

create function select_student(aa int)
returns varchar(50)
    begin
    return(select name from student where id = aa);
    end

使用存储函数

select select_student(1)

2、查看存储函数

show function status [like xxx]

show crate function sp_name

3、删除存储函数

drop function [if exists] sp_name

实际应用例子：
实现同时更新两个表，若有一个表更新失败则回滚。

CREATE PROCEDURE `EMB_Equipment_UpdateInfo`(
	  IN $EquipmentID CHAR(36),
    IN $TechnologyID CHAR(36),
    IN $EquipmentName VARCHAR(255),
    IN $EquipmentClassID CHAR(36),
    IN $OrganiseUnitID CHAR(36),
    IN $DepartMentID CHAR(36),
    IN $ManufacturerID CHAR(36),
    IN $PlaceOrigin VARCHAR(50),
    IN $EquipmentModelID CHAR(36),
    IN $PurposeClassID CHAR(36),
    IN $Standard VARCHAR(50),
    IN $SupplierID CHAR(36),
    IN $MaintenanceID CHAR(36),
    IN $Cost VARCHAR(50),
    IN $Custodian VARCHAR(20),
    IN $Recipient VARCHAR(20),
    IN $Region char(36),
    IN $Positions VARCHAR(255),
    IN $Longitude VARCHAR(50),
    IN $Latitude VARCHAR(50),
    IN $EAMCode VARCHAR(50),
    IN $ERPCode VARCHAR(50),
    IN $OtherCode VARCHAR(50),
    IN $Detaile text,
    IN $Resume text,
    IN $PurchaseMode VARCHAR(50),
    IN $BuyDate VARCHAR(50),
    IN $GuaranteeDate VARCHAR(50),
    IN $UseTime VARCHAR(50),
    IN $AgeLimit int(11),
    IN $ForceTimeLength int(11),
    IN $DailyTimeLength int(11),
    IN $RunTimeLength int(11),
    IN $RunLife int(11),
    IN $TotalRunTimeLength int(11),
    IN $RunStatus int(11),
    IN $ModifiedDate VARCHAR(50),
    IN $ModifiedBy VARCHAR(20)
)
BEGIN
	DECLARE oldModifiedDate1 VARCHAR(30);
	DECLARE oldModifiedDate2 VARCHAR(30);
	DECLARE result_code INTEGER DEFAULT 0; -- 定义返回结果并赋初值0
	DECLARE update_code INTEGER DEFAULT 0;
	DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET result_code=1; -- 在执行过程中出任何异常设置result_code为1
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET result_code = 2; -- 如果表中没有下一条数据则置为2
	##有对应的异常时候会跳到这里执行语句，如果是CONTINUE 就会继续异常代码处向下执行
	SET oldModifiedDate1 = (SELECT ModifiedDate FROM emb_equipment WHERE ID = $EquipmentID);
	SET oldModifiedDate2 = (SELECT ModifiedDate FROM emb_equipmenttechnology WHERE TechnologyID = $TechnologyID);
	
	START TRANSACTION; -- 开始事务
	UPDATE emb_equipment 
     SET EquipmentName = $EquipmentName,
				 EquipmentClassID = $EquipmentClassID,
				 OrganiseUnitID = $OrganiseUnitID,
				 DepartMentID = $DepartMentID,
				 ManufacturerID = $ManufacturerID,
				 PlaceOrigin = $PlaceOrigin,
				 EquipmentModelID = $EquipmentModelID,
				 PurposeClassID = $PurposeClassID,
				 Standard = $Standard,
				 SupplierID = $SupplierID,
				 MaintenanceID = $MaintenanceID,
				 Cost = $Cost,
				 Custodian = $Custodian,
				 Recipient = $Recipient,
				 Region = $Region,
				 Positions = $Positions,
				 Longitude = $Longitude,
				 Latitude = $Latitude,
				 EAMCode = $EAMCode,
				 ERPCode = $ERPCode,
				 OtherCode = $OtherCode,
				 Detaile = $Detaile,
				 Resume = $Resume,
				 ModifiedDate = $ModifiedDate,
				 ModifiedBy = $ModifiedBy
		WHERE ID = $EquipmentID;

	UPDATE emb_equipmenttechnology 
		 SET PurchaseMode = $PurchaseMode,
				 BuyDate = $BuyDate,
				 GuaranteeDate = $GuaranteeDate,
				 UseTime = $UseTime,
				 AgeLimit = $AgeLimit,
				 ForceTimeLength = $ForceTimeLength,
				 DailyTimeLength = $DailyTimeLength,
				 RunTimeLength = $RunTimeLength,
				 RunLife = $RunLife,
				 TotalRunTimeLength = $TotalRunTimeLength,
				 RunStatus = $RunStatus,
				 ModifiedDate = $ModifiedDate,
				 ModifiedBy = $ModifiedBy
		WHERE TechnologyID = $TechnologyID;

	
  	IF (result_code = 1 or oldModifiedDate1 is null or oldModifiedDate2 is null or $ModifiedDate is null or oldModifiedDate1 = $ModifiedDate or oldModifiedDate2 = $ModifiedDate or oldModifiedDate1 = '' or oldModifiedDate2 = '') THEN -- 可以根据不同的业务逻辑错误返回不同的result_code，这里只定义了1和0
  		ROLLBACK; 
  	ELSE 
 		COMMIT; 
		SET update_code = 1;
  	END IF;
SELECT update_code;
END

调用存储过程：

CALL EMB_Equipment_UpdateInfo('1','1','设备名称', '1', '123', '123','123', '设备产地', '123', '123', '规格', '123', '123', '123','设备保管人', '设备使用人', '123', '设备存放位置', '123', '123','code', 'code','code', 'code', '功能描述','设备说明,备注', '1','2019-12-23','2019-12-23', '2019-12-24 00:00:00', '1', '1', '1','1', '1', '1', '2020-02-13 23:49:47','123')

在mybatis中进行使用（注意使用select标签，目的是获取到更新后的结果。）：

 <select id="updateEquipmentInfo" statementType="CALLABLE" resultType="int">
        call EMB_Equipment_UpdateInfo(
        #{EquipmentID},
        #{TechnologyID},
        #{EquipmentName},
        #{EquipmentClassID},
        #{OrganiseUnitID},
        #{DepartMentID},
        #{ManufacturerID},
        #{PlaceOrigin},
        #{EquipmentModelID},
        #{PurposeClassID},
        #{Standard},
        #{SupplierID},
        #{MaintenanceID},
        #{Cost},
        #{Custodian},
        #{Recipient},
        #{Region},
        #{Positions},
        #{Longitude},
        #{Latitude},
        #{EAMCode},
        #{ERPCode},
        #{OtherCode},
        #{Detaile},
        #{Resume},
        #{PurchaseMode},
        #{BuyDate},
        #{GuaranteeDate},
        #{UseTime},
        #{AgeLimit},
        #{ForceTimeLength},
        #{DailyTimeLength},
        #{RunTimeLength},
        #{RunLife},
        #{TotalRunTimeLength},
        #{RunStatus},
        #{ModifiedDate},
        #{ModifiedBy})
    </select>


创建临时表
CREATE  PROCEDURE `Common_Sys_StrToTable`(
	$str LONGTEXT, 
  $FirstSplit varchar(2),					-- 第一个分割符  用于分割行
	$SecondSplit varchar(2)					-- 第二个分割符  用于分割列
)
    COMMENT '公共工具类_双字符分割字符串拆分为二维表'
BEGIN
-- ------------------------------------
-- 用途：公共工具类_双字符分割字符串拆分为二维表
-- 项目名称：Common_Sys_StrToTable
-- 说明：$FirstSplit  第一个分割符  用于分割行
--       $SecondSplit 第二个分割符  用于分割列
-- 作者：GL
-- 时间：2020/01/06 12:07:13
-- ------------------------------------
	DECLARE $TableCount int ;													-- 获取一级表中总数量
	DECLARE $TableRowCount int DEFAULT 1;							-- 获取一级表循环遍历次数
	DECLARE $RowTemp LONGTEXT;												-- 获取一级表待拆分列临时数据
	DECLARE $ColumnCount int DEFAULT 0;								-- 获取二维表列数数量
	DECLARE $ColumnRowCount INT DEFAULT 0;						-- 获取二维表循环遍历次数
	DECLARE $ColumnStr varchar(50) DEFAULT '';				-- 获取二维表列名的字符串

	-- 一维表用于存储一级拆分表
	DROP TEMPORARY TABLE IF EXISTS $FirstTable;
	CREATE TEMPORARY  TABLE $FirstTable
	(
		Id INT(16) NOT NULL PRIMARY KEY AUTO_INCREMENT,
		Val LONGTEXT DEFAULT NULL
	);

	-- 二维表用于存储最终结果表
	DROP TEMPORARY TABLE IF EXISTS $ColumnTable;
	CREATE TEMPORARY TABLE  $ColumnTable
	(
		Id INT(16) NOT NULL PRIMARY KEY AUTO_INCREMENT
	);
	

	-- 一维表中动态添加数据
	set @execsql= CONCAT("INSERT INTO $FirstTable (Val) VALUES ('",REPLACE($str,$FirstSplit,'''),('''),"')");
	prepare stem from @execsql;
	execute stem;
	DEALLOCATE PREPARE stem;
	set @execsql=NULL;

	-- 一维表中总数量
	SELECT COUNT(DISTINCT id) INTO $TableCount FROM $FirstTable;
	
	WHILE $TableRowCount <= $TableCount
	DO
			SELECT val INTO $RowTemp FROM $FirstTable WHERE id = $TableRowCount;

			IF($TableRowCount = 1)
			THEN
					SET $columnCount = LENGTH($RowTemp)-LENGTH(REPLACE($RowTemp,$SecondSplit,''));
					-- 动态创建列
					WHILE $ColumnRowCount <= $columnCount
					DO
						  SET @execsql = CONCAT(" alter table $ColumnTable add V",$ColumnRowCount,' varchar(50); ');
							prepare stem1 from @execsql;
							execute stem1;
							DEALLOCATE PREPARE stem1;
							set @execsql=NULL;
							SET $ColumnStr = CONCAT($ColumnStr,',V',$ColumnRowCount);
							SET $ColumnRowCount = $ColumnRowCount + 1;
					END WHILE;
					SET $ColumnStr = SUBSTRING($ColumnStr,2,LENGTH($ColumnStr));
			END IF;

			SET @execsql = CONCAT(" INSERT INTO $ColumnTable(",$ColumnStr,") VALUES ('",REPLACE($RowTemp,$SecondSplit,''','''),"');");
			
			prepare stem2 from @execsql;
			execute stem2;
			DEALLOCATE PREPARE stem2;
			SET @execsql=NULL;

			SET $TableRowCount = $TableRowCount + 1;

	END WHILE;
END