Oracle PL/SQL PIPELINED 表函数详解

适用版本：Oracle Database 10g / 11g / 12c / 19c / 23ai 文档版本：v1.0 / 2026-07

1. 概述

PIPELINED 表函数流式返回结果[1]：

2. 基本

2.1 类型

CREATE OR REPLACE TYPE emp_obj AS OBJECT (
  id NUMBER,
  name VARCHAR2(100),
  salary NUMBER
);
/

CREATE OR REPLACE TYPE emp_tab AS TABLE OF emp_obj;
/

2.2 函数

CREATE OR REPLACE FUNCTION get_emp(p_dept_id NUMBER) 
  RETURN emp_tab PIPELINED
IS
  v_emp emp_obj;
BEGIN
  FOR rec IN (SELECT id, name, salary FROM employees WHERE dept_id = p_dept_id) LOOP
    PIPE ROW(emp_obj(rec.id, rec.name, rec.salary));
  END LOOP;
  
  RETURN;
END;
/

2.3 查询

SELECT * FROM TABLE(get_emp(10));

SELECT * FROM TABLE(get_emp(10)) WHERE salary > 5000;

3. PARALLEL_ENABLE

CREATE OR REPLACE FUNCTION get_emp_parallel(p_dept_id NUMBER)
  RETURN emp_tab
  PARALLEL_ENABLE (PARTITION cur BY ANY)
  PIPELINED
IS
BEGIN
  FOR rec IN (SELECT ... ) LOOP
    PIPE ROW(...);
  END LOOP;
  RETURN;
END;
/

4. 流式处理

4.1 CURSOR 参数

CREATE OR REPLACE FUNCTION transform_emp(p_cur SYS_REFCURSOR)
  RETURN emp_tab PIPELINED
IS
  v_id NUMBER;
  v_name VARCHAR2(100);
  v_salary NUMBER;
BEGIN
  LOOP
    FETCH p_cur INTO v_id, v_name, v_salary;
    EXIT WHEN p_cur%NOTFOUND;
    
    -- 转换
    v_salary := v_salary * 1.1;
    
    PIPE ROW(emp_obj(v_id, v_name, v_salary));
  END LOOP;
  
  CLOSE p_cur;
  RETURN;
END;
/

-- 使用
SELECT * FROM TABLE(transform_emp(
  CURSOR(SELECT id, name, salary FROM employees)
));

4.2 链式

SELECT * FROM TABLE(transform_emp(
  CURSOR(SELECT * FROM TABLE(filter_emp(...)))
));

5. 聚合

5.1 输出多行

CREATE OR REPLACE FUNCTION split_string(p_str VARCHAR2, p_sep VARCHAR2 := ',')
  RETURN SYS.ODCIVARCHAR2LIST PIPELINED
IS
  v_idx NUMBER;
  v_str VARCHAR2(4000) := p_str;
BEGIN
  LOOP
    v_idx := INSTR(v_str, p_sep);
    IF v_idx > 0 THEN
      PIPE ROW(SUBSTR(v_str, 1, v_idx - 1));
      v_str := SUBSTR(v_str, v_idx + 1);
    ELSE
      PIPE ROW(v_str);
      EXIT;
    END IF;
  END LOOP;
  RETURN;
END;
/

-- 使用
SELECT * FROM TABLE(split_string('a,b,c,d'));

6. 异常处理

CREATE OR REPLACE FUNCTION safe_get_emp(p_dept NUMBER)
  RETURN emp_tab PIPELINED
IS
BEGIN
  FOR rec IN (SELECT ... WHERE dept_id = p_dept) LOOP
    PIPE ROW(...);
  END LOOP;
  RETURN;
EXCEPTION
  WHEN OTHERS THEN
    -- 必须 PIPE 或 RETURN
    RETURN;
END;
/

7. 性能

7.1 流式

- 不需全部加载
- 内存友好
- 实时

7.2 PARALLEL_ENABLE

- 并行
- 性能
- 大数据

7.3 vs 普通

- PIPELINED：流式
- 普通：全部内存

