Stored Procedure(저장프로시져: Procedure)

Stored Procedure(저장프로시져: Procedure)

• 미리 컴파일된 형태로 저장
• 모든 사용자간 처리 루틴 공유: 캡슐화, 일관성 제공
• 데이터베이스 내부 구조에 대한 보안 기능 제공
• 서버데이터 보호 기능 제공
• 반환 값이 없음
  (사용형식)
  CREATE [OR REPLACE] PROCEDURE 프로시져명(
  변수명 모드 타입명 [:=|DEFAULT 값,]
  :
  변수명 모드 타입명 [:=|DEFAULT 값,])]
  IS|AS
  선언부
  BEGIN
  실행부

   [EXCEPTION
     예외처리;
   ]

  END;
  . '모드' : 매개변수의 역활 선언으로 IN, OUT, INPUT 중 하나 기술
  . '타입명' : 크기를 지정해서는 안됨 (실행)
  EXEC|EXECUTE 프로시져명[(매개변수LIST)]; 사용예) 회원번호를 입력받아 회원명, 주소, 마일리지를 출력하는 프로시져 -- 번호를 받아서 출력할때 사용됨 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 --P_LOC에 넣어라(아웃 매개변수)
  FROM HR.DEPT A, HR.LOCATIONS B
  WHERE A.DEPARTMENT_ID=PID--입력용에 해당(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--값 3개가 프로시저 밖으로 호출됨(나온것을 받으려면 변수가 있어야됨 -> 프로시저, 익명블록, 함수가 필요함)
        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'); --입력받은 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 --수량은  V_CNT에 넣기 , 합계는  V_SUM
        FROM BUYPROD
       WHERE BUY_DATE BETWEEN V_SDATE AND V_EDATE; --V_EDATE해당월의 마지막
       P_CNT:=V_CNT;--출력 매개 변수에 넣어줘야함
       P_SUM:=V_SUM;
    END;
    
    (실행)
    DECLARE
      V_CNT NUMBER:=0;
      V_SUM NUMBER:=0;
      V_RES VARCHAR2(100);
   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월달 카트테이블을 뒤져서 매출수량을 집계한 뒤 재고 업데이트를 해주는 것
     프로시져 : 제품 코드와 판매수량을 입력 받아 재고수불테이블 변경
     실행블록 : 2005년 7월 매출집계(커서로 생성) 후 각 제품별 프로시져 호출
     
     1) 프로시져 만들기
      CREATE OR REPLACE PROCEDURE PROD_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,--재품의 현재고에서 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;
     
     2) 실행 블록 만들기: 2005년 7월 매출집계(커서로 생성) 후 각 제품별 프로시져 호출
     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 --커서 집합의 맨 첫줄을 가르키는 것
           PROD_UPDATE_REMAIN(REC.CART_PROD,REC.CNT); --REC는 레코드 포인트
       END LOOP;
     END;
     
     SELECT * FROM REMAIN;