[ 함수 (Function) ]


- 어떠한 일을 수행하는 기능으로써 주어진 인수(argument)를 재료로 처리를 하여 그 결과값을 반환함.

 

 
< 함수의 종류  >

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

- 복수행함수 : 여러개의 행당 하나의 결과값을 반환하는 함수

 


 

< 단일행함수  >

 

<1> 문자함수

 

- CHR(아스키코드)

SQL> SELECT CHR(65) FROM DUAL;


    [결과]
    CH
    --
    A

 

- CONCAT(컬럼명,'붙일문자')

SQL> SELECT CONCAT(ENAME,'IS A'),JOB FROM EMP;


    [결과]
   CONCAT(ENAME,'ISA')  JOB
 ------------------------ -------------
    SMITHIS A                    CLERK
    ALLENIS A                    SALESMAN
    WARDIS A                     SALESMAN
    JONESIS A                    MANAGER
        ...

 

- INITCAP('문자열'):시작문자를 대문자로 변환

SQL> SELECT INITCAP('the lion') FROM DUAL;

 

INITCAP('THELION
----------------
The Lion

 


- LOWER('문자열') : 소문자로 변환

SQL> SELECT LOWER('THE LION') FROM DUAL;

 

    LOWER('THELION')
    ----------------
    the lion

 


- LPAD('문자열',자릿수,'채울문자')

SQL> SELECT LPAD(SAL,10,'#') FROM EMP;

 

LPAD(SAL,10,'#')
------------------------
#######800
######1600
######1250
######2975
######1250
######2850
######2450

 


- RPAD('문자열',자릿수,'채울문자')

SQL> SELECT RPAD(SAL,10,'#') FROM EMP;

 

RPAD(SAL,10,'#')
-------------------
800#######
1600######
1250######
2975######
1250######
2850######
2450######
5000######


- LTRIM('문자열1','없앨문자열')

SQL> SELECT LTRIM('THE LION','THE') FROM DUAL;

 

LTRIM('THE
----------
LION

 

SQL> SELECT LTRIM('LION THE','THE') FROM DUAL;

 

LTRIM('LIONTHE',
----------------
LION THE

 

SQL> SELECT LTRIM(' LION',' ') FROM DUAL;

 

LTRIM('L
--------
LION


- RTRIM('문자열1','없앨문자열')

SQL> SELECT RTRIM('APPLE IS','IS') FROM DUAL;

 

    RTRIM('APPLE
    ------------
    APPLE

 

SQL> SELECT RTRIM('APPLE ',' ') FROM DUAL;

 

RTRIM('APP
----------
APPLE

 

SQL> SELECT RTRIM('APPLE%%%%%','%') FROM DUAL;

 

RTRIM('APP
----------
APPLE

 


- REPLACE(문자열,'원래문자열','바꿀문자열')

SQL> SELECT REPLACE('MY NAME','NAME','JOB') FROM DUAL;

 

REPLACE('MYN
------------
MY JOB

 


- SUBSTR('문자열',위치,갯수)

SQL> SELECT SUBSTR('ABCDEF',3,2) FROM DUAL;

 

    SUBS
    ----
    CD

 


- INSTR('문자열1','위치찾는문자열')  : 문자의 위치 찾기 (없으면 0)

SQL> SELECT INSTR('SONG@DAUM.NET','@') FROM DUAL;

 

INSTR('SONG@DAUM.NET','@')
--------------------------
5

 

 

SQL> SELECT INSTR('SONGDAUM.NET','@') FROM DUAL;

 

    INSTR('SONGDAUM.NET','@')
    -------------------------
                0

 

SQL> SELECT INSTR('SONGDAUM.NET','NET') FROM DUAL;

 

    INSTR('SONGDAUM.NET','NET')
    ---------------------------
                 10

 

 

- LENGTH('문자열') : 문자열 길이

SQL> SELECT ENAME,LENGTH(ENAME) FROM EMP;

 

ENAME LENGTH(ENAME)
---------- -------------
SMITH 5
ALLEN 5
WARD 4


- NVL(컬럼명,값) : 컬럼값이 NULL인경우 값으로 채우기

SQL> SELECT ENAME,NVL(COMM,0) FROM EMP;

 

    ENAME                NVL(COMM,0)
    -------------------- -----------
    SMITH                          0
    ALLEN                        300
    WARD                         500
    JONES                          0
    MARTIN                      1400
    BLAKE                          0
    CLARK                          0   

 

 

 

<2> 날짜함수

 

- SYSDATE(*) :현재날짜 얻어오기

SQL> SELECT SYSDATE FROM DUAL;

 

    SYSDATE
    --------
    12/11/20

 


- ADD_MONTHS(날짜,더할개월수)

SQL> SELECT ADD_MONTHS(SYSDATE,3) FROM DUAL;

 

    ADD_MONT
    --------
    13/02/20

 

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

 

    ENAME      ADD_MONT
    ----------- --------
    SMITH         81/06/17
    ALLEN         81/08/20
    WARD          81/08/22
    JONES         81/10/02
    MARTIN       82/03/28

 


- MONTHS_BETWEEN(날짜1,날짜2) : 날짜1과 날짜2의 개월수

 

 SQL> SELECT ENAME,HIREDATE,MONTHS_BETWEEN(SYSDATE,HIREDATE) "근무개월수"
       2  FROM EMP;

 

    ENAME   HIREDATE 근무개월수
    --------- -------- ----------
    SMITH     80/12/17 383.111281
    ALLEN     81/02/20        381
    WARD      81/02/22 380.949991
    JONES    81/04/02 379.595152
    MARTIN   81/09/28 373.756443

   

 


<3> 날짜를 문자로 변환하는 함수(**)


- TO_CHAR(날짜,'형식')

 

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

 

    TO_CHAR(SYSDATE,'YYY
    --------------------
    2012-11-20

 

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

 

    TO_CHAR(SYSDATE,'YYYY-MM-DDHH:MI:SS')
    --------------------------------------
    2012-11-20 11:04:26

 

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

 

    TO_CHAR(SYSDATE,'YYYY/MM/DDAMHH:MI:SS')
    ----------------------------------------------------
    2012/11/20 오전 11:05:54

 

SQL> SELECT TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS') FROM DUAL;--24시간단위

 

    TO_CHAR(SYSDATE,'YYYY/MM/DDHH24:MI:SS'
    --------------------------------------
    2012/11/20 11:07:22

 

SQL> SELECT TO_CHAR(SYSDATE,'YYYY"년"MM"월"DD"일" HH"시"MI"분"SS"초"') "현재시간"
      2  FROM DUAL;

 

       현재시간
    --------------------------------------------
    2012년11월20일 11시10분46초

   

 


<4> 문자를 날짜로 변환하는 함수
   
- TO_DATE('날짜에해당하는문자열','해석할서식')

 

SQL> SELECT TO_DATE('20-11-2012','DD-MM-YYYY') FROM DUAL;

 

    TO_DATE(
    --------
    12/11/20

 

SQL> SELECT TO_DATE('1980-11-20','YYYY-MM-DD') FROM DUAL;

 

    TO_DATE(
    --------
    80/11/20


   

<5> 수학관련 함수

 

- ABS(숫자) : 절대값

SQL> SELECT ABS(-10) FROM DUAL;

 

      ABS(-10)
    ----------
        10

 

- CEIL(숫자) : 올림값구하기

SQL> SELECT CEIL(3.2) FROM DUAL;

 

     CEIL(3.2)
    ----------
         4

 

- FLOOR(숫자) : 내림값 구하기

 

SQL> SELECT FLOOR(3.2) FROM DUAL;

 

    FLOOR(3.2)
    ----------
         3   

 

- ROUND(숫자) : 반올림값 구하기

 

SQL> SELECT ROUND(3.2) FROM DUAL;

 

    ROUND(3.2)
    ----------
         3

 

SQL> SELECT ROUND(3.7) FROM DUAL;

 

    ROUND(3.7)
    ----------
         4   

 

SQL> SELECT ROUND(3.1256,2) FROM DUAL;--소수점2째자리에서반올림적용

 

    ROUND(3.1256,2)
    ---------------
           3.13

 

SQL> SELECT ROUND(3.1256,3) FROM DUAL;--소수점3째자리에서반올림적용

 

    ROUND(3.1256,3)
    ---------------
          3.126

 

- MOD(숫자1,나눌값) : 나머지 구하기

SQL> SELECT MOD(5,3) FROM DUAL;

 

      MOD(5,3)
    ----------
         2   

 

- TRUNC(숫자,절삭할 소숫점자릿수)

SQL> SELECT TRUNC(2.3456,3) FROM DUAL;

 

    TRUNC(2.3456,3)
    ---------------
          2.345      

 

 

 


<  복수행 함수(*)   >

 

<1> COUNT(컬럼명) : 갯수 얻어오기

SQL> SELECT COUNT(EMPNO) "사원수" FROM EMP;

 

        사원수
    ----------
        12

 

SQL> SELECT COUNT(COMM) FROM EMP; --NULL은 COUNT하지 않음

 

    COUNT(COMM)
    -----------
          4

 

SQL> SELECT COUNT(*) FROM EMP;

 

 

<2> SUM(컬럼명) : 컬럼의 합 구하기

 

-- 전체사원의 급여의 총합을 구해보세요.

SQL> SELECT SUM(SAL) "급여합" FROM EMP;

 

<3> AVG(컬럼명) : 컬럼의 평균 구하기

SQL> SELECT AVG(SAL) "급여평균" FROM EMP;

 

<4> MIN(컬럼명),MAX(컬럼명) : 컬럼의 최소값,컬럼의 최대값 구하기

SQL> SELECT MIN(SAL) "최소급여",MAX(SAL) "최대급여" FROM EMP;

 

 


(4) GROUP BY : 컬럼을 기준으로 그룹으로 묶음

 

-- EMP테이블에서 부서별 급여의 평균을 구하시오.

     SELECT DEPTNO,AVG(SAL) 급여평균
     FROM EMP
     GROUP BY DEPTNO;

 

-- EMP테이블에서 급여가 2000이상인 사원들의 부서별 급여총합을 구하세요.

     SELECT DEPTNO,SUM(SAL) "급여총합"
     FROM EMP
     WHERE SAL>=2000
     GROUP BY DEPTNO;

 

-- EMP테이블에서 부서별 최대급여,최소급여를 출력해 보세요.(부서번호로 오름차순정렬)

     SELECT DEPTNO,MAX(SAL) "최대급여",MIN(SAL) "최소급여"
     FROM EMP
     GROUP BY DEPTNO
     ORDER BY DEPTNO ASC


-- 급여가 2000이상인 사원들의 부서별 평균급여을 구하고 평균급여가 높은순으로 정렬하시오.

    SQL> SELECT DEPTNO,AVG(SAL) "AVG"
      2  FROM EMP
      3  WHERE SAL>=2000
      4  GROUP BY DEPTNO
      5  ORDER BY AVG(SAL) DESC; -- ORDER BY AVG DESC :ALIAS사용가능!

 

 

-- 각 직업별 평균급여와 최대급여,최소급여를 구해보세요.

      SQL> SELECT JOB,AVG(SAL) "평균급여",MAX(SAL) "최대급여",MIN(SAL) "최소급여"
           2  FROM EMP
           3  GROUP BY JOB;

 

JOB              평균급여   최대급여   최소급여
------------- ---------- -------- -------
CLERK           1016.66667      1300      800
SALESMAN            1400       1600     1250
PRESIDENT            5000       5000     5000

    

-- 각 부서별 같은 업무를 하는 사람의 평균급여를 구하여 부서별,업무별로 오름차순 정렬해서 출력

     SELECT DEPTNO,JOB,AVG(SAL)
     FROM EMP
     GROUP BY DEPTNO,JOB
     ORDER BY DEPTNO,JOB  ;

 

 DEPTNO JOB             AVG(SAL)
--------- ------------ ----------
         10 CLERK             1300
         10 MANAGER        2450
         10 PRESIDENT      5000
         20 ANALYST         3000
               ......


-- 사원들의 부서별 평균급여를 출력하시오.단 평균급여가 2000이상인 부서만 출력되도록 하시오.
## WHERE 절에는 그룹함수(AVG,MAX,MIN,SUM,..을 사용할 수 없다.)

     SELECT DEPTNO,AVG(SAL)
     FROM EMP
     GROUP BY DEPTNO
     HAVING AVG(SAL)>=2000;

 

 

## SELECT 구문의 실행순서(**)


 SELECT

 컬럼명,..

 5

 FROM

 테이블명

 1

 WHERE

 조건절

 2

 GROUP BY

 절

 3

 HAVING

 절

 4

 ORDER BY

 절

 6


 

     SELECT DEPTNO,AVG(SAL) AG
     FROM EMP
     GROUP BY DEPTNO
     HAVING AG>=2000; -- HAVING절에서는 ALIAS를 사용할 수 없다.

     SELECT DEPTNO,AVG(SAL) AG
     FROM EMP
     GROUP BY DEPTNO
     HAVING AVG(SAL)>=2000
     ORDER BY AG DESC; -- ALIAS사용가능!

 

-- 같은 업무를 하는 사람의 수가 4명 이상인 업무와 인원수를 출력해보세요.

    SQL> SELECT JOB,COUNT(*) 인원수
      2  FROM EMP
      3  GROUP BY JOB
      4  HAVING COUNT(*)>=4;

 

    JOB                    인원수
    ------------------ ----------
    SALESMAN                    4

 

Q1) 10번 부서의 급여의 평균,최고,최저,인원수를 구하세요.

    SELECT AVG(SAL),MAX(SAL),MIN(SAL),COUNT(*)
    FROM EMP
    WHERE DEPTNO=10;

 

Q2) 각 부서별 평균급여,최고급여,최저급여를 구하세요. 단 평균급여가 2000이상인 부서만 출력하세요.

    SQL> SELECT DEPTNO,AVG(SAL),MAX(SAL),MIN(SAL)
          2  FROM EMP
          3  GROUP BY DEPTNO
          4  HAVING AVG(SAL)>=2000;


    DEPTNO   AVG(SAL)   MAX(SAL)   MIN(SAL)
    ---------- ---------- ---------- ----------
        20 2258.33333       3000        800
        10 2916.66667       5000       1300

 

Q3) 각 부서별 인원수를 구해보세요. 인원수가 3명 이상인 부서만 출력되도록 하세요.

    SQL> SELECT DEPTNO,COUNT(*)
          2  FROM EMP
          3  GROUP BY DEPTNO
          4  HAVING COUNT(*)>=3;

   
    DEPTNO   COUNT(*)
    ---------- ----------
        30          6
        20          3
        10          3

 

Q4) 각 부서별 평균급여,전체월급을 구하여 평균월급이 많은 순으로 출력하세요.

    SQL> SELECT DEPTNO,AVG(SAL) "AVG",SUM(SAL)
          2  FROM EMP
          3  GROUP BY DEPTNO
          4  ORDER BY AVG DESC;

   
    DEPTNO        AVG   SUM(SAL)
    ---------- ---------- ----------
        10 2916.66667       8750
        20 2258.33333       6775
        30 1566.66667       9400

 


(5) 기타함수

 

1)

DECODE(대상컬럼,조건1,결과값1,조건2,결과값2
                  ...,    기본값) 별칭

 

 예)

SELECT JOB,

            SAL,

            DECODE(JOB, 'ANALYST',SAL*1.1,
                                 'CLERK',SAL*1.2,
                                 'SALESMAN',SAL*1.3,
                           SAL) 보너스
 FROM EMP;
   

    JOB                       SAL     보너스
    ------------- ---------- ----------
    CLERK                  800        960
    SALESMAN          1600       2080
    SALESMAN          1250       1625
    MANAGER            2975       2975
    SALESMAN          1250       1625
    MANAGER            2850       2850

 


2)

CASE WHEN 조건1 THEN 결과값1
         WHEN 조건2 THEN 결과값2
         WHEN 조건3 THEN 결과값3
         ELSE 기본값
 END 별칭

 

예)

SELECT JOB,SAL,
             CASE WHEN JOB='ANALYST' THEN SAL*1.1
                      WHEN JOB='CLERK' THEN SAL*1.2
                      WHEN JOB='SALESMAN' THEN SAL*1.3
                      ELSE SAL
             END BONUS
   FROM EMP;

 


-- 각 부서별 평균급여를 구하고 평균급여가 3000이상이면 높음, 2000이상이면 보통,1000이상이면 낮음을 출력하세요.

SELECT DEPTNO,AVG(SAL),
             CASE WHEN AVG(SAL)>=3000 THEN '높음'
                      WHEN AVG(SAL)>=2000 THEN '보통'
                      WHEN AVG(SAL)>=1000 THEN '낮음'
                      ELSE '판단불가'
               END "급여수준"
  FROM EMP
 GROUP BY DEPTNO;


 

   DEPTNO   AVG(SAL) 급여수준
    ------ ---------- --------------
           30 1566.66667 낮음
           20 2258.33333 보통
           10 2916.66667 보통
          ...


저작자표시

'DBMS > 오라클기초' 카테고리의 다른 글

오라클 기초#6(Transaction Control Language)  (0) 2013.01.27
오라클 기초#5(Data Manipulation Language)  (0) 2013.01.27
오라클 기초#4 ( JOIN, ROWID, ROWNUM )  (0) 2013.01.27
오라클 기초#3 (subquery)  (0) 2013.01.27
오라클 기초#1  (0) 2013.01.27

+ Recent posts

티스토리툴바