220701 TIL

국비교육 23일차 Oracle : Commit/Rollback, like연산자, 단일행 문자함수, 단일행 숫자함수, 단일행 날짜 함수, case when/decode

1. Commit, Rollback

• DML(Data Manuplation Language 데이터 조작어)
  • insert(입력), update(수정), delete(삭제), merge(병합)
• DML은 기본이 수동 커밋(commit)이다.
• 수동 커밋이므로 디스크에 저장되어진 파일에 저장시키려면 반드시 commit을 해주어야 한다.
• 커밋 전에는 메모리(RAM)에만 올라가기 때문에 디스크에 올리기 위해서 커밋을 해주어야 한다.

• Commit
  commit은 DML문(insert,update,delete,merge)으로 변경된 테이블의 데이터를 실제 디스크 파일에 저장(적용)시켜주는 명령어이다.
• Rollback
  • 메모리에만 올라가있는 데이터를 취소하는 것
  • rollback은 commit한 이후로 실행된 DML문(insert,update,delete,merge)으로 변경된 테이블의 데이터를 실제 디스크 파일에 저장(적용)시켜주지 않고, 취소시키는 명령어이다.
  • 이미 commit되어진 DML문은 rollback이 불가하다.
  • 메모리에 있는 데이터에 적용되는 것이지 커밋 이후에는 소용 없다.

2. like 연산자

1) like와 % _

like 연산자와 함께 사용되어지는 % 와 _를 wild character라고 부른다.
- % : 글자수와는 관계 없이 글자가 있든지 글자가 없든지 관계없다
- _ : 아무 글자 1개

• 사용예시

-- hr 테이블
-- employees 테이블에서 first_name 컬럼의 값이 's'로 끝나는 사원들만
-- 사원번호, 이름, 성, 기본급여를 나타내세요. 

select employee_id , first_name , last_name , salary
from employees
where first_name like '%s';

-- employees 테이블에서 last_name 컬럼의 값이 첫글자는 'F' 이고 두번째 글자는 아무거나 이고
-- 세번째 글자는 소문자 'e' 이며 4번째 부터는 글자가 있든지 없든지 상관없는 사원들만 
-- 사원번호, 이름, 성, 기본급여를 나타내세요. 
select employee_id , first_name , last_name , salary
from employees 
where last_name like 'F_e%'; 

2) escape

• escape의 사용 방법

create table tbl_test
(name Nvarchar2(10)
,pct  Nvarchar2(10)
);

insert into tbl_test(name, pct) values('일번', '일번은 10점 이다.');
insert into tbl_test(name, pct) values('이번', '이번은 10% 이다.');

commit;

-- pct 컬럼에서 10% 라는 글자가 들어가 있는 행만 찾기
select *
from tbl_test
where pct like '%10\%%' escape '\';

• escape 문자로 '\'를 주었으므로 '\'다음에 나오는 wild character(% _) 문자 1개만 wild character 기능에서 탈출시켜버린다.
• 숫자나 문자 등 상관 없이 escape 문자로 사용할 수 있다. 하지만 보통은 특수기호 사용.

3. 단일행 함수 - 문자 함수

1) upper('문자열')

• '문자열' 을 모두 대문자로 변환시켜준다.

select 'SumMer', upper('SumMer')
from dual;

2) lower('문자열')

• '문자열' 을 모두 소문자로 변환시켜준다.

-- hr 테이블
select *
from employees
where lower(last_name) = lower('King');

• 뭔가 검색할 때는 찾는 문자열을 전부 대문자 혹은 소문자로 맞춰 준 후에 찾는다.

3) initcap('문자열')

• '문자열' 을 단어별(구분자 공백)로 첫글자만 대문자, 나머지는 소문자로 변환시켜준다.

select 'HOT SumMer', initcap('HOT SumMer')
from dual;
-- HOT SumMer	Hot Summer

4) substr('문자열', 시작글자번호, 뽑아야할 글자 길이)

