來自兩個表 ORACLE 的總和

我希望將兩個不同表中的兩個值用作第三個表中的總計。

例如：

1. 表accommodation和flight
2. acc_price使用來自的值添加值flight_price
3. 添加這些值以在餐桌預訂中使用subtotal

我嘗試過的事情；

SELECT  F.FLI_PRICE + AC.ACC_PRICEPN
INTO    R.SUBTOTAL
FROM    HOLIDAY_RESERVATION R, FLIGHT F, ACCOMMODATION AC;

更新

我希望將 Leigh 中的程式碼添加到觸發器中，我嘗試了以下操作，但出現了突變錯誤

CREATE OR REPLACE TRIGGER HR_SUBTOTAL
AFTER DELETE OR INSERT OR UPDATE ON HOLIDAY_RESERVATION
FOR EACH ROW
BEGIN
UPDATE HOLIDAY_RESERVATION R SET SUBTOTAL = 
  NVL((SELECT F.FLI_PRICE FROM FLIGHT F WHERE F.FLI_ID = R.IN_FLIGHT_ID), 0) +
  NVL((SELECT F.FLI_PRICE FROM FLIGHT F WHERE F.FLI_ID = R.OUT_FLIGHT_ID), 0) +
  NVL((SELECT AC.ACC_PRICEPN FROM ACCOMMODATION AC WHERE AC.ACC_ID = R.ACC_ID), 0);
END;
/

ERROR at line 1:
ORA-04091: table LATIN.HOLIDAY_RESERVATION is mutating, trigger/f
ORA-06512: at "LATIN.HR_SUBTOTAL", line 2
ORA-04088: error during execution of trigger 'LATIN.HR_SUBTOTAL'

要使用其他表中可能包含空值的值的總和來更新列，請執行以下操作：

UPDATE Holiday_Reservation R SET Subtotal = 
  NVL((SELECT F.Fli_Price FROM Flight F WHERE F.Fli_ID = R.In_Flight_ID), 0) +
  NVL((SELECT F.Fli_Price FROM Flight F WHERE F.Fli_ID = R.Out_Flight_ID), 0) +
  NVL((SELECT AC.Acc_PricePn FROM Accommodation AC WHERE AC.Acc_ID = R.Acc_ID), 0);

在觸發器中，這看起來像這樣：

CREATE OR REPLACE TRIGGER HR_SUBTOTAL
BEFORE INSERT OR UPDATE ON HOLIDAY_RESERVATION
FOR EACH ROW
BEGIN
  SELECT 
     NVL((SELECT F.Fli_Price FROM Flight F WHERE F.Fli_ID = :new.In_Flight_ID), 0) +
     NVL((SELECT F.Fli_Price FROM Flight F WHERE F.Fli_ID = :new.Out_Flight_ID), 0) +
     NVL((SELECT AC.Acc_PricePn FROM Accomodation AC WHERE AC.Acc_ID = :new.Acc_ID), 0)
     INTO :new.Subtotal
  FROM dual;
END;
/

引用自：https://dba.stackexchange.com/questions/15376