고정 헤더 영역
상세 컨텐츠
본문
1. Character Functions
① LOWER( column|expression )
=> LOWER('String') --> string : 소문자로 변환
② UPPER( column|expression )
=> UPPER('String') --> STRING : 대문자로 변환
③ INITCAP( column|expression )
=> INITCAP('string') --> String : 첫글자만 대문자이고 나머지글자는 소문자로 변환
④ CONCAT( column1|expression1 ,column2|expression2 )
=> CONCAT('Good','String') --> GoodString : ||와 같은 역할을 한다.
⑤ SUBSTR(column|expression, m [,n]) : m값이 음수면 문자값의 끝부터..)
=> SUBSTR('String',1,3) --> Str : 1번째부터 3개의 문자를 리턴한다.
⑥ LENGTH( column|expression )
=> LENGTH('String') --> 6 : 문자열의 길이를 리턴한다.
⑦ INSTR( column|expression, )
=> INSTR('String','r') --> 3 : 문자열에 'r'이 몇번째 위치하고 있나를 리턴한다.
⑧ LPAD( column|expression,n,'string' ) : n 은 전체 길이
=> LPAD('String',10,'*') --> ****String : 10자리수중에 모자란 자리수를 '*'로 왼쪽에 채운다.(문자,숫자 가능!!!)
⑨ RPAD('String',10,'*')
=> String**** : : 10자리수중에 모자란 자리수를 '*'로 오른쪽에 채운다.(문자,숫자 가능!!!)
⑩ LTRIM(' String')
=> 'String' : 문자열의 왼쪽 공백을 버린다.
⑪ RTRIM('String ')
=> 'String' : 문자령의 오른쪽 공백을 버린다.
* TRIM(leading/tailing/both, trim_character FROM trim_source )
=> TRIM( 'S' FROM 'SSMITH') --> MITH
====================================================================================================
2. Number Functions
① ROUND(45.926, 2)
=> 45.93 : 소수점 두자리수까지 보여주면서 반올림한다.
② TRUNC(45.926, 2)
=> 45.92 : 소수점 두자리까지만 보여주고 나머지는 버린다.
③ MOD(1600,300)
=> 100 : 1600을 300으로 나누고 나머지를 리턴한다.
* ROUND예제(WHOLE NUMBER:정수)
SELECT ROUND(45.923,2), ROUND(45.923,0), ROUND(45.923,-1) FROM DUAL
==> 45.92 46 50
* TRUNC예제
SELECT TRUNC(45.923,2), TRUNC(45.923), TRUNC(45.923,-1) FROM DUAL
==> 45.92 45(n이 생략된면 일의 자리까지 남기고 버린다.) 40
* SYSTEM 날짜를 갖고 오는 방법.
SELECT sysdate FROM dual
====================================================================================================
3. Date 계산( 날짜를 숫자로 저장)
date + number : date에 number만큼 후의 날자를 보여준다.
date - number : date에 number만큼 전의 날자를 보여준다.
date1 - date2 : date1에서 date2 까지의 총 일수를 보여준다.( date1+date2는 X )
date1 + 숫자/24 : date1에서 시간을 더해 날짜를 보여준다.
====================================================================================================
4. Date Functions
① MONTHS_BETWEEN('01-SEP-95','11-JAN-94')
=> 19.6774194 : 두날짜 사이의 달수를 보여준다.
② ADD_MONTHS('11-JAN-94', 6)
=> 11-JUL-94 : 날짜에 6개월을 더한 날자를 보여준다.
③ NEXT_DAY('01-SEP-95','FRIDAY')
=> '08-SEP-95' : 해당일 다음에 오는 FRIDAY의 일자를 보여준다.
('SUNDAY'는 1, 'MONDAY'는 2...이런식으로 숫자를 써줘도 된다.)
④ LAST_DAY('01-SEP-95')
=> '30-SEP-95' : 해당월의 마지막날자를 보여준다.
⑤ ROUND('25-JUL-95','MONTH')
=> 01-AUG-95 ROUND('25-JUL-95','YEAR')--> 01-JAN-96
⑥ TRUNC('25-JUL-95','MONTH')
=> 01-JUL-95 TRUNC('25-JUL-95','YEAR') --> 01-JAN-95
====================================================================================================
5. Conversion Functions
nlsparams : 십진수, 그룹구분자, 지역 통화 기호, 국제 통화 기호
TO_CHAR(date,['format'],[nlsparams]) : date를 format에 맞게 문자열로 변환한다.
- Date Format Elements
YYYY --> 1999 (년출력) , YEAR --> nineteen ninety-nine (년출력)
MM --> 12 (월출력) , MONTH --> DECEMBER (월출력), MON --> DEC
D --> 요일을 숫자로 리턴한다.(일요일은 1, 월요일은 2...)
DD --> 07 (달의 일출력)
DDD --> 200 (연의 일출력)그 해의 총 몇 일째인가를 리턴한다.
DAY --> MONDAY (요일출력) DY-->MON
CC --> 20 (몇 세기인지를 보여준다.)
WW --> 그 해의 몇 번째 주인가를 리턴한다.
W --> 그 달의 몇 번째 주인가를 리턴한다.
* Element들을 소문자로 쓰면 소문자로 나오고 대문자로 쓰면 대문자로 출력된다.
HH or HH12 or HH24 / MI(0-59분) / SS(0-59초)
* 문자열은 " " 묶어 추가한다 DD " of " MONTH --> 12 of DECEMBER
*숫자 접미어는 숫자를 문자로 표기. TH(4->4TH)/ SP(4->FOUR)/ SPTH or THSP(4->FOURTH)
ddspth : 14-> fothteenth
* / . , : 구두점은 결과에 그대로 출력한다. * 공백, 선행제로를 제거하는 fm요소가 있다.
TO_CHAR(number,'format',[nlsparams]) : number를 format에 맞게 문자열로 변환한다.
- Number Format Elements
9 : 999,999 --> 1,234 $: 부동 달러 기호 $99999 -> $1234
0 : 099999 --> 001234 99.999EEEE -> 1.234E+03 B: 0값을 공백으로
L : L99,999 --> FF1,234 (NLS_CURRENCY에 설정되어있는 값이 L로 보여진다.)
TO_NUMBER(char,['format'],[nlsparams]) : 숫자형태의 문자열을 숫자로 변한한다.
TO_DATE(char,['format'],[nlsparams]):날자형태의 문자열을 format에 맞게 날자형식으로 변환 한다.
====================================================================================================
6. NVL Funcion : 값이 null일 때 설정값을 보여준다.
① NVL(number_column, 0) : null일 때 0을 보여준다.
② NVL(date_column, '01-JAN-95') : null일 때 '01-JAN-95'를 보여준다.
③ NVL(character_column, 'Empty') : null일 때 'Empty'를 보여준다.
* column Type과 표현식의 type이 반드시 일치해야 한다.
====================================================================================================
7. DECODE Function : CASE or IF-THEN-ELSE 형식과 비슷하다.
* DECODE(col/expression, search1, result1 [,search2,result2,…] [,default])
F1 (F2 (F3 (col,arg1),arg2),arg3)
====================================================================================================
/** 함수 - Number Function **/
--무조건 올림
SELECT CEIL(13.11) FROM DUAL;
SELECT CEIL(13.001) FROM DUAL;
--나머지 구함
SELECT MOD(23, 5) FROM DUAL;
SELECT MOD(57, 145) FROM DUAL;
-- 제곱승
SELECT POWER(3, 2), POWER(3, -2) FROM DUAL;
SELECT POWER(2, 10) FROM DUAL;
--자릿수 지정
SELECT ROUND(345.123, 0) FROM DUAL;
SELECT ROUND(345.123, 2), ROUND(345.123, -1) FROM DUAL;
--지정된 자리까지 잘라내기
SELECT TRUNC(345.123, 1), TRUNC(345.123, 0), TRUNC(345.123, -1) FROM DUAL;
SELECT TRUNC( 345.123 + 0.09, 1 ) FROM DUAL;
SELECT TRUNC( 345.123 + 0.9, 0 ) FROM DUAL;
SELECT TRUNC( 345.123 + 9, -1 ) FROM DUAL;
SELECT SIGN(5.989), SIGN(0), SIGN(-999.098) FROM DUAL;
/** 함수 - Character Function **/
--특정Character의 아스키값 구하기
SELECT CHR(65) "CHR", ASCII('A') "ASCII" FROM DUAL;
SELECT ASCII( CHR(65) ) FROM DUAL;
SELECT LOWER('My name is LKM') "LOWER", UPPER('My name is LKM') "UPPER" FROM DUAL;
SELECT LPAD('LKM', 10, '*') "LPAD", RPAD('LKM', 10, '*') "RPAD" FROM DUAL;
SELECT LPAD('1234567890', 20, '+') || RPAD('1234567890', 20, '^') "12345678901234567890"
FROM DUAL;
SELECT LPAD('1,234,567', 30, ' ') "LPAD사용으로 30자리 맞춤",
'1,234,567' "단순문자 사용",
1234567 "단순숫자 사용"
FROM DUAL;
SELECT LTRIM(' AAA ') "LTRIM", RTRIM(' AAA ') "RTRIM" FROM DUAL;
SELECT LTRIM( RTRIM( ' A A A ' ) ) "TRIM" FROM DUAL;
SELECT REPLACE('ORACLE', 'A', 'BBB') "REPLACE" FROM DUAL;
SELECT EMP_NAME, REPLACE(EMP_NAME, '이', '박') "이->박"
FROM PERSONNEL
WHERE EMP_NAME LIKE '이%';
SELECT SUBSTR('ORACLE PROJECT', 1, 3) SUBSTR1,
SUBSTR('ORACLE PROJECT', 4, 5) SUBSTR2,
SUBSTR('ORACLE PROJECT', 10) SUBSTR3 FROM DUAL ;
SELECT SUBSTRB('ORACLE PROJECT', 1, 3) SUBSTRB1,
SUBSTRB('ORACLE PROJECT', 4, 5) SUBSTRB2,
SUBSTRB('ORACLE PROJECT', 10) SUBSTRB3 FROM DUAL ;
SELECT SUBSTR('오라클 PROJECT', 1, 3) SUBSTR1,
SUBSTR('오라클 PROJECT', 4, 5) SUBSTR2,
SUBSTR('오라클 PROJECT', 10) SUBSTR3 FROM DUAL ;
SELECT SUBSTRB('오라클 PROJECT', 1, 3) SUBSTRB1,
SUBSTRB('오라클 PROJECT', 4, 5) SUBSTRB2,
SUBSTRB('오라클 PROJECT', 10) SUBSTRB3 FROM DUAL ;
SELECT LENGTH ('ORACLE PROJECT') "LENGTH",
LENGTHB('ORACLE PROJECT') "LENGTHB",
FROM DUAL;
SELECT EMPNO, LENGTH(EMPNO), LENGTHB(EMPNO),
EMP_NAME, LENGTH(EMP_NAME), LENGTHB(EMP_NAME)
FROM PERSONNEL
WHERE EMPNO > '98102';
SELECT HOBBY, LENGTH(HOBBY), LENGTHB(HOBBY)
FROM PERSONNEL
WHERE EMPNO > '98102';
SELECT INSTR ('ORACLE PROJECT', 'R', 1, 1) INSTR1,
INSTR ('ORACLE PROJECT', 'R', 1, 2) INSTR2,
INSTR ('ORACLE PROJECT', 'R', 1, 3) INSTR3
FROM DUAL;
SELECT INSTR ('CORPORATE FLOOR','OR', 3, 2) INSTR,
INSTRB ('CORPORATE FLOOR','OR', 3, 2) INSTRB
FROM DUAL;
SELECT HOBBY,
INSTR (HOBBY, ')', 1, 1) INSTR,
INSTRB(HOBBY, ')', 1, 1) INSTRB
FROM PERSONNEL
WHERE EMPNO > '98102';
/** 함수 - Date Function **/
SELECT SYSDATE FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') "SYSDATE" FROM DUAL;
SELECT LAST_DAY(SYSDATE) FROM DUAL;
SELECT TO_CHAR(LAST_DAY(SYSDATE), 'YYYY-MM-DD HH24:MI:SS') "LAST_DAY" FROM DUAL;
SELECT MONTHS_BETWEEN( '2002/01/13', '2002/05/13' ) "MONTHS_BETWEEN (-)",
MONTHS_BETWEEN( '2002/01/13', '2001/11/13' ) "MONTHS_BETWEEN (+)"
FROM DUAL ;
SELECT MONTHS_BETWEEN( '2002/01/13', '2002/01/30' ) "MONTHS_BETWEEN (-)",
MONTHS_BETWEEN( '2002/01/13', '2002/01/01' ) "MONTHS_BETWEEN (+)"
FROM DUAL ;
SELECT ADD_MONTHS(SYSDATE, 1) "ADD_MONTHS (+)",
ADD_MONTHS(SYSDATE, -1) "ADD_MONTHS (-)"
FROM DUAL ;
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
SELECT ADD_MONTHS( '2002/02/28', 12 ) "1년후",
ADD_MONTHS( '2002/02/28', 24 ) "2년후",
ADD_MONTHS( '2002/02/28', 36 ) "3년후"
FROM DUAL ;
SELECT SYSDATE,
NEXT_DAY(SYSDATE, '일요일') "NEXT_DAY 1",
NEXT_DAY(SYSDATE, 1 ) "NEXT_DAY 2"
FROM DUAL;
SELECT SYSDATE,
NEXT_DAY(SYSDATE, '수요일') "NEXT_DAY 1",
NEXT_DAY(SYSDATE, 4 ) "NEXT_DAY 2"
FROM DUAL;
/** 함수 - Conversion Function **/
SELECT TO_CHAR(1234567.891) "TO_CHAR1",
TO_CHAR(1234567.891, '999') "TO_CHAR2",
TO_CHAR(1234567.891, '9,999,999') "TO_CHAR3",
TO_CHAR(1234567.891, '0.0000') "TO_CHAR5",
TO_CHAR(1234567.891, '9,999,999.0000') "TO_CHAR6",
TO_CHAR(123, '9,999.00') "TO_CHAR7",
TO_CHAR(123, '9,999.99') "TO_CHAR8"
FROM DUAL ;
SELECT TO_CHAR(1234567.891, '9G999G999') "TO_CHAR3",
TO_CHAR(1234567.891, '0D0000') "TO_CHAR5",
TO_CHAR(1234567.891, '9G999G999D0000') "TO_CHAR6",
TO_CHAR(123, '9G999D00') "TO_CHAR7",
TO_CHAR(123, '9G999D99') "TO_CHAR8"
FROM DUAL ;
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') "TO_CHAR1",
TO_CHAR(SYSDATE, 'YYYY/MM') "TO_CHAR2",
TO_CHAR(SYSDATE, 'YYYY') "TO_CHAR3",
TO_CHAR(SYSDATE, 'DD') "TO_CHAR4",
TO_CHAR(SYSDATE, 'DAY') "TO_CHAR5",
TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS') "TO_CHAR6",
TO_CHAR(TO_DATE('20020101','YYYYMMDD'), 'YYYY-MM-DD') "TO_CHAR7",
TO_CHAR(TO_DATE('20020101','YYYYMMDD'), 'YYYYMMDD HHMISS') "TO_CHAR8"
FROM DUAL ;
SELECT TO_NUMBER('123456.9') "TO_NUMBER1",
TO_NUMBER('1234567') "TO_NUMBER2"
FROM DUAL ;
SELECT TO_NUMBER('123,456.9', '999,999.9') "TO_NUMBER1",
TO_NUMBER('1,234,567', '9G999G999') "TO_NUMBER2"
FROM DUAL ;
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
SELECT TO_DATE('20020824') "TO_DATE1",
TO_DATE('2002-08-24') "TO_DATE2",
TO_DATE('200208') "TO_DATE3"
FROM DUAL ;
SELECT TO_DATE('20020824', 'YYYYMMDD') "TO_DATE1",
TO_DATE('2002-08-24', 'YYYY-MM-DD') "TO_DATE2",
TO_DATE('200208', 'YYYYMM') "TO_DATE3"
FROM DUAL ;
SELECT TO_DATE('2002/08/24 08:14:06', 'YYYY/MM/DD HH24:MI:SS') "TO_DATE1",
TO_DATE('2002/08/24 08:14:06 오후', 'YYYY/MM/DD HH:MI:SS AM') "TO_DATE2"
FROM DUAL ;
/** 함수 - Group Function **/
SELECT AVG(HEIGHT), AVG(WEIGHT) FROM PERSONNEL;
SELECT MAX(EMPNO), MAX(EMP_NAME), MIN(EMPNO), MIN(EMP_NAME) FROM PERSONNEL;
SELECT MAX(HEIGHT), MIN(HEIGHT) FROM PERSONNEL;
SELECT SUM(WEIGHT) FROM PERSONNEL;
SELECT COUNT(*), COUNT(EMPNO), COUNT(JIKCH_CODE) FROM PERSONNEL;
/** 함수 - ETC **/
SELECT EMPNO, EMP_NAME, HOBBY 취미, WELL 특기
FROM PERSONNEL
WHERE EMPNO BETWEEN '98001' AND '98005';
SELECT EMPNO, EMP_NAME, HOBBY 취미, NVL(WELL, '(없다)') 특기
FROM PERSONNEL
WHERE EMPNO BETWEEN '98001' AND '98005';
SELECT DECODE( '나', '나', '맞다', '아니다' ) "나가 나면 맞다, 아니면 아니다"
FROM DUAL ;
SELECT DECODE( '나', '대명', '아니다',
'너' , '아니다',
'그' , '아니다',
'나' , '맞다',
'모르겠다' ) FROM DUAL ;
SELECT EMPNO,
EMP_NAME,
DECODE (HT_CODE, '1', '현재원', '2', '휴직', '퇴사') HT_CODE
FROM PERSONNEL
WHERE EMPNO BETWEEN '98071' AND '98080';
SELECT GREATEST (132, 33, 45, 90, 60.77) GREATEST,
LEAST (132, 33, 45, 90, 60.77) LEAST
FROM DUAL;
SELECT GREATEST ('이공명', '이대명', '최수미') GREATEST,
LEAST ('이공명', '이대명', '최수미') LEAST
FROM DUAL;
SELECT USERENV('LANGUAGE') "LANGUAGE",
USERENV('TERMINAL') "TERMINAL",
USERENV('SESSIONID') "SESSIONID"
FROM DUAL;
SELECT UID, USER FROM DUAL;
우리가 일반적으로 점수의 순위는 ODRDER BY desc를 해서 볼수가 있습니다.
하지만 똑같은 점수를 가진 사람들이 많이 나올때..
예를 들면.100, 90, 90, 80 이렇게 점수가 나오면..
순위를 1,2,2,4 이렇게 부여 해야 합니다
이거 SQL문으로 할려고 하니 어렵더라구요.. 내 머리가 나쁜건지..
암튼 오라클 8.1.6 버전부터는 아주 쉽게 순위를 부여 할 수 있습니다..
아래 내용을 참고해 보세요...
오라클에서는 RANK Function을 사용해서 순위를 간편하게 부여할 수 있습니다.
RANK Function는 oracle 8i(8.1.6) 부터 가능합니다.
8.1.6 이전 버전에서는 사용 할 수 없습니다. ORA-923 error 가 발생 합니다.
plsql 내에서는 oracle 9i 부터 가능합니다. 8.1.6에서는 ORA-900 error가 발생 합니다.
-- scott유저로 접속을 합니다.
SQLPLUS scott/tiger
-- RANK() 함수를 사용하여 급여 순으로 순위를 부여한 예제 입니다.
-- RK의 출력값을 보면 급여가 같으면 같은 순위로 부여가 됩니다.
SQL>SELECT empno, ename, sal,
RANK() OVER (ORDER BY sal DESC ) as rk
FROM emp;
EMPNO ENAME SAL RK
--------- ---------- ---------- ----------
7839 KING 5000 1
7788 SCOTT 3000 2
7902 FORD 3000 2
7566 JONES 2975 4
7698 BLAKE 2850 5
7782 CLARK 2450 6
7499 ALLEN 1600 7
7844 TURNER 1500 8
7934 MILLER 1300 9
7521 WARD 1250 10
7654 MARTIN 1250 10
7876 ADAMS 1100 12
7900 JAMES 950 13
7369 SMITH 800 14
☞ 그룹별로 순위를 부여 하는 법
-- 위 예제는 deptno를 파티션으로 나누어서 부서별로 순위를 부여 합니다.
-- 특정한 그룹별로 순위를 부여하고 싶을때 사용 하면 편합니다.
SQL>SELECT deptno, ename, sal,
RANK() OVER (PARTITION BY deptno ORDER BY sal DESC ) as rk
FROM emp ;
DEPTNO ENAME SAL RK
------- ---------- ---------- ---------
10 KING 5000 1
10 CLARK 2450 2
10 MILLER 1300 3
20 SCOTT 3000 1
20 FORD 3000 1
20 JONES 2975 3
20 ADAMS 1100 4
20 SMITH 800 5
30 BLAKE 2850 1
30 ALLEN 1600 2
30 TURNER 1500 3
30 WARD 1250 4
30 MARTIN 1250 4
30 JAMES 950 6
☞ DENSE_RANK() 함수
DENSE_RANK( ) - 중복 RANK의 수와 무관하게 numbering을 합니다.
-- 1등, 2등, 2등 이렇게 2등이 중복되었는데 4등이 아니라 3등이 부여 됩니다.
SQL>SELECT empno, ename, sal,
DENSE_RANK() OVER (ORDER BY sal DESC ) as rk
FROM emp;
EMPNO ENAME SAL RK
--------- ---------- ---------- ---------
7839 KING 5000 1
7788 SCOTT 3000 2
7902 FORD 3000 2
7566 JONES 2975 3
7698 BLAKE 2850 4
7782 CLARK 2450 5
7499 ALLEN 1600 6
7844 TURNER 1500 7
7934 MILLER 1300 8
7521 WARD 1250 9
7654 MARTIN 1250 9
7876 ADAMS 1100 10
7900 JAMES 950 11
7369 SMITH 800 12
-- 참고 2
-- 부서별 직업별 급여 합계와, 직업별 급여 합계가 가장 큰 금액
SELECT deptno, job, SUM(SAL) sum_sal,
MAX(SUM(sal)) OVER (PARTITION BY deptno)
FROM emp
GROUP BY deptno, job
======== 보충설명 =======
# OVER analytic_clause
해당 함수가 쿼리 결과 집합에 대해 적용되라는 지시어로써 FROM, WHERE, GROUP BY와
HAVING구 이후에 계산되어 진다.
SELECT 구 또는 ORDER BY 구에 Analytic Function을 사용할 수 있다.
- PARTITION BY 구
하나 이상의 컬럼 또는 적합한 표현식이 사용될 수 있고 하나 이상의 컬럼 또는
표현식에 의한 그룹으로 쿼리의 결과를 파티션한다.
이 구가 생략되면 단일 그룹처럼 쿼리 결과 집합이 처리된다.
- ORDER BY 구
하나 이상의 컬럼 또는 적합한 표현식이 사용될 수 있고
하나 이상의 컬럼 또는 표현식을 기준으로 파티션 내의 데이터를 정렬한다.
표현식은 컬럼의 별칭 또는 위치를 나타내는 숫자를 사용할 수 없다.
