双向插入值-插入0行

Question

尝试用两种不同的方式添加7行。在这两种情况下，结果都是插入了0行。

create table bank
(
MoveNo INT ,
Valdate DATE not null,
Amount NUMBER(9,2),
Essence varchar2(30)
);
**//First way**
INSERT ALL
INTO bank (MoveNo, Valdate, Amount, Essence) VALUES (1,'2001/01/01',500,'opening balance')
INTO bank (MoveNo, Valdate, Amount, Essence) VALUES (2,'2001/01/10',3500,'salary')
INTO bank (MoveNo, Valdate, Amount, Essence) VALUES (3,'2001/01/10',-460,'cheque 055786')
INTO bank (MoveNo, Valdate, Amount, Essence) VALUES (4,'2001/01/12',1100,'deposit')
INTO bank (MoveNo, Valdate, Amount, Essence) VALUES (5,'2001/01/14',-5.50,'account management')
INTO bank (MoveNo, Valdate, Amount, Essence) VALUES (6,'2001/01/17',-300,'cheque 055787')
INTO bank (MoveNo, Valdate, Amount, Essence) VALUES (7,'2001/01/19',-867,'credit card')
SELECT 7 FROM BANK;
**//Second way**
INSERT INTO bank (MoveNo, Valdate, Amount, 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 management' 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;

Answer 1

对于这两个语句，'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 BANK时，最终的SELECT将匹配零行，并且不会插入任何内容。相反，您想要SELECT ... FROM DUAL，这将给您一行，并相应地执行每个INSERT一次：

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

对于第二个查询，语句之间有逗号，并且缺少最后一个;语句终止符：

INSERT INTO bank (MoveNo, Valdate, Amount, 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行和来自INSERT .. SELECT语句的7列(。

db<gt；小提琴这里