oracle管道流函数，字符串按照逗号分隔，函数中循环

最新推荐文章于 2023-04-24 15:40:45 发布

蘼芜-

最新推荐文章于 2023-04-24 15:40:45 发布

阅读量1.2k

点赞数 1

分类专栏： oracle

本文链接：https://blog.csdn.net/sunyqcn/article/details/52846673

版权

oracle 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

1.定义对象类

CREATE OR REPLACE TYPE "FUNC_GDT_QUERY" IS object
(
  totalRMB       NUMBER(14, 2), 
  agencyCredit   NUMBER(14, 2), 
  dollarRMB      NUMBER(14, 2), 
  bankAccept     NUMBER(14, 2), 
  letterOfCredit NUMBER(14, 2) 
)

2.创建对象类的表类型

CREATE OR REPLACE TYPE "TAB_GDT_QUERY" is table of FUNC_GDT_QUERY

3.创建包，定义函数

create or replace package PKG_GDT_QUERY is
  function F_GDT_QUERY(cltnos      in varchar2,
                            bankcodes   in varchar2,
                            actdate     in varchar2,
                            handleEnter in varchar2)
    return TAB_GDT_QUERY
    PIPELINED;
end PKG_GDT_QUERY;

4.实现包体

create or replace package body PKG_GDT_QUERY is
  function F_GDT_QUERY(cltnos      in varchar2,
                            bankcodes   in varchar2,
                            actdate     in varchar2,
                            handleEnter in varchar2)
    return TAB_GDT_QUERY
    PIPELINED is
    --定义变量
    v_result         FUNC_GDT_QUERY := FUNC_GDT_QUERY(NULL,
                                                                NULL,
                                                                NULL,
                                                                NULL,
                                                                NULL);
    --将字符串按照逗号分隔，并进行循环                                                            
    CURSOR cltno_cursor IS
      select regexp_substr(F_GDT_QUERY.cltnos, '[^,]+', 1, rownum) as cltno
        from dual
      connect by rownum <= length(regexp_replace(F_GDT_QUERY.cltnos,
                                                 '[^,]',
                                                 null)) + 1;
    CURSOR bankcode_cursor IS
      select regexp_substr(F_GDT_QUERY.bankcodes, '[^,]+', 1, rownum) as bankcode
        from dual
      connect by rownum <= length(regexp_replace(F_GDT_QUERY.bankcodes,
                                                 '[^,]',
                                                 null)) + 1;

  begin
    --进行循环  
    for rec1 in cltno_cursor Loop
      for rec2 in bankcode_cursor Loop
       --rec1.cltno,,,,rec2.bankcode
       //具体查询
       。。。。。。。。。。。。
       //赋值
      v_result.totalRMB := ???; 
      end loop;
    end loop;
    PIPE ROW(v_result);
    return;
  end F_GDT_QUERY;
end PKG_GDT_QUERY;