如何在PL/SQL中合并两个类似的数据库架构? [英] How do I merge two similar database-schema in PL/SQL?
问题描述
数据库架构(源和目标)非常大(每个数据库都有350多个表).我已经完成了将这两个表合并到一个表中的任务.数据本身(表中的内容)必须迁移.我必须小心，在合并架构之前或合并架构时，主键没有重复输入.有没有人做过这样的事情并且能够向我提供他的解决方案，或者有人可以帮助我找到完成任务的方法吗?我的方法都失败了，我的顾问告诉我要在线获得帮助:/
The database-schema (Source and target) are very large (each has over 350 tables). I have got the task to somehow merge these two tables into one. The data itself (whats in the tables) has to be migrated. I have to be careful that there are no double entries for primary keys before or while merging the schemata. Has anybody ever done that already and would be able to provide me his solution or could anyone help me get a approach to the task? My approaches all failed and my advisor just tells me to get help online :/
以我的方式: 我尝试使用"all_constraints"表从数据库中获取所有pk.
To my approach: I have tried using the "all_constraints" table to get all pks from my db.
SELECT cols.table_name, cols.column_name, cols.position, cons.status, cons.owner
FROM all_constraints cons, all_cons_columns cols
WHERE cols.owner = 'DB'
AND cons.constraint_type = 'P'
AND cons.constraint_name = cols.constraint_name
AND cons.owner = cols.owner
ORDER BY cols.table_name, cols.position;
我也知道"主键必须有序列才能为其添加值:
I also "know" that there has to be a sequence for the primary keys to add values to it:
CREATE SEQUENCE seq_pk_addition
MINVALUE 1
MAXVALUE 99999999999999999999
START WITH 1
INCREMENT BY 1
CACHE 20;
因为对于pl/sql(或一般来说是sql)，我是一个菜鸟 那么下一步我该怎么做? :/
Because I am a noob if it comes to pl/sql (or sql in general) So how/what I should do next? :/
以下是数据库ERD的链接: https://ufile.io/9tdoj
推荐答案
如我所愿，我准备了一个动态代码，您可以尝试使用源表和目标表获取数据merged.逻辑如下:
As promised to help in my comment, i had prepared a dynamic code which you can try to get the data merged with the source and target tables. The logic is as below:
步骤1:从SOURCE模式中获取所有表名.在下面的查询中，您可能需要分别替换架构(所有者)名称.出于测试目的，我只使用了1张表，因此在运行它时，请删除表名筛选子句.
Step1: Get all the table names from the SOURCE schema. In the query below you can you need to replace the schema(owner) name respectively. For testing purpose i had taken only 1 table so when you run it,remove the table name filtering clause.
第2步:获取表的受限列名称.这用于准备ON子句，该子句稍后将用于MERGE语句.
Step2: Get the constrained columns names for the table. This is used to prepared the ON clause which would be later used for MERGE statement.
Step3:获取表的不受限制的列名.使用MERGE时将在UPDATE子句中使用.
Step3: Get the non-constrainted column names for the table. This would be used in UPDATE clause while using MERGE.
步骤4:当数据与MERGE语句的ON条件不匹配时，准备insert列表.
Step4: Prepare the insert list when the data doesnot match ON conditon of MERGE statement.
阅读我的内联注释以了解每个步骤.
Read my inline comments to understand each step.
CREATE OR REPLACE PROCEDURE COPY_TABLE
AS
Type OBJ_NME is table of varchar2(100) index by pls_integer;
--To hold Table name
v_obj_nm OBJ_NME ;
--To hold Columns of table
v_col_nm OBJ_NME;
v_othr_col_nm OBJ_NME;
on_clause VARCHAR2(2000);
upd_clause VARCHAR2(4000);
cntr number:=0;
v_sql VARCHAR2(4000);
col_list1 VARCHAR2(4000);
col_list2 VARCHAR2(4000);
col_list3 VARCHAR2(4000);
col_list4 varchar2(4000);
col_list5 VARCHAR2(4000);
col_list6 VARCHAR2(4000);
col_list7 VARCHAR2(4000);
col_list8 varchar2(4000);
BEGIN
--Get Source table names
SELECT OBJECT_NAME
BULK COLLECT INTO v_obj_nm
FROM all_objects
WHERE owner LIKE 'RU%' -- Replace `RU%` with your Source schema name here
AND object_type = 'TABLE'
and object_name ='TEST'; --remove this condition if you want this to run for all tables
FOR I IN 1..v_obj_nm.count
loop
--Columns with Constraints
SELECT column_name
bulk collect into v_col_nm
FROM user_cons_columns
WHERE table_name = v_obj_nm(i);
--Columns without Constraints remain columns of table
SELECT *
BULK COLLECT INTO v_othr_col_nm
from (
SELECT column_name
FROM user_tab_cols
WHERE table_name = v_obj_nm(i)
MINUS
SELECT column_name
FROM user_cons_columns
WHERE table_name = v_obj_nm(i));
--Prepare Update Clause
FOR l IN 1..v_othr_col_nm.count
loop
cntr:=cntr+1;
upd_clause := 't1.'||v_othr_col_nm(l)||' = t2.' ||v_othr_col_nm(l);
upd_clause:=upd_clause ||' and ' ;
col_list1:= 't1.'||v_othr_col_nm(l) ||',';
col_list2:= col_list2||col_list1;
col_list5:= 't2.'||v_othr_col_nm(l) ||',';
col_list6:= col_list6||col_list5;
IF (cntr = v_othr_col_nm.count)
THEN
--dbms_output.put_line('YES');
upd_clause:=rtrim(upd_clause,' and');
col_list2:=rtrim( col_list2,',');
col_list6:=rtrim( col_list6,',');
END IF;
dbms_output.put_line(col_list2||col_list6);
--dbms_output.put_line(upd_clause);
End loop;
--Update caluse ends
cntr:=0; --Counter reset
--Prepare ON clause
FOR k IN 1..v_col_nm.count
loop
cntr:=cntr+1;
--dbms_output.put_line(v_col_nm.count || cntr);
on_clause := 't1.'||v_col_nm(k)||' = t2.' ||v_col_nm(k);
on_clause:=on_clause ||' and ' ;
col_list3:= 't1.'||v_col_nm(k) ||',';
col_list4:= col_list4||col_list3;
col_list7:= 't2.'||v_col_nm(k) ||',';
col_list8:= col_list8||col_list7;
IF (cntr = v_col_nm.count)
THEN
--dbms_output.put_line('YES');
on_clause:=rtrim(on_clause,' and');
col_list4:=rtrim( col_list4,',');
col_list8:=rtrim( col_list8,',');
end if;
dbms_output.put_line(col_list4||col_list8);
-- ON clause ends
--Prepare merge Statement
v_sql:= 'MERGE INTO '|| v_obj_nm(i)||' t1--put target schema name before v_obj_nm
USING (SELECT * FROM '|| v_obj_nm(i)||') t2-- put source schema name befire v_obj_nm here
ON ('||on_clause||')
WHEN MATCHED THEN
UPDATE
SET '||upd_clause ||
' WHEN NOT MATCHED THEN
INSERT
('||col_list2||','
||col_list4||
')
VALUES
('||col_list6||','
||col_list8||
')';
dbms_output.put_line(v_sql);
execute immediate v_sql;
end loop;
End loop;
END;
/
执行:
exec COPY_TABLE
输出:
anonymous block completed
PS:我已经用一个具有2列的表进行了测试，其中我有唯一的键约束.表的DDL如下:
PS: i have tested this with a table with 2 columns out of which i was having unique key constraint .The DDL of table is as below:
最后，我希望您能理解我的代码(您是菜鸟)并在上述条件无法满足您的要求时实施类似的操作.
At the end i wish you could understand my code(you being a noob) and implement something similar if the above fails for your requirement.
CREATE TABLE TEST
( COL2 NUMBER,
COLUMN1 VARCHAR2(20 BYTE),
CONSTRAINT TEST_UK1 UNIQUE (COLUMN1)
) ;
这篇关于如何在PL/SQL中合并两个类似的数据库架构?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
