Day14_DUAL TABLE, 오라클 DB 기본 함수

2023.08.31

 

Day14 세 줄 요약

- CREATE TABLE 없이 바로 조회하고 싶을 땐 DUAL TABLE 이용

- CONCAT 함수를 사용해 여러 조건식 또는 문자열을 함께 출력

- 문장 실행 시 DLM의 순서가 중요

 

---

 

DUAL TABLE

 

DUAL TABLE (가상의 TABLE)

CREATE TABLE 없이 가상의 새로운 TABLE을 만들어서 바로 조회해보고 싶을 때 사용

SELECT 'AbCdEf' FROM DUAL;

---

 

오라클 DB 기본 함수 (문자 관련)

 

대/소문자 바꾸기

1) UPPER : 모두 대문자로
2) LOWER : 모두 소문자로
3) INITCAP : 첫 글자만 대문자, 나머지는 소문자

SELECT 'AbCdEf' AS STR,
UPPER('AbCdEf') AS UPPER,
LOWER('AbCdEf') AS LOWER,
INITCAP('AbCdEf') AS INITCAP
FROM DUAL;

 

 

문자열 길이 구하기

LENGTH : DATA의 길이

LENGTHB : DATA의 크기 (한글 : 3byte / 영문자, 숫자, 특무문자 : 1byte)

          - 영어 외에는 국가코드 1byte가 포함되어있기 때문에 3byte

-- EMP 테이블에서 이름이 5글자 이상인 사원의 [이름], [이름길이] 조회
SELECT ENAME AS 이름, LENGTH(ENAME) AS 이름길이 FROM EMP WHERE LENGTH(ENAME) >= 5;

 

 

문자열 추출

SUBSTR(데이터, 시작위치, 추출길이(생략 시 끝까지 추출))

-- 20번 부서 사원들의 입사 월 추출
SELECT ENAME, SUBSTR(HIREDATE, 4, 2) AS 입사월 FROM EMP WHERE DEPTNO = 20;

 

 

특정 문자 위치 반환

INSTR(데이터, 찾는 문자, 시작위치(생략 시 기본값 1), 중복된 문자가 있을 경우 몇 번째인지 선택(생략 시 기본값 1))

-- 이름에 'L'이 몇 번째 위치에 들어있는지 조회
SELECT ENAME, INSTR(ENAME, 'L', 1, 1) AS "L 자릿수 반환" FROM EMP;

 

 

특정 문자를 다른 문자로 변환

REPLACE(데이터, 'a') : a 문자 삭제

REPLACE(데이터, 'a', 'b') : a -> b로 변환

-- 전화번호 안에 '-' 제거
SELECT '010-1234-5678' AS 전화번호1, REPLACE('010-1234-5678', '-') AS 전화번호2 FROM DUAL;

 

 

문자열 (혹은 함수) 합치기

CONCAT (or '||' )

-- '00번 부서 사원 : XXX' 와 같이 출력
SELECT DEPTNO || '번 부서 사원  : ' || ENAME AS "CONCAT" FROM EMP ORDER BY DEPTNO;

-- HIREDATE에서 '월'을 '*'로 표시 (실행결과는 같음)
SELECT ENAME, HIREDATE AS NOMARK, SUBSTR(HIREDATE, 1, 3) || '**' || SUBSTR(HIREDATE, 6, 3) AS MARKED FROM EMP;
SELECT ENAME, HIREDATE AS NOMARK, SUBSTR(HIREDATE, 1, INSTR(HIREDATE, '/', 1, 1)) || '**' || SUBSTR(HIREDATE, INSTR(HIREDATE, '/', 1, 2)) AS MARKED FROM EMP;

 

 

빈 공간 추가 후 특정 문자로 채우기

LPAD( ) / RPAD( )

LPAD(데이터, 데이터자릿수, 채울문자(생략 시 기본값 공백 '  '))

