首页 文章

在Oracle10gv2中使用sys_refcursor发送嵌套表

提问于
浏览 次
2

我需要使用sys_refcursor将一些数据发送到Jasper Reports . 此数据是查询结果和pl / sql中查询结果的评估结果 . 我们的想法是计算一些值在同一个表中按几列过滤，并且由于过滤限制而无法在具有子选择的查询中完成 . 很抱歉不是很清楚，但我是在NDA下 . 但是，我可以发布一些代码并解释我必须实现的功能的重要部分 . 该项目基于Java并使用Oracle 10gv2和JasperReports 3.6.1，但无法更新（因此没有Oracle v12） .

我有一个关联数组的过程，其中填充了键和我必须返回的值 . Keys表示与目标报表上的每个列类型关联的过滤结果，值是必须填充正确列的数字 . 这是过程创建和关联数组的声明 .

create or replace PROCEDURE test_proc02(test_cursor OUT sys_refcursor) IS

    /* associative arrays declaration */

    TYPE transfer_type IS TABLE OF NUMBER
        INDEX BY VARCHAR2(10);
    transfer_table transfer_type;

但其中一个问题是我不能像这样使用带有sys_refcursor的关联数组：

Select * from table(cast(transfer_table AS transfer_type))

因此，我将关联数组值复制到嵌套表中，认为先前的选择将适用于该结构 . 这是代码的一部分

/* nested table declaration */

TYPE transfer_nt_type IS TABLE OF VARCHAR2(20);

/* nested table initilization */
transfer_nt transfer_nt_type := transfer_nt_type();

/* some variables */
transfer_id VARCHAR2(10);
transfer_number NUMBER;
nt_counter INTEGER := 0;
nt_iter VARCHAR2(10);


/* copying AA into NT */
nt_iter := transfer_table.FIRST;
WHILE (nt_iter IS NOT NULL)
LOOP        
    nt_counter := nt_counter+1;
    transfer_nt.EXTEND;
    transfer_nt(nt_counter):=transfer_table(nt_iter);
    dbms_output.put_line('nested table ('||nt_counter||'): '||transfer_nt(nt_counter));
    nt_iter := transfer_table.NEXT(nt_iter);
END LOOP;

/* Trying to send NT to JR */
OPEN travelCursor FOR SELECT * FROM TABLE(cast(transfer_nt AS transfer_nt_type));

/* ERROR */
PLS-00382: expression is of wrong type

我不关心方法，我只想将数据发送到JR生成报告，所以如果我必须更换完整的Procedure结构就可以了 . 我在stackoverflow和其他来源搜索了几天，但似乎没有任何工作，所以我不确定我的所有概念想法是否都是错误的 .

有任何想法吗？谢谢 .

编辑：

transfer_nt_type的Type声明错误，从先前版本复制 . 现在这是正确的 . AA的数据是这样的：

Key       value
--------------
A548521     5
A325411     12
A329471     9

对的总数为32，密钥为varchar2（10），值为数字 . 最终嵌套表的内容（VARCHAR（20））是：

A548521#5,A325411#12,A329471#9

类型在架构级别声明 . 我也试过了：

OPEN travelCursor FOR 
        SELECT CAST(MULTISET(
            SELECT * FROM TABLE(transfer_nt)
            ORDER BY 1) AS transfer_nt_type)
        INTO transfer_nt_out FROM DUAL;

结果相同 . 两个数据结构都已经过dbms_output测试和打印，完美无缺，结构内的数据是正确的 . 如果可能的话，我至少需要以给定的顺序发送值 . 如果我可以在值响应中维护某个顺序，则键不重要 .

编辑以反映Alex Poole提案 . 在程序开始之前：

FUNCTION transfer_func (transfer_table transfer_type)RETURN transfer_nt_type PIPELINED IS

      --TYPE transfer_type IS TABLE OF NUMBER INDEX BY VARCHAR2(10);
      --transfer_table transfer_type; 

      nt_iter VARCHAR2(10);

      BEGIN

          nt_iter := transfer_table.FIRST;
          WHILE (nt_iter IS NOT NULL)
          LOOP
            PIPE ROW (nt_iter || '#' || transfer_table(nt_iter));
            nt_iter := transfer_table.NEXT(nt_iter);
          END LOOP;

    END transfer_func;

在程序结束前：

OPEN travelCursor for select * from table(transfer_func(transfer_table));

