데이터베이스(오라클) 함수

함수

UPPER : 대문자로 변환

LOWER : 소문자로 변환

INITCAP : 첫글자만 대문자로 변환

 

EX)

SELECT ENAME, LOWER(ENAME), INITCAP(ENAME)
    FROM EMP;

 

필드값을 소문자,대문자로 출력하고싶을때 활용

 

---

LENGTH

글자수를 리턴해주는 함수

LENGTHB : 글자수를 바이트로 리턴

EX)

--LENGTH로  이름의 글자 수 구하기
SELECT ENAME, LENGTH(ENAME)
    FROM EMP;

 

예시
--EMP테이블에서 이름이 5글자인 레코드만 조회
SELECT * 
    FROM EMP
WHERE LENGTH(ENAME)=5;

 

LENGTH 와 LENGTHB의 차이

 

 SELECT LENGTH('가나'), LENGTHB('가나')
    FROM DUAL;
    
SELECT LENGTH('AB'), LENGTHB('AB')
    FROM DUAL;
    
    (DUAL은 임의의 더미테이블을 만들어 준다)
    
    SELECT 20*30 FROM DUAL;
    (단순 계산을 할때 더미테이블 활용)

 

'가나'는 2 , 6 로 출력(한글은 한글자당 3바이트)

'AB'는 2, 2 로 출력 (영어,숫자는 한글자당 1바이트)

---

ABS : 절대값을 구함

ROUND : 반올림을 해준다

EX) ROUND(반올림할 숫자, 반올림할 위치 [ 생략가능 = 생략시 0으로 처리 ] )

예시)
SELECT 12.3456, ROUND(12.3456), ROUND(12.3456, 2), ROUND(12.3456,-1)
    FROM DUAL;
 
반올림할 위치에 양수(소수), 음수(정수 부분)
2: 소수 셋째자리에서 반올림
1: 소수 둘째자리에서 반올림
0: 소수 첫째자리에서 반올림[생략가능]
-1: 1의 자리에서 반올림
-2: 10의 자리에서 반올림

---

TRUNC : 버림

예시)
SELECT 12.3456, TRUNC(12.3456),TRUNC(12.3456,2),TRUNC(12.3456,-1)
    FROM DUAL;
    
버릴 위치에 양수(소수), 음수(정수 부분)
2: 소수 셋째자리에서 버림
1: 소수 둘째자리에서 버림
0: 소수 첫째자리에서 버림[생략가능]
-1: 1의 자리에서 버림
-2: 10의 자리에서 버림

---

CEIL : 올림(두번째 인수 없음) 

-> 소수있으면 그냥 올려버림(정수로 바뀜)

 

FLOOR : 버림(두번째 인수 없음)

-> 소수가 있으면 그냥 날림

--CEIL, FLOOR
SELECT CEIL(12.345), FLOOR(12.345) FROM DUAL;

---

MOD(X, Y) -> X나누기 Y의 나머지를 구해주는 함수

EX)
SELECT MOD(10,3)FROM DUAL;

10나누기 3의 나머지 1이 출력됨

---

POWER(X, Y) -> X를 Y제곱한 결과를 출력

EX)
SELECT POWER(3,4) FROM DUAL;

3의 4제곱값 81이 출력

---

SUBSTR

--SUBSTR(문자열,X,Y)
해당 문자열(컬럼)에서 X번째부터 Y개만큼 출력

--SUBSTR(문자열,X,Y)
해당 문자열(컬럼)에서 X번째부터 Y개만큼 출력

 ( X가 양수이면 오른쪽부터 시작 음수면 반대 
 Y를 생략하면 끝까지 출력)

SELECT JOB, SUBSTR(JOB,1,2),--1번째 글자부터 2개까지 글자
            SUBSTR(JOB,3,2),--3번째 글자부터 2개까지 글자
            SUBSTR(JOB,5)   --5번째 글자부터 끝까지
    FROM EMP;

---

INSTR

문자열에서 찾을 문자가 몇번째인지 알려줌

INSTR(문자열(컬럼명), 찾을문자,(X),(Y))
-> 문자열에서 찾을 문자가 몇번째인지 알려줌
(X번재 부터 검색 시작해서 Y 번째 찾을 문자의 위치를 출력)

EX)

SELECT INSTR('HELLO ORACLE','L') FROM DUAL;
(왼쪽부터 몇번째에 L이있는지 출력 값:3)
SELECT INSTR('HELLO ORACLE','L',5) FROM DUAL;
(5번째 글자부터 부터 몇번째에 L이있는지 출력 값:4)

SELECT INSTR('HELLO ORACLE','L',1,2) FROM DUAL;
(1번째 글자부터 L를 찾아 두번째에있는 L의 위치를 찾아라 값 : 4)
HELLO ORACLE에서 L의 위치를 첫번째부터 검색해서 2번째 L의 위치가 출력됨
값: 4

 

---

REPLACE

