DB 7차시 - 패키지, 트리거, 데이터모델링, ER다이어그램

* 패키지(Package)

  - 관련있는 프로시저/함수를 효율적으로 관리하기 위한 모듈화시켜 배포

 

  --패키지 선언

  CREATE OR REPLACE PACKAGE my_package

  IS

    PROCEDURE getEmployee(in_id IN employees.employee_id%TYPE,

        out_id OUT employees.employee_id%TYPE,

        out_name OUT employees.first_name%TYPE,

        out_salary OUT employees.salary%TYPE); //시그니처

   

   FUNCTION getSalary(p_no employees.employee_id%TYPE)

    RETURN NUMBER;

  END;

 

 

--패키지 본문 정의

 

CREATE OR REPLACE PACKAGE BODY my_package

IS

    --프로시저 내용

   PROCEDURE getEmployee(in_id IN employees.employee_id%TYPE,

        out_id OUT employees.employee_id%TYPE,

        out_name OUT employees.first_name%TYPE,

        out_salary OUT employees.salary%TYPE)

   IS

 

   BEGIN

    SELECT employee_id, first_name, salary

    INTO out_id, out_name, out_salary

    FROM employees

    WHERE employee_id = in_id;

   END;

 

   --함수의 내용

   FUNCTION getSalary(p_no employees.employee_id%TYPE)

    RETURN NUMBER IS

    v_salary NUMBER;

    BEGIN

    SELECT salary INTO v_salary

        FROM employees    

        WHERE employee_id = p_no;

 

    RETURN v_salary;

    END;--함수 end

 

END; --패키지 end

 

 

-- 함수 실행

SELECT my_package.getSalary(100) FROM dual;

 

-- 프로시저 실행

  DECLARE

    p_id NUMBER;

    p_name VARCHAR2(50);

    p_salary NUMBER;

 

  BEGIN

    my_package.getEmployee(100, p_id, p_name, p_salary);

    dbms_output.put_line(p_id||',  '||p_name||',  '||p_salary);

 

  END;

 

 

<mission hr> employees2에서 retire_date 컬럼을 추가하자.

  ALTER TABLE employees2 ADD(retire_date date);

  그리고 아래의 내용에 맞는 package,  procedure 만들어 보자.

 

--패키지 선언부

CREATE OR REPLACE PACKAGE hr_pkg IS

    --신규 사원 입력

    PROCEDURE new_emp_proc(ps_emp_name IN VARCHAR2,

    pe_email IN VARCHAR2,

    pj_job_id IN VARCHAR2,

    pd_hire_date IN VARCHAR2);

    -- TO_DATE(pdhire_date, 'YYYY-MM-DD');

 

   -- 퇴사 사원 처리

   PROCEDURE retire_emp_proc(pn_employee_id IN NUMBER); //데이터는 있지만 우리의 회원목록에서는 사라짐, DELETE하는 순간 돈을 읽는 것임 함부로 지우면 안됨.

 

END hr_pkg;

 

 

-- 패키지 본문

CREATE OR REPLACE PACKAGE BODY hr_pkg IS

  -- 신규 사원 입력  

  PROCEDURE new_emp_proc ( ps_emp_name   IN VARCHAR2,

              pe_email IN VARCHAR2,

              pj_job_id IN VARCHAR2,    

                           pd_hire_date  IN VARCHAR2)

  IS

    

    vn_emp_id    employees2.employee_id%TYPE;

    vd_hire_date DATE := TO_DATE(pd_hire_date, 'YYYY-MM-DD');

  BEGIN

     -- 신규사원의 사번 = 최대 사번+1

     SELECT NVL(max(employee_id),0) + 1

       INTO vn_emp_id

       FROM employees2;

  

    

    INSERT INTO employees2 (employee_id, last_name, hire_date, email, job_id)

                   VALUES (vn_emp_id, ps_emp_name,  vd_hire_date, pe_email, pj_job_id);

                   

    COMMIT;

    

    EXCEPTION WHEN OTHERS THEN

              DBMS_OUTPUT.PUT_LINE(SQLERRM);

              ROLLBACK;               

  

  

  END new_emp_proc;

  

--SQL%ROWCOUNT : 가장 최근에 수행된 SQL 문에 의해 영향을 받은 행의 갯수 (정수 값)

--SQL%FOUND : 가장 최근에 수행된 SQL 문에 의해 영향을 받은 행의 갯수가 한 개 이상이면 TURE가 되는 Boolean속성

