본문으로 바로가기
반응형

[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;


반응형

댓글을 달아 주세요

  1. 초급자 2020.06.17 17:46

    안녕하세요 pl/sql 기초배우고 있는 학생입니다..!
    다름이 아니라 제가 오라클이 처음이여서 잘 이해가 안가는데 sql에서 select문 등등 문법사용해서 할 수 있는데 pl/sql을 왜 사용해야하는지 이해가 잘 가지않습니다ㅠㅜ혹시 그 이유를 쉽게 알려주실 수 있을까요..?
    그리고 제가 pl/sql이 sql에서 테이블 만들면 pl/sql에서는 함수..?를 만드는 예를들어 c언어에서 쓰는 함수같은 기능인건지 여쭤보고싶어 이렇게 답글 남깁니다..!

    • Favicon of https://goddaehee.tistory.com BlogIcon 갓대희 2020.06.17 20:43 신고

      요즘 MSA구조로 아키텍쳐가 발전하고 있고 등등의 이유로 Procedure를 잘 안쓰는 경우도 있지만.. 그럼 전통적인 장점만 말씀 드려볼게요.

      1. 네트워크 트래픽, 즉 비용을 절감할 수 있습니다.

      web 개발을 하다보면 매우 복잡한 프로그램을 많이 작성하게되는데, 아주 간단한 예를들어 문의주신분께서 회원가입을 한다고 했을때 테이터를 저장하는 경우를 예를 들어 볼게요.

      저장해야할 테이블이 2개(회원정보 테이블, 주소테이블)라고 했을때
      말씀하신 단순 쿼리로는 서버에서 DB Insert문을 2번 호출 하지만, Pl/SQL은 DB쪽 호출은 1번만 하면 됩니다. 즉 네트워크 비용을 절감할 수 있는데, 너무 단순한 예를 들었지만 더 복잡한 프로그램일 수록 네트워크 비용 절감 효과는 클 것 같네요.

      2. 성능면에서도 뛰어날 수 있습니다.
      이부분은 어렵게 표현하기보다 간단히 말씀드리자면 Database는 DB실행계획이라는 걸 수립후 쿼리 수행을 하는데 프로시저같은 경우엔 이런 작업을 미리 ? 해둔다고 보시면 되겠네요.

      - 이외에도 단순 SQL 구문으로는 처리하기 힘든 IF / ELSE, For 문등을 PL / SQL로 처리할수 있는 등의 기능적인 장점들도 많은데

      무조건 적으로 PL/SQL이 좋다라고 생각하시기 보다는, 적절한 상황과 개발 환경에서는 장점으로 작용할 수 있다라고 생각하시면 좋을 것 같습니다.

  2. 질문합니당 2020.09.16 17:07

    좋은 글 잘 봤습니다.
    윗 답댓글에 질문드리는데요.
    왜 MSA 구조 발전으로 프로시저를 잘 안 쓰는 건가요??

    • Favicon of https://goddaehee.tistory.com BlogIcon 갓대희 2020.09.17 10:43 신고

      MSA때문에 프로시저를 안쓴다 라기보단

      예전엔 한 프로시저안에 다양한 업무 내용이 복잡하게 엮여 있는 경우가 많았죠.

      이제는 각 파트별 유닛별 셀별 업무가 분업화 되다보니

      그런 복잡한 프로시저를 쓸일이 없어지고, 파트별 셀별 업무를 하다보니
      단순한 쿼리로도 충분히 해결이 가능해지는 경우 구지 프로시저를 쓸 필요가 없어진 경우가 많이 생긴다고 하네요.

  3. 엄xx 2021.06.23 17:29

    혹시 죄송한데 혹시 3사단에 천안에 있는 대학교 나오셨나요?