SQL FUNCTION2

직원들의 주민번호를 조회하여 사원명, 생년, 생월, 생일을 각각 분리하여 조회 컬럼의 별칭은 사원명, 생년, 생월, 생일로 한다. SELECT

SELECT
    EMP_NAME 사원명
    ,SUBSTR(EMP_NO, 1,2) 생년
    ,SUBSTR(EMP_NO, 3,2) 생월
    ,SUBSTR(EMP_NO, 5,2) 생일
    FROM EMPLOYEE;

---

날짜 데이터 직원들의 입사일에서 입사년도, 입사월, 입사 날짜를 분리하여 조회

SELECT
    HIRE_DATE
    ,SUBSTR(HIRE_DATE,1,2)입사년도
     ,SUBSTR(HIRE_DATE,4,2)입사월
      ,SUBSTR(HIRE_DATE,7,2)입사일
FROM EMPLOYEE;

WHERE절에도 함수를 사용할 수 있다. EMP_NO를 통해 성별을 판단하여 여성 직원들이 모든 컬럼 정보를 조회한다.

WHERE SUBSTR(EMP_NO,8,1)='2';

SELECT
    *
    FROM EMPLOYEE
    WHERE SUBSTR(EMP_NO,8,1)='2';

 

WHERE절에는 단일행 함수만 사용 가능
사원명, 주민번호 조회. 주민번호는 생년월일만 보이게 하고 '-' 다음의 값은 '*'로 바꿔 출력. 14개로 고정, 1부터 7까지 그 뒤는 *로 하겠다. 필요에 따라 중첩적으로 사용가능.	EMP_NAME     ,RPAD(SUBSTR(EMP_NO, 1, 7), 14,'*')

SELECT
    *
    FROM EMPLOYEE
    WHERE AVG(SALARY) > 100;
    --에러
-- 함수 중첩 사용 가능 : 함수 안에서 함수를 사용할 수 있다.
-- 사원명, 주민번호 조회. 주민번호는 생년월일만 보이게 하고 '-' 다음의 값은 '*'로 바꿔 출력.
--14개로 고정, 1부터 7까지 그 뒤는 *로 하겠다. 필요에 따라 중첩적으로 사용가능.

SELECT
    EMP_NAME
    ,RPAD(SUBSTR(EMP_NO, 1, 7), 14,'*')
    FROM EMPLOYEE;

사원명, 이메일, 이메일의 @이후를 제외한 아이디 조회

,SUBSTR(EMAIL,1, INSTR(EMAIL, '@')-1)     FROM EMPLOYEE;

SELECT
    EMP_NAME
    ,EMAIL
    --맨 앞에서부터 시작해서 골뱅이 앞(-1)까지
    ,SUBSTR(EMAIL,1, INSTR(EMAIL, '@')-1)
    FROM EMPLOYEE;

 

SUBSTRB : 바이트 단위로 추출하는 함수

SUBSTR('ORACLE', 3, 2)      , SUBSTRB('ORACLE', 3, 2)

SELECT 
       SUBSTR('ORACLE', 3, 2)
     , SUBSTRB('ORACLE', 3, 2)
  FROM DUAL;

SELECT 
       SUBSTR('오라클', 2, 2)
     , SUBSTRB('오라클', 4, 6)
  FROM DUAL;

---

LOWER / UPPER / INITCAP	대소문자 변경해주는 함수,앞글자만 대문자
LOWER(문자열 | 컬럼)	소문자로 변경해주는 함수
UPPER(문자열 | 컬럼)	대문자로 변경해주는 함수
INITCAP(문자열 | 컬럼)	앞 글자만 대문자로 변경해주는 함수

SELECT
       LOWER('Welcome To My World')
  FROM DUAL;

SELECT
       UPPER('Welcome To My World')
  FROM DUAL;

SELECT
       INITCAP('welcome to my world')
  FROM DUAL;

CONCAT

문자열 혹은 컬럼 두 개를 입력 받아  하나로 합친 후 리턴 /사실 연결 연산자 사용해도 무방

SELECT
       CONCAT('가나다라', 'ABCD')
  FROM DUAL;

SELECT
       '가나다라' || 'ABCD'
  FROM DUAL;

 

REPLACE

컬럼 혹은 문자열을 입력 받아 변경하고자 하는 문자열을 변경하려고 하는 문자열로 바꾼 후 리턴

