SQL 응용 - 프로시저 , 정의 함수, 트리거

<1> 프로시저 

 

1. 선언부와 시작종료 

 

/* 선언부 */
CREATE OR REPLACE PROCEDURE EX_PROC
(
   P_DEPARTMENT IN VARCHAR2,
   P_STUDENT_CNT IN NUMBER  /*프로시저 모드: IN, OUT, INOUT 3가지있음*/
)   /
IS
P_UNIVERSITY VARCHAR2(100)  := '하하대학교';

/* 시작 종료부 */
BEGIN

INSERT INTO UNIVERSITY1 (UNIVERSITY, DEPARTMENT, STUDENT_CNT)
VALUES (P_UNIVERSITY, P_DEPARTMENT, P_STUDENT_CNT);
COMMIT;

END EX_PROC;

 

2. 프로시저 제어부 

 

(1) IF, ELSIF ,ELSE 문 

 

IF V_CLOSING_DATE < "20000101" THEN 
	SET V_CLOSING_DATE = "20200101"; /* 값 대입 */
ELSIF V.CLOSING_DATE > "20200101" THEN 
	DBMS_OUTPUT.PUT_LINE('MORE THAN JANUARY'); /* 출력 */
ELSE 

END IF
 	

 

(2) CASE 문 

 

CASE SCORE
WHEN SCORE >= 90 THEN
    SET RATING = 'A등급';
WHEN SCORE >= 80 THEN
    SET RATING = 'B등급';
ELSE
    SET RATING = 'C등급';
END CASE;

 

(3) Loop 문 

 

LOOP 
문장; 
EXIT WHEN 탈출조건;
END LOOP;

 

3. SQL 문 - SELECT, INSERT, UPDATE, DELETE

 

SELECT SUM ( SALES_AMT )  
	INTO V_SALES_TOT_AMT /*INTO에 선언된 변수에 저장 */
    FROM SALES_LIST_T
  WHERE SALES_DATE = V_CLOSING_DATE;

 

4. 예외부

 

EXCEPTION 
WHEN NO_DATA_FOUND THEN  
SET V_SALES_TOT_AMT = 0;

5. 실행부 

 

실행부에는 ROLLBACK과 COMMIT 이 있다 

 

ROLLBACK - 하나의 트랜잭션이 비정상적으로 종료되어 트랜잭션 원자성이 깨질 경우 처음부터 다시 시작하거나 부분적으로 연산을 취소하는 연산 

 

COMMIT - 하나의 트랜잭션이 성공적으로 끝나고 데이터베이스가 일관성 있는 상태에 있을 때 하나의 트랜잭션이 끝났을때 사용하는 연산 

 

* 선언부 - 시작부 - 제어부 - SQL문 - 예외부 - 실행부 - 종료부 순 

 

<2> 사용자 정의함수 - SQL 처리를 수행하고 수행결과를 반환하는 것 

 

- 프로시저와 매우 유사 

- 선언부에 PROCEDURE 대신 FUNCTION 이 나옴 

- 실행부 대신에 반환부 (RETURN) 이 있다. 

 

<3> TRIGGER - 특정 테이블에 삽입 삭제 수정 등의 데이터 변경 이벤트 발생하면 DBMS 에 자동적으로 실행되도록 구현된 프로그램이다. 

1. 선언부, 이벤트부 , 시작/종료부, 제어부, SQL, 예외부가 있다. 

2. 이벤트부 

 

이벤트 순서 : BEFORE, AFTER

이벤트 유형 : DELETE, UPDATE, INSERT 

 

/* 이벤트부 */
AFTER UPDTE OR DELETE
ON EMPLOYEE 
FOR EACH ROW