[DB] 함수(FUNCTUIN)

단일 행 함수의 주요 특징

① SELECT, WHERE, ORDER BY 절에 사용 가능하다.

② 각 행(Row)들에 대해 개별적으로 작용하여 데이터 값들을 조작하고, 각각의 행에 대한 조작 결과를 리턴 한다.

③ 여러 인자(Argument)를 입력해도 단 하나의 결과만 리턴 한다.

④ 함수의 인자(Arguments)로 상수, 변수, 표현식이 사용 가능하고, 하나의 인수를 가지는 경우도 있지만 여러 개의 인수를 가 질 수도 있다.

⑤ 특별한 경우가 아니면 함수의 인자(Arguments)로 함수를 사용하는 함수의 중첩이 가능하다

 

단일 행 함수의 종류

 

단일 행 문자형 함수 사용

--단일행 문자형 함수 예시
SELECT
LOWER('SQL Developer') AS "LOWER('SQL Developer')" --소문자로 변환
, UPPER('SQL Developer') AS "UPPER('SQL Developer')" --대문자로 변환
, ASCII('A') AS "ASCII('A')" --아스키코드값 출력
, CHR('65') AS "CHR('65')" --아스키코드값의 문자 출력
, CONCAT('SQL', 'Developer') AS "CONCAT('SQL', 'Developer')" --문자열 결합
, SUBSTR('SQL Developer', 1, 3) AS "SUBSTR('SQL Developer', 1, 3)" --문자열 잘라내기
, LENGTH('SQL') AS "LENGTH('SQL')" --문자열의 길이 출력
, LTRIM(' SQL') AS "LTRIM(' SQL')" --왼쪽 공백 제거
, RTRIM('SQL ') AS "RTRIM('SQL ')" --오른쪽 공백 제거
FROM DUAL
;

LOWER : 소문자를 대문자로 바꿔준다.

UPPER : 대문자를 소문자로 바꿔준다.

AXCII : 문자열을 아스키코드로 바꿔준다.

CHR : 아스키코도값의 문자를 출력해준다.

CONCAT : 문자열을 더해준다.

SUBSTR : 1첫번째 이상 3번째 이하의 문자를 잘라서 보여준다.

LENGTH : 문자열의 길이를 출력해준다.

LTRIM : 왼쪽 공백을 제거해준다.

RTRIM : 오른쪽 공백을 제거해준다.

 

 

단일 행 숫자형 함수 사용

SELECT
ABS(-15) AS "ABS(-15)" --절대값을 반환
, SIGN(10) AS "SIGN(10)" --양수일경우 1, 음수일경우 -1, 0일 경우 0 반환
, MOD(8,3) AS "MOD(8,3)" --나머지를 반환
, CEIL(38.678) AS "CEIL(38.678)" --무조건 올림
, FLOOR(38.678) AS "FLOOR(38.678)" --무조건 버림
, ROUND(38.678, 2) AS "ROUND(38.678, 2)" --소수점 2번째 자리에서 반올림
, TRUNC(38.678) AS "TRUNC(38.678)" --0의 자리에서 무조건 자름
, TRUNC(38.678, 1) AS "TRUNC(38.678, 1)" --1의 자리에서 무조건 자름
, TRUNC(38.678, 2) AS "TRUNC(38.678, 2)" --2의 자리에서 무조건 자름
, TRUNC(38.678, 3) AS "TRUNC(38.678, 3)" --3의 자리에서 무조건 자름
FROM DUAL
;

날짜형 데이터 변환 함수 사용

