학원 day30. 오라클 내장함수, 데이터 타입

오라클 내장함수

- SQL 작성에 사용할 수 있는 유용한 기능이 제공되는 함수

- DBMS 제품마다 조금씩 차이가 있다.

 

단일행함수

- 조회된 행마다 하나의 결과를 반환한다.

- 단일행 함수는 중첩해서 사용할 수 있다.

- 종류

문자함수 : 문자를 입력값으로 받아서 계산한 결과를 반환

숫자함수 : 숫자를 입력값으로 받아서 계산한 결과를 반환

날짜함수 : Date 타입의 값에 대한 처리를 수행

변환함수 : 데이터의 타입을 변환하는 처리를 수행

기타함수 : nvl, case, decode 등의 함수가 있다.

 

다중행 함수(그룹함수)

- 조회된 행을 그룹으로 묶고 행 그룹당 하나의 결과를 반환한다. 

- GROUP BY 절을 사용해서 조회된 행을 그룹으로 묶고 다중행 함수로 각 그룹당 하나의 결과(합계, 평균, 분산, 표준편차, 최고값, 최저값) 등을 계산해 낸다. (주로 집계시 사용) 

오라클 내장함수 - 단일행 함수와 다중행함수

문자 함수

lower(column or exp)

소문자로 변환한다.

upper(column or exp)

대문자로 변환한다.

substr(column or exp, beginIndex)

텍스트값을 시작위치부터 끝까지 잘라낸다. (시작이 0부터가 아닌 1부터라는 점을 유의하자!)

substr(column or exp, beginIndex, length)

텍스트의 값을 시작위치부터 지정된 길이만큼 잘라낸다.

concat(column or exp, column or exp)

텍스트 2개를 연결한다.

length(column or exp)

텍스트의 길이를 반환한다.

instr(column or exp, 'string')

텍스트에서 지정된 문자열이 처음으로 등장하는 위치를 반환한다.

lpad(column or exp, length, 'string') 

텍스트의 길이가 지정된 길이보다 부족하면 지정된 문자를 왼쪽에 채운다. 

rpad(column or exp, length, 'string')

텍스트의 길이가 지정된 길이보다 부족하면 지정된 문자를 오를쪽에 채운다.

trim(column or exp)

텍스트의 불필요한 공백을 제거한다.

replace(column or exp, 'search_string', 'replacement_string')

텍스트에서 검색된 문자를 대체할 문자로 바꾼다.

 

< CONCAT과 ||의 사용 >

 

CONCAT(컬럼 혹은 표현식, 컬럼 혹은 표현식) : 두 값을 합쳐서 새로운 텍스트를 반환한다. 두개의 값 초과는 안됨
SELECT CONCAT(FIRST_NAME, LAST_NAME)
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 60;

||는 텍스트를 연결한다. (여러개 연결 가능)
SELECT FIRST_NAME || ' ' || LAST_NAME
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 60;

 

숫자함수

round(column or exp)

숫자를 소숫점 첫번째 자리에서 반올림한다.

round(column or exp, n)

숫자를 지정된 자릿수로 반올림한다.

trunc(column or exp)

숫자의 소수점부분을 버린다.

trunc(column or exp, n)

숫자를 지정된 자릿수만큼 남기고 버린다.

ceil(column or exp)

숫자보다 크거가 같은 정수중에서 가장 작은 정수를 반환한다.

floor(column or exp)

숫자보다 작거나 같은 정수중에서 가장 큰 정수를 반환한다.

mod(m, n)

m을 n으로 나눈 나머지값을 반환한다.

--------------------------------------------------------------------------------
-- 오라클 내장함수 : 숫자 함수
--------------------------------------------------------------------------------
-- ROUND(컬럼 혹은 표현식) : 소숫점 1번째 자리에서 반올림한다.
-- ROUND(컬럼 혹은 표현식, 자리수) : 지정된 자리수로 반올림한다. (음수도 가능하다)
SELECT ROUND(123.45), ROUND(1234.5),   -- 소숫점 1번째자리에서 반올림한다.
       ROUND(123.4567, 3),   -- 소숫점 3번째자리까지 반올림한다.
       ROUND(123.4567, 0),   -- 1의 자리로 반올림한다.
       ROUND(123.4657, -1),  -- 10의 자리로 반올림한다.
       ROUND(123.4567, -2)   -- 100의 자리로 반올림한다.
FROM DUAL; 
-- DUAL은 1행 1열의 테이블로, 테스트할 때 주로 쓰는 더미테이블이다.

