Oracle System Package--dbms_output usage ____oracle

Dbms_output packages are primarily used to debug pl/sql programs, or to display information (displaying messages) and reports in Sql*plus commands, such as we can write a simple anonymous PL/SQL program block that is used for some reason Dbms_ Output package to display some information.

The knowledge points involved are as follows:
1, enable: In the case of serveroutput on, used to make dbms_output effective (open by default)
2, disable: In Serveroutput on the case, used to make dbms_output failure
3, put: Write content to memory, wait until put_line output together
4, Put_Line: No more said, output characters
5, New_line: As a line of the end, can be interpreted as writing buffer when the line break
6, Get_line (value, index): Obtaining a single line of information for a buffer
7, Get_lines (array, index): In array form to get the buffer of multiple lines of information

The following points need to be noted:
1. Set Serveroutput on: If you want to see the output of dbms_output in Sqlplus, you must set the parameter value on
2, each row can accommodate the maximum value is 32767bytes
3, the default value of buffer is 20000bytes, can be set to the minimum value of 2000bytes, the maximum is 1000000bytes

example one, put and New_line

1 2 3 4 5 6 7 8 9

Set serveroutput on; Begin Dbms_output.put (' a '); --Writes buffer but does not output dbms_output.put (' B '); --Writes buffer but does not output dbms_output.new_line; --Carriage return (line change), Output dbms_output.put_line (' Hello world! '); --Output and newline dbms_output.put (' d ');                                                     --Writes buffer but does not output end; /

Execute run Result:

1 2	AB Hello world!

example Two, Put_Line

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30

Set Serveroutput off; CREATE TABLE t (a int, b int, c int); INSERT into T values (111111,222222,333333); INSERT into T values (444444,555555,666666); INSERT into T values (777777,888888,999999); commit;   CREATE TABLE TT (a int, b varchar2 (100));   DECLARE     msg varchar2 (;          )                           cursor T_cur is select * FROM T-order by a;        &nbs P V_line varchar2 (MB);                                 v_status integer: = 0;                             begin                                                  dbms_output.enable;                                    for i in T_cur loop                                        msg: = I.A | | ',' || I.B | | ',' || i.c;                  Dbms_ Output.put_line (msg); --put                        End loop;                                                                                                   dbms_output.get_line (V_line, v_status); --get              While v_status = 0 loop                                   INSERT INTO TT values (V_status, V_line);           &NBsp;     dbms_output.get_line (V_line, V_status);               End loop;                                         End;                                             /                                                                &nbsp SELECT * from TT;

The results of the implementation are as follows:

1 2 3 4 5

A b--------------------------0 111111,222222,333333 0 444444,555555,666666 0 777777,888888,999999

Note: Put_Line output cannot be used when using get_line, because the buffer will be emptied after put_line. (Of course, in the case of serveroutput off Put_Line does not affect the buffer).

example three: Put_lines

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

Set serveroutput on; DECLARE     v_data dbms_output.chararr;                                V_numlines number;                                     begin                                                     --enable The buffer first.                               dbms_output.enable (1000000);                                                                                        Dbms_output.put_line (' line one ');                         Dbms_output.put_line (' line two ');                         Dbms_output.put_line (' line three ');                                                                                v_numlines: = 3;                                           Dbms_output.get_lines (V_data, V_numlines); --array, index            for V_counter in 1..v_numlines loop                          dbms_output.put_line (V_data (v_ Counter);                End loop;                                              End;                                                 /

The results of the implementation are as follows:

1 2 3

Line one line two line three

Note The format used by the array.