如何在过程中使用返回 Oracle REF_CURSOR 的函数

Question

我必须编写一个 Oracle 过程，该过程应该调用返回 REF_CURSOR 的 Oracle 函数。该函数是这样声明的，

FUNCTION "IMPACTNET"."TF_CONVERTPARA" (PARASTRING IN NVARCHAR2) RETURN SYS_REFCURSOR
AS
  c SYS_REFCURSOR;
BEGIN
    OPEN c FOR         
        SELECT SUBSTR(element, 1, INSTR(element, '|') - 1)     as key,
               SUBSTR(element, INSTR(element, '|') + 1, 99999) as val
        FROM (
            SELECT REGEXP_SUBSTR(PARASTRING, '[^;]+', 1, LEVEL) element          
                FROM dual      
                CONNECT BY LEVEL < LENGTH(REGEXP_REPLACE(PARASTRING, '[^;]+')) + 1
             );
    RETURN c;     
END;

您能告诉我需要编写什么才能从我的过程中调用该函数吗？我想将所有返回值（形成一个包含两列的表格）插入到一个理性表中。

先感谢您！

Answer 1

类似的事情应该有效（显然，我猜测表名和列名以及您要实现的确切逻辑）

CREATE PROCEDURE some_procedure_name
AS
  l_rc  SYS_REFCURSOR := impactnet.tf_convertpara( <<some string>> );
  l_key VARCHAR2(100);
  l_val VARCHAR2(100);
BEGIN
  LOOP
    FETCH l_rc
      INTO l_key, l_val;
    EXIT WHEN l_rc%notfound;

    INSERT INTO some_table( key_column, val_column )
      VALUES( l_key, l_val );
  END LOOP;
END;

正如 Ollie 指出的那样，执行 BULK 会更有效COLLECT 和 FORALL。如果您只处理几千行（因为您的函数只是解析分隔字符串中的数据，我假设您期望返回相对较少的行），则性能差异可能很小。但如果您处理更多数据，差异可能会非常明显。根据 Oracle 版本和您的具体要求，您可以简化 FORALL 中的 INSERT 语句来插入记录，而不是单独列出记录中的每一列。

CREATE PROCEDURE some_procedure_name
AS
  TYPE key_val_rec 
    IS RECORD( 
         key  VARCHAR2(100),
         val  VARCHAR2(100)
    );
  TYPE key_val_coll
    IS TABLE OF key_val_rec;

  l_rc   SYS_REFCURSOR := impactnet.tf_convertpara( <<some string>> );
  l_coll key_val_coll;
BEGIN
  LOOP
    FETCH l_rc
      BULK COLLECT INTO l_coll
     LIMIT 100;
    EXIT WHEN l_coll.count = 0;

    FORALL i IN l_coll.FIRST .. l_coll.LAST
      INSERT INTO some_table( key_column, val_column )
        VALUES( l_coll(i).key, l_coll(i).val );
  END LOOP;
END;