[SQL] Oracle Built-in Function

1. 숫자함수

• ABS(n) : ABS함수는 절대값을 계산하는 함수입니다.
• CEIL(n) : CEIL함수는 주어진 값보다는 크지만 가장 근접하는 최소값을 구하는 함수입니다.
• EXP(n) : EXP함수는 주어진 값의 e의 승수를 나타냅니다.  e는 2.171828183..입니다.
• FLOOR(n) : FLOOR함수는 주어진 값보다 작거나 같은 최대 정수값을 구하는 함수입니다. (CEIL 함수와 비교해 보세요. )
• LN(n) : LN함수는 주어진 값의 자연로그 값을 반환합니다.
• MOD(m, n) : MOD함수는 m을 n으로 나누어 남은 값을 반환한다. n이 0일 경우 m을 반환합니다.
• POWER(m, n) : POWER함수는 m의 n승 값을 계산합니다.
• ROUND(n, [m]) : ROUND함수는 n값의 반올림을 하는 함수로 m은 소숫점 아래 자릿수를 나타낸다.
• SIGN(n) : SIGN함수는 n<0일 경우 -1DFM N=0일 경우 0을 N>0일 경우 1을 반환합니다.
• SQRT(n) : SQRT함수는 n값의 루트값을 계산한다. n은 양수여야 합니다.
• TRUNC(n, m) : TRUNC함수는 n값을 m 소숫점 자리로 반내림한 값을 반환합니다. (ROUND 함수와 비교해 보세요.)

2. 문자열 처리함수

• CONCAT(char1, char2) : CONCAT 함수는 Concatenation의 약자로 두 문자를 결합하는 역할을 합니다. "||" 연산자와 같은 역할을 합니다.
• INITCAP(char) : 주어진 문자열의 첫 번째 문자를 대문자로 변환시켜 줍니다.
• LOWER(char) : 문자열을 소문자로 변환 시켜 줍니다.
• UPPER(char) : 문자열을 대문자로 변환 시켜 줍니다.
• LPAD(char1, n [,char2]) :왼쪽에 문자열을 끼어 놓는 역할을 합니다. n은 반환되는 문자열의 전체 길이를 나타내며, char1의 문자열이 n보다 클 경우 char1을 n개 문자열 만큼 반환합니다.
• RPAD(char1, n [,char2]) : LPAD와 반대로 오른쪽에 문자열을 끼어 놓는 역할을 합니다.
• SUBSTR(char, m ,[n]) : SUBSTR함수를 이용하여 m 번째 자리부터 길이가 n개인 문자열을 반환한 합니다. m이 음수일 경우에는 뒤에서 M번째 문자부터 반대 방향으로 n개의 문자를 반환합니다.
• LENGTH(char1) : 문자열의 길이를 리턴 합니다.
• REPLACE(char1, str1, str2) : REPLACE는 문자열의 특정 문자를 다른 문자로 변환 합니다.
• INSTR : 문자열이 포함되어 있는지를 조사하여 문자열의 위치를 반환합니다. 지정한 문자열이 발견되지 않으면 0이 반환 됩니다.
• TRIM : 특정한 문자를 제거 합니다. 제거할 문자를 입력하지 않으면 기본적으로 공백이 제거 됩니다.  리턴값의 데이터타입은 VARCHAR2 입니다.

 -- 두개의 문자를 연결한다.

SQL> SELECT CONCAT('Oracle', ' Korea') NAME FROM dual;

-- 문자좌측에 *를 붙인다. 문자 길이는 10개로 제한

SQL> SELECT LPAD('JUNG-SICK', 10, '*') NAME FROM dual;

-- 문자우측에 *를 붙인다. 문자 길이는 11개로 제한

SQL> SELECT RPAD('JUNG-SICK', 11, '*') NAME FROM dual;

-- 앞에서부터, 뒤에서부터 예제...

SQL> SELECT SUBSTR('JUNG-SICK', 3, 3) NAME FROM dual;

SQL> SELECT SUBSTR('JUNG-SICK', -3, 3) NAME FROM dual;

-- 문자의 길이를 구함

SQL> SELECT LENGTH('JUNG-SICK') TEST FROM dual;

-- 대소문자 구별합니닷!!

SQL> SELECT REPLACE('JACK and JUE','J','BL') "Changes" FROM dual;

SQL> SELECT REPLACE('JACK and JUE','j','BL') "Changes" FROM dual;

-- 지정한 문자 OK가 발견되지 않아서 0이 반환 됩니다.

SQL> SELECT INSTR('CORPORATE FLOOR','OK')  "Instring" FROM dual;

-- OR이 있는 위치 2를 반환 합니다. 왼쪽부터 비교를 한다는 것을 알 수 있습니다.

SQL> SELECT INSTR('CORPORATE FLOOR','OR')  "Instring" FROM dual;

-- 0을 제거 합니다.

SQL> SELECT TRIM(0 FROM 0009872348900)  "TRIM Example" FROM dual;

3. 날짜 처리함수

