mysql存储过程之内存数据

DELIMITER //

CREATE PROCEDURE itsm_order_procedure(IN param_array TEXT, IN page_size INT, IN page_number INT)
BEGIN
– 创建临时表
CREATE TEMPORARY TABLE temp_table (
temp_uuid VARCHAR(32)
);

– 添加索引
CREATE INDEX idx_temp_table_uuid ON temp_table (temp_uuid);

– 解析参数数组并插入数据到临时表
SET @sql = CONCAT(‘INSERT INTO temp_table (temp_uuid) VALUES ‘, param_array);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

– 关联查询并进行分页
SET @start_row = (page_number - 1) * page_size;
SET @limit_clause = CONCAT(‘LIMIT ‘, @start_row, ‘, ‘, page_size);

SET @sql = CONCAT(‘SELECT c.uuid, c.order_id, c.order_status, c.title
FROM temp_table t
LEFT JOIN itsm_order_view c ON t.temp_uuid = c.uuid ‘, @limit_clause);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

– 删除临时表
DROP TEMPORARY TABLE temp_table;
END //

DELIMITER ;

本作品采用《CC 协议》，转载必须注明作者和本文链接