오라클 함수- 내장 함수 : 단일행 함수

※함수란?

함수(function)는 수학에서 정의한 개념으로 x와 y 변수가 존재하고 x값이 변하면 그 변화에 따라 어떤 연산 또는 가공을 거쳐 y값고 함께 변할 때 이 y를 함수라고 한다.

x값의 변화에 따라 y값이 종속적으로 변하기때문에 '따름수'라고도 한다.

특정한 결과 값을 얻기 위해 데이터를 입력할 수 있는 특수 명령어를 의미

 

내장 함수 ( built-in function)

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

 

단일행 함수(single-row function)

데이터가 한 행씩 입력되고 입력된 한 행당 결과가 하나씩 나오는 함수

---

 문자함수

• 문자 데이터를 가공하거나 문자 데이터로부터 특정 결과를 얻고자 할 때 사용하는 함수이다.

 

① 대, 소 문자를 바꿔주는 UPPER, LOWER, INITCAP 함수

 

함수	설명
UPPER(문자열)	괄호 안 문자 데이터를 모두 대문자로 변환하여 반환
LOWER(문자열)	괄호 안 문자 데이터를 모두 소문자로 변환하여 반환
INITCAP(문자열)	괄호 안 문자 데이터 중 첫 글자는 대문자로,나머지 문자를 소문자로 변환 후 반환

문자 함수

• 문자 함수는 입력 데이터에 열 이름이나 데이터를 직접 지정해줘야 한다.
• 문자 함수는 게시판의 글 제목이나 글 본문과 같이 가변 길이 문자열 데이터에서 특정 문자열을 포함하는 데이터를 조회할 때 사용한다.

※ 문자열 데이터를 조회할 때 아래와 같이 LIKE 연산자와 와일드카드를 함께 사용할 수도 있다.

   하지만 ORACLE, oracle, OrAcLe과ㅏ 같이 대, 소문자가 다른 여러 가지 경우의 'Oracle' 단어를 찾아낼 수가 없다.

SELECT*
FROM 게시판테이블
WHERE 게시판 제목 열 LIKE %Oracle%
   or 게시판 본문 열 LIKE %Oracle%

 

▶ 이럴 때 문자 함수를 이용해 원하는 데이터를 조회할 수 있다.

 

※ 양쪽 항목의 문자열 데이터를 모두 대문자나 소문자로 바꿔서 비교하면 실제 검색어의 대, 소문자 여부와 상관없이

   검색 단어와 일치한 문자열을 포함한 데이터를 찾을 수 있다

 

② 문자열 길이를 구하는 LENGTH 함수

※ 위의 SELECT문의 경우 각 행별 사원 이름이 몇 글자인지 출력된다.

WHERE절에 사용하면 문자열 길이를 비교하여 행을 조회할 수도 있다.

나오는 결괏값이 숫자이므로 숫자 비교가 가능하다.

ex) 사원 이름의 길이가 5 이상인(이름이 5글자 이상인) 행 출력하기

SELECT ENAME, LENGTH(ENAME)
	FROM EMP
    WHERE LENGTH(ENAME) >= 5;

②-1 LENGTH 함수와 LENGTHB 함수 비교하기 

LENGTHB 함수는 문자열의 바이트 수를 반환하는데 내 컴퓨터는 AL32 UTF8이라서 한글 1 글자당 3byte여서 

 6이나 왔다.

 

▶ DB캐릭터 셋 확인하기

select * from nls_database_parameters where parameter = 'NLS_CHARACTERSET';


KO16KSC5601
완성형 한글입니다. 가장 많이 사용됩니다.
2,350자의 한글과 4,888자의 한자, 영문, 기호 및 히라가나,가타카나를 표현합니다.(한글 바이트 : 2btye)

KO16MSWIN949
조합형 한글입니다. 완성형을 포함하여 11,172자의 한글을 표현합니다.(한글 바이트 : 2byte)

AL32UTF8
유니코드의 CES중의 한 부분입니다. 11,172자의 한글을 표현합니다.(한글 바이트: 3byte)

 

③ 문자열 일부를 추출하는 SUBSTR 함수

▶ 주문 등록번호 중 생년월일 앞자리만 필요하거나 전화번호의 마지막 네 자리 숫자만 추출하는 경우와 같이 문자열 중 일부를 추출할 때 사용한다.

