정보처리기사 PART 8 정리 - 프로시저

프로시저

프로시저(Procedue)는 절차형 SQL을 활용하여 기능을 수행하는 트랜젝션언어입니다. 즉, 프로시저를 호출하게되면 SQL작업을 포함하는 데이터 조작어를 수행하게 됩니다.  

 

프로시저의 구조에 대해서 이야기하겠습니다. 

 

 

선언부(DECLARE)에선 프로시저의 이름과 변수, 데이터 타입을 정의하는 부분입니다.

 

코드로 프로시저의 선언부에 대해서 자세하게 알아봅시다.

 

CREATE PROCEDURE SALES_CLOSING
  (V_CLOSING_DATE IN CHAR(8))
IS
  V_SALES_TOT_AMT NUMBER := 0;

 

다음 코드는 SALES_CLOSING이라는 프로시저를 정의하고 IN 뒤에는 파라미터로 V_CLOSING_DATE라는 8자리 문자열을 받도록 정의 하였습니다.

 

IS 뒤에는 프로시저 내에서 사용할 숫자형식의 변수인 V_SALES_TOT_AMT를 정의하였습니다.

 

 

시작부(BEGIN)와 종료부(END)는 다수의 실행을 제어하는 기본 단위로 프로시저의 시작과 종료를 표현하고 있습니다. 반드시 시작부가 나오면 종료부로 마무리 지어야합니다.

 

BEGIN은 선언부의 뒤에 오며 선언부를 제외한 프로시저의 모든 구조들은 BEGIN과 END사이에 정의되어야합니다.  

 

 

제어부(CONTROL)에서는 조건문 IF문과 CASE문, 반복문인 LOOP문, WHILE문, FOR LOOP문을 사용한 문장을 처리합니다. 

 

IF V_CLOSING_DATE < “20000101" THEN
  SET V_CLOSING_DATE = “20200101";
END IF;

 

다음은 파라미터 V_CLOSING_DATE가 문자열 "20000101"이면 “20200101"으로 값을 변경한다는 프로시저 내부의 조건문이 됩니다

 

 

SQL은 프로시저의 SQL은 SELECT, INSERT, DELETE, UPDATE과 같은 DML을 주로 사용되며 가끔 DDL도 사용됩니다. 

SELECT SUM(SALES_AMT)
INTO V_SALES_TOT_AMT
FROM SALES_LIST_T
WHERE SALES_DATE = V_CLOSING_DATE;

 

SALES_LIST_T라는 판매내역 테이블에서 다음 SQL문을 실행하고 결과 값을 V_SALES_TOT_AMT라는 변수로 전달합니다.

 

 

예외부(EXCEPTION)에 대해서 이야기 해보겠습니다. BEGIN END문 사이에서 SQL문의 실행이 될때 발생하는 예외에 대한 처리 방식을 정의하고 있는 부분입니다. 

 

EXCEPTION
  WHEN NO_DATA_FOUND THEN
  SET V_SALES_TOT_AMT = 0;
  INSERT INTO SALES CLOSED_T( SALES DATE, SALES TOT AMT) 
  VALUES ( V_CLOSING_DATE, V_SALES_TOT_AMT);

 

위의 코드에서는 만약 SQL의 조회 결과가 없을 경우 SQL의 결과로 NO_DATA_FOUND가 발생합니다. 그 결과 V_SALES_TOT_AMT은 0이 되며, INSERT 구문을 통해 마감된 값을 테이블에 삽입하는 동작을 실행합니다.

 

 

마지막으로 실행부 (TRANSACTION)는 프로시저에서 수행되는 DML을 DBMS 적용 여부를 COMMIT(적용), ROLLBACK(취소)를 통해 결정하는 부분입니다.

 

 

위에서 선언된 프로시저를 이용하려면 EXECUTE를 이용해 호출합니다.

 

EXECUTE SALES_CLOSING('20210506');

 

위의 호출문이 정상적으로 실행되면 V_CLOSING_DATE에 "20210506"이라는 8자리 문자열 값이 프로시저로 전달되며.

 

위에서 설명한 프로시저의 구조가 실행됩니다.