[PL/SQL] 저장프로시져(Stored Procedure)

저장프로시져(Stored Procedure)

• 특정한 처리를 수행하는 PL/SQL 서브프로그램. 파라미터를 받아서 특정 작업을 수행
• 자바의 메소드에 해당
• 자바와 차이점 : 자바는 메소드가 리턴값이 있을수도 없을수도 있지만 저장 프로시져는 리턴값이 없다. 메소드의 VOID라고 생각하면 됨

1. 형식

CREATE OR REPLACE PROCEDURE 프로시져명(파라미터,...)
IS
	변수선언;
BEGIN
	실행구문;
END;
/

프로시져 호출
형식)
EXECUTE 프로시져명(매개변수값,..);
- 프로시져 삭제
형식)
DROP PROCEDURE 프로시져명;


예1) 부서번호,부서명,위치를 파라미터로 전달받아 DEPT테이블에 추가하는 프로시져
CREATE OR REPLACE PROCEDURE ADDDEPT
(
	DNO NUMBER,
    DNAME VARCHAR2,
    DLOC VARCHAR2
)
IS
BEGIN
	INSERT INTO DEPT VALUES(DNO,DNAME,DLOC);
    COMMIT;
END;
/

EXECUTE ADDDEPT(11,'영업','부산');
SELECT * FROM DEPT;

결과)
    DEPTNO DNAME          LOC          
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK     
        20 RESEARCH       DALLAS       
        30 SALES          CHICAGO      
        40 OPERATIONS     BOSTON       
        50 개발부         서울         
        42 인사부         부산         
        60 기획부         인천         
        43 인사2부        서울         
        61 기획2부        인천         
        11 영업           부산 
     

예2) 사원번호를 파라미터로 전달받아 이름과 직업을 출력하는 프로시져
CREATE OR REPLACE PROCEDURE EMPINFO
(
	ENO NUMBER
)
IS
	EMPNAME VARCHAR2(10);
    EMPJOB VARCHAR2(20);
BEGIN
	SELECT ENAME,JOB INTO EMPNAME,EMP JOB
    FROM EMP WHERE EMPNO=ENO;
    
    DBMS_OUTPUT.PUT_LINE(ENO||' '||EMPNAME||' '||EMPJOB);
END;
/
EXECUTE EMPINFO(7369);

결과
7369 SMITH CLERK


Q1) 사원번호와 급여인상률을 파라미터로 전달받아 급여를 인상하는 프로시져를
      만들고 호출해 보세요.
CREATE OR REPLACE PROCEDURE INCREASE
(
	VEMPNO NUMBER,
    RATE NUMBER
)
IS
	VSAL NUMBER(7,2);
    USAL NUMBER(7,2);
BEGIN
	SELECT SAL INTO VSAL FROM EMP WHERE EMPNO=VEMPNO;
    USAL := VSAL + VSAL*(RATE/100);
    UPDATE EMP
    SET SAL=USAL
    WHERE EMPNO=VEMPNO;
    DBMS_OUTPUT.PUT_LINE(VSAL||'==>'||USAL||' 로 급여인상 완료');
END;
/
EXECUTE INCREASE(7369,10);

결과)
800==>880 로 급여인상 완료


Q2) 사원번호를 파라미터로 전달받아 사원의 급여가 800이상이면 급여의 50%
급여가 2000이상이면 급여의 30%,3000이상이면 급여의 10%를 인상하세요.
화면에 XXX사원의 급여가 XXX로 인상되었습니다. 라고 출력되도록 하세요.
예) EXECUTE UPDATESAL(7369);
CREATE OR REPLACE PROCEDURE UPDATESAL
(
	VEMPNO EMP.EMPNO%TYPE --타입참조
	--VEMPNO NUMBER
)
IS
	VSAL EMP.SAL%TYPE; --타입참조
    --VSAL NUMBER(5);
    USAL NUMBER(5);
BEGIN
	SELECT SAL INTO VSAL FROM EMP WHERE EMPNO=VEMPNO;
    IF VSAL>=800 THEN
        USAL:=VSAL*1.5;
    ELSIF VSAL>=2000 THEN
        USAL:=VSAL*1.3;
    ELSIF VSAL>=3000 THEN
        USAL:=VSAL*1.1;
    ELSE
        USAL:=VSAL;
    END IF;
    UPDATE EMP SET SAL=USAL WHERE EMPNO=VEMPNO;
    COMMIT; --인상된 월급 저장하고 커밋
    DBMS_OUTPUT.PUT_LINE(VEMPNO||'사원의 급여가 '||USAL||' 로 인상되었습니다');
END;
/
EXECUTE UPDATESAL(7369);

결과)
7369사원의 급여가 1320 로 인상되었습니다
    

2. IN/OUT 매개변수

• IN매개변수는 프로시져 내부에서만 사용되는 매개변수이며 OUT 매개변수는 외부의 변수를 참조해서 가져온다.
• 예시

SET SERVEROUTPUT ON
CREATE OR REPLACE PROCEDURE ADD1
(
	N1 IN NUMBER,
    N2 IN NUMBER,
    N3 OUT NUMBER
)
IS
BEGIN
	N3 := N1 + N2;
    DBMS_OUTPUT.PUT_LINE(N1||'+'||N2||'='||N3);
END;
/

---
DECLARE
	N NUMBER(4);
BEGIN
	ADD1(1,2,N);
    DBMS_OUTPUT.PUT_LINE('N===========>'||N);
END;
/

• 결과

1+2=3
N===========>3

• 예2 (sqlplus)

SQL> set serveroutput on
SQL> var n number
SQL> execute add1(10,20,:n)
10+20=30

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> print n

         N
----------
        30