REPLACE( ' 문자열(컬럼명) ' , ' X ' , ' Y ' )

해당 ' 문자열(컬럼명) ' 에서 ' X '를 ' Y ' 로 바꾸는 함수

SELECT REPLACE('010-1234-5678', '-', '*') FROM DUAL;
값:010*1234*5678

SELECT REPLACE('010-1234-5678', '-') FROM DUAL;
(Y값 생략시 지정된 X를 지움)
값:01012345678

---

PAD (LPAD, RPAD)

LPAD(' 문자열 ', X, Y )        RPAD(' 문자열 ', X, Y )

해달 문자열(컬럼)을 X칸만큼의 공간을 만들어서 빈칸은 Y로 채움(L은 왼쪽에채움, R은 오른쪽에 채움)

SELECT LPAD('ORACLE',10,'+'),RPAD('ORACLE',10,'+')
    FROM DUAL;
    
    출력값 : ++++ORACLE	ORACLE++++

 

 

CONCAT : 두개를 연결시켜줌

--CONCAT : 두개를 연결시켜줌
SELECT CONCAT(EMPNO, ENAME), EMPNO || ENAME
    FROM EMP;
    
    연산자 || 와 동일한 기능

 

---

SYSDATE

현재 날짜 구하기

--현재날짜
SELECT SYSDATE FROM DUAL;
--내일 날짜
SELECT SYSDATE+1 FROM DUAL;
--어제 날짜
SELECT SYSDATE-1 FROM DUAL;

---

ADD_MONTHS(날짜,X개월수)

-> 해당날짜에서 X개월수를 더한 날짜가 나옴

--3개월뒤의 날짜
SELECT ADD_MONTHS(SYSDATE,3) FROM DUAL;
--3개월전의 날짜
SELECT ADD_MONTHS(SYSDATE,-3) FROM DUAL;

 

EX)

EMP테이블에서 입사일에 10년뒤 날짜를 출력

SELECT hiredate,ADD_MONTHS(hiredate,120)FROM EMP;

 

NEXT_DAY

오늘 날짜 기반으로 다음에 돌아오는 요일을 구한다

SELECT SYSDATE, NEXT_DAY(sysdate,'월요일') from dual;

---

to_char -> 문자로 형변환

형식대로 날짜를 출력

yyyy : 년(4자리)

yy : 년(2자리)

mm(mon: 약자, month: 다나옴) : 월 

dd : 일 

ddd : 365일중 몇일인지

dy :요일(약자)

day : 요일(다 나옴)

w : 몇째주인지 나옴( 1년중 총 몇번째 주인지)

hh24 : 시(24시간 형식)

hh : 시 (12시간 형식)

mi : 분

ss : 초

SELECT to_char(sysdate, 'yyyy/mm/dd hh:mi:ss')from dual;

---

L : 지역화폐기호

9 : 숫자(0을 표시 안함) 9,900 -> 9,999,99 천단위 구분기호롸 소수 둘째자리까지

0 : 숫자(0을 표시 해줌) 9900

 

select to_char(sal, 'L999,999') from emp;

select to_char(sal, 'L000,000') from emp;

 

to_number -> 숫자로 형변환

숫자로 형변환

select '1300' - '1100' from dual;
select to_number('1300') - to_number('1100') from dual;

둘다 값이 200으로 나오긴하지만 상황에따라 to_number로 형변환을 거쳐야할때가 있음

 

 

to_date -> 날짜 형태로 형변환

select to_date('2020-01-01','yy/mm/dd') from dual;

값 : 20/01/01

 

NVL

NULL 값 처리하기

 

nvl(x,y) : x가 NULL 이면 Y출력 X가 NULL이 아니면 그대로 X의 데이터출력

 

EX)

nvl(sal + comm, sal)

sal + comm 계산한게 null? -> null이다 : sal만 출력

                                                  null아니다 : sal + comm 출력

select nvl(sal+comm,0)from emp;

null 값이 있을 경우 0으로 대체되어 출력

 

NVL2(X, Y, Z)

x가 null인가 아닌지 파악

만약 x가 null이 아니면 : y가 출력

만약 x가 null이면 : z가 출력

 

SELECT comm, nvl2(comm,'o','x') from emp;

 

nullif 

두개가 같은지를 비교해서 같으면 null을 출력

같지 않으면 첫번째 인수가 출력

SELECT nullif(10,10), nullif(10,20) from dual;

 

 

---

decode(switch ~ case 문과 동일한 함수)

 

ex)

직책(job)이 MANAGER면 1000, SALSEMAN이면 500 나머지는 300 출력

SELECT JOB, DECODE(JOB,
                    'MANAGER',1000,
                    'SALESMAN',500,
                    300)
            FROM EMP;
            
--위의 식과 동일
            
            SELECT JOB,
    CASE
        WHEN JOB = 'MANAGER' THEN 1000
        WHEN JOB = 'SALESMAN' THEN 500
        ELSE 300
    END AS 케이스
FROM EMP;