[DB]데이터베이스_함수

타입 변환 함수

DUAL테이블

• DUMMY 칼럼에 'X'값을 하나 가지고 있는 테이블
• 아무 의미 없는 테이블
• 오라클에서는 FROM절이 필수이기 때문에, 테이블이 필요 없는 조회문에서 DUAL테이블을 사용함

  DESCRIBE DUAL;
  SELECT DUMMY
  FROM DUAL;

타입 변환 함수

• TO_NUMBER('문자열') : 문자열 형식의 숫자를 형식으로 변환
• TO_CHAR(데이터,'형식') : 지정된 데이터(주로 숫자나 날짜)를 형식에 맞는 문자열로 변환
• TO_DATE('문자열','형식') : 지정된 문자열을 날짜 형식으로 변환

숫자로 변환 (TO_NUMBER)

SELECT '100', TO_NUMBER('100')
FROM DUAL;
SELECT '1.5', TO_NUMBER('1.5')
FROM DUAL;

• 숫자와 '문자' 연산은 오라클에 의해서 숫자와 숫자 연산으로 수정된 뒤 처리됨

▶ '문자' -> TO_NUMBER('문자') 방식으로 자동으로 처리함

SELECT 1+'1'
FROM DUAL;

▶ '문자'와 '문자' 연산도 모두 숫자로 바꿔서 처리

SELECT '1' + '1'
FROM DUAL;

문자로 변환 (TO_CHAR)

• 숫자 -> 문자로 변환

  SELECT
  TO_CHAR(1234) -- '1234'
  ,TO_CHAR (1234,'999999') -- ' 1234'
  ,TO_CHAR (1234, '000000') -- '001234'
  ,TO_CHAR (1234,'9,999') -- '1,234'
  ,TO_CHAR (12345,'9,999') -- ###### ( 4자리로 지정하였으나 값은 5자리이기 때문에 표시할 수 없음)
  ,TO_CHAR (12345,'99,999') -- '12,345'
  ,TO_CHAR (1.4, '9') -- '1' 형식은 정수 1자리 표기(소수 이하 반올림)
  ,TO_CHAR (1.5, '9') -- '2' 형식은 정수 1자리 표기(소수 이하 반올림)
  ,TO_CHAR (0.123, '0.00') -- '0.12', 소수 이하 2자리 표기(반올림)
  ,TO_CHAR (0.129, '0.00') -- '0.13', 소수 이하 2자리 표기(반올림)
  FROM
  DUAL;

• 날짜 -> 문자로 변환
  현재 날짜와 시간
  SYSDATE 또는 SYSTIMESTAMP

  SELECT
  SYSDATE
  ,SYSTIMESTAMP
  FROM DUAL;
  //
  SELECT
  TO_CHAR(SYSDATE,'YYYY-MM-DD') -- YY/MM/DD 형식으로 표시하지만 시간 데이터도 가지고 있음
  ,TO_CHAR(SYSDATE,'HH:MI:SS')
  FROM
  DUAL;

날짜로 변환(TO_DATE)

• 어떤 날짜를 어떻게 해설해야 하는지 알려주는 함수
• 날짜를 문자(VARCHAR2)로 저장하면 성능이 훨씬 빠름
  -- '05/06/07' 날짜는 언제인가? 알려주기 전에는 모름
  -- 지정된 형식으로 해석
  -- 예시1) 'YY/MM/DD' : 05년06월07일
  -- 예시2) 'MM/DD/YY' : 07년05월06일

SELECT
TO_DATE ('05/06/07', 'YY/MM/DD')
,TO_DATE ('05/06/07', 'MM/DD/YY')
FROM
DUAL;
//
SELECT SYSDATE FROM DUAL;

▶ 사원 테이블에서 90/01/01~99/12/31 사이에 입사한 사원 조회하기

SELECT EMP_NO, NAME , DEPART, POSITION, GENDER, HIRE_DATE, SALARY
FROM EMPLOYEE
WHERE TO_DATE(HIRE_DATE, 'YY/MM/DD') BETWEEN TO_DATE('90/01/01', 'YY/MM/DD') AND TO_DATE ('31/12/99', 'DD/MM/YY');

