v_exists_table
number;
begin
select count(
*)
into v_exists_table
from all_tables
where table_name
= ‘NUMBERS‘;
if v_exists_table
<> 0 then
execute immediate
‘drop table NUMBERS‘;
end if;
end;
/
create table Numbers(
ID NUMBER
CONSTRAINT cons_Numbers_ID_uni_nn
NOT NULL UNIQUE,
GRP_FACTOR NUMBER
CONSTRAINT cons_Numbers_ID_nn
NOT NULL
);
/
INSERT INTO Numbers(ID, GRP_FACTOR)
with tmp
as (
select col1
from (
select 1 as col1
from all_objects
order by OBJECT_ID) a
where ROWNUM
<= 12
order by ROWNUM
ASC),
tmp2 as (
select col1
from (
select 1 as col1
from all_objects, tmp
order by OBJECT_ID) a
where ROWNUM
<= 100000
order by ROWNUM
ASC)
select rn, grp_factor
from (
select ROW_NUMBER()
OVER(
ORDER BY sys_guid())
AS rn, ntile(
10)
over (
order by sys_guid())
as grp_factor
from tmp2) t
order by rn;
又是一个蛋疼的区别。Oracle下把WITH AS语句和INSERT INTO结合起来需要把INSERT INTO放在WITH AS前面，而SQL SERVER下是放在WITH AS后面那条SELECT语句的前面。
Oracle ->> 生成测试数据
标签：
本文系统来源：http://www.cnblogs.com/jenrrychen/p/4605699.html
【说明】：
本文章由站长整理发布，文章内容不代表本站观点，如文中有侵权行为，请与本站客服联系(QQ:254677821)！