05_SQL 주요 함수

[ SQL 주요 함수 목차 ]

1. DUAL 테이블과 SQL 함수 분류

2. 숫자 함수

    1) ABS 함수

    2) FLOOR 함수

    3) ROUND 함수

    4) TRUNC 함수

    5) MOD 함수

3. 문자 처리 함수

    1) UPPER 함수

    2) LOWER 함수

    3) INITCAP 함수

    4) LENGTH 함수

    5) LENGTHB 함수

4. 날짜 함수

5. 형 변환 함수

6. NULL을 다른 값으로 변환하는 NVL 함수

7. 선택을 위한 DECODE 함수

8. 조건에 따라 서로 다른 처리가 가능한 CASE 함수

 

 

 

[ 1 ] DUAL 테이블과 SQL 함수 분류

- DUAL 테이블은 DUMMY라는 단 하나의 컬럼에 X라는 단 하나의 로우만

  저장하고 있으나 이 값은 아무런 의미가 없다. 

- DUAL 테이블은 산술 연산의 결과를 한 줄로 얻기 위해서

  오라클에서 제공하는 테이블이다.

 

* DUAL 이라는 테이블이 기본적으로 존재한다.

  DUAL 테이블은 DUMMY라는 단 하나의 컬럼으로 되어 있고 X 라는 단 하나의 로우만

  저장하고 있다. --> 별로 의미 없는 값!

 

-- 5-1. DUAL 테이블 : 산술연산의 결과를 한 줄로 얻기 위해 사용한다.
SELECT * FROM DUAL;
SELECT 24*60 FROM EMP;
-- EMP 테이블은 14개의 로우를 가지고 있어서 14개의 24*60의 값이 출력된다.
SELECT 24*60 FROM DEPT;
-- DEPT 테이블은 5개의 로우를 가지고 있어서 5개의 24*60의 값이 출력된다.

SELECT 24*60 FROM DUAL;
-- 24*60 의 값을 하나만 얻으면 되니깐 이럴 때는 DUAL 테이블을 이용해서 값을 얻으면 좋다.

SELECT SYSDATE FROM DUAL;
-- SYSDATE 는 현재의 날짜를 출력해준다. (22/12/13) 출력

 

 

---

 

[ 2 ] 숫자 함수

 

1. 절댓값 구하는 ABS 함수

- ABS 함수는 절대값을 구한다.

  절대값은 방향은 없고 크기만 있는 것으로서

  주어진 데이터가 음수일 경우 양수로 표현한다. 

-- (1) ABS 함수 : 절댓값 구하기
SELECT -10, ABS(-10) FROM DUAL;

 

 

 

 

2. 소수점 아래를 버리는 FLOOR 함수

- FLOOR 함수는 소수점 아래를 버린다.

  34.5678를 FLOOR 함수에 적용하면 34가 구해진다.

-- (2) FLOOR 함수 : 소수점 버려서 실수를 정수로 만들기
SELECT 34.5678, FLOOR(34.5678) FROM DUAL;

--SELECT FLOOR(34.5678, 2), FLOOR(34.5678, -1), FLOOR(34.5678) FROM DUAL;
-- 위의 코드는 실행 안됨 (오류)

 

 

 

 

3. 특정 자릿수에서 반올림하는 ROUND 함수

- 34.5678를 반올림하면 35이다.

  이와 같이 반올림한 결과를 구하기 위한 함수로

  오라클에서는 ROUND가 제공된다.

-- (3) ROUND 함수 : 특정 자리수에서 소수점 반올림하기
SELECT 34.5678, ROUND(34.5678) FROM DUAL;
SELECT 34.5678, ROUND(34.5678, 2) FROM DUAL;
-- ROUND(34.5678, 2) : 반올림해서 소수점 2번째까지만 출력
SELECT 34.5678, ROUND(34.5678, -1) FROM DUAL;

 

 

 

 

 

 

 

 

4. 특정 자릿수에서 잘라내는 TRUNC 함수

- TRUNC 함수는 지정한 자리 수 이하를 버린 결과를 구해주는 함수이다.

-- (4) TRUNC 함수 : 특정 자리수 잘라내기
SELECT TRUNC(34.5678, 2), TRUNC(34.5678, -1), TRUNC(34.5678) FROM DUAL;

 

 

 

 

 

5. 나머지 구하는 MOD 함수

