[ORACLE] 내장함수

숫자함수

MAX

하나 이상의 행으로부터 최대값을 반환합니다.

USAGE

MAX(char)

EXAMPLE

SELECT MAX(salary)
FROM employees
WHERE JOB_ID='IT_PROG‘;

RESULT

MAX(salary)
--------------
9000

MIN

하나 이상의 행으로부터 최소값을 반환합니다.

USAGE

MIN(char)

EXAMPLE

SELECT MIN(salary)
FROM employees
WHERE JOB_ID='IT_PROG‘;

RESULT

MIN(salary)
--------------
4200

ROUND

COMMENT - M을 소수점 N+1 자리에서 반올림한 결과를 반환합니다.

USAGE

ROUND(M, N)

EXAMPLE

SELECT ROUND(3.141579, 3) “반올림 함수”
FROM DUAL;

RESULT

반올림함수
------------
3.142

POWER

M에 N승 한 결과를 반환합니다.

USAGE

POWER(M, N)

EXAMPLE

SELECT POWER(2, 10) “POWER 함수”
FROM DUAL;

RESULT

POWER(2, 10)
------------
1024

문자함수

LENGTH

문자열의 길이를 반환합니다.

USAGE

LENGTH(CHAR)

EXAMPLE

SELECT LENGTH(‘KOREA TEAM FIGHTING’) “LENGTH 함수”
FROM DUAL;

RESULT

LENGTH 함수
------------
19

LOWER

입력된 문자 값을 소문자로 변환합니다.

USAGE

LOWER(CHAR)

EXAMPLE

SELECT LOWER(‘KOREA TEAM FIGHTING’) “LOWER함수”
FROM DUAL;

RESULT

LOWER 함수
------------
korea team fighting

UPPER

입력된 문자 값을 대문자로 변환합니다.

USAGE

UPPER(CHAR)

EXAMPLE

SELECT UPPER(‘korea team fighting’) “UPPER함수”
FROM DUAL;

RESULT

UPPER 함수
------------
KOREA TEAM FIGHTING

LTRIM

문자열 CHAR 좌측으로 부터 SET으로 지정된 문자를 만날 때 까지 문자를 제거합니다.

USAGE

LTRIM(CHAR [, SET ])

EXAMPLE

SELECT LTRIM('XXAXBAXX','X') "LTRIM" FROM DUAL;

RESULT

LTRIM
--------------
AXBAXX

RTRIM

문자열 CHAR 우측으로부터 SET으로 지정된 문자를 만날 때 까지 문자를 제거합니다.

USAGE

RTRIM(CHAR [, SET ])

EXAMPLE

SELECT RTRIM('XXAXBAXX','X') "RTRIM" FROM DUAL;

RESULT

RTRIM
--------------
XXAXBA

SUBSTR

문자열에서 일부 문자값을 선택적으로 반환합니다.

USAGE

SUBSTR(String, Position, SubString_length)

EXAMPLE

SELECT SUBSTR('ORACLE PROJECT', 1, 3) SUBSTR1,
SUBSTR('ORACLE PROJECT', 4, 5) SUBSTR2,
SUBSTR('ORACLE PROJECT', 10) SUBSTR3
FROM DUAL;

RESULT

SUBSTR1 SUBSTR2SUBSTR3
-----------------------------------------
ORA CLE P OJECT

시간함수

CURRENT_TIME_STAMP / LOCALTIMESTAMP

이 함수는 현재 session의 날짜와 시간 정보를 반환한다.
current_timestamp는 time zone까지 출력되지만,
localtimestamp는 time zone은 출력되지 않는다.

USAGE

CURRENT_TIME_STAMP
localtimestamp [(timestamp_precision)]

EXAMPLE

SELECT CURRENT_TIME_STAMP, LOCALTIMESTAMP, CURRENT_DATE FROM dual;

RESULT

CURRENT_TIME_STAMP LOCALTIMESTAMP
-------------------------------------------------------------------------
07/08/22 19:10:10.578000 +09:00 07/08/22 19:10:10.578000

EXTRACT

특정 날짜/시간 값이나 날짜 값을 가진 표현식으로부터 원하는 날짜 영역을 추출하여 출력한다.

USAGE

EXTRACT ({year|month|day|hour|minute|second|
timezone_hour|timezone_minute|
timezone_region|timezone_abbr}
FROM {datetime_value_expr|interval_value_rxpr})

EXAMPLE

SELECT EXTRACT(year from date '2004-8-2') FROM dual;

RESULT

EXTRACT(year from date '2004-8-2')
----------------------------------
2004

MONTHS_BETWEEN

날짜와 날짜 사이의 기간을 ‘월(month)’로 나타냅니다.
date1이 date2보다 큰 값이다.

USAGE

MONTHS_BETWEEN(date1,date2)

EXAMPLE

SELECT MONTHS_BETWEEN(LAST_DAY(SYSDATE),SYSDATE) "Remain Months"
FROM dual;

RESULT

Remain Months
---------------------------
.290322581

SYSDATE

시스템의 설정된 날짜 값을 반환합니다.

USAGE

SYSDATE

EXAMPLE

SELECT SYSDATE FROM dual;

RESULT

SYSDATE
------------------
07/08/22

변환함수

CAST
데이터 형식이나 collection 형식을 다른 데이터 형식이나 다른 collection 형식으로 변환 한다.

USAGE

CAST(DATA_FORM,COLLECTION_FORM AS DATA_FORM,COLLECTION_FORM)

EXAMPLE

SELECT CAST(current_date as timestamp) FROM dual

RESULT

CAST(current_date as timestamp)
----------------------------------
07/08/22 19:36:14 .000000

TO_CHAR
각종 데이터 타입을 VARCHAR2 데이터 타입으로 변환한다

USAGE

TO_CHAR(DATA_TYPE, DATA_TYPE)

EXAMPLE

SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') "sysdate"
FROM dual;

RESULT

SYSDATE
---------------------------
2007-08-22 19:40:10

분석 및 기타함수

NTILE

순서화된 데이터를 expr에 의해 지정된 bucket의 수로 분할하여, 각 행을 적절한 bucket 번호를 할당, 출력 결과를 사용자가 지정한 그룹 수로 나누어 출력하는 함수

USAGE

NTILE ( expr ) OVER (analytic_clause)

EXAMPLE

SELECT first_name,salary,NTILE(3) OVER (ORDER BY salary DESC)
FROM employees
WHERE job_id = 'IT_PROG';

RESULT

FIRST NAME SALARY NTILE(3) OVER (ORDER BY salary DESC)
-------------------------------------------------------------------
Alexander9000 1
Bruce6000 1
David48002
Valli 4800 2
Diana 42003

COUNT OVER

조건을 만족하는 행의 수를 반환합니다.

USAGE

COUNT ( expr ) OVER (analytic_clause)

EXAMPLE

SELECT employee_id,salary,COUNT(*) OVER (ORDER BY salary) AS count
FROM employees
WHERE department_id = 50;

SUM OVER

조건을 만족하는 행의 합을 반환합니다.

USAGE

SUM ( expr ) OVER (analytic_clause)

EXAMPLE

SELECT employee_id,last_name,salary,
SUM(salary) OVER (ORDER BY employee_id) AS acc_salary
FROM employees

DECODE

CASE나 IF-THEN-ELSE문처럼 조건부 결과를 반환

USAGE

DECODE(칼럼이름, 비교값, 입력값,
[비교값2, 입력값2..]
,기본값)

EXAMPLE

SELECT last_name,job_id,salary,
DECODE(job_id, 'IT_PROG',1.10*Salary,
'ST_CLERK',1.15*Salary,
'SA_REP', 1.20*Salary,
salary) as Recieve
FROM employees

RANK

값의 그룹에서 해당값의 절대순위를 계산
1위 - 공동2위 - 공동2위- 공동2위 - 공동 5위 - 공동 5위 - 7위 순서

USAGE

RANK (expr) WITH GROUP(ORDER BY expr)
RANK () OVER(query_partition_clause order_by_clause);

EXAMPLE

SELECT RANK(15500, .05)
WITHIN GROUP (ORDER BY salary DESC, commission_pct) "Rank"
FROM employees;

RESULT

Rank
----------
4

DENSE RANK

값의 그룹에서 해당값의 상대순위를 계산
1위 - 공동2위 - 공동2위- 공동2위 - 공동 3위 - 공동 3위 - 4위 순서

USAGE

DENSE_RANK (expr) WITH GROUP(ORDER BY expr)
DENSE_RANK () OVER(query_partition_clause order_by_clause);

EXAMPLE

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

RESULT

Rank
----------
3

PERCENT_RANK

그룹 수에 대한 값의 순위 퍼센트를 반환.
최고 순위 0, 최대 순위를 1로 놓고 해당 비율을 반환

EXAMPLE

SELECT PERCENT_RANK(15000, .05) WITHIN GROUP
(ORDER BY salary DESC, commission_pct) AS Percent-Rank
FROM employees;

RESULT

Percent-Rank
-------------
.028037383

FIRST_VALUE

LAST_VALUE

FIRST_VALUE 함수는 받아온 데이터에서 가장 첫번째 데이터를 반환한다.
LAST_VALUE 함수는 반대로 받아온 데이터에서 가장 첫번째 데이터를 반환한다.
오름차순 정렬후 FIRST_VALUE함수로 얻은 데이터는
내림차순 정렬후 LAST_VALUE함수로 얻은 데이터와 같다. (반대의 경우도 마찬가지)

