oracle 从 PL/SQL 函数返回多个值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23447570/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Return multiple values from PL/SQL Function
提问by OmniOwl
I need to try and find a free seat on a plane, and then reserve that seat.
我需要尝试在飞机上找到一个空闲座位，然后预订该座位。
I need to return both a value to indicate success or not as well as a seat number. By looking around I've found that functions can't return more than 1 value so I thought about using an array but looking at documentation for those made it apparent that I am not nearly good enough at PL/SQL to understand how to use them.
我需要返回一个值来指示成功与否以及座位号。通过环顾四周，我发现函数不能返回超过 1 个值，因此我考虑使用数组，但查看这些文档的文档表明我在 PL/SQL 方面还不够好，无法理解如何使用它们.
So here I am, stranded.
所以我在这里，搁浅。
The code that I got so far looks like this:
到目前为止，我得到的代码如下所示：
CREATE OR REPLACE FUNCTION RESERVE_SEAT(P_NO VARCHAR2, ID NUMBER, RESERVE_TIME NUMBER, S_NO VARCHAR2)
RETURN INTEGER AS
RES INTEGER := 0;
COUNTS INTEGER := 0;
BEGIN
SELECT COUNT(*) INTO COUNTS FROM SEAT WHERE SEAT_NO=S_NO AND PLANE_NO=P_NO;
IF(COUNTS = 1) THEN
UPDATE SEAT
SET RESERVED = ID, BOOKING_TIME = RESERVE_TIME, BOOKED=ID
WHERE PLANE_NO=P_NO AND SEAT_NO=S_NO;
COMMIT;
ELSE IF(COUNTS = 0) THEN
RES := -1;
END IF;
END IF;
RETURN RES;
END RESERVE_SEAT;
This above function is called by the one below
上面的这个函数被下面的一个调用
CREATE OR REPLACE FUNCTION GET_FREE_SEAT(P_NO VARCHAR2, ID NUMBER, RESERVE_TIME NUMBER)
RETURN INTEGER AS
RESERVED_SEAT_NO VARCHAR2(100) := 'NULL';
RES INTEGER := 0; -- Assume Success Scenario from the Get-go
BEGIN
SELECT SEAT_NO INTO RESERVED_SEAT_NO
FROM SEAT
WHERE RESERVED IS NULL AND BOOKED IS NULL AND ROWNUM = 1
OR BOOKED IS NULL AND ((RESERVE_TIME - 5000) <= BOOKING_TIME) AND ROWNUM = 1;
IF(RESERVED_SEAT_NO != 'NULL') THEN
RES := RESERVE_SEAT(P_NO,ID,RESERVE_TIME,RESERVEd_SEAT_NO);
END IF;
RETURN RES;
END GET_FREE_SEAT;
Not really sure what to do at this point.
不太确定此时该怎么办。
How do I return both RESand RESERVED_SEAT_NO?
我如何同时返回RES和RESERVED_SEAT_NO？
回答by logeekal
There is no way you can return 2 variable. It has to be one.
您无法返回 2 个变量。它必须是一个。
You can use a custom rec type or array which you can return from the function.
您可以使用可以从函数返回的自定义 rec 类型或数组。
TYPE new_type is record(RES pls_integer, RESERVED_SEAT_NO pls_integer);
CREATE OR REPLACE FUNCTION GET_FREE_SEAT(P_NO VARCHAR2, ID NUMBER, RESERVE_TIME NUMBER)
RETURN new_type AS new_type_variable
BEGIN
SELECT SEAT_NO
INTO new_type_variable.RESERVED_SEAT_NO
FROM SEAT
WHERE
RESERVED IS NULL
AND BOOKED IS NULL
AND ROWNUM = 1
OR BOOKED IS NULL
AND (RESERVE_TIME - 5000) <= BOOKING_TIME
AND ROWNUM = 1;
IF(RESERVED_SEAT_NO != 'NULL') THEN
select RESERVE_SEAT(P_NO,ID,RESERVE_TIME,RESERVEd_SEAT_NO) into new_type_variable.res from dual;
END IF;
RETURN new_type_variable;
END GET_FREE_SEAT;
回答by Buray
OUT parameter is the best solution or using RECORD (like array) and return it.
OUT 参数是最好的解决方案或使用 RECORD（如数组）并返回它。
PROCEDURE RETURN_MULTIPLE_VAR ( INPUT IN VARCHAR2, OUT_1 OUT Varchar2, --return out1 OUT_2 OUT NUMBER ... --return out2
PROCEDURE RETURN_MULTIPLE_VAR (INPUT IN VARCHAR2, OUT_1 OUT Varchar2, --return out1 OUT_2 OUT NUMBER ... --return out2
