先在网上找了一些资料如:
delimiter //
create procedure oneKey(in newName varchar(250),in oldName varchar(250),in idNum INT)
BEGIN
SET @sqlStmt = CONCAT('insert into ',newName,' (`name`,`age`,`sex`,`major`,`pass`,`photo`)
select `name`,`age`,`sex`,`major`,`pass`,`photo` from ',oldName,' where id = ',idNum);
PREPARE stmt FROM @sqlStmt;
EXECUTE stmt;
END;
//
delimiter ;
call oneKey('stu1','student',5);
自己模仿着,然后成功的完成了数据库名的动态替换:
CREATE DEFINER=`pnggu`@`%` PROCEDURE `district_to_db`(IN city_id int ( 20 ), IN basedb_city VARCHAR ( 255 ))
BEGIN
-- 参数说明:city_id:城市id;
-- basedb_city 需要传递到的业务数据库名称
-- REPLACE INTO basedb_sh.district ( TID, STATUS, DistrictName, DistrictFullName, DescriptionDate )
-- select
-- id as TID,
-- 0 as STATUS,
-- full_name as DistrictName,
-- full_name as DistrictFullName,
-- NOW() as DescriptionDate
-- from python3.district where parent_id = '310100';
SET @sqlStmt = CONCAT('REPLACE INTO ',basedb_city,' .district(`TID`,`STATUS`,`DistrictName`,`DistrictFullName`,`DescriptionDate`)
select `id` as TID,0 as STATUS,`full_name` as DistrictName,`full_name` as DistrictFullName,NOW() as DescriptionDate from python3.district where parent_id = ',city_id);
PREPARE stmt FROM @sqlStmt;
EXECUTE stmt;
END