[Oracle] 단일행함수/ 오라클 함수/ 단일행 함수 예제

#단일행 함수 : 한번에 하나씩 처리하는 함수

 

 

#단일행 함수 종류

 

문자 함수

숫자 함수

날짜 함수

변환 함수

일반 함수

 

 

 

문자함수	변환함수	1) UPPER (대문자) king → KING   2) LOWER(소문자)   3) INITCAP(이니셜) KING = King   4) REPLACE → 문자 변경        REPLACE('Hello', 'l', 'k') → Hekko
	제어함수	1) CONCAT : 문자열 결합 → ||        CONCAT('A','B') → AB         = 'A'||'B'   2) SUBSTR: 문자를 분해 → subString()            SUBSTR('Hello Oracle', 7 ,3); 0 → Hel                                      문자시작위치, 문자 갯수   3) INSTR : 해당 위치의 문자번호, 특정 문자의 위치 찾기 => indexOf()      - INSTR('A/B/C/D', '/', 1,3) →기본 default 값이 1(생략가능)
	기타함수	1) LENGTH ==> 문자의 갯수   2) LENGTHB ==> Byte의 갯수   3) LPAD, RPAD        RPAD : 지정 된 숫자가 문자보다 크면 다른 값        rpad('abc', 10, '*')          => abc******* => 비밀번호, ID찾기   4) TRIM 좌우 제거, RTRIM 오른쪽 제거, LTRIM 왼쪽제거 => 지정한 문자를 제거       LTRIM('AAAAABBAAAA','A') → BBAAAA → 왼쪽에 있는 A가 다 지워짐       LTRIM('AAAAABBAAAA') → AAAAABBAAAA       LTRIM(LTRIM('AAAAABBAAAA'),'A')       ***in ad***
숫자함수	반올림 함수	- ROUND(실수,자리수) : 반올림 함수    ROUND(실수,n) => n+1    ROUND(987.654,2)     → 987.65    ROUND(987.654,0)     → 988    ROUND(987.654,-1)     → 990
	버림 함수	TRUNC(실수,자리수) : 버림 함수     TRUNC(987.654.2) → 987.65     ROUND(987.654,0) → 987
	올림 함수	CEIL(실수) : 올림 함수     CEIL(987.354) → 988
	나머지 함수	MOD(정수, 정수) : 나머지(%)      MOD(10,3) → 10%3 → 1
날짜함수	시스템의 날짜, 시간	SYSDATE : 시스템의 날짜, 시간     SELECT SYSDATE FROM DUAL;
	기간의 개월 수	MONTHS_BETWEEN : 기간의 개월 수  -- 입사한 날로부터 오늘까지의 개월 수 → 몇 년 근무했는지.    SELECT ename, MONTHS_BETWEEN(SYSDATE, hiredate) FROM emp;
	개월 추가	ADD_MONTHS : 개월 추가      ADD_MONTHS(SYSDATE,6) → 오늘부터 6개월 후의 날짜는 몇일인가
	요일에 해당하는 날짜	NEXT_DAY : 요일에 해당하는 날짜     NEXT_DAY(SYSDATE,'금')
	입력된 달의 마지막을 출력	LAST_DAT : 입력된 달의 마지막을 출력
변환함수	문자열 반환	TO_CHAR               = 날짜 → 문자열                    YYYY (yyyy)                   RRRR (rrrr)                    YY                   RR                   YEAR → 연도를 영문으로 표시                   MM : 월 => 01,  12                   Fri Aug 07 15:12:50 KST 2020                   MON : Aug 07                   MONTH : 전체 영문                  ***DD     07                   DAY      요일(리눅스 : 영문, 윈도우 : 한글)                   DDTH                   HH     => 12시간 => 03시                    HH24  => 24시간 => 15시                   MI : 분                   SS : 초                = 정수 → 문자열                  9   TO_CHAR(1234, '9,999,999') => 1,234                  $   TO_CHAR(1234, '$9,999,999') => $800                  L   TO_CHAR(1234,  'L9,999')
	정수 변환	TO_NUMBER     TO_NUMBER('5,000')
	날짜 변환	TO_DATE : 날짜 변환
(기타) 일반함수	NVL	NVL : NULL값을 다른 값으로 변경
	DECODE	DECODE : 다중 IF    DECODE(컬럼명, 값, 출력값,                         값, 출력값,                          값, 출력값)

 

 

#문자-기타함수 예제

--기타함수 
-- LENGTH
SELECT ename, LENGTH(ename), LENGTHB(ename) FROM emp;
SELECT LENGTHB('HONG'), LENGTHB('홍길동') FROM DUAL;
 
SELECT ename FROM emp
WHERE LENGTH(ename)=5;
 
SELECT CONCAT('Hello', 'Oracle'), 'Hello' ||'Oracle' FROM DUAL;
SELECT SUBSTR('Helllo Oracle', 7,3) FROM DUAL;
 
--emp 테이블에서 12월에 입사한 사원의 모든 정보 출력
SELECT * FROM emp
WHERE SUBSTR(hiredate,4,2)=12;
--80/12/07  => 4번째부터 2글자씩 가져옴
 
--emp 테이블에서 3일에 입사한 사원의 모든 정보 출력
SELECT * FROM emp
WHERE SUBSTR(hiredate,7,2)=3;
 
