오라클의 함수 및 사용방법

                                                                                                                                                                                                 

[ 함수 만들기 ]

Q1. 검색하다면 불편-> 동적으로 입력(이름) -> ALLEN -> allen으로 입력하면 나오지 않음.

-> 소문자로 입력한 문장을 자동으로 대문자로 변환하여 검색하는 방법(내장 함수)

*DUAL 테이블

오라클의 기본적인 테이블로서 일반 유저가 사용이 가능한 테이블, 간단한 식을 쓸 때 사용

1) 기본 형식을 사용할 경우

select 24*23*12 from emp;

 24*23*12

---------

  6624

  6624

  6624

  6624

  6624

  6624

-> 레코드 수만큼 출력됨

2) dual 사용

SQL> select 24*23*12 from dual

  2 ;

  24*23*12

----------

  6624

3) 날짜 출력(sysdate)

SQL> select sysdate from dual;

SYSDATE

--------

17/04/28

함수의 기능

1. 계산
2. 검색하기

select 함수명(처리해야할 값,,,,), 함수명2(~)

from 테이블명(dual, 검색대상테이블)

Q2. emp테이블에서 전체 14명 중에서 사원의 이름 길이가 5개 이상인 직원을 검색하되, 사원번호, 이름, 업무 순으로 출력하시오.

select empno, ename, job 

from emp 

where length(ename)>=5;

*함수

- length : 문자길이를 구해주는 함수

- lower : 대문자를 소문자로 변경

- upper : 소문자를 대문자로 변경

1) 대소문자 변경

select empno, lower(ename) 소문자로변경, upper(job) 대문자

from emp

where length(ename)>5;

Q3. 저장된 형식, namesch.sql 파일을 불러와 대문자를 소문자로 변경하시오

1) 대문자로 변경하기

SQL> @L:\2017NCS\1.db\print\namesch.sql

ename의 값을 입력하십시오: ed

구 3: where ename='&ename'

신 3: where ename='ed'

선택된 레코드가 없습니다.

SQL> ed

file afiedt.buf(이)가 기록되었습니다

  1 select empno, ename, sal

  2 from emp

  3* where ename=upper('&ename')

SQL> /

ename의 값을 입력하십시오: allen

구 3: where ename=upper('&ename')

신 3: where ename=upper('allen')

  EMPNO ENAME SAL

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

  7499 ALLEN 1600

2) 소문자로 변경하기

SQL> ed

file afiedt.buf(이)가 기록되었습니다

  1 select empno, ename, sal

  2 from emp

  3* where lower(ename)='&ename'

SQL> /

ename의 값을 입력하십시오: allen

구 3: where lower(ename)='&ename'

신 3: where lower(ename)='allen'

  EMPNO ENAME SAL

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

  7499 ALLEN 1600

[SUBSRT 함수]

문자열을 취급, 추출한다. 

substr(대상문자열, 시작위치, 뽑아낼 갯수)

Q4. 'oracletest' 단어에서 일부 단어만 추출하시오

*인덱스번호

1) 다른 sql 

oracletest

01234567

2) 오라클

oracletest

12345678

SQL> select substr('abcde',1,2) from dual;

SU

--

ab

SQL> select substr('abcde',3,3) from dual;

SUB

---

cde

Q5. emp테이블에서 사원 이름의 첫글자가 'k'보다 크고(and) 'y'보다 적은 사원을 검색해서 그 사원의 사원번호, 이름, 업무 순으로 출력하시오. (단, 이름순으로 정렬할 것)

 select empno, initcap(ename), job

 from emp

 where substr(ename,1,1) > 'K' and substr(ename,1,1) < 'Y'

 order by ename;

  EMPNO INITCAP(EN JOB

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

  7654 Martin SALESMAN

  7934 Miller CLERK

  7788 Scott ANALYST

  7369 Smith SALES MAN

  7844 Turner SALESMAN

  7521 Ward SALESMAN

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

*SUBSRTB : 한글을 추출

---

[INSTR/INSRTB 함수]

Q6. 문자열중에서 특정문자가 어디에 위치하는 알아보기

형식) instr(대상문자열,찾고자하는 문자열)

-> 위치 번호를 알려준다.

select ename, instr(ename,'O')

from emp

ENAME INSTR(ENAME,'O')
---------- ----------------
SMITH 0
ALLEN 0
WARD 0
JONES 2
MARTIN 0
BLAKE 0
CLARK 0
SCOTT 3
KING 0
TURNER 0
ADAMS 0

2) 중첩함수 : 함수 내부에 또 다른 함수가 있는 경우

