220207 저장 프로시저, 저장 함수, 커서, 패키지, 트리거

4. 저장 프로시저★★★
   : PL/SQL을 저장해놓고 필요에 따라 호출해 사용할 수 있게 오라클이 제공하는 것
     ---> 성능 향상, 재활용 가능, 간단함, 호환성 문제 해결

    ※주의! 프로시저 생성 시, 각 블록마다 범위 잡고 하나씩 실행해야 함 안그럼 지멋대로 범위 잡힘

  04. 자바 연동해 프로시저 실행
    * ex1
       1. 프로시저 생성 (오라클)
         create or replace procedure del_all
         IS
         begin
             delete from emp01;
         end;
         select*from user_source;
       2. 테이블 생성 (오라클)
         drop table emp01 purge;
         create table emp01 as select*from emp; --복사본 테이블 생성
         select*from emp01;

       3. 자바에서 실행 (자바)
         - 프로시저는 CallableStatement 클래스로 실행
         - 구문을 변수 sql에 저장 후 Connection의 prepareCall(sql)을 통해 CallableStatement 객체에 보낸다
         - cs.execute()로 sql문의 프로시저를 실행
         - 정상적으로 프로시저가 실행된다면 CallableStatement를 close, 다음 Connection을 close

       4. 실행 확인 (오라클)
         - 프로시저(delete from emp01)가 제대로 실행되었는지 확인

    * ex2
       1. 프로시저 생성 (오라클)
         create or replace procedure del_ename(vename emp01.ename%type)
         IS
         begin
             delete from emp01 where ename=vename; 
         end;
         select*from user_source;
       2. 테이블 생성 (오라클)
         drop table emp01 purge;
         create table emp01 as select*from emp; --복사본 테이블 생성
         select*from emp01;
         (아니면 insert into emp01 select*from emp; 로 데이터 복사해서 넘김)
         (만약 프로시저 생성(create) 전이었다면 rollback을 사용해도 괜찮을 것... 아마도)
       3. 자바에서 실행
         - 프로시저는 CallableStatement 클래스로 실행
         - 구문을 변수 sql에 저장 후 Connection의 prepareCall(sql)을 통해 CallableStatement 객체에 보낸다
         - 입력받은 값을 setString()을 이용해 ?에 전달 (매개변수 vename에 들어갈 값)
         - cs.execute()로 sql문의 프로시저를 실행
         - 정상적으로 프로시저가 실행된다면 CallableStatement를 close, 다음 Connection을 close

       4. 실행 확인
         - 입력한 이름(SCOTT, SMITH)에 대한 row가 삭제되었음을 확인

    * ex3
       1. 프로시저 생성 (오라클)
         create or replace procedure sel_customer
                    ( vname in customer.name%TYPE,
                      vemail out customer.email%TYPE,
                      vtel out customer.tel%TYPE)
         is
         begin