▶ DT1과 DT2 칼럼에 현재 날짜 넣기

CREATE TABLE SAMPLE(
DT1 DATE,
DT2 TIMESTAMP,
DT3 VARCHAR2(10 BYTE)
--
INSERT INTO
SAMPLE (DT1,DT2,DT3)
VALUES(SYSDATE,SYSTIMESTAMP,TO_CHAR(SYSDATE,'YYYY/MM/DD'));
SELECT DT1,DT2,DT3 FROM SAMPLE;
SELECT DT1,DT2,DT3
FROM SAMPLE
WHERE DT1 = TO_DATE('22/08/26', 'YY/MM/DD');
SELECT DT1,DT2,DT3
FROM SAMPLE
WHERE DT1 = '22/08/26';
SELECT DT1,DT2,DT3
FROM SAMPLE
WHERE TO_DATE(DT1,'YY/MM/DD') = TO_DATE('22/08/26','YY/MM/DD');

NULL

NULL값이 연산에서 사용되면 결과는 NULL이다.

NULL 처리 함수

NVL 함수

• NVL(칼럼, 칼럼 값이 NULL 일 때 대신 사용할 값)

▶ NAME이 없으면 '아무개', KOR,ENG,MATH가 없으면 0으로 조회

SELECT
NVL(NAME, '아무개') AS STU_NAME
,NVL(KOR,0)
,NVL(ENG,0)
,NVL(MATH,0)
FROM
SAMPLE
WHERE
STU_NAME != '아무개'
ORDER BY
STU_NAME ASC;

▶ 이름과 총점을 조회하기 (이름이 없으면 '아무개', 점수가 없으면 0점 처리)

SELECT
NVL(NAME,'아무개') AS 이름
,NVL(KOR,0) + NVL(ENG,0) + NVL(MATH,0) AS 총점
FROM
SAMPLE;

NVL2 함수

• NVL2(칼럼, NULL이 아닐 때 사용할 값, NULL일 때 사용할 값)

  SELECT
  NVL2 (NAME, NAME || '님', '아무개') -- || ->문자열 연결 연산자 , CONCAT
  ,NVL2 (KOR, '응시', '결시')
  ,NVL2(ENG, '응시', '결시')
  ,NVL2(MATH,'응시', '결시')
  FROM
  SAMPLE;

집계함수

• 통계(합계, 평균, 최대, 최소, 개수 등)를 낼 때 사용
• NULL 값을 연산에서 제외
• 종류
  • 1) SUM(칼럼) : 칼럼 합계
  • 2) AVG(칼럼) : 칼럼 평균
  • 3) MAX(칼럼) : 칼럼 최대값
  • 4) MIN(칼럼) : 칼럼 최소값
  • 5) COUNT(칼럼) : 칼럼에 입력된 데이터의 개수

▶ 각 칼럼 (KOR, ENG, MATH)의 합계

SELECT
SUM(KOR)
,SUM(ENG)
,SUM(MATH)
,SUM(KOR + ENG + MATH)
,SUM(KOR) + SUM(ENG) + SUM(MATH)
FROM
SAMPLE;
-- 300으로 값은 동일하다

SUM(KOR, ENG, MATH)
→ 오류 : 인수 (ARGUMENTS)가 3개이므로 불가능함
SUM(KOR + ENG + MATH)
→ KOR + ENG + MATH 와 같은 연산 (SUM 함수를 잘못 사용한 예시) ROW단위에서는 SUM을 사용할 수 없다. SUM은 세로단위로 계산을 위함

▶ 각 칼럼 (KOR, ENG, MATH)의 평균

SELECT
AVG(KOR) -- NULL 제외한 KOR의 평균
,AVG(ENG) -- NULL 제외한 ENG의 평균
,AVG(MATH) -- NULL 제외한 MATH의 평균
FROM
SAMPLE;

NULL 값은 결시를 의미하므로 0점 처리함

