Oracle DB 함수
in Programming on SQL
1) 단일행 함수
- 레코드 하나하나에 함수가 적용
문자 처리 함수
LOWER, UPPER, INITCAP, LENGTH, CONCAT, RPAD,SUBSTR
SELECT first_name, upper(first_name), lower(first_name)
FROM employees;
initcap은 첫글자는 대문자 나머지는 소문자
SELECT INITCAP('soap')
FROM dual; --가상 테이블, 테스트할 때 유용
SUBSTR로 부분 문자열 출력 가능
SELECT first_name,
--JAVA와 다르게 인덱스는 1부터 시작하고, 세번째 ARG는 3글자를 나타낸다.
SUBSTR(first_name,2,3) FROM employees;
RPAD
SELECT RPAD(SUBSTR(PHONE_NUMBER,1,6),LENGTH(PHONE_NUMBER),'*') FROM EMPLOYEES;
CONCAT
SELECT 'ICE' || ' ' || 'COFFE' FROM DUAL;
SELECT CONCAT('ICE',' COFFEE') FROM DUAL; --무조건 두개만, 세개 쓰면 오류난다.
SELECT FIRST_NAME ||' '|| LAST_NAME FROM EMPLOYEES;
숫자 처리 함수
CEIL
올림 함수
SELECT CEIL(42.435) FROM DUAL;
FLOOR
버림 함수
SELECT FLOOR(42.435) FROM DUAL;
TRUNC
소수점 이하를 버리는 함수
SELECT TRUNC(42.435) FROM DUAL;
TRUNC와 FLOOR의 차이점, FLOOR는 내림이고, FLOOR은 그냥 소수점을 이하를 없애는 것이다. 양수에서는 차이점이 없지만, 음수에서는 차이점이 나타난다. FLOOR(-48.25) = -49, TRUNC(-48.25) = -48 이렇게.
ROUND
반올림 함수
-- 두번째 ARG인 1, 소숫점 이하 첫번째 자리 의미
SELECT ROUND(42.435,1) FROM DUAL;
-- 결과 : 42.4
-- 두번째 ARG인 1, 소숫점 이하 첫번째 자리 의미
SELECT ROUND(42.435,-1) FROM DUAL;
-- 결과 : 40
MOD
SELECT MOD(9,2) FROM DUAL;
날짜 다루는 함수
-- 시스템 상의 날짜를 가져온다.
SELECT SYSDATE FROM DUAL;
--30일 후 날짜
SELECT SYSDATE+30 FROM DUAL;
--근속 일수
SELECT FIRST_NAME, HIRE_DATE, TRUNC(SYSDATE-HIRE_DATE,2)
FROM EMPLOYEES;
--근속 개월수
SELECT FIRST_NAME AS "이름", HIRE_DATE AS "입사일", ROUND(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)) AS "근속 개월"
FROM EMPLOYEES
ORDER BY 3;--3번째 컬럼을 기준으로 정렬해서 보여줌
ADD_MONTHS
-- 5개월 후
SELECT ADD_MONTHS(SYSDATE,5) FROM DUAL;
select sysdate, next_day(sysdate,'월요일'),last_day('19/03/01')
from dual;
TO_CHAR(날짜, 포맷)
select to_char(sysdate,'YYYY/MM/DD HH24:MI:SS')
from dual; -- 2020/10/14 13:21:06
--MM 대신 MON 사용하면 현재 내가 있는 곳의 달로 나옴. 10월 이렇게
select to_char(sysdate,'YYYY/MON/DD HH24:MI:SS') -- 2020/10월/14 13:21:25
select to_char(sysdate,'YYYY/MON/DDD HH24:MI:SS')
from dual; --2020/10월/288 13:22:13
SELECT FIRST_NAME,SALARY, TO_CHAR(SALARY,'$999,999'),TO_CHAR(SALARY,'$099,999')
FROM EMPLOYEES
WHERE DEPARTMENT_ID=80;
Karen 13500 $13,500 $013,500 Alberto 12000 $12,000 $012,000 Gerald 11000 $11,000 $011,000 Eleni 10500 $10,500 $010,500
이런식으로 자리 맞출 때 앞에 0 써준다.
TO_DATE
SELECT FIRST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE ADD_MONTHS(TO_DATE(HIRE_DATE),180) < SYSDATE
ORDER BY HIRE_DATE;
SELECT FIRST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE (SYSDATE-HIRE_DATE)/365 >=15
ORDER BY HIRE_DATE;
NVL
NULL을 다른 문자로 대체
SELECT FIRST_NAME, SALARY, COMMISSION_PCT, NVL(COMMISSION_PCT,0)
FROM EMPLOYEES;
NVL2
NULL이 아니면 두번째 ARG 실행, NULL이면 뒤에꺼 수행
SELECT FIRST_NAME, SALARY, COMMISSION_PCT, NVL2(COMMISSION_PCT,SALARY*(COMMISSION_PCT+1),SALARY*12) AS 연봉
FROM EMPLOYEES;
--형식 지켜서, TO_CHAR이 NVL2 함수를 감싼다.
SELECT FIRST_NAME, SALARY, COMMISSION_PCT, TO_CHAR(NVL2(COMMISSION_PCT, SALARY*(COMMISSION_PCT+1),SALARY*12),'$999,999') AS 연봉
FROM EMPLOYEES;
DECODE
JAVA의 SWITCH CASE 문과 비슷하다.
DECODE(컬럼명, 조건1, 식1,
조건2, 식2,
마지막 식)
SELECT FIRST_NAME, DEPARTMENT_ID,
DECODE(DEPARTMENT_ID,
10,'ADMIN' ,
20, 'MARKETING',
30, 'PURCHASING',
'ETC'
) AS "DEPART"
FROM EMPLOYEES;
CASE WHEN THEN END
함수가 아니라서 ( ) , 사용하지 않는다
DECODE는 수식을 못쓰는데, 이것은 수식을 쓸 수 있기 때문에 좋다.
SELECT FIRST_NAME, DEPARTMENT_ID,
CASE WHEN DEPARTMENT_ID =10 THEN 'ADMIN'
WHEN DEPARTMENT_ID =20 THEN 'MARKETING'
WHEN DEPARTMENT_ID =30 THEN 'PURCHASING'
ELSE 'ETC'
END
FROM EMPLOYEES;
2) 그룹행 함수
- 레코드를 특정 조건으로 그룹화 한 후 그룹을 기준으로 함수를 적용
SUM, ROUND, MIN, MAX, COUNT
SELECT SUM(SALARY) ,ROUND(AVG(SALARY),2), MIN(SALARY),MAX(SALARY),COUNT(SALARY) FROM EMPLOYEES;
GROUP BY절과 함께 사용하기
SELECT SUM(SALARY) "급여합계", DEPARTMENT_ID
FROM EMPLOYEES
group by DEPARTMENT_ID
ORDER BY DEPARTMENT_ID;--ORDER BY 는 항상 마지막에
--아래와 같이 사용하면 오류가 뜬다
--전자는 1개, 후자는 107개기 때문에 안 맞는다. 그래서 그룹화와 적용되지 않은 함수와는 함께 사용할 수 없다.
SELECT SUM(SALARY) "급여합계", DEPARTMENT_ID
FROM EMPLOYEES;
HAVING 조건과 GROUP BY
SELECT SUM(SALARY) "급여합계", DEPARTMENT_ID
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
HAVING SUM(SALARY) >=150000
ORDER BY SUM(SALARY);
