Advanced Search

mysql batch sql statements

/ Published in: SQL
Save to your folder(s)
stored procedure for executing batch sql statements

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  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


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.