- MOD 함수는 나누기 연산을 한 후에 구한 몫이 아닌

  나머지를 결과로 되돌려주는 함수이다.

-- (5) MOD 함수 : 나머지 구하기
SELECT MOD(27,2), MOD(27,5), MOD(27,7) FROM DUAL;
-- MOD(27,2) : 27을 2로 나누어서 나오는 나머지 출력 -> 1
-- MOD(27,5) : 27을 5로 나누어서 나오는 나머지 출력 -> 2
-- MOD(27,7) : 27을 7로 나누어서 나오는 나머지 출력 -> 6

 

 

---

 

[ 3 ] 문자 처리 함수

 

1. UPPER 함수

- UPPER 함수는 입력한 문자값을 대문자로 변환하는 함수이다.

-- (1) UPPER 함수 : 모두 대문자로 변환
SELECT 'Welcome to Oracle', UPPER('Welcome to Oracle') FROM DUAL;

 

 

 

2. LOWER 함수

- LOWER 함수는 문자열을 모두 소문자로 변경한다.

-- (2) LOWER 함수 : 모두 소문자로 변환
SELECT 'Welcome to Oracle', LOWER('Welcome to Oracle') FROM DUAL;

 

 

 

3. INITCAP 함수

- INITCAP 함수는 문자열의 이니셜만 대문자로 변경한다.

-- (3) INITCAP 함수 : 문자열의 이니셜만 (즉, 각 문자열의 처음을) 대문자로 변환
SELECT 'WELCOME TO SOUTH KOREA', INITCAP('WELCOME TO SOUTH KOREA') FROM DUAL;

 

 

 

4. LENGTH 함수

- LENGTH 함수는 컬럼에 저장된 데이터 값이 몇 개의 문자로 구성되었는지

  길이를 알려주는 함수이다.

-- (4) LENGTH 함수 : 컬럼에 저장된 데이터 값이 몇개의 문자로 구성되어있는지 길이 알려주는 함수
--                   --> 사용할 일 거의 없다!!
--                   공백도 길이에 포함된다.
SELECT LENGTH('SOUTH KOREA'), LENGTH('대한민국') FROM DUAL;

 

 

 

5. LENGTHB 함수

- LENGTHB 함수는 바이트 수를 알려주는 함수이다.

  특히, 한글 1자는 3바이트를 차지한다. 그렇기 때문에 수행 결과를 보면

  한글 3자로 구성된 '오라클'의 LENGTHB 함수의 결과는 9가 된다. 

  ** 기본 세팅이 UTF-8 인지 아니면 다른 걸로 되어있는지에 따라

      한글 1자에 2바이트로 보는지, 3바이트로 보는지 달라진다!!

      여기서는 UTF-8로 세팅이 되어있어 한글 1자를 3바이트로 본다!

      --> 그래서 9가 출력됨

-- (5) LENGTHB 함수 : 바이트 수를 알려주는 함수
-- 한글 1자 = 3바이트
SELECT LENGTHB('Oracle'), LENGTHB('오라클') FROM DUAL;
-- LENGTHB('Oracle') : 6 출력  /  LENGTHB('오라클') : 9 출력
-- 한글 3자로 구성된 '오라클'의 LENGTHB 함수 결과는 9

SELECT LENGTHB('Oracle'), LENGTHB('현지는최고야') FROM DUAL;
-- LENGTHB('현지는최고야') : 18 출력
-- 가변형!! 글자의 수가 3개라도 바이트수가 9개인건 가변형이다!!
-- UTF-8 로 기본 세팅이 되어 있다.

 

 

 

6. SUBSTR, SUBSTRB 함수

- SUBSTR 과 SUBSTRB 함수는 대상 문자열이나 칼럼의 자료에서

  시작위치부터 선택 개수만큼의 문자를 추출한다.

  SUBSTRB 함수도 같은 형식이지만 명시된 개수만큼의 문자가 아닌

  바이트 수를 잘라낸다는 점에서만 차이가 있다.

 

-- (6) SUBSTR, SUBSTRB 함수 : 중요!
-- SUBSTR 함수 : 대상 문자열이나 칼럼의 자료에서 시작위치부터 선택 개수만큼의 문자를 추출
-- SUBSTRB 함수 : 대상 문자열이나 칼럼의 자료에서 시작위치부터 선택 개수만큼의 바이트 수를 추출 
SELECT SUBSTR('Welcome to Oracle', 4, 3) FROM DUAL;
-- 문자열에서 W은 1이고 공백포함 하나씩 증가한다. 그래서 문자열 4번째부터 3개 추출해서 출력해준다.

 

 

 

 

