[국비지원] KH 정보교육원 37일차

kh day 037

 

 오늘은 능력단위 시험을 마치고 SQL문의 DQL 쿼리를 계속해 학습했다.  원래 진도대로라면 조인과 서브쿼리를 배웠어야 하는데 조금 늦어지고 있는 것 같다. 아마 내일부터 함수를 마치고 조인으로 들어가지 않을까 싶은데 기초 부분이라고 쉽다고 늘어지지말고 적어도 수업시간만큼은 정신을 바짝차리고 집중해서 수업을 들어야겠다. 오늘은 SQL 함수를 배웠는데, 자바의 메소드와 비슷한 기능을 하는 것 같다. 다 외우려기 보다는 계속해서 사용함으로서 장기 메모리로 저장하려는 노력이 필요할 것 같다!!

 

---

1. 단일(행) (반환)함수
------------------------------------------------------
단일(행) (반환)함수의 구분:
 (1) 문자 (처리)함수 : 문자와 관련된 특별한 조작을 위한 함수
 (2) 숫자 (처리)함수 : 
 (3) 날짜 (처리)함수
 (4) 변환 (처리)함수
 (5) 일반 (처리)함수

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

(1) 문자 (처리)함수 : 문자와 관련된 특별한 조작을 위한 함수
     a. INITCAP  - 첫글자만 대문자로 변경
     b. UPPER    - 모든 글자를 대문자로 변경 
     c. LOWER    - 모든 글자를 소문자로 변경
     d. CONCAT   - 두 문자열 연결
     e. LENGTH   - 문자열의 길이 반환
     f. INSTR    - 문자열에서, 특정 문자열의 위치(인덱스) 반환
     g. SUBSTR   - 문자열에서, 부분문자열(substring) 반환
     h. REPLACE  - 문자열 치환(replace)
     i. LPAD     - 문자열 오른쪽 정렬 후, 왼쪽의 빈 공간에 지정문자 채우기(padding)
     j. RPAD     - 문자열 왼쪽 정렬 후, 오른쪽의 빈 공간에 지정문자 채우기(padding)
     k. LTRIM    - 문자열의 왼쪽에서, 지정문자 삭제(trim)
     l. RTRIM    - 문자열의 오른쪽에서, 지정문자 삭제(trim)
     m. TRIM     - 문자열의 왼쪽/오른쪽/양쪽에서, 지정문자 삭제(trim)
                   (단, 문자열의 중간은 처리못함)
------------------------------------------------------


(1) 문자(처리) 함수 - INITCAP : 첫글자만 대문자로 변경

SELECT
   'ORACLE SQL',
   initcap('ORACLE SQL') -- 두 단어에 모두 적용됨
FROM
   dual;

initicap - 첫 글자만 대문자로 변경

SELECT
   email,
   initcap(email)
FROM
   employees;


---

(2) 문자(처리) 함수 - UPPER : 모든 글자를 대문자로 변경

SELECT
   'Oracle Sql',
   upper('Oracle Sql')
   upper('oracle 오라클') -- 한글은 해당사항 없음
FROM
   dual;

upper - 모든 글자를 대문자로 변경

SELECT
   last_name,
   salary
FROM
   employees
WHERE
   last_name = initcap('KING');  -- 추천!
   upper(last_name) = 'KING';   -- 비추!

-- 둘다 결과는 잘 나오지만 위에 것으로 하자!


--- 

(3) 문자(처리) 함수 - LOWER : 모든 글자를 소문자로 변경

SELECT
   'Oracle Sql',
   lower('Oracle Sql')
FROM
   dual;


SELECT
   last_name,
   lower(last_name)
FROM
   employees;

lower - 모든 글자를 소문자로 변경

---

(4) 문자(처리) 함수 - CONCAT : 두 문자열 연결(Concatenation)


SELECT
   'Oracle' || 'Sql' || 'third', 
   concat( concat('Oracle', 'Sql'), 'third')
FROM
   dual;

-- || (Concatenation Operator)랑 concat function이랑 기능 같다
-- concat function은 중첩으로 사용 가능하다
-- 중첩하면 복잡하니까 그냥 ||으로 쓰는게 낫다

concat - 문자열을 연결

---

(5) 문자(처리) 함수 - LENGTH : 문자열의 길이 반환
   A. LENGTH   returns Characters
   B. LENGTHB  returns Bytes


