Oracle 通过 Select 从多个表中插入，其中一个表可能没有一行

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

问题描述

我有许多代码值表，其中包含一个代码和一个带有 Long 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 和收件人表中检索适当的值.不幸的是，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.

我试过在 ?在 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?

我显然可以修改语句，以便在外部查找接收者 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.

推荐答案

在这种情况下，外部联接无法按预期"工作，因为您已明确告诉 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 通过 Select 从多个表中插入，其中一个表可能没有一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！