同样的错误：

PLS-00382: expression is of wrong type

Final edit and solution:

最后我用GTT解决了这个问题 . 我不知道为什么，但是第一次尝试这种方法时Oracle Developer会返回与其他可能解决方案相同的错误 . 我尝试了最古老的方法：关闭程序，重置机器并从头开始·那就有效了！当然，只有GTT .

nt_iter := transfer_table.FIRST;
WHILE (nt_iter IS NOT NULL)
LOOP        
        nt_counter := nt_counter+1;
        INSERT INTO transfer_temp VALUES(nt_iter,transfer_table(nt_iter),06);

        nt_iter := transfer_table.NEXT(nt_iter);
END LOOP;

    OPEN test_cursor FOR select * from transfer_temp order by transfer_temp.id;

CREATE GLOBAL TEMPORARY TABLE transfer_temp (
        id           VARCHAR(20),
        value         NUMBER,
        month         NUMBER
        )
        ON COMMIT PRESERVE ROWS;

谢谢大家的帮助！

plsql cursor oracle10g nested-table sys-refcursor

3 回答

  • 2

    然后，一个（可能是低性能）的简单解决方案是将结果写入临时表

    OPEN travelCursor FOR SELECT * FROM That_Temp_Table;
    回复于
  • 0

    您可以使用流水线函数，使用模式级表类型：

    create or replace TYPE transfer_nt_type AS TABLE OF VARCHAR2(20)
/

create or replace FUNCTION test_func02
RETURN transfer_nt_type PIPELINED IS

  TYPE transfer_type IS TABLE OF NUMBER INDEX BY VARCHAR2(10);
  transfer_table transfer_type; 

  nt_iter VARCHAR2(10);

BEGIN

  -- sample data
  transfer_table('A548521') := 5;
  transfer_table('A325411') := 12;
  transfer_table('A329471') := 9;

  nt_iter := transfer_table.FIRST;
  WHILE (nt_iter IS NOT NULL)
  LOOP
    PIPE ROW (nt_iter || '#' || transfer_table(nt_iter));
    nt_iter := transfer_table.NEXT(nt_iter);
  END LOOP;

  RETURN;

END test_func02;
/

    然后你可以这样做：

    select * from table(test_func02);

    获得：

    Result Sequence     
--------------------
A325411#12
A329471#9
A548521#5

    如果您被限制为引用游标，则可以添加包装程序：

    create or replace PROCEDURE test_proc02(test_cursor OUT sys_refcursor) IS
BEGIN

  OPEN test_cursor FOR SELECT * FROM TABLE(test_func02);

END test_proc02;
/

var rc refcursor;

exec test_proc02(:rc);

print rc;

Result Sequence     
--------------------
A325411#12
A329471#9
A548521#5
    回复于
  • 0

    我相信你有问题，因为：

    1）您在程序中定义了类型

    所以尝试创建类似上面的例子

    CREATE OR REPLACE TYPE "TSTRINGTABLE" AS TABLE OF VARCHAR2(20)
/

    2）你在索引表上使用select * from ...

    如果你需要在嵌套表中存储2个值，那么创建记录类型的记录类型表

    CREATE OR REPLACE TYPE "TSTRING2LIST_RECORD" AS OBJECT
(
  column1_value VARCHAR2(4000),
  column2_value VARCHAR2(4000)
)
/

CREATE OR REPLACE TYPE "TSTRING2LIST_TABLE" IS TABLE OF TSTRING2LIST_RECORD
/

    之后你可以在你的程序中声明它并使用它你想要的任何东西，下面的代码可能会破坏，因为我没有IDE写它，但你需要 grab 我的想法：

    PROCEDURE idk(cur out sys_refcursor) IS
  l_table tstring2list_table := tstring2list_table();

BEGIN
  l_table.extend;
  l_table(l_table.count) := tstring2list_record('idx1', 'value1');
  l_table.extend;
  l_table(l_table.count) := tstring2list_record('idx2', 'value2');
  l_table.extend;
  l_table(l_table.count) := tstring2list_record('idx3', 'value3');

  OPEN cur for select column2_value from table(l_table);
END;

    您还可以使用批量收集或动态SQL或其他：

    SELECT tstring2list_record(t.col1, t.col2) BULK COLLECT
  INTO l_table
  FROM some_table t
    回复于

相关问题