[오라클/SQL] View 테이블 생성,변경

 

View (가상테이블) 특징

    자주 사용하는 쿼리가 여러 테이블을 조인해서 이뤄질 때 JOIN 하는 테이블 수가 늘어나면 질의문이 길고 복잡해지고 유지보수도 어렵기에 View를 생        성하여  사용한다.

• 명령어와 스크립트로 생성할 수 있는데 JOIN 하는 과정을 스크립트를 만들어 두거나 STORED PROCEDURE를 사용해서 데이터베이스 서버에 저장해두고 수시로 꺼내서 수정해가면서 재사용할 수 있다. 
• 스크립트는 파일 시스템에 저장되지만 STORED PROCEDURE는 데이터베이스에 저장되어 관리되므로 최초로 compile 한 이후로는 그냥 수정해서 실행하기만 하면 된다. 
• View는 제한된 정보(각 테이블에서 필요한 항목들만 꺼내서)로 새로운 테이블을 생성했기 때문에 내부의 다른 항목들이 보이지 않게 되므로 보안적인 측면에서 좋고, 관리자의 권한 위임(privilege delegation) 등에서도 부담이 없다.

View 주의점

• 쿼리 시 맨 끝에 where read only를 붙이면 읽기 전용 뷰가 된다.
• View 테이블에서의 데이터 변경은 단순 뷰인 경우 하나의 테이블에서 필요한 항목들만 추출해서 만드는데 INSERT, UPDATE, DELETE가 자유로우며 (NOT NULL 컬럼 주의)
• 복합 뷰는 여러 테이블들을 조인해서 필요한 항목들만 추출해서 만드는데 함수, UNION, GROUP BY 등을 사용하기 때문에 INSERT, UPDATE, DELETE가 불가능하다(조인만 사용한 복합 뷰인 경우 제한적으로 가능)
• 뷰 가상 테이블에서 데이터를 변경하면 뷰가 참조하고 있는 원본 테이블에서도 데이터도 변경된다
• View 테이블_명을 변경할 수 없다. ex) empInfor_vw 뷰 테이블_명을 
  alter table empInfor_vw rename to empInfo; 식으로 empInfo로 뷰 테이블의 이름을 변경불가

SQL> conn sys /as sysdba
암호 입력: *******
연결되었습니다.
SQL> show user;
USER은 "SYS"입니다
SQL> grant create view to hr;
권한이 부여되었습니다.
SQL> commit;
커밋이 완료되었습니다.

Q. empINF_VW1테이블 생성 (employee_id, last_name, salary, hire_date)

SQL> conn hr/rootoor						
연결되었습니다.							
SQL>  create or replace view empINF_VW1				
AS select employee_id, last_name, salary, hire_date from employees;				
select * from empINF_VW1						
where rownum <= 5;

 

Q. empINF_VW2 테이블 생성

(e.employee_id, e.last_name, d.department_name, l.city, l.state_province, r.region_id, c.country_name, r.region_name)

 

1) desc employees; 식으로 해서 어느 항목이 어느 테이블에 있는지 확인한다.

2) 각 테이블에서의 공통 항목이 무엇인지 확인

-- 널?,유형 (생략)
SQL> desc employees;
 이름                                       
 ----------------------------------------- 
 EMPLOYEE_ID                              
 FIRST_NAME                                    
 LAST_NAME                                 
 EMAIL                                      
 PHONE_NUMBER                                       
 HIRE_DATE                                 
 JOB_ID                                    
 SALARY                                        
 COMMISSION_PCT                                     
 MANAGER_ID                                         
 DEPARTMENT_ID                                

SQL> desc departments;
 이름                                     
 ----------------------------------------- 
 DEPARTMENT_ID                            
 DEPARTMENT_NAME                          
 MANAGER_ID                                          
 LOCATION_ID                                         

SQL> desc locations;
 이름                                      
 ----------------------------------------- 
 LOCATION_ID                            
 STREET_ADDRESS                                      
 POSTAL_CODE                                       
 CITY                                      
 STATE_PROVINCE                                   
 COUNTRY_ID                                         

SQL> desc regions;
 이름                                        
 ----------------------------------------- 
 REGION_ID                                
 REGION_NAME                                         

SQL> desc countries;
 이름                                      
 -----------------------------------------
 COUNTRY_ID                               
 COUNTRY_NAME                                       
 REGION_ID

create or replace view empINF_vw2
AS select employee_id, last_name, department_name, city, state_province, country_name, 
region_name 
from departments d JOIN employees e ON d.department_id = e.department_id
                   JOIN locations l ON d.location_id = l.location_id
                   JOIN countries c ON l.country_id = c.country_id
                   JOIN regions r ON c.region_id = r.region_id;

※ 마지막 줄에 where ~ 추가하면 해당 조건에 만족하는 사람만 생성 가능