[ SQL ] 데이터베이스 #5 단일행 함수

실습 ▶ scott 계정

 

(3) 단일행 함수의 종류

문자, 숫자, 날짜, 변환, 일반 등의 종류가 있다.

 

3-1) 문자함수

문자 함수는 입력으로 문자 데이터를 받아들이고 문자 변환하거나 문자를 조작하여 문자를 반환할 수 있다

 

①LOWER 함수

lower(column)

lower 함수는 인자인 column 의 인자값이 대소문자로 혼합되어 있거나 대문자로만 되어있는 문자열을 소문자 문자열로 변환한다.

 

ex)영문자를 소문자로 변환하여 조회한다

emp 테이블의 ename 컬럼값인 영문자를 소문자로 변환

SELECT EMPNO,ENAME, HIREDATE FROM EMP WHERE LOWER(ENAME) = 'ford';

 

 

② UPPER 함수

upper(column)

upper 함수는 인자인 column 의 인자값이 대소문자로 혼합되어 있거나 소문자로만 되어있는 문자열을 대문자 문자열로 변환한다.

 

ex) 영문자를 대문자로 변환하여 조회한다.

emp 테이블의 ename 컬럼값인 영문자를 대문자로 변환

.

SELECT EMPNO,ENAME, HIREDATE FROM EMP WHERE UPPER(ENAME) = 'FORD'; SELECT EMPNO,ENAME, HIREDATE FROM EMP WHERE ENAME = UPPER('FORD');

 

 

③ INITCAP 함수

initcap(column)

initcap 함수는 인자인 column 의 인자값인 영문자의 첫 번째 문자를 대문자로 변환하고 나머지 문자는 소문자로 변환한다.

 

ex)  dept 테이블의 dname 컬럼값인 영문자와 loc 컬럼값인 영문자에서 각 단어의 첫 번째 문자를 대문자로 나머지 문자는 소문자로 변환하여 조회한다.

 

먼저 DEPT 에서 DNAM, LOC 확인

SELECT DNAME FROM DEPT;

SELECT LOC FROM DEPT;

SELECT DEPTNO, INITCAP(DNAME), INITCAP(LOC) FROM DEPT WHERE DEPTNO = 10;

 

 

 

④ CONCAT 함수

concat(column1, column2)

concat 함수는 인자인 column1 컬럼값을 column2 컬럼값과 연결한다.

concat 함수는 ||(더블 버티컬바) 연결 연산자와 같은 기능을 가지지만 두 개의 인자만 사용할 수 있다.

 

ex) dept 테이블에서 dname 컬럼값과 loc 컬럼값을 연결한다.

SELECT DEPTNO, DNAME, CONCAT(DEPTNO, DNAME) FROM DEPT WHERE DEPTNO = 10;

 

 

 

 

 

⑤ SUBSTR 함수

substr(column, m[,n])

substr 함수는 인자인 column 에서 인자인 m 위치에서 시작하는 문자열의 n 개 문자열 길이만큼 지정된 문자들을 반환하고 지정된 길이의 문자열을 추출한다.
substr 함수의 인자인 m 이 음수이면 문자 끝에서부터 카운트를 시작한다.
substr 함수의 인자인 n 이 생략된 경우 문자의 끝까지 모든 문자가 반환한다.

 

ex) emp 테이블의 ename 컬럼값인 영문자의 1 번째 문자에서 시작하여 2 번째 문자 까지만 조회한다.

SELECT SUBSTR(ENAME, 1, 2) FROM EMP;

 

⑥ LENGTH 함수

length(column)

length 함수는 인자인 column 의 문자열 개수를 반환하고 문자열 길이를 숫자로 표현한다.

 

ex) emp 테이블의 ename 컬럼값인 영문자의 1 번째 문자에서 시작하여 2 번째 문자 까지만 조회한다.

SELECT DEPTNO, DNAME, LENGTH(DNAME) FROM DEPT WHERE DEPTNO = 10;

 

 

⑦ INSTR 함수

 instr(column, string, [m], [n])
instr 함수는 인자인 column 지정된 문자열을 찾아내서 0(없다) 또는 1(있다)로 값을 반환하고 위치를 숫자로 표기한다.
조회 시작 위치인 instr 함수의 인자인 m 과 문자열의 발생수인 인자인 n 을 제공할 수 있다.
instr 함수의 인자인 m 과 n 의 기본값은 1 이며 이 경우 문자열의 처음부터 조회를 시작하고 첫 번째로 찾은 결과를 반환한다.

 

