oracle 在 PL/SQL 中打印记录字段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8717251/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Print Record fields in PL/SQL
提问by Iban
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.
如何在 PL/SQL 中打印记录变量的所有字段。记录变量有很多字段，所以有没有比打印每个字段更好的方法？也试过动态sql但没有帮助。
回答by Harrison
Building on Ollies use of dbms_output, but for to dynamically go through the cursor
建立在 Ollies 使用 dbms_output 的基础上，但用于动态遍历游标
set up for test
设置测试
/*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 ;
/
*/
Block to show the test (this assumes 11g)
块以显示测试（假设为 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 .
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=505http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:88212348059
我做了类似的事情，使用这两个链接作为源动态构建一个 csv 文件 http://www.oracle-developer.net/display.php?id=505 http://asktom.oracle.com/pls/顶点/f?p=100:11:0::::P11_QUESTION_ID:88212348059
Depending on what you are going for, however, you may just want to run it in SQL Developer (or Toad) and export the results!
但是，根据您的目的，您可能只想在 SQL Developer（或 Toad）中运行它并导出结果！
回答by Ollie
If it's a PL/SQL block that you are running in an IDE then you could at a pinch use DBMS_OUTPUT to output the values.
如果它是您在 IDE 中运行的 PL/SQL 块，那么您可以在紧要关头使用 DBMS_OUTPUT 来输出值。
http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_output.htm
http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_output.htm
For example:
例如：
SET SERVEROUTPUT ON
DECLARE
-- Define the record
TYPE test_rectype IS RECORD (
field1 NUMBER,
field2 VARCHAR2
);
-- Define a variable for the record
test_rec TEST_RECTYPE;
BEGIN
-- Populate the record
test_rec.field1 := 1;
test_rec.field2 := 'my value';
-- Enable the DBMS_OUTPUT
DBMS_OUTPUT.enable(1000000);
-- Send the output to the buffer
DBMS_OUTPUT.put_line('Field1: '||test_rec.field1||', Field2: '||test_rec.field2);
END;
There is more to DBMS_OUTPUT so take a look at the docs from the link above.
DBMS_OUTPUT 还有更多内容，因此请查看上面链接中的文档。
Alternatively, you could write the values to a file using UTL_FILE.
或者，您可以使用 UTL_FILE 将值写入文件。
http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/u_file.htm
http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/u_file.htm
Hope it helps...
希望能帮助到你...
