bootstrap 订单详情页:oracle 避免约束带来的导入数据解决方案
来源:百度文库 编辑:中财网 时间:2024/05/10 05:30:50
Oracle导入数据其实很简单,但是如果数据存在约束:如主外键、主键约束、唯一约束,可能给数据导入带来很大的麻烦。比如主外键,如果批量导入数据,是难于指定导入的先后顺序的,相信有不少入门级朋友们,会遇到跟我一样的问题。因此,为了节省大家的宝贵时间,特此分享自己的研究成果。
个人解决方案为:在导入过程中,先导入表结构,再禁用约束,其次导入数据,最终启用约束即可。其核心就是禁用和启用约束过程的创建。具体步骤如下:
(1)、导出(分为2步):导出结构、导出数据
--只导出表结构
$exp dev_sem/dev_sem@pcmdb file=d:\struct.dmp log=d:\struct.log rows=n;
--导出数据
$exp dev_sem/dev_sem@pcmdb file=d:\data.dmp log=d:\data.log ;
(2)、导入结构
--导入表结构
$imp file=d:\struct.dmp log=d:\imp.log full=y;
(3)、编写过程
CREATE OR REPLACE PROCEDURE MANAGE_USER_CONSTRAINTS(OPERATION VARCHAR2,
FK BOOLEAN DEFAULT TRUE,
PK BOOLEAN DEFAULT TRUE,
UK BOOLEAN DEFAULT TRUE) IS
ST VARCHAR2(255);
CURSOR R IS
SELECT TABLE_NAME, CONSTRAINT_NAME
FROM USER_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'R';
CURSOR P IS
SELECT TABLE_NAME, CONSTRAINT_NAME
FROM USER_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'P';
CURSOR U IS
SELECT TABLE_NAME, CONSTRAINT_NAME
FROM USER_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'U';
BEGIN
IF UPPER(OPERATION) IN ('DROP', 'DISABLE') THEN
IF FK THEN
BEGIN
FOR E IN R LOOP
ST := 'ALTER TABLE '|| E.TABLE_NAME ||' '|| OPERATION ||' CONSTRAINT '||
E.CONSTRAINT_NAME;
EXECUTE IMMEDIATE (ST);
DBMS_OUTPUT.PUT_LINE(ST);
END LOOP;
END;
END IF;
IF PK THEN
BEGIN
FOR E IN R LOOP
ST := 'ALTER TABLE '|| E.TABLE_NAME ||' '|| OPERATION ||' CONSTRAINT '||
E.CONSTRAINT_NAME;
EXECUTE IMMEDIATE (ST);
DBMS_OUTPUT.PUT_LINE(ST);
END LOOP;
END;
BEGIN
FOR E IN P LOOP
ST := 'ALTER TABLE '|| E.TABLE_NAME ||' '|| OPERATION ||' CONSTRAINT '||
E.CONSTRAINT_NAME;
EXECUTE IMMEDIATE (ST);
DBMS_OUTPUT.PUT_LINE(ST);
END LOOP;
END;
END IF;
IF UK THEN
BEGIN
FOR E IN U LOOP
ST := 'ALTER TABLE '|| E.TABLE_NAME ||' '|| OPERATION ||' CONSTRAINT '||
E.CONSTRAINT_NAME;
EXECUTE IMMEDIATE (ST);
DBMS_OUTPUT.PUT_LINE(ST);
END LOOP;
END;
END IF;
ELSIF UPPER(OPERATION) IN ('ENABLE') THEN
IF PK THEN
BEGIN
FOR E IN P LOOP
ST := 'ALTER TABLE '|| E.TABLE_NAME ||' '|| OPERATION ||' CONSTRAINT '||
E.CONSTRAINT_NAME;
EXECUTE IMMEDIATE (ST);
DBMS_OUTPUT.PUT_LINE(ST);
END LOOP;
END;
END IF;
IF FK THEN
BEGIN
FOR E IN P LOOP
ST := 'ALTER TABLE '|| E.TABLE_NAME ||' '|| OPERATION ||' CONSTRAINT '||
E.CONSTRAINT_NAME;
EXECUTE IMMEDIATE (ST);
DBMS_OUTPUT.PUT_LINE(ST);
END LOOP;
END;
BEGIN
FOR E IN R LOOP
ST :='ALTER TABLE '|| E.TABLE_NAME ||' '|| OPERATION ||' CONSTRAINT '||
E.CONSTRAINT_NAME;
EXECUTE IMMEDIATE (ST);
DBMS_OUTPUT.PUT_LINE(ST);
END LOOP;
END;
END IF;
IF UK THEN
BEGIN
FOR E IN U LOOP
ST :='ALTER TABLE '|| E.TABLE_NAME ||' '|| OPERATION ||' CONSTRAINT '||
E.CONSTRAINT_NAME;
EXECUTE IMMEDIATE (ST);
DBMS_OUTPUT.PUT_LINE(ST);
END LOOP;
END;
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE('the first parameter of the procedure must be DROP or ENABLE or DISABLE');
END IF;
END;
/
--调用过程:禁用约束检查
exec MANAGE_USER_CONSTRAINTS('disable',true,true,true);
(4)、导入数据
$imp file=d:\data.dmp log=d:\data.log ignore=y full=y;
(5)、启用约束
exec MANAGE_USER_CONSTRAINTS('enable',true,true,true);
(6)、删除过程
drop procedure MANAGE_USER_CONSTRAINTS;
如上查找即正确无误的导入数据,而不用担心导入的先后顺序问题了。