select ename, instr(ename, upper('o'))

from emp;

- instr : 외부함수

- upper : 내부함수

- 내부함수부터 작동이 되고 외부함수가 작동이 된다

- 그룹함수에서 자주 사용이 됨

Q7. [ 숫자 함수 ] 계산, 반올림, 등

1) ROUND : 반올림 

select round(4567.678), round(4567.678,0),

round(4567.678,2), round(4567.678,-2)

from dual;

ROUND(4567.678) ROUND(4567.678,0) ROUND(4567.678,2) ROUND(4567.678,-2)
---------------             -----------------             -----------------             ------------------
4568                         4568                         4567.68                     4600

* 양수의 경우 소숫점을 표현할 수

* 음수의 경우 소숫점 앞부분부터 제거

SQL> select round(4567.678,-3) from dual;

ROUND(4567.678,-3)
------------------
5000

2) trunc 함수 : 소수점을 무조건 절삭, 소수점의 자릿수에 맞게 잘라냄.

1 select trunc(4567.678), trunc(4567.678,0),
2 trunc(4567.678,2), trunc(4567.678,-2)
3* from dual

TRUNC(4567.678) TRUNC(4567.678,0) TRUNC(4567.678,2) TRUNC(4567.678,-2)
---------------             -----------------           -----------------             ------------------
4567                         4567                         4567.67                    4500

3) 기타 함수

MOD : 나누기 후, 나머지

SIGN : 값이 양수인지 음수인지 판별, 표시해준다(양수일 경우 1, 음수일 경우 -1을 표시)

select mod(10,3), sign(100), sign(-100), sign(0)

from dual;

MOD(10,3) SIGN(100) SIGN(-100) SIGN(0)
----------     ----------     ----------         ----------
1                 1                 -1                 0

Q8. [SQL plus 명령어] 보고서에 관련된 명령어 (ppt 4장)

SQL> desc dept
이름                                                 널?             유형
----------------------------------------- -------- ----------------------------
DEPTNO(=emp의 deptno)         NOT NULL     NUMBER(2)
DNAME(부서명)                                                VARCHAR2(14)
LOC(위치)                                                       VARCHAR2(13)

SQL> select * from dept;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

1) 특정필드의 폭 수정하기

column(=col) 적용필드명 format 길이설정(an) <- charater a숫자

col dname format a8;  -> 8글자 설정

EX)

SQL> col dname format a8;
SQL> /

출력)
DEPTNO DNAME LOC
---------- -------- -------------
10 ACCOUNTI NEW YORK
NG

20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIO BOSTON
NS

-> ename 필드 폭이 줄어들어서 문자가 아래로 내려옴

* 사용자로 로그인 한 동안만 설정된 것으로 exit 후 다시 로그인하면 자동해체되어 원래의 폭으로 돌아감

2) 폭 해제하기

col[unm] 해제시킬 필드명 clear

EX)

col dname clear;

SQL> /

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

Q10. 가상필드 함수

1) 가상 필드 생성

SQL> col e_name format a23;
SQL> col e_empno format a15;

* CONCAT(문자열1,문자열2) : 가상필드를 만드는, 두 문자열을 합친다

2) 

SQL> select empno,ename,concat(empno,ename) as e_name,
2 concat(ename,empno) e_empno
3 from emp
4 where deptno=&deptno;
deptno의 값을 입력하십시오: 10
구 4: where deptno=&deptno
신 4: where deptno=10

EMPNO ENAME E_NAME E_EMPNO
---------- ---------- ----------------------- ---------------
7782 CLARK 7782CLARK CLARK7782
7839 KING 7839KING KING7839
7934 MILLER 7934MILLER MILLER7934

3) 입력 메세지를 출력하지 않기(verify OFF)

구 4: where deptno=&deptno

신 4: where deptno=10

-> 오라클의 내장환경변수를 변경

* show all : 오라클에 내장된 모든 환경변수를 보여줌

show 환경변수명 : 출력 (ex. show user)

set 환경변수명 설정값  : 설정변경

4)

SQL> set verify off
SQL> /
deptno의 값을 입력하십시오: 20

EMPNO ENAME E_NAME E_EMPNO
---------- ---------- ----------------------- ---------------
7369 SMITH 7369SMITH SMITH7369
7566 JONES 7566JONES JONES7566
7788 SCOTT 7788SCOTT SCOTT7788
7876 ADAMS 7876ADAMS ADAMS7876
7902 FORD 7902FORD FORD7902

* 환경변수에서 autocommit은 절대 건드리지 말것

데이터를 잘못 변경(insert, update, delete,  DML명령어 위주)했을 경우

