210615_1(데이터베이스9, 패키지, 트리거, 정규화)
~6/14
패키지 ( 저장프로시저, 저장함수, 저장프로시저 --묶음)
정의 : 저장프로시저나 저장함수등등을 여러개 묶은것
패키지를 만들때는 선언부와 바디부로 나누어서 만든다.
우리가 알고 있는 대표적인 패키지가 바로 dbms_output.put_line() 이다.
기본형
create or replace package 패키지명 -- 선언부
is
-- 원형을 선언함
procedure 프로시져명 ();
procedure 프로시져명 ();
function 함수명 () return 데이터형;
end ;
/
show errors
create or replace package body 패키지명 -- 바디부
is
-- 원형을 선언함
procedure 프로시져명 () is (지역변수) begin 처리식 end;
procedure 프로시져명 () is (지역변수) begin 처리식 end;
function 함수명 () return 데이터형 is (지역변수) begin 처리식; return 값 end;
end ;
/
show errors
6/15~
문제) 앞에서 구현해보았던 프로시져 sp03과 저장함수 sp04을 같이 묶어서 패키지 pack01을 구현하고 실행하시오
create or replace package pack01
is
procedure job_empno(
vempno in emp_copy.empno%type, vename out emp_copy.ename%type, vjob out emp_copy.job%type );
function cal_bonus (
vempno emp_copy.empno%type
) return number;
end ;
/
show errors
create or replace package body pack01
is
procedure job_empno(
vempno in emp_copy.empno%type, vename out emp_copy.ename%type, vjob out emp_copy.job%type )
is
begin
select ename, job into vename, vjob from emp_copy where empno=vempno;
end;
function cal_bonus (
vempno emp_copy.empno%type
) return number
is vsal number(7,2);
begin
select sal into vsal from emp_copy where empno=vempno;
return vsal*2;
end;
end ;
/
show errors
set serveroutput on
패키지 프로시저실행 -- 패키지.프로시저()
variable var_name varchar2(15);
variable var_job varchar2(15);
execute pack01.job_empno(7521,:var_name,:var_job)
print var_name var_job
VAR_NAME
---------------------
WARD
VAR_JOB
-----------------------
SALESMAN
패키지 함수실행 -- 변수 := 패키지.함수()
variable d_sal number;
execute :d_sal := pack01.cal_bonus(7844)
print d_sal
D_SAL
----------
3000
전역변수와 원타임 프로시저
전역변수란 패키지 내부의 모든 프로시저나 함수에서 사용이 가능한 변수를 말한다.
이 전역변수를 초기화 하기 위하여 패키지 실행시 단 한번 실행되는 프로시저를 원타임프로시저라고 부른다.
패키지 내부에서는 프로시저 오버로딩이 가능하다.
실습) 0.emp_copy에서 사번 7902를 입력받아
1. 사번을 입력받아서 이름, 급여, 직책을 반환하는 프로시저와
2. 내부적으로 FORD의 사번을 구하여 이름, 급여, 직책을 반환하는 프로시저로
구성된 패키지를 구현하고 실행하시오
전역변수 gempno;
------------------------------------------------------------- 재료1
procedure job_empno( vempno IN emp_copy.empno%type,
vename out emp_copy.ename%type, vjob out emp_copy.job%type )
is
begin
SELECT ename ,sal, job into vename ,vsal, vjob
FROM emp_copy
WHERE empno=vempno;
end;
------------------------------------------------------------재료2
procedure job_empno( vempno out emp_copy.empno%type,
vename out emp_copy.ename%type, vjob out emp_copy.job%type )
is
begin
SELECT ename ,sal, job into vename ,vsal, vjob
FROM emp_copy
WHERE empno=gempno;
end;
-------------------------------------------------원타임 재료
select empno into gempno
from emp_copy
where ename = 'FORD';
create or replace package pack02
is
gempno number(4); -- 지역변수는 선언부에
procedure job_empno(
vempno IN emp_copy.empno%type,
vename out emp_copy.ename%type,
vsal out emp_copy.sal%type,
vjob out emp_copy.job%type );
procedure job_empno( --오버로딩
vename out emp_copy.ename%type,
vsal out emp_copy.sal%type,
vjob out emp_copy.job%type );
end ;
/
show errors
create or replace package body pack02
is
procedure job_empno(
vempno IN emp_copy.empno%type,
vename out emp_copy.ename%type,
vsal out emp_copy.sal%type,
vjob out emp_copy.job%type )
is
begin
SELECT ename ,sal, job into vename ,vsal, vjob
FROM emp_copy
WHERE empno=vempno;
end;
procedure job_empno(
vename out emp_copy.ename%type,
vsal out emp_copy.sal%type,
vjob out emp_copy.job%type )
is
begin
SELECT ename ,sal, job into vename ,vsal, vjob
FROM emp_copy
WHERE empno=gempno;
end;
begin --원타임 프로시저 가장먼저실행
select empno into gempno
from emp_copy
where ename = 'FORD';
end ;
/
show errors
패키지 실행1 -- 패키지.프로시저()
variable var_name varchar2(15);
variable var_sal number;
variable var_job varchar2(15);
execute pack02.job_empno(7902,:var_name,:var_sal,:var_job) --인자4개
print var_name var_sal var_job
VAR_NAME VAR_SAL VAR_JOB
-------------- ---------- --------------
FORD 3000 ANALYST
execute pack02.job_empno(:var_name,:var_sal,:var_job) --인자3개
print var_name var_sal var_job
VAR_NAME VAR_SAL VAR_JOB
--------------- ------------- -----------------
FORD 3000 ANALYST
트리거(trigger)
연쇄작용
트리거란 방아쇠라는 뜻으로 데이터베이스에 미리 정해놓은 조건을 만족하거나 어떤 동작이 수행되면
자동으로 실행되는 동작을 말한다.
ex) 주문정보가 입력되면 재고수량을 자동으로 감소시켜라.
기본형
create trigger 트리거명
before/after insert/update/delete <=== 이벤트 발생조건
on 테이블명
begin
insert/update/delete 해라 <=== 트리거 동작
end;
/
실습)
1. emp_tri
empno 정수4, ename 가변문자 10, sal 실수 7,2, 인 테이블 생성
CREATE TABLE emp_tri (empno number(4), ename varchar2(10), sal number(7,2));
2. emp_tri에 이름과 사번이 입력되면 급여를 50으로 수정하는 트리거를 생성하고 확인하시오.
create trigger tri_sal
after insert
on emp_tri
begin
update emp_tri set sal = 50;
end;
/
3. (1111,'HONG') (2222,'KIM') (3333,'LEE') 를 입력하고 급여가 수정되었는지 확인하시오
insert into emp_tri (empno, ename) values (1111,'HONG');
insert into emp_tri (empno, ename) values (2222,'KIM');
insert into emp_tri (empno, ename) values (3333,'LEE'); EMPNO ENAME SAL
---------- -------------------- ----------
1111 HONG 50
2222 KIM 50
3333 LEE 50
정규화 (관계형 데이터베이스 구축 순서)
정규화란 ERD(EntityRelationDiagram)내에서 중복을 찾아서 제거해나가는 과정을 말한다.
정규화를 거친 엔티티 Entity (테이블) 은 더이상 쪼갤수 없을때까지 작아진다.
ERD : 테이블사이의 관계를 나타내는 도형
제 1정규화
: 엔티티에서 하나의 속성(컬럼)이 복수의 값을 갖도록 설계되어있을때 하나의 속성의
단일값(atomic value 원자값)을 갖도록하는 것.
사번 취미
--------------
1111 독서
2222 컴퓨터,영화 <--한개만 갖도록 함
---------------------------------------------- 개선1
사번pk 취미
--------------
1111 독서
2222 컴퓨터 <-- 한개만 갖도록 함, 기본키중복
2222 영화 <-- 한개만 갖도록 함
---------------------------------------------- 개선2 (컬럼을 분할)
사번pk 취미 취미2
-----------------------
1111 독서
2222 컴퓨터 영화<-- 한개만 갖도록 함
제 2정규화
: 주 식별자가 아닌 속성중에서 주식별자 전체가 아닌 일부속성에 종속된 속성을 찾아서 제거하는 것
한개의 컬럼으로 유일성을 만족하지 못한경우 두개이상 컬럼으로 유일성을 만족시킴
기본키 : pk1+ pk2
pk1 pk2 컬럼1 컬럼2 컬럼3
11 남 1
22 여 2
33 남 1
44 남 1
pk2와 컬럼3은 서로가 매칭되므로, 컬럼은 주키2에만 의존하고 있는 것이다.
따라서, pk2와 컬럼3은 테이블을 따로 분리해야 한다.
pk1 pk2 컬럼1 컬럼2 <--분리--> pk2 컬럼3
제 3정규화
: 주 식별자가 아닌 속성들 중에서 종속관계에 있는 속성을 찾아서 제거하는 것이다.
pk1 컬럼1 컬럼2 컬럼3
11 남 1
22 여 2
33 남 1
44 남 1
pk1 컬럼2 <--분리--> pk1 컬럼1 컬럼3
역정규화
: 분리보다 합쳐서 사용하는 것이 유리할 때
컬럼 = 속성 = attribute
테이블 = 엔티티 = Entity
데이터형 = 속성값들의 범위 = 도메인(domain) : 엔티티의 속성들이 가질 수 있는 값들의 집합
data dictionary : 논리적 데이터베이스 설계나 물리적 데이터베이스 설계시
사용되는 용어들의 의미를 정의해 놓은 문서
명명규칙 (naming rule) : 이름을 부여하는 규칙 복합단어 my_king 언더바 활용
관계 (relation) : 일대일 일대다 다대다 --다대다는 피해야한다.
다대다 --> 일다일, 다일다 처럼 변형해야한다