내장형 함수(BUILT-IN FUNCTION)
- 모든 DB는 SQL에서 사용할 수 있는 내장형 함수가 존재
- DBMS에 따라 약간의 차이가 존재하지만 거의 비슷함
▷ DUAL 테이블
- ORACLE에서 자동으로 생성되는 테이블
- 사용자가 임시로 사용할 수 있는 테이블로 내장형 함수를 실행할 때도 사용 가능
- ORACLE의 모든 사용자가 사용 가능
▷ 내장형 함수의 종류
| 문자열 함수 |
내용 |
| ASCII(문자) |
문자 OR 숫자를 ASCII 코드값으로 변환 |
| CHAR(ASCII 코드값) |
ASCII 코드값을 문자로 변환 |
| SUBSTR(문자열, 1, 2) |
문자열에서 첫번째 위치 부터 2개를 자른다 |
| CONCAT(문자열, 문자열2) |
문자열과 문자열2의 결합 |
| LOWER(문자열) |
소문자로 변환 |
| UPPER(문자열) |
대문자로 변환 |
| LENGTH 또는 LEN(문자열) |
문자열의 길이를 알려준다 |
| LTRIM(문자열, 옵션) |
왼쪽 지정된 문자 삭제, 옵션이 없으면 공백 삭제 |
| RTRIM(문자열, 옵션) |
오른쪽 지정된 문자 삭제, 옵션이 없으면 공백 삭제 |
| TRIM(문자열, 옵션) |
왼쪽과 오른쪽 지정된 문자 삭제, 옵션이 없으면 공백 삭제 |
| 날짜형 함수 |
내용 |
| SYSDATE |
오늘 날짜를 나타냄 |
| EXTRACT('YEAR' OR 'MONTH' OR 'DAY' FROM DUAL) |
날짜에서 지정한 년, 월, 일 조회 |
| 숫자형 함수 |
내용 |
| ABS(숫자) |
절대값 반환 |
| SIGN(숫자) |
양수, 음수, 0 구분 |
| MOD(숫자1, 숫자2) |
숫자1을 숫자2로 나누어 나머지 계산 (%를 사용과 같다) |
| CEIL / CEILING(숫자) |
크거나 같은 최소의 정수 반환 (올림) |
| FLOOR(숫자) |
작거나 같은 최대의 정수 반환 (버림) |
| ROUND(숫자, M) |
소수점 M자리에서 반올림, M의 기본값은 0 |
| TRUNC(숫자, M) |
소수점 M자리에서 절삭, M의 기본값은 0 |
숫자형 함수 중에서 숫자보다 작거나 같은 최대의 정수를 반환하는 것은? (답 : 2)
① CEIL()
② FLOOR()
③ TRIM()
④ SIGN()
▷ DECODE
- IF문을 구현할 수 잇다.
- 특정 조건이 참이면 A 거짓이면 B
DECODE(EMPNO, 1000, '사장', '직원')
| DECODE(EMPNO, 1000, '사장', '직원') |
| 사장 |
| 직원 |
| 직원 |
| 직원 |
▷ CASE문
- IF ~ THEN ~ ELSE - END의 형식으로 사용
SELECT CASE
WHEN EMPNO = 1000 THEN '사장'
WHEN EMPNO = 1001 THEN '부사장'
ELSE '직원'
END
FROM EMP;
| CASE WHEN EMPNO = 1000... |
| 사장 |
| 부사장 |
| 직원 |
| 직원 |
- WHEN의 조건에 각각 맞으면 THEN을 추출하게 되고 각 조건에 맞지 않을 경우 ELSE를 추출한다
성별이 남자이면 M, 여자이면 F를 출력하는 것은? (성별 칼럼은 남자와 여자만 있다) (답 : 1)
① DECODE(성별, '남자', 'M', 'F')
② DECODE('남자', '성별', 'M', NULL)
③ DECODE('여자', '성별', 'F', NULL)
④ DECODE('성별', '여자', 'F', NULL)
▷ ROWNUM
- ORACLE 데이터베이스의 SELECT문 결과에 대해 논리적인 일련번호를 부여
- 조회되는 행 수를 제한할 때 많이 사용
- 논리적 순번이다.
- 여러개의 행을 조회시 인라인 뷰를 사용해야한다.
SELECT *
FROM EMP
WHERE ROWNUM <= 1 -- 한개의 행을 조회
SELECT *
FROM (
SELECT ROWNUM AS LIST, ENAME -- 인라인뷰 사용
FROM EMP)
WHERE LIST <= 3;
※ MS-SQL
SELECT TOP 10 * FROM EMP
※ MYSQL
SELECT * FROM EMP LIMIT 10;
▷ ROWID
- ORACLE 내에서 데이터를 구분할 수 있는 유일한 값
- 데이터가 어떤 파일, 어느 블록에 저장되어 있는지 알 수 있다.
| 구조 |
길이 |
내용 |
| 오브젝트 번호 |
1 ~ 6 |
오브젝트 별 유일 값을 가지고 있으며, 해당 오브젝트가 속해 있는 값 |
| 상대 파일 번호 |
7 ~ 9 |
테이블 스페이스에 속해 있는 데이터 파일에 대한 상대 파일 번호 |
| 블록 번호 |
10 ~ 15 |
파일 내부에서 어느 블록에 데이터가 있는지 알려줌 |
| 데이터 번호 |
16 ~ 18 |
데이터 블록에 데이터가 저장되어 있는 순서 |
ROWID에 대한 설명으로 올바르지 않은 것은? (답 : 4)
① ROWID는 데이터베이스에 저장되어 있는 데이터를 구분할 수 있는 유일한 값
② ROWID는 데이터가 어떤 데이터 파일의 어떤 블록에 저장되어 있는지 확인할 수 있다
③ ROWID의 데이터 번호는 데이터 블록에 데이터가 저장되어 있는 순서를 의미
④ 테이블을 생성하고 데이터를 입력할 때 ROWID를 입력해야 사용할 수 있다
▷ WITH문
- 서브쿼리를 사용해서 임시테이블이나 뷰처럼 사용할 수 있는 구문
- 서브쿼리 구문에 별칭을 지정 가능
- 옵티마이저는 SQL을 인라인 뷰나 임시 테이블로 판단
WITH V_EMP AS
(SELECT * FROM EMP
UNION ALL
SELECT * FROM EMP)
SELECT * FROM V_EMP WHERE EMPNO = 1001;
EMP 테이블에서 WITH구문을 사용해서 부서번호가 20번이고 급여가 2000 이상인 것의 임시 테이블을 만들고 조회 하시오
WITH D_EMP AS
(SELECT * FROM EMP WHERE DEPTNO = 20 AND SAL >= 2000;)
SELECT * FROM D_EMP;