HighLight in your life~

*SQL Functions(10g) -  Aggregate Functions

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions001.htm#sthref912

1. AVG(N) - N의 평균을 구하여 반환한다. 단, NULL인 값은 제외하고 계산한다.

SQL> SELECT EMPNO, SAL, COMM FROM EMP;

SQL> SELECT AVG(COMM)         AS AVG1,  --  커미션이 있는 사원들의 평균 커미션
                 AVG(NVL(COMM, 0))  AS AVG2   --  사원 1인당 평균 커미션
         FROM EMP;

Analytic Example

SQL> SELECT manager_id, last_name, hire_date, salary,
   AVG(salary) OVER (PARTITION BY manager_id ORDER BY hire_date
   ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS c_mavg
   FROM employees;

2. COLLECT(N) -

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions024.htm#i1271564

  SQL> create or replace procedure proc1 (d EMP.DEPTNO%TYPE)
  is
    TYPE emp_sal_table_type IS TABLE OF emp.sal%type
      INDEX BY PLS_INTEGER;

    emp_sal_tab emp_sal_table_type;
  begin
    select sal BULK COLLECT INTO emp_sal_tab
    from emp
    where deptno = d
    order by sal desc;

    p.p(emp_sal_tab.first);
    p.p(emp_sal_tab.last);

    for i in emp_sal_tab.first .. emp_sal_tab.last loop
      p.p(emp_sal_tab(i));
    end loop;
  end;
  /

  SQL> show errors

  SQL> exec proc1(10)
  SQL> exec proc1(30)

3. CORR(M, N) -

CORR

returns the coefficient of correlation of a set of number pairs. You can use it as an aggregate or analytic function.

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions028.htm#i82637

Analytic Example

SQL> SELECT employee_id, job_id,
TO_CHAR((SYSDATE - hire_date) YEAR TO MONTH ) "Yrs-Mns", salary,
CORR(SYSDATE-hire_date, salary)
OVER(PARTITION BY job_id) AS "Correlation"
FROM employees
WHERE department_id in (50, 80)
ORDER BY job_id, employee_id;

4. COUNT(N) - N의 총 개수를 계산한다. 단, NULL은 제외한다.

SQL>  drop table t1 purge;

SQL> create table t1 (col1 number);
SQL>  insert into t1 values (1000);SQL>
SQL> insert into t1 values (1000);
SQL> insert into t1 values (2000);
SQL> insert into t1 values (2000);
SQL> insert into t1 values (null);
SQL> insert into t1 values (null);

SQL> commit;

SQL> select col1, col1, col1 from t1;

SQL> select count(*), count(col1), count(distinct col1) from t1;

5. COVAR_POP(P1, P2) - COVAR_POP returns the population covariance of a set of number pairs. You can use it as an aggregate or analytic function.

COVAR_POP(P1, P2) = (SUM(P2*P1)- SUM(P2) * SUM(P1) / N) /N

population : 집단

covariance : 공분산

 - http://terms.naver.com/entry.nhn?cid=1839&docId=75608&mobile&categoryId=1841

 - http://terms.naver.com/entry.nhn?cid=1839&docId=977687&mobile&categoryId=1841

SQL> SELECT job_id,
   COVAR_POP(SYSDATE-hire_date, salary) AS covar_pop,
   COVAR_SAMP(SYSDATE-hire_date, salary) AS covar_samp
   FROM employees
   WHERE department_id in (50, 80)
   GROUP BY job_id;

6. COVAR_SAMP(P1, P2) = (SUM(P1*P2) - SUM(P1) * SUM(P2) / N)/(N-1) 

COVAR_SAMP returns the sample covariance of a set of number pairs. You can use it as an aggregate or analytic function.

7. CUME_DEST - CUME_DIST calculates the cumulative distribution of a value in a group of values. The range of values returned by CUME_DIST is >0 to <=1. Tie values always evaluate to the same cumulative distribution value.

SQL> SELECT CUME_DIST(15500, .05) WITHIN GROUP
   (ORDER BY salary, commission_pct) "Cume-Dist of 15500"
   FROM employees;

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions035.htm#i82886

8. DENSE_RANK(M, N) - DENSE_RANK computes the rank of a row in an ordered group of rows and returns the rank as a NUMBER. The ranks are consecutive integers beginning with 1. The largest rank value is the number of unique values returned by the query. Rank values are not skipped in the event of ties. Rows with equal values for the ranking criteria receive the same rank. This function is useful for top-N and bottom-N reporting.

This function accepts as arguments any numeric datatype and returns NUMBER.

1) RANK() VS DENSE_RANK()