SELECT
   'Oracle',
   length('Oracle')
FROM
   dual;


SELECT
   last_name,
   length(last_name)
FROM
   employees;

length - 문자열 길이를 반환

SELECT
    '한글',
    length('한글')   AS length,
    lengthb('한글')  AS lengthb -- 유니코드
FROM
   dual;

length - 문자열 길이 반환 - 한글

 

--- 

(6) 문자(처리) 함수 - INSTR : 문자열에서, 특정 문자열의 (시작)위치(시작 인덱스) 반환


SELECT
   instr('MILLER', 'L', 1, 2),
   instr('MILLER', 'X', 1, 2)
FROM
   dual;

-- 주의) Oracle 의 인덱스 번호는 1부터 시작함!!!
-- 1: offset(시작점), 2: occurence (2번째 나오는 L)
-- 첫 문자부터 찾아서 두번째로 나오는 L의 인덱스 번호를 알려달라
-- 1: offset(시작점), 2: occurence (2번째로 나오는 X)
-- 첫 문자부터 찾아서 두번째로 나오는 X의 인덱스 번호를 알려달라

instr - 문자의 위치를 알려줌

---

(7) 문자(처리) 함수 - SUBSTR : 문자열에서, 부분문자열(substring) 반환

SELECT
   substr('123456-1234567', 1, 7) || '*******' AS "주민등록번호"
FROM
   dual;

-- 주의) Oracle 의 인덱스 번호는 1부터 시작함!!!
-- 1 : offset, 7 : length
-- 첫 문자부터  7번째 문자까지만 출력함
-- ||로 '*******' 이어 붙이고 AS로 별칭 명명

substr - 문자열 특정 부분만 반환

SELECT
   hire_date,
   to_char(hire_date) AS 입사일,
   substr( to_char(hire_date), 8, 2 ) AS 입사년도 
FROM
   employees;

-- 8 : offset, 2 : length
-- 8번째 문자부터 2번째 문자까지만 출력함

substr 응용

SELECT
   '900303-1234567'AS 주민번호, 
   substr('900303-1234567', 8) AS "주민번호 뒷자리"
FROM
   dual;

-- 8 : offset
-- 8번째 문자부터 끝까지 출력함


SELECT
   '900303-1234567' AS 주민번호,
   substr('900303-1234567', -7) AS "주민번호 뒷자리"
FROM
   dual;

-- -7 : offset
-- 뒤에서 7번째 문자부터 끝까지 출력함

 

substr 응용

---

(8) 문자(처리) 함수 - REPLACE : 문자열 치환(replace)

SELECT
   replace('JACK and JUE', 'J', 'BL')
FROM
   dual;

-- J를 BL로 바꿔줌
-- BLACK and BLUE 가 나오겠죠?
-- j는 안 바꿔줘~ J가 여러번 나오면 다 바꿔줘~


---

(9) 문자(처리) 함수 - LPAD 
     : 문자열 오른쪽 정렬 후, 왼쪽의 빈 공간에 지정문자 채우기(padding)


SELECT
   lpad('MILLER', 10, '*')
FROM
   dual;

-- MILLER 오른쪽으로 밀고, 10개의 칸에서, 남은 곳에 * 채운다

---

(10) 문자(처리) 함수 - RPAD
       :  문자열 왼쪽 정렬 후, 오른쪽의 빈 공간에 지정문자 채우기(padding)

SELECT
   substr('900303-1234567', 1, 8) || '******' AS 주민번호,
   rpad ( substr('911011-1025017', 1, 8), 14, '*' ) AS 주민등록번호
FROM
   dual;

rpad의 응용 - substr과 함께 사용

--- 
(11, 12) 문자(처리) 함수 - LTRIM, RTRIM
        : 문자열의 왼쪽과 오른쪽에서, 지정문자 삭제(trim)


SELECT
   ltrim('MMMIMLLER', 'M'), -- 왼쪽부터 M을 다 지움
   ltrim('MMMIMLLER', 'MI'), -- 왼쪽부터 M과, I를 다 지움
   rtrim('MILLRERRR', 'R')     -- 오른쪽부터 R을 다 지움
FROM
   dual;


