SELECT INTO不将null存储到变量中

Question

我试图检查两个表中的值，只有在TableA中找到值时，我才想继续下一个查询。下面的逻辑有问题，而且当没有数据时，它会进入异常，而不是将NULL存储到变量中。

SET SERVEROUTPUT ON;
SET FEEDBACK OFF;
SPOOL temp.txt;
DECLARE
v_ATM TableA.CODE%TYPE := NULL;
v_TBL TableB.CODE%TYPE := NULL;
BEGIN
SELECT TableA.CODE, TableB.CODE INTO v_ATM,v_TBL FROM TableA LEFT JOIN TableB ON TableA.CODE = TableB.CODE WHERE TableA.CODE = 'ABC';
IF (v_ATM IS NULL) AND (v_TBL IS NULL) THEN
DBMS_OUTPUT.PUT_LINE('No value found');
ELSIF (v_ATM IS NOT NULL) AND (v_TBL IS NOT NULL) THEN
DBMS_OUTPUT.PUT_LINE('Found in both');
ELSIF (v_ATM IS NULL) AND (v_TBL IS NOT NULL) THEN
DBMS_OUTPUT.PUT_LINE('Found in Table B');
ELSIF (v_ATM IS NOT NULL) AND (v_TBL IS NULL) THEN
DBMS_OUTPUT.PUT_LINE('Found in Table A');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Exception');
END;

或者我在尝试：

SELECT TableA.CODE INTO v_ATM FROM TableA WHERE TableA.CODE = 'ABC';
SELECT TableB.CODE INTO v_TBL FROM TableB WHERE TableB.CODE = 'ABC';

但是，如果找不到ABC，v_ATM仍然不存储NULL，并进入异常。

Answer 1

——选择count(1(以获得0/1作为输出，而不是选择代码col来获得null/not null

with 
TableA as (
--select 'ABC' as code from dual 
--union all
select 'DEF' as code from dual
),
TableB as (
--select 'ABC' as code from dual 
--union all
select 'DEF' as code from dual
),
q1 as (
SELECT 1 as id, count(1) as cnt FROM TableA WHERE TableA.CODE = 'ABC'
),
q2 as (
SELECT 1 as id, count(1) as cnt FROM TableB WHERE TableB.CODE = 'ABC'
)
select 
cast(
case 
when q1.cnt = 0 and q2.cnt = 0 then 'Both missing'
when q1.cnt = 1 and q2.cnt = 0 then 'TableA present, TableB missing'
when q1.cnt = 0 and q2.cnt = 1 then 'TableA missing, TableB present'
when q1.cnt = 1 and q2.cnt = 1 then 'Both present'
else 'NA'
end as varchar2(40)) as output_txt
from q1 inner join q2 on (q1.id = q2.id)
;

通过隐藏ABC和DEF并根据需要合并所有代码行来处理sql，以获得不同条件下的不同输出。

Answer 2

您选择的是一行，而不是标量值，因此如果没有行，就没有值。

如果您SELECT MAX(TableA.CODE) ... WHERE ...

您将获得使用的值