[Day20][Oracle] like / wild character / Data Dictionary / 기타 함수 / 문자 함수 / 숫자 함수

Ⅰ. like

 

1. like

 

-- '='와 같은 말이다.

 

HR

select *
from employees
where department_id = 80;

 

또는

 

HR

select *
from employees
where department_id like 80;

 

 

 

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

---

 

2. wild character ( %, _ )

 

HR

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

 

 

-- employees 테이블에서 first_name 컬럼의 값이 'J'로 시작하는 사원들만 사원번호, 사원이름, 사원성, 기본급여를 나타내었다.

-- first_name의 첫 글자는 'J'이고 다음 글자는 있든지 없든지 관계없다.

 

HR

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

 

-- first_name이 끝날 때 's'로 끝나는 사원들만 사원번호, 사원이름, 사원성, 기본급여를 나타내었다.

-- first_name의 끝 글자는 's'이고 이전 글자는 있든지 없든지 관계없다.

 

---

 

HR

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

 

-- first_name 값에서 이전 글자와 다음 글자가 있든지 없든지 'ee'가 포함되어야 한다.

 

HR

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

 

-- last_name에서 첫 글자는 무조건 'F'와야 하고, 두 번째 글자는 아무 글자, 세 번째 글자는 'e'어야 한다.

 

 

---

 

문제 ▷▷ employees 테이블에서 성별이 여자만 사원번호, 사원명, 주민번호를 나타내세요.

 

-- 여자는 주민번호에서 7번째 글자가 2 또는 4

 

HR

select employee_id as 사원번호
     , first_name || ' ' || last_name as 사원명
     , jubun as 주민번호
from employees
where jubun like '______2%' or
      jubun like '______4%';

 

 

---

3. %와 _의 wild character 없애기

 

HR

create table tbl_watch
(watchname Nvarchar2(10)
,bigo       Nvarchar2(100)
);

insert into tbl_watch(watchname, bigo)
values('금시계','순금 99.99% 함유 고급시계');

insert into tbl_watch(watchname, bigo)
values('은시계','고객만족도 99.99점 획득한 고급시계');

commit;

select *
from tbl_watch;

 

 

3-1. wild character 없애는 이유?

-- % 또는 _ 를 특수문자로 사용하기 위해서

 

tbl_watch 테이블에서 bigo 컬럼에 99.99%라는 글자가 들어있는 행만 추출해 보자.

 

HR

select *
from tbl_watch
where bigo like '%99.99%%';

 

-- 금시계만 추출되어야 하는데 둘 다 추출되어진다.

-- 99.99%의 문자 '%'가 아닌 wild character의 뜻으로 인식되었기 때문이다.

 

HR

select *
from tbl_watch
where bigo like '%99.99\%%' escape '\';

 

-- escape '\' 다음에 나오는 글자 1개만 wild character 기능을 잃어버린다.

-- 글자는 '\'가 아닌 다른 아무 글자나 가능하지만 영문, 숫자를 쓸 경우 가독성이 떨어지기 때문에 특수기호를 쓸 것을 권장한다.

 

 

---

 

Ⅱ. Data Dictionary(데이터사전)

 

SYS

select *
from dictionary;

또는

SYS

select *
from dict;

 

 

-- USER_...     ==> 오라클 서버에 접속한 자신의 계정에 해당하는 것. 즉, 자기 자신이 만든 것 
-- ALL_...       ==> 오라클 서버에 접속한 자신의 계정에 해당하는 것 + 자신의 것이 아닌 접근이 허락된 타인의 것
-- DBA_...       ==> 모든 오라클 사용자에 대한 것(일반사용자인 HR은 조회불가)

 

 

 

HR

select *
from dict;

 

 

-- HR은 일반 사용자 계정이므로 DBA를 조회할 수 없다.

 

 

---

 

사용자가 생성한 테이블에 대한 정보를 조회하기

 