7. INSTR 함수

- INSTR 함수는 대상 문자열이나 칼럼에서 특정 문자가 나타나는 위치를 알려준다.

-- (7) INSTR 함수 : 대상 문자열이나 칼럼에서 특정 문자가 나타나는 위치 반환
SELECT INSTR('WELCOME TO ORACLE', 'O') FROM DUAL;
-- WELCOME TO ORACLE 에서 문자열 O 가 몇번째에 있는지 알려줌
-- 맨 처음으로 있는 문자열 O는 5번째에 있다 -> 5출력됨
SELECT INSTR('WELCOME TO ORACLE', 'ORACLE') FROM DUAL;  -- 12 출력

 

 

 

8. INSTRB 함수

- INSTRB 함수 역시 SUBSTRB 함수에서와 마찬가지로

  문자의 위치를 알아내기 위한 바이트 기준 으로 한다.

-- (8) INSTRB 함수
SELECT INSTR('데이터베이스', '이', 3, 1), INSTRB('데이터베이스', '이', 3, 1) FROM DUAL;
-- INSTR('데이터베이스', '이', 3, 1) : 문자열에서 3번째부터 시작해서 '이' 문자열이 맨 처음으로 나오는 번째수
-- INSTRB('데이터베이스', '이', 3, 1) : ???

 

 

 

9. LPAD/RPAD 함수

- LPAD(LEFT PADDING) 함수는 칼럼이나 대상 문자열을 명시된 자릿수에서

  오른쪽에 나타내고, 남은 왼쪽 자리를 특정 기호로 채운다.

  RPAD(RIGHT PADDING) 함수는 반대로 칼럼이나 대상 문자열을 명시된 자릿수에서

  왼쪽에 나타내고, 남은 오른쪽 자리를 특정 기호로 채운다.

 

-- (9) 여백추가 함수 : LPAD(LEFT PADDING) 함수 : 왼쪽에 함수를 둬라 / RPAD 함수 : 오른쪽에 여백을 둬라
SELECT LPAD('Oracle', 20, '#') FROM DUAL;
-- 왼쪽에 여백을 두는데 전체 길이는 20이 되고 왼쪽 여백을 #로 채워라
SELECT RPAD('Oracle', 20, '#') FROM DUAL;
-- 오른쪽에 여백을 두는데 전체 길이는 20이 되고 오른쪽 여백을 #로 채워라
SELECT LPAD('Oracle', 20, ' ') FROM DUAL;
-- 왼쪽에 여백을 두는데 전체 길이는 20이 되고 왼쪽 여백을 빈공간 ' '로 채워라

 

 

 

10. LTRIM와 RTRIM 함수

- LTRIM 함수는 문자열의 왼쪽(앞)의 공백 문자들을 삭제한다.

  RTRIM 함수 역시 오른쪽(뒤)의 공백 문자를 잘라낸다.

-- (10) 여백삭제 함수 : LTRIM와 RTRIM 함수
SELECT LTRIM('     Oracle  ') FROM DUAL;
-- 왼쪽 여백은 지우고 오른쪽 여백은 남은 채 출력
SELECT RTRIM('     Oracle  ') FROM DUAL;
-- 오른쪽 여백은 지우고 왼쪽 여백은 남은 채 출력

 

 

 

 

11. TRIM 함수

- TRIM 함수는 칼럼이나 대상 문자열에서 특정 문자가 첫 번째 글자이거나

  마지막 글자이면 잘라내고 남은 문자열만 반환한다.

-- (11) TREIM 함수
SELECT TRIM('A' FROM 'AAAAAAORACLEAAA') FROM DUAL;
-- A 문자는 모두 다 지운 문자열 출력 -> ORACLE 만 출력된다.

 

 

---

[ 4 ] 날짜 함수 (자주 사용한다!!)

1. 현재 날짜를 반환하는 SYSDATE 함수

- SYSDATE 함수는 시스템에 저장된 현재 날짜를 반환하는 함수

-- (1) SYSDATE 함수
SELECT SYSDATE FROM DUAL;

 

 

2. 날짜 연산