SELECT
AVG(NVL(KOR,0))
,AVG(NVL(ENG,0))
,AVG(NVL(MATH,0))
FROM
SAMPLE;

▶ 각 칼럼 (KOR, ENG, MATH)의 최대값

SELECT
MAX(KOR)
,MAX(ENG)
,MAX(MATH)
FROM
SAMPLE;

▶ 각 칼럼 (KOR, ENG, MATH)의 최소값
NULL값은 결시를 의미하므로 0점 처리함

SELECT
MIN(NVL(KOR,0))
,MIN(NVL(ENG,0))
,MIN(NVL(MATH,0))
FROM
SAMPLE;

▶ 국어 시험을 응시한 학생이 몇 명인가?

SELECT
COUNT (KOR)
FROM
SAMPLE;

▶ 전체 학생은 몇 명인가? ( 전체 ROW의 개수 )
특정 칼럼을 지정하지 않고 전체 칼럼 (*)을 이용해서 전체 ROW 개수를 구함

SELECT
COUNT(*)
FROM
SAMPLE;

▶-- 성명 국어 영어 수학 합계 평균
-- 아무개 100 100 100 300 100
-- 영숙 0 100 100 200 66.67
-- 정수 100 0 100 200 66.67
-- 지영 100 100 0 200 66.67

SELECT
NVL(NAME,'아무개') AS 성명
,NVL(KOR,0) AS 국어
,NVL(ENG,0) AS 영어
,NVL(MATH,0) AS 수학
,NVL(KOR,0) + NVL(ENG,0) + NVL(MATH,0) AS 합계
,(NVL(KOR,0) + NVL(ENG,0) + NVL(MATH,0)) / 3 AS 평균
FROM
SAMPLE;

숫자함수

• 1. 제곱
     POWER(A,B) : A의 B제곱
     SELECT POWER (2,10) FROM DUAL;
• 2. 제곱근 (루트)
     SQRT(A) : 루트 A
     SELECT SQRT(25) FROM DUAL;
• 3. 절대값
     ABD(A) : A의 절대값
     SELECT ABS(5), ABS(-5) FROM DUAL;
• 4. 나머지
     MOD(A,B) : A를 B로 나눈 나머지
     SELECT MOD(7,2) FROM DUAL;
• 5. 부호 판별
     SIGN(A) : A가 양수면 1, 음수면 -1, 0이면 0을 반환
     SELECT SIGN(5), SIGN(-5), SIGN(0) FROM DUAL;
• 6. 정수로 올림
     CEIL(A) : 실수 A를 정수로 올림
     SELECT CEIL(1.1), CEIL(-1.1) FROM DUAL;
• 7. 정수로 내림
     FLOOR(A) : 실수 A를 정수로 내림
     SELECT FLOOR(1.1), FLOOR(-1.1) FROM DUAL;
• 8. 원하는 자리수로 절사
     TRUNC(A,[DIGIT]) : 실수 A를 DIGIT 자릿수로 절사, DIGIT 생략하면 정수로 절사

     SELECT
     TRUNC(1.9999) -- 1
     ,TRUNC(1.9999, 1) -- 1.9
     ,TRUNC(1.9999, 2) -- 1.99
     FROM
     DUAL;
     SELECT
     TRUNC(9999, -1) -- 9990 (원 단위 절사)
     ,TRUNC(9999, -2) -- 9900
     FROM
     DUAL;

• 9. 원하는 자리수로 반올림
     ROUND(A,[DIGIT]) : 실수 A를 DIGIT 자릿수로 반올림, DIGIT 생략하면 정수로 반올림

     SELECT
     ROUND(145.45) -- 145
     ,ROUND(145.45, 1) -- 145.5
     ,ROUND(145.45, -1) -- 150
     FROM
     DUAL;

문제발생