-- TRUNC(컬럼 혹은 표현식) : 소수점부분을 전부 버린다.
-- TRUNC(컬럼 혹은 표현식, 자리수) : 지정된 자리수만큼 남기고 전부 버린다.
SELECT TRUNC(1234.1), TRUNC(1234.5), TRUNC(1234.9),  -- 소숫점 이하를 전부 버린다.
       TRUNC(1234.1, 0),       -- 1의 자리 이하를 전부 버린다.
       TRUNC(1234.1, -2),      -- 100의 자리 이하를 전부 버린다. (0으로 바꾼다.)
       TRUNC(1234.1, -3)       -- 1000의 자리 이하를 전부 버린다. (0으로 바꾼다.)
FROM DUAL;

SELECT FIRST_NAME, SALARY, COMMISSION_PCT, SALARY*COMMISSION_PCT, TRUNC(SALARY*COMMISSION_PCT, -2)
FROM EMPLOYEES
WHERE COMMISSION_PCT IS NOT NULL;

-- MOD(컬럼 혹은 표현식, 숫자) : 나머지 값을 반환한다.
SELECT 5/2,   -- 나누기 : 2.5       
MOD(5, 2)     -- 나머지 : 1     
FROM DUAL;

 

날짜함수

sysdate

시스템의 현재날짜와 시간을 반환한다.

날짜연산

-- 날짜 + 정수 : 지정된 날짜에서 지정된 정수만큼 경과된 날짜를 반환한다.
-- 날짜 - 정수 : 지정된 날짜에서 지정된 정수만큼 이전 날짜를 반환한다.
-- 날짜 + 정수/24 : 지정된 날짜에서 지정된 정수시간만큼 경과된 날짜를 반환한다.
-- 날짜 - 정수/24 : 지정된 날짜에서 지정된 정수시간만큼 이전 날짜를 반환한다.
-- 날짜 - 날짜 : 두 날짜 사이의 경과될 일 수를 반환한다.

SELECT SYSDATE + 3,     -- 지금을 기준으로 3일 후 
       SYSDATE - 3,     -- 지금을 기준으로 3일 전
       SYSDATE + 3/24,  -- 지금을 기준으로 3시간 후 
       SYSDATE - 3/24   -- 지금을 기준으로 3시간 전
FROM DUAL;

round(날짜)

날짜를 반올림한다. 정오가 지나면 하루가 증가된다.

trunc(날짜)

지정된 날짜에서 시간정보를 전부 0으로 바꾼 값을 반환한다.

months_between(날짜, 날짜)

두 날짜사이의 월수를 반환한다.

add_months(날짜, 숫자)

날짜에서 숫자만큼 개월수를 증감시킨 값을 반환한다.

 

오라클의 데이터 타입

VARCHAR2(size)

- 가변길이 문자 데이터, 최대값 : 4000 (한글의 경우 1300정도까지)

- size범위 내에서 실제 데이터의 크기만큼만 저장공간을 사용한다.

- 예) 이름, 주소, 과목명, 상품명, 뉴스제목

CHAR(size)

- 고정길이 문자 데이터, 최대값: 2000

- size 크기만큼의 저장공간을 무조건 사용한다.

- 예) 주민번호, 학번, 수강과목코드

 

LONG

- 가변길이 대용량 문자데이터, 최대값: 2GB

- 현재는 잘 사용되지 않음

- 테이블당 하나밖에 사용할 수 없다.

- 제약조건을 정의할 수 없다.

- order by나 group by에 포함시킬 수 없다.

 

CLOB (시랍)

- 가변길이 대용량 문자 데이터(Character Large OBject), 최대값: 4GB

- 예) 블로그의 본문, 신문기사, 논문

 

NUMBER(p, s)

- 가변길이 숫자 데이터

- 예) 입사일, 가입일, 주문날짜, 이체날짜, 신청날짜 ...

 

DATE

- 날짜 및 시간데이터

- 예) 입사일, 가입일, 주문날짜, 이체날짜, 신청날짜 ...

 

TIMESTAMP

- 날짜 및 시간 데이터, 소수점 이하 초까지 포함한다.

 

BLOB

- 가변길이 대용량 이진 데이터(Binary Large Object), 최대값: 4GB

- 예) 그림, 영상, 게임파일

 

ROWID

- 테이블에서 행의 고유주소를 나타내는 64진수 숫자데이터

 

오브젝트 번호

