Início / user-3220113

Walter A's questions

Martin Hope
Walter A
Asked: 2024-05-14 21:44:22 +0800 CST
  • 5

Antes de confirmar alterações com um script mysql em um banco de dados, desejo realizar verificações adicionais. Quero reverter uma transação, quando a contagem de registros retornados por uma instrução sql não estiver entre os limites indicados na chamada. O procedimento deve ser chamado como

CALL my_database.checkSQLCount(
    "SELECT count(*) FROM my_table WHERE field1='xxx'",
30,
35);

Fiz um procedimento semelhante para verificar o número de exclusões/atualizações/inserções:

DELIMITER //
 
DROP PROCEDURE IF EXISTS my_database.checkAffectedRows;
CREATE PROCEDURE my_database.checkAffectedRows(IN sqlStatement VARCHAR(1000), IN minAffectedRows INT, IN maxAffectedRows INT)
BEGIN
DECLARE success INT;
DECLARE affectedRows INT;
 
SET @sql := sqlStatement;
-- Print current SQL statement
SELECT @sql AS 'SQL statement:';

-- Execute the provided SQL statement
PREPARE stmt FROM @sql;
EXECUTE stmt;
SELECT ROW_COUNT() INTO affectedRows;

DEALLOCATE PREPARE stmt;
 
-- If the affected rows are outside the specified range, set the success flag to 0
IF affectedRows < minAffectedRows OR affectedRows > maxAffectedRows THEN
  SELECT CONCAT('ERROR: ', affectedRows, ' not between ',  minAffectedRows, ' and ', maxAffectedRows)
    AS 'Check expected nr updates';
  SET success := 0;
ELSE
  SELECT CONCAT('OK: ', affectedRows, ' is between ',  minAffectedRows, ' and ', maxAffectedRows)
    AS 'Check expected nr updates';
  SET success := 1;
END IF;
 
-- Insert the result into the CallResults table
INSERT INTO CallResults (success) VALUES (success);
END //
 
DROP PROCEDURE IF EXISTS my_database.handleCallResults;
CREATE PROCEDURE my_database.handleCallResults()
BEGIN
-- Check if any of the calls failed, if so, rollback the transaction
IF (SELECT COUNT(*) FROM CallResults WHERE success = 0) > 0 THEN
  ROLLBACK;
  SELECT 'Transaction rolled back' AS '=== Data patch end result ===';
ELSE
  -- No Rollback for above calls? Commit!
  COMMIT;
  SELECT 'Transaction committed' AS '=== Data patch end result ===';
END IF;
END //
 
DELIMITER ;
 
-- Start transaction
START TRANSACTION;
 
-- Update table but rollback when amount of updates is not between 1 and 100
CALL my_database.checkAffectedRows(
  "UPDATE myTable SET value='xxx' WHERE idMyTable < 100",
  1,100);

Eu tentei um procedimento como este:

DROP PROCEDURE IF EXISTS myDatabase.checkSQLCount;
CREATE PROCEDURE myDatabase.checkSQLCount(IN countQuery VARCHAR(1000), IN minCount INT, IN maxCount INT)
BEGIN
DECLARE success INT;
DECLARE rowCount INT;
 
SELECT countQuery AS 'SQL statement:';

-- Execute the provided SQL statement without fetching results
SET @stmt = countQuery;
PREPARE stmt FROM @stmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
 
-- Retrieve the number of affected rows using FOUND_ROWS() or with ROW_COUNT()
SELECT FOUND_ROWS() INTO rowCount;
 
-- If the matched rows are outside the specified range, set the success flag to 0
IF rowCount < minCount OR rowCount > maxCount THEN
  SELECT CONCAT('ERROR: ', rowCount, ' not between ',  minCount, ' and ', maxCount)
 AS 'Check expected nr matched rows';
  SET success := 0;
ELSE
  SELECT CONCAT('OK: ', rowCount, ' is between ',  minCount, ' and ', maxCount)
    AS 'Check expected nr matched rows';
  SET success := 1;
END IF;

-- Insert the result into the CallResults table
INSERT INTO CallResults (success) VALUES (success);
END //

Quando eu chamo isso com

CALL my_database.checkSQLCount(
  "SELECT * FROM my_table WHERE field1='xxx'",
  30,
  35);

a saída mostrará todas as linhas selecionadas, que não quero ver. E quando eu mudo para

CALL my_database.checkSQLCount(
    "SELECT count(*) FROM my_table WHERE field1='xxx'",
30,
35);

o resultado é que encontrei um registro (contendo a contagem)

mysql