Test Case:
＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋
CREATE TABLE TestCode
(
ID VARCHAR2(5),
VAL NUMBER(1)
);
select * from (
select rownum id, decode(sign(dbms_random.value-0.9),-1,0,1) val
from dual connect by rownum<=100
)
where val>0;
insert
o testcode select * from ( select rownum id, decode(sign(dbms_random.value-0.9),-1,0,1) val
from dual connect by rownum<=100
)
where val>0;
select * from testcode;
＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋
运行
下
偶这里
情况是: SQL> select * from (
2 select rownum id, decode(sign(dbms_random.value-0.9),-1,0,1) val
3 from dual connect by rownum<=100
4 )
5 where val>0;
ID VAL
---------- ----------
30 1
40 1
54 1
75 1
91 1
SQL> /
ID VAL
---------- ----------
1 1
3 1
5 1
13 1
22 1
24 1
29 1
37 1
38 1
54 1
60 1
ID VAL
---------- ----------
62 1
93 1
96 1
已选择14行
插入到表中
SQL> insert
o testcode select * from ( 2 select rownum id, decode(sign(dbms_random.value-0.9),-1,0,1) val
3 from dual connect by rownum<=100
4 )
5 where val>0;
已创建6行
查询
下看看 SQL> select * from testcode;
ID VAL
----- ----------
18 0
21 0
22 0
33 0
60 0
94 0
已选择6行
VAL值有问题
插入到表中数据VAL值应该都是1才对 似乎问题和
dbms_random有关 请看下面
测试例子 SQL> select * from (select rownum+100 id, decode(sign(12-rownum),-1,1,0) val fro
m dual connect by rownum<20) where val>0;
ID VAL
---------- ----------
113 1
114 1
115 1
116 1
117 1
118 1
119 1
已选择7行
SQL> insert
o testcode select * from (select rownum+100 id, decode(sign(12-r ownum),-1,1,0) val from dual connect by rownum<20) where val>0;
已创建7行
SQL> select * from testcode;
ID VAL
----- ----------
18 0
21 0
22 0
33 0
60 0
94 0
113 1
114 1
115 1
116 1
117 1
ID VAL
----- ----------
118 1
119 1
已选择13行
但使用CTAS
方式就不会出现这样问题
最新评论