SQL> SELECT EMPNO, SAL,
         RANK() OVER(ORDER BY SAL DESC) RANK_T,
         DENSE_RANK() OVER(ORDER BY SAL DESC) DENSE_RANK_T
         FROM EMP;

        -- RANK() 함수는 2등이 2명이면 다음 등수는 4등이 됩니다.

        -- DENSE_RANK() 함수는 2등이 2명이면 다음 등수는 3등이 됩니다.

        -- RANK() 함수는 중간에 빠지는 등수가 있지만 DENSE_RANK() 함수는 중간에 빠지는 등수가 없습니다.

2) 상위 5명 추출

SQL> SELECT * FROM
        (SELECT EMPNO, ENAME, SAL, RANK() OVER(ORDER BY SAL DESC) RANK FROM EMP)
        WHERE RANK < 6;

SQL> SELECT * FROM
        (SELECT EMPNO, ENAME, SAL, RANK() OVER(ORDER BY SAL DESC) RANK FROM EMP)
        WHERE ROWNUM < 6;

3)

SQL> SELECT DENSE_RANK(15500, .05) WITHIN GROUP
   (ORDER BY salary DESC, commission_pct) "Dense Rank"
   FROM employees;

9. FIRST & LAST - FIRST와 LAST 함수는 SORTING된 집합에서 첫번째 혹은 마지막 결과를 얻는데 사용된다.

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions056.htm#i1000901

SQL> SELECT department_id,
         MAX(salary) KEEP (DENSE_RANK LAST ORDER BY SALARY) "최대값",
         MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY SALARY) "최소값"
         FROM employees
         GROUP BY department_id;

SQL> SELECT DEPARTMENT_ID, 
         MAX(EMPLOYEE_ID||' '||LAST_NAME) KEEP (DENSE_RANK FIRST ORDER BY SALARY DESC) 최대급여,
         MAX(SALARY) 최대값,
         MIN(EMPLOYEE_ID||' ' ||LAST_NAME) KEEP (DENSE_RANK LAST ORDER BY SALARY DESC) 최소급여,
         MIN(SALARY) 최소값
         FROM EMPLOYEES
         GROUP BY DEPARTMENT_ID;

10. GROUP_ID - GROUP_ID 함수는 지정된 GROUP BY 결과로 부터 중복된 그룹을 구별한다. 질의 결과에서 중복된 GROUPING을 필터링 하는 것에 유용하다. 유일한 중복 그룹을 식별하기 위해 ORACLE NUMBER를 반환한다. GROUP_ID는 GROUP BY clause를  포함한 SELECT statement에 적용 가능하다. 만약 N이 특정 GROUPING에서 중복이 존재하면, GROUP_ID는 0~(N-1) 범위의 값을 반환한다.

1) http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions061.htm#i1002035

2) http://www.oracle-base.com/articles/misc/rollup-cube-grouping-functions-and-grouping-sets.php

- ROLLUP, CUBE, GROUPING Functions and GROUPING SETS, GROUP_ID

3) http://etutorials.org/SQL/Mastering+Oracle+SQL/Chapter+13.+Advanced+Group+Operations/13.3+The+GROUPING_ID+and+GROUP_ID+Functions/

- GROUP BY CUBE, GROUP_ID

4) http://blog.naver.com/PostView.nhn?blogId=agoodman_jjk&logNo=130092652199&categoryNo=25&viewDate=&currentPage=1&listtype=0

5) http://neucareer.tistory.com/50

6) http://radiocom.kunsan.ac.kr/lecture/oracle/function/GROUP_ID.html

11. GROUPING(Expr) - Expr의 Value가 NULL이면 1을 NOT NULL이면 0을 반환한다.

                    SELECT 한 ROW에서 NULL 값을 유무를 확인 할 때 사용한다.

http://www.statwith.pe.kr/ORACLE/functions054.htm

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions062.htm#i77498

SQL> SELECT DEPTNO, 
                        JOB, 
                        SUM(SAL), 
                        GROUPING(DEPTNO) AS DEPT_G,
                        GROUPING(JOB) AS JOB_G
          FROM EMP
          GROUP BY ROLLUP(DEPTNO, JOB)
          ;

SQL> SELECT DECODE(GROUPING(department_name), 1, 'All Departments',
   department_name) AS department,
   DECODE(GROUPING(job_id), 1, 'All Jobs', job_id) AS job,
   COUNT(*) "Total Empl", AVG(salary) * 12 "Average Sal"
   FROM employees e, departments d
   WHERE d.department_id = e.department_id
   GROUP BY ROLLUP (department_name, job_id);

