5장 SQL 주요 함수.

Presentation on theme: "5장 SQL 주요 함수."— Presentation transcript:

1 5장 SQL 주요 함수

2 목 차 DUAL 테이블 ROUND(반올림) 함수 TRUNC(버림) 함수 MOD(나머지 ) 함수 문자 처리 함수 날짜 관련 함수
목 차 DUAL 테이블 ROUND(반올림) 함수 TRUNC(버림) 함수 MOD(나머지 ) 함수 문자 처리 함수 날짜 관련 함수 형 변환 함수 NULL 변환 함수인 NVL C언어의 else if 문과 같은 DECODE 함수 조건에 따라 다양하게 처리하는 CASE 함수

3 1. 기본 SELECT 문 SELECT ~ FROM ~ SQL문의 특징 SELECT 컬럼들
대소문자를 구분하지 않음. 여러 줄로 나누어 입력 가능. 마지막 문은 버퍼에 저장 됨. 축약할 수 없음. 문장 끝에 ;(세미콜론)으로 끝내야 함.

4 DUAL 테이블 DUAL 테이블 SYS 사용자 소유 모든 사용자가 사용 가능 DUMMY라는 단 하나의 컬럼으로 구성
이 컬럼에는 길이가 1인 문자 한 개 저장. 산술 연산이나 가상 컬럼의 값을 한번만 사용하려 할 때 사용 SQL> DESC SYS.DUAL; SQL> SELECT * FROM DUAL; SQL> SELECT sysdate FROM DUAL;

5 숫자 함수 ROUND(반올림) 함수 TRUNC(버림) 함수 소수점이하 세째자리에서 반올림해서 둘째자리까지 나타냄
SQL> SELECT ROUND(45.293, 2)   2   FROM DUAL; 소수점 0번째자리에서 반올림해서 (-)첫째자리까지 나타냄 SQL> SELECT ROUND(45.293, -1) 2 FROM DUAL; TRUNC(버림) 함수 소수점 둘째자리까지 나타내고 그 아래는 버림 SQL> SELECT TRUNC( , 2) 2 FROM DUAL;

6 MOD(나머지) 함수 급여를 100으로 나눈 나머지 조회 SQL> SELECT MOD(sal, 100)
2 FROM emp; 직원들 중 사번이 홀수인 직원 조회 SQL> SELECT * 2 FROM emp 3 WHERE MOD(empno, 2) = 1;

7 환경 변수 설정 저장 환경변수를 로그인할 때마다 적용하기 원할때
SQL> ed d:\oracle\product\10.1.0\db_1\dbs\login.sql set pagesize 50 set linesize 120

8 문자 처리 함수

9 대문자로 변환하는 UPPER 함수 문자열을 모두 대문자로 변경하여 조회
SQL> SELECT UPPER('Welcome to Oracle 10g')    2  FROM DUAL; 사원명을 대문자로 변경 조회 SQL> SELECT UPPER(ename)    2  FROM emp;

10 소문자로 변환하는 LOWER 함수 문자열을 모두 소문자로 변경하여 조회
SQL> SELECT LOWER('Welcome to Oracle 10g')    2  FROM DUAL; 사원명을 소문자로 변경 조회 SQL> SELECT LOWER(ename)    2  FROM emp;

11 이니셜만 대문자로 변환하는 INITCAP 함수
문자열의 첫글자를 대문자로 SQL> SELECT INITCAP('Welcome to Oracle 10g')    2  FROM DUAL; 사원명의 첫글자를 대문자로 SQL> SELECT INITCAP(ename)    2  FROM emp;

12 문자열의 길이를 알려주는 LENGTH 함수 문자열의 길이 조회
SQL> SELECT LENGTH('Welcome to Oracle 10g')    2  FROM DUAL; 사원명과 사원명의 길이 조회 SQL> SELECT ename, LENGTH(ename)    2  FROM emp;

13 INSTR(‘문자열’, ‘찾는 문자’, 시작위치, 반복횟수 ) -> 위치 번호 반환
SQL> SELECT INSTR('Welcome to Oracle 10g', ‘o', 3, 2)    2  FROM DUAL; 문자열에서 o를 세번째부터 두번째나오는 위치 조회 SQL> SELECT INSTR('Welcome to Oracle 10g', ‘o')    2  FROM DUAL; 문자열에서 o가 나오는 위치 조회

