오늘의 요약
#1-2. 함수( function )
(1) 함수(function) 란 ? (처리/반환)
어떠한 일을 수행하는 기능으로써 주어진 인수(argument)
를 재료로 '처리'를 하여 그 결과를 '반환'하는 일을 수행
(2) 함수 기능의 구체적 표현
1) Data에 대한 '계산'
2) Data를 다른 형태로 '변환'
3) Data의 결과를 '출력'
(3) 함수의 종류
1) 단일행 함수 (ex: nvl, .. )
-> 하나의 행(row)당, 하나의 결과값을 리턴하는 함수
SQL> select ename, nvl(comm, 0) from emp where comm is null;
2) 복수행 함수 (ex: max, min, avg, .. )
-> 여러개의 행(row)당, 하나의 결과값을 리턴하는 함수
SQL> select avg(SAL) from EMP;
(4) 단일행 함수
1) 문자 함수
<1> chr(아스키코드)
SQL> select chr(65) from dual;
<2> concat(컬럼명, '붙일문자열')
SQL> select ENAME||' is a ' ||JOB from EMP;
SQL> select concat(ENAME, ' is a '), JOB from EMP;
<3> initcap('문자열')
SQL> select initcap('the lion') from dual;
INITCAP 함수는 매개변수로 들어오는 char의 첫 문자는 대문자로, 나머지는 소문자로 반환하는 함수다
<4> lower('문자열')
SQL> select lower('MY NAME IS KHS') from dual;
SQL> select lower(ename) from emp;
<5> LPAD('문자열1', 자리수, '문자열2')
LPAD 함수는 지정한 길이 만큼 왼쪽부터 특정문자로 채워준다.
- 함수 : LPAD("값", "총 문자길이", "채움문자")
SQL> select LPAD('khs', 13, '*#') from DUAL;
SQL> select LPAD('khs', 13, ' ') from DUAL;
<6> RPAD('문자열1', 자리수, '문자열2')
SQL> select RPAD('khs', 13, '*#') from DUAL;
<7> LTRIM('문자열1', '문자열2')
SQL> select LTRIM('xyxXxyLAST WORDxy', 'xy') from DUAL;
SQL> select LTRIM('xyyXxyLAST WORDxy', 'xy') from DUAL;
SQL> select LTRIM(' LAST WORDxy', ' ') from DUAL;
SQL> select LTRIM(' LAST WORDxy') from DUAL;
<8> RTRIM('문자열1', '문자열2')
SQL> select RTRIM('xyxXxyLAST WORDxy', 'xy') from DUAL;
SQL> select RTRIM('xyxXxyLAST WORDxyx', 'xy') from DUAL;
SQL> select RTRIM('WORD ', ' ') from DUAL;
SQL> select RTRIM('WORD ') from DUAL;
SQL> select length('WORD ') from dual;
SQL> select length(rtrim('WORD ')) from dual;
<9> REPLACE('문자열1', '문자열2', '문자열3')
SQL> select 'JACK and JUE' from DUAL;
SQL> select replace('JACK and JUE', 'J','BL') from DUAL;
SQL> select DNAME from DEPT;
SQL> select replace(DNAME, 'A', 'i') from DEPT;
<10> SUBSTR('문자열', 자리수, 갯수)
SQL> select SUBSTR('ABCDEFG', 3, 2) from DUAL;
-- ename의 두번째 자리가 'A'인 사원의 이름을 출력
SQL> select ENAME from EMP where ENAME like '_A%';
SQL> select ENAME from EMP where substr(ENAME,2,1)='A';
<11> ASCII('문자')
SQL> select ascii('A') from dual;
SQL> select ASCII('"') from DUAL;
SQL> select ASCII('#') from DUAL;
SQL> select ASCII('3') from DUAL;
<12> LENGTH('문자열')
SQL> select length('진달래 꽃') from DUAL;
SQL> select length('abC D') from DUAL;
<13> GREATEST('문자열1', '문자열2', '문자열3')
SQL> select GREATEST('CC', 'ABCDE', 'CA') from DUAL;
SQL> select GREATEST('12', '132', '119') from DUAL;
SQL> select GREATEST('가나', '가다', '가자') from DUAL;
<14> LEAST('문자열')
SQL> select LEAST('CC', 'ABCDE', 'CA') from DUAL;
SQL> select LEAST('12', '132', '119') from DUAL;
SQL> select LEAST('가나', '가다', '가자') from DUAL;
<15> INSTR('문자열1', '문자열2', 자리수1, 자리수2)
SQL> select INSTR('CORPORATE FLOOR', 'OR', 3, 2) from DUAL;
SQL> select INSTR('CORPORATE FLOOR', 'OR', 6, 1) from DUAL;
cf) '자리수1'부터 '자리수2'번째의 '문자열2'를 찾음
<16> NVL(컬럼명, 숫자) **
SQL> select ENAME, NVL(COMM, 100) from EMP;
SQL> select ENAME, NVL(COMM, 0) from EMP;
2) 숫자 함수
<1> ABS(숫자)
SQL> select ABS(-15) from DUAL;
<2> CEIL(숫자)
SQL> select CEIL(11.012) from DUAL;
SQL> select CEIL(-11.012) from DUAL;
<3> FLOOR(숫자)
SQL> select FLOOR(12.999) from DUAL;
SQL> select FLOOR(-12.999) from DUAL;
<4> ROUND(숫자)
SQL> select ROUND(12.5) from DUAL;
SQL> select ROUND(-12.5) from DUAL;
<5> COS(숫자[rad])
SQL> select cos(90 * 3.141592653589793/180) from dual;
<6> SIN(숫자[rad])
SQL> select sin(90 * 3.141592653589793/180) from dual;
<7> TAN(숫자[rad])
SQL> select TAN(45 * 3.141592653589793/180) from DUAL;
<8> EXP(숫자)
SQL> select EXP(2) from dual;
<9> LOG(숫자1, 숫자2)
SQL> select LOG(10, 100) from dual;
<10> MOD(숫자1, 숫자2)
SQL> select mod(11,4) from dual;
<11> POWER(숫자1, 숫자2)
SQL> select power(3, 3) from dual;
<12> TRUNC(숫자1, 숫자2)
SQL> select TRUNC(15.789, 2) from DUAL;
SQL> select TRUNC(15.789, 0) from DUAL;
SQL> select TRUNC(-15.789, 2) from DUAL;
3) 날짜 함수 (***)
<1> SYSDATE
SQL> select SYSDATE from dual;
<2> ADD_MONTHS(날짜컬럼 or 날짜데이터, 숫자)
SQL> select HIREDATE from EMP where EMPNO=7782;
SQL> select HIREDATE, ADD_MONTHS(HIREDATE, 7) from EMP where EMPNO=7782;
SQL> select ADD_MONTHS('90/11/04', 2) from DUAL;
<3> LAST_DAY(날짜컬럼 or 날짜데이터)
SQL> select LAST_DAY('19/10/29') from DUAL;
SQL> select hiredate, LAST_DAY(hiredate) from EMP;
<4> NEW_TIME(날짜컬럼or날짜데이터, 'GMT', 'PDT')
SQL> select NEW_TIME('08/12/04', 'GMT', 'PDT') from DUAL;
SQL> select hiredate, NEW_TIME(hiredate, 'GMT', 'PDT') from EMP;
<5> MONTHS_BETWEEN(날짜컬럼or날짜데이터1, 날짜컬럼or날짜데이터2)
SQL> select MONTHS_BETWEEN('80/01/02', '81/02/04') from DUAL;
-- empno가 7782인 사원의 현재까지의 근무월수
SQL> select MONTHS_BETWEEN(SYSDATE, HIREDATE) from EMP where EMPNO=7782;
<6> NEXT_DAY(날짜컬럼or날짜데이터, 숫자)
SQL> select SYSDATE, NEXT_DAY(SYSDATE,4) from DUAL;
SQL> select SYSDATE, NEXT_DAY(SYSDATE,2) from DUAL;
-- 일(1) 월(2) 화(3) 수(4) 목(5) 금(6) 토(7)
4) 문자 변환 함수 ( ***** )
=> TO_CHAR(날짜컬럼or날짜데이터, '변환포멧')
<1> 'D'
SQL> select SYSDATE, to_char(SYSDATE, 'D') from dual;
<2> 'DAY'
SQL> select SYSDATE, to_char(SYSDATE, 'DAY') from dual;
<3> 'DY'
SQL> select SYSDATE, to_char(SYSDATE, 'DY') from dual;
<4> 'DD'
SQL> select SYSDATE, to_char(SYSDATE, 'DD') from dual;
<5> 'MM'
SQL> select SYSDATE, to_char(SYSDATE, 'MM') from dual;
<6> 'MONTH'
SQL> select SYSDATE, to_char(SYSDATE, 'MONTH') from dual;
<7> 'YY'
SQL> select SYSDATE, to_char(SYSDATE, 'YY') from dual;
<8> 'YYYY'
SQL> select SYSDATE, to_char(SYSDATE, 'YYYY') from dual;
<9> 'MM-DD-YY'
SQL> select SYSDATE, to_char(SYSDATE, 'MM-DD-YY') from dual;
<10> 'HH' or 'HH12'
SQL> select SYSDATE, to_char(SYSDATE, 'HH') from dual;
<11> 'HH24'
SQL> select SYSDATE, to_char(SYSDATE, 'HH24') from dual;
<12> 'MI'
SQL> select TO_CHAR(SYSDATE, 'MI') from DUAL;
<13> 'SS'
SQL> select TO_CHAR(SYSDATE, 'SS') from DUAL;
<14> 'AM'or'PM'
SQL> select TO_CHAR(SYSDATE, 'AM HH:MI:SS') from DUAL;
SQL> select TO_CHAR(SYSDATE, 'fmAM HH:MI:SS') from DUAL;
--현재날짜를 '2020-12-07 오후 12:13:50 월요일' 식으로 출력
<15> 'YYYY-MM-DD AM HH:MI:SS DAY'
SQL> select TO_CHAR(SYSDATE, 'YYYY-MM-DD AM HH:MI:SS DAY') from DUAL;
cf) alter session set NLS_LANGUAGE='ENGLISH';
cf) Tip
- one, two, three ..
SQL> select to_char(SYSDATE, 'DDSP') from dual;
- simple 서수
SQL> select to_char(SYSDATE, 'DDTH') from dual;
- 서수
SQL> select to_char(SYSDATE, 'DDSPTH') from dual;
(5) 복수행 함수 ( ***** )
1) count(컬럼명)
SQL> select count(ename) from emp;
SQL> select count(comm) from emp;
SQL> select count(*) from emp;
SQL> select count(EMPNO) from emp;
2) sum(컬럼명)
SQL> select sum(comm) from emp;
SQL> select sum(sal) from emp;
3) avg(컬럼명)
SQL> select avg(sal) from EMP;
SQL> select avg(comm) from EMP; -- null제외
SQL> select avg(nvl(comm,0)) from EMP; -- null포함
4) max(컬럼명)와 min(컬럼명)
SQL> select max(sal) from EMP;
SQL> select min(sal) from EMP;
SQL> select max(sal) - min(sal) from EMP;
-- 최고 급여자의 사원이름을 출력
SQL> select ENAME, SAL from EMP where SAL=max(SAL); --(X)
SQL> select ENAME, SAL from EMP where SAL=(select max(SAL) from EMP); --(O)
5) VARIANCE(컬럼명)
SQL> select VARIANCE(comm) from EMP;
6) STDDEV(컬럼명)
SQL> select STDDEV(comm) from EMP;
< Question >
-- 부서별 평균연봉(반올림)을 출력
Err> select DEPTNO, avg(SAL) from EMP;
SQL> select DEPTNO, avg(SAL) from EMP group by DEPTNO;
SQL> select DEPTNO, round(avg(SAL)) from EMP group by DEPTNO;
-- 급여 2000 이상인 사원들의 부서별 평균급여의 반올림값
SQL> select DEPTNO, round(avg(SAL)) from EMP
where SAL>=2000 group by DEPTNO;
-- 급여 2000 이상인 사원들의 부서별 평균급여의 반올림값 (부서번호의 오름차순 정렬)알게된 개념
cmd창에ping 도메인주소하면 해당 ip를 알아올 수 있다.- 복수행 함수에는
where조건문을 쓸 수가 없다.
휘발방지