Oracle SQL 요약정리 - 프로시져(PROCEDURE)생성 및 실행방법, 프로시져 장단점, 함수(FUNCTION)생성 및 실행방법 / 프로시저와 함수의 차이점

안녕하세요.
https://blog.naver.com/sysysy0302 여니입니다 :)

 

/*
    <PROCEDURE>
    PL/SQL구문을 "저장"해서 이용하게 하는 객체
    필요할때마다 내가 작성한 PL/SQL문을 편하게 호출 가능하다.
    [표현식]
    CREATE [OR REPLACE] PROCEDURE 프로시저명[(매개변수)]
    IS 
    BEGIN
        실행부분
    END;
    
    *프로시져 실행방법*
    EXEC 프로시저명;
*/

/*
    프로시저의 장점
    1. 처리속도가 빠름
    2. 대용량 자료처리시 유리함.
    EX) DB에서 대용량의 데이터를 SELECT문으로 받아와서 자바에서 처리하는 CASE
                                VS
        DB에서 대용량의 데이터를 SELECT한 후 자바로 넘기지 않고 직접처리하는 CASE
        
        DB에서 처리하는게 성능이 좋다(데이터를 넘길때마다 자원이 소비됨)
        
   프로시져 단점
   1. DB자원을 직접 사용하기 때문에 DB에 부하를 주게됨.
 ★ 2. 관리적 측면에서 자바소스코드, 오라클소스코드 두개를 동시에 관리하기가 어렵다. --;유지보수 어렵다
  
   정리)
   한번에 처리되는 데이터량이 많고, 성능을 요구하는 처리는 대체로 자바보다 DB상에서 처리하는 것이 성능측면에서
   나을것이고 소스관리(유지보수) 측면에서는 자바에서 작업하는게 더 좋다.
*/

ex)
-- EMPLOYEE테이블을 복사한 COPY테이블 생성
CREATE TABLE PRO_TEST
AS SELECT * FROM EMPLOYEE;

SELECT * FROM PRO_TEST;

-- 프로시저 생성하기
CREATE PROCEDURE DEL_DATA 
IS
BEGIN
     DELETE FROM PRO_TEST;
     COMMIT;
END;
/

-- 생성된 프로시져 조회
SELECT * FROM USER_PROCEDURES;

-- 프로시져 실행
EXEC DEL_DATA;

SELECT * FROM PRO_TEST;

---

-- 매개변수 있는 프로시져 만들기
-- IN  : 프로시져 실행시 필요한 값을 받는 변수 (일반적인 매개변수와 동일한 개념) 
-- OUT : 호출한곳으로 되돌려주는 변수 (결과값)

ex)
CREATE OR REPLACE PROCEDURE PRO_SELECT_EMP(
                                                                                                     V_EMP_ID IN EMPLOYEE.EMP_ID%TYPE,
                                                                                                      V_EMP_NAME OUT EMPLOYEE.EMP_NAME%TYPE,
                                                                                                      V_SALARY OUT EMPLOYEE.SALARY%TYPE,
                                                                                                      V_BONUS OUT EMPLOYEE.BONUS%TYPE)
IS 
BEGIN
    SELECT EMP_NAME, SALARY, BONUS
        INTO   V_EMP_NAME, V_SALARY, V_BONUS
      FROM   EMPLOYEE
    WHERE  EMP_ID = V_EMP_ID;
END;
/

-- 매개변수가 있는 프로시져 실행하기
VAR EMP_NAME VARCHAR2(20);
VAR SALARY NUMBER;
VAR BONUS NUMBER;

EXEC PRO_SELECT_EMP(200, :EMP_NAME, :SALARY , :BONUS);

PRINT EMP_NAME;
PRINT SALARY;
PRINT BONUS;

---

/*
    <FUNCTION>
    프로시져와 거의 유사하지만 실행결과를 반환받을 수 있음.
    
    FUNCTION 생성방법
    [표현식]
    CREATE [OR REPLACE] FUNCTION 펑션명[(매개변수)]
    RETURN 자료형
    IS 
    BEGIN
        실행부분
    END;
    
    펑션이름(인수)
*/

 

