-- 테이블 생성
____CREATE TABLE____ LEEOK.DATA_TYPE (
  DT_SEQ  VARCHAR2(2) NOT NULL,
  DT_CHAR  CHAR(100) NULL,
  DT_VARCHAR2 VARCHAR2(100) NULL,
  DT_NUMBER_1 NUMBER NULL,
  DT_NUMBER_2 NUMBER(9, 2) NULL,
  DT_DATE  DATE  NULL,
  DT_TIMESTAMP TIMESTAMP(6) NULL,
  DT_LONG  LONG  NULL,
  DT_BLOB  BLOB  NULL,
  DT_CLOB  CLOB  NULL
);
ALTER TABLE DATA_TYPE ADD (
  CONSTRAINT DATA_TYPE_PK PRIMARY KEY(DT_SEQ)
);

■예 5_1_1  ||  CHAR의 길이를 체크
SELECT DT_CHAR, RTRIM(DT_CHAR) FROM DATA_TYPE
UNION  ALL
SELECT TO_CHAR(LENGTHB(DT_CHAR)), TO_CHAR(LENGTHB(RTRIM(DT_CHAR)))
FROM DATA_TYPE;

■예 5_1_2  ||  VARCHAR2의 길이를 체크
SELECT DT_VARCHAR2, TRIM(DT_VARCHAR2) FROM DATA_TYPE
UNION  ALL
SELECT TO_CHAR(LENGTHB(DT_VARCHAR2)), TO_CHAR(LENGTHB(TRIM(DT_VARCHAR2))) FROM DATA_TYPE;

■예 5_1_3  ||  같은 타입(CHAR)의 조인
SELECT T1.DT_SEQ, T1.DT_CHAR, T1.DT_VARCHAR2, T1.DT_NUMBER_1,
T1.DT_NUMBER_2, T1.DT_DATE
FROM DATA_TYPE T1,
 DATA_TYPE T2
WHERE T1.DT_SEQ = T2.DT_SEQ
AND  T1.DT_CHAR = T2.DT_CHAR;

■예 5_1_4  ||  다른 타입(CHAR, VARCHAR)의 조인
SELECT T1.DT_SEQ, T1.DT_CHAR, T1.DT_VARCHAR2, T1.DT_NUMBER_1,
T1.DT_NUMBER_2, T1.DT_DATE
FROM DATA_TYPE T1,
 DATA_TYPE T2
WHERE T1.DT_SEQ = T2.DT_SEQ
AND T1.DT_CHAR = T2.DT_VARCHAR2;

■예 5_1_5  ||  다른 타입(CHAR, VARCHAR)의 조인
SELECT T1.DT_SEQ, T1.DT_CHAR, T1.DT_VARCHAR2, T1.DT_NUMBER_1,
T1.DT_NUMBER_2, T1.DT_DATE
FROM DATA_TYPE T1,
 DATA_TYPE T2
WHERE T1.DT_SEQ = T2.DT_SEQ
AND TRIM(T1.DT_CHAR) = T2.DT_VARCHAR2;

■예 5_1_6  ||  NUMBER의 길이 체크
UPDATE DATA_TYPE SET DT_NUMBER_1=123456789012345678901234567890.12345678901234567890123456789, DT_NUMBER_2=1234567.99
WHERE DT_SEQ = '01';

UPDATE DATA_TYPE SET DT_NUMBER_1=
-123456789012345678901234567890.12345678901234567890123456789,
DT_NUMBER_2=-1234567.99
WHERE DT_SEQ = '02';

SELECT DT_SEQ, DT_CHAR, DT_VARCHAR2, DT_NUMBER_1,
VSIZE(DT_NUMBER_1)||' BYTE' AS 크기,  DT_NUMBER_2
FROM DATA_TYPE;

■예 5_1_7  ||  DATE 타입 조회
SELECT VSIZE(DT_DATE)||' BYTE' AS 크기,
       TO_CHAR(DT_DATE,'YYYY/MM/DD') AS 년월일,
       TO_CHAR(DT_DATE,'YYYY/MM/DD HH24:MI:SS') AS 년월일시분초 ,
       DT_DATE,
       SYSDATE-7
FROM DATA_TYPE;

■예 5_1_8  ||  LAST_DAY 함수 사용
SELECT LAST_DAY(SYSDATE) FROM DUAL;

■예 5_1_9  ||   TO_CHAR 함수 사용
SELECT TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS') FROM DUAL;

■예 5_1_10  ||   TO_DATE 함수 사용
SELECT TO_DATE('20100503 09:59:25','yyyymmdd hh24:mi:ss') FROM DUAL;

■예 5_1_11  ||  ADD_MONTHS 함수 사용
SELECT ADD_MONTHS(SYSDATE,1) FROM DUAL;

■예 5_1_13  ||  MONTHS_BETWEEN 함수 사용
SELECT MONTHS_BETWEEN(SYSDATE,SYSDATE-30) FROM DUAL;

■예 5_1_14  ||  NEXT_DAY 함수 사용
SELECT NEXT_DAY(SYSDATE, 7) FROM DUAL;

■예 5_1_15  ||  TIMESTAMP 타입 조회
SELECT T1.DT_SEQ, TO_CHAR(T1.DT_TIMESTAMP,'YYYYMMDD HH24:MI:SS:FF') AS EX1,
 TO_CHAR(T1.DT_TIMESTAMP, 'YYYYMMDDHH24MISSFF3') AS EX2,
 VSIZE(T1.DT_TIMESTAMP)||' BYTE' AS BYTE, T1.DT_DATE
FROM DATA_TYPE T1;