- 해당 데이터가 속해있는 데이터베이스 객체(테이블)의 번호

- 데이터베이스 객체마다 고유하다.

 

파일번호

- 해당 데이터가 위치하고 있는 테이블스페이스 파일번호

 

블록번호

- 파일내부의 블록번호

 

데이터번호

- 데이터가 저장되어있는 데이터 디렉토리 슬롯 번호

 

데이터 타입 변환

• 묵시적 타입 변환 : 쿼리 실행과정에서 자동으로 데이터타입이 변환됨
  • 문자를 숫자로 (문자가 숫자로만 구성되어 있을 때)
  • 문자를 날짜로(문자가 날짜표기 형식의 문자일 때)
• 명시적 타입 변환
  • to_char(숫자, '포맷형식')
    • 숫자를 지정된 포맷형식의 문자로 변환한다.
  • to_number('특정패턴으로 구성된 숫자형식의 문자', '패턴')
    • ,가 포함된 문자열을 숫자로 변환한다.
    • 패턴 문자
      • 9 숫자를 나타낸다
      • 0 숫자를 나타낸다.
      • $ 달러 기호를 나타낸다.
      • . 소숫점을 나타낸다.
      • , 자릿수를 나타낸다.

      -- 가격에 3자리마다 ,를 추가한 문자열을 반환한다.
      select book_no, book_title, to_number(book_price, '9,999,999') 
      from sample_books;

  • to_char(날짜, '포맷형식')
    • 날짜를 지정된 포맷형식의 문자열로 변환한다.

    -- 날짜를 '년-월-일' 형식으로 변환한다.
    select book_no, book_title, to_char(book_create_date, 'yyyy-mm-dd')
    from sample_books

  • to_date('특정 패턴으로 작성된 날짜형식의 문자', '패턴')
    • 문자열을 날짜로 변환한다.

    -- '2020'년에 출간된 도서된 도서를 검색한다.
    select book_no, book_title, book_price, book_create_date
    from sample_books
    where book_create_date >= to_date('2020-01-01 00:00:00', 'yyyy-mm-dd hh:mi:ss') 
    and book_create_date < to_date('2021-01-01 00:00:00', 'yyyy-mm-dd hh:mi:ss')

  • 패턴 문자
    • YYYY 년도를 나타낸다.
    • MM 월을 나타낸다.
    • DD 일을 나타낸다.
    • AM 오전 오후를 나타낸다.
    • HH, HH12, HH24 시간을 나타낸다.
    • MI 분을 나타낸다.
    • SS 초를 나타낸다.

--------------------------------------------------------------------------------
-- 오라클의 내장함수 : 타입 변환함수
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
-- 묵시적 타입변환 : 컬럼의 데이터타입을 참조해서 자동으로 데이터타입을 변환하는 것
--------------------------------------------------------------------------------
SELECT *
FROM EMPLOYEES
WHERE EMPLOYEE_ID = '100';  -- EMPLOYEE_ID 컬럼의 데이터타입이  NUMBER 타입이기 때문에 '100'이 100으로 타입이 변환된다.

--------------------------------------------------------------------------------
-- 명시적 형변환
--------------------------------------------------------------------------------

-- TO_CHAR(숫자, '포맷형식') : 숫자를 지정된 포맷형식의 텍스트로 변환한다.
-- SQL에서 숫자값을 ,가 포함된 텍스트로 변환하는 작업은 추천되는 작업이 아니다.
SELECT EMPLOYEE_ID, FIRST_NAME, TO_CHAR(SALARY, '99,999')
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 60;

-- TO_CHAR(날짜, '포맷형식') : 날짜를 지정된 포맷형식의 텍스트로 변환한다.
SELECT 
       TO_CHAR(SYSDATE, 'YYYY') YEAR,   -- DATE타입을 텍스트로 변환한다. SYSDATE -> '2022' 
       TO_CHAR(SYSDATE, 'MM/DD') DAY,   -- DATE타입을 텍스트로 변환한다. SYSDATE -> '10/18' 
       -- TO_CHAR(SYSDATE, 'M/D')       -- D나 M을 한번씩만 적으면 오류가 발생한다.
       TO_CHAR(SYSDATE, 'AM'),          -- DATE타입을 텍스트로 변환한다. SYSDATE -> '오후'
       TO_CHAR(SYSDATE, 'HH:MI:SS'),    -- DATE타입을 텍스트로 변환한다. SYSDATE -> '12:24:11'
       TO_CHAR(SYSDATE, 'HH24:MI:SS')   -- DATE타입을 텍스트로 변환한다. SYSDATE -> '12:24:11'
