저장 프로시저 & 함수

저장 프로시저(stored procedure)

특정 처리 작업을 수행하는 데 사용하는 저장 서브프로그램

 

---

파라미터를 사용하지 않는 프로시저

작업 수행에 별다른 입력 데이터가 필요하지 않은 경우 사용

-- 프로시저 생성
CREATE (OR REPLACE) PROCEDURE 프로시저명
[IS|AS]
    변수선언문;
BEGIN
	프로그램실행문;
EXCEPTION
    예외처리문;
END 프로시저명;
/


-- 결과 출력
SET SERVEROUTPUT ON;


-- 프로시저 실행
EXECUTE 프로시저명;


-- 프로시저 삭제
DROP PROCEDURE 프로시저명;

 

---

파라미터를 사용하는 프로시저

-- 파라미터 선언문
파라미터명 [IN|OUT|IN OUT(기본값: IN)] 데이터타입;

 

IN 모드 파라미터

프로시저 실행에 필요한 값을 직접 입력받는 형식의 파라미터 지정

-- 프로시저 생성
CREATE (OR REPLACE) PROCEDURE 프로시저명 (
    파라미터명1 IN 데이터타입,
    파라미터명2 IN 데이터타입)
[IS|AS]
    변수선언문;
BEGIN
	프로그램실행문;
EXCEPTION
    예외처리문;
END 프로시저명;
/


-- 프로시저 실행
-- 위치 지정
EXECUTE 프로시저명(파라미터_입력값1, 파라미터_입력값2);

-- 이름 지정
EXECUTE 프로시저명(파라미터명1 => 파라미터_입력값1, 파라미터명2 => 파라미터_입력값2);

 

OUT 모드 파라미터

프로시저 실행 후 호출한 프로그램으로 값 반환

-- 프로시저 생성
CREATE (OR REPLACE) PROCEDURE 프로시저명 (
    파라미터명1 OUT 데이터타입,
    파라미터명2 OUT 데이터타입)
[IS|AS]
    변수선언문;
BEGIN
	프로그램실행문;
EXCEPTION
    예외처리문;
END 프로시저명;
/

-- 반환값을 담을 변수 선언
VARIABLE 반환변수명1 데이터타입;
VARIABLE 반환변수명2 데이터타입;

-- 프로시저 실행
EXECUTE 프로시저명(:반환변수명1, :반환변수명2);

 

---

프로시저 오류 정보 확인

경고 : 컴파일 오류와 함께 프로시저가 생성되었습니다.

SHOW ERRORS;

SHOW ERR 프로그램종류 프로그램명;

 

---

함수(function)

-- 함수 생성
CREATE (OR REPLACE) PROCEDURE 프로시저명 (
    파라미터명1 IN 데이터타입,
    파라미터명2 IN 데이터타입)
RETURN 데이터타입
[IS|AS]
    변수선언문;
BEGIN
	프로그램실행문;
    RETURN (반환값);
EXCEPTION
    예외처리문;
END 함수명;
/

-- 반환값을 담을 변수 선언
VARIABLE 반환변수명 데이터타입;


-- 함수 실행
EXECUTE :반환변수명 := 함수명(파라미터명1, 파라미터명2);


-- SQL에서 함수 실행
SELECT 함수명(파라미터명, 파라미터명2)
FROM DUAL;


-- 함수 삭제
DROP FUNCTION 함수명;

 

---

프로시저와 함수 비교

특징	프로시저	함수
실행	EXECUTE 명령어 또는 다른 PL/SQL 서브프로그램 내에서 호출하여 실행	변수를 사용한 EXECUTE 명령어 또는 다른 PL/SQL 서브프로그램에서 호출하여 실행 혹은 SQL문에서 직접 실행
파라미터 지정	필요에 따라 지정하지 않을 수 있음 여러 개 지정 가능 IN, OUT, IN OUT 세 가지 모드 사용 가능	프로시저와 같게 지정하지 않을 수 있음 여러 개 지정 가능 IN 모드(또는 생략)만 사용
값의 반환	실행 후 값의 반환이 없을 수 있음 OUT, IN OUT 모드의 파라미터 수에 따라 여러 개 값 반환 가능	반드시 하나의 값을 반환해야함 프로시저와 달리 OUT, IN OUT 모드의 파라미터를 사용하는 것이 아니라 RETURN절과 RETURN문을 통해 값 반환