找到你要的答案

Q:(ORACLE/SQL) Insert into when

Q:(Oracle或SQL)插入时

The example would be:

Declare
stringtest VARCHAR2(10):='yes';
Begin
INSERT WHEN (condition) THEN   -- the condition has to be like stringtest='yes';
INTO ...
Select ...
End;

But I can't do it like

Begin
INSERT WHEN TRUE THEN
INTO ...
Select ...
End;

What is the easiest way to let the condition be true?

这个例子将:

Declare
stringtest VARCHAR2(10):='yes';
Begin
INSERT WHEN (condition) THEN   -- the condition has to be like stringtest='yes';
INTO ...
Select ...
End;

但我不能这样做

Begin
INSERT WHEN TRUE THEN
INTO ...
Select ...
End;

让病情成真的最简单方法是什么?

answer1: 回答1:

the easiest (and the right) way is to move condition into select 'where' clause. If select returns (selects) nothing, then nothing will be inserted. i.e.

insert into table(col1,col2)
select 'colval1', 'colval2'
from dual
where 2=3

will select nothing and insert nothing

the easiest (and the right) way is to move condition into select 'where' clause. If select returns (selects) nothing, then nothing will be inserted. i.e.

insert into table(col1,col2)
select 'colval1', 'colval2'
from dual
where 2=3

将选择没有插入任何

answer2: 回答2:

Oracle SQL (as opposed to PL/SQL) doesn't have a boolean type, so TRUE isn't recognised in the SQL statement (standalone, or if it happens to be within a PL/SQL block).

You can use a dummy expression that always evaluates to true, e.g.:

INSERT WHEN (1=1) THEN
INTO ...
Select ...

Quick SQL Fiddle demo.

Unless this is a 'fixed' part of a larger INSERT ALL construct, the clause seems rather redundant if you know it always has to be true; if you aren't evaluating something real, the WHEN clause isn't adding anything useful. If you want to use it as a flag to control the behaviour then your stringtest approach would probably be easier since you can change that setting in one place - in the declare section - rather than hunting through and changing all the individual WHEN clauses.

Oracle SQL(相对于PL / SQL)没有一个布尔型,真的不是在SQL表确认(独立,或如果它发生在一个PL/SQL块)。

您可以使用始终计算为true的虚拟表达式,例如:

INSERT WHEN (1=1) THEN
INTO ...
Select ...

快速演示SQL的小提琴。

除非这是一个“固定”的一个更大的插入所有构建,该条款似乎相当多余的,如果你知道它总是必须是真实的,如果你没有评估一些真正的,当条款不添加任何有用的。如果你想使用它作为一个标志来控制行为那么你的stringtest方法可能会更容易因为你可以改变在一个地方,设置在声明部分-而不是通过狩猎和变化的个体当条款。

sql  oracle