如何将Firebird存储过程CALCULA_ARTIC迁移至Oracle并实现相同查询效果？

要在Oracle中实现和你提供的Firebird存储过程完全一致的可直接通过SELECT查询结果集的行为，不能用返回REF CURSOR的函数，而是要使用表函数（Table Function），具体步骤如下：

1. 创建自定义类型 #

Oracle表函数需要明确的返回类型，先定义对应结果集的记录类型和嵌套表类型：

-- 定义单条记录的类型，对应原存储过程的返回字段
CREATE OR REPLACE TYPE CALCULA_ARTIC_REC AS OBJECT (
    COSTE VARCHAR2(100),
    PROVEEDOR VARCHAR2(100),
    IDARTICULO INTEGER
);
/

-- 定义该记录类型的嵌套表类型，用于批量返回结果
CREATE OR REPLACE TYPE CALCULA_ARTIC_TBL AS TABLE OF CALCULA_ARTIC_REC;
/

2. 创建表函数 #

将原Firebird存储过程的逻辑迁移为表函数，核心是把查询结果收集到嵌套表中返回：

CREATE OR REPLACE FUNCTION CALCULA_ARTIC (
    FECHA_INICIO DATE,
    FECHA_FIN DATE
) RETURN CALCULA_ARTIC_TBL
AS
    RESULT_TBL CALCULA_ARTIC_TBL := CALCULA_ARTIC_TBL();
BEGIN
    -- 遍历查询结果，填充嵌套表
    FOR REC IN (
        SELECT CS.COSTE, P.PROVEEDOR, A.IDARTICULO
        FROM ALBARAN A
        JOIN COSTE_SUBGRUPO CS ON CS.IDCOSTESUBGRUPO = A.IDCOSTESUBGRUPO
        JOIN PROVEEDOR P ON P.IDPROVEEDOR = A.IDPROVEEDOR
        -- 修正原Firebird代码中的表别名错误：ALB应为A
        WHERE A.FECHA BETWEEN FECHA_INICIO AND FECHA_FIN
    ) LOOP
        RESULT_TBL.EXTEND;
        RESULT_TBL(RESULT_TBL.LAST) := CALCULA_ARTIC_REC(
            REC.COSTE, REC.PROVEEDOR, REC.IDARTICULO
        );
    END LOOP;
    
    RETURN RESULT_TBL;
END;
/

3. 调用方式 #

完全和Firebird的调用语法类似，通过TABLE()函数将表函数的返回结果转换为可查询的表：

SELECT * FROM TABLE(CALCULA_ARTIC(TO_DATE('2024-01-01', 'YYYY-MM-DD'), TO_DATE('2024-01-31', 'YYYY-MM-DD')));

执行后会直接返回你需要的表格格式结果：

COSTE  PROVEEDOR  IDARTICULO
-----  ---------  ----------
13     1          125
7,5    2          89

补充说明 #

• 原Firebird存储过程中的suspend关键字是逐行返回结果的逻辑，Oracle表函数通过收集结果到嵌套表后一次性返回，最终效果一致。
• 如果处理大数据量，可使用PIPELINED关键字实现管道化表函数，逐行返回结果以减少内存占用：

  CREATE OR REPLACE FUNCTION CALCULA_ARTIC (
      FECHA_INICIO DATE,
      FECHA_FIN DATE
  ) RETURN CALCULA_ARTIC_TBL PIPELINED
  AS
  BEGIN
      FOR REC IN (
          SELECT CS.COSTE, P.PROVEEDOR, A.IDARTICULO
          FROM ALBARAN A
          JOIN COSTE_SUBGRUPO CS ON CS.IDCOSTESUBGRUPO = A.IDCOSTESUBGRUPO
          JOIN PROVEEDOR P ON P.IDPROVEEDOR = A.IDPROVEEDOR
          WHERE A.FECHA BETWEEN FECHA_INICIO AND FECHA_FIN
      ) LOOP
          PIPE ROW(CALCULA_ARTIC_REC(REC.COSTE, REC.PROVEEDOR, REC.IDARTICULO));
      END LOOP;
      
      RETURN;
  END;
  /
  

  调用方式和普通表函数完全相同。

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