[2일차]함수

##  SQL - 2일차 - 함수

# 문자 함수

SELECT EMAIL, LOWER(EMAIL), UPPER(EMAIL), INITCAP(EMAIL)
FROM EMP;

SELECT LAST_NAME
FROM EMP
WHERE LAST_NAME LIKE ('T%') OR LAST_NAME LIKE ('%t%');

SELECT LAST_NAME
FROM EMP
WHERE initcap(LAST_NAME) = 'Chen';

SELECT LAST_NAME
FROM EMP
WHERE LAST_NAME = initcap('chen'); //검색 속도가 빠르다

*** 조건식의 컬럼에 인덱스가 있는 경우 변형하지 않아야 인덱스 사용!!
*** 변형이 되면 인덱스를 사용하지 않고 FULL SCAN 한다.

LENGTH (컬럼명)
-해당 컬럼의 문자의 길이
-영문자, 숫자, 특수문자, 공백, 한글... 모두 1자 인식!!!

LENGTHB(컬럼명)
-해당 컬럼의 바이트의 길이

SELECT EMAIL, LENGTH(EMAIL)
FROM EMP;

SELECT LENGTH('가나다12 %#$@') FROM DUAL;

SELECT LENGTH('가'), LENGTHB('가') FROM DUAL;

SUBSTR(컬럼명, 시작위치, [ 문자갯수])
- *****
-시작위치 > 0 : 왼쪽부터 시작
-시작위치 < 0 : 오른쪽부터 시작

SELECT EMAIL, SUBSTR(EMAIL,1,3)
FROM EMP;

SELECT EMAIL, SUBSTR(EMAIL,-2,3)
FROM EMP;

사원, 주민번호 : 881010-1234567

10월생 사원의 정보

SELECT *
FROM 사원
WHERE SUBSTR 주민번호(3,2) = '10' ;

남자 사원만 검색

SELECT *
FROM 사원
WHERE SUBSTR 주민번호(8,1) IN ('1','3');

INSTR(컬럼명,'찾고자 하는 문자 또는 문자열')
INSTR(컬럼명,'찾고자 하는 문자 또는 문자열', [시작위치, 몇번째것])

- 찾고자 하는 문자의 시작위치 반환
-없으면 0 반환
-여러개 있는 경우 첫번째 위치 반환

SELECT EMAIL, INSTR(EMAIL, 'S')
FROM EMP;

CODE
----------------------------------
DFDF_343543
ET_3345576
BBBBB_323

'_' 앞 문자만, '_' 뒤의 문자만

SELECT CODE,
          SUBSTR(CODE,1,INSTR(CODE,'_')-1) AS 왼쪽,
          SUBSTR(CODE,1,INSTR(CODE,'_')+1) AS 오른쪽
FROM 제품;

SELECT JOB_ID
SUBSTR(CODE,1,INSTR(CODE,'_')-1) AS 왼쪽,
SUBSTR(CODE,1,INSTR(CODE,'_')+1) AS 오른쪽
FROM EMP;

두번째 '_' 뒤 문자

SELECT CODE,
SUBSTR(CODE, 1, INSTR(CODE,'_',1,2)-1) AS 왼쪽,
FROM 제품;

LPAD(컬럼명, 전체사이즈, '채울문자')

SELECT EMAIL, LPAD( EMAIL, 15, '-')
FROM EMP;

SELECT SALARY, LPAD(SALARY, 15, '-')
FROM EMP;

EMAIL 의 앞 3자만 표시하고 나머지 '*' 표시하고자 한다.
SELECT EMAIL, RPAD(SUBSTR(EMAIL,1,3),LENGTH(EMAIL),*)
FROM EMP;

REPLACE (컬럼명,'찾는 문자','바꿀 문자')

SELECT EMAIL, REPLACE(EMAIL, 'S', '***')
FROM EMP;

SELECT REPLACE(컬럼명,' ','')

공백제거
SELECT REPLACE(' ABC ',' ','')
FROM DUAL;

TRIM(컬럼명)
- 앞뒤 공백 제거
- 중간 공백 유지

SELECT TRIM('                        ABC        DDD      ') FROM DUAL;

===========================

#숫자 함수

ROUND( 컬럼명, [자릿수])
-반올림
-자리수
=> 0 : 정수만 표시, 생략가능
=> > 0 : 소숫점 이하 자릿수 표시
=> < 0 : 정수 자릿수

TRUNC(컬럼명, [자릿수])
-절사, 버림함수
-자리수
=> 0 : 정수만 표시, 생략가능
=> > 0 : 소숫점 이하 자릿수 표시
=> < 0 : 정수 자릿수

===============================

#날짜 함수
-날짜:세기,년,월,일,시,분,초=>7가지
-날짜 데이터 타입
DATE : 일반적인 날짜
TIMESTAMP : 초이하 밀리세컨단위 까지 => 로그성 자료의 날짜
-날짜는 연산이 가능하다.
- 날짜 - 날짜 = 일자
- 날짜 + 숫자 = 일자에 숫자가 연산된다.

SELECT LAST_NAME, SYSDATE - HIRE_DATE
FROM EMP;

SELECT LAST_NAME, HIRE_DATE, HIRE_DATE + 10
FROM EMP;

ADD_MONTHS(날짜컬럼, 개월수)
ADD_MONTHS(날짜컴럼, -개월수)

SELECT LAST_NAME, HIRE_DATE,
 ADD_MONTHS( HIRE_DATE,10),
 ADD_MONTHS(HIRE_DATE,-10)
FROM EMP;

