如何在 Oracle 存储过程中将 CLOB 数据类型作为输出参数返回

Question

我想从预言机存储过程返回CLOB数据类型(XML数据(的输出参数。

我的程序看起来像这样

create or replace PROCEDURE myProcedure
(
  myParam1 IN NUMBER 
 ,myParam2 IN NUMBER 
 ,myParam3 OUT CLOB 
) AS  
   xml_bits CLOB;
BEGIN
 select  xmldataColumn into xml_bits 
 from myTable WHERE id = myParam1 AND SESSION_ID = myParam2;
  IMPCPM_XML := xml_bits;
END myProcedure;

我

编译了这个，当我尝试执行时，它给了我以下错误。

exact fetch returns more than requested number of rows

如何返回一个大的 xmldata 作为输出参数？

提前谢谢。

Answer 1

该错误与返回CLOB值无关。查询：

select  xmldataColumn into xml_bits 
from myTable WHERE id = myParam1 AND SESSION_ID = myParam2;

返回多行，使用 SELECT ... INTO ... 要求查询仅返回一行。

您可以为ROWNUM = 1添加过滤器：

create or replace PROCEDURE myProcedure
(
  myParam1 IN NUMBER 
 ,myParam2 IN NUMBER 
 ,myParam3 OUT CLOB 
) AS  xml_bits CLOB;
BEGIN
  select xmldataColumn
  into   xml_bits 
  from   myTable
  WHERE  id = myParam1
  AND    SESSION_ID = myParam2
  AND    ROWNUM = 1;
END myProcedure;
/

或者您可以处理异常：

create or replace PROCEDURE myProcedure
(
  myParam1 IN NUMBER 
 ,myParam2 IN NUMBER 
 ,myParam3 OUT CLOB 
) AS  xml_bits CLOB;
BEGIN
  select xmldataColumn
  into   xml_bits 
  from   myTable
  WHERE  id = myParam1
  AND    SESSION_ID = myParam2;
EXCEPTION
  WHEN TOO_MANY_ROWS THEN
    xml_bits := NULL;
END myProcedure;
/