在 PL/SQL 中打印记录字段 [英] Print Record fields in PL/SQL
问题描述
如何在 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 中打印记录字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
