Oracle System Package--dbms_output usage

The Dbms_output package is primarily used to debug PL/SQL programs, or to display information (displaying message) and reports in the Sql*plus command, such as we can write a simple anonymous PL/SQL program block that is used for some purpose 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 (default is open)
2, disable: In the case of serveroutput on, used to invalidate the Dbms_output
3, put: Write the content to memory, wait until put_line together output
4, Put_Line: Needless to say, the output character
5, New_line: As the end of a line, can be understood as the line break when writing buffer
6, Get_line (value, index): Gets the single-line information of the buffer
7, Get_lines (array, index): Gets the multi-line information of the buffer in array form

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 to ON
2, each row can hold the maximum value is 32767bytes
3, the default value of buffer is 20000bytes, the minimum value can be set to 2000bytes, the maximum value is 1000000bytes

Example one, put and new_line

123456789

set serveroutput on;begin   dbms_output.put(‘a‘); --写入buffer但不输出   dbms_output.put(‘b‘); --写入buffer但不输出   dbms_output.new_line; --回车(换行)，输出                                 dbms_output.put_line(‘hello world!‘); --输出并换行    dbms_output.put(‘d‘); --写入buffer但不输出 end;                                                     /

Execute the result of the operation:

12	abhello world!

Example Two, put_line

123456789101112131415161718192021222324252627282930

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(120);                                  cursor t_cur is select * from t order by a;         v_line varchar2(100);                               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);               dbms_output.get_line(v_line, v_status);             end loop;                                       end;                                            /                                                            select * from tt;

The results of the implementation are as follows:

12345

a    b--- ----------------------- 0   111111,222222,3333330   444444,555555,6666660   777777,888888,999999

Note: You cannot use the Put_Line output 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

12345678910111213141516171819

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:

123	line oneline twoline three

Note The format used by the array.

Oracle System Package--dbms_output usage