SQL Oracle Insert 通过从多个表中选择，其中一个表可能没有一行

Oracle Insert via Select from multiple tables where one table may not have a row

提问by Mikezx6r

I have a number of code value tables that contain a code and a description with a Long id.

我有许多代码值表，其中包含一个代码和一个带有 Long id 的描述。

I now want to create an entry for an Account Type that references a number of codes, so I have something like this:

我现在想为引用多个代码的帐户类型创建一个条目，所以我有这样的事情：

insert into account_type_standard (account_type_Standard_id,
tax_status_id, recipient_id)
( select account_type_standard_seq.nextval,
ts.tax_status_id, r.recipient_id
from tax_status ts, recipient r
where ts.tax_status_code = ?
and r.recipient_code = ?)

This retrieves the appropriate values from the tax_status and recipient tables if a match is found for their respective codes. Unfortunately, recipient_code is nullable, and therefore the ? substitution value could be null. Of course, the implicit join doesn't return a row, so a row doesn't get inserted into my table.

如果找到匹配的相应代码，这将从 tax_status 和收件人表中检索适当的值。不幸的是，recipient_code 可以为空，因此 ? 替换值可以为空。当然，隐式连接不会返回一行，因此不会将一行插入到我的表中。

I've tried using NVL on the ? and on the r.recipient_id.

我试过在 ? 并在 r.recipient_id 上。

I've tried to force an outer join on the r.recipient_code = ? by adding (+), but it's not an explicit join, so Oracle still didn't add another row.

我试图在 r.recipient_code = 上强制外部连接？通过添加 (+)，但它不是显式连接，因此 Oracle 仍然没有添加另一行。

Anyone know of a way of doing this?

有人知道这样做的方法吗？

I can obviously modify the statement so that I do the lookup of the recipient_id externally, and have a ? instead of r.recipient_id, and don't select from the recipient table at all, but I'd prefer to do all this in 1 SQL statement.

我显然可以修改语句，以便我在外部查找收件人 ID，并有一个 ? 而不是 r.recipient_id，并且根本不要从收件人表中选择，但我更愿意在 1 个 SQL 语句中完成所有这些。

回答by Greg Ogle

Outter joins don't work "as expected" in that case because you have explicitly told Oracle you only want data if that criteria on that table matches. In that scenario, the outter join is rendered useless.

在这种情况下，外部联接不会“按预期”工作，因为您已经明确告诉 Oracle，如果该表的条件匹配，您只需要数据。在这种情况下，外部连接变得无用。

A work-around

解决办法

INSERT INTO account_type_standard 
  (account_type_Standard_id, tax_status_id, recipient_id) 
VALUES( 
  (SELECT account_type_standard_seq.nextval FROM DUAL),
  (SELECT tax_status_id FROM tax_status WHERE tax_status_code = ?), 
  (SELECT recipient_id FROM recipient WHERE recipient_code = ?)
)

[Edit] If you expect multiple rows from a sub-select, you can add ROWNUM=1 to each where clause OR use an aggregate such as MAX or MIN. This of course may not be the best solution for all cases.

[编辑] 如果您希望子选择中有多个行，您可以将 ROWNUM=1 添加到每个 where 子句或使用聚合，例如 MAX 或 MIN。这当然可能不是所有情况的最佳解决方案。

[Edit] Per comment,

[编辑] 每条评论，

  (SELECT account_type_standard_seq.nextval FROM DUAL),

can be just

可以只是

  account_type_standard_seq.nextval,

回答by Tony Andrews

A slightly simplified version of Oglester's solution (the sequence doesn't require a select from DUAL:

Oglester 解决方案的略微简化版本（该序列不需要从 DUAL 中进行选择：

INSERT INTO account_type_standard   
  (account_type_Standard_id, tax_status_id, recipient_id) 
VALUES(   
  account_type_standard_seq.nextval,
  (SELECT tax_status_id FROM tax_status WHERE tax_status_code = ?),
  (SELECT recipient_id FROM recipient WHERE recipient_code = ?)
)

回答by WW.

It was not clear to me in the question if ts.tax_status_code is a primary or alternate key or not. Same thing with recipient_code. This would be useful to know.

我不清楚 ts.tax_status_code 是主键还是备用键。与recipient_code 相同。知道这会很有用。

You can deal with the possibility of your bind variable being null using an OR as follows. You would bind the same thing to the first two bind variables.

您可以使用 OR 处理绑定变量为空的可能性，如下所示。您会将相同的内容绑定到前两个绑定变量。

If you are concerned about performance, you would be better to check if the values you intend to bind are null or not and then issue different SQL statement to avoid the OR.

如果您担心性能，最好检查您要绑定的值是否为空，然后发出不同的 SQL 语句以避免 OR。

insert into account_type_standard 
(account_type_Standard_id, tax_status_id, recipient_id)
(
select 
   account_type_standard_seq.nextval,
   ts.tax_status_id, 
   r.recipient_id
from tax_status ts, recipient r
where (ts.tax_status_code = ? OR (ts.tax_status_code IS NULL and ? IS NULL))
and (r.recipient_code = ? OR (r.recipient_code IS NULL and ? IS NULL))

回答by Tony Andrews

Try:

尝试：

insert into account_type_standard (account_type_Standard_id, tax_status_id, recipient_id)
select account_type_standard_seq.nextval,
       ts.tax_status_id, 
       ( select r.recipient_id
         from recipient r
         where r.recipient_code = ?
       )
from tax_status ts
where ts.tax_status_code = ?

回答by paparao

insert into account_type_standard (account_type_Standard_id, tax_status_id, recipient_id)
select account_type_standard_seq.nextval,
   ts.tax_status_id, 
   ( select r.recipient_id
     from recipient r
     where r.recipient_code = ?
   )
from tax_status ts
where ts.tax_status_code = ?

回答by Arjun

insert into received_messages(id, content, status)
    values (RECEIVED_MESSAGES_SEQ.NEXT_VAL, empty_blob(), '');