12. PL/SQL - PROCEDURE, User-defined FUNCTION, TRIGGER, CURSOR

 PL/SQL 이란 비절차적 언어인 SQL를 절차적 언어와 같이 동작하도록 하는 명령어들을 말한다.

 선언부와 시작/종료 부를 기본으로 포함하고 변수나 SQL을 포함하도록 설계한 후 필요시 호출하거나 특정 조건시 자동으로 실행된다.

 

 

PL/SQL 변수선언

PL/SQL에서 변수는 아래와 같이 단일변수, 로우변수(record type), 테이블변수(table type)이 있다.

 

   기본타입( number,date,varchar2 등 )의 단순 변수
   여러 값을 갖는 Row형식의 Record Type 변수
   여러 Row를 갖는 table 형식의 Table Type 변수

(Recorde type, Table type은 절차지향 언어에서 변수만 있는 클래스와 유사하다. )

 

Record type 변수 선언 
  type 타입변수명 is record( 컬럼명1 컬럼1타입, 컬럼명2 컬럼2타입, ... )

 

Table type 변수 선언
  type 타입변수명 is table of 다른테이블(혹은 위에서 정의한 recordtype) %rowtype   //단일컬럼도 가능은하다.

   * 위에서 선언한 Record type(Table type)은 타입만 만든것이고, 해당 타입으로 실제 사용할 변수를 만들어줘야 한다.
     ex) 타입명1  type_vari   -  이름이 타입명1 이라는 타입의 변수 type_vari 를 생성한다.

 

Scalar 변수
  sql에서 제공하는 기본 변수 타입을 정의하는 변수.

Reference 변수
   이미 변수타입, 크기가 정의되어 있는 컬럼에서 동일한 타입을 가져오는 변수.

 

      단일 컬럼의 타입을 가져오는 경우

          <뱃겨오려는 컬럼명> % type

 

      다중 컬럼의 타입들을 가져오려는 경우

            <뱃겨오려는 테이블> % type

 

Procedure

 프로시젼은 특정 동작을 반복적으로 사용하기 위해 정의하고 필요시마다 호출해 사용할 수 있는 구문이다.

 

 정의 기본형

    DECLARE [ OR REPLACE ] <프로시젼명> (<매개변수명> <in/out/inout> <매개변수타입>)

    is

       [변수 선언]

       begin

           <실행문>

       end;

 

 호출

   exec <프로시젼명>(in매개변수, :out매개변수)   -   out으로 받을 값엔 변수명 앞에 : 를 기입한다.

 

    Exception 처리
        exception when exception이름 then 실행문;

   if 조건문
        if( 조건 ) 

                then 실행문1
        else if(조건)
                then 실행문2
        end if;

 

   case 조건문

   loop 반복문 
        실행문 내용을 반복해서 실행하다가, exit; 가 호출되면 loop문을 빠져나간다.
        loop
              실행문
                if( ) then exit;
                end if;
        end loop;

 

   for 반복문  

        i가 n1부터 n2 번까지 반복해서 loop안에 값을 실행한다.

        for i in n1..n2  loop
            실행문
        end loop;

 

 

Cursor

   테이블에서 행을 가리키고 있는 객체, LS/SQL문에서 Cursor를 정의해서 사용한다.

 

  커서 정의
      선언부와 is begin 사이 정의구간에 아래와 같이 생성한다.
           cursor <커서명> is <서브쿼리> 
      서브쿼리가 반환하는 테이블을 가르키는 커서가 된다.

  커서 호출
     open <커서명>
          loop
                  fetch 커서명                          -     행을 읽고 다음행으로 커서를 옴긴다.
                  into 행을 받을 객체                 -     커서가 읽어올 테이블의 하나의 행
              exit when <커서명>%notfound      -     커서의 다음행이 없을때 loop를 나갈 exit 실행 
          end loop;
     close <커서명>;

 

 

Trigger

DML이 실행될때 자동으로 추가적인 작업을 하는 객체로 선언해놓은 동작 조건에 따라 저장해놓은 실행문을 자동 실행한다. 별도의 호출이 필요없이 바라보고 있는 테이블에 이벤트가 발생하면 자동으로 실행된다. 주의해야할 점은 트리거 실행중 오류가 발생하면 바라보고 있는 테이블에 트리거 실행전 작업은 남아있기 때문에 데이터 무결성이 훼손될 수 있기에 주의해야 한다.

    선언 기본형
       create trigger <트리거명 >
          after(before) <DML 중 한가지>
          on 적용할테이블명
              [for each row] - 한번의 쿼리로 여러 행이 개행될 경우 각 행마다 실행하는 옵션
       begin
             트리거로 작업할 실행문
       end;

   * 트리거 장점

      foreign key 로 묶여있는 데이터를 변경하려면 제약으로 인해 실행이 되지 않는다. 하지만 트리거로 제약으로 묶여있는 부모-자식 값을 동시에 처리하도록 정의해놓으면 DBMS가 이를 알아서 인식해 값을 변경할수 있도록 처리한다.