KH 44일차 - 문자 / 숫자 / 날짜 / 변환 처리 함수 (***)

-- -------------------------------------------

-- 1. 문자처리 함수 - SUBSTR ( offset index로 음수를 사용할 수 있을까? )

-- -------------------------------------------

SELECT

    '900303-1234567',

    substr('900303-1234567', -7)

FROM

    dual;

-- 더미 테이블에서 900303-1234567이라는 문자열에서 뒤에서 7번째 글자부터 가지고 온다는 의미이다.

-- 이 경우에는 1234567이 출력된다.

-- offset index가 음수인 경우에는 맨 끝에서부터 -1,-2 ...이렇게 카운팅한다.

-- substr은 가지고 올 문자의 개수를 정하지 않으면, 끝까지 가지고 오게 된다.

 

---

-- -------------------------------------------

-- 2. 문자처리 함수 - REPLACE ( 문자열 치환 )

-- -------------------------------------------

SELECT

    replace('JACK and JUE', 'J','BL')

FROM

    dual;

-- 더미 테이블에서 JACK and JUE이라는 문자열에 있는 J라는 문자를 전부 BL로 변경해준다.

 

---

-- -------------------------------------------

-- 3. 문자처리 함수 - LPAD (***)

-- -------------------------------------------

-- 문자열을 오른쪽 정렬 후, 왼쪽의 빈 공간에 지정문자를 채운다.

-- -------------------------------------------

SELECT

    lpad('MILLER',10,'*')

FROM

    dual;

-- lpad('MILLER',10,'*')의 의미는 10개 문자로 구성된 문자열을 새로 생성한 후,

-- MILLER을 오른쪽으로 정렬한 뒤에 남은 공간에 *를 출력하라는 의미이다.

-- 만약 새로 만드는 문자열의 길이가 MILLER보다 작은 3으로 지정하면 MIL만이 출력된다.

 

---

-- -------------------------------------------

-- 4. 문자처리 함수 - RPAD (***)

-- -------------------------------------------

SELECT

    rpad('MILLER',10,'*')

FROM

    dual;

-- 주어진 문자열을 새로 생성된 10개 문자로 구성된 문자열에 왼쪽정렬한 후, 남은 공간에 *를 출력한다.

SELECT

    substr('900303-1234567',1,8) || '******' AS 주민번호

FROM

    dual;

-- substr로 900303-1234567이라는 문자열에서 1번째 문자부터 8문자를 가져오고,

-- 그 후에 ******을 합쳐서 출력한다. ( 별칭은 주민번호이다. )

SELECT

    rpad(

        substr('900303-1234567',1,8), 14,'*'

    ) AS 주민번호

FROM

    dual;

-- rpad로 14문자로 구성된 문자열을 생성한 후, substr로 900303-1234567에서 1번째 문자부터 8문자를 가지고 와

-- 오른쪽 정렬한 후에, 남은 공간에는 *를 출력하였다.

-- 문자의 개수에 -이라는 특수문자도 포함된다.

-- rpad를 사용한 경우에는, *를 얼마나 출력해야 하는지 카운팅하지 않아도 자동으로 채워준다는 장점이 있다.

-- + 함수의 중첩은 얼마든지 가능하다!

 

---

-- -------------------------------------------

-- 5. 문자처리 함수 - LTRIM (**)

-- -------------------------------------------

-- 문자열의 왼쪽에서, 지정문자 삭제 ( LTRIM )

-- -------------------------------------------

SELECT

    ltrim('MMMIMLLER','M')

FROM

    dual;

-- 더미 테이블에서 MMMIMLLER이라는 문자열에서 왼쪽부터 읽어서 M이 더이상 나오지 않으면 바로 중단한다.

-- 이 경우에는 MMMIMLLER에서 I를 만날때까지 삭제하기에, IMLLER만 출력한다.

SELECT

    ltrim('MMMIMLLER','R')

FROM

    dual;

-- 이 경우에는 왼쪽으로 읽어 나아가는데, 처음 문자부터 R이 아닌 M이기에 아무것도 삭제하지 못하고 전부 출력한다.

SELECT

    ltrim(' MILLER '),

    length( ltrim(' MILLER '))

FROM

    dual;

-- 이 경우에는 따로 삭제할 지정문자를 지정하지 않았기에, 디폴트로 공백이 삭제된다. (***)

-- 즉, 맨 앞에 있는 공백이 삭제되고 바로 M을 만났기에 뒤에 있는 공백은 삭제되지 못하여 문자의 길이는 7로 출력된다.

 

---

-- -------------------------------------------

-- 6. 문자처리 함수 - RTRIM

-- -------------------------------------------

-- 문자열의 오른쪽에서, 지정문자 삭제 ( RTRIM )

-- -------------------------------------------

SELECT

    rtrim(' MILLER '),

    length( rtrim(' MILLER '))

