4. 단일행 함수, 다중행 함수

함수

사용 위치 : select 컬럼명, having, order by , where 연산자 조건 등등 대체로 다 사용됨. - 논리적으로 사용하기 

1. 단일 행 함수
   • 문자형
     • upper, lower, initcap
     • concat, substr
     • length, lpad, rpad
     • ltrim, rtrim
     • replace, translate
       
       
   • 숫자형
     • round(반올림), trunc(버림), mod(나머지)
     • power(제곱), sqrt(루트), sign(양,음,0), chr(인코딩)
       
       
   • 날짜형
     • sysdate, last_day
     • months_between, add_months
     • round, trunc
       
       
   • 변환형
     • To_Number
     •  To_char
       • 요소: 9,   0,   $,   ㅣ,   . ,   ,
     • To_Date
       • 요소: YYYY, MM , etc
         
         
2. 다중 행 함수
   • 그룹함수
     • Count(*) - null 포함
     • Count(), avg(), sum(), min(), max() stddev(), variance() - null 포함 안함

 

 

1. 단일행 함수

 

• dual
  • Dual은 오라클 데이터베이스에서 제공하는 가상의 테이블
  • 단일 행을 포함하는 테이블
  • Dual 테이블은 테이블의 이름이나 기타 정보를 테스트하거나 데이터를 생성하기 위해 사용

SELECT * from dual;

 

1.1. 문자형

• upper, lower, initcap

SELECT UPPER('oracle server') as upper from dual; -- 대문자
SELECT LOWER('ORACLE SERVER') as lower from dual; -- 소문자
SELECT INITCAP('ORACLE server') as initCap from dual; -- 첫문자만 대문자

 

• concat, substr

SELECT CONCAT('ORACLE ', 'server') as concat from dual; -- 문자열 합하기

SELECT SUBSTR('1234567', -2, 2) as substr from dual; -- -1부터 끝에서 부터 시작
SELECT SUBSTR('1234567', 0, 2) as substr from dual; --  * 0 을 기입 시 index 1 취급
SELECT SUBSTR('1234567', 1, 2) as substr from dual; -- 처음은 0 || 1부터 시작
SELECT SUBSTR('1234567', 3) as substr from dual; -- index는 1부터 시작. 끝에 안쓰면 끝까지 읽음

 

• length, lpad, rpad

SELECT LENGTH('abc') from dual; -- 길이 문자열

SELECT LPAD('abc',10,'z') from dual; -- 왼쪽에 채움
SELECT LPAD('abc',10,'아') from dual; -- 한글은 한문자당 2문자 취급
SELECT LPAD('abc',10,'') from dual; -- null 나옴

SELECT rpad('abc',10,'z') from dual; -- 오른쪽에 채움


-- dual이 아닌 실제 코드라고 할 경우
-- lpad 뿐만이 아니라 나머지 함수도 가능
SELECT LPAD(name, 10, 'a') FROM s_emp;

 

• ltrim, rtrim

SELECT LTRIM('aaabac','a') from dual; -- 왼쪽부터 지울 때 a를 삭제하다가 없으면 멈춤. 중간에 끼어있는건 못건들임
SELECT LTRIM('aaabac','ab') from dual; -- ab연결된 걸 지우는게 아니라 왼쪽부터 지울 때 a,b 존재하면 다 없앰
SELECT RTRIM('aaabac','ac') from dual; -- 오른쪽부터 지움

 

• replace, translate
  • translate : 문자열로 변경을 해도, 문자로 1:1 매칭
  • replace : 문자열 자체를 대치

-- translate: char
SELECT TRANSLATE ('aabbcc','c','z') from dual;
SELECT TRANSLATE('aabbccaa','aaads','Zdfasdfasfasd') from dual; -- char 1개만 적용

-- replace: string
SELECT replace ('aabbcc','cc','z') from dual; -- char도 되고 string도 됨
SELECT replace ('aabbcc','cc','zasdfasf') from dual; 
SELECT replace ('aabbcc','ccc','asdfasz') from dual;

SELECT TRANSLATE ('helllllllleeeee', 'el', '12') from dual; -- h12222222211111
SELECT replace ('helllllllleeeee', 'el', '12') from dual;   -- h12llllllleeeee

 

1.2. 숫자형 함수

• round(반올림), trunc(버림), mod(나머지)

SELECT ROUND(35.12312, 4)  from dual; -- 반올림
SELECT TRUNC(35.12369, 4)  from dual; -- 내림
SELECT MOD(35, 4)  from dual; -- 나머지

 

• power(제곱), sqrt(루트), sign(양,음,0), chr(인코딩)

SELECT POWER(2,3) from dual; -- 3제곱
SELECT POWER(3,2) from dual; -- 제곱

