[Oracle] 함수(Function)와 프로시저(Procedure)

 

1. 프로시저와 함수의 차이

 

프로시저(Procedure)

---

일련의 쿼리를 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합이며, 일련의 작업을 정리한 절차. 보통 단독으로 실행해야 할 작업을 위임받았을 때 사용

함수(Function)

---

하나의 특별한 목적의 작업을 수행하기 위해 독립적으로 설계된 코드의 집합

→ 함수가 여러 작업을 위한 기능이라면 프로시저는 작업을 정리한 절차

---

 

 

 

매개변수

---

프로시저

• 매개변수를 입력, 출력, 입출력 형식으로 받을 수 있다.

함수

• 매개변수를 입력 형식으로 받을 수 있다.

---

 

반환값

---

프로시저

• 반환값을 가질 수도 있고 가지지 않을 수도 있다.

함수

반환값을 반드시 가져야 한다.

---

 

쿼리문 내에서 실행가능

---

프로시저

• SELECT, WHERE 문 등에서 사용 불가

함수

• SELECT, WHERE 문 등에서 사용 가능

---

 

처리 장소

---

프로시저

• 클라이언트(화면)에서 값을 건네받아 서버에서 작업을 한 뒤 클라이언트에게 전달
• 서버에서 실행이 되어 속도 면에서 빠른 성능을 보여줌

함수

• 클라이언트(화면)에서 값을 건네 받고 서버에서 필요한 값을 가져와서 클라이언트에서 작업을 하고 반환
• 클라이언트(화면)에서 실행이 되어 프로시저보단 속도가 느림

---

 

 

2. 함수(Function)

---

함수(Function)란?

• 오라클에서 제공하는 내장함수(TO_CHAR, TO_DATE 등)가 아닌 사용자 정의 함수(User Defined Function)
• 특정 기능들을 모듈화, 재사용 할 수 있어서 복잡한 쿼리문을 간결하게 만들 수 있다.

CREATE [OR REPLACE] FUNCTION function_name [(
    argument...
)] RETURN datatype -- 반환되는 값의 datatype  
   IS [AS]         [선언부]     
	 BEGIN         [실행부 - PL/SQL Block]     
   [EXCEPTION]         
		[EXCEPTION 처리]     
   RETURN 변수; -- 리턴문 필수 
END;

• OR REPLACE: 함수 생성 DDL 명령어 / 이미 존재하는 function이라면 기존 내용을 지우고 재생성. 생략 가능

CREATE OR REPLACE FUNCTION FN_GET_DEPT_NAME(
	P_DEPT_NO IN VALCHAR2
) RETURN VARCHAR2
	IS 
		V_TEST_NAME VARCHAR2(10);
	BEGIN
		SELECT	DELP_NAME
		INTO	V_TEST_NAME
		FROM 	DEPT
		WHERE	DEPT_NO = P_DEPT_NO;
		
	RETURN  	V_TEST_NAME;
END;
출처: <https://goddaehee.tistory.com/261> [갓대희의 작은공간:티스토리]

 

함수 사용 예시

SELECT FN_GET_DEPT_NAME('01') FROM DUAL;

---

3. 프로시저

---

데이터베이스에 대한 일련의 작업을 정리한 절차를 관계형 데이터베이스 관리 시스템에 저장한 것으로 영구저장모듈(Persistent Storage Module)이라고 불린다.

 

보통 저장 프로시저를 프로시저라고 부르며 일련의 쿼리를 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합.

즉, 특정 작업을 위한 쿼리들의 불록

 

장점

• 하나의 요청으로 여러 SQL문을 실행시킬 수 있다.(네트워크 부하를 줄일 수 있음)
• 네트워크 소요 시간을 줄여 성능을 개선할 수 있다.
• 여러 어플리케이션과 공유 가능(API처럼 제공가능)
• 기능 변경이 편함(특정 기능을 변경할 때 프로시저만 변경하면 됨)

 

단점

• 문자나 숫자열 연산에 사용하면 오히려 C, Java보다 느린 성능을 보일 수 있다.
• 유지보수가 어렵다.(프로시저가 앱의 어디에 사용되는지 확인이 어려움)

 

1) 생성

CREATE OR REPLACE PROCEDURE 프로시져이름 (파라미터1,파라미터2...);

IS
변수

BEGIN

쿼리문

END 프로시져 이름;

CREATE OR REPLACE PROCEDURE GET_TIER(in_name IN VARCHAR2,out_tier OUT VARCHAR2)

IS

BEGIN
	
    SELECT TIER INTO out_tier FROM SUMMONER_TB WHERE NAME = in_name;

EXCEPTION
  --소환사를 찾을 수 없을 때
  WEHN NO_DATA_FOUND THEN
  
    out_tier:='NO_SUMMONER_FOUND';

END GET_TIER;

파라미터 값은 in, out, inout 3가지로 작성 가능

• in: 전달될 데이터
• out: 결과로 나갈 데이터
• inout: in과 out 모두 가능한 데이터

 

2) 조회

DECLARE
출력될 변수 선언
실행할 프로시저
출력문(Optional)
END

DECLARE
out_tier VARCHAR2(10);
BEGIN get_tier('faker',out_tier);
DBMS_OUTPUT.PUT_LINE(out_tier);
END;

 

3) 수정

• create or replace 구문을 사용하면 해당 프로시저명이 있다면 수정, 없다면 생성

CREATE OR REPLACE PROCEDURE GET_TIER(in_name IN VARCHAR2,out_tier OUT VARCHAR2)

IS

BEGIN
	
    SELECT NAME INTO out_tier FROM SUMMONER_TB WHERE NAME = in_name;

EXCEPTION

  WHEN NO_DATA_FOUND THEN

    out_tier:='NO_DATA_FOUND';

END get_tier;

 

4) 삭제

DROP PROCEDURE 프로시저명;

DROP PROCEDURE GET_TIER; 

---

출처

https://goddaehee.tistory.com/261

https://fomaios.tistory.com/entry/Oracle-함수Function와-프로시저Procedure-차이

https://fomaios.tistory.com/entry/PLSQL-%ED%94%84%EB%A1%9C%EC%8B%9C%EC%A0%80Procedure%EB%9E%80-feat-CRUD

https://wrtn.ai/