【转】DBMS_OUTPUT包学习
原文链接 http://www.blogjava.net/decode360/archive/2009/06/06/282867.html
虽然一直在使用DBMS_OUTPUT.PUT_LINE，但是说实话没有仔细研究过DBMS_OUTPUT包中的其他方法和函数的用法，所以这次特地来研究一下。 先简单的讲解一下这个包的所有procedure的含义及作用： ----------------------- 1、enable：在serveroutput on的情况下，用来使dbms_output生效(默认即打开) 2、disable：在serveroutput on的情况下，用来使dbms_output失效 3、put：将内容写到内存，等到put_line时一起输出 4、put_line：不用多说了，输出字符 5、new_line：作为一行的结束，可以理解为写入buffer时的换行符 6、get_line：获取没有输出的buffer中的信息 7、get_lines：以数组形式来获取所有buffer中的信息 需要注意以下几点： ----------------------- 1、set serveroutput on：如果要在sqlplus中看到dbms_output的输出， 则必须设置该参数值为on 2、每行能容纳的最大值是32767bytes 3、buffer的默认值是20000bytes，可设置的最小值为2000bytes，最大值为1000000bytes 来看一下Package中自带的说明： ----------------------- create or replace package sys.dbms_output as -- DE-HEAD <- tell SED where to cut when generating fixed package ------------ -- OVERVIEW -- -- These procedures accumulate information in a buffer (via "put" and -- "put_line") so that it can be retrieved out later (via "get_line" or -- "get_lines"). If this package is disabled then all -- calls to this package are simply ignored. This way, these routines -- are only active when the client is one that is able to deal with the -- information. This is good for debugging, or SP's that want to want -- to display messages or reports to sql*dba or plus (like 'describing -- procedures', etc.). The default buffer size is 20000 bytes. The -- minimum is 2000 and the maximum is 1,000,000. ----------- -- EXAMPLE -- -- A trigger might want to print out some debugging information. To do -- do this the trigger would do -- dbms_output.put_line('I got here:'||:new.col||' is the new value'); -- If the client had enabled the dbms_output package then this put_line -- would be buffered and the client could, after executing the statement -- (presumably some insert, delete or update that caused the trigger to -- fire) execute -- begin dbms_output.get_line(:buffer, :status); end; -- to get the line of information back. It could then display the -- buffer on the screen. The client would repeat calls to get_line -- until status came back as non-zero. For better performance, the -- client would use calls to get_lines which can return an array of -- lines. -- -- SQL*DBA and SQL*PLUS, for instance, implement a 'SET SERVEROUTPUT -- ON' command so that they know whether to make calls to get_line(s) -- after issuing insert, update, delete or anonymous PL/SQL calls -- (these are the only ones that can cause triggers or stored procedures -- to be executed). ------------ -- SECURITY -- -- At the end of this script, a public synonym (dbms_output) is created -- and execute permission on this package is granted to public. ---------------------------- -- PROCEDURES AND FUNCTIONS -- procedure enable (buffer_size in integer default 20000); pragma restrict_references(enable,WNDS,RNDS); -- Enable calls to put, put_line, new_line, get_line and get_lines. -- Calls to these procedures are noops if the package has -- not been enabled. Set default amount of information to buffer. -- Cleanup data buffered from any dead sessions. Multiple calls to -- enable are allowed. -- Input parameters: -- buffer_size -- Amount of information, in bytes, to buffer. Varchar2, number and -- date items are stored in their internal representation. The -- information is stored in the SGA. An error is raised if the -- buffer size is exceeded. If there are multiple calls to enable, -- then the buffer_size is generally the largest of the values -- specified, and will always be >= than the smallest value -- specified. Currently a more accurate determination is not -- possible. The maximum size is 1,000,000, the minimum is 2000. procedure disable; pragma restrict_references(disable,WNDS,RNDS); -- Disable calls to put, put_line, new_line, get_line and get_lines. -- Also purge the buffer of any remaining information. procedure put(a varchar2); pragma restrict_references(put,WNDS,RNDS); -- Put a piece of information in the buffer. When retrieved by -- get_line(s), the number and date items will be formated with -- to_char using the default formats. If you want another format -- then format it explicitly. -- Input parameters: -- a -- Item to buffer procedure put_line(a varchar2); pragma restrict_references(put_line,WNDS,RNDS); -- Put a piece of information in the buffer followed by an end-of-line -- marker. When retrieved by get_line(s), the number and date items -- will be formated with to_char using the default formats. If you -- want another format then format it explicitly. get_line(s) return -- "lines" as delimited by "newlines". So every call to put_line or -- new_line will generate a line that will be returned by get_line(s). -- Input parameters: -- a -- Item to buffer -- Errors raised: -- -20000, ORU-10027: buffer overflow, limit of <buf_limit> bytes. -- -20000, ORU-10028:line length overflow, limit of 32767 bytes per line. procedure new_line; pragma restrict_references(new_line,WNDS,RNDS); -- Put an end-of-line marker. get_line(s) return "lines" as delimited -- by "newlines". So every call to put_line or new_line will generate -- a line that will be returned by get_line(s). procedure get_line(line out varchar2, status out integer); pragma restrict_references(get_line,WNDS,RNDS); -- Get a single line back that has been buffered. The lines are -- delimited by calls to put_line or new_line. The line will be -- constructed taking all the items up to a newline, converting all -- the items to varchar2, and concatenating them into a single line. -- If the client fails to retrieve all lines before the next put, -- put_line or new_line, the non-retrieved lines will be discarded. -- This is so if the client is interrupted while selecting back -- the information, there will not be junk left over which would -- look like it was part of the NEXT set of lines. -- Output parameters: -- line -- This line will hold the line - it may be up to 32767 bytes long. -- status -- This will be 0 upon successful completion of the call. 1 means -- that there are no more lines. type chararr is table of varchar2(32767) index by binary_integer; procedure get_lines(lines out chararr, numlines in out integer); pragma restrict_references(get_lines,WNDS,RNDS); -- Get multiple lines back that have been buffered. The lines are -- delimited by calls to put_line or new_line. The line will be -- constructed taking all the items up to a newline, converting all -- the items to varchar2, and concatenating them into a single line. -- Once get_lines is executed, the client should continue to retrieve -- all lines because the next put, put_line or new_line will first -- purge the buffer of leftover data. This is so if the client is -- interrupted while selecting back the information, there will not -- be junk left over. -- Input parameters: -- numlines -- This is the maximum number of lines that the caller is prepared -- to accept. This procedure will not return more than this number -- of lines. -- Output parameters: -- lines -- This array will line will hold the lines - they may be up to 32767 -- bytes long each. The array is indexed beginning with 0 and -- increases sequentially. From a 3GL host program the array begins -- with whatever is the convention for that language. -- numlines -- This will be the number of lines actually returned. If it is -- less than the value passed in, then there are no more lines. --FIXED_ONLYTYPE dbmsoutput_linesarray IS VARRAY(2147483647) OF --FIXED_ONLY VARCHAR2(32767); procedure get_lines(lines out dbmsoutput_linesarray, numlines in out integer); -- get_lines overload with dbmsoutput_linesarray varray type for lines. -- It is recommended that you use this overload in a 3GL host program to -- execute get_lines from a PL/SQL anonymous block. pragma restrict_references(get_lines,WNDS,RNDS); pragma TIMESTAMP('2000-06-22:11:21:00'); end; -- CUT_HERE <- tell sed where to chop off the rest 下面举几个例子来说明一些不常用的方法的使用： 例子1：(put和new_line) ----------------------- SQL> set serveroutput on SQL> begin 2 dbms_output.put_line('three names will be written.'); 3 dbms_output.put('j'); --增加至buffer 4 dbms_output.new_line; --回车 5 dbms_output.put('s'); --增加至buffer 6 dbms_output.new_line; --回车 7 dbms_output.put('t'); --增加至buffer 8 dbms_output.new_line; --回车 9 dbms_output.put_line('over.'); --与之前所有一起输出 10 end; 11 / three names will be written. j s t over. PL/SQL procedure successfully completed 例子2：(put_line) ----------------------- SQL> set serveroutput off; SQL> create table t(a int,b int,c int); Table created SQL> insert into t values(111111,222222,333333); 1 row inserted SQL> insert into t values(444444,555555,666666); 1 row inserted SQL> insert into t values(777777,888888,999999); 1 row inserted SQL> create table tt(a int,b varchar2(100)); Table created SQL> declare 2 msg varchar2(120); 3 cursor t_cur is select * from t order by a; 4 v_line varchar2(100); 5 v_status integer := 0; 6 begin 7 dbms_output.enable; 8 for i in t_cur loop 9 msg := i.a || ',' || i.b || ',' || i.c; 10 dbms_output.put_line(msg); 11 end loop; 12 13 dbms_output.get_line(v_line,v_status); 14 while v_status = 0 loop 15 insert into tt values(v_status, v_line); 16 dbms_output.get_line(v_line,v_status); 17 end loop; 18 end; 19 / PL/SQL procedure successfully completed SQL> select * from tt; A B --- ------------------------------ 0 111111,222222,333333 0 444444,555555,666666 0 777777,888888,999999 注：使用get_line时不能用put_line输出，因为put_line之后会将buffer清空。 (当然在serveroutput off的情况下put_line是不影响buffer的) 例子3：(put_lines) ----------------------- SQL> set serveroutput on SQL> declare 2 v_data dbms_output.chararr; 3 v_numlines number; 4 begin 5 -- enable the buffer first. 6 dbms_output.enable(1000000); 7 8 dbms_output.put_line('line one'); 9 dbms_output.put_line('line two'); 10 dbms_output.put_line('line three'); 11 12 v_numlines := 3; 13 dbms_output.get_lines(v_data, v_numlines); 14 for v_counter in 1..v_numlines loop 15 dbms_output.put_line(v_data(v_counter)); 16 end loop; 17 end; 18 / line one line two line three PL/SQL procedure successfully completed 注意数组使用的格式。
