1. 隨機數包
SELECT DBMS_RANDOM.VALUE
FROM DUAL.

2. 在[0..100]範圍內取隨機數
SELECT TRUNC (DBMS_RANDOM.VALUE (0, 100))
FROM DUAL.

3. 大於字元‘A’的10個字元隨機字串
SELECT DBMS_RANDOM.STRING (’A’, 10)
FROM DUAL.

4. 單個小寫隨機字元
SELECT CHR (ROUND (DBMS_RANDOM.VALUE (97, 122)))
FROM DUAL.

5. 在過去10天內取隨機日期
SELECT TO_DATE (TRUNC (DBMS_RANDOM.VALUE (TO_NUMBER (TO_CHAR (SYSDATE - 10,
’J’
)
),
TO_NUMBER (TO_CHAR (SYSDATE, ’J’))
)
),
’J’
)
FROM DUAL.

6. 在過去30分鐘內取隨機時間
SELECT (SYSDATE - 1 / 24 / 60 * 30)+DBMS_RANDOM.VALUE (1, 1800) / 3600 / 24
FROM DUAL

---匿名過程

declare

i number := 1;

j number := 1;

--count number := 1002275000;

v_pay_order_base_id number;

v_notify_times varchar2(20);

v_notify_type varchar2(20);

time timestamp(6);

begin

for i in 1..100000

loop

for j in 1..5000

loop

select TRUNC(DBMS_RANDOM.VALUE (0, 1000)) into v_pay_order_base_id from dual;

select TRUNC(DBMS_RANDOM.VALUE (0, 9)) into v_notify_times from dual;

select '0'||TRUNC(DBMS_RANDOM.VALUE (1, 4)) into v_notify_type from dual;

SELECT (SYSDATE - 1)+DBMS_RANDOM.VALUE (1, 1800) / 3600 / 24 into time FROM DUAL;

-- count := count +1;

insert into ET_PAY_ORDER_NOTIFY_mahanso values(mahanso.nextval,v_pay_order_base_id,v_notify_times,v_notify_type,time,time,'');

end loop;

commit;

end loop;

end;

(轉)Oracle的隨機數、隨機日期和時間、隨機字串及造資料匿名過程

相關文章