SQL 기본 - 함수

함수(FUNCTION)

내장 함수는 단일행 함수와 다중행 함수로 나눌 수 있다.

다중행 함수는 집계함수, 그룹 함수, 윈도우 함수로 나눌 수 있다.

여기서는 단일행 함수만 설명.

1. 문자열함수
LOWER('SQL Expert'); => 결과 : 'sql expert' 기능 : 모든 문자를 소문자로 바꾼다.
UPPER('SQL Expert'); => 결과 : 'SQL EXPERT' 기능 : 모든 문자를 대문자로 바꾼다.
ASCII('A'); => 결과 : 65 기능 : 문자나 숫자를 아스키코드 번호로 바꾼다.
CHAR(65); => 결과 : 'A' 기능 : 아스키코드 번호를 문자나 숫자로 바꾼다.
CONCAT('RDBMS','SQL'); => 결과 : 'RDBMS SQL' 기능 : 문자열 연결
// 'RDBMS' || 'SQL' 과 동일
SUBSTR('SQL Expert',5,3); => 결과 : 'Exp' 기능 : 문자열 자르기
// 5번째 문자부터 3개에 해당하는 문자열 까지 리턴, 3번째 인자가 없으면 마지막 문자까지 리턴
// SUBSTRING('SQL Expert',5,3) 와 동일
LENGTH('SQL Expert'); => 결과 : 10 기능 : 문자열의 길이 리턴
LTRIM('xxxYYZZxYZ','x'); => 결과 : 'YYZZxYZ' 기능 : 왼쪽에서 다른문자 만날때까지 해당문자열 제거
RTRIM('XXXYYzzXYzz','z'); => 결과 : 'XXXYYzzXY' 기능 : 오른쪽에서 다른문자 만날때까지 해당문자열 제거
TRIM('x' FROM 'xxYYZZxYZxx') => 결과 : 'YYZZxYZ' 기능 : 양쪽에서 다른 문자 만날때까지 해당문자열 제거
RTRIM('XXYYZZXYZ        '); => 결과 : 'XXYYZZXYZ' 기능 : 오른쪽에서 공백 제거
 
 
예제
SELECT LENGTH('SQL Expert')
FROM DUAL;
// DUAL 은 더미로 SELECT 문만 쓸 수 없어서 사용한 것임.
 
 
2. 숫자형함수
ABS(-15) => 결과 : 15 기능 : 절대값 리턴
SIGN(-20) => 결과 : -1 기능 : 양수인지 음수인지 0인지 구별 
SIGN(0) => 결과 : 0
SIGN(20) => 결과 : 1
MOD(7,3) => 결과 : 1 기능 : 첫번째 인자를 두번째 인자로 나눈 나머지
// 7%3
CEIL(38.123) => 결과 : 39 기능 : 숫자보다 크거나 같은 최소 정수
FLOOR(38.123) => 결과 : 38 기능 : 숫자보다 작거나 같은 최대 정수
// CEIL(-38.123) => 결과 : -38
// FLOOR(-38.123 => 결과 : -39
ROUND(38.5235,3) => 결과 38.524 기능 : 반올림함수. 두번째 인자 소수점까지 유효 디폴트는 0
TRUNC(38.5235,3) => 결과 38.523 기능 : 버림함수. 두번째 인자 소수점까지 유효
 
 
3. 날짜형함수
SYSDATE : 현재날짜와 시각 출력
SELECT SYSDATE FROM DUAL; , 17/09/02 출력
 
EXTRACT('YEAR'|'MONTH'|'DAY' FROM d) : 날짜데이터에서 년/월/일 데이터를 출력할 수 있음 시분초도 가능
SELECT ENAME, HIREDATE, EXTRACT(YEAR FROM HIREDATE) 입사년도, EXTRACT(MONTH FROM HIREDATE) 입사월, EXTRACT(DAY FROM HIREDATE) 입사일
FROM EMP;
 
TO_NUMBER(TO_CHAR(d,'YYYY')) 날짜데이터에서 년/월/일 출력가능, TO_NUMBER함수 제외시 문자형으로 출력됨.**
SELECT ENAME, HIREDATE, TO_NUMBER(TO_CHAR(HIREDATE,'YYYY')) 입사년도, TO_NUMBER(TO_CHAR(HIREDATE,'MM')) 입사월, TO_NUMBER(TO_CHAR(HIREDATE,'DD')) 입사일
FROM EMP;
 
 
4. 변환형함수
명시적(Explicit)변환 : 데이터 유형을 변환하도록 명시하는 경우
암시적(Implicit)변환 : 명시 없이 자동으로 데이터유형을 변환하여 계산하는 경우
 
