Function
ORACLE 사용자를 위한 기본 SQL 함수 및 예제모음
○ DECODE 함수
★. 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 > '19940029';
SELECT HOBBY, LENGTH (HOBBY), LENGTHB(HOBBY)
FROM PERSONNEL
WHERE EMPNO > '19940029';
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 > '19940029';
ORACLE 사용자를 위한 기본 SQL 함수 및 예제모음
○ 집합쿼리의 종류
- union : 합집합
- union all : 공통원소 두 번씩 다 포함한 합집합
- intersect : 교집합
- minus : 차집합
예>
a = {1,3,5,7} b = {1,2,3,4}
a union b = {1,2,3,4,5,7}
a union all b = {1,1,2,3,3,4,5,7}
a intersect b = {1,3}
a minus b = {2,5,7}
Union
SQL> select * from sawon where sajob = '차장'
union
select * from sawon where sajob in ('차장','대리');
☞ union 을 기준으로 좌우의 선택구문을 모두 합한 조건문
( 단, 중복되는 필드가 있는 경우의 레코드는 뺌 )
합집합
SQL> select * from sawon where sajob = '차장'
union all
select * from sawon where sajob in ('차장','대리');
☞ union all 을 기준으로 좌우의 선택구문을 모두 합한 조건문인데,
위의 union 만 사용한 경우와는 달리 중복되는 필드가 있는 경우에도
모든 레크드를 불러옴
교집합
SQL> select * from sawon where sajob = '차장'
intersect
select * from sawon where sajob in ('차장','대리');
☞ intersect 를 기준으로 좌우의 선택문중 중복되는 자료만 불러옴
차집합
SQL> select * from sawon where sajob = '차장'
minus
select * from sawon where sajob in ('차장','대리');
☞ minus 를 기준으로 중복되는 자료를 뺀 나머지 자료만 불러옴
.
○ 문자열 처리 함수 문자열처리함수
1. CONCAT ( column1|expression1 ,column2|expression2 )
; CONCAT('Good','String') --> GoodString : ||와 같은 역할을 한다.
☞ SELECT CONCAT(pj_cd, pj_nm) FROM tpd01m;
2. SUBSTR ( column|expression, m [,n]) : m값이 음수면 문자값의 끝부터.. )
; SUBSTR('String',1,3) --> Str : 1번째부터 3개의 문자를 리턴한다.
☞ SELECT SUBSTR(pj_cd, 3, 3) FROM tpd01m;
3. INITCAP ( column|expression )
; INITCAP('string') --> String : 첫글자만 대문자이고 나머지글자는 소문자로 변환
☞ SELECT INITCAP(pj_cd) FROM tpd01m;
4. SUBSTRB ( column|expression, m [,n]) : m값이 음수면 문자값의 끝부터.. )
; SUBSTRB('String',1,3) --> Str : 1번째부터 3개의 문자를 리턴한다.
. substr 글자기준
. substrb 바이트기준
예> col = "우리나라"
substr (col,1,2) --> 우리
substrb(col,1,2) --> 우
☞ SELECT SUBSTRB(pj_cd, -3, 3) FROM tpd01m;
5. UPPER ( column|expression )
; UPPER('String') --> STRING : 대문자로 변환
☞ SELECT UPPER(pj_cd) FROM tpd01m;
6. LOWER ( column|expression )
; LOWER('String') --> string : 소문자로 변환
☞ SELECT LOWER(pj_cd) FROM tpd01m;
7. LPAD ( column|expression,n,'string' ) : n 은 전체 길이
; LPAD('String',10,'*') --> ****String
; 10자리수중에 모자란 자리수를 '*'로 왼쪽에 채운다.(문자,숫자 가능!!!)
☞ SELECT LPAD(pj_cd, 20, '*') FROM tpd01m;
8. RPAD ( 'String',10,'*' ) --> String****
; 10자리수중에 모자란 자리수를 '*' 로 오른쪽에 채운다.(문자,숫자 가능!!!)
☞ SELECT RPAD(pj_cd, 20, '&') FROM tpd01m;
9. LENGTH ( column|expression )
; LENGTH('String') --> 6 : 문자열의 길이를 리턴한다.
☞ SELECT LENGTH(pj_cd) from tpd01m;
10. REPLACE ( column|expression, 0, 'r' )
; REPLACE(L01102 => Lr11r2
☞ SELECT REPLACE(pj_cd, 0, '^^') FROM tpd01m; --대소문자 구분
11. INSTR( column|expression )
; INSTR('String','r') --> 3 : 문자열에 'r'이 몇 번째 위치하고 있나를
(숫자)리턴한다.
☞ SELECT INSTR(pj_cd, 0) FROM tpd01m;
12. LTRIM(' String') --> 'String' : 문자열의 왼쪽 공백을 버린다.
13. RTRIM('String ') --> 'String' : 문자열의 오른쪽 공백을 버린다.
* TRIM(leading/tailing/both, trim_character FROM trim_source )
TRIM( 'S' FROM 'SSMITH') --> MITH
★. 숫자 처리함수
○ 숫자 처리 함수
1. ROUND(45.926, 2) --> 45.93 : 소수점 두 자리수 까지 보여주면서 반올림한다.
2. TRUNC(45.926, 2) --> 45.92 : 소수점 두 자리 까지만 보여주고 나머지는 버린다.
3. 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;
Date
○ 날짜 함수
Date 계산( 날짜를 숫자로 저장)
date + number : date에 number만큼 후의 날자를 보여준다.
date - number : date에 number만큼 전의 날자를 보여준다.
date1 - date2 : date1에서 date2 까지의 총 일수를 보여준다.( date1+date2는 X )
date1 + 숫자/24 : date1에서 시간을 더해 날짜를 보여준다.
nctions
MONTHS_BETWEEN('01-SEP-07','11-JAN-06') --> 19.6774194
; 두 날짜 사이의 달수를 보여준다.
ADD_MONTHS('11-JAN-07', 6) --> 11-JUL-07
; 날짜에 6개월을 더한 날자를 보여준다.
NEXT_DAY('01-SEP-07','FRIDAY') --> '08-SEP-07'
; 해당일 다음에 오는 FRIDAY의 일자를 보여준다.
LAST_DAY('01-SEP-07') --> '30-SEP-07'
; 해당월의 마지막 날자를 보여준다.
ROUND('25-JUL-07','MONTH') --> 01-AUG-07
ROUND('25-JUL-07','YEAR') --> 01-JAN-07
TRUNC('25-JUL-07','MONTH') --> 01-JUL-07
TRUNC('25-JUL-07','YEAR') --> 01-JAN-07
★. Conveion 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 --> 그 달의 몇 번째 주인가를 리턴한다.
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에 맞게 날자 형식으로 변환 한다.
○ NVL 함수
NVL(number_column, 0) : null일 때 0을 보여준다.
NVL(date_column, '01-JAN-08') : null일 때 '01-JAN-08'를 보여준다.
NVL(character_column, 'Empty') : null일 때 'Empty'를 보여준다.
* column Type과 표현식의 type이 반드시 일치해야 한다.
ORACLE 사용자를 위한 기본 SQL 함수 및 예제모음
/** 함수 - 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 '19940029' AND '20080011';
SELECT EMPNO, EMP_NAME, HOBBY 취미, NVL(WELL, '(없다)') 특기
FROM PERSONNEL
WHERE EMPNO BETWEEN '19940029' AND '20080011';
SELECT DECODE( '나', '나', '맞다', '아니다' ) "나가 나면 맞다, 아니면 아니다"
FROM DUAL ;
SELECT DECODE( '나', '대명', '아니다',
'너' , '아니다',
'그' , '아니다',
'나' , '맞다',
'모르겠다' ) FROM DUAL ;
SELECT EMPNO,
EMP_NAME,
DECODE(HT_CODE, '1', '현재원', '2', '휴직', '퇴사') HT_CODE
FROM PERSONNEL
WHERE EMPNO BETWEEN '19940029' AND '20080011';
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;