ROLLBACK으로 변경사항을 취소할 수 있으나 autocommit이 ON되어 있을 경우 취소가 안됨

ex) set autocommit on -> rollback을 앞으로 사용하지 못하게 된다.

Q11. [응용] emp테이블에서 사원번호는 반드시 출력하고 나머지 필드명은 동적으로 입력받아서 출력하시오.

(단, 조건식도 동적으로 입력받을 것)

-> 한문장 중심으로 입력도 가능

형식)

&필드명, '&필드명'

select &필드명

from &테이블명

where

EX)

SQL> select empno, &colunm_name
2 from emp
3 where &condition;

colunm_name의 값을 입력하십시오: ename, sal
condition의 값을 입력하십시오: ename like '%L%'

EMPNO ENAME SAL
---------- ---------- ----------
7499 ALLEN 1600
7698 BLAKE 2850
7782 CLARK 2450
7934 MILLER 1300

SQL> /
colunm_name의 값을 입력하십시오: empno, job
condition의 값을 입력하십시오: job=upper('manager')

EMPNO EMPNO JOB
---------- ---------- ---------
7566 7566 MANAGER
7698 7698 MANAGER
7782 7782 MANAGER

Q12. lpad, rpad함수, replace 함수

- lpad : 특정문자열을 왼쪽(left)으로 기준점을 설정하여 지정한 특수 기호를 출력

- replace :

1) lpad 예제: emp테이블에서 사번, 이름, 급여만 출력하되 단 부서번호는 20번인 데이터만 출력

형식) lpad(출력대상문자열,자릿수,'특수기호')

SQL> select empno, ename, lpad(ename,10,'*'),sal,rpad(sal,10,'#')
2 from emp
3 where deptno=20;

EMPNO ENAME LPAD(ENAME,10,'*') SAL RPAD(SAL,10,'#')
---------- ---------- -------------------- ---------- --------------------
7369 SMITH         *****SMITH             800     800#######
7566 JONES         *****JONES         2975     2975######
7788 SCOTT        *****SCOTT         3000     3000######
7876 ADAMS       *****ADAMS         1100     1100######
7902 FORD         ******FORD           3000     3000######

---

select : 기존의 데이터를 가공하여 화면에 출력(그룹함수까지 select), DQL

DDL(테이블 생성) 중, 무결성 제약조건 5가지 기능(not null,,,,) -> 설계(모델링)와 관련

Q13. replace 함수 

형식) replace(적용 필드명,변경전 문자열,변경후 문자열)

예제) 이름의 A 문자열은 #으로 변경

SQL> select ename, replace(ename,'A','#') as 변경후 from emp;

ENAME 변경후
---------- ----------
SMITH SMITH
ALLEN #LLEN
WARD W#RD
JONES JONES
MARTIN M#RTIN
BLAKE BL#KE
CLARK CL#RK
SCOTT SCOTT
KING KING
TURNER TURNER
ADAMS #D#MS

2) TRIM 함수 : 제거함수, 주로 공백을 제거

trim('aaa   bbbb') -> 'aaabbbb'

---

---

[날짜 함수] 

- 날짜+숫자=더한 날짜

- 날짜-숫자=뺀 날짜

- 날짜-날짜=빼진 일수

Q14. emp테이블에서 각 사원들의 근무일수(오늘날짜-입사일), 사원이름, 입사일, 근무일수 순으로 출력하시오

(단, 부서번호는 10번부서이고 근무일수가 제일 많은 순으로 정렬)

형식)근무일수 함수

months_between(날짜1,날짜2) <- 날짜1-날짜2

예제1)

select ename, hiredate, months_between(sysdate,hiredate) 근무일수

from emp

where deptno=%deptno

order by 3 desc;

ENAME HIREDATE 근무일수
---------- -------- ----------
CLARK 81/06/09 430.63294
KING 81/11/17 425.374875
MILLER 82/01/23 423.181327

-> 근무일수가 소수자리가 나옴, 반올림(round) 또는 절삭(trunc)을 해야함

예제2)

SQL> ed
file afiedt.buf(이)가 기록되었습니다

1 select ename, hiredate, round(months_between(sysdate,hiredate)) 근무일수
2 from emp
3 where deptno=&deptno
4* order by 3 desc
SQL> /
deptno의 값을 입력하십시오: 10

ENAME HIREDATE 근무일수
---------- -------- ----------
CLARK 81/06/09 431
KING 81/11/17 425
MILLER 82/01/23 423

Q15.emp 테이블에서 입사일 기준 10개월후의 날짜를 계산하라

