[PL] 프로시저 예제

1.프로시저(Procedure)

-- 프로시저 : 이름, 매개변수, 반환값(x)
CREATE OR REPLACE PROCEDURE hello  -- 매개 변수가 없으면 () 생략
    IS  -- 프로시저의 시작
        msg VARCHAR2(20) := 'hello world'; -- 변수 초기값 선언
    BEGIN  -- 문장의 시작
        DBMS_OUTPUT.PUT_LINE(msg);   
    END;   -- 문장의 끝
/   -- 프로시저 종료

- DBMS_OUTPUT.PUT_LINE 활성화

    SET SERVEROUTPUT ON;

-프로시저의 실행

    EXEC hello;

---

2.프로시저 예제 (IN / OUT / IN OUT 형태)

 

2-1) 사원번호를 받아 해당 사원 급여를 올려주는 프로시저 (in)

--IN 매개변수
--IN : 값이 프로시저 안으로 들어감
CREATE OR REPLACE PROCEDURE up_salary(eno IN NUMBER ) IS
    BEGIN
        UPDATE employees SET salary = salary*1.1
        WHERE emp_no = eno;
        COMMIT;
    END;

실행 : EXEC up_salary(115);

 

2-2) 사원번호를 받아 해당 사원 급여를 올려주는 프로시저 (out)

--out 매개변수
--OUT : 프로시저는 반환값이 없으므로 OUT 매개변수를 활용
--사원 번호를 넣으면 사원명과 급여 반환

CREATE OR REPLACE PROCEDURE find_emp(v_eno IN NUMBER, 
    v_fname OUT NVARCHAR2, v_lname OUT NVARCHAR2, v_sal OUT NUMBER)
IS
    BEGIN
        SELECT first_name, family_name, salary 
        INTO v_fname, v_lname, v_sal
        FROM employees WHERE emp_no = v_eno;
    END;

--반환된 값을 받을 변수를 선언해주어야함.

   VARIABLE v_fname NVARCHAR2(8);

   VARIABLE v_lname NVARCHAR2(8);

  VARIABLE v_sal NUMBER;

 

--실행 EXECUTE find_emp(115,:v_fname,:v_lname,:v_sal);

    PRINT v_fname; PRINT v_lname; PRINT v_sal;

 

2-3) IN OUT 매개 변수 형태의 프로시저

--IN OUT 매개 변수
--매개변수로 시작하고 반환변수로 끝난다.
--프로시저가 다른 프로시저 호출 후 반환값을 보여줄때 사용
CREATE OR REPLACE PROCEDURE find_sal(v_eno IN OUT NUMBER) 
IS
    BEGIN
    SELECT salary
    INTO v_eno
    FROM employees WHERE emp_no=v_eno;
END;

--익명 프로시저(DB에 저장 되지 않는다.)
DECLARE
    v_eno NUMBER := 115;
    BEGIN
    DBMS_OUTPUT.PUT_LINE('eno= '||v_eno);
    find_sal(v_eno);
    DBMS_OUTPUT.PUT_LINE('eno= '||v_eno);

    END;

---

3.프로시저 예제 (일반 데이터  타입/ 스칼라 타입 /  ROWTYPE )

 

 

3-1) 일반 데이터 타입의 프로시저

-- 일반 데이터 타입
-- 변수 이름 [데이터 타입] NOT NULL := 초기값;

DECLARE 
    var_emp_no NUMBER;
    var_emp_name VARCHAR2(20);
    var_emp_grade CHAR(2) := 'A';
    BEGIN
    var_emp_no :=123;
    var_emp_name :='홍길동';
    DBMS_OUTPUT.PUT_LINE('사원 번호:' ||var_emp_no);
    DBMS_OUTPUT.PUT_LINE('사원 이름:' ||var_emp_name);
    DBMS_OUTPUT.PUT_LINE('사원 평가:' ||var_emp_grade);
    END;

  3-1) 사원번호 115번에 대한 이름, 성, 급여

DECLARE 
    -- 변수 선언
    var_emp_no NUMBER(3); --사원번호
    var_emp_name NVARCHAR2(8); --사원 이름
    var_emp_salary NUMBER;
    BEGIN
    --실행할 쿼리문  
     SELECT emp_no, first_name, salary
     INTO var_emp_no, var_emp_name, var_emp_salary
     FROM employees where emp_no=115;
    
    DBMS_OUTPUT.PUT_LINE('사원 번호:' ||var_emp_no);
    DBMS_OUTPUT.PUT_LINE('사원 이름:' ||var_emp_name);
    DBMS_OUTPUT.PUT_LINE('사원 급여:' ||var_emp_salary);
    --출력문
    END;

 

3-2) 스칼라 타입의 프로시저(특정 테이블의 컬럼 형태를 그대로 가져오는 타입)

