利用临时表拆分字段(PL/SQL)

    园子里好象用 Oracle 数据库的人不是很多，写一个利用 PL/SQL 拆分字段的函数，供大家和 T-SQL 的语法做个对比吧。

原表：

拆分后的结果：

 

函数代码如下(在 PL/SQL Developer7.0.2 和 Oracle 10G 下测试通过)：

create or replace function fun_SplitField(TableName in varchar2, FieldName in varchar2) return sys_refcursor is
  Result sys_refcursor;

  -- Author  : Clark Zheng
  -- Created : 2007-4-10 15:31:39
  -- Purpose : 拆分字段

  --临时表名称
  tmpTableName constant varchar2(50) := 'TMPTABLE';

  a_cursor sys_refcursor;
  tmpNum number;
  tmpText varchar2(50);
  tmpIndex number;
  tmpStr varchar2(50);
  sqlStr varchar2(500);
begin
  --如果参数不合法直接返回
  if TableName is null or FieldName is null then
     dbms_output.put_line('参数为空');
     return(Result);
  end if;

  --如果没有临时表就创建临时表
  execute immediate 'select count(*) from user_tables where table_name=''' || tmpTableName || '''' into tmpNum;
  if tmpNum < 1 then
    execute immediate 'create GLOBAL TEMPORARY table ' || tmpTableName || ' (' || FieldName || ' varchar2(50)) ON COMMIT DELETE ROWS';
  end if;

  sqlStr := 'select distinct ' || FieldName || ' from ' || TableName;
  open a_cursor for sqlStr;

  loop
    fetch a_cursor into tmpText;
    --如果记录为空直接进入下一次循环
    if a_cursor%notfound then
       exit;
    end if;

    --判断字段的值是否为空
    if tmpText is not null then
      --获取逗号出现的位置
      select instr(tmpText, ',') into tmpIndex from dual;
      loop
        if tmpIndex <> 0 then
          --取得逗号前字符串
          select substr(tmpText, 1, tmpIndex - 1) into tmpStr from dual;
          --插入到临时表
          execute immediate 'insert into ' || tmpTableName || ' values (' || tmpStr || ')';
          --截取剩余的子符串
          select substr(tmpText, tmpIndex + 1) into tmpText from dual;
          --重新获取逗号出现的位置
          select instr(tmpText, ',') into tmpIndex from dual;
        else
          --保证最后一个子串被插入到临时表
          execute immediate 'insert into ' || tmpTableName || ' values (' || tmpStr || ')';
          exit;
        end if;
       end loop;
    end if;
  end loop;

  close a_cursor;

  --查询结果集
  sqlStr := 'select distinct * from ' || tmpTableName;
  open Result for sqlStr;

  return(Result);

  exception
    when others then
      --发生异常输出错误信息并返回空值，实际应用中可以抛出自定义异常
      dbms_output.put_line(dbms_utility.format_error_stack);
      return(Result);
end fun_SplitField;

 

例子下载：https://files.cnblogs.com/reonlyrun/SplitFieldExample.rar

 

注：创建临时表要求用户必须具有“CREATE ANY TABLE”的权限

 

ps:本函数使用临时表加双层循环实现，效率不是很理想，希望有人提出更好的思路