TO_NUMBER(문자열) : ALPHANUMERIC 문자열을 숫자로 변환
SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD') 날짜, TO_CHAR(SYSDATE, 'YYYY. MON, DAY') 문자형
FROM DUAL;
// 결과는 날짜로는 2017/09/02 문자형으로는 2017. 9월, 토요일
SELECT TO_CHAR(123456789/1200, '$999,999,999.99') 환율반영달러
FROM DUAL;
// 결과는 $102,880.66
 
5. CASE표현
오라클에서 DECODE함수와 같은 기능을 함 *****중요
SELECT ENAME,
       CASE WHEN SAL > 2000
            THEN SAL
            ELSE 2000
       END REVISED_SALARY
FROM EMP;
// IF-THEN-ELSE 논리와 유사한 방식으로 급여가 2000이 넘으면 그대로 출력하고 2000이하면 2000으로 변경해서 출력한다.
 
SELECT LOC,
       CASE LOC
       WHEN 'NEW YORK' THEN 'EAST'
       WHEN 'BOSTON' THEN 'EAST'
       WHEN 'CHICAGO' THEN 'CENTER'
       WHEN 'LA' THEN 'WEST'
       ELSE 'ETC'
       END as AREA
FROM DEPT;
// 뉴욕이면 동부, 보스턴이면 동부, 시카고면 중부, LA면 서부, 그외는 ETC로 출력하고 그 칼럼을 AREA로 하겠다.
 
SELECT ENAME,
       CASE WHEN SAL >= 3000 THEN 'HIGH'
            WHEN SAL >= 1000 THEN 'MID'
            ELSE 'LOW'
       END AS SALARY_GRADE
FROM EMP;
// CASE 이후에 칼럼이나 조건식을 따로 넣지않고 사용가능
// 급여가 3000 이상이면 HIGH, 급여가 1000 이상이면 MID 그 이하는 LOW 출력
// 자연스럽게 앞에 것 부터 적용하는것 같다. 4000은 3000보다 크고 1000보다 크니 HIGH, MID 조건에 둘다 맞지만 먼저 적힌 3000이상에 걸리는듯.
 
 
6. NULL 관련 함수
NULL 은 아직 정의 되지않은 ㅏㅄ으로 0 또는 공백과 다르다.
NULL 값을 포함하는 연산에서는 값도 NULL 이다.
NULL + 2 = NULL, 2 - NULL = NULL, NULL * 2 = NULL, 2/NULL = NULL
//그래서 이런 NULL값을 처리하기 위해 NVL함수가 생겼다.
 
NVL (NULL판단대상, 'NULL일 때 대체값');
SELECT NVL(NULL, 'NVL-OK') NVL_TEST
FROM DUAL;
// NVL-OK 출력. NULL이니까
SELECT NVL('Not-Null', 'NVL-OK') NVL_TEST
FROM DUAL;
//결과는 Not-Null 출력, 왜? NULL이 아니니까
SELECT NVL('NULL', 'NVL-OK') NVL_TEST
FROM DUAL;
// 결과는 'NULL' 출력, 왜? NULL 과 'NULL'은 다르니까
 
**NVL함수를 다중행 함수의 인자로 사용하는 경우 오히려 불필요한 부하가 발생할 수 있으므로 굳이 NVL함수 사용할 필요가 없다. 나중에 다중행함수때 알게됨.
 
NULLIF (EXPR1, EXPR2) : EXPR1과 EXPR2가 같으면 NULL 출력, 다르면 EXPR1 출력
SELECT ENAME, EMPNO, MGR, NULLIF(MGR,7698) NUIF
FROM EMP;
// MGR은 매니저 번호인데 사원테이블에서 매니저 번호가 7698과 같으면 NULL 출력, 다르면 기존의 MGR출력
// 다소 헷갈릴수 있다.
 
COALESCE(EXPR1, EXPR2, ....) : 인자는 한정되어 있지않고 임의의 개수 EXPR에서 NULL이 아닌 최초의 EXPR을 출력한다. 모든 EXPR이 NULL 이면 NULL 출력
Colored by Color Scripter