레도널스의 개발자 되어가기

SQL Functions

SQL 함수

1. Single-row Functions 단일행 함수

* 함수는 중첩 사용 가능

* Character 함수

SELECT employee_id, last_name, LOWER(last_name)

FROM   employees

WHERE  department_id = 50;

*

SELECT UPPER('oracle database'), LOWER('Oracle Database'), INITCAP('oracle database')

FROM dual;

* 계산식(dual 테이블 이용)

SELECT 23542*254 AS Result FROM dual;

*

SELECT employee_id, last_name, department_id

FROM   employees

WHERE  LOWER(last_name) = 'king';

* 연결 함수 CONCAT

SELECT employee_id, CONCAT(first_name, last_name) AS Fullname  <-  AS는 생략 가능

FROM   employees;

SELECT employee_id, CONCAT(CONCAT(first_name,' '), last_name) Fullname

FROM   employees;

* SUBSTR (섭스타) : SUBSTR('text', 검색 시작 위치, 글자 수)

SELECT SUBSTR('HelloWorld', 6, 5) FROM dual;

SELECT SUBSTR('HelloWorld', -5, 5) FROM dual;

두 식은 같다.

검색 시작 위치에 음수를 쓰면 맨 뒤부터 찾음.

*

SELECT * FROM employees

WHERE SUBSTR(last_name, -1, 1) = 'n';

= WHERE last_name LIKE '%n';

last_name이 n으로 끝나는 사원을 찾음.

* INSTR (인스타)

SELECT INSTR('HelloWorld', 'l') FROM dual;  ->  'l'의 위치

SELECT INSTR('HelloWorld', 'l', 1, 2) FROM dual;  ->  1(1번째)부터 검색해서 'l'이 2번째 반환되는 위치

해당 사항이 없으면 0을 반환

*

SELECT * FROM employees

WHERE INSTR(last_name, 'A') <> 0;

* 패딩 함수 LPAD, RPAD (글자 수 포함, 나머지 문자는 지정한 문자로 채움)

- LPAD : 왼쪽부터 패딩을 준다.

- RPAD : 오른쪽부터 패딩을 준다.

ex)

SELECT employee_id, last_name, LPAD(salary,10,'*') AS Salary

FROM   employees

WHERE  department_id = 60;

SELECT employee_id, last_name, RPAD(salary,10,'*') AS Salary

FROM   employees

WHERE  department_id = 60;

SELECT RPAD(last_name, 15, '*') AS last_name,

       LPAD(salary, 10, '*') AS salary

FROM employees;

* TRIM 함수 : 잘라내기

ex)

SELECT TRIM('w' FROM 'window') FROM dual;  ->  모든 'w'

SELECT TRIM(LEADING 'w' FROM 'window'),  ->  선행하는(앞의) 'w'

       TRIM(TRAILING 'w' FROM 'window')  ->  따르는(뒤의) 'w'

FROM dual;

* Number Functions 숫자 함수

- 올림

SELECT ROUND(45.923,2), ROUND(45.923,0), ROUND(45.923,-1)

FROM dual;

- 버림

SELECT TRUNC(45.923,2), TRUNC(45.923,0), TRUNC(45.923,-1)

FROM dual;

- 나머지

SELECT last_name, salary, MOD(salary, 5000)

FROM employees

WHERE job_id = 'SA_REP';

* 날짜 연산하기

SELECT last_name, hire_date

FROM employees

WHERE hire_date < '1988-12-17';

* sysdate 함수

Database Server의 날짜.

서버와 클라이언트가 떨어져 있는 경우(서로 다른 타임존에 있는 경우) 시차가 발생할 수 있음.

SELECT sysdate FROM dual;

* sysdate의 표시 형식 변경하기

ex)

ALTER SESSION SET nls_date_format = 'yyyy/mm/dd hh24:mi:ss';

: Session이 유지 중일 때만 날짜 형식 변경

* sysdate는 계산이 가능

ex)

SELECT sysdate +10, sysdate -10

FROM dual;  ->  날짜 계산

SELECT sysdate, sysdate +10/24, sysdate -10/24

FROM dual;  ->  시간 계산

SELECT sysdate, sysdate +10/(24*60), sysdate -10/(24*60)

FROM dual;  ->  분 계산

*

SELECT employee_id, hire_date, sysdate-hire_date

FROM employees;

date에 시간이 함께 나온다.

-

DESC employees;

해 보면, date 데이터 타입은 길이가 없음을 알 수 있다.

그렇지만 저장할 때는 시간도 같이 저장한다.

