141. (Oracle/오라클) 엑셀 (excel) 파일 더미 Dummy 데이터 첨부 - 테이블 생성 코드 추가

---

[개발 환경 설정]

​

개발 툴 : Toad

개발 언어 : Oracle

---

[TEST_USER 테이블 생성 및 삭제 소스코드]

/*[테이블 생성]*/
CREATE TABLE TEST_USER
(
  T_IDX  VARCHAR2(5 BYTE) NOT NULL,
  T_NAME  VARCHAR2(20 BYTE) NOT NULL,
  T_SEX  VARCHAR2(20 BYTE) NOT NULL,
  T_AGE  VARCHAR2(20 BYTE) NOT NULL,
  T_DEPT  VARCHAR2(50 BYTE) NOT NULL,
  T_PHONE  VARCHAR2(50 BYTE) NOT NULL,
  T_ADDR  VARCHAR2(50 BYTE) NOT NULL,
  T_RANK  VARCHAR2(50 BYTE) NOT NULL,
  T_MARRI  VARCHAR2(10 BYTE) NOT NULL,
  T_FAMILLY  VARCHAR2(10 BYTE) NOT NULL,
  T_HOBBY  VARCHAR2(50 BYTE)
);

/*[테이블 삭제]*/
DROP TABLE TEST_USER CASCADE CONSTRAINTS;