- 날짜 형 데이터에 숫자를 더하면(날짜+숫자) 그 날짜로부터

  그 기간만큼 지난 날짜를 계산한다.

  날짜 형 데이터에 숫자를 빼면(날짜-숫자) 그 날짜로부터

  그 기간만큼 이전 날짜를 구한다.

-- (2) 날짜 연산
--SELECT SYSDATE-1 어제, SYSDATE 오늘, SYSDATE+1 내일 FROM DUAL;
SELECT SYSDATE-1 "어제", SYSDATE "오늘", SYSDATE+1 "내일" FROM DUAL;
-- " " 안적고 그냥 어제, 오늘, 내일로 적어서 실행해도 똑같이 출력된다.

 

 

 

 

3. 특정 기준으로 반올림하는 ROUND 함수

- ROUND 함수는 숫자를 반올림하는 함수로 학습하였다.

  하지만, 이 함수에 포멧 모델을 지정하면 숫자 이외에 날짜에 대해서도

  반올림을 할 수 있다.

 

-- (3) 반올림 함수 : ROUND 함수
SELECT HIREDATE, ROUND(HIREDATE, 'MONTH') FROM EMP;
-- 일이 15일 지나면 반올림 되어서 2월이던게 3월로 반올림 된다
-- ex) 02/ 19  --> 03/01 이 된다.

 

 

 

---

 

[ 5 ] 형 변환 함수 (매우매우 중요!!!)

- 오라클을 사용하다 보면 숫자, 문자, 날짜의 데이터 형을

  다른 데이터형으로 변환해야 하는 경우가 생긴다.

- 이럴 때 사용하는 함수가 형 변환 함수이다.

  형 변환 함수로는 TO_NUMBER, TO_CHAR, TO_DATE 가 있다. 

[ 형 변환 함수의 종류 ]

 

 

1. 문자형으로 변환하는 TO_CHAR 함수

 

( 1 ) 날짜형을 문자형으로 변환하기

- DATE 형태의 데이터를 지정한 양식에 의해 VARCHAR2 형의 문자로 변환

-- (1-1) 날짜형 -> 문자형 변환하기 : TO_CHAR 함수
SELECT SYSDATE, TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:MI:SS') FROM DUAL;
-- 원래 SYSDATE 객체에는 시, 분, 초 까지 다 가지고 있다.

 

( 2 ) 숫자형을 문자형으로 변환하기

-- (1-2) 숫자형 -> 문자형 변환 : TO_CHAR 함수
SELECT TO_CHAR(123456, '0000000000'), TO_CHAR(123456, '999,999,999') FROM DUAL;
-- TO_CHAR(123456, '0000000000') : 123456을 10자리 문자형으로 -->  0000123456
-- TO_CHAR(123456, '999,999,999') :                         -->      123,456

 

 

 

 

 

2. 날짜형으로 변화하는 TO_DATE 함수

- 날짜 형은 세기, 년도, 월, 일. 시간, 분. 초와 같이 날짜와 시간에 대한 정보를 저장

- 오라클에서 기본 날짜 형식은 'YY/MM/DD'형식으로 '년/월/일'

  예를 들면 '06/03/08' 식으로 나타낸다.

  만일 년도를 4자리로 출력하려면 'YYYY/MM/DD' 형식으로 지정한다.

- TO_DATE 함수는 문자열을 날짜 형으로 변환

 

( 1 ) 숫자형 -> 날짜형 (NUMBER -> DATE)

-- (1) 숫자형 -> 날짜형 (NUMBER -> DATE)
SELECT ENAME, HIREDATE FROM EMP
WHERE HIREDATE=TO_DATE(19810220,'YYYYMMDD');
-- 숫자 19810220 를 날짜형 YYYYMMDD 으로 변환한 HIREDATE 와 그에 해당하는 사람의 ENAME 같이 출력

 

 

 

( 2 )  문자형 -> 날짜형 (CHAR -> DATE)

-- (2) 문자형 -> 날짜형 (CHAR -> DATE)
SELECT TRUNC(SYSDATE - TO_DATE('2022/11/02', 'YYYY/MM/DD'))FROM DUAL;
-- '2022/11/02' 문자를 YYYY/MM/DD 날짜형으로 바꿔서 SYSDATE인 오늘 날짜로부터 뺀 수 를 출력
--SELECT TRUNC(SYSDATE - TO_DATE('2022-11-02', 'YYYY-MM-DD'))FROM DUAL;
-- 위와 같이 적어도 문자형에서 날짜형으로 변환이 된다.

 

 

 

 

