[DB]4. Oracle 함수

[DB]4. Oracle 함수

 

● 함수: 특별한 목적의 작업을 수행하기 위해 독립적으로 설계된 프로그램 코드의 집합

-내장함수: 오라클에서 기본적으로 제공하고 있는 함수

-사용자 정의 함수: 사용자가 필요에 의해 직접 정의한 함수

 

1. 문자 함수(130p.)

 1)UPPER/LOWER

  ● UPPER: 데이터를 모두 대문자로 변환하여 반환

  ● LOWER: 데이터를 모두 소문자로 변환하여 반환

  *사용법: SELECT UPPER(데이터), LOWER(데이터)

 

 

[실습1]

--이름은 대문자로 변환, 이메일은 소문자로 변환해서 출력
select upper(fist_name), lower(email) from employees;

 2)LENGTH : 문자열의 길이를 구하는 함수

  - LENGTH(데이터)

   -WHERE절 조건에서도 쓰일 수 있음

 

--이름, 이름의 길이 출력
select first_name, length(first_name) from employees;

--이름의 길이가 8이상인 직원만 출력
select first_name, length(first_name) from employees where length(first_name) >=8;

 3)SUBSTR: 문자열의 일부를 추출하는 함수

   ● SUBSTR(데이터, 시작위치): 시작위치부터 끝까지 추출

   ● SUBSTR(데이터, 시작위치, 추출길이): 시작위치부터 추출할 길이만큼 추출

  *SUBSTR 여러 개 사용할 경우 ,(콤마) 입력하기!

select job_id
,SUBSTR(job_id, 1,2) AS 앞
,SUBSTR(job_id,4) AS 뒤
from employees;

[실습2]

--입사일을 (23/01/01) 형태로 출력
SELECT HIRE_DATE 
        ,SUBSTR(HIRE_DATE, 1,2) AS 연도
        ,SUBSTR(HIRE_DATE, 4, 2) AS 월
        ,SUBSTR(HIRE_DATE, 7,2) AS 일
FROM EMPLOYEES;

 

 4)REPLACE: 문자열을 대체하는 함수

  - REPLACE(데이터, 찾을 문자, 대체할 문자)

   -필수 공백이면 찾을 문자가 제거됨

--입사일에서 슬래시를 하이픈으로 대체하기, 제거하기
SELECT HIRE_DATE
        ,REPLACE(HIRE_DATE, '/','-') AS 하이픈
        ,REPLACE(HIRE_DATE, '/') AS 제거
FROM EMPLOYEES;

5)CONCAT: 문자열 합치는 함수

  -CONCAT(문자열1, 문자열2)

  -문자열의 개수는 최대 2개만 입력할 수 있음. 문자열3은 불가

 

--입사일은 '입사일' 출력
SELECT CONCAT('입사일은', HIRE_DATE)
FROM EMPLOYEES;

--|| 연산자: 문자열 연결( Shift 원화 표시)
--,콤마 없이 ||로 연결(제한없음)

SELECT 'DLQTKDLFDMS' || HIRE_DATE || '입니다'
FROM EMPLOYEES;

 

6)TRIM: 특정 문자열 지우는 함수

  TRIM(삭제 옵션, 삭제할 문자 FROM 문자열)

 

  (삭제 옵션)

  -LEADING 왼쪽 글자 지우기

  -TRAILING 오른쪽 글자 지우기

  -BOTH 양쪽 글자 지우기

 

--공백제거: TRIM(문자열) 

SELECT '['|| ' ' || '공백' || ' ' || ' ]' AS "공백 제거 전"
    , '['|| TRIM(' ' || '공백' || ' ') || ' ]' AS "공백 제거 후"
FROM DUAL;

 

--문자열 제거: TRIM(삭제 옵션 제거할 문자 FROM 문자열)

SELECT '1문자1' AS "제거 전"
       , TRIM(LEADING '1'  FROM '1문자1') AS "ㅣLEADING 왼쪽글자제거"
       ,TRIM(TRAILING '1' FROM '1문자1') AS "TRAILING 오른쪽글자제거"
       ,TRIM(BOTH '1' FROM '1문자1') AS "BOTH 양쪽 글자제거"
FROM DUAL;

 

2. 숫자 함수

  1)MOD: 나머지 반환하는 함수, 홀짝 구분 시 사용

    MOD(나눗셈 될 숫자, 나눌 숫자)

 

SELECT MOD(15,2) AS 홀수, MOD(14,2) AS 짝수 FROM DUAL;

  2)ROUND 함수: 반올림하는 숫자

    ROUND(반올림 할 숫자, (선택)반올림 위치)

반올림 위치	옵션
자연수 둘째 자리 반올림(1234.5678>1200)	-2
자연수 첫째 자리 반올림(1234.5678>1230)	-1
소수점 첫째 자리 반올림(1234.5678>1235)	0
소수점 둘째 자리 반올림(1234.5678>1234.6)	1
소수점 셋째  자리 반올림(1234.5678(1234.57)	2

 

  3)TRUNC: 버림 함수

   -TRUNC(버림할 숫자, (선택)버림위치)

   -옵션은 ROUND옵션과 동일함

