金仓数据库KingbaseES PIPELINE子句详解

 关键字

PIPELINED、PIPEROW

1、功能描述

管道函数即是可以返回行集合（嵌套表类型或者数组类型）的函数，可以将其赋值给集合变量，管道函数为并行执行，需要服务器执行完整个函数后一次性返回客户端时可以使用管道函数。

2、语法

CREATE [ OR REPLACE ] [ EDITIONABLE | NONEDITIONABLE ]
  FUNCTION [ schema. ] function_name
  [ ( parameter_declaration [, parameter_declaration]... ) ]
RETURN datatype 
  [    ...
	| pipelined_clause
	| ...
  ]
{ IS | AS } 
[ declare_section ]
  body
  ;

3、参数详解

PIPELINED：用在函数定义语句中，用于指定定义的函数为一个PIPELINED表函数，返回值必须为集合类型

PIPEROW：用来返回该集合的单个集合的元素

4、返回值

PIPE ROW语句只能出现在指定了PIPELINED关键字的表函数中，用于将表的一行数据返回给函数的调用者。PIPE ROW仅返回数据而不返回函数的控制权，函数的控制权仍通过RETURN语句返回，但PIPELINED表函数中的RETURN语句仅用于返回函数的控制权，并不返回数据，所以PIPELINED表函数中的RETURN语句不能有参数。

5、示例

示例一：简单编写pipeline函数

CREATE OR REPLACE PACKAGE pkg1 AS

    TYPE numset_t IS TABLE NUMBER;

    FUNCTION f1(x NUMBER) RETURN numset_t PIPELINED;

END pkg1;

 



CREATE OR REPLACE PACKAGE BODY pkg1 AS

    FUNCTION f1(x NUMBER) RETURN numset_t PIPELINED IS

        BEGIN

            FOR i IN 1..x LOOP

                PIPE ROW(i);

            END LOOP;

            RETURN;

        END;

END pkg1;

SELECT * FROM TABLE(pkg1.f1(5));

示例二：用于数据转换

CREATE OR REPLACE PACKAGE refcur_pkg IS

  TYPE refcur_t IS REF CURSOR RETURN emp%ROWTYPE;

  TYPE outrec_typ IS RECORD (

    var_num    NUMBER(6),

    var_char1  VARCHAR2(30),

    var_char2  VARCHAR2(30));

  TYPE outrecset IS TABLE OF outrec_typ;

 FUNCTION f_trans(p refcur_t)

      RETURN outrecset PIPELINED;

END refcur_pkg;

 



CREATE OR REPLACE PACKAGE BODY refcur_pkg IS

  FUNCTION f_trans(p refcur_t)

   RETURN outrecset PIPELINED IS

    out_rec outrec_typ;

    in_rec  p%ROWTYPE;

  BEGIN

  LOOP

    FETCH p INTO in_rec;

    EXIT WHEN p%NOTFOUND;

    -- first row

    out_rec.var_num := in_rec.empno;

    out_rec.var_char1 := in_rec.ename;

    out_rec.var_char2 := in_rec.mgr;

    PIPE ROW(out_rec);

    -- second row

    out_rec.var_num := in_rec.deptno;

    out_rec.var_char1 := in_rec.deptno;

    out_rec.var_char2 := in_rec.job;

    PIPE ROW(out_rec);

  END LOOP;

  CLOSE p;

  RETURN;

  END;

END refcur_pkg;

 

SELECT * FROM TABLE(

   refcur_pkg.f_trans(CURSOR(SELECT * FROM emp WHERE empno=7782)));

示例三：使用多个游标变量入参

CREATE PACKAGE refcur_pkg IS

  TYPE refcur_t1 IS REF CURSOR RETURN employees%ROWTYPE;

  TYPE refcur_t2 IS REF CURSOR RETURN departments%ROWTYPE; 

  TYPE outrec_typ IS RECORD (

    var_num    NUMBER(6),

    var_char1  VARCHAR2(30),

    var_char2  VARCHAR2(30));

  TYPE outrecset IS TABLE OF outrec_typ;

  FUNCTION g_trans(p1 refcur_t1, p2 refcur_t2)

    RETURN outrecset PIPELINED;

END refcur_pkg;

/

 

CREATE PACKAGE BODY refcur_pkg IS

FUNCTION g_trans(p1 refcur_t1, p2 refcur_t2)

    RETURN outrecset PIPELINED IS

    out_rec outrec_typ;

    in_rec1 p1%ROWTYPE;

    in_rec2 p2%ROWTYPE;

BEGIN

  LOOP

    FETCH p2 INTO in_rec2;

    EXIT WHEN p2%NOTFOUND;

  END LOOP;

  CLOSE p2;

  LOOP

    FETCH p1 INTO in_rec1;

    EXIT WHEN p1%NOTFOUND;

    -- first row

    out_rec.var_num := in_rec1.employee_id;

    out_rec.var_char1 := in_rec1.first_name;

    out_rec.var_char2 := in_rec1.last_name;

    PIPE ROW(out_rec);

    -- second row

    out_rec.var_num := in_rec2.department_id;

    out_rec.var_char1 := in_rec2.department_name;

    out_rec.var_char2 := TO_CHAR(in_rec2.location_id);

    PIPE ROW(out_rec);

  END LOOP;

  CLOSE p1;

  RETURN;

END;

END refcur_pkg;

/

 

SELECT * FROM TABLE(refcur_pkg.g_trans(

  CURSOR(SELECT * FROM employees WHERE department_id = 60),

  CURSOR(SELECT * FROM departments WHERE department_id = 60)));

示例四：管道函数作为聚合函数使用

CREATE TABLE gradereport (student VARCHAR2(30), subject VARCHAR2(30),

                          weight NUMBER, grade NUMBER);

INSERT INTO gradereport VALUES('Mark', 'Physics', 4, 4);

INSERT INTO gradereport VALUES('Mark','Chemistry', 4, 3);

INSERT INTO gradereport VALUES('Mark','Maths', 3, 3);

INSERT INTO gradereport VALUES('Mark','Economics', 3, 4);

 

CREATE PACKAGE pkg_gpa IS

  TYPE gpa IS TABLE OF NUMBER;

  FUNCTION weighted_average(input_values SYS_REFCURSOR)

    RETURN gpa PIPELINED;

END pkg_gpa;

/

CREATE PACKAGE BODY pkg_gpa IS

FUNCTION weighted_average(input_values SYS_REFCURSOR)

  RETURN gpa PIPELINED IS

  grade NUMBER;

  total NUMBER := 0;

  total_weight NUMBER := 0;

  weight NUMBER := 0;

BEGIN

-- The function accepts a ref cursor and loops through all the input rows

  LOOP

     FETCH input_values INTO weight, grade;

     EXIT WHEN input_values%NOTFOUND;

-- Accumulate the weighted average

     total_weight := total_weight + weight;

     total := total + grade*weight;

  END LOOP;

  PIPE ROW (total / total_weight);

  RETURN; -- the function returns a single result

END;

END pkg_gpa;

/



SELECT w.column_value "weighted result" FROM TABLE(

       pkg_gpa.weighted_average(CURSOR(SELECT weight, grade FROM gradereport))) w;