12. GROUPING_ID(Expr) - Expr의 GROUPING 비트 벡터에 대응하는 수치를 반환한다. GROUPING_ID는 ROLLUP, CUBE, GROUPING 함수와 같은 GROUP BY 확장 기능을 포함한 SELECT 문장에서 적용할 수 있다. 많은 GROUP BY 표현을 가지는 쿼리에서, 많은 GROUPING 함수를 필요로 하는 특정한 행의 GROUP BY 레벨을 지정하기 위해서는 복잡한 쿼리가 요구된다. GROUPING_ID는 이러한 경우 유용한다.

 GROUPING_ID 함수는 다수의 GROUPING 함수의 결과와 비트 벡터(0과 1의 문자열)를 연결하는 것과 동일핟. GROUPING_ID를 이용하여서 다수의 GROUPING 함수를 사용하지 않고서, 표한하기 위한 행의 필터 조건이 간단해 진다. 행 필터링은 원하는 행잉 GROUPING_ID=N의 단일 조건으로 정의 될 수 있어서 더 쉬워진다. 단일 테이블에서 다수의 집계의 레벨을 저장할 때 유용하다.

http://www.statwith.pe.kr/ORACLE/functions055.htm

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions063.htm#i1001964

SQL> SELECT  DEPTNO,
    DECODE(GROUPING_ID(DEPTNO,JOB),3,'총합계',1,'부서합계',JOB) AS JOB,
    DECODE(GROUPING_ID(DEPTNO,JOB,EMPNO),1,'직급소계',EMPNO) AS EMPNO,
    DECODE(GROUPING_ID(DEPTNO,JOB,EMPNO),0,ENAME,COUNT(*)||'명') AS ENAME,
    MGR,
    SUM(SAL) AS SAL,
    SUM(COMM) AS COMM
FROM EMP
GROUP BY ROLLUP (DEPTNO,JOB,(EMPNO,ENAME,MGR))

;

13. VARIANCE(Expr) - Exprt의 분산(VARIANCE) 값을 반환한다.

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions212.htm#i89144

1) Aggregate Example

SQL> SELECT VARIANCE(SALARY)  AS "Variance" FROM EMPLOYEES;

2) Analytic Example

SQL> SELECT LAST_NAME, SALARY, VARIANCE(SALARY) OVER (ORDER BY HIRE_DATE)  AS "Variance" FROM EMPLOYEES;

14. MAX(N) - N의 최대값을 반환한다.

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions085.htm#i89072

1) Aggregate Example
SQL> SELECT MAX(salary) AS "최대" FROM employees;

2) Analytic Example
SQL> SELECT manager_id, last_name, salary,
   MAX(salary) OVER (PARTITION BY manager_id) AS "메니져별 최대"
   FROM employees;

15. MIN(N) -  N의 최소값을 반환한다.

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions087.htm#i1280029

SQL> SELECT MIN(hire_date) AS  "Earliest" FROM employees;

SQL> SELECT manager_id, last_name, hire_date, salary,
   MIN(salary) OVER(PARTITION BY manager_id ORDER BY hire_date
   RANGE UNBOUNDED PRECEDING) AS p_cmin
   FROM employees;

16. SUM(N)- N의 합계을 반환한다.

1) Aggregate Example
SQL> SELECT SUM(salary) "Total"
         FROM employees;

2) Analytic Example

SQL> SELECT manager_id, last_name, salary,
         SUM(salary) OVER (PARTITION BY manager_id ORDER BY salary
         RANGE UNBOUNDED PRECEDING) l_csum
         FROM employees;

17. MEDIAN(Expr) - Expr의 중앙값을 반환한다. 데이터를 크기 순서로 나열할 때 가장 중앙에 위치하게 되는 데이터 값. 자료의 개수 n이 홀수이면 (n+1)/2번째 값이 중앙값이고, 짝수이면 n/2번째 값과 (n/2)+1번째 값의 평균값이 중앙값이 된다.(네이버 IT용어사진)

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions086.htm#i1279886

http://terms.naver.com/entry.nhn?cid=80&docId=512281&mobile&categoryId=80

http://terms.naver.com/entry.nhn?cid=2906&docId=1619660&mobile&categoryId=2906

SQL> SELECT department_id, MEDIAN(salary)
         FROM employees
         GROUP BY department_id;

SQL> SELECT manager_id, employee_id, salary,
         MEDIAN(salary) OVER (PARTITION BY manager_id) "Median by Mgr"
         FROM employees;

