将字符串转换成表的形式

     今天看了朋友的blog上转载了一篇将字符串转换成内存表的形式的文章，学习了一下，内容如下：    

 create or replace type type_varc is table of varchar2(1000);

CREATE OR REPLACE FUNCTION f_str2tab(p_str IN VARCHAR2 --传入的字符串,格式为1,2,3,4,5
) RETURN type_varc IS
/******************************************************************
Ver1.0 Created by xsb on 2008-04-14
把字符串(1,2,3,4,5)转换为内存表形式
create or replace type type_varc is table of varchar2(1000);
测试用例:SELECT * FROM TABLE(f_str2tab('a,s,d,12,3,4,5'));
******************************************************************/
v_str VARCHAR2(4000) := p_str || ',';
v_cnt NUMBER := length(v_str) - length(REPLACE(v_str, ','));
v_numtab type_varc := type_varc(); --返回内存表

BEGIN
FOR i IN 1 .. v_cnt LOOP
v_numtab.EXTEND;
v_numtab(i) := substr(v_str, 1, instr(v_str, ',') - 1);
dbms_output.put_line('v_str:'||v_str);
dbms_output.put_line('v_numtab(i):'||v_numtab(i));
v_str := substr(v_str, instr(v_str, ',') + 1);
dbms_output.put_line('v_str:'||v_str);
END LOOP;
RETURN v_numtab;

EXCEPTION
WHEN OTHERS THEN
v_numtab.DELETE;

END;

--上面的可能会有重复的元素出现
select distinct * from  TABLE(f_str2tab('a,s,d,12,12,3,4,5'))

使用此内存表关联时,需要加hint,如:
select /*+ ordered use_nl(a,b)*/ b.*
from TABLE(f_str2tab('a,s,d,12,3,4,5')) a,t1 b
where a.column_value=b.id1;

我把我们处理字符串成表的方式也整理了下：

1.将字符串转乘number形内存表

CREATE OR REPLACE TYPE pkNumberTable IS TABLE OF number;

--创建函数

  CREATE OR REPLACE function IntoNumberTable(v_string nvarchar2
                          ,v_char   nvarchar2) return pkNumberTable is
    m_result pkNumberTable := pkNumberTable();
    m_pks    pkList;
 
    m_begin int;
    m_end   int;
    m_len   int;
    m_s     varchar2(2000);
 
    procedure insertIntoStringID is
    begin
      m_pks(m_s) := m_s;
    exception
      when VALUE_ERROR then
        null;
    end;
 
    function convertTable(v_in pkList) return pkNumberTable is
      m_i      PLS_INTEGER;
      m_result pkNumberTable := pkNumberTable();
    begin
      m_i := v_in.FIRST;
      WHILE m_i IS NOT NULL LOOP
        m_result.extend();
        m_result(m_result.count()) := m_i;
        m_i := v_in.NEXT(m_i);
      END LOOP;
      return m_result;
    end;
 
  BEGIN
    m_begin := 1;
    m_end   := 0;
    LOOP
      m_end := instr(v_string
                    ,v_char
                    ,m_begin);
      EXIT WHEN(m_end = 0);
      m_s := substr(v_string
                   ,m_begin
                   ,m_end - m_begin);
      insertIntoStringID;
      m_begin := m_end + length(v_char);
    END LOOP;
 
    m_len := length(v_string);
    IF (m_begin <= m_len) THEN
      m_s := substr(v_string
                   ,m_begin
                   ,m_len - m_begin + 1);
      insertIntoStringID;
    END IF;
 
    return convertTable(m_pks);
  END IntoNumberTable;

select * from table(cast(util.IntoNumberTable('1,2,3', ',') as pkNumberTable));

2.将字符串转乘varchar2形内存表

CREATE OR REPLACE TYPE codeStringTable IS TABLE OF varchar2(50);

 CREATE OR REPLACE function IntoStringTable(v_string nvarchar2
                          ,v_char   nvarchar2) return codeStringTable is
    m_result codeStringTable := codeStringTable();
    m_codes  codeList;
 
    m_begin int;
    m_end   int;
    m_len   int;
    m_s     varchar2(2000);
 
    procedure insertIntoStringID is
    begin
      m_codes(m_s) := m_s;
    exception
      when VALUE_ERROR then
        null;
    end;
 
    function convertTable(v_in codeList) return codeStringTable is
      m_i      nvarchar2(50);
      m_result codeStringTable := codeStringTable();
    begin
      m_i := substr(v_in.FIRST
                   ,1
                   ,50);
      WHILE m_i IS NOT NULL LOOP
        m_result.extend();
        m_result(m_result.count()) := m_i;
        m_i := v_in.NEXT(m_i);
      END LOOP;
      return m_result;
    end;
 
  BEGIN
    m_begin := 1;
    m_end   := 0;
    LOOP
      m_end := instr(v_string
                    ,v_char
                    ,m_begin);
      EXIT WHEN(m_end = 0);
      m_s := substr(v_string
                   ,m_begin
                   ,m_end - m_begin);
      insertIntoStringID;
      m_begin := m_end + length(v_char);
    END LOOP;
 
    m_len := length(v_string);
    IF (m_begin <= m_len) THEN
      m_s := substr(v_string
                   ,m_begin
                   ,m_len - m_begin + 1);
      insertIntoStringID;
    END IF;
 
    return convertTable(m_codes);
  END IntoStringtable;  

select * from table(cast(util.IntoStringtable('1,2,3',',') as codestringtable)) ;

在这两种方法类似，只要用方法2就可以了。

3. 利用connect by找出逗号个数进行分割

create or replace function subStringByDelimiter(v_string    varchar2
                             ,v_delimiter varchar2
                             ,v_index     number) return varchar2 is
m_begin number;
m_end   number;
begin
  IF v_index=1 then 
    m_begin:=0;
  else
    m_begin:=instr(v_string, v_delimiter, 1, v_index-1) ;
    IF m_begin=0 then
      return ''; 
    END IF; 
  END IF; 
 
  m_end:=instr(v_string, v_delimiter,m_begin+1, 1);
  IF m_end=0 then
    m_end:=length(v_string)+1;
  END IF; 
   
  return trim(substr(v_string,m_begin+1,m_end-m_begin-1));
end subStringByDelimiter;

select pkid
  from (select subStringByDelimiter(str, ',', level) pkid
          from (select '1,2,3,'  str from dual)
        connect by instr(str, ',', 1, level) > 0)
 where pkid is not null;