Advertisement
artur99

Untitled

May 18th, 2024
630
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 1.07 KB | None | 0 0
  1. CREATE TABLE fructe (
  2.   id INTEGER PRIMARY KEY,
  3.   list TEXT NOT NULL
  4. );
  5. INSERT INTO fructe VALUES (1, 'mere,pere,portocale');
  6. INSERT INTO fructe VALUES (2, 'banane,rosii,mere');
  7.  
  8. DELIMITER //
  9.  
  10. CREATE PROCEDURE UpdateList()
  11. BEGIN
  12.   DECLARE done INT DEFAULT 0;
  13.   DECLARE new_list TEXT;
  14.   DECLARE current_id INT;
  15.   DECLARE current_list TEXT;
  16.  
  17.   DECLARE cur CURSOR FOR SELECT id, list FROM fructe;
  18.   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
  19.  
  20.   OPEN cur;
  21.   read_loop: LOOP
  22.     FETCH cur INTO current_id, current_list;
  23.     IF done THEN
  24.       LEAVE read_loop;
  25.     END IF;
  26.  
  27.     SET new_list = '';
  28.    
  29.     WHILE LOCATE(',', current_list) > 0 DO
  30.       SET new_list = CONCAT(new_list, SUBSTRING_INDEX(current_list, ',', 1), '1,');
  31.       SET current_list = SUBSTRING(current_list FROM LOCATE(',', current_list) + 1);
  32.     END WHILE;
  33.  
  34.     SET new_list = CONCAT(new_list, current_list, '1');
  35.    
  36.     UPDATE fructe SET list = new_list WHERE id = current_id;
  37.   END LOOP;
  38.  
  39.   CLOSE cur;
  40. END //
  41.  
  42. DELIMITER ;
  43.  
  44.  
  45. CALL UpdateList();
  46.  
  47.  
  48. SELECT * FROM fructe;
  49.  
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement