Oracle函数返回表全列问题求助：改写查询获取多列

嘿，我懂你现在的困境——原来的函数只能返回单个赔率数值，现在想要把比赛表（LIVE）和赔率表（ODDS）的所有列都查出来对吧？之前尝试用游标没拿到结果，大概率是返回类型不匹配或者查询逻辑的小问题，我给你梳理两个靠谱的解决方案。

这是最直接的方式，Oracle的引用游标（REF CURSOR）可以封装多行多列的查询结果，正好满足你的需求。

1. 修改函数为返回REF CURSOR #

把原来返回NUMBER的函数改成返回SYS_REFCURSOR，同时调整查询逻辑（注意把原来的外连接条件优化，避免意外变成内连接）：

CREATE OR REPLACE FUNCTION checkLiveSoccerOdd (p_FIXID VARCHAR2, p_TYPE VARCHAR2, p_RES VARCHAR2) 
RETURN SYS_REFCURSOR IS
  v_result_cursor SYS_REFCURSOR;
BEGIN
  -- 用LEFT JOIN替代原来的(+)语法，同时把O表的过滤条件放到JOIN子句里，保留LIVE表的行
  OPEN v_result_cursor FOR
    SELECT M.*, O.*
    FROM LIVE M
    LEFT JOIN ODDS O 
      ON M.FIXID = O.FIXID 
      AND O.TYPE = p_TYPE 
      AND O.RES = p_RES
    WHERE M.FIXID = p_FIXID;
  
  RETURN v_result_cursor;
END;
/

2. 调用函数获取结果 #

你可以在PL/SQL块里遍历游标处理数据，比如：

DECLARE
  v_cursor SYS_REFCURSOR;
  -- 这里要根据LIVE和ODDS表的实际列定义声明变量，顺序要和SELECT的列一致
  v_fixid LIVE.FIXID%TYPE;
  v_live_status LIVE.STATUS%TYPE; -- 替换成LIVE表的真实列名
  v_odd ODDS.Odd%TYPE;
  v_odds_type ODDS.TYPE%TYPE;
  -- 其他列依次声明...
BEGIN
  -- 传入参数调用函数
  v_cursor := checkLiveSoccerOdd('FIX123', 'FT', '1');
  
  -- 遍历游标获取每一行数据
  LOOP
    FETCH v_cursor INTO v_fixid, v_live_status, v_odd, v_odds_type;
    EXIT WHEN v_cursor%NOTFOUND;
    
    -- 这里可以处理数据，比如打印输出
    DBMS_OUTPUT.PUT_LINE('比赛ID: ' || v_fixid || ', 赔率: ' || v_odd);
  END LOOP;
  
  CLOSE v_cursor;
END;
/

如果想在普通SQL语句里直接调用函数并获取结果（比如SELECT * FROM ...），可以自定义对象和集合类型来封装数据。

1. 定义对象类型（对应一行数据的结构） #

CREATE OR REPLACE TYPE LiveOddsRecord AS OBJECT (
  -- 按顺序列出LIVE和ODDS表的所有列，比如：
  FIXID VARCHAR2(50),
  LIVE_DATE DATE,
  LIVE_HOME_TEAM VARCHAR2(100),
  LIVE_AWAY_TEAM VARCHAR2(100),
  ODDS_TYPE VARCHAR2(20),
  ODDS_RES VARCHAR2(10),
  ODDS_VALUE NUMBER
);
/

2. 定义集合类型（存储多行对象） #

CREATE OR REPLACE TYPE LiveOddsList AS TABLE OF LiveOddsRecord;
/

3. 修改函数返回集合 #

CREATE OR REPLACE FUNCTION checkLiveSoccerOdd (p_FIXID VARCHAR2, p_TYPE VARCHAR2, p_RES VARCHAR2) 
RETURN LiveOddsList IS
  v_result_list LiveOddsList := LiveOddsList();
  v_row_index NUMBER := 0;
BEGIN
  -- 遍历查询结果，封装到集合中
  FOR rec IN (
    SELECT M.*, O.*
    FROM LIVE M
    LEFT JOIN ODDS O 
      ON M.FIXID = O.FIXID 
      AND O.TYPE = p_TYPE 
      AND O.RES = p_RES
    WHERE M.FIXID = p_FIXID
  ) LOOP
    v_row_index := v_row_index + 1;
    v_result_list.EXTEND;
    -- 按对象类型的属性顺序传入值
    v_result_list(v_row_index) := LiveOddsRecord(
      rec.FIXID, rec.DATE, rec.HOME_TEAM, rec.AWAY_TEAM,
      rec.TYPE, rec.RES, rec.Odd
    );
  END LOOP;
  
  RETURN v_result_list;
END;
/

4. 直接在SQL中调用 #

SELECT * FROM TABLE(checkLiveSoccerOdd('FIX123', 'FT', '1'));

大概率是这两个原因：

• 原来的函数返回类型是NUMBER，你用游标去接收会导致类型不匹配，要么报错要么拿不到数据
• 原来的查询用了O.FIXID(+)外连接，但后面把O.TYPE = p_TYPE和O.RES = p_RES放到了WHERE子句里，这会把外连接变成内连接（因为O表无匹配行时这些字段是NULL，无法匹配参数），所以要把O表的过滤条件放到JOIN子句里才行。

内容的提问来源于stack exchange，提问作者civesuas_sine