Oracle : SQL - 내장 함수

내장 함수 

 

 < SQL 함수 >

   - 수학함수의 정의와 유사 

   - 여러 자주 사용하는 기능을 한 함수 이름을 정의해놓으면 필요할 때 불러서 사용 가능 

   - 그 중에서 이미 잘 만들어진 SQL 제공함수를 공부해보자.

 

 

 1. 숫자형 함수 

   - 입력되는 파라미터(매개변수)가 숫자인 경우, 계산결과(반환값)이 숫자인 함수를 의미 

 

   - 절대값 함수 : ABS(n) 

      • 매개변수 n의 절대값을 반환  

      • SELECT ABS(-199) FROM dual; 

        → 결과 : 199 

   - 천장 함수 : CEIL(n)

      • 매개변수 n과 같거나 큰 최소 정수 반환 = 올림 

      • SELECT CEIL(10.5) FROM dual; 

        → 결과 : 11

   - 바닥 함수 : FLOOR(n)

      • 매개변수 n과 같거나 작은 최대 정수 반환 = 내림 

      • SELECT FLOOR(10.5) FROM dual; 

        → 결과 : 10

   - 지수승 함수 (exponentiation) : POWER(x, n)  = 거듭제곱 : X^3 = X * X * X  

      • 매개변수 n만큼 밑의 수를 곱함      

      • SELECT POWER(10, 3) FROM dual; 

        → 결과 : 1000

   - 밑을 e(=2.71828183…)로 하는 지수승(=거듭제곱) : EXP(n) 

      • 매개변수 n번만큼 e를 거듭해서 곱함 

      • SELECT EXP(1) FROM dual; 

        → 결과 : 2.7182183

    - 매개변수 n의 자연로그 값을 반환 ( n > 0 ) : LN(n)

      • SELECT LN(10) FROM dual;

        → 결과 : 2.30258…

    - n2를 밑으로 하고 n1을 진수(결과값)으로 하는 Log값을 구하라 = 지수승 구하기 

        : LOG(n1, n2)

      • LOG( 10, 100 ) → 10^2 = 100이므로 LOG는 거듭제곱과 역함수 관계

        ∴ 밑(n1)을 몇 번 곱하면 진수(결과값)이 되느냐의 지수승을 구하는 함수 

        → 결과 : 2    

    - 나누기 후 나머지 반환 : MOD(n2, n1) 

      • SELECT MOD(11, 4) FROM dual;

        → 결과 : 3    

    - 반올림 함수 : ROUND(n, i) → n의 값을 (i+1)번째 소수점에서 반올림 ⇒ i번째 소수점 자리까지 나타냄  

       • SELECT ROUND(1000.15, 1) FROM dual;

        → 결과 : 1000.2   

    - 값 n의 부호 반환 : SIGN(n) → n > 0 일 경우, 1 반환 | n < 0 일 경우, -1 반환 | n = 0 일 경우, 0 반환 

       • SELECT SIGN(-100) FROM dual;

        → 결과 : -1  

    - 값 n의 제곱근 값을 반환 : SQRT(n)

       • SELECT SQRT(4) FROM dual;

        → 결과 : 2

    - 소수점 이하 n2 자리에서 버림 : TRUNC(n1, n2) → n2 생략 시, 0

       • SELECT TRUNC(10.549, 2) FROM dual;

        → 결과 : 10.54 

 

 

 

 2. 문자열 함수 

   - 데이터 처리에서 가장 높은 비율로 사용됨

   - 문자형 함수를 이용해 문자열 데이터를 변형한 결과를 반환함

   - 문자형 함수 : 입력값이 보통 문자형이고, 결과도 대부분 문자형에 해당  

   - 일부 문자열의 개수를 반환 또는 검색할 문자열이 있는 위치를 반환 등의 경우, 숫자가 반환되기도 함 

 

   - CHR(n) : n은 숫자로, n값에 해당하는 애스키코드(ASCII) 값을 돌려줌 

      • SELECT CHR(72) || CHR(105) || CHR(33) FROM dual;

        → 결과 : Hi! 

 

   - CONCAT(char1, char2) == char1 || char2

      • SELECT CONCAT(‘a’, ‘B’) FROM dual;

        → 결과 : aB

 

   - INITCAP(char) : char 문자열의 첫번째 문자를 대문자로 변환 

      • SELECT INITCAP(‘hello’) FROM dual;

        → 결과 : Hello 

 

  - LOWER(char) : char 문자열을 소문자로 변환 

      • SELECT LOWER(‘HELLO’) FROM dual;

        → 결과 : hello

 

  - UPPER(char) : char 문자열을 대문자로 변환 

      • SELECT UPPER(‘hello’) FROM dual;

        → 결과 : HELLO 

 

  - LPAD(표시할문자열, 총글자수, 채움문자열) : 왼쪽에 채움문자를 ( 총글자수 - 표시할문자열의 수 ) 만큼 채움문자열을 채워 문자열을 반환 

      • SELECT LPAD(‘Hello’, 10, ‘*’) FROM dual;

        → 결과 : *****Hello

 

  - RPAD(표시할문자열, 총글자수, 채움문자열) : 오른쪽에 채움문자를 ( 총글자수 - 표시할문자열의 수 ) 만큼 채움문자열을 채워 문자열을 반환 

      • SELECT RPAD(‘Hello’, 10, ‘*’) FROM dual;

        → 결과 : Hello*****

  ※ 글자 수를 정렬하고 싶을때, 공백으로 처리하면 됨 

 

  - LTRIM(표시할문자열, 제거할문자) : 표시할 문자열에서 왼쪽부터 제거할 문자를 제거하고 남은 문자열을 반환  

      • SELECT LTIRM(‘***Hello***’, ‘*’) FROM dual;

        → 결과 : Hello***  

 

  - RTRIM(표시할문자열, 제거할문자) : 표시할 문자열에서 오른쪽부터 제거할 문자를 제거하고 남은 문자열을 반환  

      • SELECT RTIRM(‘***Hello***’, ‘*’) FROM dual;

        → 결과 : ***Hello  

 

  - TRIM(표시할문자열) : 표시할 문자열에서 공백을 제거하고 남은 문자열을 반환  

      • SELECT TIRM(‘  Hello   ‘) FROM dual;

        → 결과 : Hello

 

  - SUBSTR(원본문자열, n1, n2) 

      • 원본문자열에서 n1 위치에서 시작하여 n2 길이만큼 잘라낸 결과 문자열을 반홤함 

      • n1이 0인 경우, n1 = 1이 적용

      • n1이 음수인 경우, 검색방향이 오른쪽 끝에서 거꾸로 계산함 

      • n2는 생략 가능 → 생략하면 n1부터 끝까지

      • n2는 0이하 값이 입력되면 값이 없음(=NULL) 반환 

      • SELECT SUBSTR(‘ABCDEFG’, 1, 3) FROM dual;   - ABC 

      • SELECT SUBSTR(‘ABCDEFG’, 1, 3) FROM dual;   - ABC 

      • SELECT SUBSTR(‘ABCDEFG’, 1, 3) FROM dual;   - ABC 

      • SELECT SUBSTR(‘ABCDEFG’, 1, 3) FROM dual;   - ABC 

      • SELECT SUBSTR(‘ABCDEFG’, 1, 3) FROM dual;   - ABC 

      • SELECT SUBSTR(‘ABCDEFG’, 1, 3) FROM dual;   - ABC 

   

 

 

 3. 문자형 함수 중 숫자값을 반환하는 함수 : 결과값이 숫자 

  - ASCII(문자) : 문자를 정의된 ASCII 코드 값을 반환함 

      • SELECT ASCII( CHR(65) ) “문자 A의 ASCII 코드 값” FROM dual ;

        → 결과 : 65

 

  - INSTR( char1, char2, n1, n2) : char1에서 char2를 찾아서 시작되는 위치 반환 

      • n1 : 처음 찾기 시작할 위치 | n2 : 전체 문자열에서 찾을 문자열의 반복되는 순서 

      • SELECT INSTR( ‘HELLO’, ‘L’, 3, 1 ) FROM dual;

        : char1(‘HELLO’)에서 char2(‘L’)을 찾는데, 3번째 문자부터 찾기 → 찾은 문자열 중 첫번째 ‘L’의 위치를 반환         

        → 결과 : 3

   * OFFSET 개념 

  - LENGTH(char) : char 문자열의 글자수의 크기를 반환 

 

 

 