• 원하는 자릿수로 올림
  1) 소수1자리 : CEIL(값 ＊ 10) / 10 1
  2) 소수2자리 : CEIL(값 ＊ 100) / 100 2
  3) 소수3자리 : CEIL(값 ＊ 1000) / 1000 3
  4) 정수 : CEIL(값 ＊ 1) / 1 0
  5) 일의자리 : CEIL(값 ＊ 0.1) / 0.1 -1
  6) 십의자리 : CEIL(값 ＊ 0.01)/ 0.01 -2
  7) 백의자리 : CEIL(값 ＊ 0.001) / 0.001 -3
  일반화 : CEIL(값 ＊ POWER(10, DIGIT)) / POWER(10,DIGIT)

SELECT
CEIL(1.111 ＊ POWER(10,1)) / POWER(10,1) -- 1.2
,CEIL(1.111 ＊ POWER(10,2)) / POWER(10,2) -- 1.12
,CEIL(11111＊ POWER(10,-1)) / POWER(10,-1) -- 11120
,CEIL(11111＊ POWER(10,-2)) / POWER(10,-2) -- 11200
FROM
DUAL;

• 원하는 자릿수로 내림
  CEIL 대신 FLOOR 함수를 사용하면 됨
  

날짜함수

현재 날짜와 시간

SELECT SYSDATE, SYSTIMESTAMP FROM DUAL;

원하는 형식으로 날짜와 시간 조회

• TO_CHAR 함수 : 날짜를 문자로 변환해서 조회

  SELECT
  TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:MI:SS') -- 12시간
  ,TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') -- 24시간
  FROM
  DUAL;

단위 (년,월,일,시,분,초) 추출 함수

EXTACT (단위 FROM 날짜)

SELECT
EXTRACT(YEAR FROM SYSDATE) AS 년도
,EXTRACT(MONTH FROM SYSDATE) AS 월
,EXTRACT(DAY FROM SYSDATE) AS 일
,EXTRACT(HOUR FROM SYSTIMESTAMP) AS 시 -- UTC 기준
,EXTRACT(MINUTE FROM SYSTIMESTAMP) AS 분
,EXTRACT(SECOND FROM SYSTIMESTAMP) AS 초
,EXTRACT(TIMEZONE_HOUR FROM SYSTIMESTAMP) AS 시 -- 우선 TIMEZONE 설정이 필요
,FLOOR(EXTRACT(SECOND FROM SYSTIMESTAMP)) AS 초
FROM
DUAL;

• 단위(년,월,일,시,분,초) 추출은 TO_CHAR함수로도 가능

  SELECT
  TO_CHAR(SYSDATE,'YYYY')
  ,TO_CHAR(SYSDATE,'MM')
  ,TO_CHAR(SYSDATE,'DD')
  ,TO_CHAR(SYSDATE,'HH24')
  ,TO_CHAR(SYSDATE,'MI')
  ,TO_CHAR(SYSDATE,'SS')
  FROM
  DUAL;

날짜 연산

• 하루(1일)를 숫자 1로 처리 (12시간을 숫자 0.5로 처리)
• 특정 단위 후 날짜
  • 1년 후 : 12개월 후 계산, 별도의 함수 없음
  • 1개월 후 : ADD_MONTH 함수 사용
  • 1일 후 : +1, 별도의 함수 없음

N개월 전후 날짜

ADD_MONTHS(날짜, N)

SELECT
ADD_MONTHS(SYSDATE, -1) AS "1개월전"
,ADD_MONTHS(SYSDATE, 1) AS "1개월후"
FROM
DUAL;

경과한 개월 수

MONTHS_BETWEEN(최근날짜, 이전날짜) : 두 날짜 사이의 경과한 개월 수

SELECT
MONTHS_BETWEEN(SYSDATE, HIRE_DATE)
FROM
EMPLOYEE;

문자열 함수

대소문자 변환

• UPPER(칼럼) : 칼럼의 데이터를 모두 대문자로 변환
• LOWER(칼럼) : 칼럼의 데이터를 모두 소문자로 변환
• INITCAP(칼럼) : INITIAL CAPITAL, 첫 글자는 대문자 나머지는 소문자로 변환

  SELECT
  EMAIL
  ,UPPER(EMAIL)
  ,LOWER(EMAIL)
  ,INITCAP(EMAIL)
  FROM
  EMPLOYEES;

