6.SQL 함수

DUAL 테이블

한 행으로 결과를 출력하기 위한 테이블이다.산술 연산이나 가상 컬럼 등의 값

을 한번만 출력하고 싶을 때 많이 사용한다.오라클에서 [24*60]을 실행해보자. 

알 수 없는 명령어라면서 오류 메시지가 뜰 것이다. 그럼 어떻게 해야 할까?

[SELECT 24*60 FROM EMP; ]을 실행해보면 한번만 출력되면 될것이 emp테이블

이 갖고있는 로우의 수 만큼 결과값이 출력된다. 이때 DUAL 테이블을 사용한다.

산술 연산의 결과를 한 줄로 얻기 위해서 오라클에서 제공하는 테이블이다.

• SQL> SELECT 24*60 FROM DUAL; 그럼 DUAL이라는 테이블은 어떻게 구성이 되어있을까? DESC 명령어를 통해서 알아보자.

• SQL> DESC DUAL;

• 그럼 DUMMY 컬럼에는 어떤 값이 저장되어 있는 것인가?

  SQL> SELECT * FROM DUAL; X라는 값이 들어 있는데 이 X는 아무 의미 없는 값이다. 단 하나의 로우를 구성하고 있을 뿐이다.

• 그럼 DUAL 테이블이 쓰이는 적절한 예는 현재 날짜를 구할때이다.

• 예) 현재 날짜는 구하는 쿼리문

  SQL> SELECT SYSDATE FROM DUAL;

문자함수

UPPER 대문자 변환 함수

SQL> SELECT 'Welcome to Oracle' "적용전", UPPER('Welcome to Oracle') "UPPER적용후" FROM DUAL;

LOWER :  소문자 변환 함수

소문자로 변환하는 함수이다.

SQL> SELECT 'Welcome to Oracle' "적용전", LOWER('Welcome to Oracle') "LOWER적용후" FROM DUAL;

INITCAP : 첫글자 대문자, 나머지 소문자 전환

이니셜만 대문자로 변환하는 함수이다.

SQL> SELECT 'WELCOME TO ORACLE' "적용전", INITCAP('WELCOME TO ORACLE') "INITCAP적용후" FROM DUAL;

문자 길이 반환함수

LENGTH 문자의 길이 반환 (한글 1Byte)

LENGTH 는 문자 데이터가 몇개의 문자로 구성되어있는지 길이를 알려준다.

SQL> SELECT LENGTH('Oracle'), LENGTH('오라클') FROM DUAL;

LENGTHB 문자의 길이 반환 (한글 2Byte)

위와는 달리 바이트수를 알려주는 함수

SQL> SELECT LENGTHB('Oracle'), LENGTHB('오라클') FROM DUAL;

문자 조작 함수

CONCAT : 문자의 값을 연결

SUBSTR : 문자를 잘라 추출(한글 1Byte)

SUBSTRB :문자를 잘라 추출(한글 2Byte)

INSTR : 특정문자의 위치 값을 반환 (한글 1Byte)

INSTRB : 특정문자의 위치 값을 반환(한글 2Byte)

LPAD,RPAD : 입력받은 문자열과 기호를 정렬하여 특정길이의 문자열로 반환한다.

TRIM : 잘라내고 남은 문자를 표시한다.

ABS : 절대값 반환

예) -10에 대한 절대값을 구하는 쿼리문

SQL> SELECT -10, ABS(-10) FROM DUAL;

FLOOT : 소수점 아래를 자름

예) 34.5432를 FLOOR 함수에 적용하는 쿼리문

SQL> SELECT 34.5432, FLOOR(34.5432) FROM DUAL;

ROUND : 특정 자릿수에서 반올림

• 예) 34.5678을 반올림하는 쿼리문

  SQL> SELECT 34.5678, ROUND(34.5678) FROM DUAL;

• 예) 특정 자릿수에서 34.5678을 반올림하는 쿼리문

  SQL> SELECT 34.5678, ROUND(34.5678, 2) FROM DUAL;

• 특정 자릿수가 음수일 경우 일단위→십단위→백단위 순으로 반올림 한다.

• 예) 특정 자릿수가 음수일 경우 34.5678을 반올림하는 쿼리문

  SQL> SELECT 34.5678, ROUND(34.5678, -1) FROM DUAL;

TRUNC : 특정 자릿수에서 자름

• 두번째 인자값이 2인 경우 : 소수점 이하 세번째 자리에서 버림 연산을 수행한다.
• 두번째 인자값이 0인 경우 : 소수점 자리에서 버림 연산을 수행한다.
• 두번째 인자값이 -1인 경우 : 일의 자리에서 버림 연산을 수행한다.
• 두번째 인자값이 없는 경우 : 소수점 자리에서 버림 연산을 수행한다.

  SQL> SELECT TRUNC(34.5678, 2), TRUNC(34.5678, -1), TRUNC(34.5678), TRUNC(34.5678, 0) FROM DUAL;

