[Oracle] PL/SQL 기초4 - 프로시저(Procedure)

[Oracle] PL/SQL 기초4 - 프로시저 (Procedure)

안녕하세요. 갓대희 입니다. 이번 포스팅은 [ Stored Procedure ] 입니다. : ) 

저장 프로시저 (STORED PROCEDURE)

[정의]

 - Transact-SQL 문장의 집합

[용도]

- 어떠한 동작을 절차적 일괄처리 작업하는데 사용

- SQL Server에서 사용하는 프로그래밍 기능

[특징]

- SQL Server의 성능향상 (첫 실행시 컴파일, 재컴파일 안함)

  오라클은 SQL 문 수행시 다음과 같은 행동을 한다. 

  1. 먼저 같은 실행 계획이 공유 풀에 있는지 확인한다.

  (공유풀은 다음 글에서 확인 - http://goddaehee.tistory.com/64)

  

  2. 이후 SQL문 문법 검사나 DATA DICTIONARY를 검사하여 

     해당 사용자 소유의 테이블인지 여부를 확인한다.

  3. 실행 권한이 있는지 확인한다.

  4. 이상이 없으면 실행 계획을 작성, 적용한다.

- 모듈식 프로그래밍 가능

- 보안강화 (저장프로시저 마다의 권한할당 가능)

- 네트워크 전송량 감소 (긴 쿼리문장의 단순화)

[장점]

- 빠르다

- 파라미터를 사용할 수 있다

- 여러 어플리케이션과 공유할수 있다

[기본 문법]

1. (인수없는)프로시저 생성방법

CREATE OR REPLACE PROCEDURE 프로시저이름 

IS

[

변수이름 데이터타입;  -- 프로시저내에서 사용할 변수선언

변수이름 데이터타입;

변수이름 데이터타입;

..

]

BEGIN

 기능 구현;

END;

ex1) 인수없는 프로시저

SET SERVEROUTPUT ON;

CREATE OR REPLACE PROCEDURE p_test
IS
	I_MESSAGE VARCHAR2(100) := 'http://goddaehee.tistory.com';
BEGIN 
	dbms_output.put_line(I_MESSAGE);
END;

EXEC p_test; --프로시저 호출

2. (인수있는) 프로시저 생성방법

CREATE OR REPLACE PROCEDURE 프로시저이름(

변수이름 IN 데이터타입, 변수이름 IN 데이터타입, .... --IN 생략가능

)

IS

[

변수이름 데이터타입;  -- 프로시저내에서 사용할 변수선언

..

]

BEGIN

기능 구현;

END;

ex2) 인수있는 프로시저

CREATE OR REPLACE PROCEDURE p2_test( p_name IN VARCHAR2  )
IS
BEGIN
   dbms_output.put_line(p_name||' 님 안녕하세요?');
END;

EXEC p2_test; --프로시저 호출 (오류발생 )
    -- (프로시저에 in이 있으면서 기본값이 없기 때문에 반드시 인수값 필요함)
EXEC p2_test('goddaehee');

※인수의 타입 선언부분정리 

1. IN => 내부 프로그램에 제공

변수이름 IN VARCHAR2;  --인수선언할때 byte수 지정안함 (인수는 크기를 주지 않습니다.)

변수이름 IN 테이블이름.컬럼명%TYPE;

변수이름 IN 테이블이름.컴럼명%TYPE := 값;

변수이름 IN 테이블이름.컬럼명%TYPE DEFAULT 값;

2. OUT => 호출자에게 제공

(프로시저 실행 시점에 OUT 매개변수를 변수 형태로 전달하고, 프로시저 실행부에서 이 매개변수에 특정 값을 할당, )

변수이름 IN VARCHAR2;

변수이름 IN 테이블이름.컬럼명%TYPE;

3. IN OUT => 입력과 동시에 출력용으로 사용할 수 있다.

변수이름 IN VARCHAR2;

변수이름 IN 테이블이름.컬럼명%TYPE;

암시적, 명시적 커서 선언 방법 자세한 설명 (클릭)

 => http://goddaehee.tistory.com/117 

