Database

오라클 - 산술함수,문자열함수,날짜함수,그룹함수

그녕이 2008. 9. 19. 12:17

10-1.산술 함수
 
  • ABS(n) : 절대값 |n|
  • sin() cos() tan() sinH() cosH() tanH() : radian값을 이용
    • ex> 30' 일 경우 sin(30* (3.14.../180));
  • exp(n) : 지수값 e를 밑으로 하는 e의 n승 값;
  • LN(n):자연 log값 밑수 e 지수 n
  • SQRT(n): 루트n
  • power(m,n): m의 n승
  • MOD(m.n) : m을  n 으로 나눈 나머지
  • floor(n): n보다 작은 정수값들중에서 최대값
  • ceil(n): n보다 큰 정수값들중에서 최소값
     
    • ex> floor(3.5)   -->  3   ,    ceil (3.5)  -->  4
    •        floor(-3.2) --> -4  ,     ceil (-3.5) --> -3
  • greatest(m,n): 두 수중에서 큰것
  • least(m,n);두 수중에서 작은 것
  • TO_NUMBER('숫자형 문자열'):문자를 숫자로 변환
  • round(m,n):반올림
  • trunc(m,n):절삭한다 즉 반올림을 허용하지 않는다.
     
    • ex>  Round(2.4678, 2)   -->  2.47   ,    trunc(2.4687, 2)    -->  2.46
    •      Round(3654.26, -2) --> 3700  ,   trunc (3654.26, -2 ) --> 3600
  • NVL(col명,값): col의 값이 null를 가질때 값을 가진다.
    • ex> 100+NULL은 NULL이 되기 때문에 이러한 문제점을 해결할 때 사용한다.
    • select ename,sal,comm,sal+nvl(comm,0) from emp;
               -->sal값과 comm값을 더하는데 comm값이 null값이면 0값을 취한다...
      
  < 실습 6. 산술함수의 활용 >
SQL> select ename, sal, comm, sal+comm "수령액" from emp; 
        --> comm 이  null 일경우 null과의 연산결과인 '수령액'은 모두 null을 출력한다.  
           따라서,  이를 해결하기 위해 NVL 함수 사용한다. 

ENAME            SAL      COMM    수령액  
---------- --------- --------- ---------  
SMITH            800  
ALLEN           1600       300      1900  
...........................
14 개의 행이 선택되었습니다. 
 
 

SQL> select ename, sal, comm, sal+ NVL(comm, 0) "수령액" from emp; 
        -- NVL 함수를 사용한 결과 수령액이 모두 출력됨을 알수 있다 

ENAME            SAL      COMM    수령액  
---------- --------- --------- ---------  
SMITH            800                  800  
ALLEN           1600       300     1900  
WARD            1250       500     1750  
............................
14 개의 행이 선택되었습니다. 

 

  
  
 

 

   10-2.문자열 함수

  •  lower():소문자로 변환시켜주는 함수
  • upper():대문자로 변환시켜주는 함수
  • Initcap():단어의 첫자만 대문자로 나머지는 소문자로
     
    • Initcap('i LOVE you') ---->  I Love You
     
  •  ength():문자열의 길이
  • substr(문자열,위치,갯수): 해당위치에서 갯수만큼의 문자열을 추출한다.
     
    • substr ('KOREA', 3, 2) ---->  'RE'
      -- 3번째인 R부터 2글자 출력
  • instr(문자열,찾을 문자열[,위치,찾을위치])
       :문자열에서 이 문자를 찾아라. return값은 찾은 위치
       
    • Instr("abcdabkdoerabjdlfjdg","ab") -----> 1
      -- 1번째에서 시작해서 처음  ab가 나타나는 1출력
    • Instr("abcdabkdoerabjdlfjdg","ab",4,2) ---->  12
      -- 4번째부터 시작해서 두번째 나타나는  ab 출력
     
  • user:현재 접속된 사용자를 보여준다.
  • decode(col명,비교값,취할값,비교값,취할값,....기본값)
       :col값이 비교값이면 취할값으로 대체.
     
    • decode(deptno,10,'총무부',20,'영업부',30,'전산부','관리부'):나머지는 관리부
      -- deptno가 10이면 총무부 , 20이면 영업부, 30이면 전산부 나머지 는 관리부 출력
       

  < 실습. 문자열 함수의 활용 >
 
