我正在 maria DB 上对其进行测试，并且工作正常。
CREATE PROCEDURE DeleteProcedure(IN LogTableName VARCHAR(100),IN TableName VARCHAR(100),in InputValues varchar(1000))
ProcLabel:BEGIN
-- Local Variable
DECLARE log_column_names,column_names,current_column_name,current_input_value,column_names_repeat,input_values_found,input_value_params VARCHAR(1000);
DECLARE table_key_name,this_table_name,table_key_data_type VARCHAR(100);
DECLARE column_start_pos,column_comma_pos,column_loop_count,row_start_pos,row_comma_pos,row_loop_count,total_column_loop_count,input_values_found_length SMALLINT;
DECLARE column_end_loop,row_end_loop,table_key_data_type_char TINYINT;
-- Declare Condition
DECLARE TableNotFound CONDITION for 1146 ;
-- Exit Handler
DECLARE EXIT HANDLER FOR TableNotFound
BEGIN
SELECT CONCAT('Table ',@this_table_name,' Are Not Found') MESSAGE_TEXT;
END;
-- Set Array Column
SET @this_table_name = TableName;
SET @SQL = "SELECT GROUP_CONCAT(column_name SEPARATOR ',') FROM information_schema.columns WHERE table_name = ? INTO @column_names";
PREPARE stmt FROM @SQL;
EXECUTE stmt USING @this_table_name;
SET @this_table_name = LogTableName;
SET @SQL = "SELECT GROUP_CONCAT(column_name SEPARATOR ',') FROM information_schema.columns WHERE table_name = ? AND column_key != 'PRI' AND column_name != 'create_at' INTO @log_column_names";
PREPARE stmt FROM @SQL;
EXECUTE stmt USING @this_table_name;
SET @SQL = "SELECT column_name,data_type FROM information_schema.columns WHERE table_name = ? AND column_key = 'PRI' INTO @table_key_name,@table_key_data_type";
PREPARE stmt FROM @SQL;
EXECUTE stmt USING TableName;
SELECT IF(LOWER(SUBSTRING(@table_key_data_type,-3,3) != 'int'),TRUE,FALSE) INTO @table_key_data_type_char;
SET @SQL = CONCAT("INSERT INTO ",LogTableName," (",@log_column_names,") VALUES");
-- Set Default Value Before Loop
SET @SQLVAL = '';
SET @row_start_pos = 1;
SET @row_comma_pos = LOCATE(',',InputValues);
SET @row_loop_count = 0;
SET @total_column_loop_count = 0;
SET @input_values_found = '';
SET @input_value_params = InputValues;
InputValueRepeat:REPEAT
IF @row_comma_pos > 0 THEN
SET @current_input_value = SUBSTRING(InputValues,@row_start_pos,@row_comma_pos - @row_start_pos);
SET @row_end_loop = 0;
ELSE
SET @current_input_value = SUBSTRING(InputValues,@row_start_pos);
SET @row_end_loop = 1;
END IF;
SET @SQLC = CONCAT("SELECT COUNT(1) FROM ",TableName," WHERE ",@table_key_name," = ",@current_input_value," INTO @record_found");
PREPARE stmtC FROM @SQLC;
EXECUTE stmtC;
DEALLOCATE PREPARE stmtC;
IF @record_found = 1 AND @row_loop_count > 0 AND @column_loop_count IS NOT NULL THEN
set @SQLVAL = CONCAT(@SQLVAL,",");
END IF;
IF @record_found = 1 THEN
SET @column_start_pos = 1;
SET @column_comma_pos = LOCATE(',@column_names);
SET @column_names_repeat = @column_names;
SET @column_loop_count = 0;
ColumnNameRepeat:REPEAT
IF @column_comma_pos > 0 THEN
SET @current_column_name = SUBSTRING(@column_names_repeat,@column_start_pos,@column_comma_pos - @column_start_pos);
SET @column_end_loop = 0;
ELSE
SET @current_column_name = SUBSTRING(@column_names_repeat,@column_start_pos);
SET @column_end_loop = 1;
END IF;
SET @SQLSV = CONCAT("SELECT ",@current_column_name," FROM "," = ? INTO @current_column_value");
PREPARE stmtSV FROM @SQLSV;
EXECUTE stmtSV USING @current_input_value;
DEALLOCATE PREPARE stmtSV;
SET @SQLVAL = CONCAT(@SQLVAL,"('",CONCAT_WS("','",'D',@current_column_value,@current_input_value),"')");
IF @column_end_loop = 0 THEN
SET @column_names_repeat = substring(@column_names_repeat,@column_comma_pos + 1);
SET @column_comma_pos = LOCATE(',@column_names_repeat);
set @SQLVAL = CONCAT(@SQLVAL,");
END IF;
SET @column_loop_count = @column_loop_count + 1;
UNTIL @column_end_loop = 1
END REPEAT;
SET @total_column_loop_count = @total_column_loop_count + @column_loop_count;
IF @input_values_found != '' THEN
IF (@table_key_data_type_char = TRUE) THEN
SET @input_values_found = CONCAT_WS("',@input_values_found,@current_input_value);
ELSE
SET @input_values_found = CONCAT_WS(",@current_input_value);
END IF;
ELSE
SET @input_values_found = @current_input_value;
END IF;
END IF;
IF @row_end_loop = 0 THEN
SET InputValues = substring(InputValues,@row_comma_pos + 1);
SET @row_comma_pos = LOCATE(',InputValues);
ELSE
set @SQLVAL = CONCAT(@SQLVAL,";");
END IF;
SET @row_loop_count = @row_loop_count + 1;
UNTIL @row_end_loop = 1
END REPEAT;
IF (table_key_data_type_char = TRUE) THEN
SET @input_values_found = CONCAT("'","'");
END IF;
SELECT CHAR_LENGTH(CONCAT(@input_values_found)) INTO @input_values_found_length;
if @input_values_found_length < 3 AND @table_key_data_type_char = TRUE OR @table_key_data_type_char = FALSE AND @input_values_found_length < 1 THEN
SELECT CONCAT("Can't find input value : [",@input_value_params,"] on table ",TableName) MESSAGE_TEXT;
DEALLOCATE PREPARE stmt;
LEAVE ProcLabel;
END IF;
START TRANSACTION;
SET @SQL = CONCAT(@SQL,@SQLVAL);
PREPARE stmt FROM @SQL;
EXECUTE stmt;
SELECT ROW_COUNT() into @affected_row;
IF (@affected_row != @total_column_loop_count) THEN
SELECT CONCAT("Failed to insert data expected row : ",@column_loop_count,affected row : ",@affected_row) MESSAGE_TEXT;
ROLLBACK;
DEALLOCATE PREPARE stmt;
LEAVE ProcLabel;
END IF;
SET @affected_row = 0;
SET @SQL = CONCAT("DELETE FROM "," IN (",")");
PREPARE stmt FROM @SQL;
EXECUTE stmt;
SELECT ROW_COUNT() INTO @affected_row;
IF (@affected_row = 0) THEN
SELECT CONCAT("Failed to delete "," on record key ",@input_values_found) MESSAGE_TEXT;
ROLLBACK;
DEALLOCATE PREPARE stmt;
LEAVE ProcLabel;
END IF;
SELECT CONCAT("Success ",@affected_row," record on "," was deleted") MESSAGE_TEXT;
COMMIT;
DEALLOCATE PREPARE stmt;
END
我们需要像 Call DeleteProcedure('[to table]','[from table]','[array of keys to delete]'); => Call DeleteProcedure('log_history','orders','1,3,4');
一样调用这个 SP
所以我们可以选择尽可能多的使用逗号分隔符，例如第三个参数上的“1,2,3”。但是我还没有在超过一百个键上尝试过，因为第三个参数数据类型是 varchar(1000)。
我希望在不久的将来有人可以解决这个问题。
本文链接：https://www.f2er.com/371.html
