PL/SQL 기본_4 - 프로시저

저장 프로시저

• 정의
  • Transact-SQL 문장의 집합
• 용도
  • 어떠한 동작을 절차적 일괄처리 작업하는 데 사용
  • SQL Server에서 사용하는 프로그래밍 기능
• 특징
  • SQL Server의 성능 향상(첫 실행 시 컴파일, 재컴파일 안 함)
  • 모듈식 프로그래밍 가능
  • 보안 강화(저장 프로시저마다 권한 할당 가능)
  • 네트워크 전송량 감소(긴 쿼리 문장의 단순화)
• 장점
  • 빠르다
  • 파라미터 사용 가능
  • 여러 애플리케이션과 공유 가능
• 기본 문법
  • (인수 없는)프로시저 생성 방법

	CREATE OR REPLACE PROCEDURE 프로시저이름 
	IS
	[
	변수이름 데이터타입;  -- 프로시저내에서 사용할 변수선언
	변수이름 데이터타입;
	변수이름 데이터타입;
	..
	]
	BEGIN
	 기능 구현;
	END;

 

 

• EX) 인수없는 프로시저

	SET SERVEROUTPUT ON; 
	CREATE OR REPLACE PROCEDURE p_test 
	IS 
		I_MESSAGE VARCHAR2(100) := 'https://pdache.tistory.com'; 
	BEGIN 
		dbms_output.put_line(I_MESSAGE); 
	END; 
	EXEC p_test; --프로시저 호출

 

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

	CREATE OR REPLACE PROCEDURE 프로시저이름(
	변수이름 IN 데이터타입, 변수이름 IN 데이터타입, .... --IN 생략가능
	)
	IS
	[
	변수이름 데이터타입;  -- 프로시저내에서 사용할 변수선언
	..
	]
	BEGIN
	기능 구현;
	END;

 

• EX) 인수 있는 프로시저

	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('Pdache');

 

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

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

        변수이름 IN VARCHAR2;  -- 인수선언할 때 byte 수 지정안함(인수는 크기를 주지 않음)
        변수이름 IN 테이블이름.컬럼명%TYPE;
        변수이름 IN 테이블이름.컬럼명%TYPE:=값;
        변수이름 IN 테이블이름.컬럼명%TYPE DEFAULT 값;

 

• OUT => 호출자에게 제공
  • 프로시저 실행 시점에 OUT 매개변수를 변수 형태로 전달하고, 프로시저 실행부에서 이 매개변수에 특정 값을 할당

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

 

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

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

 

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

 

 

• 프로시저 호출 방법

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

 

• 저장된 프로시저 찾기
  • 작성된 프로시저를 찾기 위해 데이터 사전을 이용할 수 있다.
  • 데이터 사전은 대문자로 값을 저장하기 때문에 대문자로 검색한다.

SELECT * FROM user_objects WHERE object_type = 'PROCEDURE';
SELECT * FROM user_source WHERE name = '프로시저명';

 

• 프로시저 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 := 'Pdache';
	    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;