详细见：Oracle PL/SQL 性能优化详解。

8. 应用场景

8.1 数据转换

CREATE OR REPLACE FUNCTION transform_data(p_src SYS_REFCURSOR)
  RETURN result_tab PIPELINED
IS
  ...
BEGIN
  LOOP
    FETCH p_src INTO ...;
    -- 转换
    PIPE ROW(...);
  END LOOP;
END;
/

INSERT INTO target SELECT * FROM TABLE(transform_data(
  CURSOR(SELECT * FROM source)
));

8.2 文件解析

CREATE OR REPLACE FUNCTION parse_file(p_dir VARCHAR2, p_file VARCHAR2)
  RETURN data_tab PIPELINED
IS
  v_file UTL_FILE.FILE_TYPE;
  v_line VARCHAR2(4000);
BEGIN
  v_file := UTL_FILE.FOPEN(p_dir, p_file, 'R');
  LOOP
    UTL_FILE.GET_LINE(v_file, v_line);
    -- 解析
    PIPE ROW(...);
  END LOOP;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    UTL_FILE.FCLOSE(v_file);
    RETURN;
END;
/

SELECT * FROM TABLE(parse_file('DATA_DIR', 'data.csv'));

8.3 字符串拆分

-- 已示例
SELECT t.column_value 
FROM TABLE(split_string('a,b,c')) t;

8.4 Web Service

CREATE OR REPLACE FUNCTION get_weather(p_city VARCHAR2)
  RETURN weather_tab PIPELINED
IS
  v_response VARCHAR2(4000);
BEGIN
  v_response := UTL_HTTP.REQUEST('http://api.weather.com/' || p_city);
  -- 解析 JSON
  PIPE ROW(...);
  RETURN;
END;
/

9. ANY 类型

9.1 系统类型

-- SYS.ODCIVARCHAR2LIST
-- SYS.ODCINUMBERLIST
-- SYS.ODCIDATELIST

CREATE OR REPLACE FUNCTION get_ids(p_dept NUMBER)
  RETURN SYS.ODCINUMBERLIST PIPELINED
IS
BEGIN
  FOR rec IN (SELECT id FROM employees WHERE dept_id = p_dept) LOOP
    PIPE ROW(rec.id);
  END LOOP;
  RETURN;
END;
/

SELECT * FROM TABLE(get_ids(10));

10. 多类型

10.1 多行多列

CREATE OR REPLACE TYPE sale_rec AS OBJECT (
  sale_date DATE,
  amount NUMBER,
  region VARCHAR2(50)
);
/

CREATE OR REPLACE TYPE sale_tab AS TABLE OF sale_rec;
/

CREATE OR REPLACE FUNCTION generate_sales(p_year NUMBER)
  RETURN sale_tab PIPELINED
IS
BEGIN
  FOR m IN 1..12 LOOP
    FOR r IN ('East', 'West', 'Central') LOOP
      PIPE ROW(sale_rec(
        TO_DATE(p_year || '-' || m || '-01', 'YYYY-MM-DD'),
        DBMS_RANDOM.VALUE(1000, 10000),
        r
      ));
    END LOOP;
  END LOOP;
  RETURN;
END;
/

SELECT * FROM TABLE(generate_sales(2025));

11. 与普通表函数

项	PIPELINED	普通
内存	流式	全部
实时	是	否
性能	大数据	小数据
复杂	中	简

12. 常见坑与排错

12.1 ORA-06512

- PIPE ROW 类型不匹配
- 检查

12.2 内存

- 普通函数全部内存
- PIPELINED 流式

12.3 并行

- PARALLEL_ENABLE
- CURSOR 参数

13. 最佳实践

PIPELINED：流式
PARALLEL_ENABLE：并行
CURSOR 参数：链式
类型：明确
异常处理：完整
RETURN：结束
ANY 类型：简化
测试：验证
性能：大数据
文档化：说明

14. 参考资料

[1] Oracle Database PL/SQL Language Reference 19c, “Pipelined Table Functions” https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/plsql-optimization-and-tuning.html