FROM

    dual;

-- rtrim은 오른쪽부터 읽어 나아가기 때문에, 맨 뒤의 공백만 삭제하고 맨 앞의 공백을 삭제하지 못하고 문자열 길이 7로 출력한다.

 

---

-- -------------------------------------------

-- 7. 문자처리 함수 - TRIM

-- -------------------------------------------

-- 문자열의 왼쪽 / 오른쪽 / 양쪽에서 지정문자 삭제 ( trim )

-- ( 단, 문자열의 중간은 처리하지 못한다. )

-- -------------------------------------------

-- 문법 :

--    TRIM( LEADING 'str' FROM 컬럼명|표현식 )  -- 왼쪽부터 삭제

--    TRIM( TRAILING 'str' FROM 컬럼명|표현식 ) -- 오른쪽부터 삭제

--    TRIM( BOTH 'str' FROM 컬럼명|표현식 ) -- 양쪽으로 삭제

--    TRIM( 'str' FROM 컬럼명|표현식 )     -- = BOTH (default)

-- -------------------------------------------

SELECT

    trim ( '0' FROM '0001234567000' )

FROM

    dual;

-- trim의 경우 키워드를 적지 않으면, 디폴트로 BOTH로 들어가서 양쪽에서 삭제된다.

-- 그렇기에 0이 양쪽에서 삭제되어 1234567이 출력된다.

SELECT

    trim ( BOTH '0' FROM '0001234567000' )

FROM

    dual;

-- BOTH라는 키워드가 있기에, 양쪽에서 0을 삭제하여 결과셋을 출력하게 된다.

SELECT

    trim( LEADING '0' FROM '0001234567000' )

FROM

    dual;

-- LEADING이라는 키워드로 지정되었기에, 왼쪽에서부터 삭제하여 결과셋을 출력한다.

-- 즉, 남은 문자열인 1234567000를 출력한다.

SELECT

    trim( TRAILING '0' FROM '0001234567000' )

FROM

    dual;

-- TRAILING이라는 키워드로 지정되었기에, 오른쪽에서부터 삭제하여 결과셋을 출력한다.

-- 즉, 남은 문자열인 0001234567를 출력한다.

 

---

-- -------------------------------------------

-- 8. 단일(행) (반환)함수 - 바로 적용이 가능하다.

-- -------------------------------------------

-- 단일(행) (반환)함수 구분 :

-- -------------------------------------------

--  (1) 문자(처리) 함수 : 문자와 관련된 특별한 조작을 위한 함수이다.

--  (2) 숫자 (처리)함수 :

--      a. ROUND     - 지정한 자리 수 이하에서 반올림

--      b. TRUNC     - 지정한 자리 수 이하에서 절삭

--      c. MOD       - 나누기 연산을 한 후에 나머지 값을 반환

--      d. CEIL      - 주어진 숫자값보다 크거나 같은 최소 정수값을 반환

--      e. FLOOR     - 주어진 숫자값보다 작거나 같은 최대 정수값을 반환

--      f. SIGN      - 주어진 값이 양수인지 음수인지 0인지 식별할 수 있는 값을 반환

--  (3) 날짜 (처리)함수

--  (4) 변환 (처리)함수 (***) : ex. to_date, to_char

--  (5) 일반 (처리)함수

--

--  단일(행) (반환)함수는, 테이블의 행 단위로 처리됨!

--  인덱스 번호는 0부터 시작된다.

--  함수는 소문자로 작성해야 한다. ( 대문자로 작성해도 문제는 없다. )

---

-- -------------------------------------------

-- 9. 숫자 (처리) 함수 - ROUD

-- -------------------------------------------

-- 지정한 자리 수 이하에서 반올림한다. (**)

-- -------------------------------------------

-- 1 ) ROUND 함수 ( 양수 )

SELECT

    round( 456.789, 2 )

FROM

    dual;

-- 더미 테이블에서 지정한 자리 수인 소숫점 자리 2까지만 남기고 그 아래에서 반올림하여 결과를 출력한다. (**)

-- 이 경우에는 456.79가 출력된다.

-- 2 ) ROUND 함수 ( 음수 )

SELECT

    round( 456.789 , -1 )

FROM

    dual;

-- 소숫점을 기준으로 양수와 음수를 구분하게 되는데,

-- 음수는 소숫점 자리 아래의 숫자들이고 양수는 자연수로 뽑을 수 있는 소숫점 위의 숫자들을 의미한다.(**)

-- 그런데 음수의 경우에는 지정한 자리수 이하에서 반올림이 일어나는 것이 아니라, 지정한 자리수를 기준으로 반올림을 판단한다. (***)

-- 즉, 이 경우에는 456 중 6을 기준으로 반올림을 하기에 460이라는 결과셋이 출력된다.

