* 이 글은 제가 공부하기 위해 최대한 사실에 입각해 내용을 적으려고 하지만 일부 내용들이 정확하지 않을 수 있습니다.
혹시나 잘못된 부분이 있으면 너그럽게 이해해 주시고 피드백 부탁드려요!
1. SQL 함수의 개념
• SQL 함수
– 칼럼의 값이나 데이터 타입의 변경하는 경우
– 숫자 또는 날짜 데이터의 출력 형식 변경하는 경우
– 하나 이상의 행에 대한 계(aggregation)를 하는 경우
• SQL 함수의 유형
– 단일 행 함수 : 테이블에 저장되어 있는 개별 행을 대상으로 함수를 적용하여 하나의 결과를 반환하는 함수
– 복수 행 함수: 조건에 따라 여러 행을 그룹화하여 그룹별로 결과를 하나씩 반환하는 함수
• 단일 행 함수
– 데이터 값을 조작하는데 주로 사용
– 행별로 함수를 적용하여 하나의 결과를 반환하는 함수
• 단일 행 함수의 종류
• 단일 행 함수의 사용법
– function_name : 단일 행 함수 이름
– column : 칼럼 이름
– expression : 문자열 또는 표현식
– arg1, arg2 : 함수의 인수(상수, 변수, 칼럼 이름, 표현식)
2. 문자 함수
• 문자 함수
– 문자 데이터를 입력하여 문자나 숫자를 결과로 반환하는 함수
• 문자 함수의 종류
– 대소문자 변환 함수
– 문자조작 함수
– 문자열 길이반환 함수
[표 6.1] 대소문자 변환 함수의 종류와 사용 예
대소문자 변환 함수 INITCAT 함수
• INITCAP 함수
– 인수로 입력되는 칼럼이나 표현식의 문자열에서 첫 번째 영문자를 대문자로 변환하는 함수
• 사용법
• 사용 예
– 학생 테이블에서 "김영균" 학생의 이름, 사용자 아이디를 출력하여라.
그리고 사용자 아이디의 첫 문자를 대문자로 변환하여 출력하여라
대소문자 변환 함수 LOWER, UPPER 함수
• LOWER, UPPER 함수
– LOWER 함수 : 인수로 입력되는 칼럼이나 표현식의 문자열 전체를 소문자로 변환하는 함수
– UPPER 함수 : 문자열 전체를 대문자로 변환하는 함수
• 사용법
• 사용 예
– 학생 테이블에서 학번이 "20101"인 학생의 사용자 아이디를 소문자와 대문자로 변환하여 출력하여라.
문자열 길이 반환 함수
• 문자열 길이 반환 함수
– LENGTH 함수는 인수로 입력되는 칼럼이나 표현식의 문자열의 길이를 반환하는 함수이고,
– LENGTHB 함수는 문자열의 바이트 수를 반환하는 함수이다.
[표 6.2] 문자열 길이 반환 함수의 종류와 사용 예
문자열 길이 반환 함수 LENGTH, LENGTHB 함수
• 사용법
• 사용 예
– 부서 테이블에서 부서 이름의 길이를 문자 수와 바이트 수로 각각 출력하여라
문자조작 함수
[표 6.3] 문자 조작 함수의 종류와 사용 예
문자조작 함수 CONCAT함수
• CONCAT 함수
– 첫 번째 문자와 두 번째 문자를 연결
• 사용법
문자조작 함수 SUBSTR 함수
• SUBSTR 함수
– 문자열의 일부를 추출하는 함수
• 사용법
– 문자열에서 m번째 문자부터 n개의 문자 추출
– m이 음수이면 시작위치는 문자열의 마지막
– n을 생략하면 m부터 마지막 문자까지 추출
• 사용 예
– 학생 테이블에서 1학년 학생의 주민등록 번호에서 생년월일과 태어난 달을 추출하여 이름, 주민번호, 생년월일, 태어난 달을 출력하여라.
문자조작 함수 INSTR 함수
• INSTR 함수
– 문자열중에서 사용자가 지정한 특정 문자가 포함된 위치를 반환하는 함수
– n번째 위치부터 m번째 char의 위치를 찾음
– 만약 n이 음수이면 char의 뒤부터 찾음
• 사용법
• 사용 예
– 부서 테이블의 부서 이름 칼럼에서 "과" 글자의 위치를 출력하여라
문자조작 함수 LPAD, RPAD 함수
• LPAD, RPAD 함수
– LPAD와 RPAD 함수는 문자열이 일정한 크기가 되도록 왼쪽 또는 오른쪽에 지정한 문자를 삽입하는 함수
• 사용법
– m 폭만큼 공백을 왼쪽 또는 오른쪽에 char 문자 삽입, char가 없으면 공백 삽입
• 사용 예
– 교수테이블에서 직급 칼럼의 왼쪽에 "*" 문자를 삽입하여 10바이트로 출력하고 교수 아이다 칼럼은
오른쪽에 "+" 문자를 삽입하여 12바이트로 출력하여라
문자조작 함수 LTRIM, RTRIM 함수
• LTRIM, RTRIM 함수
– LTRIM와 RTRIM 함수는 문자열에서 특정 문자를 삭제하기 위해 사용
– 함수의 인수에서 삭제할 문자를 지정하지 않으면 문자열의 앞뒤 부분에 있는 공백 문자를 삭제
• 사용법
– 문자열의 왼쪽 또는 오른쪽에서부터 지정된 char 문자를 삭제
• 사용 예
• 사용 예
– 부서 테이블에서 부서 이름의 마지막 글자인 "과"를 삭제하여 출력하여라.
3. 숫자 함수
• 숫자 함수
– 숫자 데이터를 처리하기 위한 함수
[표 6.4] 숫자 함수의 종류와 사용 예
숫자 함수 ROUND 함수
• ROUND 함수
– 지정한 자리 이하에서 반올림한 결과 값을 반환하는 함수
• 사용법
– expr 또는 column값을 소수점 이하 n+1자리에서 반올림
• 사용 예
– 교수 테이블에서 101 학과 교수의 일급을 계산(월 근무일은 22일)하여 소수점 첫째 자리와 셋째 자리에서 반올림 한 값과 소수점 왼쪽 첫째 자리에서 반올림한 값을 출력하여라
숫자 함수 TRUNC 함수
• TRUNC 함수
– 지정한 소수점 자릿수 이하를 절삭한 결과 값을 반환하는 함수
• 사용법
– expr 또는 column 값을 소수점 이하 n+1자리에서 절삭한 값
• 사용 예
– 교수 테이블에서 101 학과 교수의 일급을 계산(월 근무일은 22일)하여 소수점 첫째 자리와 셋째 자리에서 절삭 한 값과 소수점 왼쪽 첫째 자리에서 절삭한 값을 출력하여라
숫자 함수 MOD 함수
• MOD 함수
– MOD 함수는 나누기 연산 후에 나머지를 출력하는 함수
• 사용법
– m을 n으로 나눈 나머지
• 사용 예
– 교수 테이블에서 101번 학과 교수의 급여를 보직수당으로 나눈 나머지를 계산하여 출력하여라
숫자 함수 CEIL, FLOOR 함수
• CEIL, FLOOR 함수
– CEIL 함수는 지정한 숫자보다 크거나 같은 정수 중에서 최소 값을 출력하는 함수
– FLOOR함수는 지정한 숫자보다 작거나 같은 정수 중에서 최댓값을 출력하는 함수
• 사용 예
• 사용 예
– 19.7보다 큰 정수 중에서 가장 작은 정수와 12.345보다 작은 정수 중에서 가장 큰 정수를 출력하여라
4. 날짜 함수
• 날짜함수는 날짜 데이터 타입에 사용하는 함수
• 날짜 계산
– 날짜 계산은 날짜 데이터에 더하기, 빼기의 연산을 하는 기능
[표 6.5] 날짜 타입의 연산
• 사용 예
– 교수 번호가 9908인 교수의 입사일을 기준으로 입사 30일 후와 60일 후의 날짜를 출력하여라
날짜 함수의 종류
[표 6.6] 날짜 함수의 종류
날짜 함수 SYSDATE 함수
• SYSDATE 함수
– SYSDATE 함수는 시스템에 저장된 현재 날짜를 반환하는 함수로서, 초 단위까지 반환
• 사용 예
– 시스템의 현재 날짜를 출력하여라
날짜 함수 MONTHS_BETWEEN, ADD_MONTHS
• MONTHS_BETWEEN, ADD_MONTHS
– MONTHS_BETWEEN과 ADD_MONTHS 함수는 월 단위로 날짜 연산을 하는 함수
• 사용법
– MONTHS_BETWEEN : date1과 date2 사이의 개월 수를 계산
– ADD_MONTHS : date에 개월 수를 더한 날짜 계산
• 사용 예
– 입사한 지 120개월 미만인 교수의 교수번호, 입사일, 입사일로 부터 현재일까지의 개월 수, 입사일에서 6개월 후의 날짜를 출력하여라
날짜 함수 LAST_DAY, NEXT_DAY
• LAST_DAY, NEXT_DAY
– LAST_DAY 함수는 해당 날짜가 속한 달의 마지막 날짜를 반환하는 함수
– NEXT_DAY 함수는 해당 일을 기준으로 명시된 요일의 다음 날짜를 변환하는 함수
• 사용법
– NEXT_DAY : date 날짜 이후의 첫 번째 "day" 요일의 날짜를 계산
– LAST_DAY : date 날짜가 속한 달의 마지막 날짜를 계산
• 사용 예
– 오늘이 속한 달의 마지막 날짜와 다가오는 일요일의 날짜를 출력하여라
날짜 함수 ROUND, TRUNC 함수
• ROUND, TRUNC 함수
– 날짜를 반올림 또는 절삭
• 사용법
– ROUND : 날짜를 반올림
– TRUNC : 날짜를 절삭
• 사용 예
– 시간 정보를 생략한 경우, ROUND 함수와 TRUNC 함수의 결과를 비교한 예이다.
• 사용 예
– 101번 학과 교수들의 입사일을 일, 월, 년을 기준으로 반올림하여 출력하여라.
5. 데이터 타입의 변환
• 데이터 타입의 변환
– 숫자나 날짜 타입을 문자와 함께 결합하거나 보고서 양식에 맞추기 위해 주로 사용
• 묵시적인 데이터 타입 변환
– 묵시적인 데이터 타입 변환은 정확한 연산을 위하여 오라클에서 데이터 타입을 내부적으로 변환하는 경우
[표 6.7] 묵시적 데이터 타입 변환: WHERE A=B 일 때(A는 칼럼, B는 상수)
– 문자 타입의 숫자타입으로 변환은 문자열이 숫자로 구성된 경우에만 가능
묵시적인 데이터 타입 변환
– studno 가 NUMBER 데이터 타입이고 "10102" 데이터가 문자인 경우에는 "10102"가 NUMBER
데이터 타입으로 묵시적인 변환 발생
– grade가 VACHAR2 데이터 타입이고 상수값이 숫자인 경우 grade 칼럼의 데이터 타입이 NUMBER
데이터 타입으로 묵시적인 변환 발생
– grade 칼럼에 인덱스가 생성되어 있더라도 묵시적인 변환에 의해 인덱스 사용이 불가능하여 처리
속도가 느려질 수 있음
묵시적인 데이터 타입 변환 성능영향
명시적인 데이터 타입 변화
• 명시적인 데이터 타입 변환
– 사용자가 데이터 타입 변환 함수를 이용하여 명시적으로 데이터타입을 변환
[표 6.8] 명시적 데이터 타입 변환 함수
TO_CHAR함수
• TO_CHAR 함수
– TO_CHAR 함수는 날짜나 숫자를 문자로 변환하기 위해 사용
– 날짜 출력 형식 변경
• 사용법
날짜 출력 형식의 종류
[표 6.9] 날짜 출력 형식의 종류
TO_CHAR함수 예
• 사용 예
– 학생 테이블에서 전인하 학생의 학번과 생년월일 중에서 년월만 출력하여라
• 사용 예
– 학생 테이블에서 102번 학과 학생의 이름, 학년, 생년월일을 출력하여라
시간 표현 형식의 종류
[표 6.10] 시간 표현 형식의 종류
시간 표현 형식 예
• 사용 예
– 교수 테이블에서 101번 학과 교수의 이름과 입사일을 출력하여라.
기타 날짜 표현 형식
[표 6.11] 기타 날짜 표현 형식의 종류
기타 날짜 표현 형식 예
• 사용 예
– 교수 테이블에서 101번 학과 교수들의 이름, 직급, 입사일을 출력하여라
숫자를 문자 형식으로 변환
[표 6.12] TO_CHAR 함수를 이용한 숫자 출력 형식 변환
숫자를 문자 형식으로 변환 예
• 사용 예
– 보직수당을 받는 교수들의 이름, 급여, 보직수당, 그리고 급여와 보직수당을 더한 값에 12를 곱한 결과를 연봉으로 출력하여라.
TO_NUMBER 함수
• TO_NUMBER 함수
– TO_NUMBER 함수는 숫자로 구성된 문자열을 숫자 데이터로 변환하기 위한 함수
• 사용법
• 사용 예
TO_DATE 함수
• TO_DATE 함수
– TO_DATE 함수는 숫자와 문자로 구성된 문자열을 날짜 데이터로 변환하는 함수
• 사용법
– format : 날짜 포맷의 종류는 표 6-9 참조
• 사용 예
– 교수 테이블에서 입사일이 "june 01,01"인 교수의 이름과 입사일을 출력하여라.
중첩 함수
• 사용법
– F1함수의 결과 값은 F2의 인수로, F2의 결과 값은 F3의 인수로 사용된다.
• 사용 예
– 주민등록번호에서 생년월일을 추출하여 "YY/MM/DD" 형태로 출력하여라.
6. 일반 함수
일반 함수 NVL 함수
• NVL 변환 함수 : NVL
– NVL 함수는 NULL을 0 또는 다른 값으로 변환하기 위한 함수
• 사용법
– expression1 : NULL을 포함하는 칼럼 또는 표현식
– expression2 : NULL을 대체하는 값
– 주의 : expression1과 expression2는 반드시 동일한 데이터 타입 이어야 한다.
• 사용 예
– 201번 학과 교수의 이름, 직급, 급여, 보직수당, 급여와 보직수당의 합계를 출력하여라.
단, 보직수당이 NULL인 경우에는 보직수당을 0으로 계산한다.
일반 함수 NVL 확장 함수 : NVL2 함수
• NVL2 함수
– NVL2 함수는 첫 번째 인수 값이 NULL이 아니면 두 번째 인수 값을 출력하고, 첫 번째 인수 값이
NULL이면 세 번째 인수 값을 출력하는 함수
• 사용법
– expression1 : NULL을 포함하는 칼럼 또는 표식
– expression2 : expression1이 NULL이 아닌 때 반환되는 값
– expression3 : expression1이 NULL일 때 대체되는 값
• 사용 예
– 102번 학과 교수 중에서 보직수당을 받는 사람은 급여와 보직수당을 더한 값을 급여 총액으로 출력하여라. 단, 보직수당을 받지 않는 교수는 급여만 급여 총액으로 출력하여라.
일반 함수 NVL 확장 함수 : NVL2 함수 예
• SELECT ENAME , SAL, COMM , SAL+COMM , NVL2(COMM, SAL+COMM, SAL) ,
SAL+NVL(COMM,0) FROM EMP ;
일반 함수 NVL 확장 함수 : NULLIF 함수
• NULLIF 함수
– NULLIF 함수는 두 개의 표현식을 비교하여 값이 동일하면 NULL을 반환하고, 일치하지 않으면
첫 번째 표현식의 값을 반환
• 사용법
– expression1 : expression2와 비교하는 값
NULLIF(expression1, expression2)
– 표현식 1이 표현식 2와 같으면 NULL 값을 반환하는 함수
– 같지 않으면 표현식 1 값을 반환
예) SELECT NVL(NULLIF('A', 'A'), '널값') FROM emp; => 결과: '널값'
SELECT NVL(NULLIF('A', 'B'), '널값') FROM DUAL; => 결과 : 'A'
• 사용 예
– 교수 테이블에서 이름의 바이트 수와 사용자 아이디의 바이트 수를 비교해서 같으면 NULL을 반환하고 같지 않으면 이름의 바이트 수를 반환하여라
일반 함수 NVL 확장 함수 : COALESCE 함수
• COALESCE 함수
– COALESCE 함수는 인수중에서 NULL이 아닌 첫 번째 인수를 반환하는 함수
• 사용법
– expression-1 : expression-1이 NULL이 아니면 expression-1을 반환
– expression-2 : expression-1이 NULL이고 expression-2가 NULL이 아니면 expression-2를 반환
– expression-n : expression-1부터 expression-n-1까지의 값이 NULL이고 expression-n이 NULL이 아니면 expression-n을 반환
COALESCE(expression-1, expression-2, … , expression-n)
NVL(표현식 1, NVL(표현식 2, … NVL(표현식 n-1, 표현식 n)…))와 유사
예) SELECT COALESCE(NULL, NULL, 10, 100, NULL) FROM DUAL; => 결과 :10
SELECT NVL(표현식 1, NVL(표현식 2, NVL(표현식 3, … NVL(표현식 n-1, 표현식 n)…)))
• 사용 예
– 교수 테이블에서 보직수당이 NULL이 아니면 보직수당을 출력하고, 보직수당이 NULL이고 급여가 NULL이 아니면 급여를 출력, 보직수당과 급여가 NULL이면 0을 출력하여라.
일반 함수 DECODE 함수
• DECODE 함수
– DECODE 함수는 기존 프로그래밍 언어에서 IF문이나 CASE 문으로 표현되는 복잡한 알고리즘을
하나의 SQL 명령문으로 간단하게 표현할 수 있는 유용한 기능
– DECODE 함수에서 비교 연산자는 "="만 가능
• 사용법
– 표현식 또는 칼럼의 값이 SEARCH1 값과 일치하면 RESULT1 값을 반환, SEARCH2 값과 일치하면 RESULT2 값 반환
– 일치하는 값이 없거나 NULL인 경우에는 기본값 반환
– 기본값이 없는 경우에는 NULL 반환
• DECODE 함수의 개념을 도식적으로 표현한 것이다.
• 사용 예
– 교수 테이블에서 교수의 소속 학과 번호를 학과 이름으로 변환하여 출력하여라. 학과 번호가 101이면 "컴퓨터공학과", 102이면 "멀티미디어학과", 201이면 "전자공학과", 나머지 학과 번호는 "기계공학과"(default)로 변환한다.
일반 함수 CASE 함수
• CASE 함수
– CASE 함수는 DECODE 함수의 기능을 확장한 함수
– DECODE 함수는 표현식 또는 칼럼 값이 "=" 비교를 통해 조건과 일치하는 경우에만 다른 값으로
대치할 수 있지만, CASE 함수에서는 산술 연산, 관계 연산, 논리 연산과 같은 다양한 비교가 가능
– 또한 WHEN 절에서 표현식을 다양하게 정의
– 8.1.7에서부터 지원되었으며, 9i에서 SQL, PL/SQL에서 완벽히 지원
– DECODE 함수에 비해 직관적인 문법체계와 다양한 비교 표현식 사용
• 사용법
• 사용 예
– 교수 테이블에서 소속 학과에 따라 보너스를 다르게 계산하여 출력하여라. 학과 번호별로 보너스는 다음과 같이 계산한다. 학과 번호가 101이면 보너스는 급여의 10%, 102이면 20%, 201이면 30%, 나머지 학과는 0%이다.
DECODE 사용 시 주의사항
--23.02.01
----------------------------------------------------------------------------
-- 229 페이지
-- 시스템의 현재 날짜를 출력하여라.
SELECT SYSDATE FROM DUAL;
-- 231 페이지
-- 입사한지 360개월 미만인 교수의 교수번호, 입사일, 입사일로부터 현재일까지의 개월 수,
-- 입사일에서 6개월 후의 날짜를 출력하여라.
SELECT
PROFNO, HIREDATE,
MONTHS_BETWEEN(SYSDATE, HIREDATE),
ADD_MONTHS(HIREDATE, 6)
FROM PROFESSOR
WHERE MONTHS_BETWEEN(SYSDATE, HIREDATE) < 360;
-- 233페이지
-- 오늘이 속한 달의 마지막 날짜와 다가오는 일요일의 날짜를 출력하여라
-- KST 적용된 상태(KOREA STANDARD TIME), 다른 시간이면 한국어 안먹힘.
SELECT SYSDATE
, LAST_DAY(SYSDATE)
, NEXT_DAY(SYSDATE, '일요일')
FROM DUAL;
-- 같은 값(밑에것이 더 좋음)
SELECT SYSDATE
, LAST_DAY(SYSDATE)
, NEXT_DAY(SYSDATE, 1)
FROM DUAL;
-- 235페이지
-- 시간 정보를 생략한 경우, ROUND 함수와 TRUNC 함수의 결과를 비교한 예이다.
SELECT TO_CHAR(SYSDATE, 'YY/MM/DD HH24:MI:SS')
, TO_CHAR(TRUNC(SYSDATE), 'YY/MM/DD HH24:MI:SS')
, TO_CHAR(ROUND(SYSDATE), 'YY/MM/DD HH24:MI:SS')
FROM DUAL;
SELECT TO_CHAR(SYSDATE + 3 / 24, 'YY/MM/DD HH24:MI:SS')
, TO_CHAR(TRUNC(SYSDATE + 3 / 24), 'YY/MM/DD HH24:MI:SS')
, TO_CHAR(ROUND(SYSDATE + 3 / 24), 'YY/MM/DD HH24:MI:SS')
FROM DUAL;
-- 월기준 반올림할 때
SELECT ROUND(TO_DATE('231224', 'YYMMDD'), 'MM') FROM DUAL;
-- 236 페이지
-- 101번 학과 교수들의 입사일을 일, 월, 년을 기준으로 반올림하여 출력하여라.
SELECT TO_CHAR(HIREDATE, 'YY/MM/DD HH24:Mi:SS') A
, TO_CHAR(ROUND(HIREDATE, 'DD'), 'YY/MM/DD HH24:Mi:SS') B
, TO_CHAR(ROUND(HIREDATE, 'MM'), 'YY/MM/DD HH24:Mi:SS') C
, TO_CHAR(ROUND(HIREDATE, 'YY'), 'YY/MM/DD HH24:Mi:SS') D
FROM PROFESSOR
WHERE DEPTNO =101;'SQL > 데이터베이스' 카테고리의 다른 글
| [풀스택과정] 데이터베이스 7. 조인 (0) | 2023.02.06 |
|---|---|
| [풀스택과정] 데이터베이스 6. 그룹함수 (0) | 2023.02.03 |
| [풀스택과정] 데이터베이스 4. WHERE (0) | 2023.02.01 |
| [풀스택과정] 데이터베이스 3. 데이터 타입 (0) | 2023.01.30 |
| [풀스택과정] 데이터베이스 2. SQL 사용법 (0) | 2023.01.27 |