18. RANK(Expr) - Expr의 순위를 반환한다.

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions123.htm#i1269223

1) Aggregate Example
SQL> SELECT RANK(15500, 0.5) WITHIN GROUP
            (ORDER BY SALARY, COMMISSION_PCT) "Rank"
            FROM EMPLOYEES

SQL> SELECT RANK(15500) WITHIN GROUP
            (ORDER BY SALARY DESC) "Rank of 155500"
            FROM EMPLOYEES

2) Analytic Example

SQL> SELECT department_id, last_name, salary, commission_pct,
           RANK() OVER (PARTITION BY department_id
           ORDER BY salary DESC, commission_pct) "Rank"
           FROM employees WHERE department_id = 90;

19. PERCNET_RANK(Expr) - PERCENT_RANK 함수는 CUME_DIST(cumulative distribution) 함수와 비슷하다. PERCENT_RANK의 반환값은 0과 1사이다. 첫번째 집합의 PERCENT_RANK의 값은 0이다.

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions109.htm#i1043951

1) Aggregate Example
SQL> SELECT PERCENT_RANK(15000, .05) WITHIN GROUP
   (ORDER BY salary, commission_pct) "Percent-Rank"
   FROM employees;

2) Analytic Example

SQL> SELECT department_id, last_name, salary,
   PERCENT_RANK()
      OVER (PARTITION BY department_id ORDER BY salary DESC) AS pr
   FROM employees
   ORDER BY pr, salary;

20. STDDEV(Expr) - Exprt의 표본표준편차(the sample standard deviation)을 반환한다. STTDEV는 1건의 데이타를 갖고 있으면 0을 반환하고 STDDEV_SAMP은 NULL을 반환한다.

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions159.htm#i89108

1) Aggregate Example
SQL> SELECT STDDEV(salary) "Deviation"
   FROM employees;

2) Analytic Example

SELECT last_name, salary,
   STDDEV(salary) OVER (ORDER BY hire_date) "StdDev"
   FROM employees 
   WHERE department_id = 30;

-----------------------------------------------------------------------------------------------------------

21. GROUPING SETS

http://radiocom.kunsan.ac.kr/lecture/oracle/function/GROUPING_SETS.html

22. GROUPING(), ROLLUP, CUBE Study 정리

http://fly32.net/252

*********************************
ANALYTIC FUNCTION 정리해야 함
*********************************

*SQL Functions(10g) - NUL-Related Functions

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions001.htm#sthref912

1. COALESCE(Expr) - Expr List에서 첫번째 NULL이 아닌 값을 반환한다. Expr List의 값이 모두 NULL이면 NULL을 반환한다.

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions023.htm#i1001341

* 아래의 3개는 모두 유사하다.

1) CASE WHEN expr1 IS NOT NULL THEN expr1 ELSE expr2 END

2) COALESCE(expr1, expr2, ..., exprn), for n >= 3

3) CASE WHEN expr1 IS NOT NULL THEN exp1

SQL> SELECT coalesce('','', 'Expr1', 'Expr2') FROM DUAL;

SQL> SELECT ename, comm, sal, coalesce(comm, sal, 0) AS COAL_TEST
FROM emp;

SQL> SELECT last_name, manager_id, commission_pct,
COALESCE(manager_id,commission_pct, -1) comm
FROM employees
ORDER BY commission_pct;

2. LNNVL(Expr) - It provides a concise way to evaluate a condition when one or both operands of the condition may be null. The function can be used only in the WHERE clause of a query. It takes as an argument a condition and returns TRUE if the condition is FALSE or NUKNOWN and FALSE if the condition is TRUE. Oracle Database sometimes uses the LNNVL function internally in this way to rewrite NOT IN conditions as NOT EXISTS conditions.

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions078.htm#i1479295

아래의 1)번과 2)번 결과는 동일하다.

1)

SQL> SELECT ENAME, SAL, COMM, SAL+COMM
FROM EMP
WHERE LNNVL((SAL+COMM) >= 1800)

;

2)

SQL> SELECT ENAME, SAL, COMM, SAL+COMM
FROM EMP
WHERE (SAL+COMM) < 1800 OR (SAL+COMM) IS NULL
;

SQL> SELECT COUNT(*) FROM employees WHERE commission_pct < .2;

SQL> SELECT COUNT(*) FROM employees WHERE LNNVL(commission_pct >= .2);

3. NULLIF(Expr1, Expr2) - Expr1과 Expr2가 같으면 NULL 아니면 Expr1을 반환한다. Expr1에는 NULL이 할당될 수 없다.

CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions102.htm#i1001340

