데모준비
oracle@moon:~$ cat > depends.sql
select name, type, referenced_name, referenced_type
from user_dependencies
where referenced_owner = user
and name not in ('RUNSTATS_PKG', 'STATS')
order by name
/
oracle@moon:~$ cat > objects.sql
select object_name, object_type, status
from user_objects
where object_name not in ('RUN_STATS', 'STATS', 'RUNSTATS_PKG')
/
데모#1 - 종속성 체인을 끊어준다 (자립형)
SQL> create table t ( x int );
테이블이 생성되었습니다.
SQL> create view v as select * from t;
뷰가 생성되었습니다.
SQL> create procedure p
as
begin
for x in ( select * from v )
loop
null;
end loop;
end;
/ 2 3 4 5 6 7 8 9
프로시저가 생성되었습니다.
SQL> create function f return number
as
l_cnt number;
begin
select count(*) into l_cnt from t;
return l_cnt;
end;
/ 2 3 4 5 6 7 8
함수가 생성되었습니다.
SQL> @depends
NAME TYPE REFERENCED_NAME REFERENCED_TYPE
--------------- --------------- --------------- ---------------
F FUNCTION T TABLE
P PROCEDURE V VIEW
V VIEW T TABLE
SQL> @objects
OBJECT_NAME OBJECT_TYPE STATUS
--------------- ------------------- ---------------
T TABLE VALID
V VIEW VALID
F FUNCTION VALID
P PROCEDURE VALID
SQL> alter table t add y number
/ 2
테이블이 변경되었습니다.
SQL> @objects
OBJECT_NAME OBJECT_TYPE STATUS
--------------- ------------------- ---------------
T TABLE VALID
V VIEW INVALID
F FUNCTION INVALID
P PROCEDURE INVALID
SQL> create procedure p2
as
begin
p;
end;
/ 2 3 4 5 6
프로시저가 생성되었습니다.
SQL> @depends
NAME TYPE REFERENCED_NAME REFERENCED_TYPE
--------------- --------------- --------------- ---------------
F FUNCTION T TABLE
P PROCEDURE V VIEW
P2 PROCEDURE P PROCEDURE
V VIEW T TABLE
SQL> @objects
OBJECT_NAME OBJECT_TYPE STATUS
--------------- ------------------- ---------------
T TABLE VALID
V VIEW VALID
F FUNCTION INVALID
P2 PROCEDURE VALID
P PROCEDURE VALID
SQL> alter table t add z number
/ 2
테이블이 변경되었습니다.
SQL> @objects
OBJECT_NAME OBJECT_TYPE STATUS
--------------- ------------------- ---------------
T TABLE VALID
V VIEW INVALID
F FUNCTION INVALID
P2 PROCEDURE INVALID
P PROCEDURE INVALID
데모#1 - 종속성 체인을 끊어준다 (패키지)
SQL> create table t ( x int );
테이블이 생성되었습니다.
SQL> create view v as select * from t;
뷰가 생성되었습니다.
SQL> create package p1
as
procedure p;
end;
/ 2 3 4 5
패키지가 생성되었습니다.
SQL> create package body p1
as
procedure p
as
begin
for x in ( select * from v )
loop
null;
end loop;
end;
end p1;
/ 2 3 4 5 6 7 8 9 10 11 12
패키지 본문이 생성되었습니다.
SQL> create package p2
as
procedure p;
end;
/
2 3 4 5
패키지가 생성되었습니다.
SQL> create package body p2
as
procedure p
as
begin
p1.p;
end;
end p2;
/ 2 3 4 5 6 7 8 9
패키지 본문이 생성되었습니다.
SQL> @depends
NAME TYPE REFERENCED_NAME REFERENCED_TYPE
--------------- --------------- --------------- ---------------
P1 PACKAGE BODY V VIEW
P1 PACKAGE BODY P1 PACKAGE
P2 PACKAGE BODY P1 PACKAGE
P2 PACKAGE BODY P2 PACKAGE
V VIEW T TABLE
SQL> @objects
OBJECT_NAME OBJECT_TYPE STATUS
--------------- ------------------- ---------------
T TABLE VALID
V VIEW VALID
P2 PACKAGE VALID
P1 PACKAGE VALID
P1 PACKAGE BODY VALID
P2 PACKAGE BODY VALID
6 개의 행이 선택되었습니다.
SQL> alter table t add a number
/
2
테이블이 변경되었습니다.
SQL> @objects
OBJECT_NAME OBJECT_TYPE STATUS
--------------- ------------------- ---------------
T TABLE VALID
V VIEW INVALID
P2 PACKAGE VALID
P1 PACKAGE VALID
P1 PACKAGE BODY INVALID
P2 PACKAGE BODY VALID
6 개의 행이 선택되었습니다.
SQL> exec p2.p;
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> @objects
OBJECT_NAME OBJECT_TYPE STATUS
--------------- ------------------- ---------------
T TABLE VALID
V VIEW VALID
P2 PACKAGE VALID
P1 PACKAGE VALID
P1 PACKAGE BODY VALID
P2 PACKAGE BODY VALID
6 개의 행이 선택되었습니다.
데모#2 - 다중 정의를 지원한다
SQL> create or replace package overload_demo as
2
3 function fun_2 ( first_name in varchar2 )
4 return varchar2;
5
6 function fun_2 ( first_name in varchar2, last_name in varchar2 )
7 return varchar2;
8
9 function fun_2 ( girlfriend_count integer )
10 return integer;
11
12 end overload_demo;
13 /
패키지가 생성되었습니다.
SQL> create or replace package body overload_demo as
2
3 function fun_2 ( first_name in varchar2 )
4 return varchar2
5 as
6 begin
7 return substr(first_name, 2);
8 end fun_2;
9
10 function fun_2 ( first_name in varchar2, last_name in varchar2 )
11 return varchar2
12 as
13 begin
14 return first_name || ' ' || last_name;
15 end fun_2;
16
17 function fun_2 ( girlfriend_count integer )
18 return integer
19 as
20 begin
21 return girlfriend_count * 10;
22 end fun_2;
23
24 end overload_demo;
25 /
패키지 본문이 생성되었습니다.
SQL> select overload_demo.fun_2('Woods') from dual;
OVERLOAD_DEMO.FUN_2('WOODS')
----------------------------
oods
SQL> select overload_demo.fun_2('Woods', 'Tiger') from dual;
OVERLOAD_DEMO.FUN_2('WOODS','TIGER')
------------------------------------
Woods Tiger
SQL> select overload_demo.fun_2(1) from dual;
OVERLOAD_DEMO.FUN_2(1)
----------------------
10
데모#3 - 세션 지속형 변수를 지원한다
SQL> create or replace package pkg_sess_var
as
procedure set_val(p1 number);
function get_val return number;
end pkg_sess_var;
/ 2 3 4 5 6 7 8
패키지가 생성되었습니다.
SQL> create or replace package body pkg_sess_var
as
v1 number := 0;
procedure set_val (p1 number)
is
begin
v1 := p1;
end set_val;
function get_val return number
is
begin
return v1;
end get_val;
end pkg_sess_var;
/ 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
패키지 본문이 생성되었습니다.
SQL> select pkg_sess_var.get_val from dual;
GET_VAL
----------
0
SQL> exec pkg_sess_var.set_val(10);
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> select pkg_sess_var.get_val from dual;
GET_VAL
----------
10
데모#4 - 개시 코드를 지원한다
SQL> create or replace package pkg_init
is
function get_val return varchar2;
end pkg_init;
/ 2 3 4 5
패키지가 생성되었습니다.
SQL> create or replace package body pkg_init
2 is
3
4 v1 varchar2(20);
5
6 function get_val return varchar2
7 is
8 begin
9 return v1;
10 end get_val;
11
12 procedure initialize
13 is
14 begin
15 v1 := 'Tiger Woods';
16 dbms_output.put_line('Package Initialized...');
17 end;
18
19 begin
20 initialize;
21 end pkg_init;
22 /
패키지 본문이 생성되었습니다.
SQL> set serveroutput on;
SQL> select pkg_init.get_val from dual;
GET_VAL
-----------
Tiger Woods
Package Initialized...