• '문자열' 중에 문자열의 특정 일부분을 선택해올 때 사용한다.
• '뽑아야할 글자 길이'를 입력하지 않으면 시작 글자 번호부터 끝까지 선택한다.
• 오라클은 1부터 idenx가 시작한다.

select '더운여름'
      , substr('더운여름', 3, 2)
      , substr('더운여름', 3)
from dual;
-- 더운여름	여름  	여름

5) instr('문자열', '찾는 문자열', '출발점', '몇번째에 나오는 '찾는 문자열'을 찾는지')

• 어떤 문자열에서 명명된 문자열의 위치를 알려주는 것

select '더운여름 진짜더운여름 여름너무더워'
      , instr('더운여름 진짜더운여름 여름너무더워', '여름', 1, 2)
from dual;
-- 1에서 시작해서 2번째로 나오는 '여름'의 index

select '더운여름 진짜더운여름 여름너무더워'
      , instr('더운여름 진짜더운여름 여름너무더워', '여름', -1, 2)
from dual;
-- 뒤에서 첫번째 자리에서 시작해서 2번째로 나오는 '여름'의 index
-- 음수를 넣으면 출발점이 뒤에서 부터 시작한다. 

6) reverse('문자열')

• 어떤 문자열을 거꾸로 보여주는 것
• 한글은 오류

 select 'ORACLE', reverse('ORACLE')
 from dual;
-- ORACLE ELCARO

7) lpad('문자열', 전체 byte, '공백을 채울 문자열')

• '문자열'을 입력하고 왼쪽부터 전체 byte에서 '문자열'을 입력하고 남은 byte까지 '공백을 채울 문자열'로 채운다.
  -- 한글을 2byte로 인식

select lpad('여름여행', 20, '*')  
from dual;
-- ************여름여행	

8) rpad('문자열', 전체 byte, '공백을 채울 문자열')

• '문자열'을 입력하고 오른쪽부터 전체 byte에서 '문자열'을 입력하고 남은 byte까지 '공백을 채울 문자열'로 채운다.

select rpad('여름여행', 20, '*')   
from dual;
-- 여름여행************

9) trim 제거

• ltrim('문자열', '제거할 문자') : 왼쪽부터 문자를 제거
• rtrim('문자열', '제거할 문자') : 오른쪽부터 문자를 제거
• trim('문자열') : 왼쪽, 오른쪽 공백을 제거

select ltrim('aabccaddTaaaacddSSS','abcd'),
       rtrim('aacccaddTaaabddd','abcd'),
       rtrim(ltrim('aabccaddTaaaacdd','abcd'),'abcd')
from dual;
-- TaaaaccbdddSSS		aabcabccccaddT	T

• 제거할 글자가 없다면 ltrim, rtrim도 공백만 제거한다.

10) translate

select translate('010-1234-1234'
                ,'0123456789'
                ,'영일이삼사오육칠팔구')
from dual;
-- 일대일로 데이터를 매핑
-- 영일영-일이삼사-일이삼사

11) replace('문자열', '찾을 문자열', '바꿀 문자열')

select replace('더운여름 진짜더운여름 여름너무더워'
	  , '여름' 
	  , 'SUMMER')
from dual;

12) length('문자열')

• 문자열 길이 확인

3. 단일행 함수 - 숫자 함수

1) mod : 나머지 구하기

select 5/2, mod(5,2)
from dual;
-- 2.5	1

2) round(숫자, 자릿수) : 반올림

select 94.547
     , round(94.547)         -- 95
     , round(94.547, 0)      -- 95       0 은 정수 1자리까지만 나타내어준다.
     , round(94.547, 1)      -- 94.5     1 은 소수 첫째자리까지만 나타내어준다.
     , round(94.547, 2)      -- 94.55    2 은 소수 둘째자리까지만 나타내어준다.
     , round(94.547, -1)     -- 90       -1 은 정수 10자리까지만 나타내어준다.
     , round(94.547, -2)     -- 100      -2 은 정수 100자리까지만 나타내어준다.
from dual;

3) trunc(숫자, 자릿수) : 절삭