SQL> select saname||'님' "이름", DECODE(deptno,10,'총무',20,'영업',30,'전산','관리') "부서명" FROM  sawon; 
   -- saname에 '님'을 추가 입력후 deptno가 10이면 총무 , 20이면 영업, 30이면 전산부 나머지 는 관리 출력  

이름         부서명  
------------ ----  
이순신님     영업  
이미라님     전산  
이순라님     영업  
공부만님     전산  
.............
20 개의 행이 선택되었습니다. 
  

SQL> select goname "고객이름", gojumin "주민등록번호",  
      substr(gojumin,1,2)||'년생' "생년월일",  
     decode(substr(gojumin,8,1), 1,'남자',2,'여자') "성별"  
      from gogek; 
  

고객이름   주민등록번호     생년월일 성별  
---------- ---------------- -------- ----  
류민       111112-1567890   11년생   남자  
강민       222322-1567890   22년생   남자  
..................
10 개의 행이 선택되었습니다. 
 

  
 

 

  10-3.날짜 함수

  • TO_CHAR(날짜,'포맷'): 날짜를 문자열로 바꾼다.
  • TO_DATE(문자열,'포맷'):문자열을 날짜로 ex> TO_DATE('97-10-14',' -----')
  • ADD_MONTHS(날짜,n):현재 날짜에 n개월수를 더한다.
  • MONTHS_BETWEEN(날짜1,날짜2):두 날짜의 개월수의 차를 구한다.
  • LAST_DAY(날짜):그 달의 마지막 날짜를 구한다.
  • NEXT_DAY(날짜,'요일'):날짜로 부터 다음에 나오는 요일은 며칠인가
    • next_day(sysdate,'금요일')
  • 날짜 + n : 날짜에다 n 일을 더한다.
  • 포맷:
    • yy : 년도를 2자리로
    • yyyy : 년도를 4자리로
    • year : 영어 철자로 표시
    • mm :월을 표시(01-12)
    • mon :월을 표시(DEC....):약자로 나온다.
    • month :월을 표시(1월,2월,....혹은 ,DECEMBER)
    • d :일을 표시(주에 대한 일)
    • dd :일을 표시 (월에 대한 일)
    • ddd :일을 표시 (년에 대한 일)
    • Q : 분기를 구한다.
    • DAY :요일 (월요일)
    • DY  :요일(월)
    • HH & HH12 :시간을 12시간제로
    • HH24 :24시간제로
    • MI:분
    • SS:초
    • AM & PM & A.M & P.M:12시간제 일때 오전 오후를 표시
    • 접미사로 사용하는 포맷
      • TH:서수로 표시 ex> 4 -> DD ->4
      •                               4-> DDTH -> 4TH
      • SP:철자로 표시 ex> 4 ->DDSP -> FOUR
      • SPTH & THSP  ex> 4 -> DDSPTH -> FOURTH
      •  근무한 년월 수 ex> column "근무년수" Format a 14;

  < 실습 6-3. 날 짜 함수의 활용 >
SQL> select saname, sahire, add_months(sahire,12), sahire+365, sahire-5 FROM sawon; 

SANAME     SAHIRE    ADD_MONTHS   SAHIRE+365       SAHIRE-5  
---------- ---------------- ---------------- ---------------- ----------------  
이순신     85/03/01         86/03/01         86/03/01         85/02/24  
이미라     83/04/01         84/04/01         84/03/31         83/03/27  
이순라     90/05/01         91/05/01         91/05/01         90/04/26  
공부만     95/05/01         96/05/01         96/04/30         95/04/26  
한국남     88/11/01         89/11/01         89/11/01         88/10/27  
.....................

20 개의 행이 선택되었습니다. 
 

SQL>select  saname, sahire, months_between(sysdate, sahire), last_day(sahire), 
  2     next_day(sysdate, '월요일') FROM sawon 
SQL> / 

