如何通过 OracleCommand 使用 OracleTransaction 将父表的 id 传递给子表

Question

我最近移植了一些代码以使用 ODP.NET 的 OracleTransaction

我有以下 PL/SQL 代码：

对于父表

CREATE OR REPLACE FUNCTION insertneworder (
   orderdate        IN   orders.order_date%TYPE,
   orderdescription IN   orders.order_description%TYPE
)
   RETURN orders.order_id%TYPE
IS
  orderid   orders.order_id%TYPE;

BEGIN
   INSERT INTO orders
               (order_date, order_description
               )
        VALUES (orderdate,orderdescription
               )
     RETURNING order_id
          INTO orderid;

   RETURN orderid;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      NULL;
   WHEN OTHERS
   THEN
      -- Consider logging the error and then re-raise
      RAISE;
END insertneworder;

父表触发器

CREATE OR REPLACE TRIGGER bi_orders_pk
   BEFORE INSERT
   ON orders
   REFERENCING NEW AS NEW OLD AS OLD
   FOR EACH ROW
BEGIN
   SELECT TO_CHAR (SYSTIMESTAMP, 'yyyymmddhh24missff3')
     INTO :NEW.order_id
     FROM DUAL;
END;

对于子表

CREATE OR REPLACE PROCEDURE insertneworderdetails (
   orderid     IN   order_details.order_id%TYPE,
   quantity    IN   order_details.quantity%TYPE,
   productid   IN   order_details.prod_id%TYPE,
   itemcost    IN   order_details.itemcost%TYPE
)
IS
BEGIN
   INSERT INTO order_details
               (order_id, quantity, prod_id, itemcost
               )
        VALUES (orderid, quantity, productid, itemcost
               );
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      NULL;
   WHEN OTHERS
   THEN
      -- Consider logging the error and then re-raise
      RAISE;
END insertneworderdetails;

子表触发器

CREATE OR REPLACE TRIGGER bi_order_details_pk
   BEFORE INSERT
   ON order_details
   REFERENCING NEW AS NEW OLD AS OLD
   FOR EACH ROW
BEGIN
   SELECT TO_CHAR (SYSTIMESTAMP, 'yyyymmddhh24missff3')
     INTO :NEW.item_id
     FROM DUAL;
END;

假设我为 insertneworder 和 insertneworderdetails 创建了一个 OracleCommand 我将其传递给此代码...

public static bool ExecuteTransaction(List<OracleCommand> command) 
            {
                OracleConnection conn;
                conn = OraConnection.Instance.OracleConnection;
                if (conn.State == ConnectionState.Closed) {
                    conn.Open();
                    }
                OracleTransaction trans;
                trans = conn.BeginTransaction();

                try 
                    {
                    foreach (OracleCommand cmd in command) 
                        {
                        cmd.Connection = conn;
                        cmd.ExecuteNonQuery();
                        }
                    trans.Commit(); 
                    }

                catch (Exception ex)
                    {
                    trans.Rollback();
                    return false;
                    }
            return true;
            } 

我的问题是“如何通过 OracleCommand 将 insertneworder 函数返回的 PARENT 表的 id 传递给 insertneworderdetails”？

非常感谢任何帮助...

Answer 1

看起来您需要在 ExecuteTransaction 函数的“foreach”循环中添加一些代码，以检测何时调用“insertneworder”函数，捕获其返回值，然后检测何时调用“insertneworderdetails”函数调用并将先前捕获的返回值绑定到“insertneworderdetails”调用中适当的参数标记。抱歉，我不能说得更具体，但我认为这是一个总体的前进方向。

分享并享受。