Connected to Oracle9i Enterprise Edition Release 9.2.0.1.0 Connected as chanet SQL> SELECT COUNT(*) FROM dual CONNECT BY ROWNUM < 151; COUNT(*) ---------- 150SQL>
SQL> select level as id from dual connect by level < 102; ID ---------- .......... 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100100 rows selected为什么不是101条记录呢,而是100调剂路呢?
更奇怪的是: SQL> declare cursor csr is select id from (select level as id from dual connect by level < 102); 2 begin 3 for y in csr loop 4 if y.id > 98 then 5 dbms_output.put_line('@@@@@@'||y.id); 6 end if; 7 end loop; 8 end ; 9 /@@@@@@99 @@@@@@100 @@@@@@101PL/SQL procedure successfully completed通过游标访问得到的就是101条记录?
SQL> SELECT COUNT(*) FROM dual CONNECT BY ROWNUM < 151; COUNT(*) ---------- 150 SQL> select level as id from dual connect by level < 102; 1--101--看来我这边显示都很正常阿
不同的client显示是不一样的,谁让这个表是虚的呢,呵! 我的SQLPLUS: SQL> SELECT count(*) FROM dual CONNECT BY ROWNUM < 151; COUNT(*) ---------- 150SQL> SELECT * FROM dual CONNECT BY ROWNUM < 151;D - XSQL> select level as id from dual connect by level<102; ID ---------- 1我在Toad中执行和上述结果是不一样的,很奇怪!慢慢研究! Toad中的select level as id from dual connect by level<102;结果记录为1-->25
pl/developer: create table tab_a as 2 select level as id from dual connect by level<150;Table createdSQL> select count(*) from tab_a; COUNT(*) ---------- 149SQL>
在CMD模式下SQLPLUS里, 只显示一条:SQL> select level as id from dual connect by level < 102; ID ---------- 1
我用pb试验,和pl/sql 都没有问题
select level as id from dual connect by level < 102ORA-01436: 用户数据中的 CONNECT BY 循环SQL>各位高手, 这里什么问题??
select * from (select * from dual connect by rownum < 151)能显示全部
我是怀疑: oracle考虑到这样的情况,就定义显示100之内,没啥为什么!就是个常量了。
为什么我的是 这样子的呢,大家都可以 SQL> SELECT COUNT(*) FROM dual CONNECT BY ROWNUM < 151 2 / SELECT COUNT(*) FROM dual CONNECT BY ROWNUM < 151 * ERROR 位于第 1 行: ORA-01436: 用户数据中的 CONNECT BY 循环
Connected as chanet
SQL> SELECT COUNT(*) FROM dual CONNECT BY ROWNUM < 151; COUNT(*)
----------
150SQL>
----------
..........
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100100 rows selected为什么不是101条记录呢,而是100调剂路呢?
SQL> declare cursor csr is select id from (select level as id from dual connect by level < 102);
2 begin
3 for y in csr loop
4 if y.id > 98 then
5 dbms_output.put_line('@@@@@@'||y.id);
6 end if;
7 end loop;
8 end ;
9 /@@@@@@99
@@@@@@100
@@@@@@101PL/SQL procedure successfully completed通过游标访问得到的就是101条记录?
----------
150
SQL> select level as id from dual connect by level < 102;
1--101--看来我这边显示都很正常阿
不是的,其它的SQL语句就能显示全部记录,当然是超过100条记录的。
我的SQLPLUS:
SQL> SELECT count(*) FROM dual CONNECT BY ROWNUM < 151; COUNT(*)
----------
150SQL> SELECT * FROM dual CONNECT BY ROWNUM < 151;D
-
XSQL> select level as id from dual connect by level<102; ID
----------
1我在Toad中执行和上述结果是不一样的,很奇怪!慢慢研究!
Toad中的select level as id from dual connect by level<102;结果记录为1-->25
create table tab_a as
2 select level as id from dual connect by level<150;Table createdSQL> select count(*) from tab_a; COUNT(*)
----------
149SQL>
在CMD模式下SQLPLUS里, 只显示一条:SQL> select level as id from dual connect by level < 102; ID
----------
1
都没有问题
oracle考虑到这样的情况,就定义显示100之内,没啥为什么!就是个常量了。
SQL> SELECT COUNT(*) FROM dual CONNECT BY ROWNUM < 151
2 /
SELECT COUNT(*) FROM dual CONNECT BY ROWNUM < 151
*
ERROR 位于第 1 行:
ORA-01436: 用户数据中的 CONNECT BY 循环