插入值2種方式-插入0行

嘗試以 2 種不同的方式添加 7 行。在這兩個結果中都插入了 0 行。

create table bank
(
  MoveNo INT ,
  Valdate DATE not null,
  Ammount NUMBER(9,2),
  Essence varchar2(30)
);

**//First way**
INSERT ALL
  INTO bank (MoveNo, Valdate, Ammount, Essence) VALUES (1,'2001/01/01',500,'opening balance')
  INTO bank (MoveNo, Valdate, Ammount, Essence) VALUES (2,'2001/01/10',3500,'salary')
  INTO bank (MoveNo, Valdate, Ammount, Essence) VALUES (3,'2001/01/10',-460,'cheque 055786')
  INTO bank (MoveNo, Valdate, Ammount, Essence) VALUES (4,'2001/01/12',1100,'deposit')
  INTO bank (MoveNo, Valdate, Ammount, Essence) VALUES (5,'2001/01/14',-5.50,'account managment')
  INTO bank (MoveNo, Valdate, Ammount, Essence) VALUES (6,'2001/01/17',-300,'cheque 055787')
  INTO bank (MoveNo, Valdate, Ammount, Essence) VALUES (7,'2001/01/19',-867,'credit card')
SELECT 7 FROM BANK;

**//Second way**
INSERT INTO bank (MoveNo, Valdate, Ammount, Essence)
          select 1,'2001/01/01',500,'opening balance' from dual
union all select 2,'2001/01/10',3500,'salary' from dual
union all select 3,'2001/01/10',-460,'cheque 055786' from dual,
union all select 4,'2001/01/12',1100,'deposit' from dual,
union all select 5,'2001/01/14',-5.50,'account managment' from dual,
union all select 6,'2001/01/17',-300,'cheque 055787' from dual,
union all select 7,'2001/01/19',-867,'credit card' from dual,
SELECT * FROM bank;

uj5u.com熱心網友回復：

對于這兩個陳述句，'2001/01/01'不是一個DATE它是一個字串文字。盡管 Oracle 會嘗試提供幫助并將TO_DATE函式隱式應用于字串以將字串轉換為日期，但它不知道日期的格式，因此它將使用NLS_DATE_FORMAT. 您可能會收到錯誤，因為默認NLS_DATE_FORMAT值不太可能匹配YYYY/MM/DD。相反，您應該使用DATE文字或使用TO_DATE('2001/01/01', 'YYYY-MM-DD').

該BANK表最初有零行，因此當您使用INSERT ALL ... SELECT 7 FROM BANKfinalSELECT時將匹配零行并且不會插入任何內容。相反，您希望SELECT ... FROM DUALwhich 會給您一行并相應地執行每個INSERTs 一次：

INSERT ALL
  INTO bank (MoveNo, Valdate, Ammount, Essence) VALUES (1, DATE '2001-01-01',  500,    'opening balance')
  INTO bank (MoveNo, Valdate, Ammount, Essence) VALUES (2, DATE '2001-01-10', 3500,    'salary')
  INTO bank (MoveNo, Valdate, Ammount, Essence) VALUES (3, DATE '2001-01-10', -460,    'cheque 055786')
  INTO bank (MoveNo, Valdate, Ammount, Essence) VALUES (4, DATE '2001-01-12', 1100,    'deposit')
  INTO bank (MoveNo, Valdate, Ammount, Essence) VALUES (5, DATE '2001-01-14',   -5.50, 'account managment')
  INTO bank (MoveNo, Valdate, Ammount, Essence) VALUES (6, DATE '2001-01-17', -300,    'cheque 055787')
  INTO bank (MoveNo, Valdate, Ammount, Essence) VALUES (7, DATE '2001-01-19', -867,    'credit card')
SELECT 1 FROM DUAL;

對于第二個查詢，陳述句之間有逗號，并且缺少最終;陳述句終止符：

INSERT INTO bank (MoveNo, Valdate, Ammount, Essence)
          select 1, DATE '2001-01-01',  500,    'opening balance'   from dual
union all select 2, DATE '2001-01-10', 3500,    'salary'            from dual
union all select 3, DATE '2001-01-10', -460,    'cheque 055786'     from dual
union all select 4, DATE '2001-01-12', 1100,    'deposit'           from dual
union all select 5, DATE '2001-01-14',   -5.50, 'account managment' from dual
union all select 6, DATE '2001-01-17', -300,    'cheque 055787'     from dual
union all select 7, DATE '2001-01-19', -867,    'credit card'       from dual;

然后你可以這樣做：

SELECT * FROM BANK;

作為單獨的陳述句，它將顯示 14 行（INSERT ALL陳述句中的 7 行和陳述句中的 7 行INSERT .. SELECT）。

db<>在這里擺弄

標籤：sql 甲骨文

上一篇：如何在Oracle中選擇每天的最新值？[復制]

下一篇：如何對oracleapex集合中的列求和