管道函数

oracle ldboyghg 5973℃ 0评论
– CREATE OR REPLACE TYPE TYPE_SPLIT  as table of varchar2(1000)
– 定义:
 CREATE OR REPLACE FUNCTION “SPLIT”
(
 p_list varchar2,
 p_sep varchar2 := ‘,’
 ) return type_split
  pipelined
 is
  l_idx pls_integer;
  v_list varchar2(3000) := p_list;
begin
  loop
    l_idx := instr(v_list, p_sep);
    if l_idx > 0 then
      pipe row(substr(v_list, 1, l_idx – 1));
      v_list := substr(v_list, l_idx + length(p_sep));
    else
      pipe row(v_list);
      exit;
    end if;
  end loop;
  return;
end split;
–  使用方法:
declare
  vText    varchar2(4000) := ‘DAY_CASH_1,1,1;DXT_DAY_CW_01,1,1;DXT_DAY_CW_05,1,1;DAY_PLTIM_1_1,1,1;DXT_DAY_CW_02,1,1;DAY_SUPP_1_3,1,1;DXT_DAY_MARKET_BUSI_02,1,1;DXT_DAY_MARKET_BUSI_03,1,1;DXT_DAY_MARKET_CUST_01,0,1;DXT_DAY_MARKET_VALUE_01,0,1;DAY_JKDEV_1_6,0,1;DAY_COMTA_1_1,0,1;DXT_DAY_MARKET_CUST_03,0,1;DXT_DAY_MARKET_CUST_02,0,1;DAY_OPER_1_4,0,1;DAY_OPER_1_5,0,1;DXT_DAY_MARKET_BUSI_01,1,1;DAY_SY_2,0,1;DXT_DAY_JK_02,0,1;DXT_DAY_JK_01,1,1′;
  v_list  varchar2(30);
  a       varchar2(30);
  b       varchar2(30);
  c       varchar2(30);
begin
  for x in (select * from table(split(vText, ‘;’))) loop
       v_list := x.column_value ;
     —  dbms_output.put_line(v_list);
       a := substr(v_list,1, instr(v_list,’,’)-1);
       b := substr(v_list,instr(v_list,’,’)+1, 1);
       c := substr(v_list,-1, instr(v_list,’,’)+1) ;
       dbms_output.put_line(‘a: ‘  || a || ‘   b:   ‘ ||  b || ‘   c:   ‘   || c) ;
  end loop;
end;

转载请注明:生命不息,奋斗不止 » 管道函数

喜欢 (0)
发表我的评论
取消评论
表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址