데이터 베이스 설계 및 실습 #3 - SQL 함수

DUAL 테이블 SYS 계정 소유의 가상 테이블 산술연산이나 날짜 연산에 쓰임.

숫자 함수 ABS – 절대 값을 구함 FLOOR – 소수점 아래를 버림 ROUND – 특정 자릿수에서 반올림 TRUNC – 특정 자릿수에서 버림 MOD – 나머지 값을 반환 POWER – n제곱근을 반환 SIGH – 부호 값을 반환

숫자 함수(1) ABS POWER SIGN 형식 : ABS(숫자) SELECT ABS(-10) FROM DUAL; SELECT POWER(2, 3) FROM DUAL; (=23) SIGN 숫자의 부호를 -1, 0, 1로 표현 형식 : SIGN(숫자) SELECT SIGN(125) FROM DUAL;

숫자 함수(2) FLOOR ROUND TRUNC MOD 형식 : FLOOR(숫자) SELECT FLOOR(35.7654) FROM DUAL; ROUND 형식 : ROUND(숫자[, 자릿수]) SELECT ROUND(35.7654) FROM DUAL; TRUNC 형식 : TRUNC(숫자[, 자릿수]) SELECT TRUNC(35.7654) FROM DUAL; MOD 형식 : MOD(숫자, 나누는 수) SELECT MOD(27, 5) FROM DUAL;

예제 1. 사원테이블(EMP)의 급여(SAL)를 10단위로 반올림 해 보자. 2. 사원 번호(EMPNO)가 홀수인 사람을 검색해보자.

문자 처리 함수 LOWER – 소문자로 변환 UPPER – 대문자로 변환 INITCAP – 이니셜만 대문자로 변환 SUBSTR/SUBSTRB – 문자 추출 LENGTH/LENGTHB – 문자 길이 반환 INSTR/INSTRB – 특정 문자의 위치 값 반환 LPAD/RPAD – 문자열과 기호를 특정 길이로 정렬하여 반환 CONCAT – 두 문자열을 연결

문자 처리 함수 TRIM – 잘라내고 남은 문자를 표시 LTRIM/RTRIM – 공백문자를 제거 CHR – ASCII 코드 값을 문자로 변환 ASCII – 문자를 ASCII 코드 값으로 변환 REPLACE – 특정 문자를 변경

대소문자 변환 함수 - UPPER/LOWER/INITCAT 형식 대문자 변환 : UPPER(문자열) 소문자 변환 : LOWER(문자열) 이니셜 변환 : INITCAT(문자열) 예제 ‘Welcome to ORACLE’ 을 세 함수로 표현해보기. 사원테이블(EMP)에서 직급(JOB)이 ‘manager’인 사원 을 검색.

문자 길이를 구하는 함수 – LENGTH/LENGTHB 예제 ‘오라클’의 길이를 두 함수를 이용하여 구해 보자.

문자를 추출하는 함수 – SUBSTR/ SUBSTRB SUBSTR – 시작 위치로 부터 n개의 글자를 추출한다. SUBSTR – 시작 위치로 부터 n 바이트의 글자를 추출한다. 시작위치가 양수이면 앞에서부터, 음수이면 뒤에서부터 시작 위치를 설 정. 형식 : SUBSTR[B](문자열, 시작 위치, 추출할 개수) 예제 ‘Welcome to Oracle’의 4번째 위치로부터 문자 3개를 추출 해 보자. ‘웰컴 투 오라클’의 3번째 위치로부터 4개의 문자를 추출해 보자. 사원테이블(EMP)의 입사일(HIREDATE) 데이터를 년,월,일로 구분 하여 출력해 보자.

특정 문자의 위치를 구하는 함수 – INSTR/INSTRB 예제 ‘WELCOME TO ORACLE’ 에서 ‘O’의 위치값을 찾아 보자. ‘웰컴 투 오라클’ 에서 ‘오’의 위치값을 찾아 보자. 위의 문장에서 6번째 글자 이후 2번째로 나오는 ‘E’를 찾아 보자. (함수의 3, 4번째 인자를 이용한다.)

특정 기호로 공백을 채우는 함수 – LPAD/RPAD 문자열을 제외한 자릿수를 특정 기호로 채운다. LPAD는 문자열길이 만큼을 남겨두고 기호를 채운 후 문자열 출력. RPAD는 문자열을 먼저 출력한 후 남은 자릿수를 기호 로 채움. 형식 : L(R)PAD(문자열, 자릿수, 기호) 예제 ‘Oracle’을 출력하고 남은 자릿수를 ‘#’으로 채워 보자. (자 릿수는 20자리)

공백문자를 삭제하는 함수 – [L,R]TRIM 문자열이 공백을 포함하고 있을 경우 공백을 삭제하고 출력한 다. TRIM, LTRIM, RTRIM이 있다. 형식 : [L,R]TRIM(문자열) TRIM의 경우 공백이 아닌 문자도 삭제할 수 있다. 형식 : TRIM(문자 FROM 문자열) 예제 ‘ Oracle ‘에서 양쪽 공백을 없애 보자. ‘aaaaaOracleaaaaa’에서 ‘Oracle’만 출력해보자.

그 외 문자 처리 함수들 CONCAT CHR ASCII REPLACE 형식 : CONCAT(문자열, 문자열)

예제 1. ‘WELCOME’, ‘ TO ’, ‘ORACLE’ 을 하나의 문장으로 만 들어 보자. 2. 아스키 코드 값 102가 어떤 문자인지 확인해 보자. 3. 문자 ‘@’ 의 아스키 코드 값을 알아 보자. 4. ‘Welcome to Oracle’에서 ‘o’를 ‘a’로 바꿔 보자.

실습1 87년도에 입사한 직원을 찾아 보자. (입사일 = HIREDATE) 이름이 E로 끝나는 사원을 검색해 보자. SUBSTR을 이용 비교연산자와 AND를 이용 BETWEEN AND를 이용 이름이 E로 끝나는 사원을 검색해 보자. LIKE 연산자와 와일드카드 이용 이름의 세 번째가 R인 사원을 검색해 보자. SUBSTR 이용 INSTR 이용

날짜 함수 SYSDATE – 시스템 날짜 반환 MONTHS_BETWEEN – 두 날짜 사이를 개월 수로 반 환 ADD_MONTHS – 특정 날짜에 개월 수를 더함 NEXT_DAY – 특정 날짜에서 가장 가까운 특정 요일 날 짜 반환 LAST_DAY – 해당 달의 마지막 날짜를 반환 ROUND – 날짜를 특정 기준으로 반올림 TRUNC – 날짜를 특정 기준으로 버림

시스템 날짜 - SYSDATE 시스템에 저장된 현재 날짜를 반환한다. SYSDATE + 숫자로 날짜 연산 가능. 예제 사원들의 현재까지 근무일수를 구해보자. (입사일 데이터 이용)

특정 기준으로 날짜를 변경 – ROUND/TRUNC SELECT ROUND(HIREDATE, ‘MONTH’) FROM DUAL; SELECT TRUNC(SYSDATE, ‘DAY’) FROM DUAL;

개월 수 관련 함수 - MONTHS_BETWEEN/ADD_MONTHS 사원들의 근무 개월 수를 구해 보자. ADD_MONTHS 형식 : ADD_MONTHS(날짜, 더할 개월 수) 사원들의 입사일에 6개월을 더해 보자.

날짜를 반환하는 함수 – NEXT_DAY NEXT_DAY NLS_LANG 입력 받은 날짜 이후로 처음 찾아오는 입력 받은 요일이 언제인 지 반환. 형식 : NEXT_DAY(날짜, ‘요일’) 요일은 한글(월, 월요일), 영문(MONDAY, MON), 숫자(월 1, 화 2…)로 입력 가능. NLS_LANG 오라클의 언어설정을 변경하는 변수 설정된 언어가 한국어일 경우 요일형식은 한글과 숫자만 입력이 가능하고, 영어일 경우 영문과 숫자만 입력이 가능하다. 설정 변경 방법 ALTER SESSION SET NLS_LANGUAGE =‘AMERICAN’

날짜를 반환하는 함수 – LAST_DAY LAST_DAY 예제 해당 날짜가 속한 달의 마지막 날짜를 반환. 돌아 오는 수요일이 몇 일인지 알아보자. 이번 달의 마지막 날은 언제인지 알아 보자.

형 변환 함수 TO_CHAR (날짜형/숫자형을 문자형으로 변환) TO_DATE (문자형을 날짜형으로 변환) TO_NUMBER (문자형을 숫자형으로 변환)

문자형 변환 날짜형 데이터 변환 숫자형 데이터 변환 형식 : TO_CHAR(날짜, ‘출력 양식’)

예제 현재 날짜를 다음의 형식으로 출력해보자 사원테이블의 급여를 통화기호와 천단위 구분 표시가 되 도록 출력해보자. 2010-03-22 2010/03/22 월요일 2010/03/22 월 2010/03/22 10:01:23 사원테이블의 급여를 통화기호와 천단위 구분 표시가 되 도록 출력해보자.

날짜형 변환 형식 : TO_DATE(문자형 날짜, ‘포맷형식’) 포맷형식은 문자형 변환(TO_CHAR)의 날짜 출력 형식 과 같다. 예제 다음 쿼리문을 에러가 나지 않도록 수정해 보자. SELECT SYSDATE – ‘2009/01/01’ FROM DUAL; SELECT * FROM EMP WHERE HIREDATE=19810220;

숫자형 변환 형식 : TO_NUMBER(문자형 숫자, ‘포맷 형식’) 포맷형식은 문자형 변환(TO_CHAR)의 숫자 출력 형식 과 같다. (통화기호는 에러 남) 예제 ‘20,000’ – ’10,000’ 의 연산 결과를 계산해 보자. ‘35,245’ 를 숫자로 바꿔서 출력해보자.

NULL값을 변환하는 함수 – NVL 형식 : NVL(NULL 값, 변환할 값) 예제 사원테이블에서 유일하게 상관(MGR)이 없는(=NULL) 사 원의 MGR 컬럼의 값을 CEO로 바꾸어 출력해 보자.

조건에 따른 선택 함수 – DECODE 형식 : DECODE (표현식, 조건1, 결과1, [조건2, 결과2 …], 기본 결과) 예제 사원의 부서 번호를 이용해서 부서의 이름을 설정해보자. SELECT ENAME, DEPTNO, DECODE (DEPTNO, 10, ‘A’, 20, ‘B’, 30, ‘C’, ‘DEFAULT’) FROM EMP;

조건에 따른 선택 함수 – CASE 형식 : CASE WHEN 조건1 THEN 결과1 [WHEN 조건2 THEN 결과2...] ELSE 결과n END 예제 사원의 부서 번호를 이용해서 부서의 이름을 설정해보자. SELECT ENAME, DEPTNO, CASE WHEN DEPTNO=10 THEN ‘A’ WHEN DEPTNO=20 THEN ‘B’ WHEN DEPTNO=30 THEN ‘C’ ELSE ‘DEFAULT’ END FROM EMP;

실습2 직급(JOB)에 따라 급여를 인상해보자. 직급(JOB)이 ‘SALESMAN’인 사원들의 근무 개월 수를 계산해보자. ANALYST – 5%, SALESMAN – 10%, MANAGER – 15%, CLERK – 20%(그 외 직급은 인상 없음) 직급(JOB)이 ‘SALESMAN’인 사원들의 근무 개월 수를 계산해보자. 실습은 소스와 결과 화면을 파일로 저장하여 제출 파일명 : 학번_이름 제출기한 : 3월 22일 밤 12시