如何将clob数据转换为Oracle中的多个列？

1 回答

• 0

  下面的代码是从您的字符串和插入列获取数据到 temporary table ，如果您不需要 temperory table ，您可以使用从字符串中获取数据的逻辑

  首先像这样创建 temperory 表 Employeeinfo

  create global temporary table EmployeeInfo
  (
    EmployeeID           varchar2(15),
    HireDate             varchar2(15),
    Name                 varchar2(100),
    Department           varchar2(10)
  )
  on commit preserve rows;
  

  第二次使用此代码（这是 pl/sql test script ）

  declare
  
  lv_string varchar2(4000) := 'Employee ID: 1  
  Hire Date: 24-Oct-2013
  Name: XXXXX
  Department: IT
  
  Employee ID : 2
  Name : YYYYY
  Hire Date : 20-May-2014
  Department: ITIS
  
  
  Hire Date: 17-May-2017
  Department:SMO
  Name: ZZZZZ
  Employee ID: 3';
  
     lv_Token varchar2(4000);
     lv_Part varchar2(4000);
     lv_FirstPart varchar2(4000);
     lv_SecondPart varchar2(4000);
     li pls_integer := 0;
     lv_EmployeeID varchar2(15);
     lv_HireDate varchar2(15);
     lv_Name varchar2(100);
     lv_Department varchar2(10);
     function v_Partition(av_source long, av_separator varchar2,
                             ai_nth pls_integer) return varchar2 is
        li_Sep pls_integer := length(av_Separator);
        li_Begin pls_integer := 1 - li_Sep;
        li_End pls_integer;
        lv_Output long;
     begin
        li_End := instr(av_source, av_separator, 1, ai_nth);
        if ai_Nth > 1 then
           li_Begin := instr(av_source, av_separator, 1, ai_nth - 1);
           if li_Begin = 0 then
              return null;
           end if;
        end if;
        if li_End > 0 then
           lv_Output := substr(av_Source, li_Begin + li_Sep,
                               li_End - li_Begin - li_Sep);
        elsif length(av_Source) >= li_Begin + li_Sep then
           lv_Output := substr(av_Source, li_Begin + li_Sep,
                               length(av_Source) - li_Begin);
        end if;
        return lv_Output;
     end;
  
     function v_Token(av_source in out nocopy varchar2, av_separator varchar2)
        return varchar2 is
        lv_token varchar2(4000);
        li_pos pls_integer;
     begin
        li_pos := instr(av_source, av_separator, 1, 1);
        if li_pos > 0 then
           lv_token := rtrim(substr(av_source, 1, li_pos - 1));
           av_source := substr(av_source, li_pos + length(av_separator));
        else
           lv_token := rtrim(av_source);
           av_source := '';
        end if;
        if av_Separator = ' ' then
           av_Source := trim(av_Source);
        end if;
        return lv_token;
     end;
  begin
     while lv_string is not null loop
        lv_Token := v_Token(lv_string, chr(10));
        if lv_Token is not null then
           li := li + 1;
           lv_FirstPart := trim(v_Partition(lv_Token, ':', 1));
           put(lv_FirstPart);
           lv_SecondPart := trim(v_Partition(lv_Token, ':', 2));
           if lv_FirstPart like '%Employee ID%' then
              lv_EmployeeID := lv_SecondPart;
           elsif lv_FirstPart like '%Hire Date%' then
              lv_HireDate := lv_SecondPart;
           elsif lv_FirstPart like '%Name%' then
              lv_Name := lv_SecondPart;
           elsif lv_FirstPart like '%Department%' then
              lv_Department := lv_SecondPart;
           end if;
           if li = 4 then
              insert into EmployeeInfo
              values
                 (lv_EmployeeID, lv_HireDate, lv_Name, lv_Department);
              li := 0;
           end if;
        end if;
     end loop;
  
  
     --select * from EmployeeInfo; -- you can select from table
  end;
  

  回复于 2024-04-29T02:01:50+08:00