oracleDBMS_OUTPUT包学习.doc

DBMS_OUTPUT包学习虽然一直在使用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 = 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 cedure 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 bufferprocedure 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 bytes.- -20000, ORU-10028:line length overflow, limit of 32767 bytes per cedure 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 set serveroutput onSQL begin2 dbms_output.put_line(three names will be written.);3 dbms_output.put(j); -增加至buffer4 dbms_output.new_line; -回车5 dbms_output.put(s); -增加至buffer6 dbms_output.new_line; -回车7 dbms_output.put(t); -增加至buffer8 dbms_output.new_line; -回车9 dbms_output.put_line(over.); -与之前所有一起输出10 end;11 /three names will be written.jstover.PL/SQL procedure successfully completed 例子2：(put_line) - SQL set serveroutput off;SQL create table t(a int,b int,c int);Table createdSQL insert into t values(111111,222222,333333);1 row insertedSQL insert into t values(444444,555555,666666);1 row insertedSQL insert into t values(777777,888888,999999);1 row insertedSQL create table tt(a int,b varchar2(100);Table createdSQL declare2 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 begin7 dbms_output.enable;8 for i in t_cur loop9 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 loop15 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 completedSQL select * from tt;A B- -0