Oracle 将数据写入clob

嗨，汤姆，如何将表数据写入oracle 12.1版本中的另一个表中的clob列

create table abc(sno number(2) not null,file_clob clob);

insert into abc(sno) values(1);
insert into abc(sno) values(2);
insert into abc(sno) values(3);

create table rem(sno number(2), name varchar2(10),address varchar2(10));
insert into rem (sno,name,address) values (1,'anc','err');
insert into rem (sno,name,address)values(2,'sjd','djjd');
insert into rem (sno,name,address) values(2,'sjd','sdjd');
insert into rem (sno,name,address)values (2,'kfkf','kkkd');


create or replace TYPE type_sno FORCE AS OBJECT
( 
 sno number(2 )
);
/

create or replace TYPE type_sno_values  FORCE AS TABLE OF type_sno;
/

create or replace procedure insert_to_clob(pi_sno IN type_sno_values)
as

 cright  clob;
 cright1   clob;
 header   varchar2(1000):='ancsjsjjsjsjjssjsjjssjsjsjjs';
cursor input_sno is select osd.sno from table(pi_sno) osd;
 begin
 for task in input_sno loop 
 UPDATE abc
            SET file_clob  = empty_clob()
            WHERE sno = task.sno
            RETURNING file_clob  
         INTO cright;
        dbms_lob.writeappend(cright,length(header),header);
   cright1:=cright;
  cright1:=empty_clob();
FOR i IN ( SELECT CHR(10)
                || sno  || ';'
                || name  || ';'
                || address str
            FROM
                rem
            WHERE
                sno = task.sno) loop
 cright1:=cright1||i.str;
-- dbms_output.put_line(v_cwrite1);
 if(length(cright1)>=32000 or i.str.last )  then 
dbms_lob.writeappend(cright,length(cright1),cright1);
   end if;
 end loop;

 end loop;
 EXCEPTION
    WHEN OTHERS THEN
        RAISE;
end;

我上面的程序没有给出预期的输出，abc clob中的预期输出如下。

对于sno 1的abc表colb (FILE_CLOB) 列。

ancsjsjjsjsjjssjsjjssjsjsjjs
anc; 错误

对于abc表colb (FILE_CLOB) 列为sno 2。

ancsjsjjsjsjjssjsjjssjsjsjjs
sjd;djjd
sjd;djjd
sjd;sdjd

当cright1> = 32000或i.str last时，数据应更新为abc表 (FILE_CLOB) 列。

1) 我无法为上述条件编写代码。
2) 我没有得到程序上方的预期输出形式。
3) 在性能方面是否有任何好的方法。

我对你想在这里做什么有点困惑。但是，如果要将值追加到CLOB列，则只需将更新返回变量追加到变量即可。

不需要长度检查或中间值。

例如:

create or replace procedure insert_to_clob (
  pi_sno in type_sno_values
) as

  cright    clob;
  header    varchar2 (1000) := 'ancsjsjjsjsjjssjsjjssjsjsjjs';
  cursor input_sno is
  select osd.sno
  from   table (pi_sno) osd;

begin 
  for task in input_sno loop
    update abc
    set   file_clob = empty_clob ()
    where sno = task.sno 
    returning file_clob into cright;
  
    dbms_lob.writeappend (cright,length (header),header);

    for i in ( 
      select chr (10) || name || ';' || address as str 
      from rem
      where sno = task.sno
    ) loop
    
      dbms_lob.writeappend (cright,length (i.str),i.str);

    end loop;
  end loop;
end;
/

select * from abc;

SNO   FILE_CLOB   
     1        
     2        
     3   

begin
  insert_to_clob (
    type_sno_values ( type_sno ( 1 ) )
  );
  
  insert_to_clob (
    type_sno_values ( type_sno ( 2 ) )
  );
end;
/

select * from abc;

SNO   FILE_CLOB                                                  
     1 ancsjsjjsjsjjssjsjjssjsjsjjs
anc;err                        
     2 ancsjsjjsjsjjssjsjjssjsjsjjs
sjd;djjd
sjd;sdjd
kfkf;kkkd    
     3