---

연습

-- 11. 내장함수 
-- 연습 
SELECT abs(-1000) FROM dual;	 
SELECT abs(1000) FROM dual;	

SELECT ceil(50.0) FROM dual;
SELECT ceil(-10.5) FROM dual;

SELECT floor(10000.5) FROM dual;

-- ROUND(n, i) 함수 
-- : 값 n의 소수점 이하 (i+1)자리를 반올림하여 
--   그 결과 소수점 자리수가 i번째 자리까지 반환함 
SELECT round(10.356, 2) FROM dual; --  10.36

SELECT round(156.356, -1) FROM dual; -- 160
-- i < 0인 경우, 일의 자리(0)을 기준으로 -1경우 십의자리에 해당됨 
-- 위의 예시의 경우, 일의 자리에서 반올림한 결과 160이라는 값 반환  
SELECT round(156.356, -2) FROM dual; -- 200

-- * 최상위 자리의 반올림 : 5 이상일 경우, 올림 | 5 이하일 경우, 0으로 반환 
SELECT round(156.356, -3) FROM dual; -- 0 ( *주의* ) 
SELECT round(500.578, -3) FROM dual; -- 1000 ( *주의* )  

SELECT round(156.356, 0) FROM dual; -- 156
SELECT round(156.356, 1) FROM dual; -- 156.4
SELECT round(156.356, 2) FROM dual; -- 156.36
SELECT round(156.356, 3) FROM dual; -- 156.356