ex1) dept 테이블의 dname 컬럼값에서 명명된 문자의 위치를 구한다.

SELECT DEPTNO, DNAME, INSTR(DNAME, 'G') FROM DEPT WHERE DEPTNO = 10;

DEPTNO 10번째의 DNAME에서(ACCOUNTING) 'G' 는 ACCOUNTING 의 10번째에 있다

 

 

CF)

SELECT DEPTNO, DNAME, INSTR(DNAME, 'C') FROM DEPT WHERE DEPTNO = 10;

DEPTNO 10번째의 DNAME에서(ACCOUNTING) 'C' 는 ACCOUNTING 의 2번째에 있다

 

 

 

ex2) dept 테이블에서 loc 컬럼에서 특정 문자를 가진 행만 조회한다.

SELECT LOC FROM DEPT;

SELECT DEPTNO, DNAME, LOC FROM DEPT WHERE INSTR(LOC, 'DALLAS') > 0;

해당 행의 DEPTNO, DNAME 이 같이 불러와졌다.

 

 

 

ex3) dept 테이블에서 loc 컬럼에서 특정 문자열을 가진 행을 제외하고 조회한다.

SELECT DEPTNO, DNAME, LOC FROM DEPT WHERE INSTR(LOC, 'DALLAS') < 1 ;

 

 

 

 

⑧ LPAD 함수

lpad(column, n, string)

lpad 함수는 인자인 column 의 길이가 인자인 n 이 되도록 왼쪽부터 문자로 채운 표현식을 반환하며 전체 길이 중 남는 부분 좌측에 주어진 문자로 채운다.

 

ex) dept 테이블의 dname 컬럼에 * 문자를 지정한 15 만큼 왼쪽 자리 채운다.

 

SELECT DEPTNO, DNAME, LPAD(DNAME, 15, '*') FROM DEPT WHERE DEPTNO = 10;

 

 

 

 

 

⑨ RPAD 함수

rpad(column, n, string)

rpad 함수는 인자인 column 의 길이가 인자인 n 이 되도록 오른쪽부터 문자로 채운 표현식을 반환하며 전체 길이 중 남는 부분 우측에 주어진 문자로 채운다.

 

ex) dept 테이블의 dname 컬럼에 * 문자를 지정한 15 만큼 오른쪽 자리 채운다.

 

SELECT DEPTNO, DNAME, RPAD(DNAME, 15, '*') FROM DEPT WHERE DEPTNO = 10;

 

 

⑩ TRIM 함수

trim(leading|trailing|both, trim_character from column)

trim 함수의 인자인 leading 은 문자열에서 오른쪽 문자를 제거한다.

trim 함수의 인자인 trailing 은 문자열에서 왼쪽 문자를 제거한다.

trim 함수의 인자인 both 는 문자열에서 양 끝에 있는 문자를 제거한다.

trim 함수의 인자인 trim_characte 가 문자열 리터럴이면 단일 따옴표로 묶어야 한다.

 

ex1) dept 테이블의 dname 컬럼값인 문자의 양 끝에 있는 공백 문자를 제거한다.

 

SELECT DEPTNO, DNAME, TRIM(BOTH '' FROM DNAME) FROM DEPT WHERE DEPTNO = 10;

 

 

ex2) dept 테이블의 dname 컬럼값인 문자의 오른쪽에 있는 공백 문자를 지운다.

 

SELECT DEPTNO, DNAME, TRIM(LEADING '' FROM DNAME) FROM DEPT WHERE DEPTNO = 10;

 

 

 

 

 

⑪ TRANSLATE 함수

translate(column, string1, string2)

translate 함수는 인자인 column 에서 인자인 string1 문자열을 string2 문자열로 대체한다.

translate 함수는 지정 문자열과 대체 문자열을 각각 하나의 문자로 대체한다.

 

ex) dept 테이블의 dname 컬럼값인 문자열에서 NG 문자를 SO 문자로 대체한다.

SELECT DEPTNO, DNAME, TRANSLATE(DNAME, 'NG', 'SO') FROM DEPT WHERE DEPTNO = 10;

 

 

 

⑫ REPLACE 함수

replace(text, search_string, replacement_string)

replace 함수는 인자인 text 에서 인자인 search_string 문자열을 replacement_string 문자열로 대체한다.

replace 함수는 지정된 정확한 문자열을 찾아서 대체 문자열로 변경한다.

정확한 문자열을 변경하려고 할 때는 replace 함수를 사용하고 문자 각각을 변경하려고 하면 translate 함수를 사용한다

 

 

