MogDB如何兼容Oracle的管道函数

在之前很多数据库国产化改造项目中，我们遇到了很多难题，其中一个难点在于重度使用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;

1

调用上述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

1

那么上述代码如果要移植到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;
/

1

改写完毕之后，我们来看看查询效果。

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)

1

改写方案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;


1

当然如果这里我们不使用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;
1

这里来看看改写的效果如何。

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)

1

另外还有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;
1

不使用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;
1

对于前面提到的table()函数的用法说明，大家可以参考： https://docs.mogdb.io/zh/mogdb/v5.0/support-table-function#%E7%89%B9%E6%80%A7%E7%BA%A6%E6%9D%9F