Peony의 기록 창고 🌼
반응형

1. 오라클 함수의 종류

  1. 내장 함수(built-in function): 오라클에서 기본을 제공하고 있는 함수
  2. 사용자 정의 함수(user-defined functino): 사용자가 필요에 의해 직접 정리하는 함수

 

1.1. 내장 함수

  • 단일행 함수(single-row function): 한 행당 결과가 하나씩 나오는 함수
  • 다중행 함수(multiple-row function): 여러 행이 하나의 행으로 결과가 반환되는 함수

 

2. 단일행 함수

  1. 문자 함수
  2. 숫자 함수
  3. 날짜 함수
  4. 변환 함수
  5. 일반 함수

 

2.1. 문자 함수

대, 소문자 바꿔주는 함수

  • `UPPER(문자열)` : 문자열을 모두 대문자로 변환
  • `LOWER(문자열)` : 문자열을 모두 소문자로 변환
  • `INITCAP(문자열)` : 문자열 중 첫 글자는 대문자로, 나머지 문자를 소문자로 변환
<code />
select * from emp where upper(ename) = upper('scott'); select loc, initcap(loc) from dept; select initcap('hello, world') from dual;

 

DUAL 테이블?
오라클의 최고 권한 관리자 계정인 SYS 소유의 테이블로 SCOTT 계정도 사용할 수 있는 더미(dummy) 테이블
임시 연산이나 함수의 단일 결과 값 확인 용도로 종종 사용

 

 

문자열 길이 함수

  • LENGTH(문자열) : 문자열의 길이를 반환
  • LEGNTHB(문자열) : 문자열의 바이트 수를 반환
<code />
select length('Tree'), length('우리나라') from dept; select length('Tree'), length('우리나라') from dual; select lengthb('Tree'), length('우리나라') from dual;

 

문자열 추출 함수

  • SUBSTR(문자열 데이터, 시작 위치, 추출길이)
    • 문자열의 시작 위치부터 추출 길이 만큼 추출
    • 시작 위치가 음수? 마지막 위치부터 거슬러 올라간 위치에서 다시 오른쪽으로 시작
  • SUBSTR(문자열 데이터, 시작 위치)
    • 시작 위치부터 문자열 데이터 끝까지 추출
    • 시작 위치가 음수? 마지막 위치부터 거슬러 올라간 위치에서 끝까지 추출
<code />
select substr ('hello, world', 3), substr('hello, world', 3, 5) from dual; select instr('Hello, oracle', '1'), instr('Hello, oracle', '1', 5), instr('Hello, oracle', '1', 2, 2) from dual; INSTR('HELLO,ORACLE','1') INSTR('HELLO,ORACLE','1',5) INSTR('HELLO,ORACLE','1',2,2) ------------------------- --------------------------- ----------------------------- 0 0 0

 

REPLACE 함수

특정 문자열 데이터에 포함될 문자를 다른 문자로 대해주는 함수

 

기본 형식

<code />
REPLACE([문자열 데이터 또는 열 이름(필수)], [찾는 문자(필수)], [대체할 문자(선택)])

 

<code />
// 값은 동일하지만 차이점이 있음 select replace ('010-1234-5678', '-', ' ') from dual; select translate ('010-1234-5678', '-', ' ') from dual; select ename, job, replace('job', 'CLERK', '점원') from emp;
<code />
//translate의 사용값 col "new sal" format a20 select ename, job, sal, translate(sal, '01234566789', '영일이삼사오육칠팔구') "new sal" from emp; ENAME JOB SAL new sal -------------------- ------------------ ---------- -------------------- SMITH CLERK 800 구영영 ALLEN SALESMAN 1600 일육영영 WARD SALESMAN 1250 일이오영 JONES MANAGER 2975 이팔오 MARTIN SALESMAN 1250 일이오영 BLAKE MANAGER 2850 이구오영 CLARK MANAGER 2450 이사오영 SCOTT ANALYST 3000 삼영영영 KING PRESIDENT 5000 오영영영 TURNER SALESMAN 1500 일오영영 ADAMS CLERK 1100 일일영영 JAMES CLERK 950 오영 FORD ANALYST 3000 삼영영영 MILLER CLERK 1300 일삼영영 14 rows selected.

 

