[30]오라클 함수

작성일 | In

오라클 함수

ASCII 문자변환

CHR()

1
2
SELECT CHR(65) FROM DUAL;   --A
SELECT CHR(97) FROM DUAL;   --a

|| 결합연산자

1
2
SELECT A || '와' ||  B || '를 연결한다'
FROM DUAL;

CONCAT()

CONCAT(칼럼명, ‘문자열’)

칼럼에 해당하는 문자열을 붙임

1
2
SELECT CONCAT('로미오와',' 줄리엣')
FROM DUAL;

SYSDATE

시스템의 현재 날짜/시간을 리턴하는 함수

1
2
3
SELECT SYSDATE FROM DUAL;
SELECT SYSDATE+100 FROM DUAL;
SELECT SYSDATE-100 FROM DUAL;

ROWNUM , ROWID

ROWNUM : 행(레코드) 번호

ROWID : 행(레코드)의 주소값

1
2
3
4
SELECT empno,A FROM DUAL;
SELECT ROWNUM,A,B FROM DUAL;
SELECT ROWID,A,B FROM DUAL;
SELECT ROWNUM,ROWID,A,B FROM DUAL;

두 날짜 사이의 개월 수 계산 함수

MONTHS_BETWEEN()

1.87096774193548387096774193548387096774

1
2
SELECT MONTHS_BETWEEN('2019-09-01','2019-07-05') 
FROM dual;

TO_DATE()

TO_DATE(‘날짜문자열’)

문자열을 날짜형으로 변환

1
2
3
4
SELECT TO_DATE('2019-05-20')
FROM dual;
SELECT TO_DATE('2019-05-20')-TO_DATE('2019-05-05')
FROM dual;  --15

TO_CHAR()

TO_CHAR(날짜, ‘출력형식’)

날짜자료형의 데이터를 문자변환 함수

1
2
3
4
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD AM HH:MI:SS DAY')
FROM dual; --2019-07-22 오후 04:26:09 월요일
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD AM HH24:MI:SS DAY')
FROM dual; --2019-07-22 오후 16:26:23 월요일 ->24시간 기준

숫자 함수

  • TRUNC(숫자1, 자릿수)
    • 버림, 숫자1을 소수점 자릿수에서 버림
  • ROUND(숫자1, 자릿수)
    • 숫자1을 소수점 자릿수에서 반올림
  • CEIL(숫자1)
    • 올림
  • AVG() : 평균
  • SUM() : 합계
  • MAX() : 최곳점
  • MIN() : 최솟점
  • COUNT() : 갯수 > NULL은 카운트 안함

DECODE()

DECODE(A, B, A==B일 때의 값, A<>B일 때의 값)

A<>B일 때의 값을 생략하면 NULL로 처리됨

DECODE함수의 매개변수의 갯수는 다중조건에 의해 늘어날 수 있다.

예) 주민번호를 이용해 성별 출력

SQL문 index는 1부터 시작(java는 0부터 시작!!)

1
2
3
SELECT SUBSTR('8912303',7,1) FROM DUAL;	--3
SELECT DECODE(SUBSTR('8912303',7,1), 3, '남자', '여자')
FROM DUAL;

연습 문제

문) 각 직원의 이름, 직급, 급여를 나타내되 단 급여는 5자리로 나타내며 부족한 자리수는 ‘*‘로 표시한다.

월급이 300이상인 직원만 나타내기

1
2
3
4
5
SELECT name
	   ,job
	   ,LPAD(sal, 5, '*')
FROM dual
WHERE sal>=300;

문) 각 직원들에 대해서 직원의 이름과 근무개월수를 나타내는데 근무 개월수가 100개월 이상인 직원만 나타내시오

근무개월수는 소수 부분을 제외한 정수 부분만 나타내시오

1
2
3
4
SELECT name
	   ,TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate))
FROM dual
WHERE TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)) >=100;

문) 전체 직원에 대하여 직원의 이름과 직급, 총 근무주(week)수를 구하시오

단, 근무주수가 많은 직원부터 나타내고, 근무주수가 같으면 이름에 대하여 오름차순 정렬하기.

1
2
3
4
5
SELECT name
	   ,job
	   ,TRUNC((SYSDATE-hiredate)/7) --근무주수
FROM dual
ORDER BY TRUNC((SYSDATE-hiredate)/7) , ename;