在之前很多数据库国产化改造项目中，我们遇到了很多难题，其中一个难点在于重度使用Oracle的一些用户使用了大量的管道函数（pipeline）。在之前的版本中，由于MogDB还不支持pipeline，因此给我们造成了不小的麻烦。但是凭借团队极强的代码改写和优化能力，我们能够完美的解决这个问题。
实际上主要是因为MogDB 5.0就已经支持了table()函数，因此要解决这个问题，也不算太困难。
这里给大家一些演示例子。
构造测试用例
如下是一段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;
调用上述table函数执行的结果如下所示：
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
那么上述代码如果要移植到MogDB 有哪些解决方案呢？ 这里分享几个。
改写方案1
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
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;
/
改写完毕之后，我们来看看查询效果。
xxdb=> select * from table(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区别不太大，主要是函数部分与上面稍有区别，如下是改写后的代码，以供参考。
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
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;
当然如果这里我们不使用table()函数，是否还有解决方案呢？答案是肯定的，那就是直接改为表函数的方式。针对不使用table()函数的方式，这里我们也提供了2个改写的方法。
不使用table() 改写方案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;
这里来看看改写的效果如何。
xxdb=> 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种处理方案，这也共享一下改写示例代码，供大家参考，如下所示。
不使用table() 改写方案2
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;
不使用table() 改写方案3
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;
对于前面提到的table()函数的用法说明，大家可以参考： https://docs.mogdb.io/zh/mogdb/v5.0/support-table-function#%E7%89%B9%E6%80%A7%E7%BA%A6%E6%9D%9F
本文由 mdnice 多平台发布