通过从多个表中选择进行Oracle插入，其中一个表可能没有行 [英] Oracle Insert via Select from multiple tables where one table may not have a row

问题描述

我有许多代码值表，其中包含一个代码和一个带有长ID的描述.

I have a number of code value tables that contain a code and a description with a 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 = ?)

如果找到了各自代码的匹配项，则将从tax_status和收件人表中检索适当的值.不幸的是，receiver_code是可为空的，因此?替换值可以为null.当然，隐式联接不会返回任何行，因此不会将行插入到我的表中.

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.

我尝试在上使用NVL?并在r.recipient_id上.

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

我尝试在r.recipient_code =上强制进行外部联接.通过添加(+)，但它不是显式联接，因此Oracle仍未添加另一行.

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.

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

Anyone know of a way of doing this?

我显然可以修改该语句，以便在外部进行receive_id的查找，并且有一个?而不是r.recipient_id，并且根本不从接收者表中进行选择，但我宁愿在1条SQL语句中完成所有这些操作.

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.

推荐答案

在这种情况下，Outter联接无法按预期工作"，因为您已明确告诉Oracle您仅在该表上的条件匹配时才需要数据.在这种情况下，外部联接将变得无用.

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.

解决方法

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 = ?)
)

如果期望子选择有多个行，则可以将ROWNUM = 1添加到每个where子句中，或使用汇总(例如MAX或MIN).当然，这可能不是所有情况下的最佳解决方案.

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.

每个评论，

  (SELECT account_type_standard_seq.nextval FROM DUAL),

可以只是

  account_type_standard_seq.nextval,

这篇关于通过从多个表中选择进行Oracle插入，其中一个表可能没有行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！