오라클에서 copy_t 테이블 및 데이터 입력 쿼리문

--// 오라클에서 copy_t 테이블 및 데이터 입력 쿼리문
create table copy_t(no number, no2 varchar2(2)) nologging;

insert into copy_t values (1 ,'01');
insert into copy_t values (2 ,'02');
insert into copy_t values (3 ,'03');
insert into copy_t values (4 ,'04');
insert into copy_t values (5 ,'05');
insert into copy_t values (6 ,'06');
insert into copy_t values (7 ,'07');
insert into copy_t values (8 ,'08');
insert into copy_t values (9 ,'09');
insert into copy_t values (10 ,'10');
insert into copy_t values (11 ,'11');
insert into copy_t values (12 ,'12');
insert into copy_t values (13 ,'13');
insert into copy_t values (14 ,'14');
insert into copy_t values (15 ,'15');
insert into copy_t values (16 ,'16');
insert into copy_t values (17 ,'17');
insert into copy_t values (18 ,'18');
insert into copy_t values (19 ,'19');
insert into copy_t values (20 ,'20');
insert into copy_t values (21 ,'21');
insert into copy_t values (22 ,'22');
insert into copy_t values (23 ,'23');
insert into copy_t values (24 ,'24');
insert into copy_t values (25 ,'25');
insert into copy_t values (26 ,'26');
insert into copy_t values (27 ,'27');
insert into copy_t values (28 ,'28');
insert into copy_t values (29 ,'29');
insert into copy_t values (30 ,'30');
insert into copy_t values (31 ,'31');
insert into copy_t values (32 ,'32');
insert into copy_t values (33 ,'33');
insert into copy_t values (34 ,'34');
insert into copy_t values (35 ,'35');
insert into copy_t values (36 ,'36');
insert into copy_t values (37 ,'37');
insert into copy_t values (38 ,'38');
insert into copy_t values (39 ,'39');
insert into copy_t values (40 ,'40');
insert into copy_t values (41 ,'41');
insert into copy_t values (42 ,'42');
insert into copy_t values (43 ,'43');
insert into copy_t values (44 ,'44');
insert into copy_t values (45 ,'45');
insert into copy_t values (46 ,'46');
insert into copy_t values (47 ,'47');
insert into copy_t values (48 ,'48');
insert into copy_t values (49 ,'49');
insert into copy_t values (50 ,'50');
insert into copy_t values (51 ,'51');
insert into copy_t values (52 ,'52');
insert into copy_t values (53 ,'53');
insert into copy_t values (54 ,'54');
insert into copy_t values (55 ,'55');
insert into copy_t values (56 ,'56');
insert into copy_t values (57 ,'57');
insert into copy_t values (58 ,'58');
insert into copy_t values (59 ,'59');
insert into copy_t values (60 ,'60');
insert into copy_t values (61 ,'61');
insert into copy_t values (62 ,'62');
insert into copy_t values (63 ,'63');
insert into copy_t values (64 ,'64');
insert into copy_t values (65 ,'65');
insert into copy_t values (66 ,'66');
insert into copy_t values (67 ,'67');
insert into copy_t values (68 ,'68');
insert into copy_t values (69 ,'69');
insert into copy_t values (70 ,'70');
insert into copy_t values (71 ,'71');
insert into copy_t values (72 ,'72');
insert into copy_t values (73 ,'73');
insert into copy_t values (74 ,'74');
insert into copy_t values (75 ,'75');
insert into copy_t values (76 ,'76');
insert into copy_t values (77 ,'77');
insert into copy_t values (78 ,'78');
insert into copy_t values (79 ,'79');
insert into copy_t values (80 ,'80');
insert into copy_t values (81 ,'81');
insert into copy_t values (82 ,'82');
insert into copy_t values (83 ,'83');
insert into copy_t values (84 ,'84');
insert into copy_t values (85 ,'85');
insert into copy_t values (86 ,'86');
insert into copy_t values (87 ,'87');
insert into copy_t values (88 ,'88');
insert into copy_t values (89 ,'89');
insert into copy_t values (90 ,'90');
insert into copy_t values (91 ,'91');
insert into copy_t values (92 ,'92');
insert into copy_t values (93 ,'93');
insert into copy_t values (94 ,'94');
insert into copy_t values (95 ,'95');
insert into copy_t values (96 ,'96');
insert into copy_t values (97 ,'97');
insert into copy_t values (98 ,'98');
insert into copy_t values (99 ,'99');

commit;


=================================================================
copy_ymd 테이블 생성 및 데이터 입력 스크립트 ( 오라클용)

create table copy_ymd nologging
as
select to_char(sysdate+rownum, 'YYYYMMDD') ymd,
to_date(to_char(sysdate+rownum, 'YYYYMMDD'), 'YYYY-MM-DD') ymd_date
from tab a, tab b, tab c
where rownum < 100000
union all
select to_char(sysdate+rownum, 'YYYYMMDD') ymd,
to_date(to_char(sysdate+rownum, 'YYYYMMDD'), 'YYYY-MM-DD') ymd_date
from dual
union all
select to_char(sysdate-rownum, 'YYYYMMDD') ymd,
to_date(to_char(sysdate-rownum, 'YYYYMMDD'), 'YYYY-MM-DD') ymd_date
from tab a, tab b, tab c
where rownum < 100000
union all
select '00010101' ymd, to_date('00010101') ymd_date from dual
union all
select '99991231' ymd, to_date('99991231') ymd_date from dual;

COMMIT;
=================================================================
copy_yy 테이블 생성 및 데이터 입력 스크립트 ( 오라클용)

create table copy_yy nologging
as
select to_char(1900+rownum) yy from all_objects where rownum < 200


commit;



=================================================================
잘못된 날짜 데이터 찾기

SELECT A.EMPNO, A.ENAME, A.HIREDATE, B.YMD -- TO_DATE(HIREDATE, 'YYYY-MM-DD')
FROM TEST20 A, COPY_YMD B
WHERE A.HIREDATE = B.YMD(+)
AND B.YMD IS NULL;

혹은

SELECT * FROM (
SELECT A.EMPNO, A.ENAME, A.HIREDATE,
(SELECT B.YMD FROM COPY_YMD B WHERE A.HIREDATE = B.YMD ) YMD
FROM TEST20 A
)
WHERE YMD IS NULL;

( TEST20 테이블의 HIREDATE는 VARCHAR2(8) 로 구성되어 있으며, 중간중간 잘못된 데이터가 입력되어 있음)