从数据库中提取行，其中包括行

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为源表创建inserts语句。您可以通过编写所有插入内容来作弊，就好像这些列都是char值一样，因为Oracle会假定源和目标系统上的NLS参数相同，因此会将所有char值隐式转换为正确的数据类型。

注意，如果表中有循环关系，则下面的包将有问题；同样，在早期版本的Oracle上，dbms_output可能会用完缓冲区空间。通过将生成的sql插入到在sql上具有唯一索引的登台表中，并在遇到唯一键冲突时中止递归，可以解决这两个问题。下面最省时的方法是MakeParamList函数，该函数将将返回列列表的游标转换为以逗号分隔的列表或单个表达式，当以针对该表的查询中的select子句。

还请注意，以下包在进一步修改之前不会真正起作用（我停止编写它的原因之一）：生成的初始insert语句基于以下假设：传入的constraint_vals参数将导致仅一行生成-
当然，一旦开始递归，几乎肯定不是这种情况（因为父级将有许多子行）。您需要将第一条语句（以及后续的递归调用）的生成更改为在循环内，以处理对第一个EXECUTE
IMMEDIATE调用的调用生成多个行而不是单个行的情况。使它工作的基本知识在这里，您只需要弄清楚细节并使外部光标起作用即可。

最后要注意的一点是：不太可能运行此过程来生成将插入到目标系统中时会导致“干净”的数据集的行集，因为尽管您将获得所有相关数据，但是数据可能取决于您未导入的其他表（例如，您遇到的第一个子表可能具有其他外键，这些外键指向与初始表无关的表）。在这种情况下，您可能要从明细表开始，然后向上而不是向下工作；这样做时，您还想通过脚本实用程序或将sql插入上面提到的临时表中并按顺序使用生成的语句，然后按降序将其反转，从而将生成的语句反转。

至于调用它，您将以逗号分隔的列列表传递给constrains作为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;