Oracle迁移到MogDB之管道函数(pipeline)

jgmydsai

已于 2024-05-08 09:48:19 修改

阅读量322

点赞数 6

分类专栏： Oracle迁移到MogDB 文章标签：迁移管道函数 pipeline MogDB oracle

于 2024-04-16 14:52:43 首次发布

本文链接：https://blog.csdn.net/jgmydsai/article/details/137825117

版权

Oracle迁移到MogDB 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

本文介绍了如何将Oracle中的管道函数转换为MogDB支持的数据集返回形式，提供了两种方式：一是逐行填充类型对象数组，二是使用SQL查询并返回结果集。

摘要由CSDN通过智能技术生成

适用范围

Oracle迁移到MogDB

问题概述

迁移一个项目时发现客户的系统中大量的使用了管道函数（pipeline），MogDB目前暂时不支持pipeline。如果迁移为表函数，使用时并不支持函数table()，改动较大。但是MogDB已经支持talbe函数，只要返回数据集即可。因此可以把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

解决方案

更改方式一

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
  FOR cur IN (SELECT * FROM scott.emp WHERE deptno = p_deptno) LOOP
    res_emp.extend;
    res_emp(res_emp.last):= type_emp_row(cur.empno,
                          cur.ename,
                          cur.job,
                          cur.mgr,
                          cur.hiredate,
                          cur.sal,
                          cur.comm,
                          cur.deptno);
  END LOOP;
  return res_emp;
END;
/

查询结果

cmsdb=> 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)

更改方式二

只有函数部分与上面稍有区别

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()函数，也可以直接改为表函数，同样有几种方式

更改方式一

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;

查询结果:

cmsdb=> 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)

更改方式二

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;

更改方式三

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;