▶ FIRST_NAME 칼럼에서 'JAMES' 조회하기

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME
FROM EMPLOYEES
WHERE FIRST_NAME = INITCAP('JAMES');

길이

• LENGTH(칼럼) : 칼럼 데이터의 글자 수 반환
• LENGTHB(칼럼) : 칼럼 데이터의 바이트 수 반환

  SELECT
  LENGTH('HELLO'), LENGTHB('HELLO')
  ,LENGTH('안녕'), LENGTHB('안녕')
  FROM
  DUAL;

연결

• || 연산자
• CONCAT(A,B) : A와 B를 연결
  • CONCAT(A,B,C) : 불가능
  • CONCAT(CONCAT(A,B),C)) : 가능

SELECT
FIRST_NAME || ' ' || LAST_NAME AS FULL_NAME
,CONCAT(CONCAT(FIRST_NAME,' '),LAST_NAME) AS FUU_NAME
FROM
EMPLOYEES;

일부 반환

• SUBSTR(칼럼, BEGIN, LENGTH)
• 칼럼 데이터의 BEGIN 위치부터 LENGTH개 만큼 반환
• BEGIN은 인덱스가 아님 (BEGIN은 1부터 시작함)

SELECT
SUBSTR(FIRST_NAME,1,3) -- 1번째 글자부터 3글자를 가져옴
FROM
EMPLOYEES;

특정 문자열의 위치 반환

• INSTR(칼럼, 찾을문자열)
• 반환되는 위치는 인덱스가 아님(1부터 시작함)

  SELECT
  INSTR(EMAIL,'A') -- 'A'의 위치를 반환
  FROM
  EMPLOYEES;

문자열 채우기 (PADDING)

• LPAD(칼럼, 전체폭, 채울문자)
• RPAD(칼럼, 전체폭, 채울문자)

  SELECT
  LPAD(DEPARTMENT_ID,3,0) -- 왼쪽에 0을 채워서 3자리로 조회
  ,LPAD(NVL(DEPARTMENT_ID,0),3,0)
  FROM
  EMPLOYEES;
  --
  SELECT
  RPAD(SUBSTR(EMAIL,1,2),5,'＊') -- 오른쪽에 ＊를 채워서 5자리로 조회한다
  FROM
  EMPLOYEES;

공백 제거

• LTRIM(칼럼) : 칼럼 데이터의 왼쪽 공백 제거
• RTRIM(칼럼) : 칼럼 데이터의 오른쪽 공백 제거
• TRIM(칼럼) : 칼럼 데이터의 양쪽 공백 제거

  SELECT
  LENGTH(LTRIM(' HELLO'))
  ,LENGTH(RTRIM('HELLO '))
  ,LENGTH(TRIM(' HELLO '))
  FROM
  DUAL;

기타함수

순위

• RANK() OVER(ORDER BY 순위구할칼럼 ASC) : 오름차순 순위, 낮은 값이 1등, ASC는 생략 가능
• RANK() OVER(ORDER BY 순위구할칼럼 DESC) : 내림차순 순위, 높은 값이 1등
• 같은 값이면 같은 등수(동점)로 처리

▶ EMPLOYEES 테이블의 사원 정보를 연봉이 높은 순으로 조회하기
연봉순위를 함께 조회하기

SELECT
RANK() OVER(ORDER BY SALARY DESC) AS 연봉순위
,EMPLOYEE_ID
,FIRST_NAME
,LAST_NAME
,SALARY
FROM EMPLOYEES;

▶ EMPLOYEES 테이블의 사원 정보를 입사순으로 조회하기
먼저 입사한 사원이 1등

SELECT
RANK() OVER(ORDER BY HIRE_DATE) AS 입사순
,EMPLOYEE_ID
,FIRST_NAME
,LAST_NAME
,HIRE_DATE
FROM EMPLOYEES;

그룹화

