--sql07_뷰_인덱스_시퀀스.sql

<뷰(view)>

1. 정의 

테이블에 대한 가상의 테이블로써 테이블처럼 직접 데이터를 소유하지 않고

검색시에 이용할 수 있도록 정보를 담고 있는 객체, 테이블 정보의 부분집합.

2. 사용목적

1) 기본테이블에 대한 보안 기능을 설정해야 하는 경우

2) 복잡하며 자주 사용하는 질의 SQL 문을 보다 쉽고 간단하게 사용해야 하는 경우

 

3. 뷰생성 권한주기

cmd>

sqlplus / as sysdba

SQL>

grant create view to 사용자;

 

 

4.뷰 생성하기

(형식)

create or replace view [force | noforce ] 뷰이름(컬럼리스트 ) as [쿼리]

- replace : 이미 존재하는 뷰의 내용을 수정함

create or replace view test_view as select empno, ename, job, sal, deptno from emp;


select * from test_view;

5. 뷰 수정하기  : create or replace

6. 뷰 삭제하기 : drop view 뷰이름 drop view test_emp;

 

 

<색인(index)>

1.정의

테이블의 데이터를 보다 빠르게 검색할 수 있도록 지원해주는 객체

2.구성요소

rowid, 색인 컬럼값

3.생성

create index 인덱스명 on 테이블명(컬럼명...);

- Primary Key, Unique 제약조건을 만들면 해당 인덱스가 자동으로 생성됨

create index c_emp_name_idx on c_emp(name);

4.삭제

dop index 인덱스명;

drop index c_emp_name_idx;

 

 

<실습용 테이블>

-- 인덱스 실습용 테이블

drop table emp3;
create table emp3 (
 no number,
 name varchar2(10),
 sal number
);

declare -- 선언문
 i int :=1; --  := 대입
 name varchar(20) :='kim';
 sal int :=0;
begin
 while i <= 1000000 loop
   if i mod 2 =0 then -- mod 나머지
      name :='kim' || to_char(i);
      sal :=300;
      
   elsif i mod 3 =0 then
      name :='pack' || to_char(i);
      sal :=400;  
      
   elsif i mod 5 =0 then
      name :='hong' || to_char(i);
      sal :=500;
      
   else 
      name :='shin' || to_char(i);
      sal :=250;      
      
   end if;
   insert into emp3 values (i, name,sal);
   i :=i+1;
 end loop;
end;
/

commit;

select count(*) from emp3;

-- 인덱스를 사용하지 않은 경우
-- cost : 894, bytes : 1188
select * from emp3 where name='shin691';

-- 인덱스 추가
-- create index 인덱스이름 on 테이블 (컬럼);
drop index emp_name_idx;
create index emp_name_idx on emp3(name);
-- 데이터 사전 조회
select * from user_indexes where table_name='EMP3';

-- 인덱스를 사용한 경우
-- cost : 824, bytes : 1188
select * from emp3 where name='shin691';

 

 

<시퀀스(sequence)>

1. 정의

연속적인 숫자값을 자동으로 증가시키는 숫자를 발생시키는 객체

2. 생성

create sequence 시퀀스이름

[increment by 숫자]
[start with 숫자]
[maxvalue 숫자]
[minvalue 숫자]
[cycle | nocycle] -- 일련번호 순환여부
[cache | nocache] -- 빠른 처리를 위해 시퀀스의 값을 메모리에 저장

create sequence c_emp_seq increment by 1
    start with 103
    maxvalue 100000000
    nocache
    nocycle;

 

create sequence c_emp_seq2 
    start with 103
    nomaxvalue -- 무제한
        increment by 1 --증가값
    nocache -- 캐쉬를 사용하지 않음
    nocycle; -- 순환하지 않음

-- 시퀀스이름.nextval 다음 번호

3. 함수 (주의 : 시퀀스 생성 후 nextval을 호출해야 시퀀스에 초기값이 설정됨)

1) nextval : 다음값을 반환함

select c_emp_seq.nextval from dual;

 

2)currval: 현재값을 반환함

select c_emp_seq.nextval from dual;
select c_emp_seq.currval from dual;

4.사용예

 

insert into c_emp values(c_emp_seq.nextval, 'aaa', 1000, '3429-001',10);

-- 서브쿼리를 이용하여 id 필드에 값을 설정

-- nvl(A, A가 null 일때의 값)

select nvl(max(id)+1,1) from c_emp;


insert into c_emp values
((select nvl(max(id)+1, 1) from c_emp), 'test', 3000, '3429-0000', 10);

 

drop table memo;

create table memo (
idx number primary key,
writer varchar2(50) not null,
memo varchar2(500) not null,
post_date date default sysdate
);
-- 메모 테이블을 위한 시퀀스 생성
create sequence memo_seq
start with 1
nomaxvalue;

insert into memo (idx, writer , memo ) values(memo_seq.nextval, 'kim1', '메모1');
insert into memo (idx, writer , memo ) values(memo_seq.nextval, 'kim2', '메모2');
insert into memo (idx, writer , memo ) values(memo_seq.nextval, 'kim3', '메모3');
insert into memo (idx, writer , memo ) values(memo_seq.nextval, 'kim4', '메모4');
insert into memo (idx, writer , memo ) values(memo_seq.nextval, 'kim5', '메모5');
insert into memo (idx, writer , memo ) values(memo_seq.nextval, 'kim6', '메모6');

commit;

select * from memo;

=>출력
1    kim1    메모1    17/03/27
2    kim2    메모2    17/03/27
3    kim3    메모3    17/03/27
4    kim4    메모4    17/03/27
5    kim5    메모5    17/03/27
6    kim6    메모6    17/03/27

(연습문제)

1. c_emp 테이블을 복사한 emp5 데이터 입력시 sequence 를 이용해서 id를 입력하도록

206에서 시작하여 1씩 증가되고 최대값은 999로 설정하여 sequence를 생성하시오.

-- 레코드 복사 없이 테이블만 카피
create table emp5 as select * from c_emp where 1=0;

create sequence emp5_seq start with 206
  increment by 1 
  maxvalue 999;
  

 

2.1에서 작성한 sequence를 이용해서 사번을 입력하고 이름은 김철수, 부서는

10번 부서로 배치하여 데이터를 입력하시오.

 

insert into emp5 (id, name, dept_id) values(emp5_seq.NEXTVAL , '김철수', 10);

select * from emp5;

출력 >> 206    김철수    10