SQL> SELECT e.last_name, NULLIF(e.job_id, j.job_id) "Old Job ID"
   FROM employees e, job_history j
   WHERE e.employee_id = j.employee_id
   ORDER BY last_name;

4. NVL(Expr1, Expr2) - Expr1이 NULL이면 Expr2를 반환하고, Expr1이 NOT NULL이면 Expr1을 반환한다. 

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions105.htm#i91798

SQL> SELECT last_name, NVL(TO_CHAR(commission_pct), 'Not Applicable')
   "COMMISSION" FROM employees;

5. NVL2(Expr1, Expr2, Expr3) - Expr1이 NOT NULL이면 Expr2를 반환한다. 만약 Expr1이 NULL이면 Expr3를 반환한다.

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions106.htm#i91806

SQL> SELECT last_name, salary, commission_pct, NVL2(commission_pct,
   salary + (salary * commission_pct), salary) income
   FROM employees;

*SQL Functions(10g) -  Environment and Identifier Functions

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions001.htm#sthref912

1. SYS_CONTEXT  - 환경에 관련된 정보들을 보여준다.

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions165.htm#i1038176

SQL> SELECT SYS_CONTEXT('USERENV', 'SESSION_USER') FROM DUAL;

ACTION, AUDITED_CURSORID, AUTHENTICATED_IDENTITY, AUTHENTICATION_DATA, AUTHENTICATION_METHOD, BG_JOB_ID, CLIENT_IDENTIFIER, CLIENT_INFO, CURRENT_BIND, CURRENT_SCHEMA, CURRENT_SCHEMAID, CURRENT_SQL, CURRENT_SQLn, CURRENT_SQL_LENGTH, DB_DOMAIN, DB_UNIQUE_NAME, ENTRYID, ENTERPRISE_IDENTITY, FG_JOB_ID, GLOBAL_CONTEXT_MEMORY, GLOBAL_UID, HOST, IDENTIFICATION_TYPE, INSTANCE, INSTANCE_NAME, IP_ADDRESS, ISDBA, LANG, LANGUAGE, MODULE, NETWORK_PROTOCOL, NLS_CALENDAR, NLS_CURRENCY, NLS_DATE_FORMAT, NLS_DATE_LANGUAGE, NLS_SORT, NLS_TERRITORY, OS_USER, POLICY_INVOKER, PROXY_ENTERPRISE_IDENTITY, PROXY_GLOBAL_UID, PROXY_USERID, SERVER_HOST, SERVICE_NAME, SESSION_USER, SESSION_USERID, SESSIONID, SID, STATEMENTID, TERMINAL

2. SYS_GUID - SYS_GUID generates and returns a globally unique identifier (RAW value) made up of 16 bytes. On most platforms, the generated identifier consists of a host identifier, a process or thread identifier of the process or thread invoking the function, and a nonrepeating value (sequence of bytes) for that process or thread.

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions168.htm#i79194

3. SYS_TYPEID

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions169.htm#i1044156

4. UID - UID는 세센 사용자를 중복 없이 구분하기 위해 정수를 반환한다.

SQL> SELECT UID FROM DUAL;

5. USER - 세션 사용자(the user who logged on)의 이름을 반환한다.(데이터 타입은 VARCHAR2이다.)

SQL> SELECT USER, UID FROM DUAL; 

6. USERENV - 현재 세션의 정보를 반환한다. 이러한 정보들을 통해 application-specific audit trail table를 작성하거나 현재 세션의 determining the language-specific characters를 알 수 있다.

SQL> SELECT USERENV('LANGUAGE') AS "Lang",
--USERENV('ENTRYID') "ENTRYID",
--USERENV('ISDBA') "ISDBA",
USERENV('LANG') AS  "LANG",
USERENV('SESSIONID') "SESSIONID"
--USERENV('TERMINAL') "TERMINAL"
FROM DUAL;

*SQL Functions(10g) - Collection Functions

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions001.htm#sthref912

1. CARDINALITY
2. COLLECT
3. POWERMULTISET
4. POWERMULTISET BY CARDINALITY
5. SET

*SQL Functions(10g) - Hierarchical Functions

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions001.htm#sthref912

1. SYS_CONNECT_BY_PAHT

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions001.htm#sthref912

1. ADD_MONTHS
2. CURRENT_DATE
3. CURRENT_TIMESTAMP
4. DBTIMEZONE
5. EXTRACT (datetime)
6. FROM_TZ
7. LAST_DAY
8. LOCALTIMESTAMP
9. MONTHS_BETWEEN
10. NEW_TIME
11. NEXT_DAY
12. ROUND (date)
13. SESSIONTIMEZONE
14. SYSDATE
15. SYSTIMESTAMP
16. TO_CHAR (date)
17. TO_TIMESTAMP
18. TRUNC (date)
19. TZ_OFFSET

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions001.htm#sthref912

