将数据插入相关的 Oracle 表并维护标识值列表

Question

我有一个由过程代码填充的数据临时表。

TABLE BatchRecord{
    BatchRecordID NUMBER PRIMARY KEY,
    BatchID NUMBER, --Assigned by the procedural code
    RecordID NUMBER, --Relative to the batch
    FieldID NUMBER, --Assigned by the procedural code; FK to another table
    Value VARCHAR2(MAX)
    Instance NUMBER}

然后，每个批处理记录需要转换为三个相关表：记录、记录字段、字段值。

TABLE Record{
    RecordID NUMBER PRIMARY KEY,
    BatchID NUMBER, --Same from BatchRecords}
TABLE RecordFields{
    RecordFieldID NUMBER PRIMARY KEY,
    RecordID NUMBER, --FK from Records
    FieldID NUNBER --Same from BatchRecords}
TABLE FieldValues{
    RecordFieldID NUMBER PRIMARY KEY,
    Instance NUMBER PRIMARY KEY, --Same from BatchRecords
    Value VARCHAR2(MAX) --Same from BatchRecords}

在 SQL Server 中，我可以使用 MERGE 语句并使用OUTPUT来捕获新插入的键来实现此目的：

CREATE TABLE #InsertedRecords(RecordID INT, NewRecordID INT);
MERGE INTO Records USING (
    SELECT RecordID
    FROM BatchRecords
    WHERE BatchID = @BatchID
    GROUP BY RecordID) AS BR ON 1 = 0
WHEN NOT MATCHED THEN
    INSERT (BatchID)
    VALUES (@BatchID)
    OUTPUT BR.RecordID, INSERTED.RecordID INTO #InsertedRecords;

采用类似的方法来插入记录字段，并使用INSERT...SELECT语句插入到字段值中。

但是，在 Oracle 中，不可能在 MERGE 语句甚至 SELECT...INTO 语句中使用 RETURNING（相当于 OUTPUT）。

有没有一种方法可以在 Oracle 中完成相同的任务，同时避免FORALL循环，因为 BatchRecords 的总数可能超过数百万？

Answer 1

您可以尝试使用 oracle 管道函数从 BatchRecord 中选择所有记录，然后将行管道放入不同的表中。如果需要，我可以举一个例子。

编辑

create or replace package test_pkg AS
  TYPE REP_CURS IS REF CURSOR;
  TYPE output_REC IS RECORD(
    RecordID_    number,
    BatchID_  number);
  TYPE output_TAB IS TABLE OF output_REC;
  FUNCTION Get_Data RETURN output_TAB
    PIPELINED;
END test_pkg;
CREATE OR REPLACE PACKAGE BODY test_pkg IS
  FUNCTION Get_Data RETURN output_TAB
    PIPELINED IS
    output_REC_ output_REC;
    rep_lines_  REP_CURS;
    stmt_       VARCHAR2(5000);
    table_rec_  BatchRecord%ROWTYPE;
  begin
    stmt_ := '  (select BatchRecordID,BatchID ....Instance  from BatchRecord)  ';
    OPEN rep_lines_ FOR stmt_;
    LOOP
      FETCH rep_lines_
        INTO table_rec_;
      EXIT WHEN rep_lines_%NOTFOUND;
      output_REC_.RecordID_   := <<whatever valu that you want>;
      output_REC_.BatchID_ := table_rec_.BatchID;

        PIPE ROW(output_REC_);
    END LOOP;
    CLOSE rep_lines_;
    RETURN;
  exception
    when others then
      DBMS_OUTPUT.put_line('Error:' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE ||
                           DBMS_UTILITY.FORMAT_ERROR_STACK ||
                           DBMS_UTILITY.FORMAT_CALL_STACK);
  END Get_Data;
END test_pkg;

我展示了一个插入到记录中的示例。您还必须对其他两个表执行类似的操作。希望这有帮助！！