Postgresql update存储过程带参数

文章目录

• 修改数据的要求
• 存储过程中的数据库异常

• 我们需要数据库异常
• MariaDB 发起异常

• SIGNAL和RESIGNAL

• mariaDB 捕获异常

• 捕获指定异常
• 捕获自定义异常
• 获取异常消息

• update 锁及其测试

• Update 锁
• SELECT FOR UPDATE 测试

• 单条记录修改
• 多条记录修改

修改数据的要求

对于修改数据，我们需要做一个存储过程，要求如下：

• 我们根据查询出来的数据进行修改，在我查询出来到我提交到数据库服务器修改的这个时期内，如果有人已经改了，这个存储过程应该退回。如果我提交的修改是多条数据，存储过程还应该告诉我是哪些记录已经被改过了。
• 每一次修改都需要对要修改的记录进行行级锁定，确保不会出现多人同时修改一条记录的情况。
• 在锁定要修改的记录时，如果有记录已经被锁定了，存储过程应该返回错误并提示锁定失败，需要稍候处理。
• 如果修改过程有任何执行上的错误应该有事务回滚操作，确保修改操作的不可分割性。

存储过程中的数据库异常

我们需要数据库异常

在修改的存储过程中，执行修改的时候发现已经被其他用户修改过了，或者是锁表的时候失败，我们可以选择的做法有两种：

• 正常返回：通过 select “该数据已经被修改” ，然后退出存储过程。
• 引发异常：通过触发数据库异常，终止修改。

两种方式都可以达到目的。但是如果正常返回，我们在程序端使用事务同时执行多条SQL语句的时候，因为返回的时候数据库状态是正常的，promise编程就不能捕捉到异常，也因此不能及时回滚数据，这样就会导致我们不想要的结果。
因为上述修改数据要求里面，如果有不满足要求的情况，我们都希望后面的操作应该及时停止，已经修改的部分操作应该回滚。因此，对于不想要修改的情况应该发起数据库异常，而不是简单的返回修改不了的处理结果信息。

MariaDB 发起异常

在 MariaDB 中，可以使用 SIGNAL 语句在存储过程或函数中发起异常。SIGNAL 语句用于向当前会话或连接发送异常信息，并中断当前执行的语句。

举个例子，假设你希望在查询数据时遇到空值时发起异常，你可以这样做：

SELECT * FROM users WHERE name = '' OR name IS NULL;

IF FOUND_ROWS() > 0 THEN
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Name cannot be empty or null';
END IF;

在这个例子中，使用 SELECT 语句查询了 name 为空或为空值的行，然后使用 IF FOUND_ROWS() 语句判断是否有行被查询到。如果没有行被查询到，就使用 SIGNAL 语句发起异常，并设置错误代码和错误信息。

注意，在使用 SIGNAL 语句时，需要指定错误代码（即 SQLSTATE 参数）。错误代码是一个五位字符串，其中前两位表示错误类型，后三位表示错误的细分类别。常用的错误代码有：
使
用 SIGNAL 语句的语法如下：

SIGNAL [SQLSTATE value] SET MESSAGE_TEXT = 'error message';

其中：

• SQLSTATE value 是错误代码，是一个五位字符串，用于表示错误的类型和细分类别。
• MESSAGE_TEXT 是错误信息，是一个字符串，用于说明错误的原因。

通常自定义数据库异常的代码从45000开始，比如45001，45002…

SIGNAL和RESIGNAL

SIGNAL 和 RESIGNAL 是 MySQL 和 MariaDB 中的语句，用于在存储过程中处理异常。

• SIGNAL 语句用于引发一个异常。它可以指定异常的信息，例如错误编号和错误信息。例如：

IF (error_condition) THEN
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Error: Something went wrong';
END IF;

• RESIGNAL 语句用于重新引发已经处理的异常。它可以指定新的异常信息，但不会更改错误编号。例如：

HANDLER exit_handler
BEGIN
  ROLLBACK;
  RESIGNAL;
END;

在这个例子中，RESIGNAL 语句会重新引发已经处理的 exit_handler 异常。

