本文以「员工表查询」为场景，演示Oracle Pipelined Function（管道函数）的使用方法，所有SQL可直接复制到Oracle客户端（如PL/SQL Developer、Navicat）依次执行，快速查看效果。

一、场景说明

假设存在员工表EMP，需封装一个函数，传入部门编号，即可查询该部门的所有员工列表。

二、操作步骤

第一步：创建测试表与数据（优先执行）

-- 1. 创建测试表EMP
CREATE TABLE EMP (
    EMPNO    NUMBER(4) PRIMARY KEY,  -- 员工编号（主键）
    ENAME    VARCHAR2(10),           -- 员工姓名
    JOB      VARCHAR2(9),            -- 职位
    SAL      NUMBER(7,2),            -- 薪水
    DEPTNO   NUMBER(2)               -- 部门编号
);
-- 2. 插入测试数据
INSERT INTO EMP VALUES (7369, 'SMITH', 'CLERK', 800.00, 20);
INSERT INTO EMP VALUES (7499, 'ALLEN', 'SALESMAN', 1600.00, 30);
INSERT INTO EMP VALUES (7521, 'WARD', 'SALESMAN', 1250.00, 30);
INSERT INTO EMP VALUES (7566, 'JONES', 'MANAGER', 2975.00, 20);
INSERT INTO EMP VALUES (7698, 'BLAKE', 'MANAGER', 2850.00, 30);
INSERT INTO EMP VALUES (7782, 'CLARK', 'MANAGER', 2450.00, 10);
-- 提交数据，确保插入生效
COMMIT;

第二步：创建对象类型（Object Type）

定义函数返回结果的结构（即“每一行数据的格式”），分为行对象和表对象（行对象的集合）。

-- 1. 创建行对象类型（对应EMP表的字段结构）
CREATE OR REPLACE TYPE TY_EMP_ROW AS OBJECT (
    EMPNO  NUMBER(4),
    ENAME  VARCHAR2(10),
    JOB    VARCHAR2(9),
    SAL    NUMBER(7,2),
    DEPTNO NUMBER(2)
);
/
-- 2. 创建表对象类型（存储多个TY_EMP_ROW行对象，即结果集）
CREATE OR REPLACE TYPE TY_EMP_TAB IS TABLE OF TY_EMP_ROW;
/

第三步：创建核心函数（Pipelined Function）

封装查询逻辑，接收部门编号作为输入参数，返回对应部门的员工列表（表对象类型）。

CREATE OR REPLACE FUNCTION FUNC_GET_EMP_LIST(
    P_DEPTNO IN NUMBER  -- 输入参数：部门编号（需查询的部门）
) RETURN TY_EMP_TAB PIPELINED IS
    -- 定义游标，封装查询逻辑（可替换为实际复杂SQL）
    CURSOR cur_emp IS
        SELECT EMPNO, ENAME, JOB, SAL, DEPTNO
        FROM EMP
        WHERE DEPTNO = P_DEPTNO; -- 用输入参数过滤部门
BEGIN
    -- 循环游标，逐行推送数据到结果集
    FOR rec IN cur_emp LOOP
        -- 将游标中的每行数据封装为行对象，推送至结果集
        PIPE ROW (TY_EMP_ROW(
            rec.EMPNO,  -- 对应行对象的EMPNO字段
            rec.ENAME,  -- 对应行对象的ENAME字段
            rec.JOB,    -- 对应行对象的JOB字段
            rec.SAL,    -- 对应行对象的SAL字段
            rec.DEPTNO  -- 对应行对象的DEPTNO字段
        ));
    END LOOP;
    RETURN; -- 结束函数
END FUNC_GET_EMP_LIST;
/

第四步：函数调用（查询结果）

调用方式简单，如同查询普通表，只需传入部门编号即可获取对应员工列表。

-- 示例1：查询30部门的所有员工
SELECT * FROM TABLE(FUNC_GET_EMP_LIST(30));
-- 示例2：查询20部门的所有员工
SELECT * FROM TABLE(FUNC_GET_EMP_LIST(20));
-- 示例3：查询10部门的所有员工
SELECT * FROM TABLE(FUNC_GET_EMP_LIST(10));

三、核心总结（套用到复杂SQL）

1. 对应关系

• 示例中 SELECT EMPNO, ENAME, JOB, SAL, DEPTNO FROM EMP\.\.\. → 替换为实际业务中的复杂SQL（几百行SQL直接替换此处）。

• 示例中输入参数 P\_DEPTNO → 替换为实际业务中的输入参数（如P_RECORD_CODE、P_CITY_CODE）。

2. 关键操作

只需将复杂SQL最外层SELECT的所有字段，按顺序填入以下两处：

• 行对象类型 TY\_EMP\_ROW 的定义中（与字段顺序、类型完全一致）。

• 函数中 PIPE ROW\(TY\_EMP\_ROW\(\.\.\.\)\) 的括号内（与行对象字段顺序一致）。

四、优点

1. 网络传输高效：应用端仅需执行 SELECT \* FROM TABLE\(函数名\(参数\)\)，SQL语句极短，减少网络传输量。

2. 性能无损耗：查询逻辑在数据库端执行，性能与直接执行复杂SQL完全一致，避免应用端处理大量数据。

（注：文档部分内容可能由 AI 生成）