[ORACLE] 저장프로시져 221201-04

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

 - 특정 결과를 산출할 수 있는 모듈을 작성하고 컴파일하여 서버의 프로시져캐쉬에 저장
   하여 사용
 - 모든 응용프로그램에서 사용할 수 있도록 캡슐화 기능제공 =>일관성 제공
 - 데이터베이스 내부 구조에 대한 보안성 제공
 - 프로시져는 반환 값이 없음

(사용형식)
  CREATE [OR REPLACE] PROCEDURE 프로시져명[( 
    매개변수 [IN|OUT|INOUT] 데이터타입[:=디폴트 값], --IN 프로시져 밖에서 안으로,OUT 안에서 밖으로 INOUT 둘다가능/ 생략하면 IN
            :
    매개변수 [IN|OUT|INOUT] 데이터타입[:=디폴트 값]) --데이터타입은 타입명만 입력하고 크기는 입력하지 않는다
  IS|AS --DECLARE랑 똑같음
    선언영역
  BEGIN
    실행영역
  END;

✔수행예) 직무코드,직무명,최대급여,최소급여를 입력 받아 HR계정의 JOBS테이블에 신규 데이터를
              삽입하는 프로시져를 작성하시오. --다른계정에서 만들 수 없음

 CREATE OR REPLACE PROCEDURE insert_jobs_proc(
    p_job_id IN HR.JOBS.JOB_ID%TYPE,--매개변수는 보통 P를 사용하는게 일반적
    p_job_title IN HR.JOBS.JOB_TITLE%TYPE,
    p_misal IN NUMBER, 
    p_mxsal IN NUMBER)
  IS
  
  BEGIN
   INSERT INTO HR.JOBS VALUES( p_job_id,p_job_title,p_misal,p_mxsal);
   COMMIT;
  END;

[프로시져의 실행]
 1. 독립적 실행
  - OUT 매개변수를 사용하지 않은 경우
    EXEC|EXECUTE 프로시져명(매개변수list);
 2. PL/SQL의 블록에서 실행   
  - OUT 매개변수를 사용한 경우 - OUT매개변수에 의해 전달되는 값을 보관항 변수를 사용
    프로시져명(매개변수list)

 EXECUTE insert_jobs_proc('sample1','SAMPLE JOB TITLE',3000,5000);

✔사용예)직무코드를 입력받아 해당 자료를 JOBS테이블에서 삭제하는 프로시져 작성

 CREATE OR REPLACE PROCEDURE delete_jobs_proc(
    p_job_id IN JOBS.JOB_ID%TYPE)
   IS
    L_CNT NUMBER:=0;   
   BEGIN
     SELECT COUNT(*) INTO L_CNT
       FROM HR.JOBS
      WHERE JOB_ID=p_job_id;
      
      IF L_CNT!=0 THEN
         DELETE FROM HR.JOBS WHERE JOB_ID=p_job_id;
      ELSE
         DBMS_OUTPUT.PUT_LINE('해당자료가 테이블에 없습니다..');
      END IF;
      COMMIT;
   END;
   
   EXEC delete_jobs_proc('sample1');

✔사용예)오늘이 2020년 7월 28일 이라고 가정하고 매출정보를 장바구니에 저장하는 프로시져를
                작성하시오. 입력 값은 회원번호,오늘날짜,상품코드,수량이다

  CREATE OR REPLACE PROCEDURE delete_jobs_proc(
    p_job_id IN JOBS.JOB_ID%TYPE)
  IS  
    L_CNT NUMBER:=0;
  BEGIN
    SELECT COUNT(*) INTO L_CNT
      FROM HR.JOBS
     WHERE JOB_ID=p_job_id;
    
    IF L_CNT!=0 THEN
       DELETE FROM HR.JOBS WHERE JOB_ID=p_job_id;
    ELSE
       DBMS_OUTPUT.PUT_LINE('해당자료가 테이블에 없습니다..');
    END IF;
    COMMIT;
  END;
      
      
  EXEC delete_jobs_proc('sample1');

✔사용예) 오늘이 2020년 7월 28일리라고 가정하고 매출정보를 장바구니에 저장하는 프로시져를
                작성하시오. 입력 값은 회원번호,오늘날짜,상품코드,수량이다

CREATE OR REPLACE PROCEDURE delete_jobs_proc(
    p_job_id IN JOBS.JOB_ID%TYPE)
  IS  
    L_CNT NUMBER:=0;
  BEGIN
    SELECT COUNT(*) INTO L_CNT
      FROM HR.JOBS
     WHERE JOB_ID=p_job_id;
    
    IF L_CNT!=0 THEN
       DELETE FROM HR.JOBS WHERE JOB_ID=p_job_id;
    ELSE
       DBMS_OUTPUT.PUT_LINE('해당자료가 테이블에 없습니다..');
    END IF;
    COMMIT;
  END;
      
      
  EXEC  delete_jobs_proc('sample1');

✔사용예)오늘이 2020년 7월 28일리라고 가정하고 매출정보를 장바구니에 저장하는 프로시져를
              작성하시오. 입력 값은 회원번호,오늘날짜,상품코드,수량이다

 CREATE OR REPLACE PROCEDURE insert_cart_proc(
    p_mid IN MEMBER.MEM_ID%TYPE,
    p_date IN DATE,
    p_pid IN PROD.PROD_ID%TYPE,
    p_qty IN NUMBER)
  IS 
    L_CART_NO CART.CART_NO%TYPE;
    L_FLAG CHAR(9):=TO_CHAR(p_date,'YYYYMMDD')||'%';
    L_CNT NUMBER:=0;
  BEGIN
    SELECT COUNT(*) INTO L_CNT
      FROM CART
     WHERE CART_NO LIKE L_FLAG;
    
    IF L_CNT=0 THEN 
       L_CART_NO:=TO_CHAR(p_date,'YYYYMMDD')||TRIM('00001');
    ELSE 
       SELECT MAX(CART_NO)+1 INTO L_CART_NO
         FROM CART
        WHERE CART_NO LIKE L_FLAG; 
    END IF;
    
    INSERT INTO CART VALUES(p_mid,L_CART_NO,p_pid,p_qty);
    COMMIT;
  END;
      
  EXECUTE  insert_cart_proc('a001',TO_DATE('20200728'),'P201000008',5);