MySQL存储过程举例

DELIMITER $$CREATE PROCEDURE `p_copy_data`()    BEGIN        DECLARE i INT;       DECLARE  v_sql_col VARCHAR(200);    DECLARE j INT;    DECLARE max_id_s INT;    DECLARE max_id INT;    DECLARE v_tbname VARCHAR(50);    DECLARE v_tbname_s VARCHAR(50);    DECLARE v_col VARCHAR(50);    DECLARE  v_sql_create VARCHAR(200);    DECLARE  v_sql_insert VARCHAR(200);        CREATE TABLE IF NOT EXISTS temp_col(id INT PRIMARY KEY AUTO_INCREMENT,col VARCHAR(50));    CREATE TABLE IF NOT EXISTS tbnames(id INT PRIMARY KEY AUTO_INCREMENT,tbname VARCHAR(50));    TRUNCATE TABLE tbnames;    INSERT INTO tbnames(tbname)    SELECT  table_name FROM information_schema.`TABLES` WHERE table_name REGEXP   'test_ondev_[0-9]';      SET i = (SELECT MIN(id) FROM   tbnames);     SET  max_id_s = (SELECT MAX(id) FROM   tbnames);            WHILE  i <=max_id_s    DO    TRUNCATE TABLE  temp_col;    SET v_tbname_s = (SELECT tbname FROM  tbnames  WHERE id = i);    SET v_sql_col = CONCAT('insert into temp_col(col)  select distinct   col  from  ',v_tbname_s);      SET  @v_sql_col := v_sql_col;    PREPARE stmt FROM @v_sql_col;        EXECUTE stmt ;      DEALLOCATE PREPARE stmt;     SET j =1;    SET  max_id = (SELECT  MAX(id) FROM temp_col );    WHILE  j <= max_id    DO    SET  v_col = (SELECT col  FROM temp_col  WHERE id =j);    SET v_tbname = CONCAT('test_ondev_history_',v_col );    SET v_sql_create = CONCAT('create table  if not exists ',v_tbname,'  like test_ondev_history;');      SET  @v_sql_create := v_sql_create;    PREPARE stmt FROM @v_sql_create;        EXECUTE stmt ;      DEALLOCATE PREPARE stmt;         SET  v_sql_insert = CONCAT('insert into ',v_tbname,' select * from ',v_tbname_s," where  col='", v_col ,"';");                SET  @v_sql_insert := v_sql_insert;    PREPARE stmt FROM @v_sql_insert;        EXECUTE stmt ;      DEALLOCATE PREPARE stmt;         SET j = j + 1;    END WHILE;        SET  i = i + 1;    END  WHILE;        END$$