FROM DUAL;

-- 년도에 상관없이 이번달 입사자 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE TO_CHAR(HIRE_DATE, 'MM') = TO_CHAR(SYSDATE, 'MM');

-- TO_NUMBER('텍스트', '패턴') : 지정된 패턴과 일치하는 텍스트를 숫자로 변환한다.
-- 텍스트에 숫자가 아닌 텍스트가 포함되어 있지 않으면 패턴을 지정하지 않아도 된다.
SELECT TO_NUMBER('1234') + TO_NUMBER('1234') -- 명시적 형변환
FROM DUAL;

-- 텍스트에 숫자가 아닌 텍스트가 포함되어 있지 않으면 명시적 형변환이 필요없다.
SELECT '1234' + '1234'  -- 묵시적 형변환
FROM DUAL;

-- 텍스트에 숫자가 아닌 텍스트가 포함되어 있는 패턴을 지정해야 한다.
SELECT TO_NUMBER('1,234', '9,999') + TO_NUMBER('1,234', '9,999') -- 명시적 형변환
FROM DUAL;

SELECT '1,234' + '1,234'  -- 묵시적 형변환 오류
FROM DUAL;

-- TO_DATE('텍스트', '패턴') : 지정된 패턴과 일치하는 텍스트를 날짜로 변환한다.
SELECT TRUNC(SYSDATE) - TO_DATE('1973/04/11', 'YYYY/MM/DD')
FROM DUAL;

기타함수

nvl(null값이 예상되는 컬럼, 대체할 값)

• nvl은 null값을 지정된 대체값으로 변환한다.
• 컬럼의 값이 null이 아닌 경우에는 그 컬럼의 원래값이 반환된다.
• nvl에서는 첫번째 항목과 두번째 항목의 데이터 타입이 동일해야 한다.
• 주로 null값을 포함하고 있는 컬럼이 연산식에 포함되어 있을 때 사용한다.

NVL2(컬럼 혹은 표현식, NULL이 아닐 때 대체할 값, NULL일 때 대체할 값)

• 대체할 값은 데이터타입이 동일한 값이어야 한다.

case ~ when ~ then ~ end

• 제시된 조건에 따라서 다른 조회결과를 제공받을 수 있다.
• java의 switch나 if ~ else if ~ else과 유사한다.

decode(컬럼, 값, 반환값, 값 반환값, ...)

• 제시된 조건에 따라서 다른 조회결과를 제공받을 수 있다.

--------------------------------------------------------------------------------
-- 오라클의 내장함수 : 기타 함수
--------------------------------------------------------------------------------

-- nvl(컬럼 혹은 표현식, 대체할 값) : 지정된 컬럼 혹은 표현식의 값이 null이면 대체할 값을 반환한다.
--                               : 원래 값과 대체할 값의 타입이 같은 타입이어야 한다.
-- 급여 실수령액을 계산하기
-- 실수령액 = SALARY + SALARY * COMMISSION_PCT다.
-- * COMMISSION_PCT가 NULL인 경우 실수령액이 NULL값으로 계산된다.
-- * NVL()함수를 사용해서 COMMISSION_PCT가 NULL일 때, 0을 반환하도록 한다.
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, COMMISSION_PCT, 
       NVL(COMMISSION_PCT, 0), -- COMMISSION_PCT값이 NULL이면 0을 반환하고, 아니면 COMMISSION_PCT값을 반환한다. 
       SALARY + SALARY*NVL(COMMISSION_PCT, 0) REAL_SALARY
FROM EMPLOYEES
WHERE SALARY >= 10000;

-- NVL2(컬럼 혹은 표현식, NULL이 아닐 때 대체할 값, NULL일 때 대체할 값)
-- * 대체할 값은 데이터타입이 동일한 값이어야 한다.

SELECT EMPLOYEE_ID, FIRST_NAME, COMMISSION_PCT, NVL2(COMMISSION_PCT, '커미션 받음', '커미션 받지 않음')
FROM EMPLOYEES
WHERE SALARY >= 10000;

-- CASE ~ WHEN 표현식

