[Oracle (오라클)] PL/SQL - 2 : PACKAGE, FUNCTION 사용법

지난 장에서는 pl/sql을 간단히 소개하고, 사용법을 숙지 및 경험을 통한 장단점을 알아보았다.

 

지난 내용이 궁금하면 여기 클릭!

 

이번 장에서는 package와 function의 차이점을 알아보고, 사용법에 대해 알아보고자 한다.

 

참고한 내용은 다음과 같다.

 

http://www.tcpschool.com/cpp/cpp_function_basic

https://www.onlineinterviewquestions.com/what-is-the-difference-between-function/

 

 

1. FUNCTION vs PROCEDURE vs PACKAGE의 차이점

pl/sql은 지난 시간에 봤듯이 단순히 스크립트에서 선언해서 사용하는 것이 아니라 보통 function, procedure, package중 하나의 형태로 저장하고 원하는 것을 호출해 사용한다. 

 

우선 function, procedure, package 이 각 세 개의 항목들의 차이점에 대해 알아보자.

 

Function(뻥션)

pl/sql Function(함수)는 리턴 타입을 가지고 있는데 계산을 하는 역할을 한다. 그리고 단일 값을 반환할 수 있다. 

반환 값의 사양에 따라, 리턴되는 값은 동일한 형식이어야만 한다. 

 

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

 

이게 무슨 말이냐하면... return 하는 값이 만약 NUMBER라면, 그 함수를 호출해서 값을 받는 곳도 NUMBER여야 한다는 말이다.

 

Procedure(프로시저)

Procedure(프로시저)는 실행을 멈출 수 있고 호출자에게 반환하는 반환 상태에 책임이 있다.
함수와 달리, 프로시저는 특정한 반환 타입이나 하나를 반환하지 않고, 아닌 많은 수를 반환한다. 

 

함수와는 달리 프로시저는 여러 값을 반환할 수 있거나 혹은 없을수도 있다. 호출자는 반환값을 받던지, 안받던지 결정해 다른 일을 또 할 수 있다. 이것은 사실 함수에서도 마찬가지일 것이다.

 

Package(패키지)

schema object(스키마 오브젝트)인 Package(패키지)는 논리적으로 연결된 pl/sql의 타입들, 하위 프로그램들, 그리고 아이템들을 묶는 역할을 한다. 패키지는 즉, 모두 프로시저, 레코드 타입 상태, 함수로 구성된다. 

modularity(모듈성)을 제공할 수 있음으로서, 패키지는 어플리케이션을 개발하는것을 가능하게 한다. 

package를 사용함으로, 비인가된 사용자로부터 정보를 숨길 수 있다.

 

쉽게 말하면 패키지는 원하는 기능을 한데 묶어놓은 곳이며, 원하는 것을 호출해 사용하면 된다. 

 

필자의 경험으로는 함수같은 경우에는 보통 계산이 필요한 경우(java로 치면 util 성 method)에 많이 사용하고,

 

프로시저의 경우에 package단위로 하나의 모듈로 묶어서 사용한다.

 

그도 그럴게 Package의 경우 다양한 것이 들어갈 수 있다지만 높은 자유도는 관리에 어려움을 주기에

 

필자의 회사에서는 그렇게 하고 있는 것 같다.

 

위에 친구들(?)을 사용함으로 생기는 장점은 인터넷에 치면 나오니 궁금한 분들은 검색을 추천한다. 

 

2. Package(패키지)생성하기

필자가 실습을 위해 사용하는 Oracle 버전은 19C이며, DBeaver를 사용하고 있다.

 

아무 스키마나 알아서 생성하고 필자는 "PKG_TEST_01" 이라는 PACKAGE를 생성했다.

PACKAGE - Declaration

CREATE OR REPLACE PACKAGE LOY.PKG_TEST
AS
	FUNCTION FN_TEST_01(IN_PARAMETER IN NUMBER)
		RETURN VARCHAR2;

	PROCEDURE TEST_PROCEDURE_01(
		  IN_PARAMETER_01 IN VARCHAR2 
		, IN_PARAMETER_02 IN VARCHAR2
		, OUT_RESULT      OUT VARCHAR2
		, OUT_RESULT1     OUT VARCHAR2
	);
	
END PKG_TEST;

PACKAGE - BODY

CREATE OR REPLACE PACKAGE BODY LOY.PKG_TEST
AS
	-- FUNCTION 선언도 가능. 다만 프로시저와 섞이면 관리가 피곤하다는 생각
	FUNCTION FN_TEST_01(IN_PARAMETER IN NUMBER) -- INPUT 갯수와 형식은 마음대로 설정
		RETURN VARCHAR2
	IS 
		V_RESULT VARCHAR2(50):='함수 생성 완료';
	BEGIN
		
		DBMS_OUTPUT.PUT_LINE('함수 생성이지롱~');
		DBMS_OUTPUT.PUT_LINE('IN_PARAMETER :: ' || IN_PARAMETER);
		RETURN V_RESULT; -- 결과값 반환
	END;

	PROCEDURE TEST_PROCEDURE_01(
		  IN_PARAMETER_01 IN VARCHAR2 
		, IN_PARAMETER_02 IN VARCHAR2
		, OUT_RESULT      OUT VARCHAR2
		, OUT_RESULT1     OUT VARCHAR2
	)
	IS 
		V_RESULT VARCHAR2(50) := 'DEFAULT';
		
	BEGIN
		
		DBMS_OUTPUT.PUT_LINE('TESTING...');
		DBMS_OUTPUT.PUT_LINE('V_RESULT ?? :: ' || V_RESULT);
		
		OUT_RESULT := V_RESULT;
		OUT_RESULT1 := V_RESULT || '1';
	END TEST_PROCEDURE_01; 
