-- 반올림
SELECT ROUND(345.123, 0), ROUND(345.123,2), ROUND(345.123, -1) FROM DUAL;
-- 나머지
SELECT MOD(23,5) FROM DUAL;
-- 승수값
SELECT POWER(3,2), POWER(3, -2) FROM DUAL;
-- 버림값
SELECT TRUNC(345.123, 1), TRUNC(345.123, 0), TRUNC(345.123, -1) FROM DUAL;
-- 양수이면 1, 0이면 0, 음수이면 -1
SELECT SIGN(5.342), SIGN(0), SIGN(-2334) FROM DUAL;
-- IF + THEN 1 ELSIF - THEN -1 ELSE 0
SELECT DECODE( SIGN(POWER(2,10)-1000), 1, '2의 10승이 1000보다 크다',
-1, '2의 10승이 1000보다 작다', '2의 10승이 1000이다') 비교답 FROM DUAL;
-- ASCII <--> CHAR
SELECT CHR(65) "CHR", ASCII('A') "ASCII" FROM DUAL;
-- 대소문자 변환
SELECT LOWER('My name is KIMJINDOO') "LOWER", UPPER('My name is kimjindoo') "UPPER" FROM DUAL;
-- 자릿수 맞춰 채우기
SELECT LPAD('DALMA', 10, '*') "LPAD", RPAD('DALMA', LENGTH('DALMA')+11, '@dalcom.net') "RPAD" FROM DUAL;
SELECT LPAD('1234567890', 20, '+') || RPAD('1234567890', 20, '^') "1234567890" FROM DUAL;
SELECT LPAD('1,234,567', 30, ' ') "LPAD 사용으로 30자리 맞춤", '1,234,567' "단순문자 사용",
1234567 "단순숫자 사용" FROM DUAL;
-- 공백 제거
SELECT REPLACE(LTRIM(' AAA '), ' ', '0') "LTRIM",
REPLACE(RTRIM(' AAA '), ' ', '0') "RTRIM" FROM DUAL;
-- 문자열 치환
SELECT REPLACE('ORACLE', 'A', 'a') "REPLACE" FROM DUAL;
SELECT EMP_NAME, REPLACE(EMP_NAME, '이', '박') "이->박" FROM PERSONNEL WHERE EMP_NAME LIKE '이%';
-- 문자열의 일부만을 취하기
SELECT SUBSTR('ORACLE 프로젝트', 1, 3) SUBSTR1, SUBSTR('오라클 프로젝트', 4, 5) SUBSTR2,
SUBSTR('오라클 PROJECT', 10) SUBSTR3 FROM DUAL;
SELECT SUBSTRB('ORACLE 프로젝트', 2, 3) SUBSTRB1, SUBSTRB('오라클 프로젝트', 4, 5) SUBSTRB2,
SUBSTRB('오라클 PROJECT', 10) SUBSTRB3 FROM DUAL;
-- 문자열의 길이
SELECT EMPNO, LENGTH(EMPNO), LENGTHB(EMPNO), EMP_NAME, LENGTH(EMP_NAME), LENGTHB(EMP_NAME) FROM
PERSONNEL WHERE EMPNO>'98102'
SELECT LENGTH('가나다') "LENGTH", LENGTHB('가나다') "LENGTHB" FROM DUAL;
-- LANGUAGE : KOREAN_KOREA.KO16KSC5601
SELECT LENGTH('학교') FROM DUAL; RESULT: 2
SELECT LENGTHB('학교') FROM DUAL; RESULT: 4
-- LANGUAGE : AMERICAN_AMERICA.US7ASCII
SELECT LENGTH('학교') FROM DUAL; RESULT: 4
-- 문자열에서 특정 문자나 문자열의 위치 INSTR[B](string1, [char||string], start_position, n_counted)
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 INSTR('하늘 아래 하늘이 또 있겠는가.', '하늘', 1, 2) 하늘1,INSTRB('하늘 아래 하늘이 또 있겠는가.', '하늘', 1, 2) 하늘2 FROM DUAL;
-- 병합 (||)
다음 쿼리문은 SP_GDS, SP_COMP_CODE, MALL_DESC를 '|'를 구분자로 하여 병합한다.
SELECT DISTINCT(SP_GDS) || '|' || SP_COMP_CODE || '|' || MALL_DESC
FROM TCA_SP_ACMP
WHERE SP_TRD_DTIME BETWEEN TO_DATE('20070101', 'YYYYMMDD') AND
TO_DATE('20070131', 'YYYYMMDD') AND SP_COMP_CODE='0210';
-- SYSTEM 시간
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') "SYSDATE" FROM DUAL;
-- 주어진 날짜가 속하는 월의 마지막 날짜
SELECT TO_CHAR(LAST_DAY(SYSDATE), 'YYYY-MM-DD HH24:MI:SS') "LAST_DAY" FROM DUAL;
-- 주어진 두 날짜간의 기간을 월 단위로 계산
-- MONTHS_BETWEEN(date1, date2) : IF date1 > date2 THEN +
9i에서는 요로케...
SELECT MONTHS_BETWEEN(
TO_DATE('2006/12/26', 'YYYY/MM/DD'), TO_DATE('2007/01/25','YYYY/MM/DD')) "MONTHS_BETWEEN(-1)",
MONTHS_BETWEEN(
TO_DATE('2006/01/13', 'YYYY/MM/DD'), TO_DATE('2005/11/13', 'YYYY/MM/DD')) "MONTHS_BETWEEN(+)"
FROM DUAL;
10g에서는 요로케...
SELECT MONTHS_BETWEEN('2006/12/26', '2007/01/25') "MONTHS_BETWEEN(-)",
MONTHS_BETWEEN('2006/01/13', '2005/11/13') "MONTHS_BETWEEN(+)" FROM DUAL;
SELECT ROUND(MONTHS_BETWEEN('2006/12/28', '2006/12/01'), 1) FROM DUAL;
-- 월단위 계산
SELECT ADD_MONTHS(SYSDATE, 12) "ADD_MONTHS(+)", ADD_MONTHS(SYSDATE, -12) "ADD_MONTHS(-1)" FROM DUAL;
-- 주어진 날짜를 기준으로 주어진 요일이 처음 오는 날짜
-- 일요일:1 ~ 토요일:7
SELECT SYSDATE, NEXT_DAY(SYSDATE, '일요일') "NEXT_DAY 1",
NEXT_DAY(SYSDATE, 1) "NEXT_DAY 2" FROM DUAL;
LANGUAGE 설정에 따라 영문으로도..
SELECT NEXT_DAY(SYSDATE, 'Sunday') "NEXT_DAY 1" FROM DUAL;
-- NUMBER와 DATE를 문자타입으로 변환
SELECT TO_CHAR(123456789) "NUMBER", TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS') "DATE" FROM DUAL;
-- 문자를 숫자로 변환
SELECT TO_NUMBER('123456789') "TO_NUMBER1",
TO_NUMBER('123,456.9', '999,999.9') "TO_NUMBER2",
TO_NUMBER('1,234,567', '9G999G999') "TO_NUMBER3" FROM DUAL;
-- 문자형 데이타를 DATE형으로 변환
SELECT TO_DATE('20020824', 'YYYYMMDD') "TO_DATE1",
TO_DATE('2002-08-24', 'YYYY-MM-DD') "TO_DATE2",
TO_DATE('200208', 'YYYYMM') FROM DUAL;
-- 평균값 구하기
SELECT AVG(HEIGHT), AVG(WEIGHT) FROM PERSONNEL;
-- 최대값 최소값
SELECT MAX(EMPNO), MAX(EMP_NAME), MIN(EMPNO), MIN(EMP_NAME) FROM PERSONNEL;
-- 합계 구하기
SELECT SUM(WEIGHT) FROM PERSONNEL;
-- 조회 범위의 조회 건수
SELECT COUNT(*), COUNT(EMPNO), COUNT(JIKCH_CODE) FROM PERSONNEL;
-- NULL 값 치환
SELECT EMPNO, EMP_NAME, HOBBY 취미, NVL(WELL, '없음') 특기
FROM PERSONNEL WHERE EMPNO BETWEEN '98001' AND '98005';
-- 연속 조건문
-- DECODE(a, b, c, d) a가 b면 c고, 아니면 d
-- DECODE(a, b, c, d, e, f, g, h ....) a가 b면 c고, d면 e고, f면 g고 h면 ...
-- DECODE(a, b, c, DECODE(e, f, g, h))
SELECT EMPNO, EMP_NAME, DECODE(HT_CODE, '1', '현재원', '2', '휴직원', '퇴사') HT_CODE
FROM PERSONNEL WHERE EMPNO BETWEEN '98071' AND '98080';
-- 오라클 환경변수 값 구하기
SELECT USERENV('LANGUAGE') "LANGUAGE", USERENV('TERMINAL') "TERMINAL",
USERENV('SESSIONID') "SESSIONID" FROM DUAL;
-- 주어진 데이타중 최대값 최소값 구하기
SELECT GREATEST(132,33 ,45,99,22, 32, 77, 12) GREATEST, LEAST(132,33 ,45,99,22, 32, 77, 12) FROM DUAL;
SELECT GREATEST('가', '나', '다', '라', '마') GREATEST1, GREATEST('가', '나', '다', '라', '마', '마마') GREATEST2,
LEAST('가', '나', '다', '라', '마') FROM DUAL;
-- UID, USER
SELECT UID, USER FROM DUAL;
-- TO_CHAR : NUMBER와 DATE를 문자로 변환
TO_CHAR(n)
TO_CHAR(n, format)
|
n(number) |
format |
format |
|
1000 |
‘9,999.99’ |
‘9G999D99’ |
|
1234567.890 |
‘9,999,999.999’ |
‘9G999G999D999’ |
|
3.5 |
‘9,999.99’ |
‘9G999D99’ |
|
1234 |
‘999,999’ |
‘999G999’ |
TO_CHAR(date, format)
|
date |
format |
format sample |
|
sysdate |
‘YYYYMMDD’ |
‘20070321’ |
|
‘YYYY/MM/DD’ |
‘ | |
|
‘YYYY-MM-DD’ |
‘ | |
|
‘YYYYMMDD HH24MISS |
‘20070321 215710’ | |
|
‘YYYYMMDD HHMISS AM’ |
‘20070321 095710 PM’ | |
|
‘YYYY/MM/DD HH24:MI:SS’ |
‘ | |
|
‘YYYY/MM/DD HH24:MI:SS:SSSSS’ |
‘ |
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_CHAR4,
TO_CHAR(1234567.891, '9,999,999.0000') TO_CHAR5,
TO_CHAR(123, '9,999.00') TO_CHAR6,
TO_CHAR(123, '9,999.99') TO_CHAR7
FROM DUAL;
SELECT TO_CHAR(1234567.891, '9G999G999') TO_CHAR1,
TO_CHAR(1234567.891, '0D0000') TO_CHAR2,
TO_CHAR(1234567.891, '9G999G999D0000') TO_CHAR3,
TO_CHAR(123, '9G999D00') TO_CHAR4,
TO_CHAR(123, '9G999D99') TO_CHAR5
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('20070321', 'YYYYMMDD'), 'YYYY-MM-DD') TO_CHAR7,
TO_CHAR(TO_DATE('20070321', 'YYYYMMDD'), 'YYYYMMDD HHMISS') TO_CHAR8,
TO_CHAR(SYSDATE, 'MONTH') TO_CHAR9,
TO_CHAR(SYSDATE, 'YEAR') TO_CHAR10,
FROM DUAL;