3. 숫자형으로 변환하는 TO_NUMBER 함수

- TO_NUMBER 함수는 특정 데이터를 숫자형으로 변환해 주는 함수

  * 날짜에서 숫자형으로는 변환이 안되는,,가봄,,

  * 숫자 형태의 문자열만 TO_NUMBER 사용해서 숫자형으로 형변환 할 수 있다.

  ex) '홍길동' 과 같은 문자열은 숫자형으로 형변환이 불가능하다.

-- 문자형 -> 숫자형 (CHAR -> NUMBER)
SELECT TO_NUMBER('20,000', '99,999') - TO_NUMBER('10,000', '99,999') FROM DUAL;
-- '20,000' 문자열을 99,999 숫자형으로 바꾸고 '10,000' 문자를 99,999 숫자형으로 바꿔서 뺀 수를 출력

 

 

 

---

 

[ 6 ] NULL을 다른 값으로 변환하는 NVL 함수

- NVL 함수는 NULL을 0 또는 다른 값으로 변환하기 위해서 사용하는 함수

-- 5.6 NULL을 다른 값으로 변환하는 NVL 함수
SELECT ENAME, SAL, COMM, SAL*12+COMM, NVL(COMM,0), SAL*12+NVL(COMM,0)
FROM EMP;

 

 

 

---

 

[ 7 ] 선택을 위한 DECODE 함수

- DECODE 함수는 프로그램 언어에서 가장 많이 사용되는

  switch case 문과 같은 기능을 갖는다.

  즉, 여러 가지 경우에 대해서 선택할 수 있도록 한다.

 

-- 5.7 선택을 위한 DECODE 함수 : 자바의 switch 문과 같은 역할
SELECT * FROM EMP;
SELECT * FROM DEPT;
SELECT ENAME, DEPTNO, DECODE(DEPTNO, 10, 'ACCOUNT',
                                     20, 'RESEARCH',
                                     30, 'SALES',
                                     40, 'OPERATIONS') AS DNAME
FROM EMP;                                  
-- DEPTNO 이 10이면 ACCOUNT로, 20이면 RESEARCH로 30이면 SALES로, 40이면 OPERATIONS로 출력해라

 

 

 

---

 

[ 8 ] 조건에 따라 서로 다른 처리가 가능한 CASE 함수

- CASE 함수 역시 여러 가지 경우에 대해서 하나를 선택하는 함수

- DECODE 함수와 차이점이 있다면

  DECODE 함수는 조건이 일치(= 비교 연산자)하는 경우에 대해서만 적용되는 반면,

  CASE 함수는 다양한 비교 연산자를 이용하여 조건을 제시할 수 있으므로

  범위를 지정할 수도 있다. 

- CASE 함수는 프로그램 언어의 if else if else 와 유사한 구조를 갖는다.

-- 5.8 CASE WHEN 함수 : 자바에서의 if-else 문과 같은 역할
SELECT ENAME, DEPTNO, CASE WHEN DEPTNO=10 THEN 'ACCOUNTING'
                           WHEN DEPTNO=20 THEN 'RESEARCH'
                           WHEN DEPTNO=30 THEN 'SALES'
                           WHEN DEPTNO=40 THEN 'OPERATIONS'
                           END AS DNAME
FROM EMP;

 

 

---

과제 - 05 - 01. TXT

SQL > CONN SCOTT/TIGER 로 접속하여 SQL문을 작성해라.

 

[1번] 사원테이블(EMP)에서 입사일(HIREDATE)을

       4자리 연도로 출력되도록 SQL문을 작성하세요. (ex. 1980/01/01)

SELECT HIREDATE, TO_CHAR(HIREDATE, 'YYYY/MM/DD') FROM EMP;

 

 

 

 

[2번] 사원테이블(EMP)에서 MGR컬럼의 값이 null 인 데이터의

        MGR의 값을 CEO로 출력하는 SQL문을 작성하세요.

 

-- 내가 적은 코드
SELECT MGR, DECODE(MGR, NULL, 'CEO') AS DNAME
FROM EMP;

-- 답안 코드
SELECT ENAME, NVL(TO_CHAR(MGR), 'CEO') AS MGR FROM EMP;

 

[ 내가 적은 코드 결과물 - 틀림,, ]

 

 

[ 답안 결과물 ]