[Oracle] Function

3. 함수

가. 정의

어떤 일을 수행하는 기능으로써 주어진 인수를 입력받아 처리하여 그 결과를 반환하는 일을 수행하는 것

나. 함수의 종류

1) 단일행 함수 : 하나의 행당 하나의 결과값을 반환하는 함수

문자함수, 날짜함수, 문자변환함수, 숫자변환함수, 날짜변환함수, 시스템함수, 숫자함수

2) 복수행 함수[집계 함수, 그룹함수] : 여러 개의 행당 하나의 결과값을 반환하는 함수

count(), sum(), avg(), max(), min()

다. 단일행 함수

1) 문자함수

가) chr(아스키 코드) : 해당 아스키 코드값에 대한 문자 반환

'모든 문자는 그에 대응하는 숫자가 존재합니다. 가능하면 sql에서 답을 만들어 오면 제일 좋다.

select chr(65) from dual;

나) ascii(문자) : 문자의 아스키 코드값을 반환함

select ascii('A') from dual;

** dual : 오라클에서 제공하는 가상의 테이블

'cf. 오라클에서는 입력 파라미터가 없으면 괄호를 생략합니다. ex. sysdate

sysdate는 아래에서 자세히 살펴봅시다.

다) concat(컬럼명, '문자열') : 컬럼에 해당하는 문자열을 붙임

concat('문자열1', '문자열2') : 문자열1과 문자열2를 붙여서 결과값 반환함

'cf. 함수보단 연산자를 사용하는 게 비용이 적게 듭니다.

select concat(ename, ' 의 직책은 '), job from emp;

select ename ||' 의 직책은 '||job from emp;

select concat('로미오와 ', '줄리엣') from dual;

라) initcap('문자열') : 시작 문자를 대문자로, 다른 문자는 소문자로 반환함

select initcap('abalfaoicFIaafiaf') from dual;

마) lower('문자열'), upper('문자열') : 문자열을 소문자로, 대문자로 반환함

바) LPAD('문자열1', 자리수, '문자열2') : 문자열1을 자리수만큼 늘리는데 왼쪽으로 늘어난 자리수 공간에 문자열2를 채워서 반환함. 문자열2가 생략되면 공백으로 채워짐

사) RPAD('문자열1', 자리수, '문자열2') : 오른쪽으로 공간을 늘림

select LPAD('abcd', 9, '*') from dual;

select LPAD('abcd', 9) from dual;

아) LTRIM('문자열1', '문자열2') : 문자열1에서 문자열2를 왼쪽으로 제거한 결과값을 반환함

자) RTRIM('문자열1', '문자열2') : 오른쪽에서 제거함

select LTRIM('ABCDEFACB', 'AB') from dual;

'공백을 제거하는 용도로 자주 쓰입니다.

차) replace('문자열1', '문자열2', '문자열3') : 문자열1 중에 있는 문자열2를 문자열3으로 바꾸어서 결과값 반환함

'Java의 replace랑 비슷한 역할을 합니다. 자세한 내용을 확인하려면 여기를 클릭하세요.

select replace('asiancup is international festival', 'asiancup', 'worldcup') from dual;

카) substr('문자열', 자리수, 개수) : 문자열의 자리수부터 시작해서 지정된 개수만큼 문자를 잘라내서 결과값을 반환함(시작 인덱스는 1부터)

'Java의 substring 메서드 ~. 주의해야 할 것은 DB에서는 시작 인덱스가 1부터라는 것

'''

sql 명령어

→ 분석(Parser)

→ 실행계획(Optimizer): 튜닝(sql 명령을 최적화 작업하는 것)

'SQL 튜닝 권장사항에 보면 '함수를 매번 호출하는 것은 비효율적이다.'라고 되어 있습니다.

→ 실행

'''

 

select substr('자바개발자 과정', 4, 3) from dual;

select ename from emp where substr(ename, 2, 1) = '철';

타) instr('문자열1', '문자열2', 자리수1, 자리수2) : 자리수1부터 자리수2번째의 문자열2를 찾아서 시작위치를 반환함

select instr('wow-wow-wow-wow', '-') from dual;

select instr('wow-wow-wow-wow', '-', 10, 1) from dual;    -- 10번째 글자부터 하이픈(-) 1개를 찾아내라

파) length('문자열') : 문자열의 길이를 반환함

하) greatest('값1', '값2', '값3', ...) : 가장 큰 값을 반환함 <─> least('값1', '값2', ...)

select greatest('abc', 'abcd', 'abdcdfa') from dual;

2) 날짜 함수

가) sysdate : 시스템의 현재 날짜를 반환함    ** 의사컬럼이 아님

select sysdate from dual;

** 의사컬럼(모조컬럼) : 테이블에 있는 일반적인 컬럼처럼 행동하기는 하지만 실제로 테이블에 저장되어 있지 않은 컬럼.

1) rownum : 쿼리의 결과로 나오게 되는 각각의 row들에 대한 순서값을 가리키는 의사컬럼

2) rowid : 테이블에 저장된 각각의 row들에 대한 주소값을 가진 의사컬럼. 각각의 row들은 자신만의 rowid를 가짐

'페이지 나누기 같은 것을 할 때 필요합니다.

select rownum, rowid, empno, ename from emp

where rownum <= 10;