/*[insert 문 쿼리]*/
insert into test_user values ('1', '홍길동', 'M', '35', '조선', '010-1234-5678', 'A1-1', '사원', 'O', '2', '축구');
insert into test_user values ('2', '이순신', 'M', '25', '조선', '010-1234-5679', 'A1-2', '대리', 'X', '3', '농구');
insert into test_user values ('3', '장보고', 'M', '36', '신라', '010-1234-5680', 'A1-3', '주임', 'O', '4', '탁구');
insert into test_user values ('4', '이성계', 'M', '36', '조선', '010-1234-5681', 'A1-4', '과장', 'O', '5', '노래');
insert into test_user values ('5', '왕건', 'M', '78', '고려', '010-1234-5682', 'A1-5', '부장', 'O', '6', '독서');
insert into test_user values ('6', '정몽주', 'M', '54', '고려', '010-1234-5683', 'A1-6', '이사', 'X', '5', '');
insert into test_user values ('7', '의자왕', 'M', '21', '백제', '010-1234-5684', 'A1-7', '사장', 'X', '4', '');
insert into test_user values ('8', '선덕여왕', 'G', '23', '조선', '010-1234-5685', 'A1-8', '이사', 'X', '3', '');
insert into test_user values ('9', '이방원', 'M', '25', '조선', '010-1234-5686', 'A1-9', '부장', 'X', '2', '');
insert into test_user values ('10', '이산', 'M', '38', '조선', '010-1234-5687', 'A1-10', '과장', 'X', '1', '');
insert into test_user values ('11', '김유신', 'M', '25', '신라', '010-1234-5688', 'A1-11', '주임', 'X', '2', '');
insert into test_user values ('12', '진덕여왕', 'G', '28', '신라', '010-1234-5689', 'A1-12', '대리', 'O', '3', '');
insert into test_user values ('13', '천명공주', 'G', '18', '신라', '010-1234-5690', 'A1-13', '사원', 'O', '4', '');
insert into test_user values ('14', '온조', 'M', '65', '백제', '010-1234-5691', 'A1-14', '대리', 'O', '5', '독서');
insert into test_user values ('15', '계백', 'M', '45', '백제', '010-1234-5692', 'A1-15', '주임', 'O', '6', '영화');
insert into test_user values ('16', '진흥왕', 'M', '40', '신라', '010-1234-5693', 'A1-16', '과장', 'O', '5', '축구');
insert into test_user values ('17', '미실', 'G', '43', '신라', '010-1234-5694', 'A1-17', '부장', 'X', '4', '농구');
insert into test_user values ('18', '견훤', 'M', '56', '후백제', '010-1234-5695', 'A1-18', '이사', 'X', '3', '축구');
insert into test_user values ('19', '궁예', 'M', '55', '후고구려', '010-1234-5696', 'A1-19', '사장', 'X', '2', '농구');
insert into test_user values ('20', '장희빈', 'G', '45', '조선', '010-1234-5697', 'A1-20', '이사', 'X', '1', '독서');
insert into test_user values ('21', '평강공주', 'G', '37', '고구려', '010-1234-5698', 'A1-21', '부장', 'X', '2', '영화');
insert into test_user values ('22', '온달', 'M', '62', '고구려', '010-1234-5699', 'A1-22', '과장', 'X', '3', '');
insert into test_user values ('23', '광개토대왕', 'M', '41', '고구려', '010-1234-5700', 'A1-23', '대리', 'X', '4', '');
insert into test_user values ('24', '장수왕', 'M', '31', '고구려', '010-1234-5701', 'A1-24', '주임', 'X', '5', '');
insert into test_user values ('25', '논개', 'G', '35', '조선', '010-1234-5702', 'A1-25', '사원', 'X', '6', '');
insert into test_user values ('26', '장녹수', 'G', '39', '조선', '010-1234-5703', 'A1-26', '주임', 'X', '7', '');
insert into test_user values ('27', '강비', 'M', '54', '후고구려', '010-1234-5704', 'A1-27', '대리', 'X', '8', '노래');
insert into test_user values ('28', '신검', 'M', '67', '후백제', '010-1234-5705', 'A1-28', '과장', 'X', '8', '노래');
insert into test_user values ('29', '최우', 'M', '84', '고려', '010-1234-5706', 'A1-29', '부장', 'X', '7', '노래');
insert into test_user values ('30', '최충헌', 'M', '73', '고려', '010-1234-5707', 'A1-30', '이사', 'X', '6', '탁구');
insert into test_user values ('31', '경대승', 'M', '45', '고려', '010-1234-5708', 'A1-31', '사장', 'O', '5', '축구');
insert into test_user values ('32', '주몽', 'M', '18', '고구려', '010-1234-5709', 'A1-32', '이사', 'O', '4', '독서');
insert into test_user values ('33', '유화부인', 'G', '25', '부여', '010-1234-5710', 'A1-33', '부장', 'O', '3', '독서');
insert into test_user values ('34', '소서노', 'G', '16', '부여', '010-1234-5711', 'A1-34', '과장', 'O', '2', '영화');
insert into test_user values ('35', '대소', 'M', '23', '부여', '010-1234-5712', 'A1-35', '대리', 'O', '1', '');
insert into test_user values ('36', '권율', 'M', '38', '조선', '010-1234-5713', 'A1-36', '주임', 'O', '2', '');
insert into test_user values ('37', '강감찬', 'M', '24', '고려', '010-1234-5714', 'A1-37', '사원', 'X', '3', '');
insert into test_user values ('38', '성왕', 'M', '36', '백제', '010-1234-5715', 'A1-38', '주임', 'X', '4', '');
insert into test_user values ('39', '소수림왕', 'M', '19', '고구려', '010-1234-5716', 'A1-39', '대리', 'X', '5', '');
insert into test_user values ('40', '공민왕', 'M', '28', '고려', '010-1234-5717', 'A1-40', '과장', 'X', '6', '');
insert into test_user values ('41', '김춘추', 'M', '26', '신라', '010-1234-5718', 'A1-41', '부장', 'X', '5', '');
insert into test_user values ('42', '근초고왕', 'M', '42', '백제', '010-1234-5719', 'A1-42', '이사', 'X', '4', '');
insert into test_user values ('43', '침류왕', 'M', '32', '백제', '010-1234-5720', 'A1-43', '사장', 'X', '3', '');
insert into test_user values ('44', '소지왕', 'M', '47', '신라', '010-1234-5721', 'A1-44', '이사', 'X', '2', '');
insert into test_user values ('45', '사도태후', 'G', '63', '신라', '010-1234-5722', 'A1-45', '부장', 'X', '1', '');
insert into test_user values ('46', '지소부인', 'G', '13', '신라', '010-1234-5723', 'A1-46', '과장', 'X', '2', '');
insert into test_user values ('47', '정혜공주', 'G', '17', '발해', '010-1234-5724', 'A1-47', '대리', 'X', '1', '');
insert into test_user values ('48', '정화공주', 'G', '15', '발해', '010-1234-5725', 'A1-48', '주임', 'X', '2', '');
insert into test_user values ('49', '정효공주', 'G', '18', '발해', '010-1234-5726', 'A1-49', '사원', 'X', '3', '');
insert into test_user values ('50', '신혜왕후', 'G', '6', '고려', '010-1234-5727', 'A1-50', '사원', 'X', '3', '');
insert into test_user values ('51', '정순왕후', 'G', '8', '고려', '010-1234-5728', 'A1-51', '사원', 'X', '3', '');
insert into test_user values ('52', '신의왕후', 'G', '9', '고려', '010-1234-5729', 'A1-52', '사원', 'X', '3', '');
insert into test_user values ('53', '민회빈', 'G', '12', '조선', '010-1234-5730', 'A1-53', '대리', 'X', '3', '');
insert into test_user values ('54', '신사임당', 'G', '67', '조선', '010-1234-5731', 'A1-54', '과장', 'X', '3', '영화');
insert into test_user values ('55', '퇴계이왕', 'M', '55', '조선', '010-1234-5732', 'A1-55', '과장', 'X', '1', '영화');
insert into test_user values ('56', '율곡이이', 'M', '54', '조선', '010-1234-5733', 'A1-56', '대리', 'X', '1', '독서');
insert into test_user values ('57', '어우동', 'M', '7', '조선', '010-1234-5734', 'A1-57', '대리', 'X', '1', '독서');
insert into test_user values ('58', '철인왕후', 'G', '6', '조선', '010-1234-5735', 'A1-58', '주임', 'X', '1', '야구');
insert into test_user values ('59', '허난설헌', 'G', '5', '조선', '010-1234-5736', 'A1-59', '사원', 'X', '2', '야구');
insert into test_user values ('60', '정의공주', 'G', '4', '조선', '010-1234-5737', 'A1-60', '사원', 'X', '2', '노래');