ex) dept 테이블의 dname 컬럼값에 공백 문자열을 빈 문자열로 대체한다.

SELECT DEPTNO, DNAME, REPLACE(DNAME, ' ', '') FROM DEPT WHERE DEPTNO = 10;

 

 

 

3-2) 숫자 함수

① ROUND 함수

round(column, n)

round 함수는 절반에 걸리면 무조건 반올림하는 사사오입 방식을 선택하고 있다.

round 함수는 인자인 column 값에 소수점 n 자릿수까지 반올림한다.

round 함수의 인자인 n 자릿수는 integer 위치까지 반올림하며 integer 값에 반올림하고자 하는 자릿수를 적어준다.

round 함수의 인자인 n 이 양수이면 소수 자리를 반올림하고 음수이면 정수 자리를 반올림한다.

round 함수의 인자인 n 은 생략할 수 있고 n 이 생략된 경우 소수점의 자릿수가 없다.

round 함수의 인자인 n 이 음수이면 소수점 왼쪽의 숫자가 반올림되고 기본값은 0 이다.

예를 들어 round(123.4567, 3)이라고 했을 때 123.4567 에서 소수점 넷째 자리인 7 에서 반올림해서 소수점 셋째 자리까지 표시해 주므로 123.457 이 된다.

 

ex) dual 테이블에서 45.926 을 소수점 두 자리에서 반올림한다.

SELECT ROUND(45.926,2) FROM DUAL;

 

 

 

 

② TRUNC 함수

trunc(column, n)

trunc 함수는 인자인 column 값에 소수점 n 자릿수까지 절삭한다.

trunc 함수의 인자인 n 이 양수이면 소수 자리를 절삭하고 음수이면 정수 자리를 절삭한다.

trunc 함수의 인자인 n 은 생략할 수 있으며 기본값은 0 이다.

 

 

ex) dual 테이블에서 45.926 을 소수점 두 자리에서 절삭한다.

SELECT TRUNC(45.926,2) FROM DUAL;

 

 

 

③ MOD 함수

mod(m, n)

mod 함수의 인자인 m 을 n 으로 나눈 나머지를 반환한다.

mod 함수는 좌측에 있는 값을 우측에 있는 값으로 나눈 나머지를 반환하는 것이다.

 

ex) dual 테이블에서 1600 을 300 으로 나눈 나머지를 반환한다.

SELECT MOD(1600,300) FROM DUAL;

 

 

 

 

 

3-3) 날짜함수

• 오라클은 세기, 년, 월, 일, 시, 분, 초를 나타내는 내부 숫자인 7 바이트 형식으로 날짜를 저장한다.
• 기본 날짜 표시와 입력 형식은 DD-MM-YY 이다.
• 유효한 오라클 날짜는 기원전 4712 년 1 월 1 일부터 서기 9999 년 12 월 31 일까지이다.
• 날짜 데이터 타입의 값에 대해 실행되며 숫자를 반환하는 months_between 함수를 제외하고 모든 날짜 함수는 날짜 데이터 타입의 값을 반환한다.
• 오라클은 날짜를 숫자로 저장하므로 산술 연산자로 다음과 같이 산술 계산을 할 수 있다.
  * Date + Number 날짜 연산: 일수를 날짜에 더하며 결과는 날짜
  * Date + Number / 24 날짜 연산: 24 로 일수를 나누면 시간이 되며 시간을 날짜에 더하며 결과는 날짜
  * Date – Number 날짜 연산: 날짜에서 일수를 빼며 결과는 날짜
  * Date – Date 날짜 연산: 특정 날짜에서 다른 특정 날짜를 빼며 결과는 일수

 

 

① SYSDATE 함수

sysdate 함수는 시스템의 현재 날짜를 반환한다.
sysdate 함수는 현재의 오라클 서버 날짜와 시간을 반환한다.
sysdate 함수는 컬럼 이름을 사용하듯이 사용할 수 있다.

 

ex) dual 테이블에서 시스템의 현재 날짜를 구한다.

SELECT SYSDATE FROM DUAL;

 

 

② MONTHS_BETWEEN 함수

months_between(date1, date2)
months_between 함수의 인자인 date1 과 date2 사이의 월수를 조회한다.
months_between 함수의 결과는 인자인 date1 이 date2 보다 늦은 날짜이면 결과는 양수로 표현하고 인자 date1 이 date2 보다 앞선 날짜이면 결과는 음수로 표현한다.
months_between 함수의 결과에서 정수가 아닌 부분은 월 일부분을 나타낸다.

 

 

