오라클 SQL함수

6. SQL함수

6-2문자함수

대소문자 변환 함수

INITCAP 문자열의 첫 번째 문자만 대문자로 변환

LOWER 문자열 전체를 소문자로 변환

UPPER 문자열 전체를 대문자로 변환

> SELECT name, INITCAP(userid) FROM student;

> SELECT userid, LOWER(userid), UPPER(userid) FROM student;

---

• DAUL 테이블

• SELECT 명령문에서 필수절인 FROM절은 반드시 하나 이상의 테이블을 지정해야 한다. 하지만 SELECT 명령문으로 산술 연산만 하는 경우에는 테이블을 지정할 필요가 없다. DUAL 테이블은 이러한 경우에 사용할 수 있는 편리한 테이블이다.

> SELECT UPPER(‘gdhong’) INIT_NAME FROM STUDENT; - student 테이블의 행수만큼 출력

> SELECT UPPER(‘gdhong’) INIT_NAME FROM dual; 한행만 출력 (복붙 에러 = ‘’ 직접 입력 필)

> SELECT UPPER(‘gdhong’) INIT_NAME FROM student; = student 테이블 행수만큼 출력

---

문자열 길이 반환 함수

LENGTH (문자열 길이를 반환)

LENGTHB (문자열 바이트 수를 반환)

> SELECT dname, LENGTH(dname), LENGTHB(dname) FROM department;

문자조작 함수

CONCAT : 두 문자열을 결합, ‘||’와 동일 - ex) CONCAT(‘sql’, ‘plus’) > sqlplus

SUBSTR : 특정 문자 또는 문자열 일부를 추출 - ex) SUBSTR(‘SQL*Plus’, 5,4) > Plus

INSTR : 특정 문자가 출현하는 첫 번째 위치를 반환 - ex) INSTR(‘SQL*Plus’, ‘*’) > 4

LPAD : 오른쪽 정렬후 왼쪽에 지정문자를 삽입 - ex) LPAD(‘sql’, 5, ‘*’) > **sql

RPAD : 왼쪽 정렬후 오른쪽에 지정문자를 삽입 - ex) RPAD(‘sql’, 5, ‘*’) > sql**

LTRIM : 왼쪽의 지정문자를 삭제 - ex) LTRIM(‘*sql’, ‘*’) sql

RTRIM : 오른쪽의 지정문자를 삭제 - ex) LTRIM(‘sql*’, ‘*’) sql

SUBSTR

:문자열의 일부를 추출하는 함수이다 (주민번호 생년월일 추출이나 성별 추출하는 경우 사용)

사용법

: SUBSTR (*expr |column, m[ ,n] )

문자열에서 m번째 문자부터 n개의 문자 추출

m이 음수이면 시작 위치는 문자열의 마지막

n을 생략하면 m부터 마지막 문자까지 추출

> SELECT name, idnum, SUBSTR(idnum, 1, 6) birth_date, SUBSTR(idnum, 3, 2) birth_mon FROM student WHERE grade = ‘1’;

INSTR

: 문자열중에서 사용자가 지정한 특정 문자가 포함된 위치를 반환하는 함수.(일반적으로 INSTR함수는 다른함수와 중첩하여 사용 ex) 042)632-7777와 같은 형식의 전화번호에서 ‘)’ 왼편의 지역번호는 INSTR함수와 SUBSTR함수와 중첩하여 추출할 수 있다.

> SELECT dname, INSTR(dname, ‘과’) FROM department;

학생테이블의 전화번호칼럼에서 지역번호 추출

> SELECT name, SUBSTR(tel, 1, INSTR(tel,’)’)-1) tel_loc FROM student;

LPAD, RPAD

문자열이 일정한 크기가 되도록 왼쪽 또는 오른쪽에 지정한 문자를 삽입하는 함수

교수테이블의 직급칼럼의 왼쪽에 ‘*’ 문자를 삽입하여 10바이트로 출력, 아이디 칼럼은 오른쪽에 ‘+’문자를 삽입하여 12바이트로 출력

SELECT position, LPAD(position, 10, ‘*’) userid, RPAD(userid, 12, ‘+’) rpad_userid FROM professor;

LTRIM, RTRIM

: 문자열에서 특정 문자를 삭제하기 위한 용도로 사용한다.