SELECT
   ltrim(' MILLER '),               -- 왼쪽부터 공백을 다 지움
   length( ltrim(' MILLER ') ),   -- 오른쪽 공백은 안 지운다
   rtrim(' MILLER '),               -- 오른쪽부터 공백을 다 지움
   length( rtrim(' MILLER ') )    -- 왼쪽 공백은 안 지운다
FROM
   dual;

ltrim, rtirm

---

(13) 문자(처리) 함수 - TRIM
      : 문자열의 왼쪽/오른쪽/양쪽에서, 지정문자 삭제(trim)
        (단, 문자열의 중간은 처리못함)

문법)
TRIM( LEADING 'str' FROM 컬럼명|표현식 ) -- LEADING : 왼쪽에서 삭제
TRIM( TRAILING 'str' FROM 컬럼명|표현식 ) -- TRAILING  : 오른쪽에서 삭제
TRIM( BOTH 'str' FROM 컬럼명|표현식 )      -- BOTH : 양쪽에서 삭제
TRIM( 'str' FROM 컬럼명|표현식 )              -- BOTH는 생략가능

SELECT
   trim( '0' FROM '0001234567000' ), -- 양쪽에서 삭제 (BOTH는 생략가능)
   trim( LEADING '0' FROM '0001234567000' ), -- 왼쪽에서 삭제
   trim( TRAILING '0' FROM '0001234567000' ) -- 오른쪽에서 삭제
FROM
   dual;


------------------------------------------------------
(2) 숫자 (처리)함수 : 
 a. ROUND   - 반올림 (지정한 자리 수 이하에서 반올림)
 b. TRUNC   - 버림 (지정한 자리 수 이하에서 절삭)
 c. MOD      - 나머지 (나누기 연산을 한 후에 나머지 값을 반환)
 d. CEIL       - 올림 (주어진 숫자값보다 크거나 같은 최소 정수값을 반환)
 e. FLOOR    - 내림 ( 주어진 숫자값보다 작거나 같은 최대 정수값을 반환)
 f. SIGN       - 주어진 값이 양수인지, 음수인지, 0인지 식별할 수 있는 값을 반환
------------------------------------------------------

(1) 숫자 (처리)함수 - ROUND : 지정한 자리 수 이하에서 반올림

SELECT
    round( 456.789, 2 ),  -- 양수 : 소숫점 아래 2번째 자리까지 표시하게 반올림
    round( 456.789, -1 ), -- 음수 : 소숫점 위 첫번째 자리에서 반올림
    round( 456.789 )      -- 미지정 : 소숫점 아래에서 반올림
FROM
    dual;


--- 

(2) 숫자 (처리)함수 - TRUNC : 지정한 자리 수 이하에서 절삭

SELECT
    trunc( 456.789, 2 ),  -- 양수 : 소숫점 아래 2번째 자리까지 표시하게 절삭
    trunc( 456.789, -1 ),  -- 음수 : 소숫점 위 첫번째 자리에서 절삭
    trunc( 456.789 )      -- 미지정 : 소숫점 아래에서 절삭
FROM
    dual;


--- 

(3) 숫자 (처리)함수 - MOD (Modular) : 나누기 연산을 한 후에 나머지 값을 반환

SELECT
    mod(10, 3) ,    -- 3으로 나눈 나머지 1 반환
    mod(10, 0)      -- 0으로 나누면 : 그대로 10 반환
FROM
    dual;


-- MOD 함수의 응용
SELECT
    employee_id,
    last_name,
    salary
FROM
    employees
WHERE
    mod(employee_id, 2) = 1;     -- 사번이 홀수인 애들만 보여줌
    -- mod(employee_id, 2) != 0; -- 사번이 홀수인 애들만 보여줌
    -- mod(employee_id, 3) = 0;  -- 사번이 3의 배수인 애들만 보여줌


---

(4) 숫자 (처리)함수 - CEIL : 주어진 숫자값보다 크거나 같은 최소 정수값을 반환 (올림)

SELECT
    ceil(10.6),   -- 11
    ceil(-10.6)  -- -10
FROM
    dual;

---

(5) 숫자 (처리)함수 - FLOOR
     : 주어진 숫자값보다 작거나 같은 최대 정수값을 반환 (내림)

SELECT
    floor(10),     -- 10
    floor(10.6),   -- 10
    floor(-10.6)  -- -11
FROM
    dual;

 

ceil, floor - 올림, 버림

--- 