(단, 급여가 2000이상인 사원들의 이름, 급여, 10개월후 순으로 출력하시오)

* add_months(날짜, 개월수) -> 지정날짜에 개월수를 더한 날짜

예제1)

select ename, sal, hiredate, add_months(hiredate,10) "10개월후"

from emp

where sal>=2000;

ENAME SAL HIREDATE 10개월후
---------- ---------- -------- --------
JONES 2975 81/04/02 82/02/02
BLAKE 2850 81/05/01 82/03/01
CLARK 2450 81/06/09 82/04/09
SCOTT 3000 87/04/19 88/02/19
KING 5000 81/11/17 82/09/17
FORD 3000 81/12/03 82/10/03

Q16. 지정한 요일에 해당하는 날짜 구하기

함수 : next_day(지정한 날짜,'요일') 

*요일을 지정 방법

1) 직접 입력(월요일, 수요일,,,)

2) 내부적으로 숫자로 인식

예제1) 입사한 날짜를 기준으로 다음주 금요일(직접입력)

select deptno, ename, hiredate, next_day(hiredate,'금요일')

from emp;

DEPTNO ENAME HIREDATE NEXT_DAY
--------- ---------- -------- --------
20 SMITH 80/12/17 80/12/19
30 ALLEN 81/02/20 81/02/27
30 WARD 81/02/22 81/02/27
20 JONES 81/04/02 81/04/03

-> next_day를 기준으로 hiredate의 요일을 알 수 있다.

예제2) 숫자로 입력(ed 또는 change를 활용하여 수정가능)

* 일(1), 월(2), 화(3), 수(4), 목(5), 금(6), 토(7)

1 select deptno, ename, hiredate, next_day(hiredate,7)
2* from emp
SQL> /

DEPTNO ENAME HIREDATE NEXT_DAY
---------- ---------- -------- --------
20 SMITH 80/12/17 80/12/20
30 ALLEN 81/02/20 81/02/21
30 WARD 81/02/22 81/02/28

Q17. emp테이블에서 입사한 달의 근무일수를 계산하시오(5일, 토, 일도 포함하는 조건)

이름, 입사일, 근무일수를 출력하되 근무일수가 많은 순으로 정렬하시오.

함수 last_day(지정한 날짜) : 지정한 날짜의 월의 마지막 날짜를 계산

-> 윤년과 평년으로 자동으로 계산되어 출력

예제1)

select ename, hiredate, last_day(hiredate), last_day(hiredate)-hiredate "근무일수"

from emp

where ename like '_A%'

order by 3 desc;

ENAME HIREDATE LAST_DAY 근무일수
---------- -------- -------- ----------
JAMES 81/12/03     81/12/31     28
MARTIN 81/09/28     81/09/30     2
WARD 81/02/22     81/02/28     6

---

[오라클의 변환 함수] to_number, to_date, to_char

* 변환 : 값이 변경(문자, 숫자)

         -> 경우에 따라 문자->숫자, 숫자->문자, 날짜로 변경해서 출력

1) TO_DATE : 문자를 날짜형으로 변경(거의 안씀)

2) TO_NUMBER : 문자를 숫자로 변경(거의 안씀)

3) TO_CAHR : 날짜,숫자를 문자로 변경(자주사용!)

-> 출력양식을 지정할 때 주로 사용(ex) 쇼핑몰 판매가격 5000에서 5,000으로 출력 지정)

<저장된 데이터를 원하는 양식으로 지정하여 출력하기>

Q18. emp테이블에서 20번 부서에 근무하는 사원의 급여 앞에 '$'를 표시하고 세자리마다 ','를 표시하여 출력하는 SQL구문을 사용하여, 사번, 이름, 급여, 출력서식을 출력하시오.

*함수 : to_char(적용대상필드,'출력서식')

*숫자의 자리수를 지정할 때 0 또는 9를 사용

ex) 데이터가 0일 경우 999,990원 -> 0이라고 표시 : 0원

                                   999,999원 -> 표시 안됨 : 원

예제1)

select empno, ename, sal, to_char(sal,'$999,999') as 출력서식

from emp

where deptno=20;

EMPNO ENAME SAL 출력서식
---------- ---------- ---------- ---------
7369 SMITH 800        $800
7566 JONES 2975     $2,975
7788 SCOTT 3000     $3,000
7876 ADAMS 1100     $1,100

예제2) $를 w으로 표시 -> $ 문자를 L로 변경

* L : 각 지역의 통화기호

  S : 주가(+,-)

