프로시저

CREATE OR REPLACE PROCEDURE insert_emp_proc(
P_EID IN EMPLOYEES.EMPLOYEE_ID%TYPE,
P_NAME IN EMPLOYEES.EMP_NAME%TYPE,
P_HDATE IN EMPLOYEES.HIRE_DATE%TYPE,
P_SAL NUMBER) -- 여기까지가 매개변수를 정리해 주는 곳
IS
 V_CNT NUMBER(1) :=0;
BEGIN
 SELECT COUNT(*) INTO V_CNT --0또는 1이 나옴
 FROM EMPLOYEES
 WHERE EMPLOYEE_ID=P_EID;
 
 IF V_CNT>=1 THEN
 UPDATE EMPLOYEES
 SET EMP_NAME=P_NAME, 
 SALARY=P_SAL,
 HIRE_DATE = P_H_DATE
 WHERE EMPLOYEE_ID = P_EID;
 
 INSERT INTO EMPLOYEES(EMPLOYEE_ID,EMP_NAME,HIRE_DATE,SALARY)
 VALUES(P_EID,P_NAME,P_HDATE,P_SAL);
 COMMIT;
END;

오류

프로시저 실행형식

EXEC | EXECUTE 프로시저명 ([매개변수,...]) ;

익명블록이나 다른프로시저나 함수 내부에서 실행할 때는 'EXEC', 'EXECUTE'는 생략

EXECUTE insert_emp_proc(301,'홍길동','20180302',3500);

오루

DECLARE
BEGIN
insert_emp_proc(312,'강감찬','20200601',2700);
END;

 

회원테이블에서 회원번호를 입력받아 이름,성별, 주소, 마일리지를 출력하는 프로시저를 작성하시오

IN매개변수는 회원번호 하나 OUT은 4개

CREATE OR REPLACE PROCEDURE MEM_SER_PROC(
P_ID IN MEMBER.MEM_ID%TYPE,
P_NAME OUT MEMBER.MEM_NAME%TYPE,
P_GENDER OUT VARCHAR2,
P_ADDR OUT VARCHAR2,
P_MILE OUT MEMBER.MEM_MILEAGE%TYPE) --여기까지가 매개변수
IS
V_GENDER VARCHAR2(30);
V_REGNO2 CHAR(1);
V_ADDR VARCHAR2(100);
V_NAME MEMBER.MEM_NAME%TYPE;
V_MILE MEMBER.MEM_MILEAGE%TYPE; --BEGIN에서SELECT쓸거면 INTO써줘야하는데
BEGIN
SELECT MEM_NAME, SUBSTR(MEM_REGNO2,1,1),MEM_ADD1||MEM_ADD2, MEM_MILEAGE 
INTO V_NAME, V_REGNO2, V_ADDR, V_MILE
FROM MEMBER
WHERE MEM_ID=P_ID;
IF V_REGNO2=1 OR V_REGNO2=3 THEN
V_GENDER:='남성회원';
ELSE
V_GENDER:='여성회원';
END IF;
P_NAME := V_NAME;
P_GENDER:=V_GENDER;
P_ADDR:=V_ADDR;
P_MEIL:=V_MILE;
END;

OUT 매개변수가 존재하는프로시저 실행 시 프로시저로부터 넘겨받는 값을 저장할 변수가 필요 (블록 OR 함수 필요)

--(실행)
ACCEPT P_MID PROMPT '회원번호 : ' 
DECLARE
 V_NAME MEMBER.MEM_NAME%TYPE;
 V_MILE MEMBER.MEM_MILEAGE%TYPE;
 V_ADDR VARCHAR2(100);
 V_GENDER VARCHAR2(30);
 BEGIN
 MEM_SER_PROC('&P_MID',V_NAME, V_GENDER, V_ADDR, V_MILE);
 
DBMS_OUTPUT.PUT_LINE('회원명 :  '||V_NAME);
DBMS_OUTPUT.PUT_LINE('성별 :  '||V_GENDER);
DBMS_OUTPUT.PUT_LINE('주소 :  '||V_ADDR);
DBMS_OUTPUT.PUT_LINE('마일리지 :  '||V_MILE);
END;

 

예 )  상품코드를 입력 받아 2005년 매입정보를 이용하여, 상품명과 매입수량 및 매입금액을 구하시오

CREATE OR REPLACE PROCEDURE LPROD_AMT_PROC(
P_LPRODID IN LPROD.LPROD_GU%TYPE,
P_LNAME OUT LPROD.LPROD_NM%TYPE,
P_QTY OUT NUMBER,
P_AMT OUT NUMBER)
IS
BEGIN
SELECT C.LPROD_NM, SUM(A.BUY_QTY), SUM(A.BUY_QTY*A.BUY_COST) INTO P_LNAME,P_QTY,P_AMT
FROM BUYPROD A, PROD B, LPROD C
WHERE A.BUY_PROD=B.PROD_ID
AND B.PROD_LGU = C.LPROD_GU
AND C.LPROD_GU = P_LPRODID
GROUP BY C.LPROD_NM;
END;

(실행)
DECLARE
V_LNAME LPROD.LPROD_NM%TYPE;
V_QTY NUMBER(5):=0;
V_AMT NUMBER:=0;
BEGIN
LPROD_AMT_PROC('P201',V_LNAME,V_QTY, V_AMT);

DBMS_OUTPUT.PUT_LINE('분류명 : '||V_LNAME);
DBMS_OUTPUT.PUT_LINE('매입수량 : '||V_QTY);
DBMS_OUTPUT.PUT_LINE('매입금액 : '||V_AMT);
END;

 

상품코드와 월을 입력 받아 해달 월의 해당 상품의 입고, 출고 현황을 조회하시오

단, 월의 입력형식은 : 'YYYYMM'이고

조회해야 할 내용은 상품명, 입고수량, 출고수량이다.

입력은 두개 (해당상품코드와 월)

CREATE OR REPLACE PROCEDURE BUY_CART_PROC(
P_PRODID IN BUYPROD.BUY_PROD%TYPE,
P_MONTH IN VARCHAR2,
P_NAME OUT PROD.PROD_NAME%TYPE,
P_BUY OUT NUMBER,
P_CART OUT NUMBER
)
IS
V_DATE DATE:= TO_DATE(P_MONTH||'01');
BEGIN
SELECT C.PROD_NAME, COUNT(A.BUY_QTY), 
COUNT(B.CART_QTY) INTO P_NAME,P_BUY,P_CART
FROM BUYPROD A, CART B, PROD C
WHERE A.BUY_PROD=C.PROD_ID
AND B.CART_PROD=C.PROD_ID
AND C.PROD_ID = P_PRODID
AND A.BUY_DATE BETWEEN V_DATE AND LAST_DAY(V_DATE)
GROUP BY C.PROD_NAME;
SELECT SUM(CART_QTY) INTO P_CART
FROM CART
WHERE SUBSTR(CART_NO,1,6)=P_MONTH
AND CART_PROD=P_PRODID;
END;


(실행)
DECLARE
V_NAME PROD.PROD_NAME%TYPE;
V_QTY NUMBER(5) := 0;
V_CQTY NUMBER :=0;
BEGIN
BUY_CART_PROC('P201',V_NAME,V_QTY,V_CQTY);
DBMS_OUTPUT.PUT_LINE('이름:' ||V_NAME);
DBMS_OUTPUT.PUT_LINE('입고수량:' ||V_QTY);
DBMS_OUTPUT.PUT_LINE('출고수량:' ||V_CQTY);
END;