Oracle子查询:INTO子句应该出现在SELECT语句中

Question

这是我的函数在一个包中的简化版本

function log_booking_mapping(
pi_booking_id    in         VARCHAR2
,pi_event_id      in         VARCHAR2
...
,po_return        out        my_return_typ
)
return integer as
...
v_event_id varchar2(200) := null;
begin
IF pi_event_id IS NULL OR TRIM(BOTH FROM pi_event_id) = '' THEN
begin
select EVENT_ID into v_event_id 
from (
select EVENT_ID, row_number() over (order by UPDATE_DATE desc) as seqnum
from BOOKING_MAPPING_LOG
WHERE BOOKING_ID = pi_booking_id
)
WHERE seqnum  = 1;
IF v_event_id IS NULL THEN
return c_failure;
END IF;
exception
when NO_DATA_FOUND then
return c_failure;
end;
ELSE
v_event_id := pi_event_id;
END IF;
insert into BOOKING_MAPPING_LOG (
...
) values (
...
);
po_return := ...;
return c_success;
exception
WHEN DUP_VAL_ON_INDEX THEN
UPDATE BOOKING_MAPPING_LOG ...
WHERE BOOKING_ID = pi_booking_id AND EVENT_ID = v_event_id;
when OTHERS then
...
return c_failure;
end log_booking_mapping;

我在子查询SELECT:

中得到关于seqnum的警告

INTO clause is expected in this SELECT statement

作为回顾，下面是我得到警告的查询(及其子查询):

select EVENT_ID into v_event_id 
from (
select EVENT_ID, row_number() over (order by UPDATE_DATE desc) as seqnum
from BOOKING_MAPPING_LOG
WHERE BOOKING_ID = pi_booking_id
)
WHERE seqnum  = 1;

我不知道为什么会这样，因为我试图在子查询中创建两列，并将它们传递给外部查询以进一步选择变量。我不想把子查询的结果在一个变量的外部查询，所以为什么我要使用INTO的子查询在这种情况下?

Answer 1

这段代码是你的错:

SQL> declare
2    v_event_id varchar2(10);
3  begin
4    select 'x' into v_event_id
5    from (select 'x' event_id,
6                 row_number() over (order by null) seqnum
7          from dual
8         )
9    where seqnum = 1;
10  end;
11  /
PL/SQL procedure successfully completed.
SQL>

对我来说，它工作正常，没有错误。

---

虽然，你说你是

得到警告关于子查询SELECT:

INTO子句在这个SELECT语句

是什么引起了这个警告?它是Oracle本身(所以你得到ORA-xxxxx错误)，或者它是一个(GUI?)工具(如果是，哪个)?

同样，这是否意味着你可以成功地运行你的代码，或者这是"警告"?阻止你这么做?

Answer 2

从您给出的代码中，它不会引发关于INTO clause is expected in this SELECT statement的错误。

你的代码有问题:

• 在你给我们看的代码中没有定义C_SuCCESS或C_FAILURE变量。
• TRIM(BOTH FROM pi_event_id) = ''永远不会为真，因为在Oracle中，''和NULL是一样的，你需要使用TRIM(BOTH FROM pi_event_id) IS NULL。
• 如果DUP_VAL_ON_INDEX被引发，则没有RETURN语句，并且将引发运行时异常。

这段代码成功编译:

CREATE OR REPLACE function log_booking_mapping(
pi_booking_id    in         VARCHAR2
,pi_event_id      in         VARCHAR2
-- ...
,po_return        out        my_return_typ
)
return integer
as
-- ...
v_event_id varchar2(200) := null;
c_failure  CONSTANT INTEGER := 0;
c_success  CONSTANT INTEGER := 1;
begin
IF TRIM(BOTH FROM pi_event_id) IS NULL THEN
begin
select EVENT_ID
into   v_event_id 
from (
select EVENT_ID, row_number() over (order by UPDATE_DATE desc) as seqnum
from BOOKING_MAPPING_LOG
WHERE BOOKING_ID = pi_booking_id
)
WHERE seqnum  = 1;

IF v_event_id IS NULL THEN
return c_failure;
END IF;
exception
when NO_DATA_FOUND then
return c_failure;
end;
ELSE
v_event_id := pi_event_id;
END IF;
insert into BOOKING_MAPPING_LOG (
event_id,
update_date,
booking_id
) values (
pi_event_id,
SYSDATE,
pi_booking_id
);
po_return := MY_RETURN_TYP('x', 'y');
return c_success;
exception
WHEN DUP_VAL_ON_INDEX THEN
UPDATE BOOKING_MAPPING_LOG
SET update_date = SYSDATE
WHERE BOOKING_ID = pi_booking_id AND EVENT_ID = v_event_id;
RETURN c_success;
when OTHERS then
-- ...
return c_failure;
end log_booking_mapping;
/

小提琴