EXTRACT (요소 FROM 날짜컬럼)
- YEAR, MONTH, DAY, HOUR ...

SELECT LAST_NAME, HIRE_DATE,
EXTRACT (YEAR FROM HIRE_DATE) AS 입사년도
FROM EMP;

LAST_DAY (날짜컬럼)

SELECT LAST_NAME, HIRE_DATE
LAST_DAY(HIRE_DATE) AS 마지막날
FROM EMP;

NEXT_DAY 
- 입력한 날짜의 첫번째 요일의 날짜를 리턴

SELECT NEXT_DAY(SYSDATE,'월요일') FROM DUAL;
SELECT NEXT_DAY(SYSDATE,'월') FROM DUAL;
SELECT NEXT_DAY(SYSDATE,2) FROM DUAL;

=> 요일을 숫자로 : 일 월 화 수 목 금 토 => 1, 2, 3, 4, 5, 6, 7

MONTHS_BETWEEN ( 최근날짜, 오래전날짜)

SELECT LAST_NAME,HIRE_DATE,
TRUNC(MONTHS_BETWEEN (SYSDATE, HIRE_DATE), 0) AS 근속월
FROM EMP;

SELECT HIRE_DATE, ROUND(HIRE_DATE,'YEAR')
FROM EMP;

==================================

#형 전환 함수
-숫자, 날짜 -> TO_CHAR 
-문자 -> TO_NUMBER
-문자 -> TO_DATE

TO_DATE('문자열','날짜패턴')

'2000-10-10' => TO_DATE ('2000-10-10','YYYY-MM-DD')

TO_DATE('20001010','YYYYMMDD')
TO_DATE('2000/10/10','YYYY/MM/DD')
TO_DATE('10/10/2000','MM/DD/YYYY')

TO_CHAR(숫자컬럼, '패턴') : 숫자 -> 문자

SELECT SALARY, TO_CHAR(SALARY, '$99,9999') FROM EMP;
=> ###### 으로 출력이 될때는 데이터가 포맷보다 자릿수가 더 많을 때

SELECT SALARY, TO_CHAR(SALARY, 'L99,999') FROM EMP;
=> 서버의 지역의 화폐단위가 붙는다.

SELECT SALARY, TO_CHAR(SALARY,'$00,000') FROM EMP;

SELECT COMMISSION_PCT, TO_CHAR(COMMISSION_PCT,'99,999.00') FROM EMP;

TO_CHAR(날짜컬럼,'패턴'):날짜->문자

SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH:MI:SS' ) FROM DUAL;
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD DY HH:MI:SS') FROM DUAL;
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD DY AM HH:MI:SS') FROM DUAL;
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD DY HH24:MI:SS') FROM DUAL;

==============================

일반 함수 

# NULL
-아직 입력되지 않은 값, 컬럼의 값이 없음
-0, 공백과는 다름
-NULL 산술연산에 포함되면 결과 NULL
-NULL 논리연산에 포함되면 경우에 따라 다르다

실지급액 : 급여 + 판매수당
판매수당 : 급여 * 판매수당율

SELECT LAST_NAME,
SALARY,
SALARY + (SALARY * NVL(COMMISSION_PCT,0)) AS 실지급액
FROM EMP;


*****
# NVL
-널값을 특정 값으로 치환 하는 함수

NVL(컬럼명, 널일경우 치환할 값)
NVL(SALARY, 0)

SELECT LAST_NAME,
SALARY,
SALARY + (SALARY * NVL(COMMISSION_PCT,0)) AS 실지급액
FROM EMP;

NVL(값1, 값2):값1이 널이면 값2를 치환
NVL2(값1,값2,값3):값1이 널이면 값3을 출력, 아니면 값2를 출력
NVLIF(값1,값2):값1=값2 라면 널 출력, 값1!=값2 라면 값1을 출력
COALESCE(값1,값2,값3........)->널값이 아닌 첫번째 값 출력

SELECT LAST_NAME,COMMISSION_PCT,
NVL2(COMMISSION_PCT,'수당받음','수당안받음') AS 수령여부
FROM EMP;

==========================

CASE 문
-쿼리문 안의 조건문
-IF문과 유사한 기능
-ELSE는 필수는 아니다
-CASE에 맞는 것이 없으면 NULL 출력

SELECT LAST_NAME,SALARY,SALARY*2 AS BONUS FROM EMP;

부서별로 보너스를 차등 지급
20 -> 2배, 30 -> 3배, 40 -> 4배 나머지 -> 1배
SELECT LAST_NAME,SALARY,SALARY*2 AS BONUS FROM EMP;
SELECT LAST_NAME,SALARY,DEPARTMENT_ID
CASE DEPARTMENT_ID 
WHEN 20 THEN SALARY*2
CASE DEPARTMENT_ID
WHEN 30 THEN SALARY*3
CASE DEPARTMENT_ID
WHEN 40 THEN SALARY*4
ELSE
SALARY*1
END AS BONUS
FROM EMP;

SELECT LAST_NAME,SALARY,
CASE
WHEN DEPARTMENT_ID=20 THEN SALARY*2
WHEN DEPARTMENT_ID=30 THEN SALARY*3
WHEN DEPARTMENT_ID=40 THEN SALARY*4
ELSE
SALARY*1
END AS BONUS
FROM EMP;

SELECT LAST_NAME,SALARY,
CASE
WHEN SALARY >= 20000 THEN '☆☆☆'
WHEN SALARY >= 100000 THEN '☆☆'
WHEN SALARY >= 0 THEN '☆'
END AS GRADE
FROM EMP;