프로시저(Procedure), 함수 - ORACLE DB

프로시저(Procedure)란?

- PL/SQL 문법을 활용하여 일종의 프로그램(함수)를 미리 작성하고, DB에 저장하는 객체

- 재사용성을 높여주고, 사용자가 필요 시 언제든 호출할 수 있는 기능

---

장점

- 네트워크를 적게 사용하여 DB 성능 향상에 기여할 수 있다.

- 반복, 주기적으로 사용하는 DBA의 명령어를 영구적으로 저장할 수 있다.

- PL/SQL문을 저장하는 객체로 필요할 때 마다 복잡한 구문을 다시 입력할 필요 없이 간단하게 호출 후 실행 결과를 얻을 수 있다

---

단점

- 개발자 입장에서는 기능이 분산되어 프로그램 관리가 어려워진다.

- 디버깅이 어렵고, 현재 프로그램 상태를 정확히 알 수 없어 관리가 어려워진다.

- 프로시저가 난발되면 DB 성능 저하와 비용이 상승한다.

---

문법

CREATE PROCEDURE <프로시저명> [매개변수 명 [MODE] <TYPE>]
IS
    변수 선언부
BEGIN
    실행 내용
END;
/

MODE : IN(데이터 입력, 기본값) / OUT(데이터 반환)

※ 주의 : 매개변수 데이터 타입 크기를 지정하면 안 된다.

 

프로시저 관련 테이블 조회

SELECT * FROM ALL_PROCEDURES;   -- 모든 프로시져 확인
SELECT * FROM ALL_PROCEDURES WHERE OWNER='계정';   -- 특정 계정의 프로시져 확인
SELECT * FROM USER_PROCEDURES;  -- 현재 사용자가 생성한 프로시져 목록 확인
SELECT * FROM USER_SOURCE;      -- 현재 사용자가 생성한 프로시져 상세 코드 확인
SELECT * FROM USER_SOURCE WHERE NAME = '프로시저명';  -- 특정 프로시져의 상세 코드 확인

프로시저 실행 방법

EXECUTE <프로시저명> [전달값];
EXEC <프로시저명> [전달값];

프로시저 수정 방법

- 별도로 존재하지 않고, CREATE를 통해 재생성하여 관리

- CREATE OR REPLACE문 활용

 

프로시저 삭제 방법

DROP PROCEDURE '이름';

---

 

예제 )

 

매개변수가 있는 프로시저

CREATE OR REPLACE PROCEDURE TEST_PRO2(N_VALUE NUMBER)
IS
BEGIN
    DBMS_OUTPUT.PUT_LINE('입력값 : ' || N_VALUE);
END;
/

EXEC TEST_PRO2(100); 결과 : 100
EXEC TEST_PRO2(200.3); 결과 : 200.3

---

 

함수(FUNCTION)란?

-프로시저와 거의 유사한 용도로 사용하지만 실행 결과를 되돌려 받을 수 있다는 점에서 프로시저와 다르다.

---

문법

CREATE OR REPLACE FUNCTION '함수명' [<매개변수명> <데이터 타입, ...>]
	RETURN <반환타입>
	IS
		-- 지역변수
	BEGIN
		-- 실행할 내용
	RETURN 반환값
END;

함수 실행 방법

SELECT TEST_FUNC FROM DUAL;

---

예제 )

CREATE OR REPLACE FUNCTION TEST_FUNC
RETURN NUMBER
IS
BEGIN
    DBMS_OUTPUT.PUT_LINE('함수 1 실행');
    RETURN 10;
END ;
/