110 - [Oracle PL/SQL] Dependencies - Impact on the View

• Impact of Adding/ Altering column for the Referenced Table

 

 

 

• 원본 테이블의 변화가 view 에 주는 영향

-- 테스트 준비
DROP TABLE customer
/
create table customer
( cust_id number,
  name varchar2(100),
  tel varchar2(10)
);

create or replace view v_customer
as
select cust_id,name
from customer;
/
create or replace view v2_customer
as
select *
from customer;

 

 

 

-- 객체들의 상태는 모두 정상
select object_name, object_type, created, status
from user_objects
where lower(object_name) in ('customer','v_customer','v2_customer');
/*
OBJECT_NAME                    OBJECT_TYPE             CREATED   STATUS 
------------------------------ ----------------------- --------- -------
CUSTOMER                       TABLE                   17-MAY-24 VALID  
V2_CUSTOMER                    VIEW                    17-MAY-24 VALID  
V_CUSTOMER                     VIEW                    17-MAY-24 VALID 
*/


--adding column to table customer will not invalidate v_customer
--this started in 11g
-- 원본테이블에 컬럼을 추가
alter table customer
add( dob date);


-- 새로운 컬럼이 추가된 경우는 원본 테이블을 참조하는 뷰에 영향을 미치지 않는다
select object_name, object_type, created, status
from user_objects
where lower(object_name)in ('customer','v_customer','v2_customer');
/*
OBJECT_NAME                    OBJECT_TYPE             CREATED   STATUS 
------------------------------ ----------------------- --------- -------
CUSTOMER                       TABLE                   17-MAY-24 VALID  
V2_CUSTOMER                    VIEW                    17-MAY-24 VALID  
V_CUSTOMER                     VIEW                    17-MAY-24 VALID 
*/


-- 원본 테이블의 컬럼을 수정
--alter column to table customer may or may not invalidate the views
alter table customer
modify tel varchar2(20);


-- 원본 테이블의 컬럼을 수정하면 원본 테이블을 참조하는 뷰에 영향을 끼친다
select object_name, object_type, created, status
from user_objects
where lower(object_name) in ('customer','v_customer','v2_customer');
/*
OBJECT_NAME                    OBJECT_TYPE             CREATED   STATUS 
------------------------------ ----------------------- --------- -------
CUSTOMER                       TABLE                   17-MAY-24 VALID  
V2_CUSTOMER                    VIEW                    17-MAY-24 INVALID
V_CUSTOMER                     VIEW                    17-MAY-24 VALID 
*/

--here it will compile again, 오라클은 객체의 상태가 inValid 면 재컴파일 함
select * from v2_customer;


-- 객체의 상태가 실행이후, 정상으로 변경된 것을 확인
select object_name, object_type, created, status
from user_objects
where lower(object_name) in ('customer','v_customer','v2_customer');
------------------------------------------------------------
/*
OBJECT_NAME                    OBJECT_TYPE             CREATED   STATUS 
------------------------------ ----------------------- --------- -------
CUSTOMER                       TABLE                   17-MAY-24 VALID  
V2_CUSTOMER                    VIEW                    17-MAY-24 VALID  
V_CUSTOMER                     VIEW                    17-MAY-24 VALID 
*/