Oracle DBMS_RANDOM을 이용한 테스트 데이터 만들기

이런 회원 엔티티가 있다.

회원엔티티

여기에 테스트 데이터를 채워 넣어 보자.

DBMS_RANDOM 함수를 이용해서.

 

CREATE TABLE T_MEMBER AS
WITH T1 AS (
    SELECT
        ROWNUM AS RID1
        , REGEXP_SUBSTR(STR, '[^,]+', 1, LEVEL) AS NAME1
      FROM (
        SELECT '김,이,박,고,안,강,조,최,차,손,하,정,허,성,하,정,진,송,마' AS STR
          FROM DUAL
      )
    CONNECT BY LEVEL <=LENGTH(REGEXP_REPLACE(STR, '[^,]+')) + 1
),
T2 AS (
    SELECT
        ROWNUM AS RID2
        , REGEXP_SUBSTR(STR, '[^,]+', 1, LEVEL) AS NAME2
      FROM (
        SELECT
            '영희,수철,경희,정환,환웅,경율,인호,강희,희철,동원,병수,수휘,옥희,' ||
            '경환,가인,화수,시후,지완,동렬,동원,혜교,혜수,태지,하진,진태,철수' AS STR
          FROM DUAL
      )
    CONNECT BY LEVEL <=LENGTH(REGEXP_REPLACE(STR, '[^,]+')) + 1
)
SELECT LPAD(ROWNUM, 8,'0') AS USER_ID
        , (T1.NAME1 || T2.NAME2) AS NAME 
        , ROUND(dbms_random.value(2019,2022)) || TO_CHAR(  ROUND(dbms_random.value(1,12)) , 'FM09')  || TO_CHAR(  ROUND(dbms_random.value(1,28)) , 'FM09') AS JOIN_DT
        , (CASE WHEN MOD(ROUND(DBMS_RANDOM.VALUE(1,10)),3) = 0 THEN 'A'
            WHEN MOD(ROUND(DBMS_RANDOM.VALUE(1,10)),3) = 1 THEN 'B'
            ELSE 'C'
            END) || LPAD(ROUND(DBMS_RANDOM.VALUE(1,10)), 4, '0') AS DEPT_ID
        , (CASE WHEN MOD(ROUND(DBMS_RANDOM.VALUE(1,10)),3) = 0 THEN 'N'
            ELSE 'Y'
            END) AS MARRY_YN
  FROM (
  	SELECT   ROUND(DBMS_RANDOM.VALUE(1,19)) AS KEY1
    	   , ROUND(DBMS_RANDOM.VALUE(1,26)) AS KEY2
      FROM DUAL A
    CONNECT BY LEVEL <= 20000
  ) M, T1, T2
 WHERE M.KEY1 = T1.RID1
   AND M.KEY2 = T2.RID2;
   
 ALTER TABLE T_MEMBER ADD CONSTRAINT T_MEMBER_PK PRIMARY KEY (USER_ID);
 COMMIT;

 

결과

회원테이블

 

그리고 위에서 만들어진 회원의 부서ID 기반으로 부서 데이터까지 만들자.

CREATE TABLE T_DEPT AS
SELECT  DEPT_ID
        ,'DEPT_' || DEPT_ID AS DEPT_NAME
        ,ROUND(dbms_random.value(2018,2021)) || TO_CHAR(  ROUND(dbms_random.value(1,12)) , 'FM09')  || TO_CHAR(  ROUND(dbms_random.value(1,28)) , 'FM09') AS CREATE_DT
        , 'B' || LPAD(ROUND(DBMS_RANDOM.VALUE(1,10)), 4, '0') AS BRANCH_CD
  FROM T_MEMBER
GROUP BY DEPT_ID;
ALTER TABLE T_DEPT ADD CONSTRAINT T_DEPT_PK PRIMARY KEY(DEPT_ID); 
COMMIT;

 

부서테이블