MOD 입력받은 수를 나눈 나머지 값을 반환

나누기 연산을 한 후 나머지를 결과로 되돌려주는 함수이다.

SQL> SELECT MOD(27, 2), MOD(27, 5), MOD(27, 7) FROM DUAL;

날짜 함수

기본적으로 DATE형에 사용하는 함수 결과값으로 날짜, 기간이 나온다,.

SYSDATE

시스템에 저장된 현재 날짜를 반환

• 현재 날짜를 반환하는 함수이다.
• 시스템에서 현재 날짜를 읽어 와서 출력하는 쿼리문

  SQL> SELECT SYSDATE FROM DUAL;

• 날짜 연산
  • 날짜 + 숫자 : 그 날짜로부터 그 기간만큼 지난 날짜를 계산한다.
  • 날짜 - 숫자 : 그 날짜로부터 그 기간만큼 이전 날짜를 구한다.
  • 날짜 - 날짜 : 두 날짜 사이의 기간을 계산한다.
  • SYSDATE + 1은 내일의 날짜를 구한다.
  • SYSDATE - 1은 어제 날짜를 구한다.

    SQL> SELECT SYSDATE-1 어제, SYSDATE 오늘, SYSDATE+1 내일 FROM DUAL;

MONTHS_BETWEEN

 날짜와 날짜 사이의 개월수를 구하는 함수

• 두 날짜 사이의 개월수를 구하는 함수이다.
• 예) 각 직원들이 근무한 개월 수를 수하는 쿼리문

  SQL> SELECT ENAME, SYSDATE, HIREDATE, MONTHS_BETWEEN(SYSDATE, HIREDATE) 근무개월수 FROM EMP; 소수점 이하 자리가 있다는 뜻은 아직 한 달이 되지 않았기 때문이다. 이때 사용할 수 있는 함수가 숫자함수에서 배웠던 ROUND 또는 TRUNC 함수이다. SQL> SELECT ENAME, SYSDATE, HIREDATE, TRUNC(MONTHS_BETWEEN(SYSDATE, HIREDATE)) 근무개월수 FROM EMP;

ADD_MONTHS

특정 개월수를 더한 날짜를 구하는 함수

• 예) 입사 날짜에서 4개월을 추가한 결과값을 나타내는 쿼리문

  SQL> SELECT ENAME, HIREDATE, ADD_MONTHS(HIREDATE, 4) FROM EMP;

NEXT_DAY

지정요일에 해당되는 날짜를 반환하는데 해당 날짜를 기준으로 가장 빨리 다가오는 날짜를 반환.

예) 오늘을 기준으로 가장 가까운 다음 수요일은 언제인지 알아보는 쿼리문

SQL> SELECT SYSDATE, NEXT_DAY(SYSDATE, '수요일') FROM DUAL; 두번째 인자값이 한글일 경우는 일, 월, 화, 수, 목 등으로 표시하고, 영문판인 경우는 SUN, MON, TUE 등으로 표시한다. 문자대신 요일을 숫자로 표현하는 경우는 일요일은 7, 월요일은 1, 화요일은 2 식으로 1부터 7까지의 숫자만 표현 가능하다. 만약 두번째 인자값에 '금'이라 작성했는데 오류가 날 경우는 [ALTER SESSION SETNLS_LANGUAGE=Korean;]의 명령문을 실행한다.

LAST_DAY

해당 날짜가 속한 달의 마지막 날짜를 반환

예) 입사한 달의 마지막 날을 구하는 쿼리문

SQL> SELECT HIREDATE, LAST_DAY(HIREDATE) FROM EMP;

변환함수

숫자, 문자, 날짜의 데이터형을 다른 데이터형으로 변환하는 함수이다. 함수를 보기 전에 출력 형식의 종류부터 알아보자.

TO_CHAR

숫자, 날짜 형태의 데이터를 문자형으로 변환하는 함수

• 예) 현재 날짜를 문자형으로 변환하여 출력하는 쿼리문

  SQL> SELECT SYSDATE, TO_CHAR(SYSDATE, 'YYYY-MM-DD') FROM DUAL;

• 예) 사원들의 입사일을 출력하되, 요일까지 함께 출력하는 쿼리문

  SQL> SELECT HIREDATE, TO_CHAR(HIREDATE, 'YYYY/MM/DD DAY') FROM EMP;

• 예) 현재 날짜와 시간을 출력하는 쿼리문

  SQL> SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD, HH24:MI:SS') FROM DUAL;

• 각 지역별 통화 기호를 앞에 붙이고 천 단위마다 콤마를 붙여서 출력하는 쿼리문(예: ￦1,230,000)

  SQL> SELECT ENAME, SAL, TO_CHAR(SAL, 'L999,999') FROM EMP;

