[Oracle] PL/SQL 프로시저/함수 (Procedure/Function)

<저장프로시저> Stored Procedure

- 자주 사용되는 DML,DQL을 모듈화 시켜서 DB에 저장하였다가

  필요할 때 호출해서 사용!!   

- JAVA의 메소드와 같음

형식)

  CREATE [OR REPLACE] PROCEDURE 프로시저명(변수명1 [IN|OUT] 자료형, 변수명2 자료형)

  IS   

         변수선언

  BEGIN

        실행할 문장1;

        실행할 문장2;

        실행할 문장3;

    -- 초기화, SQL문, 함수호출, 절차적요소(조건문,반복문)      

  END;

  / 

실행방법) EXECUTE 프로시저명();

참고)

     void hello(String name){

        System.out.println("안녕,"+name);        

     }

     ----> Stored Procedure(리턴 없고)

     String hi(){

        return "좋은 하루~!!";

     }

     ----> Stored Function(리턴 있고)

     

문제) 부서테이블(dept3)에서 40번 부서를 삭제하시오. (프로시저 사용)

 drop table dept3;

 create table dept3

 as select * from dept;

 ---> pro_test.sql

SQL> @1110/pro_test

Procedure created.   ===> 프로시저를 DB에 저장!!(delete 실행 안됨!!) 

SQL> execute del_dept()

#40번 부서를 삭제하였습니다!!   ===> 정의된 프로시저내의 delete실행!!

 

SQL> execute del_dept

#40번 부서를 삭제하였습니다!!   ===> 정의된 프로시저내의 delete실행!! 

SQL> execute del_dept(40)

#40번 부서를 삭제하였습니다!!   ===> 정의된 프로시저내의 delete실행!! 

문제) 사원테이블(emp3)에서 특정사원의 이름을 입력을 통해 삭제하는 프로시저를 생성하시오.

  drop table emp3;

  create table emp3 as select * from emp;

  

  ---> pro_test2.sql

  ---> 프로시저명: del_ename

  

실행 ==> 1. 작성된 프로시저를 DB에 저장(생성)

           @1110/pro_test2.sql

       2. 저장된 프로시저 이름을 호출(실행!!)

           exec[ute] del_ename('ADAMS');

==============================================================

<저장함수> Stored Function

- 실행(함수호출)후 결과를 되돌려 받을 수 있는

  return 데이터; 를   반드시 명시해야 함!!

  

 형식)

  CREATE [OR REPLACE] FUNCTION 함수명(매개변수 선언)

  RETURN 자료형  

  IS

        변수선언

  BEGIN

        실행할 SQL문

    ....

    

    RETURN 데이터;

  END;

  / 

  

 사용법) 

   VARIABLE  바인드변수명   자료형;      --변수선언

   EXEC[UTE] :바인드변수 := 함수명();  --결과값 저장

   PRINT 바인드변수명                                 --저장값 확인!! 

문제) 특정 사원에게 보너스를 지급하려고 하는데 급여의 200%에 해당하는 비용을 지급하려 한다.

     이 보너스 금액을 FUNCTION을 통해 구하시오.     

  ---> fun_test.sql

  ---> 함수명: call_bonus('SMITH');  

 

SQL> @1110/fun_test.sql

Function created.

SQL> variable bonus number

SQL> exec :bonus := call_bonus('SMITH')

PL/SQL procedure successfully completed.

SQL> print bonus

     BONUS

----------

      1600