'sql문은 프로그램이 아니라 집합이라고 생각해야 합니다.

실행 순서: from → where → select → order by

왜 이렇게 되냐면 sql은 큰 덩어리에서 우리가 원하는 데이터를 선택하는 과정입니다. 그래서 테이블이 먼저 올라가고 조건에 맞는 로우를 추출하는 순서~.

나) add_months(날짜컬럼 or 날짜데이터, 숫자) : 날짜값에 개월 수를 더해서 결과값을 반환함

select add_months('2013/01/26', 3) from dual;

select add_months('2013/01/26', -3) from dual;

select add_months(hiredate, 3) from dual;

where hiredate between '2000/01/01' and '2010/12/31';

다) last_day(날짜컬럼 or 날짜데이터) : 파라미터 데이터와 같은 달의 마지막 날짜를 반환함

select last_day('2013/01/26') from dual;

라) months_between(날짜컬럼1 or 이후 날짜 데이터1, 날짜컬럼2 or 이전 날짜 데이터2) : 두 날짜 사이의 개월 수를 반환함

select months_between('2013/05/25', '2013/01/05') from dual;

마) next_day(날짜컬럼 or 날짜데이터, 숫자 or 요일) : 날짜 데이터 이후의 날짜 중에서 숫자 or 요일로 명시된 첫번째 날짜를 반환함

select next_day('2013/01/26', '토') from dual;

3) 문자변환 함수 : to_char(날짜컬럼 or 날짜데이터, '??')

*** "??" 에 올 수 있는 값들

가) d : 주중의 일(1~7)

나) day : 일을 서술형 이름으로 표시

다) dd : 1~31 형태로 일을 표시

라) mm : 01~12 형태로 월을 표시

마) month, mon: 월을 서술형 이름으로 표시

바) yy: 뒤의 두자리 연도 표시

사) yyyy: 네자리 연도 표시

아) dd-mm-yy: 일-월-연도

자) yyyy-mm-dd: 연도-월-일    (활용) yyyy/mm/dd

차) hh, hh12: 1~12 형태로 시를 표시    (비교) hh24

카) mi: 0~59 형태로 분을 표시

타) ss: 0~59 형태로 초를 표시

파) am, pm: 오전, 오후 표시

하) yyyy-mm-dd am hh:mi:ss day

select to_char(sysdate, 'yyyy-mm-dd am hh:mi:ss day') from dual;

4) 숫자변환 함수 : to_number('숫자 형태의 문자열')

select to_number('100') + 1 결과값 from dual;

'Integer.parseInt() in Java 의 역할 

5) 날짜변환 함수 : to_date('날짜 형태의 문자열', '날짜 변환 포맷')

select to_date('2013-01-26', 'yyyy-mm-dd') 결과값 from dual;

6) 시스템 함수 : user - 현재 오라클에 접속중인 사용자를 반환함

select user from dual;

7) 숫자 함수

가) trunc(숫자1, 자리수) : 숫자1을 소수점 자리수에서 절사

* 자리수 생략 : 소수버림

* 자리수 음수 : 정수의 자리수

나) round(숫자1, 자리수) : 숫자1을 소수점 자리수에서 반올림

다) ceil(숫자1) : 올림

(문제) 각 직원들에 대해서 직원의 이름과 근속연수를 나타내시오.

단, 근속연수는 연단위를 버림하여 나타내시오.

select ename, trunc((sysdate-hiredate)/365) 근속연수 from emp;

8) 일반 함수

가) NVL(컬럼, 치환할 값) : 컬럼의 값이 null이면 다른 값으로 치환함

(문제) 101번 학과 교수들의 이름과 급여, bonus, 연봉을 출력하시오.

select name, pay, bonus, (pay * 12 + nvl(bonus, 0)) "연봉"

from professor

where deptno=101;

나) decode(A, B, A==B일 때의 값, A<>B일 때의 값) : A<>B일 때의 값을 생략하면 null로 처리됨, decode 함수의 매개변수의 개수는 다중조건에 의해 늘어날 수 있음

'간단한 if, else의 효과를 낼 수 있어서 

(문제) 학과코드가 101인 교수만 컴퓨터공학과로 출력하시오(101번이 아닌 교수들은 학과명에 아무 것도 출력하지 않음)

select name, deptno, decode(deptno, 101, '컴퓨터공학과') "학과명"

from professor;

라. 실습문제

1) 각 직원의 이름, 직업코드, 급여를 나타내되 단 급여는 5자리로 나타내며 부족한 자리수는 '*'로 표시한다. 월급이 300 이상인 직원만 나타내시오.

2) 각 직원들에 대해서 직원의 이름과 근무개월수를 나타내되 근무개월수가 100개월 이상인 직원만 나타내시오. 근무개월수는 개월 단위를 버림하여 나타내시오.

3) 전체 직원에 대하여 직원의 이름과 직업코드, 총 근무주(week) 수를 구하시오(단, 근무주수가 많은 직원부터 나타내고, 근무주수가 같으면 이름에 대하여 오름차순 정렬하시오).

4) Student 테이블에서 제1전공(deptno1)이 101인 학과 학생들의 이름과 주민등록번호, 성별을 출력하되 성별을 주민등록번호 컬럼을 이용하여 7번째 숫자가 1일 경우 '남자', 2일 경우 '여자'로 출력하시오.