21.02.09 TIL (D+22 Oracle DDL, 자료형 , 표준 함수)

학습 내용

- 테이블 생성

CREATE TABLE 테이블명(
    컬럼명1 자료형,
    컬럼명2 자료형,
         :
         :
);

 

- 테이블 삭제

DROP TABLE 테이블명;

 

- 자료형 (Java vs DB)

    Java                DB
    
    String              VARCHAR2, CHAR, LONG
    int                 INTEGER, NUMBER
    double              NUMBER
    Date                DATE

 

---

- 문자열

 

CHAR

CREATE TABLE TB_CHAR(
    COL1 CHAR(10 BYTE),
    COL2 CHAR(10 CHAR),  -- 11111 11113 -> 12BYTE(한글 1개쓰면)
    COL3 CHAR(10)
);

 

INSERT INTO 테이블명(컬럼명, 컬럼명 ... )
VALUES( 값, 값 ... )

INSERT INTO TB_CHAR (COL1, COL2, COL3)
VALUES ('ABC', 'ABC', 'ABC');   -- 영문자 1문자의 크기 1 BYTE

SELECT * FROM TB_CHAR;

INSERT INTO TB_CHAR (COL1, COL2, COL3)
VALUES ('가', '나', '다');     -- 한글의 한문자 크기 3 BYTE

INSERT INTO TB_CHAR (COL1, COL2, COL3)
VALUES ('가나', '가나', '가나');

INSERT INTO TB_CHAR (COL1, COL2, COL3)
VALUES ('가나다', '가나다', '가나다');

SELECT COL1, COL2, COL3, LENGTHB(COL1), LENGTHB(COL2), LENGTHB(COL3)
FROM TB_CHAR;

   CHAR : 10 BYTE  11111 11111
   가(3BYTE) : 12 BYTE  11111 11113
   가나(6BYTE) : 14 BYTE 11111 11133

 

VARCHAR2

 

CREATE TABLE TB_VARCHAR(
    COL1 VARCHAR2(10 BYTE),
    COL2 VARCHAR2(10 CHAR),
    COL3 VARCHAR2(10)
);

 

LONG
 최대 2GB까지 저장 가능
 TABLE당 1개의 컬럼만 사용가능

 

 

---

- 숫자

 

INTEGER 정수

CREATE TABLE TB_INTEGER(
    COL1 INTEGER,
    COL2 INTEGER
);

NUMBER 정수, 소수 둘다 가능

CREATE TABLE TB_NUMBER(
    COL1 NUMBER,
    COL2 NUMBER(5),     --정수 자리수
    COL3 NUMBER(5, 2),  --정수자리수, 소수점자리수
    COL4 NUMBER(*, 2)   -- 정수자리는 제한없다
);

---

DATE (날짜)
 연도, 월, 일, 시, 분, 초

CREATE TABLE TB_DATE(
    COL1 DATE,
    COL2 DATE
);

SYSDATE = 현재시간

INSERT INTO TB_DATE(COL1,COL2)
VALUES (SYSDATE, SYSDATE -1);  --  -1 = 하루 전날

INSERT INTO TB_DATE(COL1,COL2)
VALUES (SYSDATE, TO_DATE('2021-02-05 05:23:21', 'YYYY-MM-DD HH:MI:SS') );   
			-- TO_DATE('문자열', '형식')

---

- Standard Function (표준 함수)

- DUAL TABLE : 가상테이블 -> 결과 확인용 테이블

SELECT 'A' FROM DUAL;

 

- 문자 함수

 

1. CHR( N ) : ASCII 값을 문자로 변환

SELECT CHR(65) FROM DUAL; --결과 A

 

2. 문자열 합치기 ( || )

SELECT '내 점수는 ' || CHR(65) || '입니다' FROM DUAL;

 

3. LPAD( or RPAD) : 나머지를 빈칸(or 지정문자)으로 채운다.

SELECT LPAD('BBB', 10) FROM DUAL; -- 총 10칸, 왼쪽부터 빈칸 7 + BBB
SELECT RPAD('CCC', 10) FROM DUAL; -- 총 10칸, 오른쪽부터 빈칸 7 + CCC
SELECT LPAD('123', 10, '0') FROM DUAL; -- 총 10칸, 왼쪽부터 '0' 7 + 123

 

4. INSTR    == indexOf('a') --> "abcde" - > 0   -- 오라클은 1번지부터 시작한다.

SELECT INSTR('123ABC','A') FROM DUAL;  -- 값 : 4
SELECT INSTR('123ABC456DEFABCABC', 'A', 7, 2) FROM DUAL; -- 7번째 이후로 나오는 두번째 A의 위치 : 값 16
SELECT INSTR('123ABC','Y') FROM DUAL;  -- 문자가 없으면 0이 나온다.

 