SELECT 'ICIA' AS DATA,
LPAD('ICIA', 7) AS LPAD기본형, RPAD('ICIA', 7) AS RPAD기본형,
LPAD('ICIA', 7, '#') AS LPAD#, RPAD('ICIA', 7, '#') AS RPAD# FROM DUAL;

 

 

공백제거

TRIM() : 공백제거, 문자와 문자 사이의 공백은 '문자'로 인식

LTRIM() / RTRIM() : 왼쪽 / 오른쪽 공백 제거

SELECT
TRIM('    인천일보    아카데미    ') AS "TRIM",
LTRIM('    인천일보    아카데미    ') AS "LTRIM",
RTRIM('    인천일보    아카데미    ') AS "RTRIN"
FROM DUAL;

 

---

 

오라클 DB 기본 함수 (MATHEMATICAL)

 

교집합 INTERSECT

-- 이름에 'L'이 포함되어 있으면서 직무가 'SALESMAN'인 사원조회
SELECT * FROM EMP WHERE INSTR(ENAME, 'L') != 0
INTERSECT
SELECT * FROM EMP WHERE JOB LIKE 'SALE%';

 

 

합집합 UNION

-- 부서번호가 20번인 사원들과 직무가 'MANAGER'인 사원 함께 조회
SELECT * FROM EMP WHERE DEPTNO = 20
UNION
SELECT * FROM EMP WHERE JOB LIKE 'MANAGE%';

 

 

차집합 MINUS

-- 30번 부서에서 직무가 'SALESMAN'인 사원 제외
SELECT * FROM EMP WHERE DEPTNO = 30
MINUS
SELECT * FROM EMP WHERE JOB LIKE 'SALES%';

 

 

절대값 ABS( )

SELECT ABS(5) AS "절대값 5", ABS(-10) AS "절대값 -10" FROM DUAL;

 

 

반올림 ROUND( )

SELECT
ROUND(1234.5678) AS "ROUND", 
ROUND(1234.5678, 0) AS "ROUND_0",
ROUND(1234.5678, 1) AS "ROUND_1",
ROUND(1234.5678, 2) AS "ROUND_2",
ROUND(1234.5678, -1) AS "ROUND_-1",
ROUND(1234.5678, -2) AS "ROUND_-2"
FROM DUAL;

 

 

버림 TRUNC( )

SELECT
TRUNC(1234.5678) AS "TRUNC", 
TRUNC(1234.5678, 0) AS "TRUNC_0",
TRUNC(1234.5678, 1) AS "TRUNC_1",
TRUNC(1234.5678, 2) AS "TRUNC_2",
TRUNC(1234.5678, -1) AS "TRUNC_-1",
TRUNC(1234.5678, -2) AS "TRUNC_-2"
FROM DUAL;

 

 

가까운 정수 찾기 CEIL( ) / FLOOR( )

CEIL : 큰 쪽으로 변환 / FLOOR : 작은 쪽으로 변환

SELECT 
CEIL(3.14), FLOOR(3.14), CEIL(-3.14), FLOOR(-3.14), CEIL(-3.6), FLOOR(-3.6), ROUND(-3.6)
FROM DUAL;

 

 

나머지 MOD( )

MOD(A, B) = A % B

SELECT '15/6 = ' AS 식, TRUNC(15 / 6) || '...' || MOD(15, 6) AS "몫...나머지" FROM DUAL;

 

---

 

 

DLM 구성 순서

 

SELECT 문으로 조회 시

SELECT + COLUMN_NAME 혹은 함수 + FROM + TABLE_NAME + WHERE + 조건식 + ORDER BY + COLUMN_NAME

-- 직원번호가 7839인 사원이 부하직원을 호출한다.
-- ex) 00번 부서의 XXX님! 회의실로 오세요!
-- 조건1) 이름을 부를 땐 첫 글자 대문자로, 나머지는 소문자로
-- 조건2) 부서번호를 낮은 순서대로 정렬한 후 호출
SELECT DEPTNO || '번 부서의 ' || INITCAP(ENAME) ||'님! 회의실로 와주세요!' AS 호출 FROM EMP WHERE MGR = 7839 ORDER BY DEPTNO;