[Oracle SQL] 저장프로시저

> # Stored Procedure(저장프로시저: Procedure)

• 미리 컴파일된 형태로 저장
• 모든 사용자간 처리 루틴 공유: 캡슐화 , 일관성 제공
• 데이터베이스 내부 구조에 대한 보안 기능 제공
• 서버데이터 보호 기능 제공
• 반환값이 없음

(사용형식)
CREATE [OR REPLACE] PROCEDURE 프로시저명[(변수명 모드 타입명 [:=|DEFAULT 값,]
: -- 프로시저밖에서 안으로 데이터를 가져올때-IN 그반대는-OUT. 생략시 MOD
변수명 모드 타입명 [:=|DEFAULT 값])]
IS|AS -- 익명블록만들때 DECLARE와 같은기능. IS나 AS암거나 쓰면됨
선언부
BEGIN
실행부

[EXCEPTION 예외처리; ]
END;
- '모드': 매개변수의 역할선언으로 IN(입력), OUT(출력), INOUT 중 하나 기술 --생략되면 IN
- '타입명': 크기를 지정할 수 없음

(실행)
EXEC|EXECUTE 프로시저명[(매개변수list)];

사용예) 회원번호를 입력받아 회원명, 주소, 마일리지를 출력하는 프로시저 --프로시저는 입력받은 데이터로 다른데이터를 UPDATE, INSERT, DELETE = DML

  CREATE OR REPLACE PROCEDURE PROC_MEMBER01(
    P_MID IN MEMBER.MEM_ID%TYPE)
    IS
    V_NAME MEMBER.MEM_NAME%TYPE; 
    V_ADDR VARCHAR2(200);
    V_MILE MEMBER.MEM_MILEAGE%TYPE;
    BEGIN
      SELECT MEM_NAME, MEM_ADD1||' '||MEM_ADD2, MEM_MILEAGE
        INTO V_NAME, V_ADDR, V_MILE
        FROM MEMBER
       WHERE MEM_ID=P_MID; --이 조건을 만족하는 회워명, 주소, 마일리지를 꺼내서 V_NAME, V_ADDR, V_MILE에 넣음
       
       DBMS_OUTPUT.PUT_LINE('회원명 : ' ||V_NAME);
       DBMS_OUTPUT.PUT_LINE('주소 : ' ||V_ADDR);
       DBMS_OUTPUT.PUT_LINE('마일리지 : ' ||V_MILE);
  
    END;
    
    --익명블록은 실행할때마다 컴파일. 프로시저는 컴파일 한번만. 호출만하면됨 2가지방법
    
    EXECUTE PROC_MEMBER01('d001'); --d001 회원 호출
    
    -- 익명블록 사용하여 출력
    ACCEPT P_MID PROMPT '회원번호 입력: ' --회원번호 입력받음 
    DECLARE 
    BEGIN
      PROC_MEMBER01(LOWER('&P_MID'));
    END;
    
    (프로시져)
  CREATE OR REPLACE PROCEDURE PROC_DEPT01(
     PID IN HR.DEPT.DEPARTMENT_ID%TYPE, --입력매개변수
     P_LOC OUT VARCHAR2,
     P_CNT OUT NUMBER,
     P_ASAL OUT NUMBER)
    IS
    BEGIN
      --주소 검색
      SELECT B.STREET_ADDRESS||' '||B.CITY||' , '|| B.STATE_PROVINCE
        INTO P_LOC
        FROM HR.DEPT A, HR.LOCATIONS B
       WHERE A.DEPARTMENT_ID=PID
         AND A.LOCATION_ID=B.LOCATION_ID;
         --인원수와 평균급여 계산
         SELECT COUNT(EMPLOYEE_ID), ROUND(AVG(SALARY))
           INTO P_CNT, P_ASAL
           FROM HR.EMP
           WHERE DEPARTMENT_ID=PID;
    END;
    
    
    ACCEPT P_DID PROMPT '부서번호: '
    DECLARE
      V_LOC VARCHAR2(200);
      V_CNT NUMBER:=0;
      V_ASAL NUMBER:=0;
      BEGIN
        PROC_DEPT01(TO_NUMBER('&P_DID'), V_LOC,V_CNT,V_ASAL);
        
         DBMS_OUTPUT.PUT_LINE('부서번호: '||'&P_DID');
        DBMS_OUTPUT.PUT_LINE('주소: '||V_LOC);
        DBMS_OUTPUT.PUT_LINE('직원수: '||V_CNT);
        DBMS_OUTPUT.PUT_LINE('평균급여: '||V_ASAL);
        END;
        

사용예) 년도와 월을 입력받아 해당 기간동안 매입수량합계와 매입금액 합계를 출력하는 프로시저 작성

  CREATE OR REPLACE PROCEDURE PROC_BUY01(
    P_YEAR IN VARCHAR2,
    P_MON  IN VARCHAR2,
    P_CNT OUT NUMBER,
    P_SUM OUT NUMBER)
   IS
    V_SDATE DATE:=TO_DATE(P_YEAR||P_MON||'01');
    V_EDATE DATE:=LAST_DAY(V_SDATE);
    V_CNT NUMBER:=0;
    V_SUM NUMBER:=0;
   BEGIN
     SELECT SUM(BUY_QTY), SUM(BUY_QTY*BUY_COST)  INTO V_CNT, V_SUM
       FROM BUYPROD
      WHERE BUY_DATE BETWEEN V_SDATE AND V_EDATE;
     P_CNT:=V_CNT;
     P_SUM:=V_SUM;
     
   END;
   
   (실행)
       DECLARE
         V_CNT NUMBER:=0;
         V_SUM NUMBER:=0;
       BEGIN
         PROC_BUY01('2005','04',V_CNT, V_SUM);
         V_RES:='2005년 04월 매입수량은 ' ||V_CNT||'이고 매입금액은' ||TO_CHAR(V_SUM,99,999,999||'원이다';
         DBMS_OUTPUT.PUT_LINE(V_RES);
         END;
         

사용예) 2005년 7월 매출을 분석하여 각 제품의 재고를 변경하는 프로시저를 작성하시오
프로시저: 제품코드와 판매수량을 입력받아 재고수불테이블 변경
실행블록: 2005년 7월 매출집계(커서) 후 각 제품별 프로시져 호출

   (프로시저) 
      CREATE OR REPLACE PROCEDURE RPOC_UPDATE_REMAIN(
      P_PID IN PROD.PROD_ID%TYPE,
      P_CNT IN NUMBER)
      IS
      
      BEGIN
        UPDATE REMAIN A
           SET A.REMAIN_O=A.REMAIN_O+P_CNT,
               A.REMAIN_J_99=A.REMAIN_J_99 - P_CNT, 
               A.REMAIN_DATE=TO_DATE('20050731')
               
         WHERE A.PROD_ID = P_PID
           AND A.REMAIN_YEAR='2005';
          DBMS_OUTPUT.PUT_LINE(P_PID||'제품의 재고가'||P_CNT||'개 변경됨');
       END;
       
       (실행블록)
       DECLARE
          CURSOR CUR_CART01 IS 
            SELECT CART_PROD, SUM(CART_QTY) AS CNT
              FROM CART
             WHERE CART_NO LIKE '200507%'
             GROUP BY CART_PROD;
       BEGIN
         FOR REC IN CUR_CART01 LOOP
             PROC_UPDATE_REMAIN(REC.CART_PROD, REC.CNT);
         END LOOP;
         
       END;