-- 12. 문자열 함수 
-- 연습 
-- * Oracle의 경우, 문자열을 왼쪽에서 오른쪽으로 읽음 
--  1) 부분 문자열 : 1부터 3개 가져오기 
SELECT SUBSTR('ABCDEFG',1,3) "부분문자열" FROM dual;  -- 결과 : ABC
--  2) 부분 문자열 : 0부터 3개 가져오기 
--     → 0부터 시작하면 데이터베이스는 일반적인 업무에서는 시작을 1로 봄 
--       ∴ 0부터 시작과 1부터 시작은 동일 
SELECT SUBSTR('ABCDEFG',0,3) "부분문자열" FROM dual;  -- 결과 : ABC
--  3) 시작위치만 지정하는 경우 : 양수 지정 (시작방향 : 왼쪽부터)
SELECT SUBSTR('ABCDEFG',1) "부분문자열" FROM dual;  -- 결과 : ABCDEFG
SELECT SUBSTR('ABCDEFG',3) "부분문자열" FROM dual;  -- 결과 : CDEFG
--  4) 시작위치만 지정하는 경우 : 음수 지정 (시작방향 : 오른쪽부터)
SELECT SUBSTR('ABCDEFG', -2) "부분문자열" FROM dual;  -- 결과 : FG 
SELECT SUBSTR('ABCDEFG', -4) "부분문자열" FROM dual;  -- 결과 : DEFG 	  
--  5) 시작위치와 길이 지정하는 경우 
SELECT SUBSTR('ABCDEFG', -4, 2) "부분문자열" FROM dual;  -- 결과 : DE	  
--  6) 길이의 경우, 음수가 될 수 없음 
SELECT SUBSTR('ABCDEFG', -2, -1) "부분문자열" FROM dual;  -- 결과 : NULL 

-- 
SELECT ASCII(CHR(65)) "문자 A의 ASCII코드 값" FROM dual; 	 

SELECT INSTR('HELLO', 'L', 3, 1) FROM dual;  -- 결과 : 3
SELECT INSTR('HELLO', 'L', 3, 2) FROM dual;  -- 결과 : 4

-- 'HELLO' 문자열에서 'L'문자 찾기 
SELECT INSTR('HELLO', 'L', 4, 1) FROM dual; -- 결과 : 4

-- 'HELLO' 문자열의 크기는?
SELECT LENGTH('HELLO') FROM dual;  -- 결과 : 5

-- 위, 아래 예시는 동일
SELECT INSTR('HELLO', 'L', INSTR('HELLO','L' ,1 ,2), 1) FROM dual; -- 결과 : 4
SELECT INSTR('HELLO', 'L', 4, 1) FROM dual; -- 결과 : 4

-- 두번째 'E'의 위치 
SELECT INSTR('WECOME TO SEOUL', 'E', 1, 2) FROM dual ;

-- 세번째 'or'의 위치 
SELECT INSTR('OR!OR@OR#OR$', 'OR', 1, 3) FROM dual;
-- * 단어로 찾을 경우, 단어의 첫 문자가 나오는 위치를 반환함 

-- 대소문자 구분 
SELECT INSTR('OR!Or!Or!OR!Or!', 'OR', 1, 2) FROM dual; -- 결과 : 10
SELECT INSTR(Upper('OR!Or!Or!OR!Or!'), 'OR', 1, 2) FROM dual; -- 결과 : 4

-- 찾는 시작 위치가 0이하인 경우 
SELECT INSTR(Upper('OR!Or@Or#OR$Or%'), 'OR', 0, 1) FROM dual; -- 결과 : 0	  
SELECT INSTR(Upper('OR!Or@Or#OR$Or%'), 'OR', -1, 1) FROM dual; -- 결과 : 13	 
SELECT INSTR(Upper('OR!Or@Or#OR$Or%'), 'OR', -1, 2) FROM dual; -- 결과 : 	10 
SELECT INSTR(Upper('OR!Or@Or#OR$Or%'), 'OR', -1, 3) FROM dual; -- 결과 : 	7 
SELECT INSTR(Upper('OR!Or@Or#OR$Or%'), 'OR', -1, 4) FROM dual; -- 결과 : 4	 
SELECT INSTR(Upper('OR!Or@Or#OR$Or%'), 'OR', -1, 5) FROM dual; -- 결과 : 	1