--// 오라클에서 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) 로 구성되어 있으며, 중간중간 잘못된 데이터가 입력되어 있음)
