-- 테스트 준비
drop table students;
drop view v1_students;
drop view v2_students;
drop procedure print_all_students;
drop procedure print_all_students_from_v1;
create table students
(student_id number,
student_name varchar2(30),
dob date
);
/
insert into students(student_id,student_name,dob) values (1,'aya ahmed','1-jan-1980');
insert into students(student_id,student_name,dob) values (2,'sara mahmoud','1-jan-1980');
insert into students(student_id,student_name,dob) values (3,'nabil alail','1-jan-1980');
commit;
select * from students;
/*
STUDENT_ID STUDENT_NAME DOB
---------- ------------------------------ ---------
1 aya ahmed 01-JAN-80
2 sara mahmoud 01-JAN-80
3 nabil alail 01-JAN-80
*/
직/간접 dependency 객체 생성
create or replace view v1_students
as
select * from students;
/
create or replace view v2_students
as
select student_id,dob
from students;
/
create or replace procedure print_all_students
is
begin
for i in (select student_id from students)
loop
dbms_output.put_line(i.student_id);
end loop;
end;
/
create or replace procedure print_all_students_from_v1
is
begin
for i in (select student_id from v1_students)
loop
dbms_output.put_line(i.student_id);
end loop;
end;
-- 객체 상태 조회, 모두 정상
select object_name, object_type, created, status
from user_objects
where lower(object_name) in
('v1_students','v2_students','print_all_students','print_all_students_from_v1');
/*
OBJECT_NAME OBJECT_TYPE CREATED STATUS
------------------------------ ----------------------- --------- -------
PRINT_ALL_STUDENTS PROCEDURE 16-MAY-24 VALID
PRINT_ALL_STUDENTS_FROM_V1 PROCEDURE 16-MAY-24 VALID
V1_STUDENTS VIEW 16-MAY-24 VALID
V2_STUDENTS VIEW 16-MAY-24 VALID
*/
--원본 테이블을 수정
alter table students
modify student_name varchar2(200);
--원본 테이블이 수정되면, 관련된 뷰, 프로시져의 상태는 비정상이 된다
select object_name, object_type, created, status
from user_objects
where lower(object_name) in ('v1_students','v2_students','print_all_students','print_all_students_from_v1');
/*
OBJECT_NAME OBJECT_TYPE CREATED STATUS
------------------------------ ----------------------- --------- -------
PRINT_ALL_STUDENTS PROCEDURE 16-MAY-24 VALID
PRINT_ALL_STUDENTS_FROM_V1 PROCEDURE 16-MAY-24 INVALID
V1_STUDENTS VIEW 16-MAY-24 INVALID
V2_STUDENTS VIEW 16-MAY-24 VALID
*/
--비정상인 프로시져를 실행하면 서버에서 자동으로 재컴파일이 발생하여 다시 정상 상태로 변경된다
exec PRINT_ALL_STUDENTS_FROM_V1;
--2개의 객체가 비정상 상태였는데, 지금은 모두 정상의 상태로 변경되었다
select object_name, object_type, created, status
from user_objects
where lower(object_name) in ('v1_students','v2_students','print_all_students','print_all_students_from_v1');
/*
OBJECT_NAME OBJECT_TYPE CREATED STATUS
------------------------------ ----------------------- --------- -------
PRINT_ALL_STUDENTS PROCEDURE 16-MAY-24 VALID
PRINT_ALL_STUDENTS_FROM_V1 PROCEDURE 16-MAY-24 VALID
V1_STUDENTS VIEW 16-MAY-24 VALID
V2_STUDENTS VIEW 16-MAY-24 VALID
*/