ex) emp 테이블에서 입사 날짜와 현재 날짜의 근무 개월 수를 조회한다.

SELECT ENAME, HIREDATE, SYSDATE, MONTHS_BETWEEN(SYSDATE, HIREDATE) FROM EMP WHERE DEPTNO = 10;

정수가 아닌 부분은 월 일부분을 나타낸다.

 

 

 

③ ADD_MONTHS 함수

add_months(date, n)

add_months 함수의 인자인 date 에 월 수 n 을 추가하고 인자인 n 값은 정수여야 하며 음수가 될 수도 있다.

 

 

 

ex) emp 테이블에서 입사 날짜로부터 5 개월이 지난 후의 날짜를 조회한다.

SELECT ENAME, HIREDATE, ADD_MONTHS(HIREDATE, 5) FROM EMP WHERE DEPTNO = 10;

 

 

 

④ NEXT_DAY 함수

next_day(date, char)
next_day 함수는 인자인 date 다음에 오는 지정된 요일인 char 의 날짜를 찾는다.
next_day 함수의 인자인 char 값은 요일을 나타내는 숫자나 문자열이 될 수 있다.

 

ex) emp 테이블에서 입사일로부터 돌아오는 금요일을 조회한다.

SELECT ENAME, HIREDATE, NEXT_DAY(HIREDATE, '금요일') AS 월 FROM EMP WHERE DEPTNO = 10;

 

 

 

⑤ LAST_DAY 함수

last_day(date)
last_day 함수는 인자인 date 에 해당하는 날짜가 있는 월의 말일 날짜를 찾는다.
숫자 함수인 round 함수와 trunc 함수는 날짜 조작에도 사용할 수 있다.

 

ex1) emp 테이블에서 해당 날짜에 대한 월의 말일 날짜를 조회한다.

SELECT ENAME, HIREDATE, LAST_DAY(HIREDATE) AS 월 FROM EMP WHERE DEPTNO = 10;

 

 

ex2) emp 테이블에서 입사일과 현재 날짜의 근무 개월 수를 반올림하여 조회한다.

SELECT ENAME, HIREDATE, SYSDATE, ROUND(MONTHS_BETWEEN(SYSDATE,HIREDATE)) 월수 FROM EMP WHERE DEPTNO = 10;

 

 

ex3) emp 테이블에서 입사일과 현재 날짜의 근무 개월 수를 절삭하여 조회한다. 

SELECT ENAME, HIREDATE, SYSDATE, TRUNC(MONTHS_BETWEEN(SYSDATE,HIREDATE)) 월수 FROM EMP WHERE DEPTNO = 10;

 

 

 

 

 

 

3-4) 변환함수

• 데이터 타입 변환은 오라클 서버에 의해 암시적으로 수행되거나 사용자에 의해 명시적으로 수행될 수 있다.
• 명시적 데이터 타입 변환은 변환 함수를 사용하여 수행되며 변환 함수는 값의 데이터 타입을 변환한다.
• 일반적으로 함수 이름의 형식은 data type TO data type 규칙을 따르며 첫 번째 데이터 타입은 입력 데이터 타입이고 두 번째 데이터 타입은 출력 데이터 타입이다.
• 암시적 데이터 타입 변환은 오라클 서버는 자동으로 표현식에서 데이터 타입 변환을 수행한다.
• 변환 함수에는 to_char 함수, to_number 함수, to_date 함수가 있다.
• to_char 함수는 to_number 함수의 기능과 to_date 함수의 기능을 포함하고 있으므로 일반적으로 to_char 함수의 사용을 권장한다.
• 일반적으로 오라클 서버에서는 데이터 타입 변환이 필요한 경우 표현식에 대한 규칙을 사용한다.
• 날짜 형식의 요소는 출력 포맷 서식으로 지정한다.
  *  YYYY : 년도를 4 자리로 표현하며 숫자로 된 전체 년도이다.
  *  YEAR : 영어 철자로 표기된 년도로 표현한다.
  *  MM : 월을 숫자로 표현하며 월의 2 자리 값이다.
  *  MONTH : 월의 전체 이름으로 표현한다.
  *  MON : 월의 약자로 표현한다.
  *  DAY : 요일의 전체 이름으로 표현한다.
  *  DY : 요일의 약자로 표현한다.
  *  DD : 숫자 형식의 월과 일을 표현한다.
  *  AM(PM) : 오전인 AM 과 오후인 PM 의 시각을 표현한다.
  *  A.M(P.M) : 오전인 A.M 과 오후인 P.M 의 시각을 표현한다.
  *  HH(HH12) : HH 는 전일 시간과 HH12 는 시간을 반일 시간을 표현한다.
  *  HH24 : 전일 시간인 0~23 으로 24 시간을 표현한다.
  *  MI : 분인 0~59 로 표현한다.
  *  SS : 초인 0~59 로 표현한다.
  *  0(1111, ‘099999’) : 앞부분을 0 을 숫자로 표현한다.
  *  $(1111, ‘$99999’) : 달러 기호를 앞에 표현한다.

 

 

 

