[20180111]Oracle's Bulk Collect and Limit2.txt

--//昨天在测试时遇到一个的奇怪问题，链接http://blog.itpub.net/267265/viewspace-2149969/,执行
--//@ limit 9 时，重复输出了2次。主要是我的glogin配置文件里面包含如下：
set termout off
call dbms_output.enable(1e6);
set termout on

--//itpub论坛的提问：http://www.itpub.net/thread-2096243-1-1.html
--//缺省设置 set serverout off，而且dbms_output设置为disable，而我在配置文件中打开了即使是set serverout off的情况下。
--//通过一个例子说明问题：

1.环境：
SCOTT@book> @ &r/ver1

PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SCOTT@book> show serveroutput
serveroutput OFF

2.测试：

SCOTT@book> call dbms_output.enable(1e6);
Call completed.

SCOTT@book> exec DBMS_OUTPUT.put_line ('this is a first test!');
PL/SQL procedure successfully completed.

--//现在不显示是正常的。

SCOTT@book> set serverout on
SCOTT@book> exec DBMS_OUTPUT.put_line ('this is a second test!');
this is a first test!
this is a second test!
PL/SQL procedure successfully completed.

--//OK,现在把在缓存的第一行一起显示出来。出现了2行。再次执行就不会了。

SCOTT@book> exec DBMS_OUTPUT.put_line ('this is a second test!');
this is a second test!
PL/SQL procedure successfully completed.

3.回到我当时的出现的问题就很好理解了。
我现在set serverout off情况下执行
set serverout off
@limit 9
set serverout on
@limit 9

--//这样就出现2次显示，感觉重复显示了。主要问题我在set serverout off的情况下打开了dbms_output.enable(1e6)。
--//而这样设置源于去年的这个帖子：http://blog.itpub.net/267265/viewspace-2148591/=>[20171211]dbms_output无serveroutput on
--//这样看来没有想作者想像的这么复杂，只要随手调用1次exec DBMS_OUTPUT.put_line就ok了。

4.测试：
--//退出在登录：
SCOTT@book> exec dbms_output.enable(1e6);
PL/SQL procedure successfully completed.

SCOTT@book> exec dbms_output.put_line('first');
PL/SQL procedure successfully completed.

SCOTT@book> set serverout on
SCOTT@book> exec dbms_output.put_line(' ');
first

PL/SQL procedure successfully completed.

--//退出在登录：
SCOTT@book> exec dbms_output.enable(1e6);

PL/SQL procedure successfully completed.

SCOTT@book> @ limit 9
old  13:       FETCH get_emp_data BULK COLLECT INTO emp_c LIMIT &1;
new  13:       FETCH get_emp_data BULK COLLECT INTO emp_c LIMIT 9;
PL/SQL procedure successfully completed.

SCOTT@book> @ &r/output_print.sql
PL/SQL procedure successfully completed.
OUTPUT
---------------
7369SMITH800
7499ALLEN1600
7521WARD1250
7566JONES2975
7654MARTIN1250
7698BLAKE2850
7782CLARK2450
7788SCOTT3000
7839KING5000

10 rows selected.

--//这样的好处不用设置set serverout on就能显示。