适用范围
Oracle迁移到磐维分布式
问题概述
迁移一个项目时发现客户的系统中大量的使用了管道函数（pipeline），磐维分布式目前暂时不支持pipeline。如果迁移为表函数，使用时并不支持函数table()，改动较大。但是磐维分布式支持unnest函数，只要返回数据集即可。因此可以把oracle中的管道函数进行改造，改为返回数据集。
Oracle中源码示例
CREATE OR REPLACE TYPE type_emp_row AS OBJECT
(
empno NUMBER(4),
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2)
)
/
CREATE OR REPLACE TYPE type_emp IS TABLE OF type_emp_row;
/
CREATE OR REPLACE FUNCTION f_get_emp(p_deptno NUMBER) RETURN type_emp
PIPELINED AS
v_emp type_emp_row;
BEGIN
FOR cur IN (SELECT * FROM scott.emp WHERE deptno = p_deptno) LOOP
v_emp := type_emp_row(cur.empno,
cur.ename,
cur.job,
cur.mgr,
cur.hiredate,
cur.sal,
cur.comm,
cur.deptno);
PIPE ROW(v_emp);
END LOOP;
END;
SQL> select * From table(f_get_emp(10));
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ----------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 1981/6/9 2450 10
7839 KING PRESIDENT 1981/11/17 5000 10
7934 MILLER CLERK 7782 1982/1/23 1300 10
解决方案
方案1:返回数组
返回数组，并用unnest()代替table()
1、函数中返回数组
1.1 函数中循环处理
适合在循环中增加逻辑处理
CREATE TYPE type_emp_row AS
(
empno NUMBER(4),
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2)
);
CREATE TYPE type_emp IS TABLE OF type_emp_row;
CREATE OR REPLACE FUNCTION f_get_emp(p_deptno NUMBER) RETURN type_emp
AS
v_emp type_emp_row;
res_emp type_emp := type_emp();
BEGIN
FOR cur IN (SELECT * FROM scott.emp WHERE deptno = p_deptno) LOOP
v_emp := type_emp_row(cur.empno,
cur.ename,
cur.job,
cur.mgr,
cur.hiredate,
cur.sal,
cur.comm,
cur.deptno);
res_emp.extend;
res_emp(res_emp.last)=v_emp;
END LOOP;
return res_emp;
END;
/
select * from unnest(f_get_emp(10));
empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+--------+-----------+------+---------------------+---------+------+--------
7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | | 10
7839 | KING | PRESIDENT | | 1981-11-17 00:00:00 | 5000.00 | | 10
7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | | 10
(3 rows)
1.2 函数中批量处理
不能在循环中增加逻辑处理，可以批量返回
CREATE TYPE type_emp_row AS
(
empno NUMBER(4),
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2)
);
CREATE TYPE type_emp IS TABLE OF type_emp_row;
/
CREATE OR REPLACE FUNCTION f_get_emp(p_deptno NUMBER) RETURN type_emp
AS
res_emp type_emp := type_emp();
BEGIN
SELECT * bulk collect into res_emp FROM scott.emp WHERE deptno = p_deptno;
return res_emp;
END;
/
select * from unnest(f_get_emp(10));
2、包中返回数组
方式同上面一样，只是代码放在包中，不需要单独定义TYPE对象
2.1 包中循环处理
适合在循环中增加逻辑处理
CREATE OR REPLACE PACKAGE scott.pkg_emp AS
TYPE type_emp_row IS RECORD (
empno NUMBER(4),
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2)
);
TYPE type_emp IS TABLE OF type_emp_row;
FUNCTION f_get_emp(p_deptno NUMBER) RETURN type_emp;
END pkg_emp;
/
CREATE OR REPLACE PACKAGE BODY scott.pkg_emp AS
FUNCTION f_get_emp(p_deptno NUMBER) RETURN type_emp
AS
v_emp type_emp_row;
res_emp type_emp := type_emp();
BEGIN
FOR cur IN (SELECT * FROM scott.emp WHERE deptno = p_deptno) LOOP
v_emp.empno := cur.empno;
v_emp.ename := cur.ename;
v_emp.job := cur.job;
v_emp.mgr := cur.mgr;
v_emp.hiredate := cur.hiredate;
v_emp.sal := cur.sal;
v_emp.comm := cur.comm;
v_emp.deptno := cur.deptno;
res_emp.extend;
res_emp(res_emp.last) := v_emp;
END LOOP;
RETURN res_emp;
END f_get_emp;
END pkg_emp;
/
SELECT * FROM unnest(scott.pkg_emp.f_get_emp(10));
2.2 包中批量处理
不能在循环中增加逻辑处理，可以批量返回
CREATE OR REPLACE PACKAGE scott.pkg_emp AS
TYPE type_emp_row IS RECORD (
empno NUMBER(4),
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2)
);
TYPE type_emp IS TABLE OF type_emp_row;
FUNCTION f_get_emp(p_deptno NUMBER) RETURN type_emp;
END pkg_emp;
/
CREATE OR REPLACE PACKAGE BODY scott.pkg_emp AS
FUNCTION f_get_emp(p_deptno NUMBER) RETURN type_emp
AS
v_emp type_emp_row;
res_emp type_emp := type_emp();
BEGIN
SELECT * bulk collect into res_emp FROM scott.emp WHERE deptno = p_deptno;
RETURN res_emp;
END f_get_emp;
END pkg_emp;
/
SELECT * FROM unnest(scott.pkg_emp.f_get_emp(10));
方案2:表函数
2.1 自定义返回类型
自定义返回类型，并且可通过循环处理数据
drop function if exists scott.f_get_emp;
CREATE OR REPLACE FUNCTION scott.f_get_emp(p_deptno NUMBER)
RETURNS TABLE (
empno INTEGER,
ename VARCHAR(10),
job VARCHAR(9),
mgr INTEGER,
hiredate DATE,
sal NUMERIC(7,2),
comm NUMERIC(7,2),
deptno INTEGER
) AS $$
BEGIN
FOR cur IN (SELECT * FROM scott.emp WHERE deptno = p_deptno) LOOP
empno :=cur.empno ;
ename :=cur.ename ;
job :=cur.job ;
mgr :=cur.mgr ;
hiredate :=cur.hiredate ;
sal :=cur.sal ;
comm :=cur.comm ;
deptno :=cur.deptno ;
RETURN NEXT;
END LOOP;
END;
$$ LANGUAGE plpgsql;
SELECT * FROM scott.f_get_emp(10);
empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+--------+-----------+------+---------------------+---------+------+--------
7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | | 10
7839 | KING | PRESIDENT | | 1981-11-17 00:00:00 | 5000.00 | | 10
7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | | 10
(3 rows)
2.2 返回与表结构一样的数据
返回列与表结构一样，直接使用setof方式，可在循环中处理数据
drop function if exists scott.f_get_emp;
CREATE OR REPLACE FUNCTION scott.f_get_emp(p_deptno NUMBER)
RETURNS setof scott.emp AS $$
BEGIN
FOR emp_row IN (SELECT * FROM scott.emp WHERE deptno = p_deptno) LOOP
-- 使用 RETURN NEXT 返回结果集中的一行
RETURN NEXT emp_row;
END LOOP;
END;
$$ LANGUAGE plpgsql;
SELECT * FROM scott.f_get_emp(10);
2.3 RETURN QUERY
直接返回查询结果，中间不做处理，也可以同2.1一样自定义返回类型
drop function if exists scott.f_get_emp;
CREATE OR REPLACE FUNCTION scott.f_get_emp(p_deptno NUMBER)
RETURNS setof scott.emp AS $$
BEGIN
RETURN QUERY SELECT * FROM scott.emp e WHERE deptno = p_deptno;
END;
$$ LANGUAGE plpgsql;
SELECT * FROM scott.f_get_emp(10);
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