ex)
CREATE OR REPLACE FUNCTION MYFUNC(V_STR VARCHAR2)
RETURN VARCHAR2
IS
    RESULT VARCHAR2(1000);
BEGIN
    DBMS_OUTPUT.PUT_LINE(V_STR);
    RESULT := '*' || V_STR || '*';
    RETURN RESULT;
END;
/

SELECT MYFUNC('김소연') FROM DUAL;

ex)
-- EMP_ID를 전달받아서 연봉(보너스 포함연봉)을 계산해서 출력해주는 함수 만들기.
-- CALC_SALARY

CREATE OR REPLACE FUNCTION CALC_SALARY(V_EMP_ID IN EMPLOYEE.EMP_ID%TYPE)
RETURN NUMBER
IS 
V_EMP_NAME EMPLOYEE.EMP_NAME%TYPE;
V_SALARY EMPLOYEE.SALARY%TYPE;
V_BONUS EMPLOYEE.BONUS%TYPE;
RESULT NUMBER;
-- E EMPLOYEE%ROWTYPE;
BEGIN
--    SELECT *
--    INTO E
--    FROM EMPLOYEE
--    WHERE EMP_ID = V_EMP_ID;
    
    SELECT EMP_NAME, SALARY, BONUS
    INTO   V_EMP_NAME, V_SALARY, V_BONUS
    FROM   EMPLOYEE
    WHERE  EMP_ID = V_EMP_ID;
    
    DBMS_OUTPUT.PUT_LINE(V_EMP_ID);
    RESULT := (V_SALARY*12)+V_BONUS;
--  RESULT := (E.SALARY + E.SALARY* NVL(E.BONUS,0))*12;
    RETURN RESULT;
END;
/

SELECT CALC_SALARY(200) FROM DUAL;
-- SELECT EMP_ID, CALC_SALARY(EMP_ID) FROM EMPLOYEE;

--; -- 부분으로 바꾸어도 실행 OK
--; 함수는 프로시져와 다른 성질이다.

---

* 함수와 프로시저의 차이 *

 프로시저 또한 매개변수를 받아 여러 작업을 수행하여 값을 반환할 수 있고,
함수 또한 매개변수를 받아 여러 작업을 수행하여 값을 반환할 수 있습니다.
하지만 함수와 프로시저가 똑같은 것은 아닌데요, 프로시저와 함수의 차이점에 대해 알아보겠습니다.

1. 의미

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

함수
하나의 특별한 목적의 작업을 수행하기 위해 독립적으로 설계된 코드의 집합입니다.
즉, 함수가 여러 작업을 위한 기능이라면 프로시저는 작업을 정리한 절차입니다.
보통 로직을 도와주는 역할이며, 간단한 계산, 수치 등을 나타낼 때 사용합니다.

 

2. 매개변수

프로시저
매개변수를 입력,출력,입출력 형식으로 받을 수 있습니다.

함수
매개변수를 입력 형식으로만 받을 수 있습니다.

 

3. 반환값

프로시저
반환값을 가질 수도 있고 가지지 않을 수도 있습니다.

함수
반환값을 반드시 가져야 합니다.

 

4. 쿼리문 내에서 실행가능

프로시저
SELECT, WHERE 문 등에서 사용 불가합니다.

함수
SELECT, WHERE 문 등에서 사용이 가능합니다.

 

5. 처리 장소

프로시저
클라이언트(화면)에서 값을 건네받아 서버에서 작업을 한 뒤 클라이언트에게 전달합니다.
즉, 서버에서 실행이 되어 속도면에서 빠른 성능을 보여줍니다.

함수

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

 

+ 위 프로시저와 함수의 차이에 대한 내용은 해당 포스팅을 참고했습니다.

https://fomaios.tistory.com/entry/Oracle-%ED%95%A8%EC%88%98Function%EC%99%80-%ED%94%84%EB%A1%9C%EC%8B%9C%EC%A0%80Procedure-%EC%B0%A8%EC%9D%B4