--SQL%NOTFOUND : 가장 최근에 수행된 SQL 문에 의해 영향을 받은 행이 없으면 TRUE가 되는 Boolean 속성

                   

  -- 퇴사 사원 처리

  PROCEDURE retire_emp_proc ( pn_employee_id IN NUMBER )

  IS

    vn_cnt NUMBER := 0;

    e_no_data    EXCEPTION;

  BEGIN

    -- 퇴사한 사원은 사원테이블에서 삭제하지 않고 일단 퇴사일자(RETIRE_DATE)를 NULL에서 갱신한다.

    UPDATE employees2

       SET retire_date = SYSDATE

     WHERE employee_id = pn_employee_id

     AND retire_date IS NULL;

       

    -- UPDATE된 건수를 가져온다.    

   vn_cnt := SQL%ROWCOUNT;

    

    -- 갱신된 건수가 없으면 사용자 예외처리

    IF vn_cnt = 0 THEN

       RAISE e_no_data;

    END IF;

    

    COMMIT;

    

    EXCEPTION WHEN e_no_data THEN

                   DBMS_OUTPUT.PUT_LINE (pn_employee_id || '에 해당되는 퇴사처리할 사원이 없습니다!');

                   ROLLBACK;

              WHEN OTHERS THEN

                   DBMS_OUTPUT.PUT_LINE (SQLERRM);

                 ROLLBACK;              

  

  

  END retire_emp_proc;

  

END  hr_pkg;

 

 

EXECUTE hr_pkg.new_emp_proc('홍길동', 'aa@aa.com', 'AD_VP', '2017-03-15');

 

EXECUTE hr_pkg.retire_emp_proc(100);

 

 

 

 

**트리거(Trigger)

-트리거란 특정 테이블의 데이터에 변경이 이루어 졌을 때 자동으로 다른 어떤 작업이 함께 수행되도록 설정하기 위해

 

set serveroutput on;

CREATE TABLE emp11(

            empno NUMBER PRIMARY KEY,

            ename VARCHAR2(20),

            job VARCHAR2(20)

)

 

CREATE OR REPLACE TRIGGER trg_01

            AFTER INSERT

            ON emp11

            BEGIN

                DBMS_OUTPUT.PUT_LINE('신입 사원이 추가 되었습니다.');

END;

 

INSERT INTO emp11 VALUES(1, '홍길동', '개발');

INSERT INTO emp11 VALUES(2, '김길동', '개발'); 

->신입 사원이 추가 되었습니다.

 

--트리거 유형

문장 레벨 트리거 - DML문을 실행할 때 단 한번만 트리거 실행

행 레벨 트리거(FOR EACH ROW) - DML문 여러번 실행 => 트리거 여러번 수행

 

CREATE TABLE sal01(

    salno NUMBER PRIMARY KEY,

    sal NUMBER,

    empno NUMBER REFERENCES emp11(empno)

)

 

CREATE SEQUENCE sal01_salno_seq;

**시퀀스는 롤백이 안되서 중간에 오류나면 그 사이의 값을 건너뛰게 되니 drop하고 다시 설정해주기.

 

CREATE OR REPLACE TRIGGER trg02

            AFTER INSERT

            ON emp11

            FOR EACH ROW

            BEGIN

                INSERT INTO sal01 VALUES(  sal01_salno_seq.NEXTVAL, 10000, :NEW.empno);

END;

 

• INSERT -> OLD : NULL, NEW : 입력값

• UPDATE -> OLD : 변경 전 값, NEW : 변경 후 값

• DELETE -> OLD : 삭제 전 값, NEW : NULL

 

INSERT INTO emp11 VALUES(3, '김바보', '개발');

 

퀴즈 hr> 사원이 삭제되면 그 사원의 급여정보(sal01) 테이블에서 해당 로우도 함께 삭제되도록 트리거를 구현해보자.

 

->CREATE OR REPLACE TRIGGER trg03

            AFTER DELETE

            ON emp11

            FOR EACH ROW

            BEGIN

               DELETE FROM sal01 WHERE empno=:OLD.empno;

END;

 

->DELETE FROM emp11 WHERE empno = 3;

 

 

**데이터 모델링

 

 

 

 

-기본 entity : 사원, 부서, 회원

-행위 entity : 주문, 예약 렌트

 

 

sprint현황판 &스크럼은 365일 내내 전속력으로 돌릴 수 있음.

삼성화재, 메리츠화재 : 차세대프로젝트 5년단위개발

 

-엔터티타입 : 업무에 필요하고 유용한 정보를 저장하고 관리하기 위한 것으로 영속적으로 존재하는 단위

 

사고는 행위에서 시작하기

 

주식별자 : PK, FK

복합식별자 : PK가 2개

대리식별자 : 복합식별자가 너무 많아지면 새로운 식별자 생성

 

주식별자 관계 : 조인할 필요없이 WHERE절 한방에 끝남, 확장된 개념이고 서로 관련이 있을때(결합력이 높을때)

비식별자 관계 : 과도한 조인을 하게 됨, 부서 사원 관계로 업무적인 관계일때

 

 

entity type, relation type => 2개만 잘하기

 

1:1 관계 나중에 반정규화로 하나의 엔터티타입으로 정의할 수 있다. 구매신청-구매주문

대부분은 1: M관계 부서-사원