-- 3 ) ROUND 함수 ( 양수, 음수 미지정 - 소수점 자리에서 반올림 )

SELECT

    round( 456.789 )

FROM

    dual;

-- 숫자를 지정하지 않을 때는 소수점 자리에서 반올림하게 되는데, 이때 반올림 판단 기준은 소숫점 바로 아래 숫자인 소숫점 첫번째 숫자로 판단한다.(**)

-- 즉, 456.789 중 .7로 반올림을 실행하기에 결과셋은 457로 출력되게 된다.

---

-- -------------------------------------------

-- 10. 숫자 (처리) 함수 - TRUNC 함수 ( 버림 )

-- -------------------------------------------

-- 1 ) TRUNC 함수 ( 양수 )

SELECT

    trunc( 456.789 , 2 )

FROM

    dual;

-- ROUND와 같이 지정한 자릿수인 2까지는 남겨두고, 그 아래 소숫점 자리부터 삭제된다.

-- 이 경우에는 소숫점 2까지인 8까지는 남겨둬야 하기에 456.78로 결과셋이 출력된다.

-- 2 ) TRUNC 함수 ( 음수 )

SELECT

    trunc( 456.789 , -1 )

FROM

    dual;

-- ROUND와 같이 양수의 경우, 음수와 다르게 지정한 자릿수를 기준으로 버림을 실행하기에 450이 출력된다.

-- 3 ) TRUNC 함수 ( 양수, 음수 미지정 - 소수점 자리에서 반올림 )

SELECT

    trunc( 456.789 )

FROM

    dual;

-- ROUND와 같이 소숫점 첫번째 자리부터 버림을 실행하기에 456이라는 결과셋이 출력된다.

-- 이 방법은 소숫점 자릿수를 삭제해야 될 때 많이 사용된다. (***)

---

-- -------------------------------------------

-- 11. 숫자 (처리) 함수 - MOD ( Modular )

-- -------------------------------------------

-- 나누기 연산을 한 후에 나머지 값을 반환한다.

-- -------------------------------------------

SELECT

    mod(10, 3),

    mod(10, 0)

FROM

    dual;

-- 자바와 다르게 0으로 나눌 수가 있는데, 이 경우에는 나눠짐을 당하는 수를 그대로 반환한다. (***)

-- 이 경우에는 10을 3으로 나눈 후의 나머지인 1과 10을 0으로 나누었기에 나오는 수인 10이 출력된다.

-- MOD 함수의 응용 ( 홀수 판단 ) (***)

SELECT

    employee_id,

    last_name,

    salary

FROM

    employees

WHERE

    mod ( employee_id, 2 ) = 1;

-- 나머지 연산자인 MOD를 사용하여 사원번호를 2로 나누었을 때 나머지가 1인 사원을 출력하게 하였다.

SELECT

    employee_id,

    last_name,

    salary

FROM

    employees

WHERE

    mod ( employee_id, 2 ) != 0;

-- 나머지 연산자인 MOD를 활용하여, 2로 나누었을 때 나머지가 0이 아닌 사원을 출력한다.

---

-- -------------------------------------------

-- 12. 숫자 (처리) 함수 - CEIL

-- -------------------------------------------

-- 주어진 숫자값보다 크거나 같은 최소 정수값을 반환

-- -------------------------------------------

SELECT

    ceil(10.6),

    ceil(-10.6)

FROM

    dual;

-- CEIL의 경우 크거나 같은 정수를 반환하기에 10.6보다 큰 11과 -10.6보다 큰 -10를 출력하게 된다.

---

-- -------------------------------------------

-- 13. 숫자 (처리) 함수 - FLOOR

-- -------------------------------------------

-- 주어진 숫자값보다 작거나 같은 최대 정수값을 반환

-- -------------------------------------------

SELECT

    floor(10.6),

    floor(-10.6)

FROM

    dual;

-- FLOOR의 경우 작거나 같은 정수를 반환하기에 10.6보다 작은 정수인 10과 -10.6보다 작은 -11을 출력하게 된다.

---

-- -------------------------------------------

-- 14. 숫자 (처리) 함수 - SIGN

-- -------------------------------------------

-- 주어진 값이 양수인지 음수인지 0인지 식별할 수 있는 값을 반환

-- -------------------------------------------

SELECT

    sign(100),

    sign(-20),

    sign(0)

FROM

    dual;

-- sign에 주어진 값이 양수이면 1, 음수이면 -1, 0이면 0으로 구분해 출력해 준다.

-- 1 ) SIGN함수의 응용 (***)

SELECT

    employee_id,

    last_name,

    salary

FROM  

    employees

WHERE

    sign( salary - 15000 ) = 1;         -- 15000 초과

    -- sign( salary - 15000 ) != -1;    -- 15000 이상

-- 월급이 15000이상인 직원들만 출력하였다.

