Ask Tom之中英文对照20051228——PL/SQL函数返回CLOB类型值

 

Ask Tom之中英文对照20051228——PL/SQL函数返回CLOB类型值

问：

我已经通读了相关文档，但是在实际应用中仍然不太清楚我的理解是否确切。

我已经创建了如下的函数——产生一个小的结果集的游标查询实例，用合并varchar2变量返回一个clob类型值来做试验。

create or replace function testclob return clob
as
    v_clob clob;
begin
    dbms_lob.createtemporary(v_clob,FALSE,DBMS_LOB.CALL);
    dbms_lob.open(v_clob,dbms_lob.lob_readwrite);
    for val in (select 'abc' colalias from dual union
                select 'def' colalias from dual union
                select 'ghi' colalias from dual)
    loop
        dbms_lob.writeAppend( v_clob, length(val.colalias), val.colalias );
    end loop;

    return v_clob;
end;

由于我实际返回了clob类型值，因此我没有利用这个函数执行如下语句：

    dbms_lob.close(v_clob);
    dbms_lob.freetemporary(v_clob);

我在sql/plus中用如下语句测试：

variable c clob
exec :c := testclob
print c

我得到了预期的结果。然而，当我执行

select * from v$temporary_lobs

在同一个sql/plus会话中我做的多次函数调用，nocache_lobs的数据好像是不断增加，而不是保持在1。

最后，我用一个JDBC的java客户端调用类似上述函数，不过在多次调用之间会话保持打开。我要确保这些句柄范围的正确，以保证内存的使用没有过时。

怎样返回clob类型值，并且在适当的时候释放它呢？

请帮帮我，指导我在此类情形下的如何选择。

谢谢！

回答：

在应用开发指导中关于LOBS的描述如下：

<quote>
注意：DBMS_LOB.CREATETEMPORARY存储过程有一个可选的持续时间参数。在PL/SQL中，这个持续时间参数只作为LOB数据持续时间的隐含参数。PL/SQL在内部自动计算LOB数据的持续时间，替换你的帐号中的隐含参数。你并不需要特别指定LOB数据的持续时间。
</quote>

在你那种情况下，这样做比较好——既然你返回LOB值到客户端，仅仅在函数调用的那一瞬间的范围对你来说是不合适的。这个调用是函数调用。如果PL/SQL并没有意识到你返回了CLOB值，在实际中由于需要释放了该值，那么这个调用就没有任何内容可以输出。

如果你的目的是在一个会话中用一个LOB，必须满足以下两条：

o 你做完之后释放这个LOB。就像你读完一个文件要关闭它，或者当你知道不在需要一个游标而释放它一样。

o 在程序包中使用单一LOB。

在程序包中使用单一LOB，你可以用如下代码：

ops$[e-mail]> create or replace package testclob_pkg
  2  as
  3  
  4      function testclob( p_data in varchar2 ) return clob;
  5  
  6  end;
  7  /

Package created.

ops$[e-mail]>
ops$[e-mail]> create or replace package body testclob_pkg
  2  as
  3  
  4  g_clob     clob;
  5  
  6  
  7  function testclob( p_data in varchar2 ) return clob
  8  as
  9  begin
10      dbms_lob.trim( g_clob, 0 );
11      for val in (select p_data colalias from dual union all
12                  select ' abc' colalias from dual union all
13                  select ' def' colalias from dual union all
14                  select ' ghi' colalias from dual)
15      loop
16          dbms_lob.writeAppend( g_clob, length(val.colalias), val.colalias );
17      end loop;
18  
19      return g_clob;
20  end;
21  
22  
23  begin
24      -- elaboration code, run once when package
25      -- is first accessed in a sesion
26      dbms_lob.createtemporary(g_clob,FALSE);
27      dbms_lob.open(g_clob,dbms_lob.lob_readwrite);
28  end;
29  /

Package body created.

ops$[e-mail]>
ops$[e-mail]> select * from v$temporary_lobs;

no rows selected

ops$[e-mail]> declare
  2      l_clob clob;
  3  begin
  4      for i in 1 .. 10
  5      loop
  6          l_clob := testclob_pkg.testclob( 'loop ' || i  || ',' );
  7          dbms_output.put_line( dbms_lob.substr( l_clob, 250, 1 ) );
  8      end loop;
  9  end;
10  /
loop 1, abc def ghi
loop 2, abc def ghi
loop 3, abc def ghi
loop 4, abc def ghi
loop 5, abc def ghi
loop 6, abc def ghi
loop 7, abc def ghi
loop 8, abc def ghi
loop 9, abc def ghi
loop 10, abc def ghi

PL/SQL procedure successfully completed.

ops$[e-mail]> select * from v$temporary_lobs;

       SID CACHE_LOBS NOCACHE_LOBS
---------- ---------- ------------
        14          0            1

(不要使用sqlplus中的变量进行测试，sqlplus倾向于分配自己的lobs变量）

原文引自：
--------------------------------------------------------------------------------
[url]http://asktom.oracle.com/pls/ask/f?p=4950:14033939053232470511::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:1464712373163[/url]
--------------------------------------------------------------------------------
原文版权归属asktom.oracle.com所有，翻译 by millet•CNOUG
2005-12-29