oracle – 从PL / SQL函数返回多个值

我必要实行在飞机上找到一个免费座位,然后保存该座位.

我必要返回一个值来暗示乐成与否以及座位号.通过环视附近,我发明函数不能返回高出1的值,以是我思量行使数组,可是查察那些文档,很明明我在PL / SQL上不太相识怎样行使它们.

以是我在这里停留了.

到今朝为止,我获得的代码如下所示：

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;

以上函数挪用上述函数

CREATE OR REPLACE FUNCTION GET_FREE_SEAT(P_NO VARCHAR2,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;

今朝还不确定要做什么.

怎样返回RES和RESERVED_SEAT_NO？

办理要领

您可以行使可以从函数返回的自界说rec范例或数组.

TYPE new_type is record(RES pls_integer,RESERVED_SEAT_NO pls_integer);

CREATE OR REPLACE FUNCTION GET_FREE_SEAT(P_NO VARCHAR2,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,RESERVEd_SEAT_NO) into new_type_variable.res from dual;
  END IF;
  RETURN new_type_variable;
END GET_FREE_SEAT;

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!