• 자릿수가 맞지 않을 경우 0은 0으로 채우고 9는 채우지 않는다.

  SQL> SELECT TO_CHAR(123456, '000000000'), TO_CHAR(123456, '999,999,999') FROM DUAL;

TO_DATE

숫자형, 문자형 데이터를 날짜형으로 변환하는 함수

날짜형은 세기, 연도, 월, 일, 시간, 분, 초와 같은 날짜와 시간에 대한 정보를 저장한다.

기본 날짜 형식은 'YY/MM/DD' 형식으로 '년/월/일'을 나타낸다.

1981년 2월 20일에 입사한 사원을 검색하는 쿼리문[SELECT ENAME, HIREDATE FROM EMP WHERE HIREDATE=19810220;] 쿼리문을 실행하면 오류가 발생한다.

 

그 이유는 입사일 컬럼의 데이터 형은 DATE이므로 WHERE절에 숫자형으로 기술하면 데이터형이 일치하지 않기 때문에 요류가 발생한다.

 오류가 발생하지 않게 하려면, 비교할 두 데이터의 자료 형태를 맞추어야 한다.

• SQL> SELECT ENAME, HIREDATE FROM EMP WHERE HIREDATE=TO_DATE(19810220, 'YYYYMMDD');

• 올해 며칠이 지났는지 현재 날짜에서 2016/01/01을 뺀 결과를 출력하는 쿼리문

  SQL> SELECT TRUNC(SYSDATE-TO_DATE('2016/01/01', 'YYYY/MM/DD')) FROM DUAL;

TO_NUMBER

특정 데이터를 숫자형으로 변환해 주는 함수

예) '20,000'과 '10,000'의 차이를 알아보기 위해서 빼기(-)연산을 구하는 쿼리문

[SELECT '20,000'-'10,000' FROM DUAL;]을 실행하면 오류가 발생한다.

 그 이유는 '10,000'과'20,000'은 문자형이기 때문에 산술 연산을 수행하지 못하기 때문이다. 

따라서 산술 연산을 하려면 문자형을 숫자형으로 변환한 다음에 연산을 실행해야 한다.

• SQL> SELECT TO_NUMBER('20,000', '99,999')-TO_NUMBER('10,000', '99,999') FROM DUAL;

일반함수 

NVL

NULL값을 0혹은 다른 값으로 변환하기 위한 함수

예) 연봉 계산을 하는 쿼리문

SQL> SELECT ENAME, SAL, COMM, JOB FROM EMP ORDER BY JOB; 여기서 영엽사원이 아닌 사원들의 커미션은 NULL로 지정이 되어 있기 때문에 연봉을 구하는 산술 연산을 수행할 시 결과가 NULL값이 되기 때문에 연봉 계산이 제대로 되지 않는다. 고로 NULL인 커미션은 NVL함수를 통해 0으로 변환해야 한다. SQL> SELECT ENAME, SAL, COMM, SAL12+COMM, NVL(COMM, 0), SAL12+NVL(COMM, 0) FROM EMP ORDER BY JOB;

DECODE

switch case와 같은 기능을 가진 함수 여러가지 경우에 대해 선택할수 있도록 한다.

예) 사원의 부서 번호를 이름으로 설정하는 쿼리문

SQL> SELECT DEPTNO FROM EMP ORDER BY DEPTNO;

SQL> SELECT DEPTNO, DECODE(DEPTNO, 10, 'A', 20, 'B', 'DEFAULT') FROM EMP ORDER BY DEPTNO; 위의 부서번호를 이름으로 나타내는 부분이 함수로 되어 있어 이것을 DNAME로 바꿔 보자

SQL> SELECT ENAME, DEPTNO, DECODE(DEPTNO, 10, 'ACCOUNTING', 20, 'RESEARCH', 30, 'SALES', 40, 'OPERATIONS') AS DNAME FROM EMP;

CASE

여러가지 경우에따라 하나를 선택하는 함수, DECODE함수는 조건이 일치 하는 경우에만 적용되지만 CASE는 다양한 비교연산자를 사용할수있으므로 조건을 다양하게 지정가능하다.

• DECODE 함수는 조건이 일지(=비교 연산자)하는 경우에 대해서만 적용하지만 CASE 함수는 다양한 비교 연산자를 이용하여 조건 제시 및 범위 지정할 수 있다는 점이 이 둘의 차이점이다.
• '중첩 'IF ELSE'와 유사한 구조이다.
• 예) 부서 번호에 해당하는 부서명을 구하는 쿼리문

  SQL> SELECT ENAME, DEPTNO, CASE WHEN DEPTNO=10 THEN 'ACCOUNTING' WHEN DEPTNO=20 THEN 'RESEARCH' WHEN DEPTNO=30 THEN 'SALES' WHEN DEPTNO=40 THEN 'OPERATIONS' END AS ENAME FROM EMP;