oracle 从数据库中提取行，包括相关行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1027968/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Extracting rows from a DB including depedent rows
提问by Karl Bartel
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).
我想为我的 Oracle 数据库中的一行生成插入字符串，包括其他表中的所有相关行（及其相关行）。
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:
当我从 a_id = 1 中提取行时，结果应该是该行和相关行的插入字符串：
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.
我想要这样做的原因是，我有一个包含许多不同表和约束的大型数据库，我想提取一小部分数据作为测试数据。
回答by Steve Broberg
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.
可能已经有一些工具可以做到这一点，但是从起始表中任意提取所有行表本身就是一项小型开发任务。我不能为你写整篇文章，但我可以让你开始——我开始写它，但大约 20 分钟后，我意识到我想承诺一个无偿的答案是更多的工作。
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.
我可以看到它最好通过递归 PL/SQL 过程完成，该过程将使用 dbms_ouput 和 user_cons_columns & user_constraints 为源表创建插入语句。您可以通过将所有插入内容当作 char 值来编写来作弊，因为假设您的 NLS 参数在源和目标系统上相同，Oracle 会将任何 char 值隐式转换为正确的数据类型。
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.
请注意，如果您的表中有循环关系，则下面的包会出现问题；此外，在早期版本的 Oracle 上，您可能会用完 dbms_output 的缓冲区空间。这两个问题都可以通过将生成的 sql 插入到在 sql 上具有唯一索引的临时表中来解决，如果遇到唯一键冲突，则中止递归。下面的大节省时间是 MakeParamList 函数，它将返回列列表的游标转换为逗号分隔的列表或单个表达式，当作为针对表的查询中的 select 子句。
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.
另请注意，以下包在您进一步修改它之前不会真正起作用（我停止编写它的原因之一）：生成的初始插入语句基于这样的假设，即传入的constraint_vals 参数将导致单行生成 - 当然，一旦您开始递归，情况几乎肯定不是这样（因为您将有许多父行的子行）。您需要将第一个语句（以及随后的递归调用）的生成更改为在循环内，以处理对第一个 EXECUTE IMMEDIATE 调用的调用生成多行而不是单个行的情况。让它工作的基础知识在这里，你只需要打磨细节并让外部光标工作。
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.
还有最后一个注意事项：您不太可能运行此过程来生成一组行，这些行在插入目标系统时会产生一组“干净”的数据，因为尽管您将获得所有相关数据，但数据可能取决于您未导入的其他表（例如，您遇到的第一个子表可能有其他外键指向与您的初始表无关的表）。在这种情况下，您可能希望从详细表开始，然后向上而不是向下工作；这样做，您还想将顺序颠倒为您生成的语句，或者使用脚本实用程序，或者通过将 sql 插入我上面提到的临时表中，使用序列，然后使用降序将其选中.
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.:
至于调用它，您将逗号分隔的列列表作为constraint_cols 进行约束，并将相应的逗号分隔的值列表作为constraint_vals 传递，例如：
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;
回答by Steve Broberg
I just use plain old SQL to do these tasks - use the select statements to generate your inserts:
我只是使用普通的旧 SQL 来完成这些任务 - 使用 select 语句来生成您的插入：
set pagesize 0
set verify off
SELECT 'INSERT INTO a(a_id, name) VALUES ('
|| a_id || ', '
|| '''' || name || ''');'
FROM a
WHERE a_id = &&1;
SELECT 'INSERT INTO b(b_id, a_id) VALUES ('
|| b_id || ', '
|| a_id || ');'
FROM b
WHERE a_id = &&1;
回答by Jens Schauder
I think DBUnit can do this.
我认为 DBUnit 可以做到这一点。
