[SQL]SQL -Day. 6

Oracle

[SQL]SQL -Day. 6

MoZZANG 2022. 4. 1. 13:23

저장 프로시저([Stored] Procedure)

프로시저는 복잡한 처리의 SQL에 사용한다.
프로시저는 RETURN문이 없다. OUT 매개변수로 값을 RETURN한다.

저장 프로시저의 장점

매우 좋은 성능(최초 실행시에만 parsing(분석)하며 이후부터는 바로 실행한다.)
보안성을 높일 수 있다.
다양한 처리가 가능하다
네트워크의 부하를 줄일 수 있다.(1.과 동일한 이유)

프로시저 구문

CREATE [OR REPLACE] PROCEDURE 프로시저명
[(
매개변수 IN 자료형, //크기 지정 안함
매개변수 OUT 자료형, //출력용
매개변수 IN OUT 자료형 //입 출력용 EXEC시 숫자 전달 불가능 ,역시 변수 전달
)]

IS
[변수선언]
BEGIN
........
END;
/

▲ 내가 만든 MEMBER테이블. 위 테이블에 사용할 프로시저를 하나 만들자

▲ 프로시저를 호출하면서 username, password, name을 IN 파라미터로 입력받고 MEMBER 테이블에 INSERT INTO한다. 만약 정상 INSERT가 되면 SQL%FOUND가 true가 될 것이며 IF문 안으로 들어가 rtval에 '입력성공'이 대입되고 COMMIT이 될 것이다. 이후 변수 rtval을 print해보면 '입력성공'이 출력될 것이다.

만약 정상적으로 INSERT되지 않으면 exception절로 들어가 ROLLBACK 즉 실행되었던 코드들이 취소될 것이며 rtval에는 실패를 알리는 메세지가 저장되게된다.

▲ 입력이 잘 되면 rtval에 '입력성공'이 입력되므로 원하는 메세지가 잘 출력되고 테이블에도 잘 입력된 것을 볼 수 있다.

수정용 프로시저도 만들자

수정용 프로시저를 실행해보면

▲ 첫 행에서 PARK의 비밀번호와 이름을 각각 5678과 박길동1로 수정하면 SUCCESS가 RTVAL에 저장된다.

▲ PARK2 라는 USERNAME으로 검색해서 수정을 하려고 하니 없는 USERNAME이므로 위와 같이 RTVAL에 NOT EXIST USERTNAME이라는 예외처리 결과 값이 저장된다.

▲ USERNAME은 존재하지만 비밀번호 길이가 너무 길어 또다른예외가 발생하여 RTVAL에 저장된다.

마지막으로 삭제용 프로시저도 만들어보자

▲ 현재 MEMBER테이블을 참조하고 있는 자식테이블 BBS의 레코드이다.

▲먼저 존재하지 않는 USERNAME을 삭제하려고 해보자.

▲ 존재하지 않는 USERNAME일 때의 코멘트와 0이 반환되는 것을 볼 수 있다.

▲ 자식이 참조하고 있는 레코드를 삭제하려고 하면?

▲ 원하는 logic대로 잘 처리된 것을 볼 수 있다.

마지막으로 username과 password가 맞는지 안맞는지를 검증할 수 있는 프로시저를 만들어 보자

▲USERNAME이 일치하지 않는다면 -1을 반환, 아이디는 일치하지만 비밀번호가 틀렸다면 0을, USERNAME과 비밀번호 모두 레코드와 동일하다면 1을 반환하는 프로시저이다.

▲ 현재 MEMBER테이블에 저장되어 있는 레코드

▲ USERNAME이 KIM이고 password가 1234는 레코드와 일치하므로 1을 반환, USERNAME KIM2는 존재하지 조차 않으므로 -1반환, USERNAME은 존재하지만 비밀번호가 틀린경우는 0을 반환하는 것을 볼 수 있다.

트리거(Trigger)

자동으로 실행되는 프로시저의 한 종류로 직접 실행불가하다.
하나의 테이블에 최대 3개까지 트리거 적용이 가능하다. 단 트리거가 많을수록 성능저하 초래 가능성이 있다.
트리거 몸체(PL/SQL블락)안에는 COMMIT, ROLLBACK이 불가능하다.
:NEW(변경 후), :OLD(변경 전) 임시테이블은 행단위 트리거에서만 사용가능하다.

트리거 종류

1. 트리거몸체(DECLARE~ BEGIN END;절)의 실행 횟수에 따른 분류

     문장단위 트리거 - 한번만 실행
     행단위 트리거 - 행단위로 변경된 횟수만큼 실행됨

2. 타이밍에 따른 분류
      BEFORE 트리거- 이벤트(I/D/U)발생전 트리거몸체 실행됨
                            예] 판매와 상품(재고)관계 ,보안
      AFTER 트리거  - 이벤트(I/D/U)발생후 트리거몸체 실행됨
                            예] 입고와 상품(재고) 관계

트리거 생성

CREATE TRIGGER 트리거명
[BEFORE | AFTER] 이벤트 [INSERT [OR] | UPDATE [OR] DELETE]
ON 트리거를 걸 테이블명
[FOR EACH ROW] --생략시 문장단위 트리거
[WHEN 트리거 조건]
DECALRE
변수 선언
BEGIN

END;
/

트리거 삭제

DROP TRIGGER 트리거명

특정 트리거 활성화/비활성화

ALTER TRIGGER 트리거명 [ENABLE/DISABLE]

테이블에 건 모든 트리거 활성화 비활성화

ALTER TABLE 테이블명 ENABLE/DISABLE ALL TRIGGERS

※ INSERTING /DELETING/UPDATING 키워드
     PL/SQL블락안에서 사용하는 상수
     예]INSERT이벤트가 발생하면 INSERTING 이 TRUE

