— 创建表类型
CREATE OR REPLACE
TYPE
emp_table AS
TABLE
OF emp_record;
/

— 创建示例表
CREATE TABLE
fgedu_employees (
emp_id NUMBER PRIMARY KEY,
emp_name VARCHAR2(100),
emp_dept VARCHAR2(50),
emp_salary NUMBER
);
/

— 插入示例数据
INSERT
IN
TO fgedu_employees VALUES (1001, ‘风哥1号’, ‘技术部’, 5000);
INSERT
IN
TO fgedu_employees VALUES (1002, ‘风哥2号’, ‘市场部’, 6000);
INSERT
IN
TO fgedu_employees VALUES (1003, ‘王五’, ‘技术部’, 5500);
INSERT
IN
TO fgedu_employees VALUES (1004, ‘赵六’, ‘市场部’, 6500);
INSERT
IN
TO fgedu_employees VALUES (1005, ‘孙七’, ‘财务部’, 5800);
COMMIT
;
/

— 创建返回记录类型的管道函数
CREATE OR REPLACE
FUNCTION
get_employees (p_dept IN
VARCHAR2) RETURN
emp_table PIPELIN
ED IS
CURSOR emp_cursor IS
SELECT
emp_id, emp_name, emp_dept, emp_salary
FROM fgedu_employees
WHERE emp_dept = p_dept;
emp_rec emp_cursor%ROWTYPE
;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor IN
TO emp_rec;
EXIT WHEN emp_cursor%NOTFOUND;
PIPE ROW(emp_record(emp_rec.emp_id, emp_rec.emp_name, emp_rec.emp_dept, emp_rec.emp_salary));
END LOOP
;
CLOSE emp_cursor;
RETURN
;
END
;
/

— 使用返回记录类型的管道函数
SELECT
*
FROM TABLE
(get_employees(‘技术部’));

— 创建带并行执行的管道函数
CREATE OR REPLACE
FUNCTION
get_employees_parallel (p_dept IN
VARCHAR2) RETURN
emp_table PIPELIN
ED
PARALLEL_ENABLE (PARTITION p_dept BY ANY) IS
CURSOR emp_cursor IS
SELECT
emp_id, emp_name, emp_dept, emp_salary
FROM fgedu_employees
WHERE emp_dept = p_dept;
emp_rec emp_cursor%ROWTYPE
;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor IN
TO emp_rec;
EXIT WHEN emp_cursor%NOTFOUND;
PIPE ROW(emp_record(emp_rec.emp_id, emp_rec.emp_name, emp_rec.emp_dept, emp_rec.emp_salary));
END LOOP
;
CLOSE emp_cursor;
RETURN
;
END
;
/

— 使用带并行执行的管道函数
SELECT
/
*+ PARALLEL(4) */
*
FROM TABLE
(get_employees_parallel(‘市场部’));