​

​

​

[TEST_DEPT 테이블 생성 및 삭제 소스코드]

/*[테이블 생성]*/
CREATE TABLE TEST_DEPT
(
  T_DEPT_IDX  VARCHAR2(5 BYTE) NOT NULL,
  T_DEPT_NAME  VARCHAR2(20 BYTE) NOT NULL,
  T_DEPT_PHONE  VARCHAR2(50 BYTE) NOT NULL,
  T_DEPT_PLACE  VARCHAR2(50 BYTE) NOT NULL
);

/*[테이블 삭제]*/
DROP TABLE TEST_DEPT CASCADE CONSTRAINTS;

/*[insert 문 쿼리]*/
insert into test_dept values ('1', '부여', '070-123-4567', 'P-1');
insert into test_dept values ('2', '고구려', '070-123-4568', 'P-2');
insert into test_dept values ('3', '백제', '070-123-4569', 'P-3');
insert into test_dept values ('4', '신라', '070-123-4570', 'P-4');
insert into test_dept values ('5', '후고구려', '070-123-4571', 'P-5');
insert into test_dept values ('6', '후백제', '070-123-4572', 'P-6');
insert into test_dept values ('7', '고려', '070-123-4573', 'P-7');
insert into test_dept values ('8', '조선', '070-123-4574', 'P-8');
insert into test_dept values ('9', '대한제국', '070-123-4575', 'P-9');
insert into test_dept values ('10', '청동기', '070-123-4576', 'P-10');
insert into test_dept values ('11', '발해', '070-123-4577', 'P-11');
insert into test_dept values ('12', '대한민국', '070-123-4578', 'P-12');
insert into test_dept values ('13', '신석기', '070-123-4579', 'P-13');
insert into test_dept values ('14', '구석기', '070-123-4580', 'P-14');

​

​

​

[TEST_RANK 테이블 생성 및 삭제 소스코드]

/*[테이블 생성]*/
CREATE TABLE TEST_RANK
(
  T_RANK_IDX  VARCHAR2(5 BYTE) NOT NULL,
  T_RANK_NAME  VARCHAR2(20 BYTE) NOT NULL,
  T_RANK_MONEY  VARCHAR2(50 BYTE) NOT NULL
);

/*[테이블 삭제]*/
DROP TABLE TEST_RANK CASCADE CONSTRAINTS;

/*[insert 문 쿼리]*/
insert into test_rank values ('1', '사장', '8000');
insert into test_rank values ('2', '이사', '6000');
insert into test_rank values ('3', '부장', '4000');
insert into test_rank values ('4', '과장', '3500');
insert into test_rank values ('5', '대리', '3000');
insert into test_rank values ('6', '주임', '2500');
insert into test_rank values ('7', '사원', '2000');
insert into test_rank values ('8', '인턴', '1500');

---

[결과 출력]

​

​

​

---

[엑셀 파일 첨부]

​

TEST_USER(DB데이터).xlsx

0.01MB

TEST_DEPT(DB데이터).xlsx

0.01MB

TEST_RANK(DB데이터).xlsx

0.01MB

---