select 94.547
     , trunc(94.547)         -- 94
     , trunc(94.547, 0)      -- 94       0 은 정수 1자리까지만 나타내어준다.
     , trunc(94.547, 1)      -- 94.5     1 은 소수 첫째자리까지만 나타내어준다.
     , trunc(94.547, 2)      -- 94.54    2 은 소수 둘째자리까지만 나타내어준다.
     , trunc(94.547, -1)     -- 90       -1 은 정수 10자리까지만 나타내어준다.
     , trunc(94.547, -2)     -- 0        -2 은 정수 100자리까지만 나타내어준다.
from dual;

4) power : 거듭제곱

select 2*2*2*2*2, power(2,5)    -- 2의 5승
from dual;

5) sqrt : 제곱근(루트)

select sqrt(16), sqrt(2), sqrt(3)
from dual;

6) sin, cos, tan, asin, acos, atan

select sin(90), cos(90), tan(90), asin(0.3), acos(0.3), atan(0.3)
from dual;

7) log

select log(10, 100)
from dual;

8) sign

• 결과값이 양수라면 1, 결과값이 0이라면 0, 결과값이 음수라면 -1

select sign(5-2), sign(5-5), sign(2-5)
from dual;

9) ceil

• ceil(실수) ==> 입력되어진 실수보다 큰 최소의 정수를 나타내어준다.
• ceil(정수) ==> 입력되어진 정수를 그대로 나타내어준다.

select ceil(10.1), ceil(10), ceil(-10.1), ceil(-10)
from dual;
-- 11	10	-10	-10

10) floor

• floor(실수) ==> 입력되어진 실수보다 작은 최대의 정수를 나타내어준다.
• floor(정수) ==> 입력되어진 정수를 그대로 나타내어준다.

select floor(10.1), floor(10), floor(-10.1), floor(-10)
from dual;
-- 10	10	-11	-10

11) ascii, chr

select ascii('A'), ascii('a'), ascii('0'), ascii(' ')
from dual;
-- 65	97	48	32

select chr(65), chr(97), chr(48), chr(32)
from dual;
-- A	a	0	 ' '

4. 단일행 함수 - 날짜 함수

• 날짜1 + 숫자 = 날짜2 ==> 날짜1에서 숫자(일수)만큼 더한 값이 날짜2가 된다.
• 날짜1 - 숫자 = 날짜2 ==> 날짜1에서 숫자(일수)만큼 뺀 값이 날짜2가 된다.
• ★여기서 중요한 것은 숫자의 단위가 일수이다. ★

1) to_yminterval , to_dainterval

• to_yminterval : 연 과 월을 나타내어 연산자가 + 이면 날짜에서 더해주는 것
• to_dsinterval : 일 시간 분 초를 나타내어 연산자가 + 이면 날짜에서 더해주는 것
• 연산자가 - 면 날짜를 빼주는 것이다.

select to_char(sysdate + to_yminterval('01-02') + to_dsinterval('003 04:05:06'), 
        'yyyy-mm-dd hh24:mi:ss') AS "1년 2개월 3일 4시간 5분 6초 뒤"
from dual;

5. 기타 함수

1) ★ case when then else end ★

select case 5-2	-- 이 결과가 when과 같다면
       when 4 then '5-2=4입니다.'  
	   -- when이 참이면 then 뒤에를 보여주고 여기서 끝남. 거짓이라면 다음
       when 1 then '5-2=1입니다.'
       when 3 then '5-2=3입니다.'
       else '전부 다 아님'    -- 전부 다 아니라면 else
       end AS 결과
from dual;

select case 
       when 4>5 then '4는 5보다 큽니다.'  
       when 5>7 then '5는 7보다 큽니다.'
       when 3>2 then '3은 2보다 큽니다.'
       else '전부 다 아님'         
       end AS 결과
from dual;

2) decode

• case when과 동일하나, 오라클에서만 사용

select decode(5-2, 4, '5-2=4입니다.'
                 , 1, '5-2=1입니다.'
                 , 3, '5-2=3입니다.'
                    , '전부 다 아님' ) AS 결과
from dual;