从包含depedent行的数据库中提取行 [英] Extracting rows from a DB including depedent rows
问题描述
我想为Oracle数据库中的行生成插入字符串，包括其他表（及其相关行）中的所有相关行。
示例：
CREATE TABLE a（
a_id number PRIMARY KEY，
name varchar2（100）
）;
CREATE TABLE b（
b_id number PRIMARY KEY，
a_id number REFERENCES a（a_id）
）;
当我从a中提取带有a_id = 1的行时，结果应该是一个插入字符串该行和从属行：
INSERT INTO a_id，name VALUES（1，'foo'）;
INSERT INTO b（b_id，a_id）VALUES（1,1）;
INSERT INTO b（b_id，a_id）VALUES（2，1）;
INSERT INTO b（b_id，a_id）VALUES（3，1）;
我想这样做的原因是，我有大型数据库有许多不同的表和约束
可能有一些工具它已经，但任意从起始表中提取所有行表是一个小的开发任务本身。我不能为你写整个事情，但我可以让你开始 - 我开始写它，但在大约20分钟后，我意识到这是一个更多的工作，我想承诺一个未付的回答。 p>
我可以看到它是最好的递归PL / SQL过程，将使用dbms_ouput和user_cons_columns& user_constraints为源表创建inserts语句。您可以通过编写所有的插入来欺骗一样，就像列是char值，因为Oracle将隐式地将任何char值转换为正确的数据类型，假设您的NLS参数在源&目标系统。
请注意，如果你在表中有循环关系，下面的包将有问题;此外，在早期版本的Oracle上，可能会使用dbms_output耗尽缓冲区空间。这两个问题都可以通过将生成的sql插入到在sql上具有唯一索引的临时表中来解决，并且如果获得唯一的键冲突，则中止递归。下面的大时间保护程序是MakeParamList函数，它将一个返回列表列表的游标转换为逗号分隔列表或单个表达式，以运行时以逗号分隔的形式显示这些列的值。
请注意，下面的软件包将不会真正工作，直到你进一步修改它（我停止写它的原因之一） ：生成的初始插入语句基于假设传递的constraint_vals参数将导致生成单个行 - 当然，这一点几乎肯定不是这样的，一旦您开始递归（因为您将有许多子行父代）。您将需要将第一个语句（以及后续的递归调用）的生成更改为在循环内，以处理调用第一个EXECUTE IMMEDIATE调用时生成多个行而不是单个行的情况。让它工作的基础在这里，你只需要研究细节，并得到外部的光标工作。
最后一个注意：你不太可能运行此过程以生成一组行，当插入到目标系统中时，将导致干净的数据集，因为虽然您将获得所有依赖数据，但该数据可能取决于您没有使用的其他表导入（例如，您遇到的第一个子表可能有其他外键指向与初始表无关的表）。在这种情况下，你可能想要从细节表开始，向上而不是向下;这样做，你也想要颠倒你生成的语句的顺序，或者使用脚本实用程序，或者通过将sql插入到上面提到的临时表中，使用序列，然后使用降序排序。
对于调用它，你传递逗号分隔的列列表来约束为constraint_cols，并将相应的逗号分隔的值列表作为constraint_vals，例如：
exec Data_extractor.MakeInserts（'MYTABLE'，'COL1，COL2'，'99，105'）
这是：
CREATE OR REPLACE PACKAGE data_extractor
IS
TYPE column_info IS RECORD（
column_name user_tab_columns.column_name％TYPE
）;
TYPE column_info_cursor IS REF CURSOR
RETURN column_info;
FUNCTION makeparamlist（
column_info column_info_cursor
，get_values NUMBER
）
RETURN VARCHAR2;
PROCEDURE makeinserts（
source_table VARCHAR2
，constraint_cols VARCHAR2
，constraint_vals VARCHAR2
）;
END data_extractor;
CREATE OR REPLACE PACKAGE BODY data_extractor
AS
FUNCTION makeparamlist（
column_info column_info_cursor
，get_values NUMBER
）
RETURN VARCHAR2
AS
BEGIN
DECLARE
column_name user_tab_columns.column_name％TYPE;
tempsql VARCHAR2（4000）;
separator VARCHAR2（20）;
BEGIN
如果get_values = 1
THEN
separator：='''''''''|| ';
ELSE
separator：='';
END IF;
LOOP
FETCH column_info
INTO column_name;
EXIT WHEN column_info％NOTFOUND;
tempsql：= tempsql ||分隔符|| column_name;
如果get_values = 1
THEN
separator：='|| ''''''，''''''|| ';
ELSE
separator：='，';
END IF;
END LOOP;
如果get_values = 1
THEN
tempsql：= tempsql || '|| '''''''''';
END IF;
返回tempsql;
END;
END;
PROCEDURE makeinserts（
source_table VARCHAR2
，constraint_cols VARCHAR2
，constraint_vals VARCHAR2
）
AS
BEGIN
DECLARE
basesql VARCHAR2（4000）;
extractsql VARCHAR2（4000）;
temppsql VARCHAR2（4000）;
valuelist VARCHAR2（4000）;
childconstraint_vals VARCHAR2（4000）;
BEGIN
SELECT makeparamlist（CURSOR（SELECT column_name
FROM user_tab_columns
WHERE table_name = source_table），0）
INTO tempsql
FROM DUAL;
basesql：='INSERT INTO'|| source_table || '（'|| tempsql ||'）VALUES（';
SELECT makeparamlist（CURSOR（SELECT column_name
FROM user_tab_columns
WHERE table_name = source_table），1）
INTO tempsql
FROM DUAL;
extractql：='SELECT'|| tempsql ||'FROM'|| source_table
||'WHERE（'|| constraint_cols || '）=（SELECT'
|| constraint_vals ||'FROM DUAL）';
EXECUTE IMMEDIATE extractsql
INTO valuelist;
- 打印出根行的insert语句
DBMS_OUTPUT.put_line（basesql || valuelist ||'）;'）;
- 现在我们为后续调用构造constraint_vals参数：
SELECT makeparamlist（CURSOR（SELECT column_name
FROM user_cons_columns ucc
，user_constraints uc
WHERE uc.table_name = source_table
AND ucc.constraint_name = uc.constraint_name
ORDER BY position）
，1）
INTO tempsql
FROM DUAL;
extractql：='SELECT'|| tempsql || 'FROM'|| source_table
|| 'WHERE'|| constraint_cols || '='||约束_
EXECUTE IMMEDIATE extractsql
INTO childconstraint_vals;
childconstraint_vals：= childrenconstraint_vals;
- 现在遍历该表的依赖表
- 对此语句的游标：
- SELECT uc.table_name child_table，uc.constraint_name fk_name
- FROM user_constraints uc
- ，user_constraints ucp
- WHERE ucp.table_name = source_table
- AND uc.r_constraint_name = ucp.constraint_name;
- 对于该语句中的每个表，查找外键
- 对应于行中的行
- 在父表中的列
- SELECT column_name
- FROM user_cons_columns
- WHERE constraint_name = fk_name
--ORDER BY POSITION;
- 将该列传递到上面的makeparamlist中以创建
- 下面调用的constraint_cols参数：
- makeinserts（child_table，ChildConstraint_cols，childconstrain_vals） ;
END;
END;
END data_extractor;
I'd like to generate insert-strings for a row in my Oracle database including all its dependent rows in other tables (and their dependent rows).
Example:
CREATE TABLE a (
a_id number PRIMARY KEY,
name varchar2(100)
);
CREATE TABLE b (
b_id number PRIMARY KEY,
a_id number REFERENCES a(a_id)
);
When I extract the row from a with a_id = 1, the result should be an insert-string for that row and dependent rows:
INSERT INTO a(a_id, name) VALUES (1, 'foo');
INSERT INTO b(b_id, a_id) VALUES (1, 1);
INSERT INTO b(b_id, a_id) VALUES (2, 1);
INSERT INTO b(b_id, a_id) VALUES (3, 1);
The reason why I want to do this is, that I have large database with many different tables and constraints between then and I'd like to extract a small subset of the data as test data.
There may be some tool that does it already, but to arbitrarily extract all rows tables from a starting table is a small development task in itself. I can't write the whole thing for you, but I can get you started - I started to write it, but after about 20 minutes, I realized it was a little more work that I wanted to commit to a unpaid answer.
I can see it being done best by a recursive PL/SQL procedure that would use dbms_ouput and user_cons_columns & user_constraints to create inserts statement for the source table. You can cheat a little by writing all the inserts as if the columns were char values, since Oracle will implicitly convert any char values to the right datatype, assuming your NLS parameters are identical on the source & target system.
Note, the package below will have problems if you have circular relationships in your tables; also, on earlier versions of Oracle, you may run out of buffer space with dbms_output. Both problems can be solved by inserting the generated sql into a staging table that has a unique index on the sql, and aborting the recursion if you get a unique key collision. The big time saver below is the MakeParamList function, which converts a cursor that returns a list of columns into either a comma separated list, or a single expression that will display the values of those columns in a quoted, comma separated form when run as the select clause in a query against the table.
Note also that the following package won't really work until you modify it further (one of the reasons I stopped writing it): The initial insert statement generated is based on the assumption that the constraint_vals argument passed in will result in a single row being generated - of course, this is almost certainly not the case once you start recursing (since you will have many child rows for a parent). You'll need to change the generation of the first statement (and the subsequent recursive calls) to be inside a loop to handle the cases where the call to the first EXECUTE IMMEDIATE call generates multiple rows instead of a single one. The basics of getting it working are here, you just need to grind out the details and get the outer cursor working.
One final note also: It is unlikely that you could run this procedure to generate a set of rows that, when inserted into a target system, would result in a "clean" set of data, since although you would get all dependent data, that data may depend on other tables that you didn't import (e.g., the first child table you encounter may have other foreign keys that point to tables unrelated to your initial table). In that case, you may want to start with the detail tables and work your way up instead of down; doing that, you'd also want to reverse the order to the statements you generated, either using a scripting utility, or by inserting the sql into a staging table as I mention above, with a sequence, then selecting it out with a descending sort.
As for invoking it, you pass the comma separated list of columns to constrain as constraint_cols and the corresponding comma separated list of values as constraint_vals, e.g.:
exec Data_extractor.MakeInserts ('MYTABLE', 'COL1, COL2', '99, 105')
Here it is:
CREATE OR REPLACE PACKAGE data_extractor
IS
TYPE column_info IS RECORD(
column_name user_tab_columns.column_name%TYPE
);
TYPE column_info_cursor IS REF CURSOR
RETURN column_info;
FUNCTION makeparamlist(
column_info column_info_cursor
, get_values NUMBER
)
RETURN VARCHAR2;
PROCEDURE makeinserts(
source_table VARCHAR2
, constraint_cols VARCHAR2
, constraint_vals VARCHAR2
);
END data_extractor;
CREATE OR REPLACE PACKAGE BODY data_extractor
AS
FUNCTION makeparamlist(
column_info column_info_cursor
, get_values NUMBER
)
RETURN VARCHAR2
AS
BEGIN
DECLARE
column_name user_tab_columns.column_name%TYPE;
tempsql VARCHAR2(4000);
separator VARCHAR2(20);
BEGIN
IF get_values = 1
THEN
separator := ''''''''' || ';
ELSE
separator := '';
END IF;
LOOP
FETCH column_info
INTO column_name;
EXIT WHEN column_info%NOTFOUND;
tempsql := tempsql || separator || column_name;
IF get_values = 1
THEN
separator := ' || '''''', '''''' || ';
ELSE
separator := ', ';
END IF;
END LOOP;
IF get_values = 1
THEN
tempsql := tempsql || ' || ''''''''';
END IF;
RETURN tempsql;
END;
END;
PROCEDURE makeinserts(
source_table VARCHAR2
, constraint_cols VARCHAR2
, constraint_vals VARCHAR2
)
AS
BEGIN
DECLARE
basesql VARCHAR2(4000);
extractsql VARCHAR2(4000);
tempsql VARCHAR2(4000);
valuelist VARCHAR2(4000);
childconstraint_vals VARCHAR2(4000);
BEGIN
SELECT makeparamlist(CURSOR(SELECT column_name
FROM user_tab_columns
WHERE table_name = source_table), 0)
INTO tempsql
FROM DUAL;
basesql := 'INSERT INTO ' || source_table || '(' || tempsql || ') VALUES (';
SELECT makeparamlist(CURSOR(SELECT column_name
FROM user_tab_columns
WHERE table_name = source_table), 1)
INTO tempsql
FROM DUAL;
extractsql := 'SELECT ' || tempsql || ' FROM ' || source_table
|| ' WHERE (' || constraint_cols || ') = (SELECT '
|| constraint_vals || ' FROM DUAL)';
EXECUTE IMMEDIATE extractsql
INTO valuelist;
-- This prints out the insert statement for the root row
DBMS_OUTPUT.put_line(basesql || valuelist || ');');
-- Now we construct the constraint_vals parameter for subsequent calls:
SELECT makeparamlist(CURSOR( SELECT column_name
FROM user_cons_columns ucc
, user_constraints uc
WHERE uc.table_name = source_table
AND ucc.constraint_name = uc.constraint_name
ORDER BY position)
, 1)
INTO tempsql
FROM DUAL;
extractsql := 'SELECT ' || tempsql || ' FROM ' || source_table
|| ' WHERE ' || constraint_cols || ' = ' || constraint_vals;
EXECUTE IMMEDIATE extractsql
INTO childconstraint_vals;
childconstraint_vals := childconstraint_vals;
-- Now iterate over the dependent tables for this table
-- Cursor on this statement:
-- SELECT uc.table_name child_table, uc.constraint_name fk_name
-- FROM user_constraints uc
-- , user_constraints ucp
-- WHERE ucp.table_name = source_table
-- AND uc.r_constraint_name = ucp.constraint_name;
-- For each table in that statement, find the foreign key
-- columns that correspond to the rows
-- in the parent table
-- SELECT column_name
-- FROM user_cons_columns
-- WHERE constraint_name = fk_name
--ORDER BY POSITION;
-- Pass that columns into makeparamlist above to create
-- the constraint_cols argument of the call below:
-- makeinserts(child_table, ChildConstraint_cols, childconstrain_vals);
END;
END;
END data_extractor;
这篇关于从包含depedent行的数据库中提取行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
