达梦数据库学习之三:管道函数(SqlServer中的表函数)

        把SqlServer的函数或过程转为达梦数据库的函数或过程时，遇到最多的就是临时表以及表变量的问题，达梦和Oracle到是很像，和SqlServer相差太多，微软的SqlServer的临时表这一块还是做的不错的，用起来很方便，达梦实现起来就有点费劲。

     学习前，请先看一下达梦文档

自定义类型 | 达梦技术文档用户使用 CREATE TYPE 语句可以定义记录类型、对象类型、命名的数组类型、集合类型等，如果在对象类型中声明了过程或方法，可以使用 CREATE TYPE BODY 定义这些过程和方法。 13.1 创建类型 可以使用 CREATE TYPE 语句创建记录类型、对象类型、数组和集合类型。 语法格式 CREATE [OR REPLACE] TYPE [<模式名>.]<类型https://eco.dameng.com/document/dm/zh-cn/pm/custom-type.html主要是用到里面的

    <记录类型定义子句> ::= RECORD(变量列表定义)

   <嵌套表定义子句> ::= TABLE OF <数据类型>

   <索引表定义子句> ::= TABLE OF <数据类型> [INDEX BY <数据类型>]

废话少说，直接上学习代码，下面的这个代码是拷贝上的:

create or replace type s100_t is table of varchar(100);

create or replace function s100f1 
 return s100_t 
as
  a s100_t;
begin
  a = s100_t('hello', 'world', 'xxxx');
  a.extend(2);
  a(4) = 'yyyy';
  a(5) = 'uuuu';
  return a;
end;

create or replace function s100f2 return s100_t pipelined
as
   begin
     pipe row('hello');
     pipe row('world');
end;

declare
  b s100_t;
begin
  b = s100f1;
  for i in 1..b.count loop
     print b(i);
  end loop;
end;

declare
  b s100_t;
begin
  b = s100f2;
  for i in 1..b.count loop
     print b(i);
  end loop;
end;

执行代码：

declare
  b s100_t;
begin
  b = s100f1;
  for i in 1..b.count loop
     print b(i);
  end loop;
end;

declare
  b s100_t;
begin
  b = s100f2;
  for i in 1..b.count loop
     print b(i);
  end loop;
end;

 目的：直接用select 语句输出：

select * from table(s100f1);
select COLUMN_VALUE AS MYFILE from table(s100f2);

这是只有一个字段的情况，如果有多个字段的表怎么办，这里的OBJECT类开可以换成Record类型？代码如下：

--1.建记录的数据类型 mytype
CREATE OR REPLACE  TYPE mytype AS OBJECT (
  COL1 INT,
  COL2 VARCHAR (64)
);
--2.建表的行类型
CREATE OR REPLACE  TYPE mytypelist AS TABLE OF mytype;

--3.返回表的函数
CREATE OR REPLACE FUNCTION func_piperow RETURN mytypelist PIPELINED
IS
  v_mytype mytype;
BEGIN
FOR I IN 1 .. 5 LOOP
  v_mytype := mytype (I, 'Row ' || I);
  PIPE ROW (v_mytype);
  END LOOP;
  EXCEPTION
  WHEN OTHERS THEN NULL;
end;

--4.查询
SELECT * FROM TABLE (FUNC_piperow);
--5.带条件查询
SELECT * FROM TABLE (FUNC_piperow) where col1 between 3 and 4;

 

只是类型要提前定义好 ，如果这样的函数有很多，那还得定义很多这样的类型，所以命名上就要下功夫了。

 现在根据上面的示例自已做一个把SqlServer表函数转达梦的管道函数：<根据分隔符，获取字符串表达的范围>

CREATE FUNCTION [dbo].[KF_Pub_SplitStrByChar]
(@strval nvarchar(max),@splitstr char(1))
   returns @db table (_num int identity,myfield nvarchar(max),UNIQUE CLUSTERED(_num))
   --UNIQUE CLUSTERED (A,Uniqueifier)
--可加入加密码代码:WITH ENCRYPTION
AS
 begin
    declare @i int,@tmpstr1 nvarchar(max)
    select @i=CHARINDEX(@splitstr,@strval)
    while @i>0
        begin
             select @tmpstr1=substring(@strval,1,@i-1) ,@strval=substring(@strval,@i+1,len(@strval)-@i)
             insert into @db(myfield) values(@tmpstr1)
             select @i=CHARINDEX(@splitstr,@strval)
        end
    insert into @db(myfield) values(@strval)
    return
end

 执行

select * from [dbo].[KF_Pub_SplitStrByChar]('1,3,5,7,7,8,3,11',',')

返回结果

转成达梦后变成这样,

  a.选建类型：

--1.建记录的数据类型 
CREATE OR REPLACE TYPE TYPE_ROW_SPLITSTR AS OBJECT (
  _num INT,
  myfield NVARCHAR(500)
);
--2.建表的行类型
CREATE OR REPLACE TYPE TYPE_TABLE_SPLITSTR AUTHID DEFINER is TABLE OF TYPE_ROW_SPLITSTR;

  b.建表函数

--3.返回表的函数
CREATE OR REPLACE FUNCTION KF_Pub_SplitStrByChar
(
  "strval" IN TEXT,
  "splitstr" IN CHAR(1)
) 
RETURN TYPE_TABLE_SPLITSTR PIPELINED
AS
  R TYPE_ROW_SPLITSTR;
  i INT;
  num INT;
  tmpstr1 TEXT;
BEGIN
  num:=1;
  i:=INSTRB("strval","splitstr");
  while i>0 loop
    tmpstr1:=substring("strval",1,i-1);
    "strval":=substring("strval",i+1,len("strval")-i);
    R := TYPE_ROW_SPLITSTR(num, tmpstr1);    
    PIPE ROW (R);
    i:=INSTRB("strval","splitstr");
    num:=num+1;    
  end loop;
  R := Type_Row_SplitStr(num, "strval");
  PIPE ROW (R);
  EXCEPTION
    WHEN OTHERS THEN NULL;  
END;

  c.查询表函数

select ROWNUM,* 
    FROM TABLE ("KF_PUB_SPLITSTRBYCHAR"('1,3,5,df,7,9,8,a,11',',')) 
    WHERE ISNUMERIC(MYFIELD) =1 
    ORDER BY CAST(MYFIELD AS INT);