Posted By

ktrout on 01/03/16


Tagged

mysql dynamic batch queries


Versions (?)

mysql batch sql statements


 / Published in: SQL
 

stored procedure for executing batch sql statements

  1. DROP TABLE IF EXISTS batch_sql_t;
  2. CREATE TABLE batch_sql_t
  3. (
  4. id integer PRIMARY KEY AUTO_INCREMENT NOT NULL,
  5. operation varchar(33) NOT NULL,
  6. order_number integer NOT NULL,
  7. sql_statement varchar(512) NOT NULL
  8. );
  9.  
  10. DROP procedure IF EXISTS run_batch_sql;
  11. delimiter $$
  12. CREATE procedure run_batch_sql(IN sql_operation varchar(22))
  13. begin
  14. declare finished integer;
  15. declare dynamic_sql varchar(512);
  16. declare sql_cursor CURSOR FOR SELECT sql_statement FROM batch_sql_t t WHERE operation = sql_operation ORDER BY order_number;
  17. DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
  18.  
  19. open sql_cursor;
  20. SET finished = 0;
  21. read_loop: loop
  22. fetch sql_cursor INTO dynamic_sql;
  23. IF finished = 1 then
  24. leave read_loop;
  25. end IF;
  26.  
  27. SET @query := dynamic_sql;
  28. -- select @query;
  29.  
  30. prepare prep_sql FROM @query;
  31. execute prep_sql;
  32. deallocate prepare prep_sql;
  33.  
  34. end loop read_loop;
  35.  
  36. close sql_cursor;
  37.  
  38. DELETE FROM batch_sql_t WHERE operation = sql_operation;
  39.  
  40. end $$
  41. delimiter ;

Report this snippet  

You need to login to post a comment.