SQL Functions
보다 효율적이고 알아보기 쉬운 SQL문을 작성하기 위하여.
1. Single-Row Functions
어떤 테이블에 10개의 Row가 있고 질의문에 조건이 없다면, 10개의 Row 모두에 단일 행 함수가 적용되어 변환된 결과를 반환한다. 즉, 단일 행 함수는 행마다 적용 된다.
1) Number Functions
숫자 데이터 타입의 값을 조작하여 변화된 숫자 값을 반환하는 함수.
1-1) MOD : MOD( m, n )
m을 n으로 나누었을 때 나머지를 반환 한다.
1-2) ROUND : ROUND( m, n )
m을 소수점 n+1 자리에서 반올림한 결과를 반환 한다.
1-3) WIDTH_BUCKET : WIDTH_BUCKET( expr, min_value, max_value, num_buckets )
어떤 값의 최소에서부터 최대값을 설정하고 bucket을 지정하여 임의의 값이 지정된 범위 내에서 어느 위치에 있는지를 반환 한다.
2) Character Functions
문자 데이터 타입의 값을 조작하여 문자 값을 반환 하는 함수.
2-1) LOWER, UPPER : LOWER( char ), UPPER( char )
입력된 문자 값을 소문자, 대문자로 변환 한다.
2-2) SUBSTR : SUBSTR( string, position, substriong_length )
문자열에서 일부 문자값을 선택적으로 반환 한다.
3) Datetime FunctionsDATE로 지정된 데이터 타입의 값에 적용 하는 함수.
3-1) SYSDATE : SYSDATE
시스템의 설정된 날짜 값을 반환 한다.
3-2) ADD_MONTH : ADD_MONTHS( date, integer )
지정한 날짜 중 해당 월에 정수를 더한 값을 반환 한다.
3-3) LAST_DAY : LAST_DAY( date )
해당하는 월의 마지막 일을 반환 한다.
3-4) MONTHS_BETWEEN : MONTHS_BETWEEN( date1, date2 )
날짜와 날짜 사이의 기간을 월로 나타냅니다.
4) Conversion Functions
4-1) TO_CHAR ( datetime ) : TO_CHAR( datetime, ‘format’ )
DATE 관련 데이터 타입을 VARCHAR2 데이터 타입으로 변환 한다.
SQL > SELECT TO_CHAR( SYSDATE, ‘YYYY-MM-DD HH24:MI:SS’ ) “sysdate”
FROM dual;
Result > 2011-06-28 11:22:10
FORMAT
D : 요일 번호 (1-7) DAY : 요일명 DD : 일 (1-31) DY : 요일명 약자
HH : 시 (1-12) HH24 : 시 (0-23) MI : 분 (0-59) MM : 월 (1-12)
MON : 월 이름 약자 MONTH : 월 이름 SS : 초 (0-59) YEAR : 년도
YYYY : 4자리 년도
4-2) TO_CHAR ( number ) : TO_CHAR( number, ‘format’ )
NUMBER 타입을 VARCHAR2 데이터 타입으로 변환 한다.
SQL > SELECT TO_CHAR( 20000, ‘$999,999’) “money”
FROM dual;
Result > $20,000
FORMAT ( 123.45 를 기준 ) : L은 화폐단위, S는 양수나 음수를 표시
999.999 : 123.450 9999 : 123 $999.99 : $123.45
L999.99 : \123.45 S99.999 : +123.45 999,999 : 12,345
5) Miscellaneous Single-Row Functions
5-1) DECODE : DECODE( expr, search, result, default )
expr과 각 search값을 비교하여 같으면 result값을 반환 하고 같지 않다면 default 값을 반환 한다.
SQL > SELECT job_id,
DECODE(job_id, ‘SA_MAN’, ‘Sales Dept’, ‘SA_REP’, ‘Sales Dept’, ‘Another’) “decode”
FROM jobs WHERE job_id LIKE ‘S%’;
설명 > job_id가 S로 시작하는 것 중에서 ‘SA_MAN’과 ‘SA_REP’인 경우 ‘Sales Dept’로 표현하고,
5-2) NVL : NVL( expr1, expr2 )
expr1이 NULL값인 경우 expr2의 값을 반환 한다.
2. Aggregate Functions
하나 이상의 행으로부터 하나의 결과를 반환한다.
1) AVG
1-1) AVG( expr )
하나 이상의 값들로부터 평균값을 반환 한다.
1-2) AVG( expr ) OVER( analytical_clause )
분석 함수로 사용된 예.
2) RANK
2-1) RANK( expr ) WITHIN GROUP( ORDER BY expr )
전체 값을 대상으로 각 값의 순위를 반환 한다.
2-2) RANK( ) OVER ( query_partition_clause order_by_clause )
특정 순위가 아닌 전체 순위에 대해 하나하나 알고 싶을 때 사용.
3) SUM, MIN, MAX, COUNT
합계, 최대, 최소, 전체 행의 수를 반환 하는 함수.
3. Analytical Functions
행의 집단에 대해 연산이 이루어진다는 점에서 일반 그룹 함수와 유사하다. 그러나 일반 그룹 함수는 하나의 집단에 대해 하나의 결과가 RETURN 되지만 분석용 함수는 하나의 집단에 대해 여러 가지 기준을 적용해서 여러 개의 결과가 RETURN 될 수 있다.
분석용 함수는 하나의 Query에서 ORDER BY 절 직전에 수행된다. 즉, JOIN, WHERE, GROUP BY, HAVING이 처리된 결과에 대해 분석용 함수가 적용되고 마지막으로 ORDER BY가 수행된다.따라서 분석용 함수는 SELECT절이나 ORDER BY절에만 나타날 수 있다.
Analytic-Function ( <Argument>, <Argument>, … )
OVER ( <Query-Partition-clause> <order-By-Clause> <Windowing-Clause> )
1) FIRST_VALUE : FIRST_VALUE( expr ) OVER ( analytic_clause )
정렬된 값들 중 첫 번째 값을 반환 한다.
2) COUNT : COUNT ( expr ) OVER ( analytic_clause )
조건을 만족하는 행의 수를 반환 한다.
3) SUM : SUM ( expr ) OVER ( analytic_clause )
조건에 만족하는 행의 합을 반환 한다.
4) RANK : RANK( ) OVER ( order_by_clause )
값의 그룹에서 값의 순위를 계산 한다.
5) ROW_NUMBER : ROW_NUMBER( ) OVER ( [query_partition_clause] order_by_clause )
ORDER BY 절에서 지정된 행의 순위 순서로 각 행에 고유한 순서를 1부터 할당 한다.
6) DENSE_RANK : DENSE_RANK( ) OVER ( [query_partition_clause] order_by_clause )
값의 그룹에서 값의 순위를 계산한다. RANK와는 달리 같은 순위가 둘 이상 있어도 다음 순위는1만 증가하여 반환 한다.
4. Regular Expression
Regular Expression은 Oracle 10g에서 새로 도입된 것이다. 기존 함수의 확장한 새로운 함수를 제공하고 있다.
1) REGEXP_LIKE : REGEXP_LIKE ( srcstr, pattern [,match_option] )
LIKE 연산자와 유사하며, 표현식 패턴을 수행하여, 일치하는 값을 반환 한다.
SQL > SELECT product_name
FROM oe.product_information
WHERE REGEXP_LIKE ( product_name, ‘SS[^P]’ );
설명 > 제품의 이름중에 ‘SS’다음에 ‘P’를 포함하지 않는 문자열
2) REGEXP_SUBSTR : REGEXP_SUBSTR ( srcstr, pattern [, position [, occurrence [, match_option ] ] ] )
SUBSTR 함수의 기능을 확장. 주어진 문자 열을 대상으로 정규 표현식 패턴을 수행하여, 일치하는 하위 문자열을 반환.
SQL > SELECT cust_email, REGEXP_SUBSTR( cust_email, ‘[^@]+’ ) “REGEXP_SUBSTR”
FROM oe.customers
WHERE nls_territory = ‘SWITZERLAND’;
설명 > 이메일에 저장된 문자열에서 ‘@’이 아닐 때까지 읽어 고객의 아이디만을 얻는다.
3)REGEXP_REPLACE
:REGEXP_REPLACE ( srcstr,pattern [,replacestr [,position [,occurrence [,match_option] ] ] ] )
주어진 문자 열을 대상으로 정규 표현식 패턴을 조사하여, 다른 문자로 대체한다.
SQL > SELECT REGEXP_REPLACE(‘Oracle is the information company’, ‘( ){2,}’, ‘ ‘ )
FROM DUAL;
설명 > 문자열에 공백이 일정하지 않은 경우 두 번째 매개변수 위치에 두 문자 이상의 공백이 발생하는 경우의 의미로 ‘{2,}’로 지정하고 있다. 한 문자의 크기로 일정하게 설정 하기 위해 세번째 매개변수에 ‘ ‘와 같이 지정 하였다.
*** 참조 문헌 : 10g로 시작하는 오라클 SQL & PL/SQL - 3장 SQL 함수를 통해 멋진 질의문 만들기