-- sign( salary - 15000 )의 의미는 각 직원의 월급을 추출하여 15000을 뺀 다음 양수인지 판단하여, 월급이 15000이상인 직원만 추출하였다.

-- 즉 15000이상인 직원의 경우 15000을 빼도 양수의 결과가 남기에, sign( salary - 15000 ) = 1로 필터링을 하게 되면 월급이 15000이상인 직원만 남는다.

---

-- -------------------------------------------

-- 15. 단일(행) (반환)함수 - 바로 적용이 가능하다.

-- -------------------------------------------

-- 단일(행) (반환)함수 구분 :

-- -------------------------------------------

--  (1) 문자(처리) 함수 : 문자와 관련된 특별한 조작을 위한 함수이다.

--  (2) 숫자 (처리)함수 :

--  (3) 날짜 (처리)함수 : 날짜 데이터 타입 컬럼에 사용하기 위한 함수이다.

--      a. SYSDATE          - DB서버에 설정된 날짜를 반환

--      b. MONTH_BETWEEN    - 두 날짜 사이의 월수를 계산하여 반환

--      c. ADD_MONTHS       - 특정 개월수를 더한 날짜를 계산하여 반환

--                          - 음수값을 지정하면 뺀 날짜를 반환

--      d. NEXT_DAY         - 명시된 날짜로부터, 다음 요일에 대한 날짜 반환

--      e. LAST_DAY         - 지정된 월의 마지막 날짜 반환

--                          - 윤년 및 평년 모두 자동으로 계산

--      f. ROUND            - 날짜를 가장 가까운 년도 또는 월로 반올림하여

--                            반환

--      g. TRUNC            - 날짜를 가장 가까운 년도 또는 월로 절삭하여 반환

--      * Oracle은 날짜정보를 내부적으로 7바이트 숫자로 관리 -> 산술연산가능 (***)

--  (4) 변환 (처리)함수 (***) : ex. to_date, to_char

--  (5) 일반 (처리)함수

--

--  단일(행) (반환)함수는, 테이블의 행 단위로 처리됨!

--  인덱스 번호는 0부터 시작된다.

--  함수는 소문자로 작성해야 한다. ( 대문자로 작성해도 문제는 없다. )

---

-- -------------------------------------------

-- + 현 Oracle 서버의 날짜표기형식(DATE FORMAT) 설정 확인

-- -------------------------------------------

-- Oracle NLS: National Language Support

-- 오라클의 년도표기 방식 (page 41 ~ 42 참고)

-- -------------------------------------------

DESC nls_session_parameters; -- 오라클의 표기 방식 테이블 확인

SELECT

    *

FROM

    nls_session_parameters;

-- nls_session_parameters 속 NLS_DATE_FORMAT에서 현재 개발도구에서는 날짜를 DD-MON-RR 형식으로 정해 놓았음을 알 수 있다.

-- NLS_TIME_FORMAT에서 시간은 HH.MI.SSXFF AM 형식으로 표기하고 있음을 알 수 있다.

SELECT

    sysdate

FROM

    dual;

-- 현재의 날짜를 개발도구의 설정에 맞게 출력한다. ( DD-MON-RR -> 28-4월-22 )

---

-- ------------------------------------------------------

-- * To change Oracle's default date format (***)

-- ------------------------------------------------------

ALTER SESSION SET NLS_DATE_FORMAT = 'RR/MM/DD';

-- 현재 세션에 대해서만 변경된 내용이 적용된다.

SELECT

    sysdate

FROM

    dual;

-- 현재 세션에 대해서 날짜를 RR/MM/DD형태로 바꾸었기에 22/04/28로 출력이 된다.

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD HH24:MI:SS';

SELECT

    sysdate

FROM

    dual;

-- 우리나라 표기 형식으로 바꾸었지만, sysdate는 세계표준시각을 기준으로 표기하기 때문에 우리나라와 시간이 맞지 않는다.

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD HH24:MI:SS';

SELECT

    current_date

FROM

    dual;

-- 우리나라 현재 시각을 우리나라 시간 표기형식으로 바꾸었기에 2022/04/28 11:38:53라고 출력된다.

---

-- -------------------------------------------

-- 16. 날짜 (처리) 함수 - SYSDATE

-- -------------------------------------------

-- DB서버에 설정된 날짜를 반환

-- -------------------------------------------

-- * 날짜 연산 (page 43참고) (***)

-- (1) 날짜 + 숫자: 날짜에 일수를 더하여 반환

-- (2) 날짜 - 숫자: 날짜에 일수를 빼고 반환

-- (3) 날짜 - 날짜: 두 날짜의 차이(일수) 반환

-- (4) 날짜 + 숫자/24: 날짜에 시간을 더한다. ( 1시간 단위 )

-- -------------------------------------------