-- 스칼라 타입 : 특정 테이블의 컬럼 형태를 그대로 가져온다.
-- 변수명 tableName.columeName%TYPE;
-- 
-- 사원번호 115번에 대한 이름, 성, 급여
-- 변수명 tableName.columeName%TYPE;
DECLARE
    var_emp_no employees.emp_no%TYPE;
    var_emp_fn employees.first_name%TYPE;
    var_emp_ln employees.family_name%TYPE;
    var_emp_sal employees.salary%TYPE;    
    BEGIN
        SELECT emp_no, family_name, first_name, salary
        INTO var_emp_no, var_emp_ln, var_emp_fn, var_emp_sal
        FROM employees WHERE emp_no = 115;    

        DBMS_OUTPUT.PUT_LINE('사원 번호 : ' || var_emp_no);
        DBMS_OUTPUT.PUT_LINE('사원 이름 : ' || var_emp_ln ||','|| var_emp_fn);
        DBMS_OUTPUT.PUT_LINE('사원 급여 : ' || var_emp_sal);
    END;

 

위의 코드 내용을 사원 번호를 매개변수로 받는 프로시저로 바꾸어보자

CREATE OR REPLACE PROCEDURE up_no(eno IN employees.emp_no%TYPE) IS
    var_emp_no employees.emp_no%TYPE;
    var_emp_fn employees.first_name%TYPE;
    var_emp_ln employees.family_name%TYPE;
    var_emp_sal employees.salary%TYPE;    
   BEGIN
   SELECT emp_no, family_name, first_name, salary
   INTO var_emp_no, var_emp_ln, var_emp_fn, var_emp_sal
   FROM employees WHERE emp_no = eno; 
   
      DBMS_OUTPUT.PUT_LINE('사원 번호 : ' || var_emp_no);
      DBMS_OUTPUT.PUT_LINE('사원 이름 : ' || var_emp_ln ||','|| var_emp_fn);
      DBMS_OUTPUT.PUT_LINE('사원 급여 : ' || var_emp_sal);
   END;

EXEC up_no(115);
 

 

3-3) ROWTYPE의 프로시저(해당 테이블의 데이터 타입을 통으로 들고 온다.)

--ROWTYPE은 해당 테이블의 데이터 타입을 통으로 들고 온다.
--변수명 tableNAmes%ROWTYPE
DECLARE
    v_emp employees%ROWTYPE;
    BEGIN
        SELECT emp_no, family_name, first_name, salary
        INTO v_emp.emp_no , v_emp.family_name, v_emp.first_name, v_emp.Salary 
        FROM employees WHERE emp_no = 115;    

        DBMS_OUTPUT.PUT_LINE('사원 번호 : ' || v_emp.emp_no);
        DBMS_OUTPUT.PUT_LINE('사원 이름 : ' || v_emp.first_name ||','|| v_emp.family_name);
        DBMS_OUTPUT.PUT_LINE('사원 급여 : ' || v_emp.Salary );
    END;

 

---

4. 프로시저 예제

-emp 테이블에 ename, job, deptno을 추가하는 프로시저 생성

-- 문제1) emp 테이블에 ename, job, deptno을 추가하는 프로시저 생성
--emp_insert('jo','manager',4)
SELECT * FROM emp;
desc emp;
CREATE OR REPLACE PROCEDURE test001
   (en IN emp.ename%TYPE, ej IN emp.job%Type, dp IN emp.deptno%TYPE) 
Is
    BEGIN
    INSERT INTO emp(ename,job,deptno)VALUES(en, ej, dp); 
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('이름: '||en);  
    DBMS_OUTPUT.PUT_LINE('직급: '||ej);
    DBMS_OUTPUT.PUT_LINE('부서번호: '||dp);
        DBMS_OUTPUT.PUT_LINE('등록완료');  
    END;
/
SET SERVEROUT ON;
EXECUTE test001('jo','manager',4);

 

-employees에서 특정 사원의 급여를 n% (인상/이하 ) 하는 프로시저

--문제2) employees에서 특정 사원의 급여를 n% 인상/이하
--예) emp_update(117,10) --117번 사원의 급여 10% 인상
--예) emp_update(117,-10) --117번 사원의 급여 10% 인하

CREATE OR REPLACE PROCEDURE test002(v_emp_no IN EMPLOYEES.EMP_NO%TYPE, v_sal IN NUMBER)
  IS
  v_emp employees%ROWTYPE;
  BEGIN 
  UPDATE employees SET Salary=salary+(salary*(v_sal/100)) WHERE emp_no=v_emp_no;   
  commit;
  SELECT EMP_NO, FIRST_NAME, SALARY
  INTO v_emp.emp_no, v_emp.first_name, v_emp.salary
  FROM employees WHERE emp_no=v_emp_no;
  
  DBMS_OUTPUT.PUT_LINE('사원 번호 : ' ||v_emp.emp_no);
  DBMS_OUTPUT.PUT_LINE('이름 : ' || v_emp.first_name);
  DBMS_OUTPUT.PUT_LINE('급여 : ' || v_emp.salary );
END;
/
execute test002(116,10);
execute test002(116,-10);

SET SERVEROUTPUT ON;

 

-  특정 사원을 삭제하는 프로시저를 작성

--문제3. 특정 사원을 삭제하기.
--사원 번호 118번이 삭제 되었습니다.

CREATE OR REPLACE PROCEDURE test03(v_emp_no IN EMPLOYEES.EMP_NO%TYPE)
IS
  v_emp employees%ROWTYPE;
BEGIN
DELETE FROM employees WHERE emp_no=v_emp_no;
commit;
DBMS_OUTPUT.PUT_LINE('사원 번호 : ' ||v_emp_no ||'가 삭제되었습니다.');
END;
/
EXECUTE test03(115);