--emp이름 중에 3번째 자리가 T인 사원의 모든 정보 출력
SELECT * FROM emp WHERE SUBSTR(ename,3,1)='A';
SELECT * FROM emp WHERE ename LIKE '__A%';
 
SELECT SUBSTR('Helllo Oracle', -6,3) FROM DUAL;
-- 왼쪽 ==> 오른쪽
SELECT SUBSTR('Helllo Oracle', -3,3) FROM DUAL;
-- 오른쪽 ==> 왼쪽
 
SELECT INSTR('A/B/C/D','/',1,2) FROM DUAL;
-- 첫번째 값 1일때는 생략가능
 
SELECT INSTR('A/B/C/D','/',3,2) FROM DUAL;
 
SELECT LPAD('SCOTT',10,'*')FROM DUAL;
 
SELECT ename, RPAD(SUBSTR(ename,1,2),LENGTH(ename),'*') FROM emp;
 
SELECT RPAD('Hello Oracle',20,'#')FROM DUAL;
- 20 => 문자 출력 갯수
 
SELECT LTRIM('  AAAAABBBAAAA','A') FROM DUAL;
--BBBAAAAA
 
SELECT RTRIM('AAAAABBBAAAA','A') FROM DUAL;
--AAAAABBB
 
SELECT LTRIM(LTIRM('AAAAABBBAAAA'),'A') FROM DUAL;
--AAAAABBB
 
SELECT TRIM('A' FROM 'AAAAABBBAAAA') FROM DUAL;
 
--emp에서 이름중에 => A를 제거해서 출력
SELECT ename, LTRIM(ename, 'A')FROM emp;
SELECT ename, REPLACE(ename, 'A', 'M') FROM emp;

 

 

#숫자함수 예제

--반올림함수 ROUND
SELECT ROUND(987.654,2),ROUND(987.654,0),ROUND(987.654,-1) FROM DUAL;
 
--버림함수 TRUNC
SELECT TRUNC(987.654,2),TRUNC(987.654,0),TRUNC(987.654,-1) FROM DUAL;
 
--올림함수 CEIL
SELECT CEIL(987.354) FROM DUAL;
 
-- CEIL => 총 페이지 구하기
SELECT CEIL(COUNT(*)/10.0) FROM emp;
SELECT CEIL(COUNT(*)/7.0) FROM genie_music;
 
--나머지함수 MOD
SELECT MOD(10,3) FROM DUAL;
 
SELECT empno, ename FROM emp WHERE MOD(empno,2)=0;

-- 기타함수
SELECT ename, sal, comm, sal+comm FROM emp;
 
 
--NVL
SELECT ename, sal, comm, sal+NVL(comm,0) FROM emp;
 
 
--DECODE
SELECT ename, deptno FROM emp;
 
SELECT ename, deptno, DECODE(deptno, 10, '영업부', 
                                                         20, '개발부',
                                                         30, '기획부') as dname
FROM emp;
--switch case 형태
 
SELECT ename, CASE WHEN deptno=10 THEN '영업부'
                              WHEN deptno=20 THEN '개발부'
                              WHEN deptno=30 THEN '기획부'
                        END "dname"
FROM emp;
 
SELECT title, DECODE(state, '유지','-',
                                      '상승','▲',
                                      '하강','▼',
                                      'new','new') as 상태
FROM genie_music;
 
SELECT title, CASE WHEN state='유지' THEN '-' 
                              WHEN state='상승' THEN '▲'
                              WHEN state= '하강' THEN '▼'
                              WHEN state= 'new' THEN 'new'
          END "상태"
FROM genie_music;
 
SELECT ename, sal, RANK() OVER(ORDER BY sal DESC) as rank FROM emp;
 
SELECT ename, sal, RANK() OVER(ORDER BY sal) as rank FROM emp;
 
SELECT ename, sal, DENSE_RANK() OVER(ORDER BY sal) as rank FROM emp;
 
SELECT ename, SUBSTR(hiredate, 4, 2) "분기",
           CASE WHEN SUBSTR(hiredate, 4,2) BETWEEN '01' AND '03' THEN '1/4분기'
                   WHEN SUBSTR(hiredate, 4,2) BETWEEN '04' AND '06' THEN '2/4분기'
                   WHEN SUBSTR(hiredate, 4,2) BETWEEN '07' AND '09' THEN '3/4분기'
                   WHEN SUBSTR(hiredate, 4,2) BETWEEN '10' AND '12' THEN '4/4분기'
END "입사주기" 
FROM emp;
 
--sal => 1~1000 (Level 1), 1001~2000 (Level 2), 2001~3000(Level3), 3001~4000(Level4), 4001~5000(Level5)
SELECT ename, sal,
           CASE WHEN sal BETWEEN '1' AND '1000' THEN 'Level 1'
                   WHEN sal BETWEEN '1001' AND '2000' THEN 'Level 2'
                   WHEN sal BETWEEN '2001' AND '3000' THEN 'Level 3'
                   WHEN sal BETWEEN '3001' AND '4000' THEN 'Level 4'
                   WHEN sal BETWEEN '4001' AND '5000' THEN 'Level 5'
END "Level"
FROM emp;