SELECT

    sysdate AS 오늘,

    sysdate + 1 AS 내일,

    sysdate - 1 AS 어제

FROM

    dual;

-- 날짜 연산을 활용하여 오늘에 1일을 더하고 빼서 내일과 어제를 출력

SELECT

    last_name,

    hire_date,

    sysdate - hire_date AS 근속일수,

    ( sysdate - hire_date ) / 365 AS 근속년수,

    trunc( (sysdate - hire_date) / 365 ) AS "근속년수 ( 소숫점 버림 )"

FROM

    employees

ORDER BY

    근속일수 DESC;

-- 근속일수를 기준으로 내림차순으로 정렬하였다.

 

---

-- -------------------------------------------

-- 17. 날짜 (처리) 함수 - MONTHS_BETWEEN

-- -------------------------------------------

-- 두 날짜 사이의 개월 수를 계산하여 반환

-- -------------------------------------------

SELECT

    last_name,

    hire_date,

    months_between(sysdate, hire_date) AS "근속월 수 ( 소숫점 포함 )",

    trunc( months_between(sysdate, hire_date) ) AS "근속월수",

    trunc( months_between(sysdate, hire_date) / 12) AS "근속년수"

FROM

    employees

ORDER BY

    3 DESC;

-- 근속월 수 ( 소숫점 포함 )를 기준으로 내림차순으로 정렬하였다.

-- 근속월수와 근속년수를 구하기 위해서 months_between을 활용하여 두 날짜 사이의 개월 수를 구하였고, trunc를 통해 소숫점을 버렸다.

 

---

-- -------------------------------------------

-- 18. 날짜 (처리) 함수 - ADD_MONTHS

-- -------------------------------------------

SELECT

    sysdate AS 오늘,

    add_months(sysdate, 1) AS "1개월 후 오늘",

    add_months(sysdate, -1) AS "1개월 전 오늘"

FROM

    dual;

-- 오늘에서 1개월을 더하고 빼서, 1개월 전의 오늘과 1개월 후의 오늘의 날짜를 구하였다.

---

-- -------------------------------------------

-- 19. 날짜 (처리) 함수 - NEXT_DAY

-- -------------------------------------------

-- 명시된 날짜로부터, 다음 요일에 대한 날짜 반환

-- 일요일(1), 월요일(2) ~ 토요일(7)

-- -------------------------------------------

-- NEXT_DAY( date1, {'string' | n } )

-- -------------------------------------------

SELECT

    last_name,

    hire_date,

    next_day(hire_date,'FRI'),      -- 최초로 돌아오는 금요일에 해당하는 날짜 출력 1

    next_day(hire_date, 6)          -- 최초로 돌아오는 금요일에 해당하는 날짜 출력 2

FROM

    employees

ORDER BY

    3 desc;

-- 이를 통해서 금요일이라는 요일을 FRI라는 문자로 지정하든, 6이라는 숫자 타입으로 지정하든 같은 결과를 출력하고 있음을 알 수 있다.

-- 디벨로퍼에서는 '금'이라고 한글로 작성해도 괜찮지만, 역으로 디벨로퍼에서는 FRI로는 인식이 되지 않는다.

---

-- -------------------------------------------

-- 20. 날짜 (처리) 함수 - LAST_DAY

-- -------------------------------------------

-- 지정된 월의 마지막 날짜를 반환해 준다.

-- 윤년 및 평년 모두 자동으로 계산해 준다.

-- -------------------------------------------

-- LAST_DAY(date1)

-- -------------------------------------------

SELECT

    last_name,

    hire_date,

    last_day(hire_date)     -- 채용일자가 속한 그 달의 마지막 날짜를 반환

FROM

    employees

ORDER BY

    2 desc;

-- hire_date로 내림차순으로 정렬하였으며, last_day를 활용하여 채용한 달의 마지막 날짜를 출력해 주고 있다.

SELECT

    last_name,

    hire_date,

    next_day(add_months(hire_date, 5), 'SUN')

FROM

    employees

ORDER BY

    hire_date desc;

-- hire_date로 내림정렬하였기에, 최근 날짜에 채용된 직원부터 정렬하였으며

-- next_day를 통해서 채용일자 5개월 후의 돌아오는 일요일의 날짜를 반환한다.

---

-- -------------------------------------------

-- 21. 날짜 (처리) 함수 - ROUD

-- -------------------------------------------

-- 날짜를 가장 가까운 년도 또는 월로 반올림하여 반환한다.

-- -------------------------------------------

-- ROUND(date1, 'YEAR') : 지정된 날짜의 년도를 반올림(to YYYY/01/01) <- 월과 일은 모두 1로 초기화되어 버린다.

-- ROUND(date2, 'MONTH'): 지정된 날짜의 월을 반올림(to YYYY/MM/01) <- 일이 1로 초기화되어 버린다.

