데이터베이스 - 단일 행 함수

문자 함수
* 대소문자 변환 함수

예제 1. ERP 부서가 있는 지역을 검색한다. 

SQL> SELECT loc ERP_부서_지역 
 2 FROM dept 

 3 WHERE LOWER(dname)='erp';

 

ERP_부서_지역
------------ 

서울

 

예제 2. 'XX 부서는 XX에 위치합니다.' 형식으로 부서 정보를 검색한다. 

SQL> SELECT INITCAP(dname) || ' 부서는 ' || loc || '에 위치합니다.' 부서_위치
 2 FROM dept;
부서_위치

-------------------------------- 

총무 부서는 서울에 위치합니다. 

회계 부서는 서울에 위치합니다.
Erp 부서는 서울에 위치합니다.
................
................

 

* 문자 연산 함수

 

예제 3. 부서의 명과 위치를 하나의 컬럼으로 검색한다. 

SQL> SELECT CONCAT(dname,' '|| loc) CONCAT
 2 FROM dept; 
 
CONCAT
-----------------

총무 서울
회계 서울
ERP 서울
................
................

 

concat 함수는 인자를 둘 밖에 사용할 수 없다.

  -> '||' 연산자에 비해 활용도가 떨어진다.

일반적으로 연산자가 함수보다 성능이 좋다. 

 

예제 4. 부서명과 문자열의 길이를 출력한다. 

SQL> SELECT dname, LENGTH(dname), LENGTHB(dname)
 2 FROM dept;
DNAME LENGTH(DNAME) LENGTHB(DNAME)
----------            -------------            -------------- 

총무                           2                             4
회계                           2                             4
ERP                           3                             3
................
................ 

 

length -> 글자의 수 , lengthb -> Byte 

 

예제 5. substr 함수를 이용해서 컬럼에 일부 내용만을 검색한다. 

SQL> SELECT ename, SUBSTR(ename,2), 
 2 SUBSTR(ename,-2), 
 3 SUBSTR(ename,1,2),
 4 SUBSTR(ename,-2,2)
 5 FROM emp; 

 

ENAME SUBSTR(ENAME,2) SUBS SUBS SUBS
---------- ------------------          ----     ----    ---- 

안영희    영희                         영희   안영   영희
안영숙    영숙                         영숙   안영   영숙
손하늘    하늘                         하늘   손하   하늘
................
................ 

 

매개 인자로 제공된 문자열에서 일부를 검색하는 함수

 

substr(ename,2) : 앞에서 두글자 추출

substr(ename,-2) : 뒤에서 두글자 추출

substr(ename,1,2) : 첫 번째 문자로부터 두 글자 추출

substr(ename,-2,2) : 뒤에서 두 번째 문자로부터 두글자 추출

 

예제 6. 다양한 방법으로 'a'가 나오는 위치를 출력한다. 

SQL> SELECT INSTR('database','a'), 
 2 INSTR('database','a',3),
 3 INSTR('database','a',1,3)
 4 FROM dual;

INSTR('DATABASE','A')    INSTR('DATABASE','A',3)     INSTR('DATABASE','A',1,3)
-----------------------------    --------------------------------     -----------------------------------
                                   2                                            4                                               6

 

instr('database','a') : a가 처음 나오는 위치 검색

instr('database','a',3) : 세 번째 문자 이후에 'a'의 위치 검색

instr('database','a',1,3) : 첫 번째 문자 이후에 'a'가 세 번째 나오는 위치 검색 

 

예제 7. TRIM 함수를 이용 다양한 방법으로 문자열을 검색한다.

SQL> SELECT TRIM('남' from '남기남'),  < --- 맨앞 남 과 맨뒤 남 삭제 후 출력
 2 TRIM(leading '남' from '남기남'),   <---- 맨앞 남 삭제 후 출력
 3 TRIM(trailing '남' from '남기남'),  <---- 맨뒤 남 삭제 후 출력
 4 TRIM(' 남기남 ')  <----- 그냥 출력
 5 FROM dual;
