숫자함수
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 SUBSTR2 SUBSTR3
-----------------------------------------
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)
-------------------------------------------------------------------
Alexander 9000 1
Bruce 6000 1
David 4800 2
Valli 4800 2
Diana 4200 3
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_id salary 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
SELECT REGEXP_INSTR('Mary has a cold','a') position
FROM dual;
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
'database > oracle' 카테고리의 다른 글
| Oracle 10g Express Edition 현재 설치 설정 (0) | 2012.03.22 |
|---|---|
| 테스트 이용자DB 구축하기 (0) | 2012.03.22 |
| 공유기로 연결된ora10g error : ORA-12514: TNS:리스너가 현재 접속 기술자에 요청된 서비스를 알지 못함 (0) | 2012.03.22 |
| Oracle 10g Network Configuration (0) | 2012.03.22 |
| tablespace, user delete (0) | 2012.03.22 |