-- -------------------------------------------

SELECT

    last_name,

    hire_date,

    round(hire_date, 'YEAR'),       -- 채용날짜의 "연도"를 반환한다. ( 월과 일 초기화 )

    round(hire_date, 'MONTH')       -- 채용날짜의 "월"을 반환한다. ( 일을 초기화 )

FROM

    employees;

-- 년도를 반환할 때에는 1년의 중간인 7월 1일을 기준으로 반올림하여 결과셋을 출력하게 된다.

-- 월의 경우에는 해당 월의 반을 기준으로 반올림하게 된다.

---

-- -------------------------------------------

-- 22. 날짜 (처리) 함수 - TRUNC ( 버림 )

-- -------------------------------------------

-- TRUNC(date1, 'YEAR') : 지정된 날짜의 년도를 절삭(to YYYY/01/01) <- 월과 일은 모두 1로 초기화되어 버린다.

-- TRUNC(date1, 'MONTH'): 지정되 날짜의 월을 절삭(to YYYY/MM/01) <- 일이 1로 초기화되어 버린다.

-- -------------------------------------------

SELECT

    last_name,

    hire_date,

    trunc(hire_date, 'YEAR'),           -- 년 아래의 월과 일을 모두 버려버린다. ( 월과 일이 1로 출력 )

    trunc(hire_date, 'MONTH')           -- 월 아래의 일을 모두 버려버린다. ( 일이 1로 출력 )

FROM

    employees;

-- trunc는 버림이기에 YEAR을 기준으로 하면 월과 일을 버린 상태로 출력이 되고, MONTH를 기준으로 하면 일만 버린 상태로 출력된다.

 

---

-- -------------------------------------------

-- 23. 단일(행) (반환)함수 - 바로 적용이 가능하다.

-- -------------------------------------------

-- 단일(행) (반환)함수 구분 :

-- -------------------------------------------

--  (1) 문자(처리) 함수 : 문자와 관련된 특별한 조작을 위한 함수이다.

--  (2) 숫자 (처리)함수 :

--  (3) 날짜 (처리)함수 : 날짜 데이터 타입 컬럼에 사용하기 위한 함수이다.

--  (4) 변환 (처리)함수 (***) : 숫자/문자/날짜 데이터 간의 형 변환 함수 ex. to_date, to_char

--      a. TO_CHAR   - 숫자 데이터를 문자 데이터로 변환 또는

--                     날짜 데이터를 문자 데이터로 변환

--      b. TO_NUMBER - 문자 데이터를 숫자 데이터로 변환

--      c. TO_DATE   - 문자 데이터를 날짜 데이터로 변환

--  (5) 일반 (처리)함수

--

--  단일(행) (반환)함수는, 테이블의 행 단위로 처리됨!

--  인덱스 번호는 0부터 시작된다.

--  함수는 소문자로 작성해야 한다. ( 대문자로 작성해도 문제는 없다. )

--  변환 함수는 중간에 문자 데이터가 있음을 인지해야 한다.

-- -------------------------------------------

-- 1 ) 자동형변환 (묵시적 형변환) - Promotion

--

--    <NUMBER> <--> <CHARACTER> <--> <DATE>

--

-- 2 ) 강제형변환 (명시적 형변환) - Casting

--

-- (1) <NUMBER>     -- TO_CHAR      --> <CHARACTER>

--     <CHARACTER>  -- TO_NUMBER    --> <NUMBER>

-- (2) <CHARACTER>  -- TO_DATE      --> <DATE>

--     <DATE>       -- TO_CHAR      --> <CHARACTER>

-- (3) <NUMBER>     --    X         --> <DATE>

--     <DATE>       --    X         --> <NUMBER>

-- + (3)에 해당하는 강제형변환은 불가능한 형태이다.

---

-- -------------------------------------------

-- 24. 변환 (처리) 함수 - 문자타입 -> 숫자타입 (자동형변환)

-- -------------------------------------------

DESC employees;

SELECT

    last_name,

    salary

FROM

    employees

WHERE

    salary = '17000';

-- salary는 숫자타입, '17000'은 문자타입으로 서로 다른 타입이지만, 숫자타입과 문자타입은 서로 자동형변환이 되기에 잘 출력이 된다.

-- 그러나 자동형변환이든 강제형변환이든 자원소모가 심하기에 가능하면 사용하지 않는 것이 좋다.

---

-- -------------------------------------------

-- 25. 변환 (처리) 함수 - TO_CHAR

-- -------------------------------------------

-- 1 ) 날짜 데이터를 문자 데이터로 변환

--    예: TO_CHAR( hire_date, 'YYYY' )

--

-- 2 ) 숫자 데이터를 문자 데이터로 변환

--    예: TO_CHAR( 123456, '999,999' )

-- -------------------------------------------

