Oracle PL/SQL调用返回REF CURSOR的多记录函数如何处理

你原代码报错的原因是sb_transfer_crse.f_query_all返回的是transfer_crse_ref类型的引用游标，不是单行结果，不能直接用SELECT ... INTO给单组变量赋值，需要先接收游标再处理，以下是两种常见实现方案：

适合需要对每条记录做个性化业务处理的场景，实现代码如下：

DECLARE
  -- 入参变量，填写你要查询的机构编码
  VAR_p_sbgi_code shbtatc.shbtatc_sbgi_code%TYPE := '000497';
  -- 接收函数返回的引用游标
  v_crse_cur transfer_crse_ref;
  -- 存储单条返回记录的变量，和函数返回的record类型完全匹配
  v_crse_rec transfer_crse_rec;
BEGIN
  -- 调用函数获取结果游标
  v_crse_cur := sb_transfer_crse.f_query_all(p_sbgi_code => VAR_p_sbgi_code);
  
  -- 循环遍历游标所有记录
  LOOP
    FETCH v_crse_cur INTO v_crse_rec;
    -- 游标遍历结束时退出循环
    EXIT WHEN v_crse_cur%NOTFOUND;
    
    -- 此处编写单条记录的处理逻辑，示例为打印课程信息
    DBMS_OUTPUT.PUT_LINE('课程编码：' || v_crse_rec.r_subj_code_trns || v_crse_rec.r_crse_numb_trns || '，课程名称：' || v_crse_rec.r_trns_title);
  END LOOP;
  
  -- 关闭游标释放资源
  CLOSE v_crse_cur;
EXCEPTION
  WHEN OTHERS THEN
    -- 异常场景下也需关闭游标避免资源泄漏
    IF v_crse_cur%ISOPEN THEN
      CLOSE v_crse_cur;
    END IF;
    RAISE;
END;
/

如果不需要逐行处理，直接全量落地数据的话，可按以下步骤操作：

步骤1：创建存储数据的表 #

可根据需求创建临时表或普通永久表，字段和transfer_crse_rec定义对齐即可，示例：

-- 创建会话级临时表，也可替换为普通CREATE TABLE语句创建永久表
CREATE GLOBAL TEMPORARY TABLE tmp_transfer_crse (
  sbgi_code               shbtatc.shbtatc_sbgi_code%TYPE,
  program                 shbtatc.shbtatc_program%TYPE,
  tlvl_code               shbtatc.shbtatc_tlvl_code%TYPE,
  subj_code_trns          shbtatc.shbtatc_subj_code_trns%TYPE,
  crse_numb_trns          shbtatc.shbtatc_crse_numb_trns%TYPE,
  trns_title              shbtatc.shbtatc_trns_title%TYPE,
  term_code_eff_trns      shbtatc.shbtatc_term_code_eff_trns%TYPE,
  trns_low_hrs            shbtatc.shbtatc_trns_low_hrs%TYPE,
  trns_high_hrs           shbtatc.shbtatc_trns_high_hrs%TYPE
  -- 其余需要存储的字段可参考transfer_crse_rec的定义自行补充
) ON COMMIT PRESERVE ROWS;

步骤2：批量插入数据 #

方式A：兼容所有Oracle版本的批量插入 #

性能最优，无版本限制：

DECLARE
  VAR_p_sbgi_code shbtatc.shbtatc_sbgi_code%TYPE := '000497';
  v_crse_cur transfer_crse_ref;
  -- 定义集合类型存储批量读取的结果
  TYPE crse_tab_type IS TABLE OF transfer_crse_rec INDEX BY PLS_INTEGER;
  v_crse_tab crse_tab_type;
BEGIN
  v_crse_cur := sb_transfer_crse.f_query_all(p_sbgi_code => VAR_p_sbgi_code);
  -- 批量读取所有结果到集合，可加LIMIT 1000参数控制单次读取条数
  FETCH v_crse_cur BULK COLLECT INTO v_crse_tab;
  CLOSE v_crse_cur;
  
  -- 批量插入到目标表
  FORALL i IN 1 .. v_crse_tab.COUNT
    INSERT INTO tmp_transfer_crse(
      sbgi_code, program, tlvl_code, subj_code_trns, crse_numb_trns, trns_title, term_code_eff_trns, trns_low_hrs, trns_high_hrs
    ) VALUES (
      v_crse_tab(i).r_sbgi_code, v_crse_tab(i).r_program, v_crse_tab(i).r_tlvl_code, v_crse_tab(i).r_subj_code_trns,
      v_crse_tab(i).r_crse_numb_trns, v_crse_tab(i).r_trns_title, v_crse_tab(i).r_term_code_eff_trns,
      v_crse_tab(i).r_trns_low_hrs, v_crse_tab(i).r_trns_high_hrs
    );
  COMMIT;
END;
/

方式B：Oracle 12c+ 简化写法 #

如果数据库版本为12c及以上，可直接通过SQL读取游标内容插入，代码更简洁：

INSERT INTO tmp_transfer_crse(
  sbgi_code, program, tlvl_code, subj_code_trns, crse_numb_trns, trns_title
)
SELECT 
  r_sbgi_code, r_program, r_tlvl_code, r_subj_code_trns, r_crse_numb_trns, r_trns_title
FROM TABLE(sb_transfer_crse.f_query_all(p_sbgi_code => '000497'));

提示：如果方式B执行报错提示类型不识别，降级使用方式A即可。

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