• OVER(PARTITION BY 그룹화칼럼)
• 그룹화작업을 수행하므로 집계함수(그룹함수)와 함께 사용이 가능함

SELECT
DISTINCT DEPARTMENT_ID
,SUM(SALARY) OVER(PARTITION BY DEPARTMENT_ID) AS 부서별연봉합계
,FLOOR (AVG(SALARY) OVER(PARTITION BY DEPARTMENT_ID)) AS 부서별연봉평균
,MAX(SALARY) OVER(PARTITION BY DEPARTMENT_ID) AS 부서별최대연봉
,MIN(SALARY) OVER(PARTITION BY DEPARTMENT_ID) AS 부서별최저연봉
,COUNT* OVER(PARTITION BY DEPARTMENT_ID) AS 부서별사원수
FROM
EMPLOYEES
WHERE
DEPARTMENT_ID IS NOT NULL;

▶ RANK() 함수와 PARTITION BY를 함께 사용하면 그룹 내 순위 구하기 가능

SELECT
RANK() OVER(PARTITION BY DEPARTMENT_ID ORDER BY SALARY DESC) AS 부서내연봉순위
,EMPLOYEE_ID
,FIRST_NAME
,LAST_NAME
,SALARY
,DEPARTMENT_ID
FROM
EMPLOYEES
ORDER BY
DEPARTMENT_ID ASC;

분기처리 (IF)

DECODE(표현식
, 값1, 결과1
, 값2, 결과2
, 값3, 결과3
, ...)

• 표현식의 결과가 값 1이면 결과1 반환, 값2 이면 결과2 반환, ...
• 표현식의 결과와 값의 비교는 동등 비교(=)만 가능함

▶ JOIN 없이 EMPLOYEES 테이블만 이용하여 EMPLOYEE_ID, DEPARTMENT_NAME 조회하기

SELECT
EMPLOYEE_ID
,DECODE(DEPARTMENT_ID
, 10, 'Administration'
, 20, 'Marketing'
, 30, 'Purchasing'
, 40, 'Human Resources'
, 50, 'Shipping'
, 60, 'IT') AS 부서명
FROM
EMPLOYEES;

▶ EMPLOYEE_ID, PHONE_NUMBER, REGION 조회하기
-- PHONE_NUMBER REGION
-- 011 MOBILE
-- 515 EAST
-- 590 WEST
-- 603 SOUTH
-- 650 NORTH

SELECT
EMPLOYEE_ID
, PHONE_NUMBER
, DECODE(SUBSTR(PHONE_NUMBER,1,3)
,'011','MOBILE'
,'515','EAST'
,'590','WEST'
,'603', 'SOUTH'
,'650','NORTH') AS REGION
FROM
EMPLOYEES;

분기 표현식

-- CASE
-- WHEN 조건식1 THEN 결과값1
-- WHEN 조건식2 THEN 결과값2
-- ...
-- ELSE 결과값N
-- END


-- SALARY <10000 : C
-- SALARY <20000 : B
-- SALARY >= 20000 : A

SELECT
EMPLOYEE_ID
,SALARY
,CASE
WHEN SALARY <10000 THEN 'C'
WHEN SALARY <20000 THEN 'B'
ELSE 'A'
END AS 구분
FROM
EMPLOYEES;

▶ 퇴직금정산대상 : 근무개월수가 240개월 이상이면 '정산대상', 아니면 빈 문자열
EMPLOYEE_ID, HIRE_DATE(YYYY-MM-DD), 근무개월수, 퇴직금정산대상유무

SELECT
EMPLOYEE_ID AS 사원번호
,TO_CHAR(HIRE_DATE,'YYYY-MM-DD')AS 입사일
,FLOOR(MONTHS_BETWEEN(SYSDATE, HIRE_DATE)) AS 근무개월수
,CASE
WHEN MONTHS_BETWEEN(SYSDATE, HIRE_DATE) >= 240 THEN '정산대상'
ELSE ''
END AS 퇴직금정산대상유무
FROM
EMPLOYEES;