5. REPLACE : 문자열 치환

SELECT REPLACE('AAAAABCD', 'A') FROM DUAL;  -- 바꾸고 싶은 문자를 지정하지 않으면 'A'가 없어진다.
SELECT REPLACE('AAAAABCD', 'A', 'a') FROM DUAL; -- 'A'가 'a'로 바뀐다.
SELECT REPLACE('AAAAABCD', 'AA', 'a') FROM DUAL; -- 문자열도 가능

 

6. TRANSLATE : 문자 치환

SELECT TRANSLATE('AAAAABCD', 'A', 'a') FROM DUAL;
SELECT TRANSLATE('AAAAABCD', 'AA', 'a') FROM DUAL; -- 문자만 치환되기 때문에 'A'한글자만 인식한다.
-- 둘다 결과는 aaaaaBCD로 같다.

 

7. SUBSTR : 특정 위치 문자열 가져오기

SELECT SUBSTR('ABCDE', 3) FROM DUAL;    --3번지부터 끝까지 
SELECT SUBSTR('ABCDE', 3, 2) FROM DUAL; -- 3번지부터 2문자
SELECT SUBSTR('2021/02/09', 6, 2) FROM DUAL;  -- 월만 뽑아내기

---

- 숫자 함수

 

1. CEIL : 올림

SELECT CEIL(13.1) FROM DUAL; --값 : 14

 

2. FLOOR : 내림

SELECT FLOOR(13.9) FROM DUAL; --값 : 13

 

3. MOD : 나머지값

SELECT MOD (7, 4) FROM DUAL; -- 값 : 3

 

4. POWER : 제곱

SELECT POWER(3,2) FROM DUAL; -- 3의 2승

 

5. ROUND : 반올림

SELECT ROUND(13.5) FROM DUAL; --값 : 14

 

6. SIGN : 부호 ( + = 1     0 = 0    - = -1)

SELECT SIGN(13.4) FROM DUAL; -- 값 : 1
SELECT SIGN(0) FROM DUAL;   -- 값 : 0
SELECT SIGN(-0.4) FROM DUAL; -- 값 : -1

 

7. TRUNC : 버림(소수)

SELECT TRUNC(123.456) FROM DUAL;    -- 값 : 123
SELECT TRUNC(123.456, 2) FROM DUAL; -- 값 : 123.45
SELECT TRUNC(123.456, -1) FROM DUAL; -- 값 : 120
SELECT TRUNC (17/6) FROM DUAL; -- 값 : 2

 

8. ASCII : 문자 -> ASCII

SELECT ASCII('A') FROM DUAL;    -- 값 : 65

---

 

- 변환 함수

 

1. TO_CHAR (DATE -> VARCHAR2 할때 제일 많이 사용)

SELECT SYSDATE FROM DUAL; -- 오늘 날짜를
SELECT TO_CHAR(SYSDATE) FROM DUAL; -- 문자열로 변환
SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD HH:MI:SS') FROM DUAL;
SELECT TO_CHAR(243556546, '$999,999,999') FROM DUAL; -- 이렇게도 활용 가능. 값 : $243,556,546

2. TO_DATE (VARCHAR2 -> DATE)

SELECT TO_DATE('20210208') FROM DUAL; -- 결과 : 21/02/08
SELECT TO_DATE('20210208', 'YYYYMMDD') FROM DUAL;

3. TO_NUMBER (VARCHAR2 -> NUMBER)    ==parseInt()

SELECT TO_NUMBER('123') + 45 FROM DUAL; -- 123이 숫자로 변환되기 때문에 연산이 된다.

4. LAST_DAY (달의 마지막 날짜)

SELECT LAST_DAY('21/02/08') FROM DUAL;
SELECT LAST_DAY(TO_DATE('210201', 'YYMMDD')) FROM DUAL; -- 원래대로 한다면 이런 식(값은 같다.)

5. MONTHS_BETWEEN (날짜와 날짜 사이의 개월 수)

SELECT MONTHS_BETWEEN(TO_DATE('211231', 'YYMMDD'), TO_DATE('210331', 'YYMMDD')) FROM DUAL;

 

6. ADD_MONTHS 월수 증가

SELECT ADD_MONTHS(TO_DATE('210331', 'YYMMDD'), -1 ) FROM DUAL; 
	-- 이전 달 (2월에는 31일이 존재하지 않으므로 28일이 출력된다)
SELECT ADD_MONTHS(TO_DATE('210331', 'YYMMDD'), 1 ) FROM DUAL; 
	-- 다음 달 (4월에는 31일이 존재하지 않으므로 30일이 출력된다)

 

---