112 - [Oracle PL/SQL] Dependencies - Impact on the Procedure

 

 

 

테스트 준비 - 프로시져 생성 및 상태 확인

create or replace procedure p1
is
begin
for i in (select cust_id from customer)
  loop
  dbms_output.put_line(i.cust_id);
  end loop;

end;
/
create or replace procedure p2
is
begin
for i in (select * from customer)
  loop
  dbms_output.put_line(i.cust_id);
  end loop;

end;



-- 새로 생성한 프로시져 2개의 상태는 정상
select object_name, object_type, created, status
from user_objects
where lower(object_name) in ('p1','p2');
/*
OBJECT_NAME                    OBJECT_TYPE             CREATED   STATUS 
------------------------------ ----------------------- --------- -------
P1                             PROCEDURE               10-MAY-24 VALID  
P2                             PROCEDURE               17-MAY-24 VALID 
*/

 

 

• 컬럼 추가 및 변경 테스트

--ading column to proce May or May not invalidate according for the logic of changes 
alter table customer add( addresss varchar2(100));


select object_name, object_type, created, status
from user_objects
where lower(object_name) in ('p1','p2');
/*
OBJECT_NAME                    OBJECT_TYPE             CREATED   STATUS 
------------------------------ ----------------------- --------- -------
P1                             PROCEDURE               10-MAY-24 VALID  
P2                             PROCEDURE               17-MAY-24 INVALID
*/


exec p2;
-- P2의 상태를 정상으로 변경됨


--altering column to proce May or May not invalidate according for the logic of changes                              
alter table customer modify cust_id number(10);


-- 2개의 프로시져에 관련된 컬럼에 대한 변경으로 상태가 모두 비정상으로 변경됨
select object_name, object_type, created, status
from user_objects
where lower(object_name) in ('p1','p2');
/*
OBJECT_NAME                    OBJECT_TYPE             CREATED   STATUS 
------------------------------ ----------------------- --------- -------
P1                             PROCEDURE               10-MAY-24 INVALID
P2                             PROCEDURE               17-MAY-24 INVALID
*/