在我的 oracle PL/SLQ 函数中返回了我的光标，但并非所有行都被返回.您只能在 Oracle pl/sql 函数中返回 1 行吗? [英] Returned my Cursor in my oracle PL/SLQ function but not all rows are being returned. Can you only return 1 row in a Oracle pl/sql function?

问题描述

这是我的代码，我有 2 行共享相同的名称、预订日期和酒店 ID.我不明白为什么当我执行这个函数时，它给了我错误精确获取返回的行数超过请求的行数".而不是在我的预订表中返回我的两行.

Here is my code I have 2 rows that share the same name, reservation date, and hotel Id. I don't understand why when I execute this function it gives me the error "exact fetch returns more than requested number of rows" instead of returning my both rows in my Reservation Table.

我假设正确返回了游标，所以它应该可以工作?

I have returned the cursor correctly I assume, so it should work?

CREATE OR REPLACE FUNCTION findres(cname   IN reservation.cust_name%type,
                                   hotelID IN reservation.hotel_id%type,
                                   resdate IN reservation.reserve_date%type)
  RETURN reservation.reserve_id%type is
   resid reservation.reserve_id%type;
BEGIN
    SELECT reserve_id
      INTO resid
      FROM reservation
     WHERE Cust_name = cname
       AND Hotel_id = hotelID
       AND reserve_date = resdate;
    RETURN resid;

  EXCEPTION
    WHEN no_data_found THEN
      dbms_output.put_line('No reservation found'); 
END;
/

推荐答案

来自 into_clause 定义的文档:SELECT INTO 语句从一行中检索一个或多个列并存储它们在一个或多个标量变量或一个记录变量中

那么当前的 SELECT 语句应该针对返回多于一行的情况进行替换.以下查询可能是您当前 SQL Select 语句的替代方案

Then the current SELECT statement should be replaced against the cases of returning more than one row. The following queries might be alternatives for your current SQL Select statement

SELECT reserve_id
  INTO resid
  FROM
  ( SELECT r.*,
           ROW_NUMBER() OVER (ORDER BY 0) AS rn
      FROM reservation
     WHERE Cust_name = cname
       AND Hotel_id = hotelID
       AND reserve_date = resdate
   )
  WHERE rn = 1;

如果数据库版本是 12+，则使用

If DB version is 12+, then use

SELECT reserve_id
  INTO resid
  FROM reservation
 WHERE Cust_name = cname
   AND Hotel_id = hotelID
   AND reserve_date = resdate
 FETCH NEXT 1 ROW ONLY; 

没有子查询以便仅返回一行，考虑到您只会获得那些没有数据排序规则的列的重复项.通过使用这些查询，无需处理 no_data_found 或 too_many_rows 异常.

without a subquery in order to return one row only, considering you only get duplicates for those columns with no ordering rules for the data. Through use of these queries, no need to handle no_data_found or too_many_rows exceptions.

更新:如果您的目标是返回所有行，即使一次多于一行，那么您可以使用 SYS_REFCURSOR 如

Update : If your aim is to return all the rows even there are more than one row at once, then you can use SYS_REFCURSOR such as

CREATE OR REPLACE FUNCTION findres(cname   reservation.cust_name%type,
                                   hotelID reservation.hotel_id%type,
                                   resdate reservation.reserve_date%type)
  RETURN SYS_REFCURSOR IS
  recordset SYS_REFCURSOR;
BEGIN
  OPEN recordset FOR
  SELECT reserve_id
    FROM reservation
   WHERE Cust_name = cname
     AND Hotel_id = hotelID 
     AND reserve_date = resdate;

  RETURN recordset;
END;
/

并以这样的方式调用

VAR   v_rc REFCURSOR
EXEC :v_rc := findres('Avoras',111,date'2020-12-06');
PRINT v_rc

来自 SQL Developer 的控制台.

from the SQL Developer's console.

这篇关于在我的 oracle PL/SLQ 函数中返回了我的光标，但并非所有行都被返回.您只能在 Oracle pl/sql 函数中返回 1 行吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！