※ IN OUT, OUT 모드로 선언된 파라미터에는 DEFAULT를 적용할 수 없다.

3. 프로시져 호출방법

 - EXEC 프로시져이름 ;  -- 인수없는 경우 호출

 - EXEC 프로시져이름(값, 값,....); -- 인수있는 경우 호출;

4. 저장된 프로시저 찾기

 - 작성된 프로시저를 찾기 위해 데이터 사전을 이용할 수 있다.

 ※ 주의 : 데이터 사전은 대문자로 값을 저장하기 때문에 대문자로 검색하자.

1. SELECT * FROM user_objects WHERE object_type = 'PROCEDURE';

2. SELECT * FROM user_source WHERE name = '프로시저명';

  

 

5 프로시저 output 매개변수 사용하기

 - 웹개발 하면서 프로시저 결과 핸들링을 위해 많이 사용 했다.

 - 프로시저를 실행하여 특정결과값을 out변수에 저장하여 보낸다.(프로시저에서 실행환경으로 값을 반환)

 - out있는 프로시저 작성방법

CREATE OR REPLACE PROCEDURE 프로시저이름 (

변수이름 IN 데이터타입, 변수이름 IN 데이터타입, .... --in 생략가능

변수이름 OUT 데이터타입, 변수이름 OUT 데이터타입 ... --프로시저를 호출하는곳으로 값을 보낸다.

)

IS

[

변수이름 데이터타입;  -- 프로시저내에서 사용할 변수선언

..

]

BEGIN

기능 구현;

END;

 

ex) 이름을 output 해주는 프로시저 작성

CREATE OR REPLACE PROCEDURE p_outTest(
	p_NAME OUT VARCHAR2
)
IS
BEGIN
	p_NAME := 'goddaehee';
    DBMS_OUTPUT.PUT_LINE('호출 완료');
END;
--out이 있는 프로시저 호출방법
--DECLARE 로 선언되변수는 일회용

DECLARE
OUT_MSG VARCHAR2(2000);

BEGIN
p_outTest(OUT_MSG);  -- 프로시저를 실행 한후에 out을 받을 변수지정
dbms_output.put_line(OUT_MSG); -- 출력하기
END;

ex) 업무에 사용 하던 프로시저 쌤플 (오류처리는 다음 포스팅에 ㅠ ㅠ)

CREATE OR REPLACE PROCEDURE "PWPNT_SET_MEMBER" (
    p_mbr_no IN VARCHAR2
   ,p_modr_no IN VARCHAR2
   ,p_mod_pgm_url IN VARCHAR2
   ,o_ret_cd OUT VARCHAR2
   ,o_ret_msg OUT VARCHAR2
) IS
    v_count NUMBER := 0;
    v_sysdate DATE := SYSDATE;
    USER_SQL_EXCEPTION EXCEPTION;
BEGIN

    DBMS_OUTPUT.PUT_LINE('p_mbr_no '||p_mbr_no);
    DBMS_OUTPUT.PUT_LINE('p_modr_no '||p_modr_no);
    DBMS_OUTPUT.PUT_LINE('p_mod_pgm_url '||p_mod_pgm_url);
    
    IF p_mbr_no IS NULL OR p_modr_no IS NULL OR p_mod_pgm_url IS NULL THEN
       o_ret_cd := '2000';
       o_ret_msg := '파라미터를 입력하세요';
       RAISE USER_SQL_EXCEPTION;
    END IF;

    BEGIN
        SELECT COUNT(*)
        INTO   v_count
        FROM   TMBR_MBR_BAS
        WHERE  MBR_NO = p_mbr_no;
    EXCEPTION
        WHEN OTHERS THEN
            o_ret_cd := '2020';
            o_ret_msg := '회원이 존재하지 않습니다';
            RAISE USER_SQL_EXCEPTION;
    END;
    
    BEGIN 
		--실제 본문 PROCEDURE
	END;
	
    o_ret_cd := '1004';
    o_ret_msg := '정상적으로 처리 되었습니다';
EXCEPTION
    WHEN OTHERS THEN
    NULL;
END;