-- if ~ else if ~ else if ~ else 형식으로 사용하기
-- CASE 
--      WHEN 조건식1 THEN 표현식1     -- 조건식1이 TRUE로 판정되면 표현식1이 최종결과가 된다.
--      WHEN 조건식2 THEN 표현식2     -- 조건식2이 TRUE로 판정되면 표현식2이 최종결과가 된다.
--      WHEN 조건식3 THEN 표현식3     -- 조건식3이 TRUE로 판정되면 표현식3이 최종결과가 된다.
--      ELSE 표현식4                 -- 조건시1, 조건식2, 조건식3이 모두 FALSE로 판정되면 표현식4가 최종결과가 된다.
-- END

-- switch문 형식으로 사용하기
-- CASE 컬럼 혹은 표현식
--      WHEN 값1 THEN 표현식1       -- 컬럼 혹은 표현식의 값이 값1과 일치하면 표현식1이 최종 결과가 된다.
--      WHEN 값2 THEN 표현식2       -- 컬럼 혹은 표현식의 값이 값2과 일치하면 표현식2이 최종 결과가 된다.
--      WHEN 값3 THEN 표현식3       -- 컬럼 혹은 표현식의 값이 값3과 일치하면 표현식3이 최종 결과가 된다.
--      ELSE 표현식4                -- 컬럼 혹은 표현식의 값과 모두 일치하지 않으면 표현식4가 최종결과가 된다.
-- END

-- 직원아이디, 이름, 급여, 급여등급을 조회하기
-- 급여등급: A-20000이상, B-15000이상, C-1000이상, D-5000이상, E-2500이상, F-1000이상, G-그 외 급여
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, 
       CASE
            WHEN SALARY >= 20000 THEN 'A'
            WHEN SALARY >= 15000 THEN 'B'
            WHEN SALARY >= 10000 THEN 'C'
            WHEN SALARY >= 5000 THEN 'D'
            WHEN SALARY >= 2500 THEN 'E'
            WHEN SALARY >= 1000 THEN 'F'
            ELSE 'G'
        END AS EMPLOYEE_GRADE
FROM EMPLOYEES
ORDER BY EMPLOYEE_GRADE;

-- 직원아이디, 이름, 급여, 보너스 조회하기
-- 보너스는 10000불 이상은 급여의 10%, 5000불 이상은 20%, 그 외는 급여의 30%를 보너스로 지급한다.
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, 
       CASE 
            WHEN SALARY >= 10000 THEN SALARY*0.1
            WHEN SALARY >= 5000  THEN SALARY*0.2
            ELSE SALARY*0.3
       END AS BONUS
FROM EMPLOYEES
ORDER BY EMPLOYEE_ID ASC;

-- 100번 부서에 근무하는 직원의 직원아이디, 이름, 급여, 보너스를 조회하기
-- 보너스는 직종에 따라서 지급되며, FI_MGR: 10%, FI_ACCOUNT: 20%를 지급한다.
SELECT EMPLOYEE_ID, FIRST_NAME, JOB_ID, SALARY,
       CASE JOB_ID
            WHEN 'FI_MGR' THEN SALARY*0.1
            WHEN 'FI_ACCOUNT' THEN SALARY*0.2
       END AS BONUS
FROM EMPLOYEES 
WHERE DEPARTMENT_ID = 100;

-- DECODE() 함수
-- DECODE(컬럼 혹은 표현식, 값1, 표현식1,     -- 컬럼 혹은 표현식의 값이 값1과 일치하면 표현식1이 최종 결과가 된다.
--                        값2, 표현식2,     -- 컬럼 혹은 표현식의 값이 값2과 일치하면 표현식2이 최종 결과가 된다.
--                        값3, 표현식3,     -- 컬럼 혹은 표현식의 값이 값3과 일치하면 표현식3이 최종 결과가 된다.
--                        표현식4)          -- 컬럼 혹은 표현식의 값이 값1, 값2, 값3 모두와 일치하지 않으면 표현식4가 최종 결과가 된다.

-- 부서별로 팀 나누기,
-- A팀(10, 20, 30, 40 번 부서), B팀(50, 60번 부서), C팀(70, 80번 부서), D팀(그 외 부서)로 직원들의 팀을 나누기
SELECT EMPLOYEE_ID, FIRST_NAME, DEPARTMENT_ID,
       DECODE(DEPARTMENT_ID, 10, 'A팀', 
                             20, 'A팀', 
                             30, 'A팀', 
                             40, 'A팀', 
                             50, 'B팀', 
                             60, 'B팀', 
                             70, 'C팀', 
                             80, 'C팀', 
                             'D팀') AS TEAM
FROM EMPLOYEES
ORDER BY TEAM ASC, DEPARTMENT_ID ASC;