• LAST_DAY(d) : LAST_DAY함수는 달의 마지막 날의 날짜를 반환합니다.
• ADD_MONTHS(a, b) : ADD_MONTHS 함수는 a의 날짜에 b의 달을 더한 값을 반환 합니다.
• MONTH_BETWEEN(a1, a2) : MONTH_BETWEEN은 a1과 a2 사이의 달의 수를 NUMBER형 타입으로 반환 합니다.
• ROUND(d[,F]) : ROUND 함수는 F에 지정된 단위로 반올림 합니다. F가 연도라면 연도 단위로 반올림 합니다.

 -- 오늘과 이달의 마지막날 반환

SQL> SELECT SYSDATE TODAY, LAST_DAY(SYSDATE) LASTDAY FROM dual;

-- 오늘에서 3개월을 더한 후, RRRR/MM/DD 형식으로 반환

SQL> SELECT TO_CAHR(ADD_MONTHS(SYSDATE,3),'YYYY/MM/DD') "date" FROM dual;

-- 두 날짜 사이의 개월수를 반환

SQL> SELECT MONTHS_BETWEEN(TO_DATE('2000/06/05') , TO_DATE('2000/09/23')) "Date" FROM dual;

-- '1998/09/11' 의 년도인 1998을 반환

SQL> SELECT ROUND(TO_DATE('1998/09/11'), 'YEAR') FROM dual;

4. 변환함수

• TO_CHAR : TO_CHAR함수는 DATE형, NUMBER형을 VARCHAR2형으로 바꿔줍니다.
• TO_DATE : TO_DATE함수는 CHAR, VARCHAR2형을 DATE 타입으로 변환합니다.
• TO_NUMBER : TO_NUMBER함수는 CHAR, VARCHAR2의 데이터 타입을 숫자형식으로 변환합니다.

-- 오늘의 월만 반환

SQL> SELECT TO_CHAR(sysdate, 'MONTH') CHARTEST FROM dual;

-- 문자형이 날짜형으로 변환

SQL> SELECT TO_DATE('2000/06/16','YYYY/MM/DD') FROM dual;

-- '1210616'의 문자열이 숫자형으로 변환

SQL> SELECT TO_NUMBER('1210616') FROM dual;

5. 기타함수

• NVL
• NVL 함수는 NULL값을 다른 값으로 바꿀 때 쓰입니다.
• 모든 데이터 타입에 적용 가능합니다.
• 전환되는 값의 데이터 타입을 일치시켜야 합니다.
• DECODE
• DECODE 함수는 데이터 들을 다른 값으로 바꾸어 줍니다.
• 형식 DECODE(VALUE, IF1, THEN1, IF2, THEN2...)
• VALUE 값이 IF1일경우에 THEN1값으로 바꾸어 주고 VALUE값이 IF2일경우에는 THEN2값으로 바꾸어 줍니다.
• DUMP : DUMP는 바이트 크기와 해당 데이터 타입 코드를 반환합니다.
• GREATEST : GREATEST함수는 검색값 중에서 가장 큰 값을 반환 합니다.
• LEAST : LEAST함수는 GREATEST함수와 반대로 가장 작은 값을 반환합니다.
• UID : 현재 사용자의 유일한 ID번호를 반환 합니다.
• USER : 현재 오라클을 사용하는 사용자를 VARCHAR2형식으로 반환 합니다.
• USERENV : USERENV 함수는 현재 세션의 환경 정보를 반환합니다.
• ENTRYID : 사용 가능한 Auditing entry Identifier를 반환합니다.
• LABEL : 현재 세션의 Label을 반환합니다.
• LANGUAGE : 현재 세션에서 사용중인 언어와 테리토리 값을 반환합니다.
• SESSIONID : Auditing(감사) Session ID를 반환 합니다.
• TERMINAL : 현재 세션 터미널의 OS ID를 반환 합니다.
• VSIZE : 해당 문자의 BYTE수를 반환 합니다. 해당 문자가 NULL이면 NULL값이 반환 됩니다.

-- 컬럼 comm의 값이 NULL일 경우, 0을 반환합니다.

SQL> SELECT empno, NVL(comm, 0) FROM emp WHERE deptno = 30;

-- 컬럼 deptno의 값이 10이며 'ACCOUNTING'으로, 20이며 'RESEARCH'으로, 30이며 'SALES'으로, 40이며 'OPERATIONS'으로

SQL> SELECT deptno, DECODE(deptno, 10 , 'ACCOUNTING', 20 , 'RESEARCH', 30 , 'SALES', 40 , 'OPERATIONS') FROM emp;

-- 만약에 16대신 8을 넣으면 8진수로, 10를 넣으면 10진수로 변환이 됩니다. 결과값 중, Len은 ename의 해당 byte수 입니다.

SQL> SELECT ename, DUMP(ename, 16) "16진수" FROM emp WHERE ename = 'ALLEN';

-- 가장 큰수/가장 작은수 반환

SQL> SELECT GREATEST(10, 100, 5, -7) FROM dual;

SQL> SELECT LEAST(10, 100, 5, -7) FROM dual;

-- 현재 사용자의 이름과 ID값을 반환

SQL> SELECT USER, UID FROM DUAL;

-- 현재 세션의 환경정보를 반환

SQL> SELECT USERENV('LANGUAGE') FROM dual;

-- 현재 문자의 byte 수를 반환

SQL> SELECT VSIZE(ename), ename FROM emp  WHERE deptno = 30;