SANAME     SAHIRE      MONTHS_BETWEEN(SYSDATE,SAHIRE)  LAST_DAY(SAHIRE)  NEXT_DAY(SYSDATE  
---------- ---------------- ------------------------------ ---------------- ----------------  
이순신     85/03/01                              154.41549  85/03/31              98/01/19  
이미라     83/04/01                              177.41549  83/04/30              98/01/19  
이순라     90/05/01                              92.415486  90/05/31              98/01/19  
공부만     95/05/01                              32.415486  95/05/31              98/01/19  
한국남     88/11/01                              110.41549  88/11/30              98/01/19  
.............
20 개의 행이 선택되었습니다. 
 

SQL> select saname, sapay, TO_CHAR(sapay, '9,999,999.99') FROM sawon; 

SANAME         SAPAY TO_CHAR(SAPAY  
---------- --------- -------------  
이순신          3500      3,500.00  
이미라          2503      2,503.00  
이순라          1200      1,200.00  
공부만          4003      4,003.00  
...............

20 개의 행이 선택되었습니다. 

SQL> select saname, sahire, TO_CHAR (sahire, 'yy-yyy-yyy-year MM-MON-month d-dd-ddd') FROM sawon; 
            -- 입사일에 대한 포맷 형태를 지정 
 
SANAME     SAHIRE  
---------- ----------------  
TO_CHAR(SAHIRE,'YY-YYY-YYY-YEARMM-MON-MONTHD-DD-DDD')  
---------------------------------------------------------------------------  
이순신     85/03/01  
85-985-985-nineteen eighty-five 03-MAR-3월  6-01-060  
이미라     83/04/01  
83-983-983-nineteen eighty-three 04-APR-4월  6-01-091  
...................  
20 개의 행이 선택되었습니다. 

--> 위의 날짜를 포맷형태를 변화시켜 출력 
SQL> select saname, TO_CHAR(sahire, 'yy/mm/dd Q DAY') FROM sawon; 

SANAME     TO_CHAR(SAHIRE,'YY/MM/DDQDAY')  
---------- ---------------------------------------------------------------------------  
이순신     85/03/01 1 금요일  
이미라     83/04/01 2 금요일  
이순라     90/05/01 2 화요일  
.................
20 개의 행이 선택되었습니다. 

SQL> select saname, TO_CHAR (sahire, 'yyyy"년" mm"월" dd"일" dy Q"/분기" ') "날짜" FROM sawon; 
         --sahire를 년, 월, 일, 분기를 함께 출력해야함으로 날짜를 문자열로 바꿔주는 TO_CHAR함수를 쓴다. 

SANAME     날짜  
---- ---------------------------------------------------------------------------  
이순신     1985년 03월 01일 금 1/분기  
이미라     1983년 04월 01일 금 2/분기  
이순라     1990년 05월 01일 화 2/분기  
........................
20 개의 행이 선택되었습니다. 

 


 

문 제 풀 이 
 
문1>날짜함수를 이용 사원명, 입사일, 근무연수( ~년~개월), 퇴직일(입사후 20년) 출력 
    select saname "사원명", 
    TO_CHAR (sahire, 'yyyy"년" mm"월" dd"일"')    "입사일", 
    floor( MONTHS_BETWEEN (sysdate,sahire)/12)||'년' 
    ||floor(MOD(MONTHS_BETWEEN (sysdate,sahire),12))||'개월'  "근무연수", 
    ADD_MONTHS (sahire, 240) "퇴직일" 
    from sawon;
 
문2>고객 테이블을 이용 고객번호, 주민등록번호, 나이,  성별 출력 
    select gobun "고객번호", 
    gojumin "주민등록번호", 
    TO_NUMBER(TO_CHAR(sysdate,'yy'))- TO_NUMBER(substr(gojumin,1,2))||'살' "나이", 
    decode(substr(gojumin,8,1),1,'male',2,'female')  "성별"  
    from gogek;


 

 
 

  10-4.그룹 함수

  •  group함수 :
    • NULL값은 계산되지 않는다.
    • 여러개의 데이타에 대한 결과 이므로 그룹함수라 한다
    • 단일 필드와 함께 사용할 수 없다.
    • 만약 단일 필드와 사용되면 group by절에 반드시 나와야 한다.
  • sum() : 합
  • avg() : 평균
  • count() : 갯수  count(*): 리턴되는 레코드의 수를 계산
    • ex> select deptno,sum(sapay) from sawon;  에러
       -->단위 필드이므로 따라서 마지막에 group by deptno;라고 지정해주면 사용가능
  • max() : 최대값
  • min() : 최소값
  • stddev : 표준편차
  • variance : 분산  
 
  <실습. 그룹 함수의 활용>
 SQL> select sum(sapay), avg(sapay), MAX(sapay), MIN(sapay) FROM sawon; 

SUM(SAPAY)    AVG(SAPAY)   MAX(SAPAY)    MIN(SAPAY)  
---------- ---------- ---------- ----------  
     48318           2415.9          5000            400  
  

SQL>  select sum(sapay), avg(sapay), MAX(sapay), MIN(sapay) FROM sawon where deptno=10; 
             --> WHERE절 추가 

SUM(SAPAY) AVG(SAPAY) MAX(SAPAY) MIN(SAPAY)  
---------- ---------- ---------- ----------  
     17400       2900       5000       1100  
  

SQL> select deptno, sum(sapay), avg(sapay), MAX(sapay), MIN(sapay) FROM sawon where deptno=10; 
      * 
라인 1 에 오류: 
ORA-00937: 단일 그룹의 그룹 함수가 아닙니다  
          --10번 부서를 찍을 수 있는 방법은? --- 없다... 단일필드인 deptno와 함께 사용할 수 없다. 

SQL> select count (gobun), count(*), count(godam) FROM gogek; 
         -- count사용 

COUNT(GOBUN)  COUNT(*) COUNT(GODAM)  
------------ --------- ------------  
          10        10            8  

      --결과를 보면  godam에서 8인것으로 보아 NULL은 그룸함수에서 계산되지않다... 
      --count (*)는 줄의 갯수를 의미 
SQL>  select deptno,sum(sapay), count(*) FROM sawon GROUP BY deptno ; 
          --GROUP BY를 이용한 그룹함수 
  
 DEPTNO SUM(SAPAY)  COUNT(*)  
--------- ---------- ---------  
       10      17400         6  
       20      17200         7  
       30      13718         7  

SQL> select deptno, sasex, sum(sapay),count(*) FROM sawon GROUP BY deptno, sasex; 
          --여러개의 그룹을 줄수도 있다... 예를들어 학년별, 반별로 그룹을지고자 할때 

   DEPTNO SASE SUM(SAPAY)  COUNT(*)  
--------- ---- ---------- ---------  
       10 남자      11300         3  
       10 여자       6100         3  
       20 남자       8700         3  
       20 여자       8500         4  
       30 남자       9512         5  
       30 여자       4206         2  

6 개의 행이 선택되었습니다. 
 
SQL>  select deptno, sasex, sum(sapay),count(*) FROM sawon where sajob !='과장' 
          GROUP BY deptno, sasex; 
          --과장을 제외한 사람을 출력하고자 할때.... WHERE절 사용 

   DEPTNO SASE SUM(SAPAY)  COUNT(*)  
--------- ---- ---------- ---------  
       10 남자       6800         2  
       10 여자       6100         3  
       20 남자       5200         2  
       20 여자       4600         2  
       30 남자       5509         4  
       30 여자       4206         2  
6 개의 행이 선택되었습니다. 
 
SQL>select deptno, sasex, sum(sapay),count(*) FROM sawon where sajob !='과장' 
  2      having count(*)>=3 
  3*  GROUP BY deptno, sasex; 
        --->HAVING 조건의 추가 
 
   DEPTNO SASE SUM(SAPAY)  COUNT(*)  
--------- ---- ---------- ---------  
       10 여자       6100         3  
      30 남자       5509         4  

SQL> l 
  1   select deptno, sasex, sum(sapay),count(*) FROM sawon where sajob !='과장' 
  2  having count(*)>=3 
  3   GROUP BY deptno, sasex 
  4* order by count(*) desc 
SQL> / 

   DEPTNO SASE SUM(SAPAY)  COUNT(*)  
--------- ---- ---------- ---------  
       30 남자       5509         4  
       10 여자       6100         3  

SQL> select ROWNUM, saname FROM sawon; 
                -->ROWNUM를 입력시킨면 행번호가 출력된다.... 
    ROWNUM SANAME  
--------- ----------  
        1 이순신  
        2 이미라  
................
      18 강감찬  
       19 임꺼정  
       20 깨똥이  

20 개의 행이 선택되었습니다. 


SQL> select sum(sapay), avg(sapay) FROM sawon GROUP BY floor((rownum-1)/5); 
        -- rownum 함수를 이용,, 다섯 개 단위로 끊어서  합계와 평균을 출력 

SUM(SAPAY) AVG(SAPAY)  
---------- ----------  
     14206     2841.2  
      8006     1601.2  
     15200       3040  
     10906     2181.2