SELECT
       REPLACE('서울시 강남구 역삼동', '역삼동', '삼성동')
  FROM DUAL;

 

숫자 처리 함수	ABS, MOD, ROUND, FLOOR, TRUNC, CEIL
ABS(숫자 | 숫자로 된 컬럼명)	절대값 구하는 함수

SELECT
       ABS(-10)
     , ABS(10)
  FROM DUAL;

MOD(숫자 | 숫자로 된 컬럼명, 숫자 | 숫자로 된 컬럼명)

두 수를 나누어서 나머지를 구하는 함수 처음 인자는 나누어지는 수, 두 번째 인자는 나눌 수

SELECT 
       MOD(10, 5)
     , MOD(10, 3)
  FROM DUAL;

ROUND(숫자 | 숫자로 된 컬럼명, [위치])

반올림해서 리턴하는 함수

SELECT ROUND(123.456) FROM DUAL;
SELECT ROUND(123.456, 0) FROM DUAL;
SELECT ROUND(123.456, 1) FROM DUAL;
SELECT ROUND(123.456, 2) FROM DUAL;
SELECT ROUND(123.456, -2) FROM DUAL;

FLOOR(숫자 | 숫자로 된 컬럼명)

내림처리하는 함수

SELECT FLOOR(123.456) FROM DUAL;
SELECT FLOOR(123.678) FROM DUAL;

 

TRUNC(숫자 | 숫자로 된 컬럼명, [위치])

내림처리(절삭) 함수

SELECT TRUNC(123.456) FROM DUAL;
SELECT TRUNC(123.678) FROM DUAL;
SELECT TRUNC(123.456, 1) FROM DUAL;
SELECT TRUNC(123.456, 2) FROM DUAL;
SELECT TRUNC(123.456, -1) FROM DUAL;

CEIL(숫자 | 숫자로 된 컬럼명)

올림 처리 함수

SELECT CEIL(123.456) FROM DUAL;
SELECT CEIL(123.678) FROM DUAL;

SELECT
       ROUND(123.456)
     , FLOOR(123.456)
     , TRUNC(123.456)
     , CEIL(123.456)
  FROM DUAL;

날짜 처리 함수

SYSDATE

시스템에 저장되어 있는 날짜를 반환하는 함수

SELECT 
SYSDATE 
FROM DUAL;

MONTHS_BETWEEN(날짜,날짜)	두 날짜의 대월 수 차이를 숫자로 리턴
CEIL 소수점 처리	,CEIL(MONTHS_BETWEEN(SYSDATE, HIRE_DATE))

--CEIL 소수점 처리
SELECT
    EMP_NAME
    ,HIRE_DATE
    ,CEIL(MONTHS_BETWEEN(SYSDATE, HIRE_DATE))
    FROM EMPLOYEE;

ADD_MOMTH(날짜,숫자)

날짜에 숫자만큼 개월 수 더해서 리턴

SELECT
    ADD_MONTHS(SYSDATE,5)
    FROM DUAL;

근무년수가 20년 이상인 직원의 모든 컬럼 조회

WHERE ADD_MONTHS(HIRE_DATE,240) <= SYSDATE;

SELECT
    *
    FROM EMPLOYEE
--   WHERE MONTHS_BETWEEN(SYSDATE, HIRE_DATE) >= 240; 숫자로반환
--날짜에다가 숫자를 더해서 날짜로 반환 최종 날짜값이 반환
    WHERE ADD_MONTHS(HIRE_DATE,240) <= SYSDATE;

NEXT_DAY(기준날짜, 요일(문자 |숫자))

기준 날짜에서 구하려는 요일에 가장 가까운 날짜 리턴

 SELECT 
 SYSDATE
 , NEXT_DAY(SYSDATE,'금요일') 
 FROM DUAL;
 
  SELECT
  SYSDATE
  , NEXT_DAY(SYSDATE,'금') 
  FROM DUAL;

--숫자는 1~7이며 일요일부터 시작한다.
   SELECT 
   SYSDATE
   , NEXT_DAY(SYSDATE,6) 
   FROM DUAL;
--시스템 환경에 따라 언어 설정되어 있으므로 변경을 원하면 설정을 변경해서 사용한다.
SELECT 
SYSDATE
, NEXT_DAY(SYSDATE,'FRIDAY') 
FROM DUAL;