14 특정문자가 출현하는 위치 INSTR 함수 –cont’d
사원명에 ‘A’자가 나오는 위치 조회 SQL> SELECT ename, INSTR(ename, ‘A’)    2  FROM emp; 이름에 ‘A’가 속해 있지 않으면 0(zero) 반환

15 SUBSTR(‘문자열’, 시작 위치, 개수) -> 문자열 반환
SQL> SELECT SUBSTR('Welcome to Oracle 10g', 4, 3)    2  FROM DUAL; 문자열에서 네번째부터 세 개 조회 사번, 입사일, 입사년도 조회 SQL> SELECT empno, hiredate, SUBSTR(hiredate, 1, 2)    2  FROM emp; 입사일의 첫번째부터 두 개 조회 이름의 뒤에서 세번째부터 두 글자 조회 SQL> SELECT ename, SUBSTR(ename, -3, 2)    2  FROM emp;

16 특정문자를 왼쪽삽입하는 LPAD함수(오른쪽정렬후)
SQL> SELECT LPAD('Oracle 10g', 20, ‘#')    2  FROM DUAL; 20자리의 문자열에 오른쪽정렬하고 왼쪽부터 빈곳에 #으로 채움 LPAD(‘문자열’, 전체자릿수 , ‘채우려는 문자’) 특정 문자를 오른쪽에 삽입하는 RPAD 함수(왼쪽 정렬 후 ) SQL> SELECT RPAD('Oracle 10g', 20, ‘#')    2  FROM DUAL; 20자리의 문자열에 왼쪽정렬하고 오른쪽부터 빈곳에 #으로 채움

17 왼쪽에서 특정 문자를 삭제하는 LTRIM 함수
문자열 왼쪽의 a를 삭제 SQL> SELECT LTRIM('aaaOracle 10gaaa', ‘a') 2 FROM DUAL; 문자열 왼쪽의 공백 삭제 SQL> SELECT LTRIM(‘ Oracle 10g ‘ ) 2 FROM DUAL;

18 오른쪽에서 특정 문자를 삭제하는 RTRIM 함수
문자열 오른쪽의 x를 삭제 SQL> SELECT RTRIM(‘xxxOracle 10gxxx', ‘x') 2 FROM DUAL; 문자열 오른쪽의 공백 삭제 SQL> SELECT RTRIM(‘ Oracle 10g ‘ ) 2 FROM DUAL; 특정 문자를 삭제하는 TRIM 함수 문자열 양쪽의 ‘A’ 삭제 SQL> SELECT ename, TRIM(‘A‘ from ename ) 2 FROM emp;

19 문자 함수 예제 사원명이 Smith 인 사람 검색 SQL> SELECT * 2 FROM emp
3 WHERE UPPER(ename)=‘SMITH’; 사원명이 대소문자인지 모르므로 Upper, lower, Initcap 함수 사용 이름의 세번째 자리가 ‘R’인 사원 검색 SQL> SELECT * 2 FROM emp 3 WHERE SUBSTR(ename, 3, 1) = ‘R’; Where ename like ‘__R%’ 이름의 끝자리가 ‘N’인 사원 검색 SQL> SELECT * 2 FROM emp 3 WHERE SUBSTR(ename, -1, 1) = ‘N’; Where ename like ‘%N’

20 문자 함수 예제 87년도에 입사한 사원 검색 SQL> SELECT * 2 FROM emp
3 WHERE hiredate like ‘%87%’; Where hiredate between ‘1987/1/1’ and ‘1987/12/31’ Where substr(hiredate, 1, 2)=’87’ 직원 이름이 5글자인 직원의 이름 소문자와 입사일, 급여 검색 SQL> SELECT LOWER(ename), hiredate, sal 2 FROM emp 3 WHERE LENGTH(ename) = 5;

21 날짜 관련 함수 날짜 연산 날짜 + 숫자 (결과: 날짜) : 일 수를 날짜에 더한다.
날짜 – 숫자 (결과: 날짜) : 날짜에서 일 수를 뺀다. 날짜 – 날짜 (결과: 숫자(일 수)) : 한 날짜에서 다른 날짜를 뺀다. 날짜 + 숫자/24 (결과: 날짜) : 날짜에 시간을 더한다.

22 SYSDATE 함수 날짜 연산 오늘 날짜 조회 SQL> SELECT SYSDATE 2 FROM DUAL; 어제 날짜 조회
직원의 이름, 입사일, 근무일수 조회 SQL> SELECT ename, hiredate, SYSDATE - hiredate   2  FROM emp;

23 MONTHS_BETWEEN ( 날짜, 날짜 ) -> 숫자 ADD_MONTHS ( 날짜, 숫자 ) -> 날짜
두 날짜 사이의 개월 수 계산 함수 직원의 근무 개월수 조회 SQL> SELECT SYSDATE, hiredate, MONTHS_BETWEEN(SYSDATE, hiredate)   FROM emp; MONTHS_BETWEEN ( 날짜, 날짜 ) -> 숫자 ADD_MONTHS 함수 ADD_MONTHS ( 날짜, 숫자 ) -> 날짜 입사일에서 6개월이 지난 날짜 조회 SQL> SELECT hiredate, ADD_MONTHS(hiredate, 6)   2   FROM emp;

24 LAST_DAY 함수 NEXT_DAY 함수 NEXT_DAY(날짜, 요일) 어떤 날짜(date) 달의 마지막 날
입사일 달의 마지막 날 조회 SQL> SELECT  LAST_DAY(hiredate)   2  FROM emp; NEXT_DAY 함수 NEXT_DAY(날짜, 요일) day(요일) : Date 의 돌아오는 day 요일 일, 월, 화, 수, 목, 금, 토 1, 2, 3, 4, 5, 6, 7 입사일이후 첫번째 금요일 조회 SQL>SELECT hiredate, NEXT_DAY(hiredate, '금'), NEXT_DAY(sysdate,’수’)   2  FROM emp;

25 날짜 형식 변경 date format을 우리식으로 하기위해 국제언어시스템을 Korean으로
SQL> alter session set NLS_LANGUAGE=Korean; SQL> alter session set NLS_DATE_FORMAT=‘YYYY-MON-DD’; SQL> select * from emp; 이번달 마지막날짜와 다가오는 일요일 조회 SQL> SELECT  LAST_DAY(sysdate), NEXT_DAY(SYSDATE, 1)   2  FROM DUAL; 입사일이후 6개월이 지난후 돌아오는 토요일 조회 SQL> SELECT NEXT_DAY(ADD_MONTHS(hiredate, 6), 7)   2  FROM emp; SQL> alter session set NLS_DATE_FORMAT=‘RR/MM/DD’;

26 날짜 함수 날짜 함수 ROUND 명시한 ‘날짜 형식 모델’을 기준으로 하여 날짜를 반올림.
‘일’에서 반올림해서 ‘월’까지 표시 ‘월’에서 반올림해서 ‘년’까지 표시

27 날짜 함수 날짜 함수 (8) TRUNC 명시한 ‘날짜 형식 모델’을 기준으로 하여 날짜를 절삭.
‘일’이하 절사 ‘월’이하 절사

28 형 변환 함수 숫자를 문자로 변환 문자를 숫자로 변환 날짜를 문자로 변환 문자를 날짜로 변환

29 문자열을 날짜 형으로 변환하는 TO_DATE
2006년1월1일부터 현재까지 경과된 일수 조회 SQL> select sysdate, sysdate – to_date('2006/01/01', 'YYYY/MM/DD')    2 FROM DUAL;

30 SQL>c/round/trunc
날짜 함수 TRUNC SQL>c/round/trunc 날짜형식이 생략되면 가장 가까운 날짜로 반올림등 연산 Round문에서 사용한 질의어 복사해서 SQL plus로 해결

31 날짜나 숫자 형을 문자로 변환하는 TO_CHAR
TO_CHAR(number | date, 'format') Format : 출력 형식으로 ‘’로 나타냄

32 날짜나 숫자 형을 문자로 변환하는 TO_CHAR
오늘날짜를 시분초까지 출력하라 SQL> SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD, HH24:MI:SS')     FROM DUAL; 현재 시간에 3시간을 더한 시각 조회 SQL> SELECT TO_CHAR(SYSDATE +3/24, 'yyyy“년” mm“월” dd”일“ hh”시” mi”분“’)   FROM DUAL;

33 숫자 출력 형식

34 숫자 출력 형식 sal을 6자리로 바꾸고 앞에 $를 붙여 출력하는 예제
SQL> SELECT ename, TO_CHAR(sal, '$999,999')   2  FROM emp; sal을 6자리로 바꾸고 앞에 \(원화)를 붙여 출력하는 예제 SQL> SELECT ename, TO_CHAR(sal, 'L999,999')   2   FROM emp;

35 NULL 변환 함수인 NVL NVL (변수/식, 원하는값): ‘식’이 null 이면 ‘원하는 값’으로 변환
NVL( A, B) : A가 null이면 B값으로 바꿈. 단 A, B는 같은 데이타형이어야 함 이름, 급여, 커미션, 커미션을 합한 년봉을 조회 SQL> SELECT ename, sal, comm, sal*12 +NVL(comm, 0)   2   FROM emp; 커미션이 널값이면 0으로 바꿈

36 Mgr은 숫자형이므로 먼저 문자형으로 바꾸고
NULL 변환 함수인 NVL –cont’d SQL> DESC emp 상사(mgr)가 없으면 ‘CEO’로 변경하여 출력 SQL> SELECT ename, NVL( mgr, ‘CEO’)   2   FROM emp; SQL> SELECT ename, NVL( TO_CHAR(mgr, ‘9999’), ‘CEO’) Mgr은 숫자형이므로 먼저 문자형으로 바꾸고 null 이면 ‘CEO’로 변경

37 C언어의 else if 문과 같은 DECODE 함수

38 C언어의 else if 문과 같은 DECODE 함수
부서번호가 10 이면 ‘accounting’, 20 이면 ‘research’, 30 이면 ‘sales’, 40이면 ‘operations’의 부서명을 DNAME으로 하여 이름, 부서번호, 부서명 조회 SQL> SELECT ename, deptno,   2            DECODE(deptno, 10, ‘ACCOUNTING’,   3                              20, ‘RESEARCH’,   4                              30, ‘SALES’,   5                              40, ‘OPERATIONS’) dname   6   FROM emp;

39 C언어의 else if 문과 같은 DECODE 함수
직급이 ‘MANAGER’이면 15%, ‘CLERK’이면 5%인상하여 ‘Up Sal’로 하여 이름, 직급, ‘Up Sal ‘조회 SQL> SELECT ename, job,   2         DECODE(job, ‘CLERK’, sal*1.05,   3                      ‘MANAGER’, sal*1.15,   4                          sal) as “인상급여”   5      FROM emp;

40 조건에 따라 다른 처리가 가능한 CASE 함수 CASE 표현식 WHEN 조건1 THEN 결과1 WHEN 조건2 THEN 결과2
               ELSE 결과N END

41 조건에 따라 다른 처리가 가능한 CASE 함수 각 부서번호에 해당되는 부서명을 이름, 부서번호와 함께 조회
SQL> SELECT ename, deptno,   2       CASE WHEN deptno = 10 THEN ‘ACCOUNTING’   3                   WHEN deptno = 20 THEN ‘RESEARCH’   4                   WHEN deptno = 30 THEN  ‘SALES’   5                   WHEN deptno = 40 THEN  ‘OPERATIONS’   6        END  dname   7   FROM emp;

42 조건에 따라 다른 처리가 가능한 CASE 함수 직급이 ‘CLERK’이면 20%, ‘ANALYST’이면 15%, ‘MANAGER’이면 10% 인상하여 이름, 직급, 급여, 인상된 급여를 조회하시오 SQL> SELECT ename, job, sal,   2         CASE WHEN job= ‘CLERK’ THEN sal*1.20   3                   WHEN job= ‘ANALYST’ THEN sal*1.15   4                   WHEN job=‘MANAGER’ THEN sal*1.10 ELSE sal 6       END AS 인상된급여   7      FROM emp;