저장프로시져

• 비지니스 로직을 처리하는 모듈을 작성하여 컴파일하고
  해당 기능이 필요한 곳에서 함수 처럼 호출할 수 있는 단위
• 컴파일된 프로시저는 캐시메모리에 저장되어 처리속도가 빠르고, 네트워크 트래픽 감소 효과
• 사용자에게 모듈을 감추기 때문에 보안확보
• 프로시져는 반환값이 없음
• java에서 void타입 메서드다..
• 사용형식

CREATE [OR REPLACE] PROCEDURE 프로시져명[(
   매개변수명 [IN|OUT|INOUT(모드)] 타입명 [:=DEFAULT 값],
   매개변수명 [모드] 타입명 [:=DEFAULT 값],
                :
   매개변수명 [모드] 타입명 [:=DEFAULT 값])],
 IS | AS
    선언문; --변수...
 BEGIN
    처리문;
    [EXCEPTION
       예외처리문;
 END; 
  . '변수명' : 매개변수명으로 보통 P_로 시작
  . 모드 : IN -> 입력용, OUT->출력용, INOUT->입출력 공용
          default는 IN임
  . 타입명 : 매개변수의 타입으로 크기 설정 안함.

• 실행문 사용형식

EXEC|EXECUTE 프로시져명[(매개변수|값, ...)];
 - PL/SQL에서 프로시져 실행시 'EXEC|EXECUTE'는 생략

예시) 회원테이블에서 'd001'회원의 회원번호를 입력받아 회원명,주소,직업을 출력하는 프로시져 작성

create or replace procedure proc_mem01(
  p_mem_id  IN  member.mem_id%type)
is
  v_name member.mem_name%type;
  v_addr varchar2(100);
  v_job member.mem_job%type;
begin 
  select mem_name, mem_add1||' '||mem_add2, mem_job
         into v_name, v_addr, v_job
    from member
   where mem_id=p_mem_id;
  dbms_output.put_line(v_name||', '||v_addr||', '||v_job);
end;
execute proc_mem01('d001');

예시)상품테이블에서 분류코드 'P301'에 속한 상품의 상품명, 분류코드,
매입가격, 매출가격을 출력하는 프로시져를 작성하시오.

create or replace procedure proc_pod01(
  p_lgu in  prod.prod_lgu%type)
is 
  v_pname prod.prod_name%type;
  v_lgu lprod.lprod_gu%type;
  v_cost prod.prod_cost%type;
  v_price prod.prod_price%type;
  v_res varchar2(100);
begin
  select prod_name, prod_lgu, prod_cost, prod_price
         into v_pname, v_lgu, v_cost, v_price
    from prod
   where prod_lgu=p_lgu;
  v_res:=v_pname||' '||v_lgu||' '||v_cost||' '||v_price;
  dbms_output.put_line(v_res);
end;

execute proc_pod01('p301');
--안되는 이유 프로시져의 값이 변하는데 얘는 그걸 처리할수 없다.

• 그래서 프로시져에 반복해서 값을 집어넣음

create or replace procedure proc_prod01(
   p_id in prod.prod_id%type)
 is 
   v_pname prod.prod_name%type;
   v_lgu lprod.lprod_gu%type;
   v_cost prod.prod_cost%type;
   v_price prod.prod_price%type;
   v_res varchar2(100);
 begin
   select prod_name, prod_lgu, prod_cost, prod_price
          into v_pname, v_lgu, v_cost, v_price
     from prod
    where prod_id = p_id;
   v_res := v_pname||' '||v_lgu||' '||v_cost||' '||v_price;
   dbms_output.put_line(v_res);
 end;
   ※ 실행 : 익명블록'(서브쿼리)'을 사용
 declare
 begin
   for rec_pr in (select prod_id
                    from prod
                   where prod_lgu='P301') -- 대문자 유의 데이터값은 대소문자구분한다.
       loop
          proc_prod01(rec_pr.prod_id);
   end loop;
 end;

예시) 2005년 6월 가장 많이 판매된 상품의 상품코드와 수량을
입력받아 재고수불테이블에 해당제품에 대한 재고를 수정하시오.