ALTER SESSION SET NLS_LANGUAGE = AMERICAN;
ALTER SESSION SET NLS_LANGUAGE = KOREAN;

LAST_DAY(날짜)

해당 월의 마지막 날짜를 구하여 리턴

SELECT
SYSDATE
, LAST_DAY(SYSDATE) 
FROM DUAL;

---

사원명, 입사일, 입사한 월의 근무일수(주말 포함) 날짜-날짜 빼기 연산 가능, 했을 때 숫자가 나옴, 두개의 일 수차이가 나온다.

EMP_NAME     ,HIRE_DATE     ,LAST_DAY(HIRE_DATE) - HIRE_DATE +1 "입사월의 근무일수"

SELECT
    EMP_NAME
    ,HIRE_DATE
    ,LAST_DAY(HIRE_DATE) - HIRE_DATE +1 "입사월의 근무일수"
    FROM EMPLOYEE;

---

EXTRACT	년,월,일 정보를 추출하여 리턴하는 함수
-EXTRACT(YEAR FROM 날짜)	년도만 추출
EXTRACT(MONTH FROM 날짜)	월만 추출
EXTRACT(DAY FROM 날짜)	일만 추출

 

SELECT
    EXTRACT(YEAR FROM SYSDATE) 년도
    ,EXTRACT(MONTH FROM SYSDATE) 월
    ,EXTRACT(DAY FROM SYSDATE) 일
    FROM DUAL;

---

직원의 이름, 입사일, 근무년수 조회 근무년수는 현재년도 - 입사년도로 조회한다.

EMP_NAME     ,HIRE_DATE     ,EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM HIRE_DATE)

SELECT
    EMP_NAME
    ,HIRE_DATE
    ,EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM HIRE_DATE)
    FROM EMPLOYEE;

---

2023-1990 인경우 33이 나옴 근무년수를 만으로 계싼하는 경우에는 월의 차이를 계산해야 한다

EMP_NAME     ,HIRE_DATE     ,FLOOR (MONTHS_BETWEEN(SYSDATE,HIRE_DATE) / 12) "만 근무년수"

SELECT
    EMP_NAME
    ,HIRE_DATE
    ,FLOOR (MONTHS_BETWEEN(SYSDATE,HIRE_DATE) / 12) "만 근무년수"
    FROM EMPLOYEE;

---

형변환 함수
TO_CHAR(날짜, [포맷])	날짜형 데이터를 문자형 데이터로 변경
TO_CHAR(숫자, [포맷])	숫자형 데이터를 문자형 데이터로 변경

SELECT TO_CHAR(1234) FROM DUAL;
SELECT TO_CHAR(1234, '99999') FROM DUAL;
SELECT TO_CHAR(1234, '00000') FROM DUAL;
SELECT TO_CHAR(1234, 'L99999') FROM DUAL;
SELECT TO_CHAR(1234, '$99999') FROM DUAL;
SELECT TO_CHAR(1234, '00,000') FROM DUAL;
SELECT TO_CHAR(1234, '999') FROM DUAL;

---

직원 테이블에서 사원명, 급여 조회
급여는 '\9,000,000' 형식으로 표시	,TO_CHAR(SALARY, 'L99,999,999')

SELECT
    EMP_NAME
    ,TO_CHAR(SALARY, 'L99,999,999')
    FROM EMPLOYEE;

---

날짜 데이터 포맷 적용 시에도 TO_CHAR 함수 사용

SELECT TO_CHAR(SYSDATE, 'PM HH24:MI:SS') FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'AM HH:MI:SS') FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'MON DY, YYYY') FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'YYYY-fmMM-DD DAY') FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD DAY') FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'YEAR, Q') || '분기' FROM DUAL;

SELECT
       EMP_NAME
     , TO_CHAR(HIRE_DATE, 'YYYY-MM-DD') 입사일
  FROM EMPLOYEE;

SELECT
       EMP_NAME
     , TO_CHAR(HIRE_DATE, 'YYYY"년" MM"월" DD"일"') 입사일
  FROM EMPLOYEE;

SELECT
       EMP_NAME
     , TO_CHAR(HIRE_DATE, 'YYYY/MM/DD HH24:MI:SS') 상세입사일
  FROM EMPLOYEE;