Oracle PL/SQL TABLE类型的TO

本文介绍了Oracle PL/SQL TABLE类型的TO_CHAR的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

出于调试目的，我希望能够"TO_CHAR"一个Oracle PL/SQL内存表.这是我想要做的一个简化示例:

For debugging purposes, I'd like to be able to "TO_CHAR" an Oracle PL/SQL in-memory table. Here's a simplified example, of what I'd like to do:

DECLARE TYPE T IS TABLE OF MY_TABLE%ROWTYPE INDEX BY PLS_INTEGER; V T; BEGIN -- .. -- Here, I'd like to dbms_output V's contents, which of course doesn't compile FOR i IN V.FIRST .. V.LAST LOOP dbms_output.put_line(V(i)); END LOOP; -- I want to omit doing this: FOR i IN V.FIRST .. V.LAST LOOP dbms_output.put_line(V(i).ID || ',' || V(i).AMOUNT ...); END LOOP; END;

这可以简单地实现吗?我问的原因是因为我懒得一次又一次地编写此调试代码，并且希望将其用于任何表类型.

Can this be achieved, simply? The reason I ask is because I'm too lazy to write this debugging code again and again, and I'd like to use it with any table type.

推荐答案

好的，抱歉，这还没有完成，但是要跟@Lukas进行跟进，这就是我到目前为止所要做的:

ok, sorry this isn't complete, but to followup with @Lukas, here's what I have so far:

首先，我没有尝试创建任何数据/任何类型类型，而是尝试使用从游标中提取的XML ...很奇怪，但它具有通用性:

First, instead of trying to create anydata/anytype types, I tried using XML extracted from a cursor...weird, but its generic:

CREATE OR REPLACE procedure printCur(in_cursor IN sys_refcursor) IS begin FOR c IN (SELECT ROWNUM rn, t2.COLUMN_VALUE.getrootelement () NAME, EXTRACTVALUE (t2.COLUMN_VALUE, 'node()') VALUE FROM TABLE (XMLSEQUENCE (in_cursor)) t, TABLE (XMLSEQUENCE (EXTRACT (COLUMN_VALUE, '/ROW/node()'))) t2 order by 1) LOOP DBMS_OUTPUT.put_line (c.NAME || ': ' || c.VALUE); END LOOP; exception when others then raise; end; /

现在，要调用它，您需要一个游标，所以我尝试在pl/sql中将其强制转换为游标，例如:

Now, to call it, you need a cursor, so I tried casting to cursor in pl/sql, something like:

open v_cur for select * from table(cast(v_tab as tab_type));

但是取决于v_tab的定义方式，这可能会或可能不会导致pl/sql强制转换出现问题(在嵌套表def中使用％rowtype似乎会产生问题).

But depending on how v_tab is defined, this may or may not cause issues in pl/sql cast (using %rowtype in nested table def seems to give issues).

无论如何，您都可以在此基础上构建或优化它. (并可能使用xmltable ...)

Anyway, you can build on this or refine it as you like. (and possibly use xmltable...)

希望有帮助