① TO_CHAR 함수

 to_char(number|date, fmt)
to_char 함수는 포맷 서식인 fmt 를 사용하여 숫자나 날짜를 varchar2 문자열로 변환한다.
to_char 함수의 포맷 서식 fmt 는 데이터 타입의 형식을 설명하는 문자열 리터럴이다.
to_char 함수는 다음의 규칙을 준수한다.
포맷 서식은 '(싱글 쿼터)로 묶어야 하며 대소문자를 구분한다.
포맷 서식에서 문자열을 표현하기 위해서는 '(싱글 쿼터) 안에 "(더블 쿼터)를 사용한다.
포맷 서식은 임의의 유효한 날짜 형식 요소를 포함할 수 있다.
,(콤마)를 사용하여 날짜를 포맷 서식과 구분해야 한다.
자동으로 출력의 날짜와 월 이름은 공백으로 채워진다.

 

 

ex1) dual 테이블에서 현재 날짜를 년 월 일이라는 한글 형식을 적용하여 포맷 서식으로 조회한다.

SELECT SYSDATE, TO_CHAR(SYSDATE, 'YYYY "년" MM "월" DD "일"') FROM DUAL;

 

 

ex2) dual 테이블에서 포맷 서식으로 현재 시각을 조회한다.

SELECT SYSDATE, TO_CHAR(SYSDATE, 'HH24:MI:SS') FROM DUAL;

 

 

ex3) emp 테이블에서 포맷 서식으로 입사 년도만 출력하여 조회한다. 

SELECT EMPNO, ENAME, TO_CHAR(HIREDATE, 'YYYY') AS 년도 FROM EMP;

 

 

 

ex4) emp 테이블에서 급여를 6 자리고 바꾸고 앞에 $ 숫자 포맷 서식으로 변환하여 조회한다.

SELECT EMPNO, ENAME, TO_CHAR(SAL, '$999,999') AS 급여 FROM EMP;

 

급여 :  SAL

 

 

 

② TO_NUMBER 함수

to_number(number, fmt)

to_number 함수는 숫자를 포함한 문자열을 포맷 서식인 fmt 를 사용하여 지정된 형식의 숫자로 변환한다.

 

 

ex) dual 테이블에서 문자열을 숫자 포맷 서식으로 변환하여 조회한다.

SELECT TO_NUMBER('1234', '9999') FROM DUAL;

 

 

 

3-5) 일반함수

• 일반 함수는 임의의 데이터 타입을 사용한다.
• 일반 함수는 표현식 리스트에서 null 값의 사용과 관련이 있다.

 

① NVL 함수

형식 nvl(expr1, expr2)
nvl 함수는 null 값을 실제 값으로 변환한다.
nvl 함수의 인자인 expr1 은 null 을 포함할 수 있는 표현식이나 값이다.
nvl 함수의 인자인 expr2 는 null 을 변환하기 위한 대상값이다.

 

 

② NVL2 함수

nvl2(expr1, expr2, expr3)
nvl2 함수는 인자인 expr1 을 검사하고 expr1 이 null 이 아니면 인자인 expr2 를 반환하고 인자인 expr1 이 null 이면 expr3 를 반환한다.
nvl2 함수의 인자인 expr1 은 null 을 포함할 수 있는 표현식이나 값으로 임의의 데이터 타입을 가질 수 있다.
nvl2 함수의 인자인 expr2 는 인자인 expr1 이 null 이 아니면 반환되는 값이다.
nvl2 함수의 인자인 expr3 는 인자인 expr1 이 null 이면 반환되는 값이다.

 

 

 

ex) emp 테이블에서 사원 테이블의 커미션을 추가한 연봉을 조회하고 null 이면 0 으로 조회한다.

SELECT ENAME, SAL, SAL*12, COMM, SAL*12 + NVL(COMM, 0) FROM EMP;

SAL : 급여

SAL*12 : 연봉

 

**** NVL(COMM, 0)

COMM : null 을 포함할 수 있는 표현식이나 값

0 :  null 을 변환하기 위한 대상값