mysql - 使用游标循环的MYSQL存储过程在IN Select函数中不起作用

Question

我有一个 SP 函数，它将传递 ID 和逗号分隔值以插入新行
现在我有一个游标选择多个帖子，其中 ID =（逗号分隔值）是一个动态变量

这是我第一个获取逗号分隔 ID 值的存储过程

CREATE DEFINER=`root`@`%` PROCEDURE `getGalleryItems`()
BEGIN
DECLARE postID int default 0;
DECLARE uData varchar(1024);
   DECLARE done INT DEFAULT 0;
   
   DECLARE cur CURSOR FOR
(SELECT post_id, unserializeData(meta_value)
      FROM wp_postmeta AS meta
      LEFT JOIN wp_posts AS post ON meta.post_id = post.ID
      WHERE meta.meta_key LIKE 'image_gallery' LIMIT 2);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;



   OPEN cur;
   
   REPEAT
       FETCH cur INTO postID, uData;
    if ! done then
    call insertGalleryImages(postID,uData);

       end if;

   UNTIL done  = 1 END REPEAT;
   CLOSE cur;
END

然后值传入的第二个 SP 是这样的

CREATE DEFINER=`root`@`%` PROCEDURE `insertGalleryImages`(in parentID int, in objectID varchar(1024))
BEGIN
DECLARE postID bigint(20) default 0;
DECLARE postTitle text default null;
DECLARE postExcerpt text default null;
DECLARE postContent longtext default null;
   DECLARE done INT DEFAULT 0;
   DECLARE cntr INT DEFAULT 0;
   
   DECLARE cur CURSOR FOR
(SELECT ID, post_title, post_excerpt, post_content 
FROM wp_posts WHERE ID IN (objectID));
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
   
   OPEN cur; 
   
   REPEAT
       FETCH cur INTO  postID, postTitle, postExcerpt, postContent;
       if ! done then
INSERT INTO `wp_postmeta` (`post_id`, `meta_key`, `meta_value`) VALUES
(parentID, concat("gallery_items_", cntr, "_title"), postTitle),
(parentID, concat("_gallery_items_", cntr, "_title"), 'field_5cac53504a85f'),
(parentID, concat("gallery_items_", cntr, "_item_type"), 'image'),
(parentID, concat("_gallery_items_", cntr, "_item_type"), 'field_5d1d6e1497252'),
(parentID, concat("gallery_items_", cntr, "_image"), postID),
(parentID, concat("_gallery_items_", cntr, "_image"), 'field_5cac53434a85e'),
(parentID, concat("gallery_items_", cntr, "_credit"), postExcerpt),
(parentID, concat("_gallery_items_", cntr, "_credit"), 'field_5cac53884a861'),
(parentID, concat("gallery_items_", cntr, "_description"), postContent),
(parentID, concat("_gallery_items_", cntr, "_description"), 'field_5cac53594a860');

SET cntr = cntr + 1;
       end if;
   UNTIL done END REPEAT;
   CLOSE cur;
   
      INSERT INTO `wp_postmeta` (`post_id`, `meta_key`, `meta_value`) VALUES
(parentID, 'gallery_items', cntr),
(parentID, '_gallery_items', 'field_5cac4ee94a85d');
   
END

现在我尝试调试这个，我将第二个过程对象 ID 设置为静态。循环运行良好，但是当我使用要传递的变量时。它没有按应有的方式循环，希望有人能帮助我解决这个问题，谢谢！

标签： mysqlstored-procedures

Answer 1

顺便说一句，我通过操纵传入的变量来将表视为prepared statement 并从那里选择来解决这个问题

显然光标不能动态工作，因为它应该是

这是我应用的代码

CREATE DEFINER=`root`@`%` PROCEDURE `insertGalleryImages`(in parentID int, in objectID varchar(1024))
BEGIN
DECLARE postID bigint(20) default 0;
DECLARE postTitle text default null;
DECLARE postExcerpt text default null;
DECLARE postContent longtext default null;
   DECLARE done INT DEFAULT 0;
   DECLARE cntr INT DEFAULT 0;
   
   DECLARE cur CURSOR FOR
(SELECT ID, post_title, post_excerpt, post_content 
FROM vw_myproc);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

SET @select = concat('CREATE VIEW vw_myproc as SELECT * FROM wp_posts WHERE ID IN(', objectID, ')');
    PREPARE stm FROM @select;
    EXECUTE stm;
    DEALLOCATE PREPARE stm;

    SET @select = concat('SELECT * FROM wp_posts WHERE ID IN (', objectID, ')');
    PREPARE stm FROM @select;
    EXECUTE stm;
    DEALLOCATE PREPARE stm;
   
   OPEN cur; 
   
   REPEAT
       FETCH cur INTO  postID, postTitle, postExcerpt, postContent;
       if ! done then
INSERT INTO `wp_postmeta` (`post_id`, `meta_key`, `meta_value`) VALUES
(parentID, concat("gallery_items_", cntr, "_title"), postTitle),
(parentID, concat("_gallery_items_", cntr, "_title"), 'field_5cac53504a85f'),
(parentID, concat("gallery_items_", cntr, "_item_type"), 'image'),
(parentID, concat("_gallery_items_", cntr, "_item_type"), 'field_5d1d6e1497252'),
(parentID, concat("gallery_items_", cntr, "_image"), postID),
(parentID, concat("_gallery_items_", cntr, "_image"), 'field_5cac53434a85e'),
(parentID, concat("gallery_items_", cntr, "_credit"), postExcerpt),
(parentID, concat("_gallery_items_", cntr, "_credit"), 'field_5cac53884a861'),
(parentID, concat("gallery_items_", cntr, "_description"), postContent),
(parentID, concat("_gallery_items_", cntr, "_description"), 'field_5cac53594a860');

SET cntr = cntr + 1;
       end if;
   UNTIL done END REPEAT;
   CLOSE cur;
   
      INSERT INTO `wp_postmeta` (`post_id`, `meta_key`, `meta_value`) VALUES
(parentID, 'gallery_items', cntr),
(parentID, '_gallery_items', 'field_5cac4ee94a85d');
   
END

来自这个问题的参考：MySQL 存储过程游标用于准备好的语句