(2005년 6월 많이 판매된 상품의 상품코드와 수량)
 select a.cart_prod as id,
        a.cart_qty as mqty
   from cart a, (select cart_prod as cid,
                      max(cart_qty) as maxqty
                 from cart
                where cart_no like '200506%'
                group by cart_prod
                order by 2 desc) b
  where a.cart_prod=b.cid
    and a.cart_qty=b.maxqty
    and a.cart_no like '200506%'
    and rownum=1; --rownum 의사컬럼

▶프로시져

create or replace procedure proc_cart01(
  p_prod in cart.cart_prod%type, --익명용 매개변수로 사용할 예정
  p_qty in number,  -- number(2)이런식으로 매개변수에서 크기를 설정하면 100%오류! 
  p_date cart.cart_no%type)
is
  v_date date := to_date(substr(p_date,1,8));
begin
  update remain                     -- 여기서는 update의 사용규칙에 따라 ':='가 아닌 '='를 쓴다.
     set remain_o = remain_o + p_qty,
         remain_j_99 = remain_j_99 - p_qty,
         remain_date = v_date
   where remain_year = '2005'
     and remain_prod = p_prod;
     -- REMAIN 테이블은 복합키 :  두개의 키를 가지고 주소가 만들어짐 
     -- * 만약 PK 가 세개인 테이블이 있으면 WHERE 절에는 조건 3개로 찾는게 가장 정확한 주소   
end;
/*
select max(cart_qty) as maxqty
  from cart
 where cart_no like '200506%'
이러면 값이 하나이다 그룹을 지었기 때문에 그룹별로 값이나온다.
*/
▶실행-- exec가 아닌 반드시 블록을 설정해야한다.
declare
  v_id cart.cart_prod%type;
  v_qty number := 0;
  v_no cart.cart_no%type;
begin
   select a.cart_prod, b.maxqty, a.cart_no
          into v_id, v_qty, v_no
     from cart a, (select cart_prod as cid,  
     -- 제품 코드 별로 그룹을 지어지니까 최대값이 안나온다?!
                          max(cart_qty) as maxqty
                     from cart
                    where cart_no like '200506%'
                    group by cart_prod
                    order by 2 desc) b
    where a.cart_prod = b.cid
      and a.cart_qty = b.maxqty
      and a.cart_no like '200506%'
     and rownum=1;
   -- 프로시져 호출
   proc_cart01(v_id, v_qty, v_no); 
   -- 변수를 인자값으로 받아 프로시져(함수)를 통해 변경한다.
end;

rollback;
select * from remain where remain_prod='P302000014';

**OUT 매개변수

• 실행이 BLOCK에 기술
  예시) 회원번호를 입력받아 이름과 직업을 출력하는 프로시져를 작성
  (단,이름과 직업은 OUT 매개변수 사용)

create or replace procedure proc_mem02(
  p_mem_id in member.mem_id%type,
  p_name out member.mem_name%type,
  p_job out member.mem_job%type)
is
begin
  select mem_name, mem_job
         into p_name, p_job
    from member
   where mem_id = p_mem_id;
end;
 ▶실행 --변수를 담을 공간이 없으니 결국 PL/SQL블럭에서 실행되어야한다.
declare
  v_name member.mem_name%type;
  v_job member.mem_job%type;
begin
  --exec proc_mem02('g001',:v_name,:v_job);
  --exec를 해서 쓸때는 out해서 받는 녀석은 :v_name으로 받아야한다.
  --sqlplus에 코딩(LINE 단위)
  proc_mem02('g001',v_name,v_job);
  dbms_output.put_line('회원명 : '||v_name);
  dbms_output.put_line('직업 : '||v_job);
end;
--out은 잘 안씀 얘보다 더 좋은 function을 쓰기 때문 하지만 function은 데이터하나밖에 반환을 못시킴

	※ SQLPLUS
 SQL>VAR V_NAME MEMBER.MEM_NAME%TYPE;
 SQL>VAR V_JOB MEMBER.MEM_JOB%TYPE;
 SQL>EXEC PROC_MEM02('g001',:V_NAME,:V_JOB);
 SQL>PRINT V_NAME;
 SQL>PRINT V_JOB;
 /