ABOUT ME

-

Today
-
Yesterday
-
Total
-
  • [PL/SQL] 함수와 프로시저
    IT, 프로그래밍/Database (Oracle) 2019. 1. 16. 13:58






    PL/SQL의 대표적인 부 프로그램에는 함수(Function)과 프로시져(Procedure)가 있다.

     





    함수(Function)




    함수 생성



    CREATE OR REPLACE FUNCTION 함수 이름 (매개변수1, 매개변수2....)

    RETURN 데이터 타입;

    IS[AS]

    변수, 상수 선언..

    BEGIN

    실행부


    RETURN 반환값

    [EXCEPTION 예외처리부]

    END [함수 이];




    함수는 기본적으로 SQL문 안에서 사용해야 한다.

    오라클에서 기본적으로 제공해주는 함수(concat 이라던가하는..)는 Built-in 함수라고 부른다. 보통 PL/SQL에서 함수란 사용자가 직접 정의한 함수를 말한다.



    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    CREATE OR REPLACE FUNCTION fu_sum_gugu( vn_row NUMBER )
    RETURN NUMBER
    IS
      vn_gugu_sum NUMBER := 0;
    BEGIN
      FOR i in 1..9
        LOOP
         vn_gugu_sum := vn_gugu_sum + (vn_row * i);
        END LOOP;
        RETURN vn_gugu_sum;
    END;
    cs


    예시는 구구단에서 단 번호를 넘겨주면 해당 단의 합계를 반환하는 함수이다.

    (매개변수를 2로 넘겨주면 2*1 + 2*2 .... + 2*9 의 결과를 반환한다)


    호출은 함수명으로 가능하다. SQL문 안에서 사용한다.


    1
    2
    SELECT fu_sum_gugu(1FROM dual;
     
    cs



    매개변수가 없는 함수는 괄호 안을 비우고 호출하면 된다.





    프로시저 (Procedure, Stored Procedure)




    CREATE OR REPLACE PROCEDURE 프로시저 이름

    ( 매개변수명1[ IN | OUT | IN OUT ] 데이터타입[:= 디폴트값],

      매개변수명2[ IN | OUT | IN OUT ] 데이터타입[:= 디폴트값], ... )

    IS[AS]

    변수, 상수 등 선언

    BEGIN

    실행


    [EXCEPTION 예외처리부]


    END [프로시저 이름]





    여기서 IN, OUT, IN OUT에 대하여 알아보자


    이것들은 매개변수의 스코프를 말하는데,


    IN은 매개변수가 프로시저 내부로 전달되면 연산에만 사용되고 더이상 반환되지 않는다.

    OUT은 프로시저 내부로 전달되지는 않지만 프로시저가 해당 변수의 참조를 통해 호출한 곳으로 값을 전달할 수 있다.

    IN OUT은 프로시저 내부로 전달하여 연산에 사용하고 호출한 곳에서 참조를 통해 값을 전달받을 수 있다.


    그림을 통해 좀 더 쉽게 이해 해 보자.





    전달받은 매개변수에 10을 더하는 프로시저가 있다고 하자.


    변수 A,B,C에 각자 IN, OUT, IN OUT 을 선언하고 프로시저에 하나씩 순서대로 전달하였다. 

    (exec sum_ten_proc(a), exec sum_ten_proc(b), exec sum_ten_proc(c).... )


    프로시저 수행 완료후에 다시 해당 변수를 참조했다고 생각하면,


    A는 프로시저로 전달되어 10을 더하여 내부적으로는 40이 되어 끝났지만, 호출한 곳에서는 다시 전달 받을 수 없다.

    B는 원래의 값이 프로시저로 전달되지 않고, 프로시저가 수행한 결과만 B에 저장된다. (여기서는 매개변수를 전달받지 못했으므로 0 + 10으로 계산된다)

    C는 원래 값인 50을 건네주고, 연산 결과 50 + 10 = 60이 되어 다시 호출한 곳에서 C를 60으로 사용할 수 있다.



    책에 있는 괜찮은 예제로 확인해 보자.




    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    CREATE OR REPLACE PROCEDURE my_parameter_test_proc(
        p_var1 VARCHAR2,
        p_var2 OUT VARCHAR2,
        p_var3 IN OUT VARCHAR2 )
    IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('p_var1 value = ' || p_var1);
        DBMS_OUTPUT.PUT_LINE('p_var2 value = ' || p_var2);
        DBMS_OUTPUT.PUT_LINE('p_var3 value = ' || p_var3);
     
        p_var2 := 'B2';
        p_var3 := 'C2';
     
    END;
    cs



    매개변수를 전달받는 프로시저를 하나 작성하자. 건네받은 매개변수를 모두 찍은후에, p_var2와 p_var3를 다시 초기화 한다.


    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    DECLARE
        v_var1 VARCHAR2(10) := 'A';
        v_var2 VARCHAR2(10) := 'B';
        v_var3 VARCHAR2(10) := 'C';
    BEGIN
        my_parameter_test_proc (v_var1, v_var2, v_var3);
        DBMS_OUTPUT.PUT_LINE('-------------------');
        DBMS_OUTPUT.PUT_LINE('v_var1 = ' || v_var1);
        DBMS_OUTPUT.PUT_LINE('v_var2 = ' || v_var2);
        DBMS_OUTPUT.PUT_LINE('v_var3 = ' || v_var3);
    END;
    cs


    프로시저를 컴파일 한 후에 익명블록을 통해 매개변수를 전달한다.



    나온 결과를 확인 해 보자




    p_var1 value = A

    p_var2 value = 

    p_var3 value = C

    -------------------

    v_var1 = A

    v_var2 = B2

    v_var3 = C2







    포인터 넘기는 것 처럼 변수를 Call by Reference 방식으로 넘겨주는 것 처럼 생각하면 될 듯 하다.


    마지막으로 정리해보자.


    IN = 프로시저 내부에서 참조만 가능, 값 할당 불가, 상수나 리터럴 문자를 전달할 수 있다. 매개변수 타입을 지정하지 않으면 디폴트로 IN으로 설정된다.

    OUT = 값 할당 가능. 전달받기 불가

    IN OUT = 참조와 값 할당 모두 가능.


    ** OUT, IN OUT 매개 변수에는 디폴트값 설정 불가, 반드시 변수에 값을 담아서 전달해야 함.




    * 매개변수의 선언과 동시에 초기화


    1
    2
    3
    4
    5
    6
    CREATE OR REPLACE PROCEDURE my_new_job_proc
    ( p_job_id IN JOBS.JOB_ID%TYPE,
      p_job_title IN JOBS.JOB_TITLE%TYPE,
      p_min_sal IN JOBS.MIN_SALARY%TYPE ,
      p_max_sal IN JOBS.MAX_SALARY%TYPE  )
      ...
    cs


    총 4개의 매개변수를 전달받는 프로시저가 있다고 생각 해 보자.


    1
    2
    EXECUTE my_new_job_proc ('SM_JOB1''SAMPLE JOB1');
     
    cs


    이런식으로 해당 프로시저에 2개의 값만 전달한다면, 당연히 컴파일 에러를 띄우고 생성되지 않는다.


    이때, 아래와 같이 선언과 동시에 초기화 시키는 매개변수가 존재한다면, 그 매개변수를 전달하지 않아도 프로시저 생성이 수행된다.



    1
    2
    3
    4
    5
    6
    CREATE OR REPLACE PROCEDURE my_new_job_proc
    ( p_job_id IN JOBS.JOB_ID%TYPE,
      p_job_title IN JOBS.JOB_TITLE%TYPE,
      p_min_sal IN JOBS.MIN_SALARY%TYPE := 10,
      p_max_sal IN JOBS.MAX_SALARY%TYPE := 100, )
      ...
    cs



    * 매개변수 전달 시 초기화



    1
    2
    3
    EXECUTE my_new_job_proc ( p_job_id => SM_JOB1', p_job_title=>  'SAMPLE JOB1', 
         p_min_sal => 2000, p_max_sal => 7000);
    cs


     => 이라는 기호를 통해 매개변수를 전달 시에 값을 초기화 할 수 있다. 






    프로시저 실행


    SELECT절에서는 사용할 수 없고 EXEC 혹은 EXECUTE 명령어를 사용하여 실행한다.


    EXEC (혹은 EXECUTE) 프로시저명 (매개변수1 값... 매개변수2 값...)

     



    RETURN문


    RETURN을 사용하면 프로시저의 실행을 해당 시점에서 종료시킬 수 있다.

    조건문으로 분기시켜 로직을 수행할때 자주 사용한다.


    1
    2
    3
    4
    IF p_min_sal < 1000 THEN
      DBMS_OUTPUT.PUT_LINE('최소 급여값은 1000원 이상이어야 합니다. 수행을 종료합니다.');
      RETURN;
    END IF;
    cs




Designed by Tistory.