SYS

select *
from dictionary
where table_name like 'DBA_%' and
      lower(comments) like '%table%';

 

 

-- dictionary에서 table명에 'DBA'가 포함되어 있고, comments에 'table'이 포함되어 있는 것을 조회한다.

 

 

 

SYS

select *
from dba_tables;

 

 

-- 모든 오라클 사용자가 생성한 테이블에 대한 정보를 조회한다.

 

 

 

SYS

select *
from dba_tables
where owner = 'HR';

 

 

-- HR 사용자가 생성한 테이블에 대한 정보를 조회한다.

 

 

 

SYS

select *
from dba_tables
where owner = 'TESTUSER01';

 

 

-- TESTUSER01 사용자가 생성한 테이블에 대한 정보를 조회한다.

 

---

 

자기(HR)가 생성한 테이블에 대한 정보를 조회

 

HR

select *
from dictionary
where table_name like 'USER_%' and
      lower(comments) like '%table%';

 

 

-- dictionary에서 table명에 'USER'가 포함되어 있고, comments에 'table'이 포함되어 있는 것을 조회한다.

 

 

 

HR

select *
from user_tables;

 

 

---

 

 

모든 오라클 사용자에 대해 할당량(quota) 조회

 

SYS

select *
from dictionary
where table_name like 'DBA_%' and
      lower(comments) like '%quota%';

 

 

-- dictionary에서 table명에 'DBA'가 포함되어 있고, comments에 'quota'이 포함되어 있는 것을 조회한다.

 

 

 

SYS

select *
from dba_ts_quotas;

 

 

 

 

SYS

select *
from dba_ts_quotas
where username = 'HR';

 

 

-- HR 사용자에 대한 할당량을 조회한다.

 

---

 

 

HR 할당량 조회

 

SYS

select *
from dictionary
where table_name like 'USER_%' and
      lower(comments) like '%quota%';

 

 

-- dictionary에서 table명에 'USER'가 포함되어 있고, comments에 'quota'이 포함되어 있는 것을 조회한다.

 

 

 

SYS      
      
select *
from user_ts_quotas;

 

 

---

 

문제 ▷▷ 모든 오라클 사용자에 대해 부여해준 system privilege(시스템 권한) 조회

 

SYS

select *
from dictionary
where table_name like 'DBA_%' and
      lower(comments) like '%system privilege%';

 

 

 

SYS

select *
from dba_sys_privs;

 

 

 

SYS

select *
from dba_sys_privs
where grantee = 'HR';

-- HR 사용자에 대한 system privilege(시스템 권한)을 조회한다.

 

 

---

 

HR의 system privilege 조회

 

HR

select *
from dictionary
where table_name like 'USER_%' and
      lower(comments) like '%system privilege%';

 

 

 

HR

select *
from user_sys_privs;

 

 

---

Ⅲ. 단일행 함수

 

▷ 단일행 함수의 종류   

1. 문자함수    2. 숫자함수    3. 날짜함수    4. 변환함수    5. 기타함수

 

---

5. 기타함수

 

▷ case when then else end

 

HR

select case 5-2
       when 1 then '5 - 2 = 1 입니다.'
       when 2 then '5 - 2 = 2 입니다.'
       else '나는 수학을 몰라요'
       end
from dual;

 

 

 

HR

select case 5-2
       when 1 then '5 - 2 = 1 입니다.'
       when 3 then '5 - 2 = 3 입니다.'
       else '나는 수학을 몰라요'
       end
from dual;

 

 

 

HR

select case
       when 5 - 2 > 10 then '5 - 2 > 10 입니다.'
       when 5 - 2 > 1 then '5 - 2 > 1 입니다.'
       else '나는 수학을 몰라요'
       end
from dual;

 

 

 

▷ decode

-- 참, 거짓 구분 없이 값이 같은지 같지 않은지 비교한다.

 

HR