SELECT SYSDATE AS "SYSDATE" --현재 년월일시분초 출력
, EXTRACT(YEAR FROM SYSDATE) AS "EXTRACT(YEAR FROM SYSDATE)" --년 출력
, EXTRACT(MONTH FROM SYSDATE) AS "EXTRACT(MONTH FROM SYSDATE)" --월 출력
, EXTRACT(DAY FROM SYSDATE) AS "EXTRACT(DAY FROM SYSDATE)" --일 출력
, TO_CHAR(SYSDATE, 'YYYY') AS "TO_CHAR(SYSDATE, 'YYYY')" --년 출력(문자열)
, TO_CHAR(SYSDATE, 'MM') AS "TO_CHAR(SYSDATE, 'MM')" --월 출력(문자열)
, TO_CHAR(SYSDATE, 'DD') AS "TO_CHAR(SYSDATE, 'DD')" --일 출력(문자열)
, TO_CHAR(SYSDATE, 'HH24') AS "TO_CHAR(SYSDATE, 'HH24')" --시 출력(문자열)
, TO_CHAR(SYSDATE, 'MI') AS "TO_CHAR(SYSDATE, 'MI')" --분 출력(문자열)
, TO_CHAR(SYSDATE, 'SS') AS "TO_CHAR(SYSDATE, 'SS')" --초 출력(문자열) 
, TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY')) AS "TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY'))" --년 출력
, TO_NUMBER(TO_CHAR(SYSDATE, 'MM')) AS "TO_NUMBER(TO_CHAR(SYSDATE, 'MM'))" --월 출력
, TO_NUMBER(TO_CHAR(SYSDATE, 'DD')) AS "TO_NUMBER(TO_CHAR(SYSDATE, 'DD'))" --일 출력
, TO_NUMBER(TO_CHAR(SYSDATE, 'HH24')) AS "TO_NUMBER(TO_CHAR(SYSDATE, 'HH24'))" --시 출력
, TO_NUMBER(TO_CHAR(SYSDATE, 'MI')) AS "TO_NUMBER(TO_CHAR(SYSDATE, 'MI'))" --분 출력
, TO_NUMBER(TO_CHAR(SYSDATE, 'SS')) AS "TO_NUMBER(TO_CHAR(SYSDATE, 'SS'))" --초 출력
FROM DUAL
;

날짜형 데이터 연산

SELECT
SYSDATE AS "SYSDATE" --현재 년월일 시분초
, SYSDATE - 1 AS "SYSDATE - 1" --1일을 뺀 년월일 시분초
, SYSDATE - (1/24) AS "SYSDATE - (1/24)" --1시간을뺀 년월일 시분초
, SYSDATE - (1/24/60) AS "SYSDATE - (1/24/60)" --1분을뺀 년월일 시분초
, SYSDATE - (1/24/60/60) AS "SYSDATE - (1/24/60/60)" --1초을뺀 년월일 시분초
, SYSDATE - (1/24/60/60) * 10 AS "SYSDATE - (1/24/60/60) * 10" --10초을뺀 년월일 시분초
, SYSDATE - (1/24/60/60) * 30 AS "SYSDATE - (1/24/60/60) * 30" --30초을뺀 년월일 시분초
FROM DUAL 
;

 

CASE표현의 종류

 

SELECT CASE WHEN SAL_CD = '100001' THEN '기본급'
WHEN SAL_CD = '100002' THEN '상여급'
WHEN SAL_CD = '100003' THEN '특별상여급'
WHEN SAL_CD = '100004' THEN '야근수당'
WHEN SAL_CD = '100005' THEN '주말수당'
WHEN SAL_CD = '100006' THEN '점심식대'
WHEN SAL_CD = '100007' THEN '복지포인트'
ELSE '유효하지않음'
END SAL_NM
FROM TB_SAL
;

SELECT DECODE(SAL_CD, '100001', '기본급', '100002', '상여급', '기타') AS SAL_NM
FROM TB_SAL 
;

 

NULL연산

SELECT NVL(MAX(EMP_NM), '존재안함') AS EMP_NM
FROM TB_EMP
WHERE EMP_NM = '박찬호';

NVL 함수는 왼쪽이 널이면 오른쪽이 나오게 되는 함수인데 

EMP_NM을 MAX 함수로 감싸게 되면 공집합의 경우에도 한개의 ROW 생기게 됩니다.

현재 DB에는 박찬호라는 사람이 없을 경우에 MAX를 사용안하게되면 ROW가 안생기게 되어서 

위와 같이 아무것도없는 공집합이 나오게 됩니다.

그래서 이와같은 상황을 방지하기 위해 MAX로 감싸주어서 공집합의 경우에도 한개의 ROW가 나오도록 해줍니다.

 

SELECT NULLIF('박찬호', '박지성') --같지않으면 박찬호 리턴
FROM DUAL
; 박찬호

SELECT NVL(NULLIF('박찬호', '박찬호'), '같으면널')
FROM DUAL
; 같으면 널

SELECT COALESCE(NULL, NULL, 0) AS SAL
FROM DUAL; 0

SELECT COALESCE(5000, NULL, 0) AS SAL
FROM DUAL; 5000

SELECT COALESCE(NULL, 6000, 0) AS SAL
FROM DUAL; 6000

NULLIF 는 앞과 뒤의 값이 다르면 앞에것을 리턴하고 같으면 널을 리턴합니다.

COALESCE 는 3개의 값들 중의 널이 아닌 첫번째값을 리턴합니다.