오라클PLSQL, 저장 프로시저, CREATE OR REPLACE PROCEDURE
15.3. 저장 프로시저(Stored Procedure)
오라클 데이터베이스에 스키마 객체로 저장된 명명된 PL/SQL 블록으로 자신을 호출한 곳으로 리턴 해주는 값은 없고 호출되어 실행만 됩니다.
저장 프로시저 Header의 끝에는 IS[or AS]가 와야하고 IS와 BEGIN 사이에 BEGIN ~ END에서 사용할 변수를 선언합니다.
자신의 스키마에서 저장 프로시저를 만들기 위해서는 CREATE PROCEDURE 시스템권한이
있어야 하며 다른 스키마 계정에서 프로시저를 만들기 위해서는 CREATE ANY PROCEDURE 시스템 권한이 이 있어야 합니다.
실행환경과 저장프로시저 사이에 값을 전달하기 위해 파라미터를 사용하는데 다음과 같은 종류가 있습니다.
IN : 호출하는 곳에서 함수 or 프로시저로 값을 전달 OUT : 함수 or 프로시저에서 호출한 곳으로 값을 전달 IN OUT : 호출하는 곳에서 함수 or 프로시저로 값을 전달하고 동시에 함수 or 프로시저에서 호출한 곳으로 값을 전달하는 변수. |
<기본형식>
CREATE [OR REPLACE] PROCEDURE procedure_name [parameters] IS[or AS] declaration_section BEGIN execution_section EXCEPTION exception section END [procedure_name]; /
procedure_name : 생성하고자 하는 프로시저의 이름. parameters: 프로시저에 전달할 파라미터 목록. declaration_section : 변수나 커서, 타입등을 선언 하는 영역 execution_section : 실제 비지니스 로직처리, PL/SQL 제어문, 반복문, DML등 exception_section : 오류가 발생했을 때의 처리. / : SQL*Plus에서 실행기호. SQL*Plus에서 함수를 생성하는 경우에는 반드시 기술해야 합니다. SQL*Developer에서 실행시에는 생략 가능.
|
저장 프로시저를 만들고 호출하는 실습을 해보겠습니다.
15.3.1. 저장 프로시저 작성 실습 : CREATE OR REPLACE PROCEDURE
EMP 테이블에서 사번을 인자로 입력받아 해당 사원의 이름을 출력하는 저장 프로시저를 P_PRINT_ENAME 이라는 이름으로 작성하세요. |
SET SERVEROUTPUT ON;
-- 이미 P_PRINT_ENAME 프로시저가 있다면 대체 합니다. -- 프로시저를 호출할 때는 숫자형으로 사번을 던져줘야 합니다. CREATE OR REPLACE PROCEDURE P_PRINT_ENAME(P_EMPNO IN NUMBER) IS /* IS ~ BEGIN 사이는 이름없는 블럭을 만들때의 DECLARE ~ BEGIN과 동일하게 변수를 선언하는 선언부 입니다. V_EANE 변수는 EMP 테이블의 ENAME과 타입이 같습니다. */ V_ENAME EMP.ENAME%TYPE; BEGIN
/* BEGIN ~ END 사이의 SELECT절은 반드시 한건만 SELECT 되야 하며 INTO절이 반드시 필요합니다. WHERE절의 사원이 없다면 EXCEPTION절의 WHEN NO_DATA_FOUND THEN 절로 이동합니다. */ SELECT ENAME INTO V_ENAME FROM EMP WHERE EMPNO = P_EMPNO; -- V_ENAME 변수값을 출력 합니다. DBMS_OUTPUT.PUT_LINE(V_ENAME);
-- 예외처리 절 입니다. EXCEPTION
/* NO_DATA_FOUND는 PL/SQL에 사전에 정의되어 있는 예외 입니다. ORA-01403 오류 코드이며 SELECT시 데이터를 찾을 수 없을때 발생하는 예외 입니다. WHEN 절에서 일일이 예외를 처리하기가 번거러우면 모든 예외를 다 처리할 수 있는 WHEN OTHERS THEN만 사용해도 무방 합니다. */ WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(P_EMPNO || ' 사원은 없습니다.'); WHEN OTHERS THEN -- 기타 오류인 경우 SQL 에러코드와 에러 메시지를 출력 합니다. DBMS_OUTPUT.PUT_LINE(SQLCODE || ':' || SQLERRM); END P_PRINT_ENAME; |
<실행결과>
Procedure P_PRINT_ENAME이(가) 컴파일되었습니다.
앞에서 작성한 P_PRINT_ENAME 프로시저를 사번을 파라미터로 주면서 호출해 보겠습니다. |
EXEC P_PRINT_ENAME(7369); |
<실행결과>
SMITH
존재하지 않는 사번을 파라미터로 해서 호출해 보겠습니다. |
EXEC P_PRINT_ENAME(1111); |
<실행결과>
1111 사원은 없습니다.
존재하지 않는 사번을 입력하니 WHEN NO_DATA_FOUND THEN절이 호출되는 것을 확인할 수 있습니다.
15.3.2. 저장 프로시저 OUT 파라미터 실습 : CREATE OR REPLACE PROCEDURE
저장 프로시저를 호출한 곳으로 값을 넘기기 위해서는 파라미터 타입을 OUT 으로 선언해야 합니다. 저장 프로시저의 파라미터를 사번(IN), 급여(OUT) 2개로 정의하고 사번은 IN 파라미터로 호출하는 곳에서 프로시저로 전달하고 급여는 OUT 파라미터로 해서 프로시저에서 호출한 곳으로 전달하는 예제를 작성하겠습니다. |
SET SERVEROUTPUT ON; -- P_EMPNO는 프로시저를 호출하는 곳에서 넣어 주는 파라미터 -- P_SAL은 프로시저에서 호출한 쪽으로 넘기는 파라미터 CREATE OR REPLACE PROCEDURE P_GET_SAL(P_EMPNO IN NUMBER, P_SAL OUT NUMBER) IS BEGIN -- 인자로 받은 사번을 WHERE절에서 조건으로 비교하여 급여(SAL)를 -- SELECT하여 OUT 파라미터인 P_SAL 변수에 담습니다. -- 담기만 하면 이 값은 외부에서 참조 가능 합니다. SELECT SAL INTO P_SAL FROM EMP WHERE EMPNO = P_EMPNO; EXCEPTION WHEN OTHERS THEN -- 기타 오류인 경우 에러 메시지를 출력 합니다. DBMS_OUTPUT.PUT_LINE(SQLERRM); P_SAL := 0; END P_GET_SAL; |
<실행결과>
Procedure P_GET_SAL이(가) 컴파일되었습니다.
앞에서 작성한 P_GET_SAL 프로시저를 사번, 급여를 파라미터로 주면서 호출해 보겠습니다. |
VARIABLE SAL NUMBER; ❶ EXEC P_GET_SAL(7369, :SAL); ❷ PRINT SAL; ❸ |
<실행결과>
SAL
----------
800
❶ NUMBER형 변수 SAL을 선언 합니다.
❷P_GET_SAL 프로시저를 호출하면서 IN 파라미터인 사번(7369)과 OUT 파라미터인 SAL을 넘깁니다. 외부에서 선언한 SAL 변수는 호스트 변수라고 하며 앞에 콜론(:)표시를 해야 합니다.
❸ 프로시저가 실행이 되고 나면 SAL 변수에 값이 들어 있으므로 PRINT문으로 출력 합니다.
VARIABLE, EXEC, PRINT등은 SQL문이 아닌 SQL*PLUS 명령어로 SQL*PLUS 실행환경에서 사용가능 합니다. 물론 SQL*DEVELOPER에서도 사용 가능 합니다.
앞에서 작성한 P_GET_SAL 프로시저를 존재하지 않는 사번, 급여를 파라미터로 주면서 호출해 보겠습니다. |
SET SERVEROUTPUT ON; ❶ VARIABLE SAL NUMBER; ❷ EXEC P_GET_SAL(1111, :SAL); ❸ PRINT SAL; ❹ |
<실행결과>
ORA-01403: 데이터를 찾을 수 없습니다.
SAL
----------
0
❶ DBMS_OUTPUT.PUT_LINE 출력을 활성화 합니다.
❷ NUMBER형 변수 SAL을 선언 합니다.
❸ P_GET_SAL 프로시저를 호출하면서 IN 파라미터인 존재하지 않는 사번(1111)과 OUT 파라미터인 SAL을 넘깁니다. 외부에서 선언한 SAL 변수는 호스트 변수라고 하며 앞에 콜론(:)표시를 해야 합니다. 1111 사번은 존재하지 않으므로 WHEN OTHERS THEN절이 실행되어 “ORA-01403: 데이터를 찾을 수 없습니다.” 라는 오류 메시지를 출력 후 P_SAL 변수에 0을 대입하여 호출한 곳에서 참조할 수 있도록 합니다.
❹ 프로시저가 실행이 되고 나면 SAL 변수에 0이 들어 있으므로 PRINT문으로 출력 합니다.