1 select empno, ename, sal, to_char(sal,'L999,999') as 출력서식
2 from emp
3* where deptno=20
SQL> /

EMPNO ENAME SAL 출력서식
---------- ---------- ---------- ------------------
7369 SMITH 800 ￦800
7566 JONES 2975 ￦2,975
7788 SCOTT 3000 ￦3,000

예제3) 날짜와 시간을 지정한 양식(문자)로 출력하기

*날짜 문자형 변환(엑셀과 유사)

select to_char(sysdate, 'yyyy/mm/dd, hh24:mi;ss') as "날짜와 시간"

from dual;

날짜와 시간
--------------------
2017/04/28, 16:08;47

---

[일반함수] nvl

함수 nvl : null값을 어떤 특정한 값으로 변환시킬 때 사용

값이 없는 경우 null값이라고 함

형식) nvl(null값이 포함이 된 필드명,적용시킬 값) -> 실제 테이블에 들어가는 데이터가 아님

Q18. emp테이블에 comm

예제1) 

select ename, sal, comm

from emp;

ENAME SAL COMM
---------- ---------- ----------
SMITH     800       (값이 누락된것인지 알수 없음)
ALLEN 1600         300
WARD 1250         500
JONES 2975
MARTIN 1250     1400
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
TURNER 1500         0

-> 누락된 곳에 출력양식을 지정하여 문자를 표시

예제2) nvl 함수 적용

SQL> ed
file afiedt.buf(이)가 기록되었습니다

1 select ename, sal, comm, nvl(comm,0) as 커미션
2* from emp
SQL> /

ENAME SAL         COMM   커미션
---------- ---------- ---------- ----------
SMITH 800                         0
ALLEN 1600         300     300
WARD 1250         500     500
JONES 2975                      0
MARTIN 1250     1400 1400
BLAKE 2850                     0
CLARK 2450                     0
SCOTT 3000                     0
KING 5000                        0
TURNER 1500      0         0
ADAMS 1100                    0

Q20. [응용] nvl2 보너스가 있는 경우 급여(급여+보너스), 없으면 급여만 출력(조건식)

함수 nvl2(null값을 체크할 필드명,인수1,인수2)

- null이 아닌 경우 : 인수1을 선택해서 적용

- null값인 경우 : 인수2를 선택해서 적용 (삼항연산자와 유사)

예제)

1 select ename, sal, comm, nvl2(comm,sal+comm,sal) as "급여(커미션)"
2* from emp
SQL> /

ENAME SAL         COMM 급여(커미션)
---------- ---------- ---------- ------------
SMITH 800                         800
ALLEN 1600             300 1900
WARD 1250             500 1750
JONES 2975                     2975
MARTIN 1250 1       400 2650
BLAKE 2850                     2850
CLARK 2450                     2450
SCOTT 3000                    3000
KING 5000 5000
TURNER 1500           0     1500
ADAMS 1100                 1100

Q21. 부서별로 매출실적에 따라서 급여를 인상하고자 한다. 

(ANALYST 급여 10%, CLERK 급여 15%, MANAGER 급여 20%)

함수 DECODE : 각각의 조건에 따른 계산식을 적용할 때(=case ~when 구문과 동일)

형식) decode(적용시킬 필드명, 조건식, 계산값,

                                      필드명, 조건식2, 계산값,,,디폴드값

예제1)

select ename, job, sal, decode(job,'ANALYST',sal*1.1,

                                                        'CLERK',sal*1.15,

                                                        'MANAGER',sal*1.2,sal) as "급여인상액"

from emp

order by sal desc;

ENAME JOB SAL 급여인상액
---------- --------- ---------- ----------
KING PRESIDENT 5000 5000
FORD ANALYST 3000 3300
SCOTT ANALYST 3000 3300
JONES MANAGER 2975 3570
BLAKE MANAGER 2850 3420
CLARK MANAGER 2450 2940
ALLEN SALESMAN 1600 1600
TURNER SALESMAN 1500 1500
MILLER CLERK 1300 1495
WARD SALESMAN 1250 1250
MARTIN SALESMAN 1250 1250

* decode와 동일한 기능을 하는 구문

형식) case 적용대상필드명 when 조건식1 then 결과1

                                           when 조건식2 then 결과2 ,,,,

                                           els 결과 n

          end

          from emp;

예제2) case를 활용하기

select ename, job, sal, 

case when job='ANALYST' then sal*1.1

when job='CLERK' then sal*1.15

when job='MANAGER' then sal*1.2

else sal

end 급여인상액

from emp;

* 오류 : 이중인용부를 지정해 주십시오 -> " 가 잘못되었다는 뜻