(oracle)SQL함수 정리

-- 무조건 올림
SELECT CEIL(13.11) FROM DUAL;

 

-- 반올림
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;

 

-- IF + THEN 1 ELSEIF 0 THEN 0 ELSE -1
SELECT SIGN(5.342), SIGN(0), SIGN(-2334) FROM DUAL;
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;

-- 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 +
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;

-- 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;