select decode(5-2, 1, '5 - 2 = 1 입니다.'
                 , 2, '5 - 2 = 2 입니다.'
                    , '나는 수학을 몰라요')
from dual;

 

 

 

HR

select decode(5-2, 1, '5 - 2 = 1 입니다.'
                 , 3, '5 - 2 = 3 입니다.'
                    , '나는 수학을 몰라요')
from dual;

 

 

 

---

 

1. 문자함수

 

1.1 upper : 전부 대문자로 변환
1.2 lower : 전부 소문자로 변환
1.3 initcap : 단어별로 첫글자만 대문자 나머지는 소문자로 변환

 

HR

select 'kOrEA'
        , upper('kOrEA seOUL')
        , lower('kOrEA seOUL')
        , initCap('kOrEA seOUL')
from dual;

 

 

 

HR

select employee_id, first_name, last_name
from employees
where upper(first_name) = upper('jOHn');

 

 

 

1.4 substr : 문자열 중 특정문자 또는 문자열의 특정일부분을 선택해올 때 사용한다.

 

HR

select '우리나라무궁화'
        , substr('우리나라무궁화', 3, 2)
        , substr('우리나라무궁화', 3)
from dual;

 

 

---

 

imployees 테이블에서 모든 사원들의 사원번호, 사원명, 주민번호, 성별을 추출하세요.

 

HR

select employee_id
     , first_name || ' ' || last_name
     , jubun
     , substr(jubun, 7, 1)
from employees

 

 

 

employees 테이블에서 남자만 사원번호, 사원명, 주민번호를 출력하세요.

 

HR

select employee_id
     , first_name || ' ' || last_name
     , jubun
from employees
where substr(jubun, 7, 1) in ('1','3');

 

 

 

employees 테이블에서 사원번호, 사원명, 주민번호, 성별(남 / 여)를 추출하세요.

 

▷ 첫 번째 방법(case when then else end)

 

HR

select employee_id as 사원번호
     , first_name || ' ' || last_name as 사원명
     , jubun as 주민번호
     , case substr(jubun, 7, 1)
       when '1' then '남'
       when '3' then '남'
       else '여'
       end as 성별
from employees;

 

 

▷ 두 번째 방법(case when then else end)

 

HR

select employee_id as 사원번호
     , first_name || ' ' || last_name as 사원명
     , jubun as 주민번호
     , case
       when substr(jubun, 7, 1) in('1','3') then '남'
       else '여'
       end as 성별
from employees;

 

 

▷ 세 번째 방법(decode)

 

HR

select employee_id as 사원번호
     , first_name || ' ' || last_name as 사원명
     , jubun as 주민번호
     , decode(substr(jubun, 7, 1), '1', '남'
                                 , '3', '남'
                                      , '여') as 성별
from employees;

 

 

---

 

1.5 length : 문자열의 길이를 알려주는 것

 

HR

select length('우리나라무궁화'), length('korea')
from dual;

 

 

 

HR

select first)name, length(first_name)
from employees;

 

 

 

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

 

HR

select instr('간장 공장 공장장은 장공장장', '공장', 1, 1)
       ,instr('간장 공장 공장장은 장공장장', '공장', 1, 2)
       ,instr('간장 공장 공장장은 장공장장', '공장', 4, 1)
       ,instr('간장 공장 공장장은 장공장장', '공장', 4, 3)
from dual;

 

 

 

1.7 reverse : 어떤 문자열을 거꾸로 보여주는 것

 

HR

select 'ORACLE', reverse('ORACLE')
      ,'대한민국', reverse('대한민국')
      ,reverse(reverse('대한민국'))
from dual;

 

 

---

 

문제 ▷▷

 

HR

create table tbl_files
(fileno     number(3)
,filepath   varchar2(200)
);

insert into tbl_files(fileno, filepath)
values(1, 'C:\myDocuments\resume.hwp');

