在Oracle SP中用更大的数据类型替换varchar2 [英] Replacing varchar2 with bigger data type in oracle SP

问题描述

我正在使用oracle版本10. PL/SQL中使用varchar2变量存储了存储过程. 该代码不断添加varchar2变量. 当varchar2变量大小超过32767时，它将无法追加更多值. 现在，我想将数据类型更改为long或clob(为了容纳更多字符)，它不起作用. 如何在此处修改代码以使Clob或long具有相同的附加功能?

I am using oracle verion 10. There is stored procedure in PL/SQL using varchar2 variable. The code is constantly appending the varchar2 variable. When the varchar2 variable size exceeds 32767, it cannot append any more value. Now I want to change the data type to long or clob(in order to accomodate more characters), it does not work. How to modify the code here to have the same appending functionality with clob or long?

样本附加 x:= x || 'mydata';

sample appending x:= x || 'mydata';

推荐答案

不推荐使用long数据类型；如果可以的话，您应该认真考虑迁移您的long列到clob .

The long datatype is deprecated; if you can you should seriously consider migrating your long column to a clob.

如果您使用的是clob，则可以附加超过32k varchar2的限制，如下所示:

If you were working with a clob you could append past the 32k varchar2 limit like this:

declare
  l_clob clob;
begin
  dbms_lob.createtemporary(l_clob, true);
  dbms_lob.open(l_clob, dbms_lob.lob_readwrite);
  dbms_lob.writeappend(l_clob, 4, '1234');
  for i in 1..10000 loop
    dbms_lob.writeappend(l_clob, 5, '.5678');
  end loop;
  dbms_output.put_line('CLOB length: ' || length(l_clob));
  dbms_lob.close(l_clob);
  dbms_lob.freetemporary(l_clob);    end;
/

CLOB length: 50004

您可以使用连接运算符||附加到long，但是正如您已经看到的那样，最大只能为32k.在PL/SQL中没有简单的方法来处理long值.您也许可以使用dbms_sql进行处理，但是如果有可能将表列切换为clob，则确实不值得付出任何努力.

You can append to a long with the concatenate operator ||, but as you've seen already, only up to 32k. There is no easy way to handle long values above that within PL/SQL. You might be able to do soemthing with dbms_sql but it's really not going to be worth the effort if there is any possiblility of switching the table column to a clob.

如果您要将Clob传递回调用方，并且它是一个临时Clob，则必须由调用方定义并在创建后将其传递给它:

If you want to pass the clob back to the caller, and it's a temporary clob, it will have to be defined by the caller and be passed it after it's created:

create or replace procedure proc1 as
  l_clob clob;
begin
  dbms_lob.createtemporary(l_clob, true);

  proc2(l_clob);
  dbms_output.put_line('proc1 CLOB length: ' || length(l_clob));

  dbms_lob.freetemporary(l_clob);
end;
/

create or replace procedure proc2(p_clob in out clob) as
begin
  dbms_lob.open(p_clob, dbms_lob.lob_readwrite);
  dbms_lob.writeappend(p_clob, 5, '12345');
  for i in 1..9999 loop
    dbms_lob.writeappend(p_clob, 5, '.56789');
  end loop;
  dbms_output.put_line('proc2 CLOB length: ' || length(p_clob));
  dbms_lob.close(p_clob);
end;
/

exec procs;

proc2 CLOB length: 50000
proc1 CLOB length: 50000

否则，就调用者而言，该对象将不存在.

Otherwise the object won't exist as far as the caller is concerned.

如果clob存在-从表中选择，例如，因此您不需要createtemporary调用-那么您可以将其分配给out参数，但是我不认为这是您所描述的情况.

If the clob exists - selected from a table, say, so you don't need the createtemporary call - then you can just assign it to an out parameter, but I don't think that's the case for what you've described.

这篇关于在Oracle SP中用更大的数据类型替换varchar2的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！