안녕하세요, 코린이의 코딩 학습기 채니 입니다.
개인 포스팅용으로 내용에 오류 및 잘못된 정보가 있을 수 있습니다.
FUNCTION
- 일련의 작업 절차를 모아 놓은 database 객체
- 호출 시 인자를 전달하고 리턴 값을 받아 처리
- SQL의 function은 반드시 리턴 값을 가짐
| 함수 유형 | |
| 단일행 처리 함수 |
문자 처리 함수 |
| 숫자 처리 함수 | |
| 날짜 처리 함수 | |
| 형변환 처리 함수 | |
| 기타 함수 | |
| 그룹 처리 함수 |
그룹마다 호출 |
단일행 처리 함수
- 매 행마다 함수 호출, 리턴된 값을 결과 집합에 반영
문자 처리 함수
☞ length(value) , lengthb(value)
- length : value의 글자 수 반환
- lengthb : value의 실제 차지한 데이터 크기
select
emp_name 사원명, length(emp_name) 사원명length, lengthb(emp_name) 사원명lengthb,
email 이메일, length(email) 이메일length, lengthb(email) 이메일lengthb
from
employee;
length() 함수는 실제 입력된 value의 글자 수를 반환하는 함수이고,
lengthb()함수는 value의 실제 차지한 데이터의 크기를 반환하는 함수입니다.
한글은 1글자 당 3byte를 차지 하기 때문에 사원명의 lengthb는 9byte로 출력되는 것을 확인할 수 있습니다.
☞ instr(value, search, [position], [occurrence])
- value에서 search의 인덱스 반환
- [position] : 검색 시작 인덱스
- [occurrence] : 출현 횟수
select
instr('정보교육원 국가정보원 정보문화사', '정보') 첫번째,
instr('정보교육원 국가정보원 정보문화사', '정보', 1, 1) 두번째,
instr('정보교육원 국가정보원 정보문화사', '정보', 1, 3) 세번째,
instr('정보교육원 국가정보원 정보문화사', '정보', -1) 네번째 -- 뒤에서부터 찾음
from
dual;
☞ substr(value, position, [length])
- value에서 position부터 [length]개 잘라서 문자열 반환
select
substr('show me the money', 6, 2) 첫번째,
substr('show me the money', 6) 두번째, -- 개수 생략 시 끝까지 잘라옴
substr('show me the money', -5) 세번째
from
dual;
☞ lpad(value, len, [padding_str]), rapd(value, len, [padding_str])
- lpad : len에서 value의 길이를 뺀 만큼 [padding_str]를 왼쪽에 채워서 처리
- rapd : len에서 value의 길이를 뺀 만큼 [padding_str]를 오른쪽에 채워서 처리
select
lpad('123', 5, '0'),
lpad('123', 10, '0'),
rpad('123', 5, '0'),
rpad('123', 10, '0')
from
dual;
select
rpad('990119-', 14, '*') as rpad_ssn,
rpad('010-1234-', 13, '*') as rpad_phone
from
dual;
데이터의 일부만 노출해야 하는 개인 정보 출력 시에 유용할 것 같습니다.
☞ replace(문자열데이터 또는 열 이름, 찾는문자, [대체문자])
- 특정 문자열 데이터에 포함된 문자를 다른 문자로 대체
- 대체할 문자를 입력하지 않으면, 찾는 문자로 지정한 문자는 데이터에서 삭제
select
'010-1234-1234' as replace_before,
replace('010-1234-1234', '-', ' ') as replace_1,
replace('010-1234-1234', '-') as replace_2
from
dual;
replace_1은 '-'를 ' ' 공백으로 대체하였기 때문에 '010 1234 1234' 출력,
replace_2는 '-'에 대한 대체 문자를 지정하지 않았으므로 삭제 되어 '01012341234'가 출력 되었습니다.
☞ concat(value, value)
- 두 개의 문자열 데이터를 하나의 데이터로 연결 (|| 연산자와 유사)
select
concat(emp_id, emp_name),
concat(emp_id, concat(' : ', emp_name))
from
employee
where
emp_name = '선동일';
☞ trim([삭제옵션] [삭제할문자] from value)
- 문자열 데이터 내에서 특정 문자를 지우기 위해 사용
- 삭제할 문자 생략 시, 공백 제거
select
'[' || trim(' _ _ oracle _ _ ') || ']' as trim,
'[' || trim(leading from ' _ _ oracle _ _ ') || ']' as trim_leading,
'[' || trim(trailing from ' _ _ oracle _ _ ') || ']' as trim_trailing,
'[' || trim(both from ' _ _ oracle _ _ ') || ']' as trim_both
from
dual;
삭제할 문자 생략했으므로, 공백을 제거하고 있으며 각 삭제 옵션 별로 왼쪽/오른쪽/양쪽의 공백이 삭제 되는 것이 확인 됩니다.
select
'[' || trim('_' from '_ _ oracle _ _') || ']' as trim,
'[' || trim(leading '_' from '_ _ oracle _ _') || ']' as trim_leading,
'[' || trim(trailing '_' from '_ _ oracle _ _') || ']' as trim_trailing,
'[' || trim(both '_' from '_ _ oracle _ _') || ']' as trim_both
from
dual;
삭제할 문자('_') 지정하니 옵션에 따라 왼쪽/오른쪽/양쪽의 '_'가 삭제 되는 것을 확인할 수 있습니다.
☞ ltrim(value, [삭제할문자], rtrim(value, [삭제할문자])
- 각각 왼쪽, 오른쪽의 지정 문자를 삭제
- 삭제할 문자 생략 시, 공백 문자 삭제
- trim 함수와 달리 삭제할 문자를 여러 개로 지정 가능
select
'[' || trim(' _oracle_ ') || ']' as trim,
'[' || ltrim(' _oracle_ ') || ']' as ltrim,
'[' || ltrim('<_oracle_>', '_<') || ']' as ltrim_2,
'[' || rtrim(' _oracle_ ') || ']' as rtrim,
'[' || rtrim('<_oracle_>', '>_') || ']' as rtrim_2
from
dual;
삭제 대상이 문자일 경우, 해당 문자의 순서와 반복을 통해 만들어 낼 수 있는 모든 조합이 각각 왼쪽/오른쪽에서 삭제 되어 갑니다.
따라서 원본 문자열이 '<_<_oracle'일 경우 → 'oracle' 출력
원본 문자열이 '<_o<_racle'일 경우 → 'o<_racle' 출력 됩니다.
@실습문제
1. 사원 테이블에서 이메일 @ 앞의 아이디를 조회 (사원명, 아이디)
select
emp_name 사원명,
substr(email, 1, instr(email, '@')-1) 아이디
from
employee;
2. 사원 테이블에서 여자 사원만 조회 (사원명, 주민번호)
- 성별 컬럼은 존재하지 않으므로 주민번호 뒤 첫번째 자리 참고
select
emp_name 사원명,
emp_no 주민번호
from
employee
where
substr(emp_no, instr(emp_no, '-')+1, 1) in ('2', '4');