-- Package body
END PKG_TEST;

실행코드

SELECT LOY.PKG_TEST.FN_TEST_01(10) FROM DUAL;

DECLARE 
	V_TESTING VARCHAR2(50) := 'TESTING 입니다.';
	V_RESULT  VARCHAR2(50);
	V_RESULT1 VARCHAR2(50);
BEGIN
	LOY.PKG_TEST.TEST_PROCEDURE_01(
		  IN_PARAMETER_01 => 'TEST1' 
		, IN_PARAMETER_02 => 'TEST2'
		, OUT_RESULT      => V_RESULT
		, OUT_RESULT1     => V_RESULT1
	);
	DBMS_OUTPUT.PUT_LINE('-------------');
	DBMS_OUTPUT.PUT_LINE('V_RESULT :: ' || V_RESULT);
	DBMS_OUTPUT.PUT_LINE('V_RESULT1 :: ' || V_RESULT1);
END;

함수 실행 결과

함수 실행 - 출력 결과

 

프로시저 실행 결과

 

함수, 프로시저를 실행하면서 주의할 점은 결과값에 따라서 호출(call)한 pl/sql에서도

동일한 type으로 받아야 한다는 것이다. 

 

실제로 설명과 동일하게 프로시저의 경우에는 다수의 값을 받고, 보낼 수 있음을 확인했다.

3. Package(패키지) 내용 추가

해당 내용등을 각각 항목에 잘 넣어주면된다.

PACKAGE - Declaration (TEST_PROCEDURE_02 추가)

	...
    
    PROCEDURE TEST_PROCEDURE_02(
		  IN_PARAMETER_01    IN VARCHAR2 
		, IN_PARAMETER_02    IN VARCHAR2
		, OUT_CURSOR_RESULT  OUT SYS_REFCURSOR
	);
    ...

PACKAGE - Procedure body (TEST_PROCEDURE_02 추가)

    ...
    PROCEDURE TEST_PROCEDURE_02(
		  IN_PARAMETER_01    IN VARCHAR2 
		, IN_PARAMETER_02    IN VARCHAR2
		, OUT_CURSOR_RESULT  OUT SYS_REFCURSOR
	)
	IS 
		V_COMMENT VARCHAR2(50) := '여기는 커서 테스트지롱~';	
	BEGIN
		DBMS_OUTPUT.PUT_LINE('IN_PARAMETER_01 :: ' || IN_PARAMETER_01);
		DBMS_OUTPUT.PUT_LINE('IN_PARAMETER_02 :: ' || IN_PARAMETER_02);
		OPEN OUT_CURSOR_RESULT 
		 FOR SELECT 'TEST1-1' AS COL1
		          , 'TEST2-1' AS COL2
			   FROM DUAL
			  UNION ALL
			 SELECT 'TEST1-2' AS COL1
			      , 'TEST2-2' AS COL2
			   FROM DUAL;
	END TEST_PROCEDURE_02;
    ...

실행 테스트 코드

DECLARE 
	V_RESULT  VARCHAR2(50);
	V_RESULT1 VARCHAR2(50);
	V_CURSOR  SYS_REFCURSOR;  -- 기본적으로 oracle 에서 제공하는 cursor 타입. 
	V_COL1    VARCHAR2(50);
	V_COL2    VARCHAR2(50);
BEGIN
	LOY.PKG_TEST.TEST_PROCEDURE_02(
		  IN_PARAMETER_01   => 'TEST1' 
		, IN_PARAMETER_02   => 'TEST2'
		, OUT_CURSOR_RESULT => V_CURSOR
	);
	DBMS_OUTPUT.PUT_LINE('-------------');
    LOOP FETCH V_CURSOR 
	      INTO V_COL1, V_COL2;          -- 전달받은 커서의 컬럼 항목만큼 변수 선언해야함! 
	      EXIT WHEN V_CURSOR%NOTFOUND;  -- 커서의 정보를 뽑아낼 때, 데이터가 없으면 escape
	      DBMS_OUTPUT.PUT_LINE('V_COL1 :: ' || V_COL1);
	      DBMS_OUTPUT.PUT_LINE('V_COL2 :: ' || V_COL2);
    END LOOP;
END;

결과

 

---

 

 

이번 시간에는 크게 어렵지 않은 선에서 함수, 프로시저, 패키지를 알아보았고 사용법도 숙지했다.

 

사실 pl/sql에서 크게 엄청 복잡한 데이터를 다룰 일이 있다고 한다면, 

 

결국 다양한 테이블의 연관관계를 파악하고 (1), 그것을 커서에 담아서(2) 값을 잘 전달해 활용하는 경우(3)가 개인적으로 많았다. 

 

많은 양의 데이터를 insert 혹은 update하는 경우에는 pl/sql에서 cursor 의 활용도가 매우 높아질것이다. 

 

dummy 데이터를 넣을 때도 pl/sql을 사용할 수 도 있을 것이다.

 

다음에는 프로시저에서 custom data type을 다루는 방법에 대해 설명할 것이다.