[Oracle] PL/SQL Class 6 - 주민번호 유효성 체크, 트리거(trigger), 업무자동화(연습), 과제(4)

2023.11.14

 

(연습1)

주민번호로 유효성 검사 해보기
--1. 주민번호 자리수( 1  2  3  4  5  6  -   7  8  9  1   2  3  4)

                                          *  *  *   *   *   *     *   *   *   *   *  *   *

                                          2  3  4  5  6  7     8   9  2  3  4  5  

                                          =(1*2)+(2*3) ... = 0 +12+4+10+0+7+24+9+16+6+20+5 = 113
--2. 1번결과 / 11 나머지 = 3
--3. 11 - (2 번결과) = 8
--4. (3번결과)/10 나머지 =8 ==> 주민번호 마지막 숫자와 같다면 유효

create or replace function f_jubun_ck
                                         (v_jubun varchar2 )
                     return varchar2
--                  retrun boolean   // boolean 사용도 가능
is
                     v_sum number;
begin
                    v_sum :=substr(v_jubun, 1, 1)*2+substr(v_jubun, 2, 1)*3+substr(v_jubun, 3, 1)*4+substr(v_jubun, 4, 1)*5
                                   +substr(v_jubun, 5, 1)*6+substr(v_jubun, 6, 1)*7+substr(v_jubun, 8, 1)*8+substr(v_jubun, 9, 1)*9

                                   +substr(v_jubun, 10, 1)*2+substr(v_jubun, 11, 1)*3+substr(v_jubun, 12, 1)*4

                                   +substr(v_jubun, 13, 1)*5;
                    if mod(11-mod(v_sum,11),10) = substr(v_jubun, 14,1) then
                    return '참'; else return '거짓';
--                 return true; else turn false;         // boolean 사용시 참/거짓 사용
           end if;
end;
/

-- 회원가입시 = 또다른 PL/SQL 프로그램에 사용
-- if (f_jubun_ck(v_jumin)) then 
-- insert ~ into ~

-- (아웃풋 적용 예시)
-- select f_jubun_ck('041201-3182518') from dual;       // dual은 사용할 테이블 없을때 지정
-- select goname, f_jumin_check(gojumin) jumin_ck from gogek;

 

 

# Trigger(트리거)

• 지정한 이벤트(DML)가 발생할 때마다 자동으로 실행되는 PL/SQL 프로그램 객체( 호출문 없다. )
• 데이터 흐름 제어문 사용할 수 없음(기본 값) // DML TCL..
• 서버 백업 목적으로도 사용

 

** Trigger 사용목적(4가지)

• 업무 자동화(연쇄 작용, 예= 급여계산, 연금계산, 재고관리...)
• 데이터 백업(복제)
• 이벤트 발생과 사용자 감시 (보안목적)
• 제약조건으로 구현할 수 없는 무결성 구현
• commit 불가

 

** 형식

create or replace trigger trigger_name

                                   [alter | before ] insert or update or delete on table_name   // 이벤트 지정

-- declare   // 생략가능

-- 지역변수; 

begin

             명령;

end;

/

** 행단위 트리거 사용하는 키워드

-- [ :new. 컬럼명] ==> 입력이 발생한 행의 컬럼값을 표현

-- [ :old. 컬럼명] ==> 삭제가 발생한 행의 컬럼값을 표현

 

※사용형태

    :new.컬럼명/ :old.컬럼명
    insert              delete or insert or update

    for each row                      // 없인 못쓴다...*******

 

(예시1)

create table dept_bk as select *from dept;

-- dept table에 입력이 발생하면 dept_bk table에 입력이 발생하는 트리거

 

create or replace trigger t_dept_in

           after insert on dept

           for each row              ==> 기본 명령어로 입력하는 습관 들일 것...!!

begin

           insert into dept_bk values( :new.deptno, :new.dname, :new.loc );

end;

/

 

아웃풋 입력방법

SQL> insert into dept values(66, '기술부', '경기')

 

(연습2)

--dept table에 삭제가 발생하면, dept_bk table에 삭제가 발생하는 트리거

create or replace trigger t_dept_del 
                            after delete on dept
                            for each row           // 기본입력 필수
begin
              dept_bk where deptno=:old.deptno;
end;
/

 

(연습3)

--dept table에 변경이 발생하면, dept_bk table에 변경@ㄴ이 발생하는 트리거

create or replace trigger t_dept_up
                            after update on dept
                            for each row
begin
              delete dept_bk where deptno=:old.deptno;
              insert into dept_bk(deptno,dname,loc) values( :new.deptno, :new.dname, :new.loc);
--            update dept_bk set dname=:new.dname, loc=:new.loc where deptno=:old.deptno;   // 이렇게 해도 된다
end;
/

 

-- (연습2+연습3) 간략하게 코딩하는 방법

create or replace trigger t_dept_backup

                   alter insert or delete or update on dept

                   for each row

begin

                 if inserting then

                         insert into dept_bk values(:new.deptno, :new.dname, :new.loc);

                 elsif deleting then

                         delete dept_bk where deptno=:old.deptno;    

                 elsif updating then

                         update dept_bk set dname=:new.dname, loc=:new.loc where deptno=:old.deptno;  

입력확인 방법

SQL> insert into dept_bk values(66, '기술부', '경기');

 

 

## 업무자동화(테이블 생성 + 트리거)

(연습4)

emp 테이블 생성 row 3개 생성