总的来说，SIGNAL 语句用于引发新的异常，而 RESIGNAL 语句用于重新引发已经处理的异常。它们的区别在于，RESIGNAL 语句不会更改错误编号，而 SIGNAL 语句可以指定新的错误编号。

mariaDB 捕获异常

在 MariaDB 中，可以使用 DECLARE 和 HANDLER 语句来捕获异常。基本操作如下：

DECLARE EXIT HANDLER FOR exception_type
BEGIN
  -- code to handle error goes here
END;

其中:

• HANDLER 是一种类型的处理程序，它在存储过程中发生特定类型的错误时调用。在 DECLARE EXIT HANDLER FOR 语句中，EXIT HANDLER 用于声明一个处理程序，该处理程序将在存储过程中发生特定类型的错误时调用，并且调用完处理代码之后，立刻退出存储过程，后面的代码不会继续执行。如果希望在存储过程中遇到错误时继续执行代码，而不是立即退出，则可以使用 CONTINUE HANDLER 语句。它的基本格式与 DECLARE EXIT HANDLER FOR 相同，只是在 DECLARE 关键字后面添加了 CONTINUE 关键字。比如以下代码在处理异常之后，继续从异常处执行剩余代码：

DECLARE CONTINUE HANDLER FOR  SQLEXCEPTION
BEGIN
  -- code to handle error goes here
END

• exception_type 是要捕获的异常类型。MySQL 中的可用异常类型包括 SQLWARNING、NOT FOUND 和 SQLEXCEPTION。

• SQLWARNING：当 MySQL 遇到此类错误时，将调用声明的处理程序。SQLWARNING 错误通常用于表示发生的错误是轻微的，并且不会导致操作失败。
• NOT FOUND：当 MySQL 遇到此类错误时，将调用声明的处理程序。NOT FOUND 错误通常用于表示在存储过程中没有找到所请求的行。
• SQLEXCEPTION：当 MySQL 遇到此类错误时，将调用声明的处理程序。SQLEXCEPTION 错误通常用于表示发生的错误是不可恢复的，并且需要立即终止当前操作。

捕获SQL异常并执行回滚的代码示例：

DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
   	    ROLLBACK;    		   	   
    END;

捕获指定异常

除了捕获SQL的一般性错误之外，也可以捕获指定的错误，或是我们自己定义的错误，比如：

DECLARE EXIT HANDLER FOR 1205

错误代码 1205 表示的是锁定超时错误，这通常发生在在存储过程中使用了 SELECT … FOR UPDATE 或者 SELECT … LOCK IN SHARE MODE 语句时，但是目标行被另一个事务锁定了。

捕获自定义异常

前面我们自定义SQL错误，并指定SQLSTATE，这种类型的自定义异常也是可以捕获的，比如：

DECLARE EXIT HANDLER FOR SQLSTATE '45002';
BEGIN
   ROLLBACK; 
END;

其中，SQLSTATE ‘45002’ 就是我们上面用如下语句定义的：

SIGNAL SQLSTATE '45002' SET MESSAGE_TEXT = ‘不是最新版本数据’

我们通过捕获这个异常，执行回滚操作。

获取异常消息

GET CURRENT DIAGNOSTICS 是一个 MySQL 和 MariaDB 中的语句，用于获取有关处理异常的诊断信息。

其中，CONDITION 1 表示获取的是异常的第一个条件。

例如，在处理异常时，可以使用以下语句获取异常的相关信息：

GET CURRENT DIAGNOSTICS CONDITION 1
@error_number = MYSQL_ERRNO,
@error_message = MESSAGE_TEXT;

这样，就可以使用 @error_number 和 @error_message 变量访问异常的错误编号和错误信息。

注意，GET CURRENT DIAGNOSTICS 语句可以获取其他相关信息，例如 SQLSTATE 值、异常处理程序的名称等。可以使用 CONDITION n 参数指定要获取的信息的编号。

此外，可以使用 RESIGNAL 语句重新引发异常，以将异常传播到调用层。这样，就可以在不同的层中使用 GET CURRENT DIAGNOSTICS 语句获取异常的信息，并进行适当的处理。

代码示例如下：

DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
   	    ROLLBACK;    
		GET CURRENT DIAGNOSTICS CONDITION 1 msg = MESSAGE_TEXT;     
   	    RESIGNAL SET MESSAGE_TEXT = msg;		
    END;

以上代码捕获SQL普通异常，捕获后：

• 执行回滚
• 获取异常消息
• 重新发送异常信号
• 退出存储过程

update 锁及其测试

Update 锁

在 MariaDB 和 MySQL 中，使用 UPDATE 语句更新表中的记录时，有时会锁定行或表，以确保数据的一致性和完整性。

• 当使用带有 FOR UPDATE 或 FOR SHARE 选项的 SELECT 语句锁定行时，使用 UPDATE 语句更新这些行时也会锁定这些行。这种情况下，只有在事务提交或回滚后，锁定的行才会被释放。例如：

START TRANSACTION;

SELECT * FROM my_table WHERE id = 1 FOR UPDATE;
UPDATE my_table SET value = 'new value' WHERE id = 1;

COMMIT;

在这个例子中，使用 SELECT 语句锁定了 my_table 表的第一行，然后使用 UPDATE 语句更新了这一行。在事务提交后，这一行才会被释放。

SELECT FOR UPDATE 测试

为了更加清晰，我们可以对update 锁行的操作进行测试：
1、在HeidiSQL中，执行如下语句开始一个事务，锁定id=51的行，并且不执行commit，代码如下：

start TRANSACTION;
SELECT COUNT(1) FROM  user  WHERE id=51 FOR UPDATE;

2、保留HeidiSQL程序，从桌面再次打开HeidiSQL（注意这个地方要再次打开HeidiSQL，也就是系统中需要打开多个HeidiSQL进程），打开user表的数据，找到id为51的数据，手动修改age的值，如下图：

结果修改失败。HeidiSQL会进入等待，几分钟后发出Lock wait timeout exceeded; try restarting transaction 的错误。表明id=51的记录被锁。

3、回到上一个HeidiSQL程序进程，提交commit之后，我们可以看到其他进程变成可以修改了。

单条记录修改

基于以上设计更新一条记录的存储过程baseUpdateOne，实现的功能是：

• 根据传过来的updatedAt，检查是否已经被改动过（该updatedAt数据在数据库中是否还存在），如果已经被改动，放弃sqlState为45002的异常。并退出存储过程。
• 如果存在，使用SELECT FOR UPDATE NOWAIT 排他锁锁定记录，并开启事务进行修改。
• 采用Sails 组织修改的内容（在nodejs代码里面实现），无论修改什么数据，都会同时把该行记录的updateAt重置为当前的时间戳。

具体代码如下：

