情况：
1.实际的应用中，我们会遇到数据库某些表及表中部门列数据的迁移
2.笔者当前按照自己的业务需求使用情况，初步测试了Mysql，postgresql及SQLServer相关的简单的数据迁移（备以后参考使用）
① mysql的相关语句
-- 移植单行
USE studentinfo;
DELIMITER //
CREATE PROCEDURE p2()
BEGIN
DECLARE id VARCHAR(10);
DECLARE NAME VARCHAR(10);
DECLARE sex VARCHAR(4);
DECLARE birthday VARCHAR(10);
DECLARE dept VARCHAR(10);
DECLARE major VARCHAR(10);
DECLARE classid VARCHAR(10);
DECLARE name_dept_cursor CURSOR FOR SELECT * FROM stuinfo_table WHERE student_id='001';
OPEN name_dept_cursor;
FETCH name_dept_cursor INTO id,NAME,sex,birthday,dept,major,classid;
SELECT id,NAME,sex,birthday,dept,major,classid;
CLOSE name_dept_cursor;
BEGIN
INSERT INTO test.`stuinfo_table`(student_id,student_name,student_sex,student_birthday,student_dept,student_major,student_classid) VALUES(id,NAME,sex,birthday,dept,major,classid);
END;
END;
//删除存储过程
DROP PROCEDURE IF EXISTS p2;
//调用存储过程
CALL p2();
-- 移植多行
USE studentinfo;
DELIMITER //
CREATE PROCEDURE p2()
BEGIN
DECLARE id VARCHAR(10);
DECLARE NAME VARCHAR(10);
DECLARE sex VARCHAR(4);
DECLARE birthday VARCHAR(10);
DECLARE dept VARCHAR(10);
DECLARE major VARCHAR(10);
DECLARE classid VARCHAR(10);
DECLARE name_dept_cursor CURSOR FOR SELECT * FROM stuinfo_table;
OPEN name_dept_cursor;
FETCH name_dept_cursor INTO id,NAME,sex,birthday,dept,major,classid ;
WHILE(id IS NOT NULL)
DO
INSERT INTO test.`stuinfo_table`(student_id,student_name,student_sex,student_birthday,student_dept,student_major,student_classid) VALUES(id,NAME,sex,birthday,dept,major,classid);
FETCH name_dept_cursor INTO id,NAME,sex,birthday,dept,major,classid;
END WHILE;
CLOSE name_dept_cursor;
END;
DROP PROCEDURE IF EXISTS p2;
CALL p2();
-- 不会报错，优化后的数据移植语句样本
USE studentinfo;
DELIMITER //
CREATE PROCEDURE p2()
BEGIN
DECLARE id VARCHAR(10);
DECLARE NAME VARCHAR(10);
DECLARE sex VARCHAR(4);
DECLARE birthday VARCHAR(10);
DECLARE dept VARCHAR(10);
DECLARE major VARCHAR(10);
DECLARE classid VARCHAR(10);
DECLARE done BOOLEAN DEFAULT 0;
DECLARE name_dept_cursor CURSOR FOR SELECT * FROM stuinfo_table;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
-- DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; --与上面的语句等价
OPEN name_dept_cursor;
FETCH name_dept_cursor INTO id,NAME,sex,birthday,dept,major,classid ;
lp1:LOOP
INSERT INTO test.`stuinfo_table`(student_id,student_name,student_sex,student_birthday,student_dept,student_major,student_classid) VALUES(id,NAME,sex,birthday,dept,major,classid);
FETCH name_dept_cursor INTO id,NAME,sex,birthday,dept,major,classid ;
IF done!=0 THEN
LEAVE lp1;
END IF;
END LOOP;
CLOSE name_dept_cursor;
END;
DROP PROCEDURE IF EXISTS p2;
CALL p2();
USE test
SELECT student_id FROM `stuinfo_table`;
② 关于postgresql数据库的迁移
一、建环境
1.创建一个拓展（有该拓展postgresql才能建立远程数据访问）
create extension dblink;
---------------- 使用前测试用------------------------
-- 建立跨库连接
select dblink_connect('test_dblink2','dbname=security_db host=10.0.1.237 port=5432 user=root password=sa123');
select * from dblink('test_dblink2','SELECT pin,name,dept_id FROM pers_person') as data(pin VARCHAR(30),name VARCHAR(50),dept_id int)
LOOP
-- 工号为空则不插入
IF recordRow.pin is NOT NULL THEN
INSERT INTO pers_person(pin,name,deptid) VALUES(recordRow.pin,recordRow.name,recordRow.dept_id);
END IF;
END LOOP;
PERFORM dblink_disconnect('test_dblink1');
---------------- 以下为真正使用sql------------------------
CREATE OR REPLACE FUNCTION p2()
RETURNS boolean AS
$BODY$
DECLARE recordRow RECORD;
DECLARE querySQlStr text;
BEGIN
querySQlStr:='SELECT pin,name,dept_id FROM pers_person';
-- 建立跨库连接
PERFORM dblink_connect('test_dblink1','dbname=security_db host=10.0.1.237 port=5432 user=root password=sa123');
-- 循环写入获得数据
for recordRow in select * from dblink('test_dblink1','SELECT pin,name,dept_id FROM pers_person') as data(pin VARCHAR(30),name VARCHAR(50),dept_id int)
LOOP
-- 工号为空则不插入
IF recordRow.pin is NOT NULL THEN
INSERT INTO pers_person(pin,name,deptid) VALUES(recordRow.pin,recordRow.name,recordRow.dept_id);
END IF;
END LOOP;
PERFORM dblink_disconnect('test_dblink1');
RETURN TRUE;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION p2()
OWNER TO ROOT;
-------- 调用函数 -------
select * from p2();
备注：参考网址
③ SQLServer的数据迁移
DECLARE @zk_userid int --人员id
DECLARE @zk_username varchar(200) --姓名
DECLARE @zk_card varchar(50) --卡号
DECLARE @kh_userid varchar(50) --人员id
DECLARE @kh_username varchar(200) --姓名
DECLARE @kh_card varchar(50) --卡号
DECLARE @zj_card varchar(50) --中间转换
DECLARE @r bigint
DECLARE @i bigint
DECLARE @zk_pin int
DECLARE @zk_deptid varchar(10) --部门号
DECLARE @j int
SET @zk_pin = 0
DECLARE curDeviceCusror CURSOR
FOR select GE.Base_Chn_Name,GE.Base_Dept_Code,GC.Base_Card_No from General_Employee as GE,General_Card_Distributed as GC where GE.Base_Employee_ID=GC.Base_Employee_ID
FOR READ Only
OPEN curDeviceCusror
FETCH Next FROM curDeviceCusror INTO @kh_username,@zk_deptid,@kh_card
WHILE(@@Fetch_Status=0)
BEGIN
IF @kh_card IS NOT NULL
BEGIN
--print @kh_card
SET @zj_card=substring(@kh_card,7,1)
SET @zj_card=@zj_card+substring(@kh_card,8,1)
SET @zj_card=@zj_card+substring(@kh_card,5,1)
SET @zj_card=@zj_card+substring(@kh_card,6,1)
SET @zj_card=@zj_card+substring(@kh_card,3,1)
SET @zj_card=@zj_card+substring(@kh_card,4,1)
SET @zj_card=@zj_card+substring(@kh_card,1,1)
SET @zj_card=@zj_card+substring(@kh_card,2,1)
print @zj_card
SET @r=0
SET @i=1
set @j=0
SET @zk_pin=@zk_pin+1
while @i<=len(@zj_card)
begin
set @r=@r+
convert(bigint,
(
case
when substring(@zj_card,@i,1)<='9' then substring(@zj_card,@i,1)
when substring(@zj_card,@i,1)<='A' then '10'
when substring(@zj_card,@i,1)<='B' then '11'
when substring(@zj_card,@i,1)<='C' then '12'
when substring(@zj_card,@i,1)<='D' then '13'
when substring(@zj_card,@i,1)<='E' then '14'
when substring(@zj_card,@i,1)<='F' then '15'
end
))
*power(16,len(@zj_card)-@i)
SET @i=@i+1
END
END
SET @kh_card=@r
--print @kh_userid
--print @kh_username
print @kh_card
print @zk_pin
print '----------------'
begin
set @j=convert(int,(
case
when @zk_deptid='0002' then 2
when @zk_deptid='0003' then 3
when @zk_deptid='0004' then 4
when @zk_deptid='0005' then 5
when @zk_deptid='0006' then 6
when @zk_deptid='0007' then 7
when @zk_deptid='0008' then 8
when @zk_deptid='0009' then 9
when @zk_deptid='001' then 10
when @zk_deptid='0010' then 11
when @zk_deptid='0011' then 12
when @zk_deptid='0016' then 13
when @zk_deptid='0018' then 14
when @zk_deptid='002' then 15
when @zk_deptid='002773' then 16
when @zk_deptid='01' then 17
when @zk_deptid='02' then 18
when @zk_deptid='03' then 19
when @zk_deptid='04' then 20
when @zk_deptid='05' then 21
when @zk_deptid='06' then 22
when @zk_deptid='07' then 23
when @zk_deptid='1' then 24
when @zk_deptid='1#A' then 25
when @zk_deptid='10' then 26
when @zk_deptid='11' then 27
when @zk_deptid='11#' then 28
when @zk_deptid='110' then 29
when @zk_deptid='1-A' then 30
when @zk_deptid='1号楼' then 31
when @zk_deptid='500' then 32
when @zk_deptid='55' then 33
when @zk_deptid='9#' then 34
end
))
end
insert into zktk.dbo.userinfo(
badgenumber,name,card,defaultdeptid,status,
att,overtime,holiday,photo,offduty,deltag,set_valid_time,isatt
) values(@zk_pin,@kh_username,@kh_card,@j,0,1,1,1,'',0,0,0,1)
-- ###########################################
FETCH Next FROM curDeviceCusror INTO @kh_username,@zk_deptid,@kh_card
END
Close curDeviceCusror
Deallocate curDeviceCusror
网友评论