함수의 인수에서 삭제할 문자를 지정하지 않으면 문자열의 앞뒤 부분에 있는 공백 문자를 제거한다.

부서테이블에서 부서 이름의 마지막 글자인 ‘과’를 삭제하여 출력.

> SELECT dname, RTRIM(dname, ‘과’) FROM department;

숫자함수

: 숫자 데이터를 처리하기 위한 함수

ROUND : 지정한 자리 이하에서 반올림 - ex) ROUND(123.17,1) > 123.2

TRUNC : 지정한 자리 이하에서 절삭 - ex) TRUNC(123.17, 1) > 123.1

MOD : m을 n으로 나눈 나머지 - ex) MOD(12,10) > 2

CEIL : 지정한 값보다 큰 수 중에서 가장 작은 정수 CEIL(123.17) > 124

FLOOR : 지정한 값보다 작은 수 중에서 가장 큰 정수 FLOOR(123.17) > 123

ROUND

: 지정한 자리 이하에서 반올림한 결과 값을 반환하는 함수이다

ROUND(a,b) a를 소수점 이나 b+1자리에서 반올림하여 b자리까지 출력.

b가 없으면 소수점 이하를 출력하지 않으며 b값이 음수이면 소수점 왼쪽 b자리에서 반올림하여 출력한다.

> SELECT name, sal, sal/22, ROUND(sal/22), ROUND(sal/22, 2), ROUND(sal/22, -1) FROM professor WHERE deptno = 101;

TRUNC

: 지정한 소수점 자리수 이하를 절삭한 결과 값을 반환하는 함수.

TRUNC(a,b) 는 a를 소수점 이하 b+1자리에서 절삭하여 b자리까지 출력한다. b가 없으면 소숫점 이하를 출력하지 않으며 b값이 음수이면 소숫점 왼쪽 b자리에서 절삭한 값을 출력한다 .

> SELECT name, sal, sal/22, TRUNC(sal/22), TRUNC(sal/22, 2), TRUNC(sal/22, -1) FROM professor WHERE deptno=101;

MOD

: 나누기 연산 후 나머지를 출력하는 함수. (NULL 연산후의 결과도 NULL이다)

> SELECT name, sal, comm, MOD(sal, comm) FROM professor WHERE deptno = 101;

CEIL

: 지정한 숫자보다 크거나 같은 정수중에서 최소 값을 출력하는 함수이다.

FLOOR

: 지정한 숫자보다 작거나 같은 정수중에서 최대값을 출력하는 함수이다.

> SELECT CEIL(19.7), FLOOR(12.345) FROM dual;

날짜 함수

날짜 계산

날짜 + 숫자 = 날짜 (날짜에 일수를 가산하여 날짜 계산)

날짜 - 숫자 = 날짜 (날짜에 일수를 감산하여 날짜 계산)

날짜 - 날짜 = 일수 (날짜와 날짜를 감산하여 일수 계산)

날짜 + 숫자/24 = 날짜 (날짜에 시간을 가산하여 날짜 계산)

교수 번호가 9908인 교수의 입사일 기준 30일 후와 60일 후의 날짜를 출력.

> SELECT name, hiredate, hiredate+30, hiredate+60 FROM professor WHERE profno=9908;

날짜 함수

: 날짜 또는 기간을 결과값으로 반환하는 함수.

SYSDATE : 시스템의 현재 날짜 > 결과=날짜

MONTHS_BETWEEN : 날짜와 날짜사이의 개월을 계산 > 결과=숫자

ADD_MONTHS : 날짜에 개월을 더한 날짜 계산 > 결과=날짜

NEXT_DAY : 날짜 후의 첫 요일의 날짜를 계산 > 결과=날짜

LAST_DAY : 월의 마지막 날짜를 계산 > 결과=날짜

ROUND : 날짜를 반올림 > 결과=날짜

TRUNC : 날짜를 절삭 > 결과=날짜

SYSDATE함수는 시스템에 저장된 현재 날짜를 반환하는 함수로서 초 단위까지 반환한다.(ex- 웹 게시판에서 글을 등록한 시간을 자동적으로 저장 할 수 있다.)

> SELECT SYSDATE FROM dual;

시,분,초 단위까지 출력하기 위해서는 TO_CHAR 함수를 사용하여 날짜형식을 변환.

