> # 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;