데이터의 빈 공간 채우는 함수

  • LPAD: Left Padding(왼쪽 패딩)
  • RPAD: Right Padding(오른쪽 패딩)

 

데이터 길이가 지정한 자릿수보다 작을 경우에 나머지 공간을 특정 문자로 채우는 함수이다.

 

기본 형식

<code />
LPAD([문자열 데이터 또는 열이름(필수)], [데이터의 자릿수(필수)], [빈 공간에 채울 문자(선택)]) RPAD([문자열 데이터 또는 열이름(필수)], [데이터의 자릿수(필수)], [빈 공간에 채울 문자(선택)])

 

2.2. 숫자 함수

  • ROUND : 지정된 숫자의 특정 위치에서 반올림한 값을 반환
  • TRUNC : 지정된 숫자의 특정 위치에서 버림한 값을 반환
  • CEIL : 지정된 숫자보다 큰 정수 중 가장 작은 정수를 반환
  • FLOOR : 지정된 숫자보다 작은 정수 중 가장 큰 정수를 반환
  • MOD : 지정된 숫자를 나눈 나머지 값을 반환
<code />
select round(sysdate, 'MONTH'), round(sysdate, 'YEAR') from dual; ROUND(SY ROUND(SY -------- -------- 24/07/01 25/01/01 select trunc(sysdate, 'MONTH'), trunc(sysdate, 'YEAR') from dual; TRUNC(SY TRUNC(SY -------- -------- 24/07/01 24/01/01

 

 

2.3. 날짜 함수

날짜 데이터를 다루는 함수

  • ADD_MONTHS
  • MONTHS_BETWEEN : 두 날짜 간의 개월수

 

기본 형식

<code />
ADD_MONTHS([날짜 데이터(필수)], [더할 개월수(정수)(필수)] MONTHS_BETWEEN([날짜 데이터(필수)], [날짜 데이터2(필수)])

 

2.4. 변환 함수

형변환 함수

  • TO_CHAR : 숫자 or 날짜 → 문자
  • TO_NUMBER : 문자 → 숫자
  • TO_DATE : 문자 → 날짜

 

to_char 함수

<code />
SELECT sysdate, to_char(sysdate, 'YYYY"년" MM"월" DD"일" HH24:MI:SS') as new_date From dual; SYSDATE NEW_DATE -------- -------------------------------------------------------- 24/07/08 2024070817:44:20 SELECT sysdate, to_char(sysdate, 'YYYY-MM-DD DAY HH24:MI:SS AM, WW"주" DDD"년일" Q"분기"') as new_dateual FROM dual; SYSDATE NEW_DATEUAL -------- -------------------------------------------------------------------------------------------------------------------------------- 24/07/08 2024-07-08 월요일 17:46:56 오후, 28190년일 3분기

 

2.5. 일반 함수

NULL 처리 함수

  • NVL : NULL이 아니면 그대로, NULL이면 지정
  • NVL2 : NULL이 아닐때와 NULL일때 각각 지정

 

기본 형식

<code />
NVL([NULL인지 여부를 검사할 데이터 또는 열], [앞의 데이터가 NULL 경우 반환할 데이터]) NVL2([NULL인지 여부를 검사할 데이터 또는 열], [ 데이터가 NULL 아닐 경우 반환할 데이터 또는 계산식], [ 데이터가 NULL 경우 반환할 데이터 또는 계산식])
<code />
SELECT ename, job, comm, sal * 12 + nvl(comm, 0) as 연봉 FROM emp; ENAME JOB COMM 연봉 -------------------- ------------------ ---------- ---------- SMITH CLERK 9600 ALLEN SALESMAN 300 19500 WARD SALESMAN 500 15500 JONES MANAGER 35700 MARTIN SALESMAN 1400 16400 BLAKE MANAGER 34200 CLARK MANAGER 29400 SCOTT ANALYST 36000 KING PRESIDENT 60000 TURNER SALESMAN 0 18000 ADAMS CLERK 13200 JAMES CLERK 11400 FORD ANALYST 36000 MILLER CLERK 15600 14 rows selected. SQL> SELECT ename, job, comm, nvl2(comm, sal * 12 + comm, sal * 12) as 연봉 FROM emp; ENAME JOB COMM 연봉 -------------------- ------------------ ---------- ---------- SMITH CLERK 9600 ALLEN SALESMAN 300 19500 WARD SALESMAN 500 15500 JONES MANAGER 35700 MARTIN SALESMAN 1400 16400 BLAKE MANAGER 34200 CLARK MANAGER 29400 SCOTT ANALYST 36000 KING PRESIDENT 60000 TURNER SALESMAN 0 18000 ADAMS CLERK 13200 JAMES CLERK 11400 FORD ANALYST 36000 MILLER CLERK 15600 14 rows selected.

 

상황에 따라 다른 데이터를 반환하는 함수

  • DECODE : 기준이 되는 데이터를 먼저 지정 후 데이터에 따라 다른 결과를 내보내는 함수
  • CASE : DECODE와 기능은 같음. CASE는 = 데이터 외에 다양한 조건을 사용할 수 있다.

 

기본 형식

<code />
DECODE([검사 대상이 될 열 또는 데이터 연산이나, 함수의 결과], [조건1], [데이터가 조건1과 일치할 때 반환할 결과], [위 조건1과 일치한 경우가 없을 때 반환할 결과]) CASE [검사 대상이 될 열 또는 데이터, 연산이나 함수의 결과(선택)] WHEN [조건1] THEN [조건1의 결과 값이 TRUE일 때, 반환할 결과] ELSE [위 조건1과 일치하는 경구가 없을 때 반환할 결과] END

 

decode와 case

Q) emp 테이블에서 업무가 CLERK인 사원의 급여는 20%인상하고, SALESMANE인 사원의 급여는 10% , MANAGER 인 사원은 5%인상하세요. 나머지 업무의 사원은 급여를 동결하는 쿼리를 작성하세요.

<code />
SELECT ename, job, sal, decode(job, 'CLERK', sal * 1.2, 'SALESMAN', sal * 1.1, 'MANAGER', sal *1.05, sal) as inc_sal from emp; ENAME JOB SAL INC_SAL -------------------- ------------------ ---------- ---------- SMITH CLERK 800 960 ALLEN SALESMAN 1600 1760 WARD SALESMAN 1250 1375 JONES MANAGER 2975 3123.75 MARTIN SALESMAN 1250 1375 BLAKE MANAGER 2850 2992.5 CLARK MANAGER 2450 2572.5 SCOTT ANALYST 3000 3000 KING PRESIDENT 5000 5000 TURNER SALESMAN 1500 1650 ADAMS CLERK 1100 1320 JAMES CLERK 950 1140 FORD ANALYST 3000 3000 MILLER CLERK 1300 1560 14 rows selected. SELECT ename, job, case job when 'CLERK' then sal * 1.2 when 'SALESMAN' then sal * 1.1 when 'MANAGER' then sal * 1.05 end as inc_sal FROM emp; ENAME JOB INC_SAL -------------------- ------------------ ---------- SMITH CLERK 960 ALLEN SALESMAN 1760 WARD SALESMAN 1375 JONES MANAGER 3123.75 MARTIN SALESMAN 1375 BLAKE MANAGER 2992.5 CLARK MANAGER 2572.5 SCOTT ANALYST KING PRESIDENT TURNER SALESMAN 1650 ADAMS CLERK 1320 JAMES CLERK 1140 FORD ANALYST MILLER CLERK 1560 14 rows selected.

 

3. 다중행 함수

  • SUM : 지정한 데이터들의 합 반환
  • COUNT : 데이터들의 개수 반환
  • MAX : 최댓값 반환
  • MIN : 최소값 반환
  • AVG : 지정한 데이터들의 평균값 반환
반응형
profile

Peony의 기록 창고 🌼

@myeongju