-- 1 ) 날짜 데이터를 문자 데이터로 변환

SELECT

    to_char(sysdate, 'YYYY/MM/DD (AM) DY HH24:MI:SS')

FROM

    dual;

-- 날짜 데이터가 작성한 문자열로 포멧팅되어 출력되었다.

-- 개발도구에 따라서 결과가 다르게 출력될 수 있다.

-- YYYY : 4자리년도, MM : 2자리 월, DD : 2자리 일

-- AM : 오전 / 오후, DY : 요일, HH : 12시간제 시간, HH24 : 24시간제 시간

-- MI : 2자리 분, SS : 2자리 초

SELECT

    last_name,

    hire_date,

    salary

FROM

    employees

WHERE

    to_char(hire_date, 'MM') = '09';

-- 채용된 달이 9월인 직원을 모두 출력한다.

SELECT

    to_char(sysdate, 'YYYY "년" MM "월" DD "일" ') AS 날짜

FROM

    dual;

-- ' '로 문자열을 생성한 후에 " "로 문자를 집어 넣을 수 있다. (***)

-- ""는 포멧팅 문자가 아니기에 ""를 사용하지 않는다면, 개발도구가 인식하지 못한다.

-- 다국어 문자가 아닌 특수문자인 경우에는 ""를 사용하지 않아도, 정상적으로 출력된다.

-- 이 경우에는 년도, 월, 일을 포멧팅문자와 지정한문자를 같이 사용하여 2022 년 04 월 28 일로 출력하게 만들었다.

-- -------------------------------------------

-- 2 ) 숫자 데이터를 문자 데이터로 변환

-- [1] 숫자 출력형식

SELECT

    to_char(1234, '99999') AS "99999",           -- 9: 한 자리의 숫자 표현

    to_char(1234, '099999') AS "099999",        -- 0: 앞부분을 0으로 표현 ( 숫자가 안들어가는 공백 부분 ) (***)

    to_char(1234, '$99999') AS "$99999",        -- $: 달러 기호를 앞에 표현

    to_char(1234, '99999.99') AS "99999.99",    -- .: 소수점을 표시

    to_char(1234, '99,999') AS "99,999",        -- ,: 특정 위치에 , 표시

    to_char(1234, 'B99999.99') AS "B99999.99",  -- B: 공백을 0으로 표현

    to_char(1234, 'B99999') AS "B99999",        -- B: 공백을 0으로 표현

    to_char(1234, 'L99999') AS "L99999",        -- L: 지역 통화(Local currency)

    to_char(1234)                               -- 포멧팅은 생략해도 괜찮다.

FROM

    dual;

-- 지정한 형식에 맞게 숫자 데이터인 1234가 문제 데이터로 포멧팅되어 출력된다.

-- 숫자는 기본적으로 오른쪽 정렬이 된 상태로 출력된다.

-- [2] 화폐 출력형식

SELECT

    last_name,

    salary,

    to_char(salary, '$999,999') AS "달러",

    to_char(salary, 'L999,999') AS "원화"

FROM

    employees;

-- 각 포멧팅 기호에 맞게 월급이라는 숫자 데이터를 포멧팅하였다.

---

-- -------------------------------------------

-- 26. 변환 (처리) 함수 - TO_NUMBER

-- -------------------------------------------

-- 문자 데이터를 숫자 데이터로 변환

-- -------------------------------------------

SELECT

    to_number('123') + 100,              -- 강제형변환 (Casting)   : *Recommended*

    '456' + 100,                         -- 자동형변환 (Promotion) :  Decommended

    to_char(123) || '456',               -- 강제형변환 (Casting)   : *Recommended*

    123 || '456'                         -- 자동형변환 (Promotion) :  Decommended

FROM

    dual;

-- ||를 사용할 때에는 사칙연산이 실행되는 것이 아니라 문자타입의 데이터로 합쳐져서 123456으로 출력되고 있다.

-- +를 사용할 때에는 ''로 문자열을 작성해도 숫자 데이터로 변환되어 계산되고 있음을 볼 수 있다.

---

-- -------------------------------------------

-- 27. 변환 (처리) 함수 - TO_DATE

-- -------------------------------------------

-- '날짜형태'의 문자 데이터를 날짜 데이터로 변환

-- -------------------------------------------

-- 1 ) TO change Oracle's default date format

ALTER SESSION SET NLS_DATE_FORMAT = 'RR/MM/DD';

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD HH24:MI:SS';

-- 2 ) to_date 응용

SELECT

    to_date('20170802181030', 'YYYYMMDDHH24MISS' )

FROM

    dual;

-- YYYY년 MM월 DD일 HH시 MM분 SS초로 해당 문자열인 20170802181030을 포멧팅하였기에 2017/08/02 18:10:30라고 출력된다.

SELECT

    sysdate,

    sysdate - to_date('20170101', 'YYYYMMDD')