empno	ename	loan_ck pk
1	한국	N
2	직업	N
3	교육	Y

 

loan 테이블 생성 row 3개 생성 

eno fk	amount	loan_date

 

drop table emp;    // 테이블 삭제
drop table loan;    // 테이블 삭제

-- 2개의 테이블 생성해서 트리거 실행

-- emp 테이블 생성

create table emp(                                                                         
                            empno number(3) constraint emp_empno_pk primary key,
                            ename varchar2(10),
                            loan_ck char(4) default 'N' 
);
insert into emp(empno, ename) values(1, '홍동우');
insert into emp(empno, ename) values(2, '유승민');
insert into emp(empno, ename) values(3, '정반장');

-- loan 테이블 생성
create table loan(
                            eno number(3), 
                            amount number(10),
                            loan_date date default sysdate,
                            constraint loan_eno_fk foreign key(en@o) references emp(empno)
);

create or replace trigger loan_tri
                            after insert or delete or update of eno on loan           // update+of+테이블명은 특정 테이블만 업데이트
                            for each row
begin
            if inserting then
                    update emp set loan_ck = 'Y' where empno= :new.eno;
             elsif deleting then
                    update emp set loan_ck = 'N' where empno= :old.eno;
             elsif updating then
                    update emp set loan_ck = 'Y' where empno= :new.eno;
                    update emp set loan_ck = 'N' where empno= :old.eno;
            end if;
end;
/

 

SQL> insert into loan(eno,amount) values(1,200000);

 

(연습5)

-- update cascade 구현
-- 부서 테이블의 부서번호가 변경되면, 그 부서를 참조하는 사원의 부서번호도 함께 변경
create or replace trigger t_cas
                     after update of deptno on dept
                     for each row
begin
                    update sawon set deptno= :new.deptno where deptno= :old.deptno;
end;

/

변경확인

SQL> SQL> update dept set deptno=20 where deptno=222;

222로 변경확인

 

원상복구 확인

SQL> update dept set deptno=20 where deptno=222;

 

 

## 오류 생성하는 방법( 사용자정의 에러 함수)

• raise_application_error( -20001 ~ 20999, '메세지' )  메세지는 1024byte이내 작성.
• rollback 명령 포함, 프로그램 종료

 

(예제3)

부서 테이블에서 부서정보를 삭제하면 에러사용

create or replace trigger t_dept_undel

                   after delete on dept

                   for each row

begin

                 raise_application_error(-20001, '부서정보를 삭제할 수 없습니다.');

end;

/

SQL> delete dept where deptno=40;

 

 

 

(과제1)

사원의 급여가 변경되면, 아래 4문제를 모두 연계해서 코딩하시오.

1. 에러발생

create or replace trigger dept_error
               after update or insert or delete on dept
               for each row
begin
              raise_application_error(-20002, '에러를 발생 시키셨네요?');
end;
/

SQL> update dept set deptno=40                  // dept 테이블에 40번 부서를 업데이트 하고자 할때 에러메시지 발송.

 

2. 변경한 정보(사용자, 날짜(시간포함), 변경한 사번, 변경전 급여, 변경후 급여)를 black_list 테이블 생성해서 입력.

drop table black_list;
create table black_list(
                      ssesion_name varchar2(20) default user,
                       time varchar2(30) default to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),
                       sabun varchar2(10),
                       before_sapay number(10),
                       after_sapay number(10) 
);
create or replace trigger t_gamsi
                      after insert or delete or update on sawon
                      for each row
begin
                     insert into black_list(sabun, before_sapay, after_sapay) values(:new.sabun, :old.sapay, :new.sapay);
end;
/

 

 

3. 변경하려는 급여가 기존 급여보다 적거나, 기존 급여의 20% 초과하면 에러발생(단, 부장은 제외)

create or replace trigger sapay_money
               after update of sapay on sawon
                for each row

--             when (old.sajob != '부장')                   // 조건 값을 미리 줘도 됨.                      (참고)old 앞에 : 는 안쓴다.
begin
                if (:old.sajob !='부장') then
                               if :old.sapay>:new.sapay or :new.sapay>=:old.sapay*1.2
                                         then raise_application_error(-20999, '급여를 변경할 수 없습니다.');
                               end if;
                end if;
end;
/ 

SQL> update sawon set sapay=100 where sajob='부장';

SQL> select *from sawon wherer sajob='부장';

 

 

4. 급여를 변경할 수 없으면서 변경을 시도한 정보는 black_list 테이블 입력

create or replace trigger sawon_sal_mod
                             after update of sapay on sawon
                             for each row

declare 

                    pragma autonomous_transaction;                    // commit 사용하기 위해 필수 입력
begin
--                  insert into black_list(sapay, saname )
--                  values(:old.sal, :new.sal) 
--                  raise_application_error(-20003, '급여를 변경할 수 없습니다.');
--                  commit;                                       // 오류코드( raise_application_error )는 rollback 기능이 포함되므로
                                                                            반드시 commit 해줘야 됨

                    insert intoblack_list(sabun, before_sapay, after_sapay)values(:new.sabun, :old.sapay, :new.sapay);

                    commit;

                    raise_application_error(-20999, '급여를 변경할 수 없습니다.');

end; 
/

 

## 트리거(Trigger) 상태변경

 

SQL> alter trigger trigger_name [enable | disable];                    // enable과 disable 로 트리거 단위상태 변경(관리 가능)

SQL> alter table table_name all trigger [enable | disable];        // 테이블 단위로 관리 가능