ORACLE 造数脚本

SELECT DBMS_RANDOM.VALUE() FROM DUAL;

SELECT DBMS_RANDOM.VALUE(20,30) FROM DUAL;

SELECT DBMS_RANDOM.NORMAL FROM DUAL;

SELECT DBMS_RANDOM.RANDOM() FROM DUAL;

CREATE TABLE ORDER_TABLE

(

ORDER_ID NUMBER

,USER_CODE VARCHAR2(256)

,GOODS_NAME VARCHAR2(256)

,ORDER_DATE DATE

,CITY VARCHAR2(256)

,ORDER_NUM NUMBER

);

COMMENT ON TABLE ORDER_TABLE IS '订单表 ';

COMMENT ON COLUMN ORDER_TABLE.ORDER_ID IS '订单ID';

COMMENT ON COLUMN ORDER_TABLE.USER_CODE IS '下单用户ID';

COMMENT ON COLUMN ORDER_TABLE.GOODS_NAME IS '商品名称';

COMMENT ON COLUMN ORDER_TABLE.ORDER_DATE IS '下单日期';

COMMENT ON COLUMN ORDER_TABLE.CITY IS '下单城市';

COMMENT ON COLUMN ORDER_TABLE.ORDER_NUM IS '订单数量';

CREATE SEQUENCE SE_ORDER_ID --序列名称

MINVALUE 1001 --最小值

;

CREATE OR REPLACE PROCEDURE SP_CREATE_DATA IS

BEGIN

FOR X IN 1..1000

LOOP

INSERT INTO ORDER_TABLE

VALUES ( se_order_id.nextval --引用自增序列

, DBMS_RANDOM.STRING('x', 8) --获取8位随机数字加字母

, CASE TRUNC(DBMS_RANDOM.VALUE(1, 6)) --随机选取5个枚举值

when 1 then '零食大礼包A'

when 2 then '零食大礼包B'

when 3 then '零食大礼包C'

when 4 then '零食大礼包D'

when 5 then '零食大礼包E'

END

, TO_DATE(TO_CHAR(TO_DATE('20210101', 'yyyymmdd'), 'J') -

TRUNC(DBMS_RANDOM.VALUE(1, TO_DATE('20210101', 'yyyymmdd') -

ADD_MONTHS(TO_DATE('20210101', 'yyyymmdd'), -12))

- 1), 'J')

-- to_char(sysdate, 'J') 自公元前 4712 年1月1日到指定日期的总天数

, CASE TRUNC(DBMS_RANDOM.VALUE(1, 6)) --随机选取5个枚举值

when 1 then '北京'

when 2 then '上海'

when 3 then '广州'

when 4 then '深圳'

when 5 then '杭州'

END

, TRUNC(DBMS_RANDOM.VALUE(1, 11)) --获取1-10的随机整数

);

END LOOP;

COMMIT;

END SP_CREATE_DATA;

CREATE OR REPLACE PROCEDURE SP_500 IS

BEGIN

FOR X IN 1..500 LOOP

SP_CREATE_DATA;

END LOOP;

END;

BEGIN

SP_500;

end;

SELECT * FROM ORDER_TABLE;

SELECT COUNT(*) FROM ORDER_TABLE;