사용예)부서번호를 입력받아 해당 부서의 주소와 직원수,평균급여를 출력하는 프로시져를 작성하시오.
(프로시져)
CREATE OR REPLACE PROCEDURE PROC_DEPT01( --검색한 정보를 잠시 저장할 변수가 필요
PID IN HR.DEPT.DEPARTMENT_ID%TYPE,--모드 생략 : IN(입력용)
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 --OUT매개변수
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); --부서코드 입력용1,출력용3개
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;
사용예)년도와 월을 입력 받아 해당 기간동안 매입수량합계와 매입금액합계를 출력하는 프로시져 작성 --OUT매개변수 사용
--입력 : 년도, 월 / 출력 : 매입수량합계, 매입금액합계
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;
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월 매출을 분석하여 각 제품의 재고를 변경하는 프로시져를 작성하시오
--입력,출력은 어떤것을 받고 어떤 형식으로 만들지 먼저 생각하기.
--1.월을 입력받아 변경하는 프로시져 작성
--2.익명블록에서 상품코드별 매출 집계를 내서 프로시져에 넘기고 그 프로시져가 받은 정보로 REMAIN테이블을 변경=>커서만들기
--2번째방법 사용빈도수 높음
프로시져 : 제품코드와 판매수량을 입력 받아 재고수불테이블 변경
실행블록 : 2005년 7월 매출집계(커서로 생성) 후 각 제품별 프로시져 호출
(프로시져)
CREATE OR REPLACE PROCEDURE PROC_UPDATE_REMAIN(
P_PID IN PROD.PROD_ID%TYPE, --입력용
P_CNT IN NUMBER)--입력용 / 업데이트 처리하기때문에 출력용은 없음.
IS
BEGIN
UPDATE REMAIN A
SET A.REMAIN_O=REMAIN_O + P_CNT,--출고 : 매출발생, 7월이전출고수량 + 7월달출고수량(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'; --2005년에 출고된 상품코드만 업데이트
DBMS_OUTPUT.PUT_LINE(P_PID||'제품의 재고가 '||P_CNT||'개 변경됨');
END;
(실행블록:2005년 7월 매출집계(커서로 생성) 후 각 제품별 프로시져 호출)
DECLARE --SELECT ~ INTO절은 BEGIN블록에서만 사용
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;
SELECT * FROM REMAIN;
User Defined Function : Function
- 프로시져와 유사한 구조
- 반환 값이 존재
- 일반 내장함수처럼 사용--(ORACLE상에서 만들어진 함수)
(사용형식)
CREATE [OR REPLACE] FUNCTION 함수명[(
변수명 모드 타입명 [:=|DEFAULT 값,]
:
변수명 모드 타입명 [:=|DEFAULT 값,])]
RETURN 타입명
IS|AS --익명블록의 DECLARE와 같음
선언부
BEGIN
실행부
RETURN expr;
[EXCEPTION
예외처리;
]
END;
사용예)사원번호를 입력 받아 부서내에서 입사순번을 출력하는 함수
--매개변수, 입력용, 출력용을 구분
(함수)--반환되는 값은 하나가 대부분, 값이 두개면 결합해서 반환해야 함.
CREATE OR REPLACE FUNCTION FN_EMP_HIRE_DATE(
P_EID IN HR.EMP.EMPLOYEE_ID%TYPE)
RETURN NUMBER --순번을 반환하기 때문에 리턴값은 숫자(세미콜론 안씀)
IS
V_ORDER_NUM NUMBER:=0; --입사순번
BEGIN
SELECT A.ONUM INTO V_ORDER_NUM
FROM (SELECT DEPARTMENT_ID DID, --입사순번을 구해서 변수에 저장
EMPLOYEE_ID AS EID,
RANK()OVER(PARTITION BY DEPARTMENT_ID
--부서별 등수(부서를 그룹화=GROUP BY와 비슷)
ORDER BY HIRE_DATE ASC) AS ONUM
FROM HR.EMP) A
WHERE A.EID=P_EID;
RETURN V_ORDER_NUM;
END;
(실행)
SELECT A.EMPLOYEE_ID AS 사원번호,
A.EMP_NAME AS 사원명,
B.DEPARTMENT_NAME AS 부서명,
FN_EMP_HIRE_DATE(A.EMPLOYEE_ID) AS 입사순번
FROM HR.EMP A , HR.DEPT B
WHERE A.DEPARTMENT_ID=B.DEPARTMENT_ID
ORDER BY 3,4;
