[ORACLE] sequence / index / 뷰 / 주민번호 이용해서 나이, 성별

 

※ sequence

 

- 연속적인 숫자를 생성해내는 데이터베이스 객체
- 기본키가 각각의 입력되는 row를 식별할 수 있기만 하면 된다고 할때, 시퀀스에 의해 생성된 값을 사용함
- 테이블에 있는 기본키 값을 생성하기 위해 사용되는 독립적인 객체
- 테이블에 종속되지 않음 => 하나의 시퀀스를 여러 개의 테이블에 동시에
  사용할 수 있다

 

 

▷ 시퀀스 생성

create sequence 시퀀스명
  minvalue      --시퀀스의 최소값
  maxvalue      --시퀀스의 최대값
  start with 시작값
  increment by 증가치
  nocache       --cache사용하지 않겠다
  nocycle       --생성된 시퀀스값이 최대치 혹은 최소치에 다다랐을 때
                   초기값부터 다시 시작할 지 여부
  order         --요청되는 순서대로 값을 생성

create sequence pd_temp1_seq
increment by 1
start with 50 
nocache;

 - 50부터 시작해서 1씩 증가

 

create sequence pd2_seq
start with 1
increment by 1
nocache;

 - 1부터 시작해서 1씩 증가하는 시퀀스쿼리

 

 

 

▷ 시퀀스 사용

  1) nextval - 바로 다음에 생성될 시퀀스를 가지고 있다
  2) currval - 현재 시퀀스값을 가지고 있다 

insert into pd_temp1(no, pdname, price, regdate)
values(pd_temp1_seq.nextval, '노트북',1500000, sysdate);

 

 

▷ 시퀀스 조회

select * from user_sequences;

 

 

▷ 시퀀스 삭제

drop sequence 시퀀스명;
drop sequence test2_seq;

 

---

※ index

 

- 테이블의 데이터를 빨리 찾기 위한 꼬리표
- 인덱스가 없다면 특정한 값을 찾기 위해 모든 데이터 페이지를 다 뒤져야 함 (table full scan)
- index seek
      인덱스를 사용하는 것이 더 효과적이라면, 오라클은 모든 페이지를 뒤지지 
      않고 인덱스 페이지를 찾아서 쉽게 데이터를 가져옴
- 한 테이블에 여러 개의 인덱스를 생성할 수 있음
- primary key나 unique 제약조건을 주면 자동으로 unique index가 생성됨

 

 

▷ index 생성

    create [unique] index 인덱스명
    on 테이블명(컬럼명1, 컬럼명2, ...)

create index idx_pd2_pdname
on pd2(pdname);

create index idx_pd2_regdate_company
on pd2(regdate, company); // 복합인덱스

 

▷ index 조회

select * from user_indexes
where table_name='PD2';

 

▷ index를 이용한 조회

select * from pd2
where pdcode='B01';

 

▷ index 삭제

drop index 인덱스명;
drop index idx_pd2_pdname;

 

---

※ 뷰(view)

- view는 테이블에 있는 데이터를 보여주는 형식을 정의하는 select문장의 덩어리하고 할 수 있다    
- view는 실제로 데이터를 가지고 있지는 않지만, 뷰를 통해 데이터를 조회할 수 있고, 또 데이터를 입력,수정,삭제할 수 있으며 다른 테이블과 조인도 할 수 있기 때문에 가상의 논리적 테이블이라고 함

 

▶ 뷰를 사용하는 목적

1) 보안성
2) 편의성

 

 

▷ 뷰 생성

create [or replace] view 뷰이름
as
select문장;

create or replace view v_emp
as
select ename, job, hiredate
from emp
where deptno=30;

 

▷ 뷰 조회

select * from v_emp;

 

▷ 뷰 권한부여

hr => scott

grant select on v_emp to scott;

 

   >> scott 계정에서 emp 뷰 select

스키마이름.데이터베이스 오브젝트명
select * from hr.v_emp;

스키마(Schema) - 임의의 사용자가 생성한 모든 데이터베이스 객체들을 말하며, 
스키마 이름은 그 사용자의 이름과 같다.

 

 

▷ 뷰 권한제거

revoke select on v_emp from scott;

 

▷ 뷰 변경

create or replace view v_emp
as
select ename, job, hiredate
from emp
where deptno in(20,30);

create or replace 이기때문에 생성이랑 똑같다. drop 할 필요 없음

 

 

 

ex ) employees 테이블에서 급여가 높은 순으로 5명만 조회하는 뷰를 작성하시오.

create or replace view v_emp_sal
as
select *
from
(
    select first_name, hire_date, salary
    from employees
    order by salary desc
)
where rownum<=5;

 

 

응용 ) 조인을 이용하여 복잡한 쿼리문을 뷰로 만들기

create or replace view v_employees
as
select e.EMPLOYEE_ID, e.FIRST_NAME || '-' || e.LAST_NAME "이름",
    e.HIRE_DATE, e.DEPARTMENT_ID, d.DEPARTMENT_NAME,
    e.salary + e.SALARY * nvl(e.COMMISSION_PCT,0) 급여
from employees e left join departments d
on e.DEPARTMENT_ID=d.DEPARTMENT_ID;

select * 
from v_employees
where 급여>=10000;

 

 

▶ 뷰를 통한 데이터 수정

 

[1] updatable view

 - 뷰를 통한 조회도 가능하고, 입력,수정,삭제도 가능함

 

▷ updatable view 생성

create or replace view 뷰이름
as 
select문;

▷ updatable view 수정

update v_emp
set ename='SMITH2'
where ename='SMITH';

뷰를 통한 입력을 하는경우, 뷰에 없는 컬럼은 null을 허용하거나 default 값이 있어야 함, 그렇지 않으면 에러 발생

 

[2] read only view

    - 조회만 가능한 뷰

 

▷ read only view 생성

create or replace view 뷰이름
as 
select문
with read only;

create or replace view v_emp_readonly
as
select ename, job, hiredate
from emp
where deptno in (20,30)
with read only;

 

 

 

▽ 기본적으로 뷰를 만들때 뷰의 조건을 벗어나는 범위로 데이터를 수정할 수 있으며
이를 허용하지 않고자 할때는 with check option을 사용

create or replace view v_emp_3
as
select empno, ename, job, hiredate
from emp
where deptno in(20,30)
with check option;

 

 

---

▶ 오라클 주민번호 이용해서 나이, 성별 구하기 쿼리

select 
    case when substr(jumin, 7,1) in(1,3) then '남'
         when substr(jumin, 7,1) in(2,4) then '여' end 성별,          
   extract(YEAR FROM SYSDATE) - 
            (DECODE(SUBSTR(jumin,7,1),'1', '19','2','19','20') || SUBSTR(jumin,1,2)) +1  as "나이"