如何从未知列数的存储过程中查看Toad中结果集的内容? [英] How to view the content of a resultset in Toad from a stored procedure with unknown number of columns?

问题描述

在Tsql中，我可以在查询分析器中执行存储过程，并在查询分析器窗口中查看结果集的内容，而无需了解查询结构(表，列，...)

In Tsql I can execute a stored procedure in Query Analyzer and view the content of a resultset right there query analyzer window without know anything about the query structure (tables, columns, ...)

-Tsql示例 exec myproc parm1，parm2，parm3

--Tsql sample exec myproc parm1, parm2, parm3

现在，我正在使用PLsql和Toad(对于Toad来说，我相对较新).我需要查看卷积存储过程的结果集的内容，但我不知道列数是多少-更不用说它们的数据类型了(此proc由几个怪异的子查询组成-我可以分别查看，但它们会受到影响，并且最终结果集中的列数会有所不同).不知道有多少列或它们的数据类型时，执行该过程时如何在Toad中查看此结果集的内容?

Now I am working with PLsql and Toad (which I am relatively new at for Toad). I need to view the content of a resultset of a convoluted stored procedure, and I don't know what the number of columns is -- let alone their data types (this proc is composed of several freaky subqueries -- which I can view individually, but they get pivoted, and the number of columns varies in the final resultset). How can I view the content of this resultset in Toad when I execute the procedure when I don't know how many columns there are or their data types?

下面是我聚集在一起的代码，用于查看存储过程结果集中的内容，其中我提前知道有多少列及其数据类型.在下面的代码示例中，我使用了一个名为x_out的sys_refcursor，并且还创建了一个临时表来存储结果集的内容以供其他查看.当我不知道结果集中有多少列时，有没有办法做到这一点?如何使用PLsql-Toad做到这一点?

Below is code that I have mustered together for viewing the content of a result set of stored procedures where I know how many columns there are and their data types ahead of time. In my code sample below I use a sys_refcursor that I named x_out and I also create a temporary table to store the content of the resultset for additional viewing. Is there a way I can do this when I don't know how many columns there are in the resultset? How to do this with PLsql -- Toad?

create global temporary table tmpResult (fld1 number, fld2 varchar(50), fld3 date);

declare
  x_out sys_refcursor;  
  tmpfld1 number;
  tmpfld2 varchar2(50);
  tmpfld3 date;

BEGIN
  myschema.mypkg.myproc(parm1, parm2, x_out);

LOOP
    FETCH x_out INTO tmpfld1, tmpfld2, tmpfld3;
    DBMS_OUTPUT.Put_Line ('fld1:-- '||tmpfld1||': fld2:-- '||tmpfld2||':   fld3:-- '||tmpfld3);

-- I also insert the result set to a temp table for additional viewing of the data from the stored procedure

    Insert Into tmpResult values(tmpfld1, tmpfld2, tmpfld3);        
    EXIT WHEN x_out%NOTFOUND;    

END LOOP;

END;

推荐答案

，这是另一种方式的前半部分:

and here is the 1st half of the other way:

DECLARE 
x_out SYS_REFCURSOR; 

CURSOR get_columns IS 

...

这篇关于如何从未知列数的存储过程中查看Toad中结果集的内容?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！