SELECT SQRT(9) from dual; -- 제곱근

SELECT SIGN(-15) from dual;-- 양수 음수판별기 
SELECT SIGN(15) from dual;-- 양수 음수판별기 
SELECT SIGN(0) from dual;-- 0판별기

SELECT CHR(69) from dual; -- 해당 char 숫자에 인코딩된 char 출력

 

1.3. 날짜형 함수

• sysdate, last_day

SELECT SYSDATE from dual;
-- 현재 날짜 시간 
-- mac은 docker로 연결되서 내부 설정 변경 필요

SELECT LAST_DAY('12/02/01') from dual; -- 현재 월 마지막 날짜

 

• months_between, add_months

SELECT MONTHS_BETWEEN('19/02/01','15/06/25') from dual; -- 12.3 이렇게 소수점으로 나옴

SELECT TO_NUMBER(TO_CHAR(SYSDATE, 'J')) - TO_NUMBER(TO_CHAR(TO_DATE('2015-06-25', 'YYYY-MM-DD'), 'J')) FROM dual;
-- 일로 나타냄. j: oracle에서 제공하는 기준 날짜이다.

SELECT ADD_MONTHS('19/02/01', 5) from dual;

 

• round, trunc

-- 수학 반올림, 내림 적용, day만 week으로 생각해서 반올림, 내림을 한다.
TO_DATE ('문자열', '형식 ex) yyyy-MM-dd')

-- 날짜 반올림
SELECT ROUND(TO_DATE('19/02/16'), 'month') from dual; -- 날짜 기준으로 반올림. 자동형변환 안되므로 string인 날짜를 to_date로 수동 형변환 필요
SELECT ROUND(TO_DATE('2019/06/17','yyyy-MM-dd'), 'month') from dual; -- month를 day를 보고 반올림
SELECT ROUND(TO_DATE('2019/07/1','yyyy-MM-dd'), 'year') from dual; -- year를 month를 보고 반올림
SELECT ROUND(TO_DATE('2023/04/6','yyyy-MM-dd'), 'day') from dual; -- 일월화수목금토를 기준으로 반올림 

-- 날짜 내림
SELECT TRUNC(TO_DATE('1919/07/30','yyyy-MM-dd'), 'month') from dual; -- month 기준으로 아래꺼는 다 내림. 자동형변환 안되므로 string인 날짜를 to_date로 수동 형변환 필요
SELECT TRUNC(TO_DATE('1919/08/16','yyyy-MM-dd'), 'year') from dual; -- year 기준으로 아래꺼는 다 내림.
SELECT TRUNC(TO_DATE('2023/04/16','yyyy-MM-dd'), 'day') from dual; -- day를 week로 생각, trunc가 내림이니깐 해당 주 day는 그 주 일요일로 넘어간다.


-- 날짜에 대한 산술연
SELECT SYSDATE - 1 from dual;
SELECT TO_DATE('12/02/01', 'yyyy-MM-dd') - 1 from dual;  -- sysDate 아닌 이상 to_date 로 형변환필요
SELECT TO_DATE('12/02/01', 'yyyy-MM-dd') - TO_DATE('12/01/01', 'yyyy-MM-dd') from dual; -- 일로 나옴

 

1.4. 변환형 함수

 

• To_Number
•  To_char
• To_Date

 

• 요소
  • to_char : 9 , 0, $, ㅣ, . , ,
  • to_date : scc, YEAR, YYYY, YY, MONTH, MON, MIM, DAY, DY, DDD,DD,D HH, HH24, MI, SS, AM, PM

 

• To_char

SELECT TO_CHAR(1234, 'L999,999') from dual; -- L은 지역 화폐 단위
SELECT TO_CHAR(SYSDATE , 'YYYY/MM/DD') from dual; -- L은 지역 화폐 단위
SELECT TO_CHAR(SYSDATE , 'YYYY-MM-DD') from dual; -- L은 지역 화폐 단위

 

• To_Number

SELECT TO_NUMBER('122020') from dual;

 

• To_Date

SELECT TO_DATE('12 5월 2020', 'dd MONTH YYYY') from dual;  -- DDD, DD, D 차이점

 

2. 다중 행 함수

• 그룹함수
  • Count(*) - null 포함
  • Count(), avg(), sum(), min(), max() stddev(), variance() - null 포함 안함

-- group 함수 안 쓸 때 - column명을 명시 못함
SELECT SUM(se.SALARY) 
FROM S_EMP se 
;

-- group 함수 쓸 때 -- column명 명시 가능
SELECT se.DEPT_ID , SUM(se.SALARY) 
FROM S_EMP se
group by se.dept_id 
;