select email, tel into vemail, vtel from customer where name = vname;
         end;
       2. 테이블 생성 (오라클)
         insert into customer values(customer_no_seq.nextval,?,?,?,?,sysdate);
         select*from customer;

       3. 바인드 변수 생성 (오라클)
         variable var_email varchar2(20);
         variable var_tal varchar2(20);
       4-1. 오라클에서 실행

       4-2. 자바에서 실행
         - 프로시저는 CallableStatement 클래스로 실행
         - 구문을 변수 sql에 저장 후 Connection의 prepareCall(sql)을 통해 CallableStatement 객체에 보낸다
         - 매개변수 vename에 전달될 값은 setString()을 이용해 ?에 전달
         - 매개변수를 통해 리턴될 값(out parameter)은 cs.registerOutParameter을 통해 불러온다
            cs.registerOutParameter(물음표 위치, java.sql.Types.VARCHAR)
            --> 리턴받을 바인드변수의 데이터형으로 받는다 (VARCHAR로 써야함. varchar2가 아님!)
         - cs.execute()로 sql문의 프로시저를 실행
         - 리턴받는 out 매개변수는 getString(물음표 위치)을 이용해 원하는 매개변수 값을 출력할 수 있다
         -  정상적으로 실행되었다면 CallableStatement를 close, 다음 Connection을 close

  05. 저장 함수
      : 함수의 실행 결과를 리턴받을 수 있음 (저장 프로시저와 유사)
        -> 리턴받을 지역변수와 리턴받을 값을 반드시 기술해야 함    --이부분 글자색으로 아래 리턴이랑 연결
         create or replace FUNCTION func_name
          (argument [mode] 데이터타입) --필요하다면 기술
             RETURN date_type;
         IS
             지역변수 선언
         begin
             내용
             RETURN 리턴받을 값;   
         end;
    +) 저장함수 실행★ --프로시저와 상당히 다르므로 주의할 것
        execute(exec) :바인드변수 :=func_name(argument)
                             ---> 지역변수로 리턴된 값이 바인드변수에 저장됨
 
    * ex1. emp 테이블에서 특정 사원의 sal을 200% 인상한 결과를 리턴하는 저장 함수

      +) 리턴된 값이 지역변수에 저장되어 호출 시 출력되는 방식이므로 (out 매개변수로 빼는게 아니라),
          select sal, cal_bonus(7788) from emp where empno=7788; 와 같이 select문으로도 출력이 가능

    * ex2. ename을 매개변수로 전달해 해당 사원의 직급job을 리턴하는 저장 함수

      +) select문 출력 시 where 조건 반드시 써야 하는 이유: 매개변수로 출력된 값이 모든 row에 호출되기 때문

  06. 커서
      : 두 개 이상의 데이터를 출력할 때 사용
        declare (create procedure IS ~~~ 이 부분과 같음. 핵심은 선언부에 커서를 선언해야 한다는 것!)
           CURSOR 커서명 IS select_검색할내용;    --커서 선언
        begin
           OPEN 커서명;          --커서 오픈
              loop
                 FETCH 커서명 INTO 변수;  ---> 인출: 해당되는 row에 도달하면 그 값을 변수에 전달
                 EXIT when 커서명%NOTFOUND;
              END loop;                     └> 처리할 row가 없을 때까지(not found) fetch 반복하며 해당되는 row를 계속 변수에 전달
           CLOSE 커서명;        --커서 클로즈
        end;
     +) 속성
        %notfound: 커서 영역 자료가 모두 fetch되면 true 리턴
        %fount: 커서 영역에 fetch되지 않은 자료가 남아있으면 true 리턴
        %isopen: 커서가 open된 상태라면 true
        %rowcount: 커서가 리턴한 레코드의 개수

   * ex1. dept 테이블의 모든 데이터를 출력
     01. loop ~ end 사용
       - open ~ loop ~ fetch ~ exit ~ end loop ~ close 순서
        begin
            DBMS_OUTPUT.PUT_line('부서번호   /   부서명 /   지역명 ');
            DBMS_OUTPUT.PUT_line('-----------------------------------------');
            open c1;
               loop
                 fetch c1 into vdept.deptno, vdept.dname, vdept.loc;
                 exit when c1%notfound; --c1에 더이상 값이 존재하지 않는다면(not found) 끝
             ----> %notfound=c1에 더이상 값이 할당되지 않는 상태.
                       즉, 모든 처리가 끝났으므로 exit로 루프 빠져나가는 것
                 DBMS_OUTPUT.PUT_line(vdept.deptno||'/'||vdept.dname||'/'||vdept.loc);
             ----> 출력함수가 exit 다음에 있는 이유: c1에 아무것도 할당되지 않으면 출력시킬 게 없음
                       즉, c1에 값이 할당되어있는 상태에서만 출력되고 아닌 경우 출력 안되고 exit로 끝나게 하는 것
             >> 순서: fetch로 c1에 값을 할당 -> 할당된 값이 있다면 DBMS~로 출력
                                                     -> 할당된 값이 없다면 exit로 루프 끝 (DBMS 출력 하지 않음)
              end loop;
            close c1;
        end;

     02. for loop 사용
       - for loop : 루프 조건에 커서가 있으면 루프가 반복될 때마다 자동으로 open-fetch(인출)-close 과정이 처리됨 -> 간결하게 사용할 수 있음!
        create or replace procedure cursor_sample02
        is
            vdept dept%rowtype;     --테이블 전체를 참조하겠음 (데이터까지 전부)
            cursor c1 is select*from dept; --dept의 전체 테이블(*)을 커서로 선언
        begin
            DBMS_OUTPUT.PUT_line('부서번호   /   부서명 /   지역명 ');
            DBMS_OUTPUT.PUT_line('-----------------------------------------');
               for vdept in c1 loop     --커서 c1에 vdept 테이블의 row값이 할당되는 동안 루프
               -- 조건에 c1가 있다면 -> 루프할 때마다 알아서 open해서 fetch하고 루프 끝나면 close됨
               ---> 즉, 여기선 open, fetch, close를 쓸 필요가 없음 이걸로 오류 두번 났다
                 exit when c1%notfound; --c1에 할당된 값이 존재하지 않는다면(not found) 끝
                 DBMS_OUTPUT.PUT_line(vdept.deptno||'/'||vdept.dname||'/'||vdept.loc);
              end loop;
        end;

   * ex2. deptno을 매개변수로 전달해 부서에 소속된 사원 정보를 커서를 이용해 출력