--소수점 첫째 자리에서 반올림/버림
SELECT ROUND(15.6), TRUNC(15.6) FROM DUAL;
--소수점 둘째 자리에서 반올림/버림
SELECT ROUND(15.65, 1), TRUNC(15.65, 1) FROM DUAL;

 

 

3. 날짜 함수

  1)SYSDATE: 오라클 DB가 있는 OS 의 현재 날짜와 시간을 알려주는 함수

SELECT SYSDATE FROM DUAL;

SELECT SYSDATE
    ,SYSDATE + 1 AS "하루더함"
    ,SYSDATE + 1/24 AS "한시간더함"
    ,SYSDATE + 1/24/60 AS "1분더함"
    ,SYSDATE + 1/24/60/60 AS "1초더함"
    
    FROM DUAL;

 

 2)ADD_MONTHS: 몇 개월 이후의 날짜를 구하는 함수

  ADD_MONTHS(날짜, 더할/뺄 개월 수)

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

 

4. 형변환 함수(158p.)

  1)명시적 형변환: 형변환 함수 사용 할 때 변환

  ● TO_CHAR: 문자로 변환하는 함수

     TO_CHAR(데이터, 출력형태)* 옵션 162p.

SELECT SYSDATE, TO_CHAR(SYSDATE, 'YYYY/MM/DD/DAY') FROM DUAL;
SELECT SALARY, TO_CHAR(SALARY, 'L999,999') FROM EMPLOYEES;

 ● TO_NUMBER: 숫자로 변환하는 함수

   TO_NUMBER(‘데이터’)+변환할 숫자

SELECT TO_NUMBER('1')+1 FROM DUAL;

 ● TO_DATE: 날짜로 변환하는 함수

   TO_DATE(‘데이터’, 변환할 날짜 형태)

SELECT TO_DATE('20230504', 'YY-MM-DD') FROM DUAL;

2)암시적 형변환: 자동으로 형변환

--우선순위: 날짜>숫자>문자
--우선순위가 낮은 문자부터 형변환

 

5. NULL 함수

  ●NVL(데이터/컬럼, NULL일 경우 반환할 값)

    -데이터나 컬럼에 NULL이 있으면 지정한 값으로 대체하여 반환

 

--직원의 이름을 출력, 만약 NULL이 있으면 '이름없음'으로 대체해서 출력
SELECT FIRST_NAME, NVL(FIRST_NAME, '이름없음')
FROM EMPLOYEES;

  ●NVL2(1데이터 /컬럼, NULL이 아닐경우 반환할 값, NULL일 경우 반환할 값)

--직원의 이름을 출력, 만약 NULL이 아니라면 '이름있음', NULL이라면 '이름없음'으로 출력
SELECT FIRST_NAME, NVL2(FIRST_NAME, '이름있음', '이름없음')
FROM EMPLOYEES;

[실습3]

--커미션이 NULL일 때 0으로 대체해서 출력

SELECT COMMISSION_PCT, NVL(COMMISSION_PCT, 0)
FROM EMPLOYEES;

--매니저가 있는 직원은 직원으로 매니저가 없는 직원은 관리자로 출력
SELECT MANAGER_ID, NVL2(MANAGER_ID, '직원', '관리자')
FROM EMPLOYEES;

 

 

6. 조건함수

   DECODE(검사대상, 비교값, 일치 시 반환 값, 일치하지 않을 때 반환값)

--부서에 따라서 급여 인상률 변경
SELECT DEPARTMENT_ID, SALARY, 
    DECODE(DEPARTMENT_ID 
        , 100 , SALARY*2 
        , 90, SALARY*1.9
        , 80, SALARY*1.8
        ,SALARY) AS UPSALAY
FROM EMPLOYEES;

 

7. 그룹함수(177p.)

  ● SUM: 합

  ● COUNT: 개수

  ● MIN: 최소값

  ● MAX: 최대값

  ● AVG: 평균

 

--급여의 총 합을 구하시오.
SELECT SUM(SALARY) FROM EMPLOYEES;
SELECT DISTINCT SUM(SALARY) FROM EMPLOYEES;

--커미션 비율이 NULL아닌 개수를 구하시오.
SELECT COUNT(COMMISSION_PCT) FROM EMPLOYEES
WHERE COMMISSION_PCT IS NOT NULL ;

--급여 최소값, 최대값, 평균
SELECT MIN(SALARY) FROM EMPLOYEES;
SELECT MAX(SALARY) FROM EMPLOYEES;
SELECT AVG(SALARY) FROM EMPLOYEES;

8. 정렬 함수 ORDER BY(88p.)

 - ORDER BY 정렬할 컬럼 정렬옵션(오름차순, 내림차순)

  -옵션: 오름차순 ASC, 내림차순 DESC

 

SELECT department_id
FROM EMPLOYEES
ORDER BY DEPARTMENT_ID DESC;