sql - 从数据库中提取行，包括依赖行

Question

我想为我的 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_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);

我想这样做的原因是，我有一个包含许多不同表和约束的大型数据库，我想提取一小部分数据作为测试数据。

Answer 1

可能已经有一些工具可以做到这一点，但是从起始表中任意提取所有行表本身就是一项小的开发任务。我不能为你写完整的东西，但我可以让你开始 - 我开始写它，但大约 20 分钟后，我意识到我想为一个无偿的答案做更多的工作。

我可以看到它通过递归 PL/SQL 过程完成得最好，该过程将使用 dbms_ouput 和 user_cons_columns & user_constraints 为源表创建插入语句。您可以稍微作弊，将所有插入都写成好像列是 char 值一样，因为 Oracle 会隐式地将任何 char 值转换为正确的数据类型，假设您的 NLS 参数在源系统和目标系统上是相同的。

注意，如果你的表中有循环关系，下面的包就会有问题；此外，在早期版本的 Oracle 上，您可能会用完 dbms_output 的缓冲区空间。这两个问题都可以通过将生成的 sql 插入到在 sql 上具有唯一索引的暂存表中来解决，并在遇到唯一键冲突时中止递归。下面的大节省时间的是 MakeParamList 函数，它将返回列列表的游标转换为逗号分隔列表，或者将在作为针对表的查询中的 select 子句。

另请注意，除非您进一步修改以下包，否则它不会真正起作用(我停止编写它的原因之一):生成的初始插入语句基于这样的假设，即传入的 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
         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;