问题描述

如何在 PL/SQL 中打印记录变量的所有字段.记录变量有很多字段，所以有没有比打印每个字段更好的方法?也试过动态sql但没有帮助.

How can I print all the fields of a record variable in PL/SQL. The record variable has got lots of fields so is there a better way than printing each field? Also tried dynamic sql but didn't help.

推荐答案

建立在 Ollies 上使用 dbms_output，但用于动态遍历游标

Building on Ollies use of dbms_output, but for to dynamically go through the cursor

设置测试

/*create table temp (aa varchar2(50) , bb number , cc date ) ;

insert into temp (aa,bb,cc)
  select chr(level+100) , level, sysdate+level
    from dual
     connect by level < 15 ;
/
*/

<小时>

阻止显示测试(这里假设是 11g)

Block to show the test (this assumes 11g)

set serveroutput on
declare
   l_cur SYS_REFCURSOR ;

    PROCEDURE CursorOutput(
                            p_refcursor        IN OUT SYS_REFCURSOR
                         )  
    AS
        l_desc          DBMS_SQL.DESC_TAB ;
        l_cols          BINARY_INTEGER ;
        l_cursor        BINARY_INTEGER ;
        v_varchar2      VARCHAR2( 4000 ) ;
        v_number        NUMBER ;
        v_date          DATE ;
        l_data          varchar2( 32767 ) ;
        l_columnValue   VARCHAR2( 32767 ) ;
        l_processedRows Number := 0;
    BEGIN

        /* Convert refcursor "parameter" to DBMS_SQL cursor... */
        l_cursor := DBMS_SQL.TO_CURSOR_NUMBER( p_refcursor );
        /* Describe the cursor... */
        DBMS_SQL.DESCRIBE_COLUMNS( l_cursor, l_cols, l_desc );

        /* Define columns to be fetched. We're only using V2, NUM, DATE for example...
        for a complete list of the col_types this link is accessible.
        http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/sql_elements2a.htm#45504
        http://forums.oracle.com/forums/thread.jspa?threadID=912475
        if not a usable type, will throw new exception
        */
         FOR i IN 1 .. l_cols LOOP
             IF l_desc(i).col_type = 2 THEN
               DBMS_SQL.DEFINE_COLUMN(l_cursor, i, v_number);
            ELSIF l_desc(i).col_type = 12 THEN
               DBMS_SQL.DEFINE_COLUMN(l_cursor, i, v_date);
            ELSif l_desc(i).col_type = 01 or l_desc(i).col_type = 96 then
               DBMS_SQL.DEFINE_COLUMN(l_cursor, i, v_varchar2, 4000);
            else
                --raise an exception if the user's query contains a datatype not (yet) supported by this procedure
                RAISE_APPLICATION_ERROR(-20000, 'Invalid Data Type for conversion to delimited file. {' || l_desc(i).col_name || '}');
            END IF;
          END LOOP;


        /* -- print out the column names if desired
             FOR i IN 1 .. l_cols LOOP
                     dbms_output.put_line('** ' || l_desc(i).col_name) ;
             END LOOP;
        */

         /* Fetch all data... */
         WHILE DBMS_SQL.FETCH_ROWS(l_cursor) > 0 LOOP
             dbms_output.put_line('LINE: '  || l_processedRows || '');
             FOR i IN 1 .. l_cols LOOP
                 if l_desc(i).col_type = 12 THEN --we are in a date
                    DBMS_SQL.COLUMN_VALUE(l_cursor, i, v_date);
                    v_varchar2 := to_char(v_date , 'dd-MON-yyyy' ) ;
                 elsif  l_desc(i).col_type = 2 THEN --we are in a number
                    DBMS_SQL.COLUMN_VALUE(l_cursor, i, v_number);
                    v_varchar2 := to_char(v_number) ;
                 else --treat it as a string (should be varchar2,char,etc)
                    DBMS_SQL.COLUMN_VALUE(l_cursor, i, v_varchar2);
                    IF v_varchar2 IS NOT NULL THEN
                       v_varchar2 := '"' || v_varchar2 || '"' ;
                       ELSE
                       v_varchar2 := '';
                    END IF ;
                 end if ;
                 dbms_output.put_line(l_desc(i).col_name || '=>' || v_varchar2) ;
             END LOOP;
             l_processedRows := l_processedRows + 1 ;
          END LOOP;

          dbms_sql.close_cursor(l_cursor);
          dbms_output.put_line('I found and processed  '  || l_processedRows || ' rows .');

    END;

begin
        open l_cur for select * from temp;

        CursorOutput(p_refcursor => l_cur) ;

end ;
/

<小时>

会给你这个结果

will give you this result

LINE: 0
AA=>"e"
BB=>1
CC=>04-JAN-2012
LINE: 1
AA=>"f"
BB=>2
CC=>05-JAN-2012
LINE: 2
AA=>"g"
BB=>3
CC=>06-JAN-2012
LINE: 3
AA=>"h"
BB=>4
CC=>07-JAN-2012
LINE: 4
AA=>"i"
BB=>5
CC=>08-JAN-2012
LINE: 5
AA=>"j"
BB=>6
CC=>09-JAN-2012
LINE: 6
AA=>"k"
BB=>7
CC=>10-JAN-2012
LINE: 7
AA=>"l"
BB=>8
CC=>11-JAN-2012
LINE: 8
AA=>"m"
BB=>9
CC=>12-JAN-2012
LINE: 9
AA=>"n"
BB=>10
CC=>13-JAN-2012
LINE: 10
AA=>"o"
BB=>11
CC=>14-JAN-2012
LINE: 11
AA=>"p"
BB=>12
CC=>15-JAN-2012
LINE: 12
AA=>"q"
BB=>13
CC=>16-JAN-2012
LINE: 13
AA=>"r"
BB=>14
CC=>17-JAN-2012
I found and processed  14 rows .

我做了类似的事情，以使用这两个链接作为源动态构建一个 csv 文件http://www.oracle-developer.net/display.php?id=505http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:88212348059

I had done something similar to this to dynamically build a csv file utilizing these two links as sources http://www.oracle-developer.net/display.php?id=505 http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:88212348059

不过，根据您的目的，您可能只想在 SQL Developer(或 Toad)中运行它并导出结果！

Depending on what you are going for, however, you may just want to run it in SQL Developer (or Toad) and export the results!

这篇关于在 PL/SQL 中打印记录字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！