[ORACLE] ( PL/SQL)오라클 프로시저(Procedure) 생성 및 실행

프로시저(Procedure) 란?

오라클에서의 프로시저는 PL/SQL을 통해 만들어진다. 자주 사용하는 SQL을 프로시저로 만든 뒤 필요 할때마다 호출,사용하여 작업 효율을 늘릴 수 있다. 함수는 특정 연산을 수행한 뒤 결과 값을 반환하지만 프로시저는 특정한 로직을 처리하기만 하고 결과 값은 반환하지 않는 서브 프로그램이다.

- 1. 파라미터 없는 형태

프로시저 생성

CREATE OR REPLACE PROCEDURE RUN_PROC
IS
	RETURN_MESSAGE VARCHAR2(100) := 'http://devmg.tistory.com'; 

BEGIN
	dbms_output.put_line(RETURN_MESSAGE); 
END RUN_PROC;

프로시저 실행

EXEC RUN_PROC;

--결과
http://devmg.tistory.com

 

- 2. 파라미터 있는 형태

프로시저 생성

CREATE OR REPLACE PROCEDURE RUN_PROC
(
   P_DEPARTMENT IN VARCHAR2,    -- 변수명 IN 데이터 타입, 인수에 크기는 주지 않는다.
   P_STUDENT_CNT IN NUMBER		-- 변수명 IN 데이터 타입
)
IS
-- 기능구현부
	P_UNIVERSITY VARCHAR2(100)  := '카이스트';

BEGIN
	dbms_output.put_line('인서트를 실행합니다.');
	INSERT INTO UNIVERSITY_TB (UNIVERSITY, DEPARTMENT, STUDENT_CNT)
	VALUES (P_UNIVERSITY, P_DEPARTMENT, P_STUDENT_CNT);
	COMMIT;

END RUN_PROC;

테이블에 인서트를 하는 프로시저를 생성했다. 파라미터로 받을 값을 프로시저명 뒤에 명시해주면 되고 변수를 선언할 일이 있으면 IS뒤에다가 써주면 된다. 동작은 BEGIN뒤에 명시한다. 이 프로시저를 통해서 UNIVERSITY_TB 테이블에 데이터를 넣어보도록 하자. 위 프로시저는 예시이며 SELECT를 하는 프로시저든, UPDATE를 원하는 동작을 하는 프로시저를 만들면 된다.

프로시저 실행

EXEC RUN_PROC('전자과',200); --프로시저를 실행시킬때에는 EXEC [프로시저명] 이라고 명시한다.


-- 결과
인서트를 실행합니다.


-- 테이블 결과
UNIVERSITY 		DEPARTMENT		STUDENT_CNT
	카이스트			전자과			200

 

저장돤 프로시저 찾기

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

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

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

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

OUT변수 활용하기

프로시저 생성

CREATE OR REPLACE PROCEDURE P_OUT_SAMPLE
( 
	P_MESSAGE OUT VARCHAR2 
) 
IS 
BEGIN P_MESSAGE := '아웃출력'; 
DBMS_OUTPUT.PUT_LINE('호출 완료'); 
END; 

프로시저 실행

 

--out이 있는 프로시저 호출방법 
--DECLARE 로 선언되변수는 일회용 
DECLARE 
OUT_MESSAGE VARCHAR2(2000); 

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


//결과
호출 완료
아웃출력

 

갓대희님의 프로시저 예제

CREATE OR replace PROCEDURE "SAMPLE_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 := '1000'; 
    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;

 

본문의 출처 https://coding-factory.tistory.com/453?category=758273, goddaehee.tistory.com/163