[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