5. 패키지
   : 관련 있는 프로시저를 효율적으로 관리 + 패키지 단위로 배포할 때 사용
   : 패키지 선언(명세부:프로시저, 함수들을 선언)+몸체 선언(몸체부:프로시저, 함수들 각각의 내용 정의)으로 구성 ---> 두 개 모두 정의해야 함!

  * 패키지 생성

    +) 저장함수 출력할 때 select sal, exam_pack.cal_bonus(7788) from emp where empno=7788; 사용 가넝

  * 패키지 검색
    ex. DBMS_output 패키지 검색 예
         conn system/1234
         select object_name from dba_objects
         where object_type='PACKAGE' and object_name like 'DBMS_%' order by object_name;

6. 트리거
   : 어떤 이벤트가 발생했을 때 자동으로 실행되는 프로시저
         create trigger 트리거명
         timing(before/after) event(insert/update/delete) on 테이블명
         [for each row]
         [when 상황]
         begin
           내용
         end;
    - timing: before(event 전에 begin~end 실행) / after(event 후에 begin~end 실행)
    - event: 트리거 실행시킬 이벤트를 결정 (insert/update/delete 중)
    - [for each row]
          생략-> 문장 레벨 트리거: 트리거 설정된 테이블에 이벤트 실행 시 한 번만 트리거 발생
          명시-> 행 레벨 트리거: 각각의 행이 변경될 때마다 트리거 발생
    - [when 상황]
          행 레벨 트리거에서만 설정 가능. 이벤트 발생 시 구체적인 조건을 부여할 때 사용

  * ex1. emp01 테이블에 사원을 등록하면 메시지가 출력되는 트리거
         create trigger emp01_trigger
         after insert on emp01    --emp01에 insert 이벤트가 발생한 후에 트리거 발생한단 의미
         -- for each row 생략-> 문장 레벨 트리거: 한 번만 트리거 발생 (insert~; 한번, insert~; 한번)
         begin
             DBMS_OUTPUT.PUT_LINE('새로운 사원이 등록되었습니다.');
         end;     --발생할 트리거의 내용

  * ex2. emp01 테이블에 사원을 등록하면 sal01 테이블에 급여 정보를 자동으로 추가하는 트리거
         create or replace trigger sal_trigger
         after insert on emp01
         for each row --> 행 레벨 트리거: 행이 변경될 때마다 트리거 발생
         begin
             insert into sal01 values(sal01_salno_seq.nextval,100,:new.empno);
                --:new.empno--> 참조하고 있는 테이블 emp01에 새로 insert된 empno를 가져옴 
             DBMS_OUTPUT.put_line('급여가 등록되었습니다.');
         end;

  * ex3. emp01 테이블에서 사원이 삭제되면 sal01의 급여 정보도 함께 삭제되는 트리거
         delete from emp01 where empno=1;
         ---> 오류 보고 -
                 ORA-02292: integrity constraint (SCOTT.SYS_C007054) violated - child record found
              = empno를 참조하고 있는 테이블sal01이 존재하기 때문에 삭제할 수 없음
                 (on delete cascade 옵션이 붙어있어야 가능한데, sal01 만들 때 옵션을 넣지 않았음)
         ---> 하지만 트리거로 데이터를 삭제하도록 조치할 수 있음!!
         create or replace trigger del_trigger
         after delete on emp01
         for each row
         begin
             delete from sal01 where empno=:old.empno;
             --:old.empno--> 참조하고 있는 테이블 emp01에 존재하고 있던 해당 empno를 가져옴
         end;

  *저장프로시저와 트리거의 차이점
      저장프로시저는 execute를 이용해 직접 실행시켜야 하지만 트리거는 오라클에서 자동으로 실행시킨다