TR TRIM TRIM TRIM('
--    ----    ----    ------ 

기  기남   남기   남기남

 

TRIM 함수는 문자열의 맨 앞이나 마지막의 문자를 삭제할 때 이용하는 문자 함수. 

 

예제 8. 이름과 급여를 각각 10 컬럼으로 검색한다. 

SQL> SELECT RPAD(ename, 10, '*'), LPAD(sal, 10, '*') 
 2 FROM emp;

RPAD(ENAME,10,'*') LPAD(SAL,10,'*')
--------------------            -------------------- 

안영희****                        ******4800
안영숙****                        ******3900
손하늘****                        ******3510
................
................ 

 

문자열의 출력 폭과 채움 문자를 지정하는 함수이다.

주로 보고서 등 무결성이 중요한 문서를 작성할 때 임의로 내용이 변견되는 것을 방지하기 위해 사용된다.

RPAD - 출력 결과물이 앞에 나온다.

LPAD - 출력 결과물이 뒤에 나온다.

 

예제 9. 부서명의 마지막 글자를 제외하고 검색한다.

SQL> SELECT dname, SUBSTR(dname, 1, LENGTH(dname)-1) dname 
 3 FROM dept; 

 

DNAME DNAME
---------- ---------------- 

총무       총
회계       회
ERP       ER 
................
................

 

* 문자 치환 함수

예제 10. 형식이 비슷한 TRANSLATE와 REPLACE를 동일한 치환을 통해 비교해 보자
SQL> SELECT TRANSLATE('World of Warcraft', 'Wo', '--') Translate,     ---> 문자의 대소문자 구분을 한다. 
 2 REPLACE('World of Warcraft', 'Wo', '--') Replace                          ------>  문자열의 대소문자 구분을 한다.
 3 FROM dual;

 

TRANSLATE   REPLACE
-----------------   ------------------

rld -f -arcraft     --rld of Warcraft

 

실습 (모두 단일 행 함수를 이용한다.)
1. 이름이 두 글자인 학생의 이름을 검색한다. 

    SELECT SNO, SNAME

 2 FROM STUDENT

 3 WHERE LENGTH(SNAME) = 2;

 

2. '강' 씨 성을 가진 학생의 이름을 검색한다. 

    SELECT SNO, SNAME

 2 FROM STUDENT

 3 WHERE SUBSTR(SNAME, 1, 1) = '강';

 

3. 교수의 지위를 한글자로 검색한다.(ex. 조교수 → 조)

    SELECT PNO, PNAME, SUBSTR(ORDERS, 1, 1)

 2 FROM PROFESSOR;

 

4. 일반 과목을 기초 과목으로 변경해서 모든 과목을 검색한다. (ex. 일반화학 → 기초화학)

    SELECT CNO, CNAME, REPLACE(CNAME, '일반', '기초')

 2 FROM COURSE;

 

5. 만일 입력 실수로 student 테이블의 sname 컬럼에 데이터가 입력될 때 문자열 마지막에 공백이

 추가 되었다면 검색할 때 이를 제외하고 검색하는 SELECT문을 작성한다.

    SELECT SNO, SNAME FROM STUDENT

 2 WHERE SUBSTR(SNAME, -1, 1 != ' ');

 

6. 직원의 년봉을 10자리로 검색한다. 단 공백은 임의의 채움 문자로 채워 넣는다.

   SELECT ENO, ENAME, LPAD(SAL*12, 10, '*')

2 FROM EMP;

 

7. 학생의 이름을 검색한다. 단 성이 '심'인 학생은 성을 '사마' 로 바꾸어 검색한다.

   SELECT SNO, CONCAT(REPLACE(SUBSTR(SNAME, 1, 1), '공', '사마'),

2 SUBSTR(SNAME, 2)) FROM STUDENT;

 

숫자 함수

숫자 함수는 숫자를 인자로 사용하고 결과 값도 숫자를 반환하는 함수이다. 

예제 1. 다양한 숫자 함수를 이용한 결과를 확인한다.

SQL> SELECT ROUND(98.765), TRUNC(98.765), 
 2 ROUND(98.765,2), TRUNC(98.765,2) 3 FROM dual;
ROUND(98.765)   TRUNC(98.765)   ROUND(98.765,2) TRUNC(98.765,2)
---------------------   --------------------   -----------------------  -----------------------
                      99                          98                        98.77                    98.76

 

SQL> SELECT MOD(19,3), MOD(-19,3) 3 FROM dual;
MOD(19,3) MOD(-19,3)
--------------  ----------------
               1                 -1

 

SQL> SELECT CEIL(3.5), FLOOR(3.5) 3 FROM dual;
CEIL(3.5)    FLOOR(3.5)
-----------     --------------
            4                   3

 

예제 2. 10번 부서의 년봉을 계산한다. 단 100 단위 미만은 절삭한다.

SQL> SELECT eno, ename, TRUNC(sal*12+NVL(comm,0),-2) 년봉

 3 FROM emp
 4 WHERE dno='10';

 

ENO ENAME 년봉
-------- ---------- ----------
1001 문시현 54500
2001 남궁연호 47600
3001 김선유 38700
0269 권나현 33100
0401 김진성 39400

 

날짜 함수와 날짜 연산의 이해
예제 3. 현재 날짜를 검색하고 날짜 출력 양식을 수정해 보자.

SQL> ALTER SESSION SET nls_date_format = 'YYYY/MM/DD:HH24:MI:SS';

세션이 변경되었습니다.

 

SQL> SELECT sysdate FROM dual; 

SYSDATE
-------------------
2018/01/02:11:15:30

 

SQL> ALTER SESSION SET nls_date_format = 'YYYY/MM/DD';
세션이 변경되었습니다. 

 

SQL> SELECT sysdate FROM dual; 

SYSDATE
----------
2018/01/02

 

예제 4. 오늘 날짜, 입사 일자, 입사일로부터 오늘까지 기간, 입사일 이후 100째 되는날 등을 
 검색하고 날짜 연산의 결과를 살펴보자. 

SQL> SELECT sysdate 오늘, hdate 입사일, 
 2 TRUNC(sysdate)-TRUNC(hdate)+1 근무일, hdate+99 "100일"
 3 FROM emp;

오늘 입사일 근무일 100일
---------- ---------- ---------- ----------
2018/01/02 1991/01/01 9864 1991/04/10
2018/01/02 1991/02/01 9833 1991/05/11
2018/01/02 1991/12/01 9530 1992/03/09
................
................ 

예제 5. 숫자와 날짜를 반올림하거나 잘라내고 출력해본다. 

SQL> SELECT sysdate, ROUND(sysdate,'YY') 년, 
 2 ROUND(sysdate,'MM') 월,
 3 ROUND(sysdate,'DD') 일
 4 FROM dual; 

 

SYSDATE  년               월               일
----------       ----------       ----------       ----------
2017/11/30 2018/01/01 2017/12/01 2017/11/30

 

SQL> SELECT sysdate, TRUNC(sysdate,'YY') 년, 
 2 TRUNC(sysdate,'MM') 월,
 3 TRUNC(sysdate,'DD') 일
 4 FROM dual; 

 

SYSDATE  년               월               일
----------       ----------      ----------        ----------
2017/11/30 2017/01/01 2017/11/01 2017/11/30

 

예제 6. 문시현이 오늘까지 일한 일수를 검색한다. 

SQL> SELECT ename, TRUNC(sysdate)-TRUNC(hdate)+1 DAY
 2 FROM emp
 3 WHERE ename='문시현';

 

ENAME DAY
---------- ---------- 

문시현 9833

 

예제 7. 20번 부서 직원들이 현재까지 근무한 개월 수를 검색한다.

SQL> SELECT eno, ename, TRUNC(MONTHS_BETWEEN(sysdate,hdate)) 근무_개월
 2 FROM emp
 3 WHERE dno = '20';

 

ENO ENAME 근무_개월
-------- ---------- ----------
1002 김주란 309
2002 제갈민 260
3002 권아현 203
0120 김경현 219

 

예제 8. 20번 부서원들이 입사 100일째 되는 날과 10년째 되는 날을 검색한다. 

SQL> SELECT eno, ename, hdate 입사일, 
 2 hdate+99 "100일", ADD_MONTHS(hdate,120) "10년"
 3 FROM emp 
 4 WHERE dno = '20'; 

 

ENO ENAME 입사일 100일 10년
-------- ---------- ---------- ---------- ----------
1002 김주란 1992/03/03 1992/06/10 2002/03/03
2002 제갈민 1996/04/30 1996/08/07 2006/04/30
3002 권아현 2001/01/29 2001/05/08 2011/01/29
0120 김경현 1999/09/05 1999/12/13 2009/09/05

 

예제 9. 20번 부서원들이 입사한 이후 첫 번째 일요일을 검색한다. 

SQL> SELECT eno, ename, hdate, NEXT_DAY(hdate,'일요일') Sunday 
 2 FROM emp 
 3 WHERE dno = '20';

 

ENO ENAME HDATE SUNDAY

-------- ---------- ---------- ----------
1002 김주란 1992/03/03 1992/03/08
2002 제갈민 1996/04/30 1996/05/05
3002 권아현 2001/01/29 2001/02/04
0120 김경현 1999/09/05 1999/09/12

 

예제 10. 20번 부서원들의 입사한 달의 마지막 날짜와 입사한 달에 근무 일수를 검색한다.

SQL> SELECT eno, ename, hdate, LAST_DAY(hdate) 마지막날, 
 2 LAST_DAY(TRUNC(hdate))-TRUNC(hdate)+1 "마지막달 근무일수"
 3 FROM emp
 4 WHERE dno = '20';

 

ENO ENAME HDATE 마지막날 마지막달 근무일수
-------- ---------- ---------- ---------- -----------------
1002 김주란 1992/03/03 1992/03/31 29
2002 제갈민 1996/04/30 1996/04/30 1
3002 권아현 2001/01/29 2001/01/31 3
0120 김경현 1999/09/05 1999/09/30 26

 

실습 (모두 단일 행 함수를 이용한다.)
1. 교수들이 부임한 달에 근무한 일수는 몇 일인지 검색한다. 

   SELECT PNO, PNAME,HIREDATE, LAST_DAY(TRUNC(HIREDATE)) - TRUNC(HIREDATE) + 1

"부임한달 근무일수" FROM PROFESSOR;

 

2. 교수들의 오늘까지 근무한 주가 몇 주인지 검색한다. 

    SELECT PNO, PNAME, HIREDATE, TRUNC((SYSDATE-HIREDATE+1)/7) "근무한 주"

 2 FROM PROFESSOR; 

 

3. 1991년에서 1995년 사이에 부임한 교수를 검색한다.

    SELECT PNO, PNAME, HIREDATE

 2 FROM PROFESSOR

 3 WHERE TRUNC(HIREDATE, 'YYYY') >= '1991/01/01'

 4 AND TRUNC(HIREDATE, 'YYYY') <= '1995/12/31'

 

4. 학생들의 4.5 환산 평점을 검색한다. (단 소수 이하 둘째 자리까지)

    SELECT SNO, SNAME, ROUND(AVR*4.5/4, 2) "4.5환산평점"

 2 FROM STUDENT;

 

5. 사원들이 일한 날짜에 대해서만 급여를 받는다면 급여가 현재와 동일하다는 조건에서 입사한 달에
 급여는 얼마나 지급되었을지 검색한다. 

    SELECT ENO, ENAME, HDATE, SAL, SAL*((SUBSTR(TRUNC(LAST_DAY(HDATE)), -2, 2) -   

 2 (SUBSTR(TRUNC(HDATE),-2, 2)-1)) / SUBSTR(TRUNC(LAST_DAY(HDATE)), -2, 2))

 3 "입사한 달의 급여" FROM EMP;

 

6. 사원들의 오늘까지 근무 기간이 몇 년 몇 개월 몇 일인지 검색한다.

    SELECT ENO, ENAME, HDATE, SYSDATE, TRUNC(MONTHS_BETWEEN(TRUNC(SYSDATE+1),

 2 TRUNC(HDATE))/12) "기간_년", TRUNC(MOD(MONTHS_BETWEEN(TRUNC(SYSDATE+1), TRUNC(HDATE)),12))

 3 "기간_월", TRUNC(SYSDATE+1) - TRUNC(ADD_MONTHS(HDATE, MONTHS_BETWEEN(TRUNC(SYSDATE+1),

 4 TRUNC(HDATE)))) "기간_일" FROM EMP;

 

단일 행 함수 : 변환 함수

* 단일 행 함수에서 사용하는 날짜 출력 형식

수정 : 시 - HH24 - 0시에서 23시까지표현

          시 - HH - 0시에서 12시까지 표현

 

TO_DATE - 정해진 날짜 및 숫자를 입력할때
TO_CHAR - 테이블에 저장되어있는 날짜, 숫자를 검색할때

 

예제 1. 현재 날짜를 다양한 형식으로 출력해보자
SQL> SELECT TO_CHAR(sysdate, 'YYYY/MM/DD') 날짜, 
 2 TO_CHAR(sysdate, 'YYYY/MM/DD:HH24:MI:SS') 날짜,
 3 TO_CHAR(sysdate, 'YY/MM/DD:HH:MI:SS AM') 날짜 
 4 FROM dual; 

 

날짜            날짜                           날짜
----------       -------------------            ----------------------
2018/01/02 2018/01/02:12:41:49 18/01/02:12:41:49 오후

 

SQL> SELECT TO_CHAR(sysdate, 'DD Month YYYY') ToDay FROM DUAL;

TODAY
------------
02 1월 2018

 

SQL> SELECT TO_CHAR(sysdate, 'DAY Mon YY') ToDay FROM DUAL;

TODAY
-------------- 

화요일 1월 18

 

SQL> SELECT TO_CHAR(sysdate, 'DY Mon YY') ToDay FROM DUAL;

TODAY
---------- 

화 1월 18

 

SQL> SELECT TO_CHAR(sysdate, '"오늘은 "YYYY"년 "MM"월 "DD"일 입니다."') 날짜
2 FROM DUAL; 날짜
------------------------------ 

오늘은 2018년 01월 02일입니다. 

 

예제 2. 10번 부서 사원의 입사일을 다음의 형식으로 검색한다.
 'XXX 사원의 입사일은 XXXX년 XX월 XX일입니다.' 
SQL> SELECT ename||' 사원의 입사일은 '|| 
 2 TO_CHAR(hdate, 'YYYY"년 "MM"월 "DD"일 입니다."') 입사일    <- 시험관련으로 날짜 년월일 출력할때                          
 3 FROM emp                                                                                          to_char는 무조건 붙여야한다.
 4 WHERE dno = '10';
입사일
------------------------------------------------- 

문시현 사원의 입사일은 1991년 02월 01일 입니다. 

남궁연호 사원의 입사일은 1993년 12월 13일 입니다. 

김선유 사원의 입사일은 1998년 10월 17일 입니다. 

권나현 사원의 입사일은 2015년 05월 21일 입니다. 

김진성 사원의 입사일은 2008년 03월 13일 입니다. 

 

예제 3. 다양한 형식으로 숫자를 출력해 보자
SQL> SELECT TO_CHAR(12345.678, '999,999.99999') num FROM dual;
NUM
--------------

 12,345.67800 ← 소수 이하에 지정한 자리는 0을 채우지만 앞쪽 빈자리는 무시한다.

 

SQL> SELECT TO_CHAR(12345.678, '099,999.999') num FROM dual;
NUM
------------
012,345.678

 

SQL> SELECT TO_CHAR(12345.678, '9,9999.9') num FROM dual;

NUM  ← 소수 이하 자리수를 부족하게 지정하면 반올림 된다
---------
1,2345.7

 

SQL> SELECT TO_CHAR(12345.678, '9,999.9') num FROM dual;
NUM  ← 소수점 앞쪽 형식이 원래 값보다 작으면 출력장애가 발생한다.
--------
########

 

SQL> SELECT TO_CHAR(1234, '$999,999') num FROM dual;
NUM
---------
 $1,234

 

SQL> SELECT TO_CHAR(1234, 'L999,999') num FROM dual;
NUM      ← 시스템의 설정에 따라 경우 '$' 또는 '￥' 기호로 출력되기도 한다.
------------------
 ￦1,234

 

SQL> SELECT TO_CHAR(-1234, '999,999MI') num FROM dual;
NUM
--------
 1,234-

 

SQL> SELECT TO_CHAR(123456789, '9.999EEEE') num FROM dual;
NUM
-----------
 1.235E+08

 

예제 3. 10번 부서 사원의 보너스가 급여의 몇 퍼센트 인지 검색한다.
 - 급여는 월간 급여이고 보너스는 연간 보너스이다.
 - 보너스가 NULL인 경우 0으로 환원해서 검색한다. 

 

SQL> SELECT eno 사번, ename 이름, 
 2 TO_CHAR(NVL(comm,0)/(sal*12)*100, '90.99')||'%' 급여_비율

 3 FROM emp
 4 WHERE dno='10';
사번 이름 급여_비율
---- ---------- ---------
1001 문시현 0.96%
2001 남궁연호 0.42%
3001 김선유 0.78%
0269 권나현 6.09%
0401 김진성 2.60%

 

예제 4. 1992년 이전에 입사한 사원의 정보를 검색해 보자
SQL> SELECT eno 사번, ename 이름, hdate 입사일
 2 FROM emp
 3 WHERE hdate < TO_DATE('19920101','YYYYMMDD');
사번 이름 입사일
---- ---------- ----------
0001 안영희 1991/01/01
0201 안영숙 1991/02/01
0202 손하늘 1991/02/01
0301 이승철 1991/02/01
0302 박선경 1991/02/01
1001 문시현 1991/02/01
6 개의 행이 선택되었습니다. 

 

실습
1. 학생의 평균 평점을 다음 형식에 따라 소수점 이하 두 자리까지 검색한다.
 'XXX 학생의 평균 평점은 x.xx입니다.'

 

2. 교수의 부임 일을 다음 형식으로 검색한다.
 'XXX 교수의 부임일은 YYYY년 MM월 DD일입니다.'

 

3. 교수 중에 3월에 부임한 교수의 명단을 검색한다. 

 

4. 화학과 1학년 학생들의 4.5 환산 평점을 다음 형식에 따라 소수점 이하 두 자리까지 검색한다. 
 'XXX 학생의 4.5 환산 평점은 x.xx입니다.'