(6) 숫자 (처리)함수 - SIGN 
    : 주어진 값이 양수인지 음수인지 0인지 식별할 수 있는 값을 반환 

SELECT
    sign(100),   -- 1 : 양수라는 의미
    sign(-20),   -- -1 : 음수라는 의미
    sign(0)      --  0 : 0이라는 의미
FROM
    dual;

SELECT
    employee_id,
    last_name,
    sign(salary)
FROM
    employees

-- 이렇게 조회하면 salary 칼럼에 다 1 출력됨 (다 양수니깐)


-- SIGN 함수의 응용 (부등호 표시 가능)
SELECT
    employee_id,
    last_name,
    salary
FROM
    employees
WHERE
   sign(salary - 15000) = 1;       -- salary > 15000 라는 의미
   -- sign(salary - 15000) != -1;  -- salary >= 15000 라는 의미

sign 함수의 응용 - 부등호 표시 가능

-- if salay > 15000 와 같음
-- sign 함수는, 비교연산자를 대체할 수 있음(>,<,=,>=,<=)



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

(3) 날짜 (처리)함수 : 날짜 데이터 타입 컬럼에 사용하기 위한 함수
    a. SYSDATE                 - DB서버에 설정된 날짜를 반환
    b. MONTH_BETWEEN    - 두 날짜 사이의 월수를 계산하여 반환
    c. ADD_MONTHS          - 특정 개월수를 더한 날짜를 계산하여 반환
                                    - 음수값을 지정하면 뺀 날짜를 반환
    d. NEXT_DAY               - 명시된 날짜로부터, 다음 요일에 대한 날짜 반환
    e. LAST_DAY                - 지정된 월의 마지막 날짜 반환
                                    - 윤년 및 평년 모두 자동으로 계산
    f. ROUND                   - 날짜를 가장 가까운 년도 또는 월로 반올림하여 반환
    g. TRUNC                   - 날짜를 가장 가까운 년도 또는 월로 절삭하여 반환

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

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

ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI:SS'; 
-- 출력방식을 VS방식에서 오라클 방식으로 바꾸고, 초까지 나오게끔 바꿈

SELECT
    sysdate,     -- GMT(영국) 기준
    current_date, -- 이게 현재위치(서울) 기준
    to_char(current_date, 'YYYY/MM/DD HH24:MI:SS') AS now
FROM
    dual;

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

(1) 날짜 (처리)함수 - SYSDATE : DB서버에 설정된 날짜를 반환

SELECT
    sysdate   -- 우리는 클라우드라 sysdate 쓰면 GMT 기준으로 나온다
FROM
    dual;

* 날짜 연산 (page 43참고)
a. 날짜 + 숫자: 날짜에 일수를 더하여 반환
b. 날짜 - 숫자: 날짜에 일수를 빼고 반환
c. 날짜 - 날짜: 두 날짜의 차이(일수) 반환
d. 날짜 + 숫자/24: 날짜에 시간을 더한다


SELECT
    sysdate     AS 오늘, 
    sysdate + 1 AS 내일,    -- 현재날짜 + 하루
    sysdate - 1 AS 어제     -- 현재날짜 - 하루
FROM
    dual;


SELECT
    last_name,
    hire_date,
    sysdate - hire_date,            -- 현재날짜 - 채용일자 = 기간(일수)
    (sysdate - hire_date) / 365,   -- 근속기간(일수) / 365 = 근속년수(소숫점포함)
    trunc( (sysdate - hire_date) / 365 ) AS 근속년수 -- trunc()로 소숫점 아래에서 절삭
FROM
    employees
ORDER BY
    3 DESC; -- 내림차순 정렬

sysdate 함수의 응용

--- 

(2) 날짜 (처리)함수 - 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;


--- 

(3) 날짜 (처리)함수 - ADD_MONTHS
     : 특정 개월수를 더한 날짜를 계산하여 반환
      음수값을 지정하면 뺀 날짜를 반환

SELECT
    sysdate AS 오늘,
    add_months(sysdate, 1) AS "1개월후 오늘",   -- 현재날짜 + 1개월
    add_months(sysdate,-1) AS "1개월전 오늘"    -- 현재날짜 - 1개월
FROM
    dual;

add_months - 1달 더하는 함수

SELECT
    sysdate AS 오늘,
    add_months(sysdate, (80-32)*12) AS "80살까지 살때 사망날짜"
FROM
    dual;