> SELECT TO_CHAR(SYSDATE, ‘YY/MM/DD HH24:MI:SS’) FROM daul;

입사한지 120개월 미만인 교수의 교수번호,입사일, 입사일로부터 현재일까지의 개월수, 입사일에서 6개월 후의 날짜를 출력하여라.

>SELECT profno, hiredate,

MONTHS_BETWEEN(SYSDATE, hiredate) TENURE,

ADD_MONTHS(hiredate,6) REVIEW

FROM professor; WHERE MONTHS_BETWEEN(SYSDATE, hiredate) < 120;

오늘이 속한 달의 마지막 날짜와 다가오는 토요일의 날짜 출력

> SELECT SYSDATE, LAST_DAY(SYSDATE), NEXT_DAY(SYSDATE, ‘SAT’) FROM dual;

6-5 데이터 타입의 변환

명시적 데이터 타입 변환

TO_CHAR

: 날짜/숫자 타입을 문자로 변환 (출력 형식은 작은 따옴표 ‘ ’ 로 묶어서 표현)

학생 테이블에서 전인하 학생의 학번과 생년월일중에서 년월만 출력하여라.

> SELECT studno, TO_CHAR(birthdate, 'YY-MM') birthdate FROM student WHERE name = '전인하';

학생 테이블에서 102번 학과 학생의 이름, 학년 생년월일을 출력하여라.

> SELECT name, grade, TO_CHAR(birthdate, 'Day Month DD, YYYY') birthdate FROM student WHERE deptno=102;

시간 표현형식

교수테이블에서 101번 학과 교수의 이름과 입사일을 출력하여라.

> SELECT name, TO_CHAR(hiredate, 'MONTH DD, YYYY HH24:MI:SS PM') HIREDATE FROM professor WHERE deptno=101;

TO_NUMBER

: 숫자로 구성된 문자열을 숫자 데이터로 변환

> SELECT TO_NUMBER('1') num FROM dual;

TO_DATE

: 숫자와 문자로 구성된 문자열을 날짜 데이터로 변환하는 함수이다.

교수 테이블에서 입사일이 june 01, 01인 교수의 이름과 입사일을 출력하여라.

> SELECT name, hiredate FROM professor WHERE hiredate = TO_DATE(‘june 01, 01’, ‘MONTH DD, YY’);

일반함수

NVL

: NULL을 0또는 다른 값으로 변환하기 위한 함수.

사용법 : NVL(exp1, exp2) - exp1 : NULL을 포함하는 칼럼 또는 표현식 , exp2 : NULL대체값

NVL2

사용법 : NVL2(exp1, exp2, exp3)

exp1 : NULL을 포함 하는 칼럼 또는 표현식

exp2 : exp1이 NULL이 아닐 때 반환되는 값

exp3 : exp1이 NULL일때 대체되는 값

NULLIF

: 두개의 표현식을 비교하여 값이 동일하면 NULL을 반환하고 동일하지 않으면 첫 번째 표현식의 값을 반환한다.

COALESCE

: 인수중에서 NULL이 아닌 첫번째 인수를 반환.

DECODE

: IF문이나 CASE문으로 표현되는 복잡한 알고리즘을 하나의 SQL명령문으로 표현.

> SELECT name, deptno, DECODE(deptno,101,'컴퓨터공학과',102,'멀티미디어학과',201,'전자공학과','기계공학과') DNAME FROM professor;

생일이 오늘인 학생에게 ‘생일 축하합니다’ 메시지 출력

>SELECT name, to_char(sysdate,'yy/mm/dd') currdate, birthdate, DECODE(TO_CHAR(birthdate,'mmdd'), TO_CHAR(sysdate,'mmdd'),'생일 축하합니다','') message FROM student;

CASE

: DECODE 함수의 기능을 확장한 함수.

교수 테이블에서 소속 학과에 따라 보너스를 다르게 계산하여 출력하여라. 학과번호가 101이면 보너스는 급여의 10%, 102면 20%, 201이면 30% 나머지 학과는 0%이다.

> SELECT name, deptno, sal, CASE WHEN deptno=101 THEN sal*0.1

WHEN deptno=102 THEN sal*0.2

WHEN deptno=201 THEN sal*0.3

ELSE 0

END bonus

FROM professor;