이것을 응용하면,

-

SELECT employee_id, hire_date, TRUNC(sysdate-hire_date)

FROM employees;

* 날짜 함수

1) MONTHS_BETWEEN : 두 날짜의 차이

ex)

SELECT employee_id, hire_date, MONTHS_BETWEEN(sysdate, hire_date)

FROM employees;

응용)

SELECT employee_id, hire_date, ROUND(MONTHS_BETWEEN(sysdate, hire_date))

FROM employees;

2) ADD_MONTHS : 개월 더하기

SELECT employee_id, hire_date, ADD_MONTHS(hire_date, 4)

FROM employees;

3) NEXT_DAY : 

SELECT sysdate, NEXT_DAY(sysdate, '월요일') FROM dual;

OR

SELECT sysdate, NEXT_DAY(sysdate, '월') FROM dual;

BUT

SELECT sysdate, NEXT_DAY(sysdate, 'Monday') FROM dual; <- 지역에 따라 안 됨

따라서,

일요일 1, 토요일 7

SELECT sysdate, NEXT_DAY(sysdate, 2) FROM dual;

4) LAST_DAY : 생략

5) ROUND : 날짜 초기화(기준에 따라)

ex)

- 'year' : 6월 30일 기준으로 반올림

SELECT sysdate, ROUND(sysdate, 'year') FROM dual;

- 'mm' : 15일 기준(30/2 = 15일)

- 'dd' : 하루 기준(24/2 = 12시간)

- 'd' : 일주일 기준(7/2 = 3.5일)

6) TRUNC : 날짜 초기화(이전)

* Conversion Functions : 변환 함수

1) TO_CHAR : 문자로

ex) SELECT TO_CHAR(sysdate, 'yyyy-mm-dd hh24:mi:ss') FROM dual;

- 날짜 형식 앞에 'fm' 붙이면, 데이터 외에 필요없는 것(자리수 채우는, 0이나, 공백) 삭제

ex) SELECT employee_id, hire_date, TO_CHAR(hire_date, 'yy/mm/dd')

    FROM employees;  ->  18/05/07

    SELECT employee_id, hire_date, TO_CHAR(hire_date, 'fmyy/mm/dd')

    FROM employees;  ->  18/5/7

2) TO_NUMBER : 숫자로

ex)

SELECT employee_id, last_name, TO_CHAR(salary, 'L99,999.99') AS salary

FROM employees

WHERE salary > TO_NUMBER('$8,000', '$9,999');

3) TO_DATE : 날짜로

ex)

SELECT employee_id, last_name, TO_CHAR(hire_date, 'yyyy/month/dd') AS salary

FROM employees

WHERE hire_date > TO_DATE('31-12-99', 'dd-mm-rr');

* yy: 같은 세기에서 찾음(99, yy일 경우 2099년을 찾게 됨)

  rr: 현재 세기를 기준으로(100/2=50년), 뒤쪽의 데이터를 찾는다면 자동으로 앞의 것에서 찾음

* Nesting Functions : 함수 중첩

ex)

SELECT last_name,

UPPER(CONCAT(SUBSTR(last_name, 1, 8), '_US'))

FROM employees

WHERE department_id = 60;

* General Functions : 일반 함수

1) NVL (expr1, expr2) : 인수가 null일 경우, 계산 가능한 값(타입이 같아야 함)으로 변환

ex) SELECT employee_id, last_name, salary + salary*NVL(commission_pct, 0) AS monthly_salary

    FROM employees;

- 계산하지 않는 경우에도 쓸 수 있음

ex) SELECT employee_id, last_name, salary, NVL(commission_pct, -1)

 FROM employees;

2) NVL2 (expr1, expr2, expr3) : expr1이 null이 아니면 expr2, null이면 expr3을 반환

ex)

SELECT employee_id, last_name, salary + salary*NVL(commission_pct, 0) AS monthly_salary,

NVL2(commission_pct, 'COMM', 'NOCOMM') AS bigo

FROM employees;

3) NULLIF (expr1, expr2) : 인수가 같으면 null, 다르면 expr1을 반환

ex)  SELECT NULLIF('a','a') FROM dual;

ex2) SELECT first_name, last_name, NULLIF(LENGTH(first_name), LENGTH(last_name))

  FROM employees;

4) COALESCE (expr1, expr2, ..., exprn) : 처음으로 null이 아닌 인수의 값을 반환

ex) SELECT employee_id, commission_pct, manager_id,

 COALESCE(commission_pct, manager_id, 7777)

 FROM employees;