SQL Functions(10g) - Character Functions

1. CHR(N) - 바이너리 값 N을 VARCHAR2 값으로 변환한 값을 반환한다.

SQL> SELECT CHR(72)||CHR(69)||CHR(76)||CHR(76)||CHR(79) AS "HELLO" FROM DUAL;

A(65), B(66), C(67), D(68), E(69), F(70), G(71), H(72), I(73)
J(74), K(75), L(76), M(77), N(78), O(79), P(80), Q(81), R(82)
S(83), T(84), U(85), V(86), W(87), X(88), Y(89), Z(90)

2. CONCAT(S1, S2) - 문자열 S1과 문자열 S2를 연결하여 합친다.

SQL> SELECT CONCAT('Hello',' World') AS "Hello World" FROM DUAL;

SQL> SELECT CONCAT(CONCAT(LAST_NAME, '''s salary is '), SALARY ) AS "Salary"  FROM EMPLOYEES;
 

3. INITCAP(S) - 문자열 S에서 각 단어에서 시작하는 첫 문자를 대문자로 시작한다.

SQL> SELECT INITCAP('hello world') AS "Hello World", INITCAP('HELLO WORLD') AS "HELLO WORLD", INITCAP('hElLO wOrLd') AS "hElLO wOrLd" FROM DUAL;

4.  LOWER(S) - 문자열 S의 알파벳을 모두 소문자로 변환하여 반환한다.

SQL> SELECT LOWER('HELLO WORLD') AS "HELLO WORLD" FROM DUAL;

SQL> SELECT LAST_NAME AS "LAST_NAME", LOWER(LAST_NAME) AS "LOWERCASE" FROM EMPLOYEES WHERE ROWNUM < 3;

5. UPPER - 문자열 S의 알파벳을 모두 대문자로 변환하여 반환한다.

SQL> SELECT UPPER('hello world') AS "hello world" FROM DUAL;

SQL> SELECT LAST_NAME AS "LAST_NAME", UPPER(LAST_NAME) AS "UPPER" FROM EMPLOYEES WHERE ROWNUM < 3;

6. LPAD(S, N, P) - 문자열 S의 길이가 N보다 작으면 왼쪽에 P문자를 N-LENGTH(S) 만큼 추가한다.

SQL> SELECT LPAD('Star',7,'*') AS "Star 7", LPAD('Star',4,'*') AS "Star 4", LPAD('Star',3,'*') AS "Star 3" FROM DUAL;

7. RPAD(S, N, P) - 문자열 S의 길이가 N보다 작으면 오른쪽에 P문자를 N-LENGTH(S) 만큼 추가한다.

SQL> SELECT RPAD('Star',7,'*') AS "Star 7", RPAD('Star',4,'*') AS "Star 4", RPAD('Star',3,'*') AS "Star 3" FROM DUAL;

SQL> SELECT LAST_NAME, RPAD(' ', SALARY/1000/1, '*') "SALARY" FROM EMPLOYEES
ORDER BY LAST_NAME;

8. RELPACE(S, S1, S2) - 문자열 S에서 문자 혹은 문자열 S1을 찾아 S2로 바꾼 결과를 반환한다.

SQL> SELECT REPLACE('There is no blank!.', ' ', '*') "Changes" FROM DUAL;

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

9. TRIM - LEADING(왼쪽 제거), TRAILING(오른쪽 제거), BOTH(왼쪽, 오른쪽 제거) 사용 가능

              제거 해야 할 문자가 없으면, blank space 제거, 그리고 BOTH(양쪽)에서 blank space 제거

SQL> SELECT TRIM(LEADING '*' FROM '***Hello***World***') AS "LEADING" FROM DUAL;

SQL> SELECT TRIM(TRAILING '*' FROM '***Hello***World***') AS "TRAILING" FROM DUAL;

SQL> SELECT TRIM(BOTH '*' FROM '***Hello***World***') AS "BOTH" FROM DUAL;

SQL> SELECT TRIM('   Hello   World   ') AS "BOTH" FROM DUAL;

10. LTRIM(S, S1) - 문자열 S에서 문자 혹은 문자열 S1을 왼쪽에서 모두 지운 값을 반환한다.

                          제거 해야 할 문자가 없으면, 왼쪽만 blank space 제거

SQL> SELECT '***Hello***World***' AS "Hello World", LTRIM('***Hello***World***', '*') AS "LTRIM" FROM DUAL;

SQL> SELECT '   Hello   World   ' AS "Hello World", LTRIM('   Hello   World   ') AS "LTRIM" FROM DUAL;

11. RTRIM(S, S1) - 문자열 S에서 문자 혹은 문자열 S1을 오른쪽에서 모두 지운 값을 반환한다.

                          제거 해야 할 문자가 없으면, 오른쪽만 blank space 제거

SQL> SELECT '***Hello***World***' AS "Hello World", RTRIM('***Hello***World***', '*') AS "RTRIM" FROM DUAL;

SQL> SELECT '   Hello   World   ' AS "Hello World", RTRIM('   Hello   World   ') AS "RTRIM" FROM DUAL;

SQL> SELECT RTRIM('BROWNING: ./=./=./=./=./=.=','/=.') "RTRIM example" FROM DUAL;

12. SUBSTR(S, M, N) - 문자열 S를 M번째 CHAR부터 N의 길이로 잘라낸다.

SQL> SELECT SUBSTR('Hello World', 1, 5) AS "Hello", SUBSTR('Hello World', 7, 5) AS "World", SUBSTR('Hello World', -5, 5) AS "Hello"  FROM DUAL;

SQL>SELECT SUBSTR('Hello World', 0, 1) FROM DUAL;
SQL>SELECT SUBSTR('Hello World', 0, 2) FROM DUAL;
SQL>SELECT SUBSTR('Hello World', 1, 2) FROM DUAL;

SQL>SELECT SUBSTR('Hello World', 0) FROM DUAL;
SQL>SELECT SUBSTR('Hello World', 1) FROM DUAL;
SQL>SELECT SUBSTR('Hello World', 5) FROM DUAL;

13. INSTR(S,S1, M, N) - 문자열 S에서 문자 혹은 문자열 S1의 시작 위치를 반환한다.
                                 단, 시작 위치는 M이고 N번째 S1의 시작 위치를 반환한다.

SQL> SELECT INSTR('HELLO WORLD','O', 1, 1) "Instring1", INSTR('HELLO WORLD','O', 1, 2) "Instring2", INSTR('HELLO WORLD','O', 1, 3) "Instring3" FROM DUAL;

SQL>SELECT INSTR('HELLO WORLD','O', -1, 1) "Instring" FROM DUAL;

SQL>SELECT INSTR('HELLO WORLD','O', -7, 1) "Instring" FROM DUAL;

14. TRANSLATE(S, S1, R) - 문자열 S에서 문자 S1을 찾아 R로 바꾼 값을 반환한다.

SQL> SELECT TRANSLATE('0123456789', '0123456789', 'helloworld') FROM DUAL;
SQL> SELECT REPLACE('0123456789', '1', 'x') FROM DUAL;

SQL> SELECT TRANSLATE('A0B1CDEF2G3H9', '+.0123456789', ' ') FROM DUAL;
SQL> SELECT TRANSLATE('A0B1CDEF2G3H9', '1234567890', ' ') FROM DUAL;

SQL> SELECT NVL(LENGTH(TRANSLATE('A0B1CDEF2G3H9', '+.0123456789', ' ')), 0) FROM DUAL;
SQL> SELECT NVL(LENGTH(TRANSLATE('123456789', '+.0123456789', ' ')), 0) FROM DUAL;

SQL> SELECT TRANSLATE('123전화번호', '0123456789'||'전화번호', 'ABCDEFGHIJKLMN') FROM DUAL;

15. LENGTH(S) - 문자열 S의 길이를 반환한다.

SQL> SELECT LENGTH('HelloWorld') FROM DUAL;

SQL>SELECT LENGTH(HelloWorld) FROM DUAL;

SQL>SELECT LENGTH('01234') FROM DUAL;
SQL>SELECT LENGTH(01234) FROM DUAL;

16. REGEXP_REPLACE

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions130.htm#i1305521

SQL> SELECT
  REGEXP_REPLACE(PHONE_NUMBER,
                 '([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})',
                 '(\1) \2-\3') "REGEXP_REPLACE"
  FROM EMPLOYEES;

SQL> SELECT PHONE_NUMBER FROM EMPLOYEES;

SQL> SELECT
  REGEXP_REPLACE(COUNTRY_NAME, '(.)', '\1 ') "REGEXP_REPLACE"
  FROM COUNTIRES;

17. REGEXP_SUBSTR

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions131.htm#i1239858

18. SOUNDEX(S) - 문자열 S와 소리가 비슷한 것들 나타낸다.

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions148.htm#i78853

SQL> SELECT LAST_NAME, FIRST_NAME
         FROM HR.EMPLOYEES
         WHERE SOUNDEX(LAST_NAME) = SOUNDEX('SMYTHE');

SQL Functions(10g) - Numeric Functions

1. ABS(N) : N의 절대 값을 반환한다.

SQL> SELECT ABS(3) AS "양수", ABS(-3) AS "음수" FROM DUAL;

         양수          음수

---------- ----------

             3               3

2. CEIL(N) : N 이상의 최초의 정수를 반환한다.

SQL> SELECT CEIL(0.9), CEIL(1.3), CEIL(1.5), CEIL(1.7), CEIL(1.9), CEIL(2.1)  FROM DUAL;

 CEIL(0.9)  CEIL(1.3)  CEIL(1.5)  CEIL(1.7)  CEIL(1.9)  CEIL(2.1)
---------- ---------- ---------- ---------- ---------- ----------
         1          2          2          2          2          3

3. FLOOR(N) : N 이하의 최초의 정수를 반환한다.

SQL> SELECT FLOOR(0.9), FLOOR(1.3), FLOOR(1.5), FLOOR(1.7), FLOOR(1.9), FLOOR(2.1)  FROM DUAL;

FLOOR(0.9) FLOOR(1.3) FLOOR(1.5) FLOOR(1.7) FLOOR(1.9) FLOOR(2.1)                                                                                                                                      
---------- ---------- ---------- ---------- ---------- ----------

0          1          1          1          1          2

4. MOD(M,N) : M을 N으로 나누었을 때의 나머지를 반환한다.

SQL> SELECT MOD(10,1), MOD(10,2), MOD(10,3), MOD(10,4), MOD(10,5) FROM DUAL;

MOD(10,1)  MOD(10,2)  MOD(10,3)  MOD(10,4)  MOD(10,5)
---------- ---------- ---------- ---------- ----------
         0          0          1          2          0

5. POWER(M,N) : M의 N 승을 반환한다.

SQL> SELECT POWER(2,1), POWER(2,2), POWER(2,3) FROM DUAL;

POWER(2,1) POWER(2,2) POWER(2,3)                                                                                                                                                                       
---------- ---------- ----------

 2          4          8    

6. ROUND(M,N) : M을 소수점 N이 양수일 때는 소수 'N+1' 자리에서 반올림한 값을 반환한다.
                       단, N이 음수일 때는 -(N) 자리에서 반올림한다. -(N) 미만의 자리수는 버린다.

SQL> SELECT ROUND(143.456,1), ROUND(143.456,2), ROUND(143.456,3), ROUND(143.456,-1), ROUND(143.456,-2) FROM DUAL; 

ROUND(143.456,1) ROUND(143.456,2) ROUND(143.456,3) ROUND(143.456,-1) ROUND(143.456,-2)                                                                                                                 
---------------- ---------------- ---------------- ----------------- -----------------              

143.5                  143.46               143.456                    140               100 

7. SQRT(N) : '루트 N'을 의미한다.

SQL> SELECT SQRT(25), SQRT(36) FROM DUAL;

  SQRT(25)   SQRT(36)                                                                                                                                                                                  

---------- ----------

 5          6

8. TRUNC(M,N) : M을 소수점 N이 양수일 때는 소수 'N+1' 이하의 자리를 절삭한 값을 반환한다.
                       단, N이 음수일 때는 -(N) 이하의 자리를 절산한 값을 반환한다.

SQL> SELECT TRUNC(143.456,1), TRUNC(143.456,2), TRUNC(143.456,3), TRUNC(143.456,-1), TRUNC(143.456,-2) FROM DUAL;

TRUNC(143.456,1) TRUNC(143.456,2) TRUNC(143.456,3) TRUNC(143.456,-1) TRUNC(143.456,-2)

---------------- ---------------- ---------------- ----------------- -----------------

143.4                  143.45                 143.456               140                      100

9. WIDTH_BUCKET(P, M, N, L) : P컬럼 값을 기준으로 L개의 그룹으로 나눈다. 범위는 M부터 N사이의 값이 기준이다.

예를 들면, 아래의 WIDTH_BUCKET(SAL, 100, 5000, 5)의 집단은 아래와 같다.
1집단: 100~1000
2집단: 1001~2000
3집단: 2001~3000
4집단: 3001~4000
5집단: 4001~5000

SQL> SELECT EMPNO, SAL, WIDTH_BUCKET(SAL, 100, 5000, 5) FROM EMP;