M;M관계 주문-제품 (중간에 모델링에 또다른 엔터티타입이 되어 1:M관계로 만들게 됨)

 

 

부서코드(FK)는 처음부터 넣는 값이 아니라 relation이 추가된 후 부여받는 값이다.

 

 

 

 

1. 엔티티타입 도출 : 사각형

1. ☞ 첫 번째 반드시 업무에서 필요하고 관리하고자 하는 정보이어야 한다. (예. 환자, 토익의 응시횟수…)

2. ☞ 두 번째 유일한 식별자에 의해 식별이 가능해야 한다.

3. ☞ 세 번째 영속적으로 존재하는 엔티티의 집합이어야 한다 (“한 개”가 아니라 “두 개 이상”)

4. ☞ 네 번째 엔티티타입은 업무 프로세스에 의해 이용되어야 한다.

5. ☞ 다섯 번째 엔티티타입에는 반드시 속성이 있어야 한다.

6. ☞ 여섯 번째 엔티티타입은 다른 엔티티타입과 최소 한 개 이상의 관계가 있어야 한다.

2. 관계 : 마름모 (커디널리티 중요)

3. 속성 : 타원

4. 식별자 : 줄

 

문제1> A택배회사의 직원은 입사하면서 직원 한 명당 한대의 자동차를 할당 받게 된다.

직원은 직원번호, 이름, 입사일을 기록하며, 할당받은 자동차는 자동차번호, 색상, 모델명을 기록하게 된다.

 

 

 

문제2>B병원에서 환자는 한명의 의사만 있고 의사는 여러명의 환자를 진료한다.

환자는 환자번호, 이름, 나이, 보험번호를 기록하며, 의사는 의사번호, 이름, 담당부서

들을 기록하게 된다.

 

 

문제3>

1. 각 직원은 하나 또는 그 이상의 프로젝트에 배정될 수 있으며 프로젝트가 없는

직원도 있을 수 있다. 그러나 각프로젝트는 반드시 한 명 이상의 직원이 배정되어야 한다.

2. 직원은 이름,호봉,특기,생년월일을 속성으로 가지며, 프로젝트는 프로젝트 번호,

프로젝트내역, 시작일, 예상완료일을 속성으로 갖는다.

 

 

위의 예제들을 엔티티타입과 관계 그리고 속성들을 설정하여 다이어그램을

작성하시오.

 

 

1)무지개 슈퍼에서는 고객 명단을 관리라는데,

각 고객마다 고객번호, 고객명, 주소 및

주소번호를 기록합니다.

 

2)또한 매장에 있는 물품들의 명세를 관리하는데,

각 물품마다 물품명, 단가, 수량 및

각 물품에 고유한 물품 번호를 기록한다.

 

3) 어느 한 고객이 여러 물품들을 구입하며,

또한 어느 한 물품을 여러 고객들이 구입한다.

고객이 물품을 구입할때 마다 날짜와 구입 수량 및 총 구입 가격을

기록한다.

 

4) 물품들은 제조회사에서 제조되므로,

제조회사들에 대한 데이터도 관리하며,

각 제조회사마다 제조회사명, 주소, 전화번호 및 각 제조회사에

대한 제조회사번호를

기록한다.  물품이 제조될 때마다 날짜와 수량을 기록한다.

 

 

* 도서 대여점 관리 프로그램

 

요구사항 분석

 

1. 도서에 대해서 도서번호, 도서명, 출판사, 지은이, 장르, 가격, 대여유무,

    등록일을 저장한다. 도서번호에는 장르와 위치를 넣어서 도서를 찾기 쉽게한다.

 

2. 도서는 코믹스, 순정, 애정판, 소설, 월간지 장르로 구분한다. 장르마다 대여료와

     대여기간, 연체금이 정해져 있다. 도서는 한 장르에만 속해 있다. 대여 중인

     책은 대여 될 수 없다.

 

3. 회원에 대해서 회원번호, 이름, 전화번호, 주소, 대여 중인 책, 마일리지를 저장한다.

    회원은 도서를 대여 할 수 있다. 회원은 여러 권의 책을 동시에 빌릴 수 있다.

    회원은 도서를 대여할 때마다 대여금액의 10%를 마일리지로 적립받을 수 있고,

    도서를 대여할 때 현금처럼 사용할 수 있다.

 

4. 사원에 대해서 사원번호, 이름, 주소, 전화번호, 비밀번호를 저장한다.

    사원은 고객등록, 도서등록, 도서대여, 도서반납, 고객조회를 할 수있다.

    사원중에 사원을 관리하는 관리자가 있어서 사원을 추가/삭제를 할 수 있고,

    일별, 월별, 연별 매출액을 조회할 수 있다.

 

 

 

-다대다 관계일때만 중간에 하나의 엔터티를 추가해줌(borrow)

-점선은 비식별자 관계(book과 직원), 실선은 식별자 관계(상속관계)