Oracle ->> 產生測試資料

標籤：

declare 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) awhere ROWNUM <= 12order by ROWNUM ASC),tmp2 as (select col1 from (select 1 as col1 from all_objects, tmp order by OBJECT_ID) awhere ROWNUM <= 100000order 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_factorfrom tmp2) t order by rn;

又是一個蛋疼的區別。Oracle下把WITH AS語句和INSERT INTO結合起來需要把INSERT INTO放在WITH AS前面，而SQL SERVER下是放在WITH AS後面那條SELECT語句的前面。

 

Oracle ->> 產生測試資料