함수	설명
SUBSTR(문자열 데이터, 시작 위치, 추출길이)	문자열 데이터의 시작 위치부터 추출 길이만큼 추출한다. 시작 위치가 음수일 경우에는  마자막 위치부터 거슬러 올라간 위치에서 시작한다.
SUBSTR(문자열 데이터, 시작 위치	문자열 데이터의 시작 위치부터 문자열 데이터 끝까지 추출한다. 시작 위치가 음수일 경우에는 마지막 위치부터 거슬러 올라간 위치에서 끝까지 추출한다.

 

-

 

③-1 SUBSTR 함수와 LENGTH 함수와 함께 사용하기

 

음수일 경우에는 시작을 반대에서 한다

④ 문자열 데이터 안에서 특정 문자 위치를 찾는 INSTR 함수

•  문자열 데이터 안에 특정 문자나 문자열이 어디에 포함되어 있는지를 알고자 할 때 사용한다.

▶ 기본형식

INSTR([대상문자열 데이터(필수)],
	  [위치를 찾으려는 부분 문자(필수)],
      [위치 찾기를 시작할 대상 문자열 데이터 위치(선택, 기본값은 1)],
      [시작 위치에서 찾으려는 문자가 몇번째인지 지정(선택, 기본값은 1)])

※ 음수일 경우는 반대로 오른쪽부터 시작되며, 찾으려는 문자가 문자열 데이터에 포함되어 있지 않다면 0을 반환한다.

그래서 INSTR 함수를 LIKE와 비슷한 용도로 사용할 수도 있다.

 

※ 같은 결과가 출력된다. 자주 사용하는 표현은 아니지만 이런 형태로 응용할 수도 있다.

 

⑤특정 문자를 다른 문자로 바꾸는 REPLACE 함수

REPLACE 함수는 특정 문자열 데이터에 포함된 문자를 다른 문자로 대체할 경우에 유용한 함수이다.

REPLACE([문자열 데이터 또는 열 이름(필수)], [찾는 문자(필수)], [대체할 문자(선택)])
-- 대체할 문자를 입력하지 않으면 '찾는 문자'로 지정한 문자는 문자열 데이터에서 삭제된다.

※ REPLACE 함수는 카드 번호나, 주민번호, 계좌번호, 휴대전화 번호 또는 2017-12-25이나 13:59:23과 같이 날짜나 시간을 나타내는 데이터처럼 특정 문자가 중간중간 끼어있는 데이터에서 해당 문자를 없애거나 다른 문자로 바꾸어 출력할 때 종종 사용한다.

 

⑥ 데이터의 빈 공간을 특정 문자로 채우는 LPAD, RPAD 함수

LPAD : Left Padding(왼쪽 패딩)을 뜻한다. 남은 빈 공간을 왼쪽에 채운다.

RPAD : Right Padding(오른쪽 패딩) 을 뜻한다. 남은 빈 공간을 오른쪽에 채운다.

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

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

※ 빈 공간에 채울 문자를 지정하지 않으면 빈 공간의 자릿수만큼 공백 문자로 띄운다.

 

이러한 문자열 데이터를 특정 문자로 채우는 패딩 처리는 데이터의 이름만 노출해야 하는 개인정보를 출력할 때 다음과 같이 사용한다.

 

⑦ 두문자 열 데이터를 합치는 CONCAT 함수와 | | 연산자

• 두 개의 문자열 데이터를 하나의 데이터로 연결해 주는 역할을 한다.
• 두개의 입력 데이터 지정을 하고 열이나 문자열 데이터 모두 지정할 수 있다.

⑧ 특정 문자를 지우는 TRIM, LTRIM, RTRIM 함수

원본 문자열 데이터를 제외한 나머지 데이터는 모두 생략할 수 있다.

삭제할 문자가 생략될 경우에 기본적으로 공백을 제거한다.

삭제 옵션은 왼쪽에 있는 글자를 지우는 LEADING, 오른쪽에 있는 글자를 지우는 TRAILING, 

양쪽의 글자를 모두 지우는 BOTH를 사용한다.

 

⑧-1  TRIM 함수의 기본 사용법

TRIM([삭제 옵션(선택)] [삭제할 문자(선택)] FROM [원본 문자열 데이터(필수)])

TRIM 함수 사용하기 (삭제할 문자가 없을 때)

공백 제거하여 출력하기

TRIM 함수 사용하기 (삭제할 문자가 있을 때)

삭제할 문자 ' _ '삭제후 출력하기

⑧-2  LTRIM, RTRIM 함수의 기본 사용법

LTRIM([원본 문자열 데이터(필수)], [삭제할 문자 집합(선택)])
--원본 문자열의 왼쪽에서 삭제할 문자열을 지정합니다.(삭제할 문자열을 지정하지 않으면 공백이 삭제됨)

RTRIM([원본 문자열 데이터(필수)], [삭제할 문자 집합(선택)])
--원본 문자열의 오른쪽에서 삭제할 문자열을 지정합니다.(삭제할 문자열을 지정하지 않으면 공백이 삭제됨)

 

TRIM, LTRIM, RTRIM 사용하여 문자열 출력하기

 

삭제할 문자열을 지정하지 않았을 경우 함수 종류에 따라 양쪽, 왼쪽, 오른쪽 공백이 제거되었다.

LTRIM과 RTRIM을 사용한 예시에서 삭제 대상이 문자일 경우 해당 문자의 순서와 반복을 통해 만들어 낼 수 있는

모든 조합이 삭제된다. 

위 그림처럼 LTRIM함수는 문자열의 왼쪽에 삭제할 문자열과 같은 문자가 포함된 문자들은 모두 삭제되었다.

(삭제할 문자가 '<_ '  면 '<',  '_'가 따로 포함된 문자도 삭제된다.) 

삭제해 가다가 삭제할 문자가 아닌 문자가 나오면 삭제 작업이 끝난다. 그래서 오른쪽의 문자열은 삭제되지 않았다.

 

TRIM 함수는 검색 기준이 되는 데이터에 혹시나 들어있을지도 모르는 양쪽 끝의 공백을 제거할 때 많이 사용한다.

예를 들어 유저가 로그인을 하려고 아이디를 입력했을 때 사용자 실수로 스페이스바가 눌러져 공백이 함께 입력되는

경우이다.