DROP PROCEDURE IF EXISTS `baseUpdateOne`;
CREATE DEFINER=`root`@`localhost` PROCEDURE `baseUpdateOne`(
	IN `tableName` VARCHAR(200),
	IN `oldVer` BIGINT,
	IN `criteria` VARCHAR(2000),
	IN `valuesToSet` VARCHAR(3000)
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT '修改单一记录，如果当前版本不一致，就触发错误'
TOP:BEGIN
	DECLARE msg TEXT;
	DECLARE EXIT HANDLER FOR 1205 
	BEGIN
   		ROLLBACK;      	
		SIGNAL SQLSTATE '45003' SET MESSAGE_TEXT = '更新锁定失败，可能已经被其他用户锁定，请稍候再试'; 	
   	END;
   
   	DECLARE EXIT HANDLER FOR SQLEXCEPTION
   	BEGIN
   		ROLLBACK;    
		GET CURRENT DIAGNOSTICS CONDITION 1 msg = MESSAGE_TEXT;     
   		RESIGNAL SET MESSAGE_TEXT = msg;		
   	END;
   	
	IF LENGTH(TRIM(tableName)) = 0 THEN
		SELECT '表名称不能为空';
		LEAVE TOP;
	END IF;
	
	IF LENGTH(TRIM(oldVer)) = 0 THEN
		SELECT '请输入旧的updatedAt';
		LEAVE TOP;
	END IF;
	
	IF LENGTH(TRIM(criteria)) = 0 THEN
		SELECT '不能无条件更新';
		LEAVE TOP;
	END IF;
	
	# 事务开始
	START TRANSACTION; 
	
	SET @findStr = CONCAT('SELECT COUNT(1) INTO @idCount FROM ', tableName,' WHERE `updatedAt`=? and ',criteria,' FOR UPDATE NOWAIT');	
	SET @updateStr = CONCAT('UPDATE  ', tableName,' SET ',valuesToSet,' where ',criteria);

	# 开始检查是否被更新过	
	PREPARE stmt FROM @findStr;
	EXECUTE stmt USING oldVer;
	DEALLOCATE PREPARE stmt;

    IF @idCount = 0 THEN
	 	ROLLBACK;
		SIGNAL SQLSTATE '45002' SET MESSAGE_TEXT = oldVer;        
    	LEAVE TOP;
    ELSEIF @idCount > 1 THEN 
    	ROLLBACK;
    	SIGNAL SQLSTATE '45004' SET MESSAGE_TEXT = '查询结果不唯一，不能使用baseUpdateOne';
    ELSE 
    	PREPARE updateST FROM @updateStr;
		EXECUTE updateST ;	
    	DEALLOCATE PREPARE updateST;
      	COMMIT;
    END IF;	
END

多条记录修改

有时候我们需要同时更新多条数据记录，比如我们需要更新用户表里面age大于35的人的数据，这样我们就需要设计更新多条记录的存储过程checkThenUpdate，因为我们需要确保每次update和我们查询之间这段时间内没有人改过数据，因此我们在更新多条记录的时候，我们必须提交涉及到的所有记录的updatedAt数据，多条记录的updateAt数据里面只要有条记录是被改过的，我们就需要发起异常并且在异常中返回已经被修改过的数据。
为了实现这个功能，我们需要先做一个存储过程，这个存储过程用来对一个用逗号分隔的多个updatedAt数据进行检查，如果有被更新过的，就写入零时表，最后发起异常，并且把被更新过的updatedAt数据通过异常的Message_text返回。如果都没有被更新过，返回所有updatedAt数据，这个存储过程为checkUpdateArray，代码如下：

DROP PROCEDURE IF EXISTS `checkUpdateArray`;

CREATE DEFINER=`root`@`localhost` PROCEDURE `checkUpdateArray`(
	IN `tableName` VARCHAR(200),
	IN `oldData` VARCHAR(10000)
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT '检查一系列的updatedAt，返回哪些数据已经被修改'

TOP: BEGIN
	# 初始化变量申明
	DECLARE endSign INT DEFAULT 0;
	DECLARE curVer BIGINT DEFAULT 0; 
	DECLARE verData CURSOR FOR	SELECT * FROM tmpStrs FOR UPDATE; 
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET endSign=1;
	# 简单的参数检查
	IF LENGTH(TRIM(tableName)) = 0 THEN
		SELECT '表名称不能为空'; 
		LEAVE TOP; 
	END IF;
	IF LENGTH(TRIM(oldData)) = 0 THEN
		SELECT '旧数据的updatedAt不能为空'; 
		LEAVE TOP; 
	END IF;

	# 分解用逗号串起来的旧数据的updatedAt	
	SPLIT:BEGIN 
		SET @INDEX=-1;#逗号的位置
		DROP TEMPORARY TABLE IF EXISTS tmpStrs;
		CREATE TEMPORARY TABLE tmpStrs(
			str BIGINT 
		);
		SET @INDEX = LOCATE(',',oldData); 
		
		WHILE @INDEX > 0 DO
			INSERT INTO tmpStrs VALUES (LEFT(oldData,@INDEX - 1)); 
			SET oldData=SUBSTR(oldData FROM @INDEX + 1); 
			SET @INDEX = LOCATE(',',oldData); 
		END WHILE;
		
		IF LENGTH(oldData)>=0 THEN
			INSERT INTO tmpStrs VALUES (oldData); 
		END IF; 
	END SPLIT;
	# 检查所有带过来的旧记录，看看里面有没有已经被修改过的数据（updatedAt 不一致）
	OPEN verData;
	DROP TEMPORARY TABLE IF EXISTS tmpReturns;
	CREATE TEMPORARY TABLE tmpReturns(
		updatedAt BIGINT,
		`exist` TINYINT 
	); 
	SET @sqlStr = CONCAT('insert into tmpReturns (select ?,count(1) from ', tableName, ' where `updatedAt`=?)'); 
	PREPARE stmt FROM @sqlStr;
	# 遍历所有updatedAt,检查出已经被修改的updatedAt的值
	read_loop:LOOP 
		FETCH verData INTO curVer;
		IF endSign=1 THEN 
			LEAVE read_loop; 
		END IF; 
		EXECUTE stmt USING curVer,curVer; 
	END LOOP; 
	
	DEALLOCATE PREPARE stmt; 
	CLOSE verData;
	DROP TEMPORARY TABLE IF EXISTS tmpStrs;
	
	SELECT COUNT(1) FROM tmpReturns WHERE `exist`=0 INTO @noExists;
	IF @noExists>0 THEN 
		SET @result = (SELECT GROUP_CONCAT(`updatedAt` SEPARATOR ',') FROM tmpReturns WHERE `exist`=0); 
		DROP TEMPORARY TABLE IF EXISTS tmpReturns; 
		# 引发错误
		SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = @result;		
		LEAVE TOP; 
	END IF;
	# 返回通过检查的数据
	SELECT `updatedAt` FROM tmpREturns;
	DROP TEMPORARY TABLE IF EXISTS tmpReturns;	
END

有了这个存储过程，我们更新多条数据的时候，可以先调用该存储过程，如果没有异常就往下执行，如果有异常就停止执行，这个存储过程checkThenUpdate代码如下：

DROP PROCEDURE IF EXISTS `checkThenUpdate`;
CREATE DEFINER=`root`@`localhost` PROCEDURE `checkThenUpdate`(
	IN `tableName` VARCHAR(200),
	IN `oldData` VARCHAR(10000),
	IN `criteria` VARCHAR(10000),
	IN `valuesToSet` VARCHAR(10000)
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT '检查要修改的数据是否被改过，如果没有就修改（多条）'
TOP:BEGIN
	DECLARE msg TEXT;
	DECLARE EXIT HANDLER FOR 1205 
	BEGIN
   		ROLLBACK;      	
		SIGNAL SQLSTATE '45003' SET MESSAGE_TEXT = '更新锁定失败，可能已经被其他用户锁定，请稍候再试'; 	
   	END;
   
   	DECLARE EXIT HANDLER FOR SQLEXCEPTION
   	BEGIN
   		ROLLBACK;    
		GET CURRENT DIAGNOSTICS CONDITION 1 msg = MESSAGE_TEXT;     
   		RESIGNAL SET MESSAGE_TEXT = msg;		
   	END;
   
   	# 简单的参数检查
	IF LENGTH(TRIM(tableName)) = 0 THEN
  		SELECT '表名称不能为空';
		LEAVE TOP;
	END IF;
	
	IF LENGTH(TRIM(oldData)) = 0 THEN
		SELECT '请输入旧的updatedAt';
		LEAVE TOP;
	END IF;
	
	IF LENGTH(TRIM(criteria)) = 0 THEN
		SELECT '不能无条件更新';
		LEAVE TOP;
	END IF;

	
	CALL checkUpdateArray(tableName,oldData);
	
	# 查询指定条件的关联id
	SET @idsSql = CONCAT('SELECT GROUP_CONCAT(`id` SEPARATOR \',\') INTO @ids FROM ',tableName,' WHERE `updatedAt` in (',oldData,') and ',criteria);

	PREPARE idsST FROM @idsSql;
	EXECUTE idsST;
	DEALLOCATE PREPARE idsST;	
	# 事务开始
	START TRANSACTION; 
	
	SET @findStr = CONCAT('SELECT `id`  FROM  ', tableName,' WHERE  `id` in (',@ids,') FOR UPDATE NOWAIT');
	SET @updateStr = CONCAT('UPDATE  ', tableName,' SET ',valuesToSet,' WHERE  `id` in (',@ids,')');
	# 行级锁定
	PREPARE stmt FROM @findStr;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;

	
	PREPARE updateST FROM @updateStr;
	EXECUTE updateST;
	DEALLOCATE PREPARE updateST;
	COMMIT;
END