※ BEFORE트리거 걸때 주로 사용하는 함수
    RAISE_APPLICATION_ERROR(에러번호,'에러메시지');
    에러번호는 -20000 ~-20999사이의 임의의 숫자 지정

우리가 트리거를 만들면서 :NEW와 :OLD를 계속 사용할 것인데 개념을 좀 더 확실히 알고 가자

이제 예제를 보면서 개념을 적용하고 이해해보자

▲ 트리거를 걸 테이블을 만들자

▲ 위에서 우리가 만든 TRIGER_TBL에 트리거를 걸어서 트리거가 작동되면(Input/Delete/Update) TARGET_TBL에 자동으로 어떤 이벤트가 발생했는지 반영될 테이블도 만들자

이제 본격적으로 트리거를 만들고 걸자

▲ AFTER를 적어줌으로써 AFTER트리거임을 설정하고 INSERT, UPDATE, DELETE 중 하나만 선택해도 되지만 우리는 모든 트리거를 작동시켜보기 위해 모두 선택을 했다. ON을 이용해 트리거를 걸 테이블을 설정해주고 FOR EACH ROW 즉, INSERT 또는 UPDATE 또는 DELETE가 실행된 횟수만큼 트리거도 실행되는 것이다.

이제 트리거를 실행해보자

▲트리거를 건 테이블에 데이터를 INSERT한다.

▲이후 트리거를 건 테이블을 확인해보면 데이터가 잘 입력된 것을 볼 수 있다.

그리고 TARGET_tbl을 보면

▲ 위 처럼 INSERT이벤트가 3번 발생했기 때문에 트리거 또한 3번 실행되어 INSERT기록이 3번 입력되어 있는 것을 볼 수 있다.

이것이 트리거이다. 트리거를 건 테이블의 이벤트발생여부에 따라 특정 테이블에 또한 어떤 이벤트가 자동으로 발생되는 것이다.

이번에는 BEFORE트리거를 만들어서 실행해보자 BEFORE트리거는 트리거를 건 테이블에 이벤트 발생하기전에 조건을 판단해서 조건에 따라 이벤트를 실행시키거나 예외를 발생시켜 이벤트실행을 막는다.

▲이번에 만든 트리거는 BEFORE트리거이므로 AFTER대신 BERORE을 입력해주고 INSERT 이벤트만 발생시킬 것이므로 INSERT만 선택해준다. 역시 TRIGER_TBL에 트리거를 걸것이고, 행단위로 실행할 것이다.

BEFORE트리거의 조건은 현재 요일이 금요일이거나 낮12시를 넘으면 작동하지 않는 조건으로 트리거를 생성할 것이다.

BEFORE트리거에서는 RAISE_APPLICATION_ERROR를 이용하여 에러를 발생시킨다.

▲BEFFORE 트리거 때문에 실행이 사전에 차단된 것을 볼 수 있다.

이번에는 좀 더 실생활적인 예제를 가지고 연습해보자. 쇼핑몰을 운영한다고 할 때 우리가 팔려고하는 상품테이블, 입고테이블 그리고 판매가 이루어 졌을 때 사용하는 판매테이블까지 만들어서 트리거를 운영해보자.

▲상품 및 재고, 입고, 판매테이블을 위와 같이 만들었다. 위 세개의 테이블은 P_CODE로 서로 부모와 자식관계로 이루어 져있다.

▲ 부모테이블인 PRODUCT 테이블에 데이터를 INSERT해보자.

이제 트리거를 만들고 걸자. 입고테이블에 INSERT트리거를 걸어서 상품이 입고되면 PRODUCT테이블의 재고수량이 자동으로 증가하는 트리거를 만들자.

▲ AFTER트리거로 INSERT이벤트에만 반응할 것이며, 트리거는 INP테이블에 걸었다. INP테이블 INSERT가 일어나면 PRODUCT테이블을 UPDATE시킬껀데 PRODUCT테이블의 p_qty컬럼에 기존의 재고수량+ :NEW.i_qty 즉, inp테이블에 새로 입고되는 수량을 더해주는 것이다. 물론 WHERE PRODUCT의 p_code와 새로 입고되는 제품의 p_code가 같은 컬럼에 말이다.

▲이제 트리거를 컴파일해주고 입고테이블에 INSERT를 해서 트리거를 작동시켜보자.

▲ INP테이블에 잘 입력되었으며

▲PRODUCT테이블에 자동으로 재고수량이 수정된것을 볼 수 있다.

상품이 입고되었지만 그중에 불량품이 있어서 입고갯수를 수정해야할 경우도 있을 것이다. 이렇듯 INP테이블의 입고수량이 변경되면 자동으로 PRODUCT테이블의 재고수량도 수정되는 UPDATE 트리거도 만들어보자.

▲INP테이블의 I_qty에 수량이 변경되면 즉 update되면 PRODUCT테이블의 p_qty컬럼에 있던 기존의 값에 INP테이블의 i_qty컬럼에 수량변경 전 갯수를 뺀 뒤 다시 수정된(:NEW) 갯수만큼 더해주는 로직이다.

▲ 꼭 트리거를 실행해서 컴파일 해준 뒤에 수량수정코드를 실행하자!

▲INP테이블과 PRODUCT테이블 모두 잘 변경된 것을 볼 수 있다.

이번에는 판매테이블에서 판매가 일어나면 즉, 판매수량에 INSERT가 생기면 PRODUCT테이블의 재고 수량이 줄어들어야 하므로 트리거를 이용하여 자동 반영되도록 해보자 단, 재고수량이 판매수량보다 적으면 판매가 이루어지지 않도록 해보자. 이때는 미리 재고를 check해야하므로 BEFORE 트리거를 사용해아 할 것이다.