Oracle ->> 生成测试数据
时间：2021-07-01 10:21:17
帮助过：3人阅读
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 ->> 生成测试数据
标签：