FROM

    dual;

-- 현재 날짜(국제 표준 시각 기준)에서 20170101이라는 문자열을 YYYYMMDD로 포멧팅한 날짜 데이터를 빼서 두 날짜 간의 일수를 출력한다.

-- 우리나라 기준으로 하려면, CURRENT_DATE()를 사용해야 한다. (***)

---

-- -------------------------------------------

-- 28. 단일(행) (반환)함수 - 바로 적용이 가능하다.

-- -------------------------------------------

-- 단일(행) (반환)함수 구분 :

-- -------------------------------------------

--  (1) 문자(처리) 함수 : 문자와 관련된 특별한 조작을 위한 함수이다.

--  (2) 숫자 (처리)함수 :

--  (3) 날짜 (처리)함수 : 날짜 데이터 타입 컬럼에 사용하기 위한 함수이다.

--  (4) 변환 (처리)함수 (***) : 숫자/문자/날짜 데이터 간의 형 변환 함수 ex. to_date, to_char

--  (5) 일반 (처리)함수 : if 조건문, switch 다중 조건문을 함수형태로 제공

--      a. DECODE   - if 조건문 제공

--      b. CASE     - switch 다중 조건문 제공

--  단일(행) (반환)함수는, 테이블의 행 단위로 처리됨!

--  인덱스 번호는 0부터 시작된다.

--  함수는 소문자로 작성해야 한다. ( 대문자로 작성해도 문제는 없다. )

--  변환 함수는 중간에 문자 데이터가 있음을 인지해야 한다.

---

-- -------------------------------------------

-- 29. 일반 (처리) 함수 - DECODE ( 자바 : 다중 if문, 삼항연산자 ) (******)

-- -------------------------------------------

-- 조건이 반드시 일치하는 경우에 사용하는 함수

-- 즉, 동등비교연산자(=)가 사용가능한 경우에만 사용가능

-- -------------------------------------------

-- 문법) DECODE(

--          column,

--          비교값1, 결과값1,

--          비교값2, 결과값2,

--          ...

--          비교값n, 결과값n,

--          기본결과값 ( 거짓일 때의 값 )

--      )

-- -------------------------------------------

SELECT

    decode(                                 -- if

        salary,                             -- column이

            1000, salary * 0.1,             -- 만약, 월급이 1000이라면 월급 * 0.1을 주어라

            2000, salary * 0.2,             -- 만약, 월급이 2000이라면 월급 * 0.2를 주어라

            3000, salary * 0.3,             -- 만약, 월급이 3000이라면 월급 * 0.3을 주어라

            salary * 0.4                    -- 그 외의 사람들은 월급 * 0.4을 주어라

    ) AS "보너스"

FROM

    employees;

-- salary의 컬럼을 기준으로 1000일때, 2000일때, 3000일때 그리고 그 외일때를 구분하여 보너스를 계산하여 출력하였다.

-- 월급여액 별, 보너스 계산하기

SELECT

    last_name,

    salary,

    decode(

        salary,

        24000, salary * 0.3,

        17000, salary * 0.2,

        salary

    ) AS "보너스"

FROM

    employees

ORDER BY

    salary desc;

-- 디폴트 값(기본결과 값)을 작성하지 않아도 괜찮지만, 그때는 NULL(결측치 값)이 들어가게 된다.

-- 하지만 결측치를 그대로 두기 보다는 0으로 입력해주는 편이 좋다.

-- 입사년도별 사원들의 인원수 구하기

SELECT

    count(*) AS "총 인원수",

    sum( decode ( to_char(hire_date,'YYYY'), '2001', 1, 0) ) AS "2001",       -- 만약 입사년도가 2001이면 1을 반환하고 그렇지 않으면 0을 반환

    sum( decode ( to_char(hire_date,'YYYY'), 2002, 1, 0) ) AS "2002",

    sum( decode ( to_char(hire_date,'YYYY'), 2003, 1, 0) ) AS "2003",

    sum( decode ( to_char(hire_date,'YYYY'), 2004, 1, 0) ) AS "2004",

    sum( decode ( to_char(hire_date,'YYYY'), 2005, 1, 0) ) AS "2005",

    sum( decode ( to_char(hire_date,'YYYY'), '2006', 1, 0) ) AS "2006",

    sum( decode ( to_char(hire_date,'YYYY'), 2007, 1, 0) ) AS "2007",

    sum( decode ( to_char(hire_date,'YYYY'), 2008, 1, 0) ) AS "2008"

FROM

    employees;

-- 총 인원수에서는 *를 통해 총 직원의 수를 출력하였고, 이후에는 해당 연도에 해당하면 1, 그렇지 않으면 0을 반환하여 합을 구해 출력한다.

-- 별칭이 숫자로 시작할 때에는 ""를 붙여줘야 한다.