USAGE

FIRST_VALUE(expr) OVER (analyic_clause)
LAST_VALUE(expr) OVER (analyic_clause)

EXAMPLE

SELECT employee_id,salary, FIRST_VALUE(salary)
OVER (PARTITION BY department_id ORDER BY salary DESC) as "Highsal"
FROM employees;

RESULT

employee_idsalary Highsal
---------------------------------------------------------
200 4400 4400
201 13000 13000

NVL

변수 1의 값을 체크하여 변수 1이 NULL이면 변수 2의 값을 반환하고
그렇지 않다면 변수1의 값을 반환한다.

USAGE

NVL(expr1, expr2)

EXAMPLE

SELECT last_name, nvl(manager_id, 0) AS Manager_id
FROM employees
WHERE employee_id BETWEEN 100 AND 105;

RESULT

LAST_NAME manager_id
-----------------------------------
King 0
Kochhar 100

WIDTH_BUCKET

어떤 값의 최소에서부터 최대값을 설정하고 bucket(구간)을 지정하여 임의의 값이
지정된 범위 내에서 어느 위치에 있는지를 반환한다. <등급 지정>

USAGE

WIDTH_BUCKET(expr, min_value, max_value, num_buckets)

EXAMPLE

SELECT salary, WIDTH_BUCKET(salary, 0, 20000, 5)
FROM employees
ORDER BY salary desc

LAG

SELF JOIN하지 않고 하나의 테이블에서 동시에 한 행 이상을 접근 할 수 있게 함 - 분석용 함수로써, 이전 레코드를 참조(동일한 테이블에 있는 다른 행의 값을 참조하는 함수)

USAGE

LAG ( value_expr [,offset] [,default] )
OVER ([query_partition_clause] order_by_clause )

EXAMPLE

SELECT last_name, salary, LAG(salary, 1, 0)
OVER (ORDER BY hire_date) AS prev_sal
FROM employees
WHERE job_id = 'PU_CLERK';

RESULT

last_name salary LAG(salary,1,0)
---------------------------------------------
Grant 3200 2600
Himuro 2600 3200
Colmenares 2500 2600

LEAD

SELF JOIN하지 않고 하나의 테이블에서 동시에 한 행 이상을 접근 할 수 있게 함
- LAG와는 반대로 하나 앞의 레코드 값 참조

USAGE

LEAD ( value_expr [,offset] [,default] )
OVER ([query_partition_clause] order_by_clause )

EXAMPLE

SELECT last_name, salary,LEAD(salary, 1, 0)
OVER (ORDER BY hire_date) AS NextHire
FROM employees
WHERE job_id = 'PU_CLERK';

RESULT

last_name salary LEAD(salary,1,0)
---------------------------------------------
Grant 3200 2600
Himuro 2600 2500
Colmenares 2500 3300

정규표현식 함수 (Oracle 10g에서 추가됨)

REGEXP-REPLACE

매치된 패턴을 지정된 replace_string으로 대체하고, 복잡한 검색/대체 작업을 가능하게 하는 함수

USAGE

REGEXP_REPLACE(source_string, pattern
[, replace_string
[, position
[, occurrence
[match_parameter]]]])

EXAMPLE

SELECT REGEXP_REPLACE(TEST,'[0-9]','*',7)REG_REP
FROM(
SELECT '123456789134' TEST
FROM dual
)

RESULT

REG_REP
--------------
123456******

REGEXP-INSTR

INSTR기능과 비슷하며 패턴이 첫 매치된 시작위치를 반환하며 시작위치는 지정가능

USAGE

REGEXP_INTSTR(source_string,pattern
[, start_position
[,occurrence
[, return_option
[, match_parameter]]]])

EXAMPLE

SELECTREGEXP_INSTR('Maryhasacold','a')position
FROMdual;

RESULT

POSITION
----------
2

REGEXP_LIKE

USAGE

REGEXP_LIKE(scrstr, patten [,match_option])

EXAMPLE

SELECT product_name
FROM product_information
WHERE REGEXP_LIKE(product_name,'SS[^p]')

RESULT

REGEXP_LIKE(product_name,'SS[^p]')
-----------------------------------
Spread sheet - SSS/V 2.1
Spread sheet - SSS/V 2.0
SS Stock - 3mm
SS Stock - 1mm

REGEXP_SUBSTR

USAGE

REGEXP_SUBSTR(srcstr,patten[,position[,occurrence[,match_option]]])

EXAMPLE

SELECT cust_email,REGEXP_SUBSTR(cust_email,'[^@]+')
FROM customers

RESULT

cust_email REGEXP_SUBSTR(cust_email,'[^@]+')
--------------------------------------------------------------
abc@abc.com abc

[출처] 오라클 함수 정리 (전산스터디 seen 카페) |작성자 여우비