insert into tbl_files(fileno, filepath)
values(2, 'D:\mymusic.mp3');

insert into tbl_files(fileno, filepath)
values(3, 'C:\myphoto\2019\09\face.jpg');

commit;

select *
from tbl_files;

 

 

 

아래의 결과가 나오도록 해 보자.

 

 

 

▷ 첫 번째 방법(나)

 

HR

select fileno as 파일번호
      ,reverse(substr(reverse(filepath), 1, (instr(reverse(filepath), '\', 1, 1))-1)) as 파일명
from tbl_files;

 

 

▷ 두 번째 방법

 

HR

select fileno as 파일번호
     , substr(filepath, instr(filepath, '\', -1)+1) as 파일명
from tbl_files;

 

---

 

1.8 lpad : 왼쪽부터 문자를 자리채움

1.9 rpad : 오른쪽부터 문자를 자리채움

 

HR

select lpad('제주한라산', 20, '*')
        -- 20byte를 확보해서 거기에 제주한라산을 넣습니다. 
        -- 넣은 후 빈 공간(10byte)이 있으면 왼쪽부터 * 로 채워라.
       ,rpad('제주한라산', 20, '*')
       -- 20byte를 확보해서 거기에 제주한라산을 넣습니다. 
        -- 넣은 후 빈 공간(10byte)이 있으면 오른쪽부터 * 로 채워라.
from dual;

 

 

 

 

1.10 ltrim : 왼쪽부터 문자를 제거

1.11 rtrim : 오른쪽부터 문자를 제거

 

HR

select ltrim('aaaabbbbbbbbccccdaaabTadssegrrwbbas', 'abcd')
    ,rtrim('SEaabbccaaddccaadd','abcd')
from dual;

 

 

 

HR

select '제주' || '                         한라          산'
       ,'제주' || ltrim ('                         한라          산') 
       ,'제주' || rtrim ('                         한라          산')  
       ,'제주' || trim ('               한라         ') || '산' 
from dual;

 

 

-- 제거해야 할 문자가 없으면 공백을 제거한다.

-- trim을 사용하면 양쪽 다 제거한다.

 

 

1.12 translate

 

HR

select translate('010-3456-7890'
                ,'0123456789'
                ,'영일이삼사오육칠팔구')
from dual;

 

 

 

1.13 replace

 

HR

select replace('간장공장 공장장은 장공장장'
               ,'공장'
               ,'factory')
from dual;

 

 

---

 

2. 숫자함수

 

2.1 mod : 나머지를 구해 주는 것

 

HR

select 5/2
      ,mod(5,2) as 나머지
      ,trunc(5/2) as 몫
from dual;

 

 

 

2.2 round : 반올림을 해 주는 것

 

HR

select 94.547
      ,round(94.547)  
      ,round(94.547, 0)  
      ,round(94.547, 1)  
      ,round(94.547, 2) 
      ,round(94.547, -1) 
      ,round(94.547, -2) 
from dual;

 

 

 

2.3 trunc : 절삭해 주는 것

 

HR

select 94.547
      ,trunc(94.547)
      ,trunc(94.547, 0)
      ,trunc(94.547, 1) 
      ,trunc(94.547, 2) 
      ,trunc(94.547, -1)
      ,trunc(94.547, -2)
from dual;

 

 

---

 

문제 ▷▷

 

HR

create table tbl_sungjuk
(hakbun   varchar2(20) 
,name     varchar2(20)
,kor      number(3)
,eng      number(3)
,math     number(3)
);

insert into tbl_sungjuk(hakbun, name, kor, eng, math)
values('KH001','한석규', 90, 92, 93);

insert into tbl_sungjuk(hakbun, name, kor, eng, math)
values('KH002','두석규', 100, 100, 100);

insert into tbl_sungjuk(hakbun, name, kor, eng, math)
values('KH003','세석규', 71, 72, 73);

insert into tbl_sungjuk(hakbun, name, kor, eng, math)
values('KH004','네석규', 89, 87, 81);

insert into tbl_sungjuk(hakbun, name, kor, eng, math)
values('KH005','오석규', 60, 50, 40);

insert into tbl_sungjuk(hakbun, name, kor, eng, math)
values('KH006','육석규', 80, 81, 87);

commit;

select *
from tbl_sungjuk;

 

 

tbl_sungjuk을 사용하여 아래와 같이 나오도록 하세요.

--------------------------------------------------------------------------------------------------------------------------------- 
  학번    성명    국어    영어    수학    총점    평균(반올림하여 소수부 첫째자리까지 보여준다.)    학점(A, B, C, D, F)
---------------------------------------------------------------------------------------------------------------------------------

 

-- 학점을 구할 땐 case when then else end 와 decode 각각 사용하여 만들어 보자.

 

HR

select hakbun as 학번
      ,name as 성명
      ,kor as 국어
      ,eng as 영어
      ,math as 수학
      ,kor+eng+math as 총점
      ,round(((kor+eng+math)/3),1) as 평균
      ,case trunc((kor+eng+math)/3,-1)
       when 100 then 'A'
       when 90 then 'A'
       when 80 then 'B'
       when 70 then 'C'
       when 70 then 'D'
       else 'F'
       end as 학점1
      ,decode(trunc((kor+eng+math)/3,-1), 100, 'A'
                                 , 90, 'A'
                                 , 80, 'B'
                                 , 70, 'C'
                                 , 60, 'D'
                                 , 'F') as 학점2     
from tbl_sungjuk;

 

 

 

-- case when then else end 를 사용할 때, 십의 자리로 절삭하지 않고 and를 사용하여 다중비교가 가능하다.

 

HR

case 
       when 90 <= trunc((kor+eng+math)/3,-1) and trunc((kor+eng+math)/3,-1) <= 100 then 'A'
       when 80 <= trunc((kor+eng+math)/3,-1) and trunc((kor+eng+math)/3,-1) < 90 then 'B'
       when 70 <= trunc((kor+eng+math)/3,-1) and trunc((kor+eng+math)/3,-1) < 80 then 'C'
       when 60 <= trunc((kor+eng+math)/3,-1) and trunc((kor+eng+math)/3,-1) < 70 then 'D'
       else 'F' end as 학점3

 

 

 

2.4 power : 거듭제곱

 

HR

select 2*2*2*2*2, power(2,5)
from dual;

 

 

 

2.5 sqrt : 제곱근

 

HR

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

 

 

 

2.6 sin, cos, tan, asin, acos, atan

 

HR

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

 

 

 

2.7 log

 

HR

select log(10, 100)
from dual;

 

 

 

2.8 ceil, floor

 

HR

select ceil(10.1), ceil(10.9), ceil(10.0), ceil(10)
from dual;

 

 

-- 입력한 숫자의 소수부가 0이 아닌 경우 : 숫자보다 큰 최소의 정수

-- 입력한 숫자의 소수부가 0인 경우 :  자신의 숫자를 정수로 나타낸다. 

 

 

 

HR

select floor(10.1), floor(10.9), floor(10.0), floor(10)
from dual;

 

 

-- 입력한 숫자의 소수부가 0이 아닌 경우 : 숫자보다 작은 최대의 정수

-- 입력한 숫자의 소수부가 0인 경우 :  자신의 숫자를 정수로 나타낸다. 

 

 

 

2.9 sign(수식) : 수식의 결과가 양수이라면 1, 음수이라면 -1, 0이라면 0으로 나타내어 준다.

 

HR

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

 

 

 

2.10 ascii, chr

 

HR

select ascii('A'), ascii('a'), ascii('0'), ascii(' ')
from dual;

 

 

 

HR

select chr(65), chr(97), chr(48), chr(32)
from dual;