저장 프로시저 (Stored Procedure, SP)

저장 프로시저 (Stored Procedure, SP)

"저장 프로시저 또는 스토어드 프로시저(stored procedure)는
일련의 쿼리를 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합이다."

_위키백과

 

"데이터베이스에 대한 일련의 작업을 정리한 절차를
관계형 데이터베이스 관리 시스템에 저장한(지속성) 것으로,
영구저장모듈(Persistent Storage Module)이라고도 불린다."

 

장점 -

네트워크 부하 감소

처리 시간 감소

데이터 무결성 유지

편리한 유지보수 및 개발 업무와의 구분

절차적 기능 구현 가능

단점 -

불편한 유지보수 ( 개발적 역량으로 인해 )

DB 확장 어려움

 

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

CREATE OR REPLACE PROCEDURE 프로시저이름 

IS

[

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

변수이름 데이터타입;

변수이름 데이터타입;

..

]

BEGIN

 기능 구현;

END;

 

 

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

 

CREATE OR REPLACE PROCEDURE 프로시저이름(

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

)

IS

[

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

..

]

BEGIN

기능 구현;

END;

 

3. 프로시져 호출방법

 

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

 

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

 

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

 

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

 

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

 

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

 

CREATE OR REPLACE PROCEDURE 프로시저이름 (

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

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

)

IS

[

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

..

]

BEGIN

기능 구현;

END;

 

-----

프로시저 문법

DECLARE

> 변수는 선언 시에 기본값을 지정할 수 있습니다. 만약 지정하지 않는다면 그 변수의 값은 NULL 값을 갖게 됩니다.

 

v_ :  내부 변수로서 프로시저 내에서 사용할 변수에 대해 주로 v라는 표현을 붙여 사용한다.

p_ :  위에서 cmd 창에서 입력을 받던 대체 변수들에 대해  p라는 표현을 붙였던 것을 기억할 것이다.

상수(constant)

> 상수는 변수와 다르게 한번 값이 정해지면 변경할 수 없다는 차이가 있습니다. 상수를 선언하는 방식은 변수의 선언처럼 DECLARE 구문 뒤에서 이루어지며, 상수 이름과 타입 사이에 CONSTANT라고 지정하면 됩니다.

CALL

> 저장 프로시저 호출 

Call 문의 저장 프로시저에서 반환되는 유일한 지원되는 형식은 행 집합입니다. 행 집합 직렬화는 XML for Analysis에 의해 정의됩니다. Call 문의 저장 프로시저가 다른 형식을 반환하는 경우 무시되고 호출 애플리케이션에 XML로 반환되지 않습니다 - https://docs.microsoft.com/

 

EXCEPTION 

> 예외처리, 프로시저가 예외처리가 없는 경우 프로시저에서 강제적으로 종료되고 오류 메시지가 출력된다고 한다.

 

GET DIAGNOSTICS

> GET DIAGNOSTICS 명령문은 실행된 이전 SQL문에 대한 정보를 얻습니다.

 

CURRENT 또는 STACKED액세스할 진단 영역을 지정합니다.

• CURRENT 첫 번째 진단 영역에 액세스하도록 지정합니다. 실행되었고 GET DIAGNOSTICS 명령문이 아닌 이전 SQL문에 해당합니다. 이 값이 기본값입니다.
• STACKED 두 번째 진단 영역에 액세스하도록 지정합니다. 두 번째 진단 영역은 핸들러 내에서만 사용 가능합니다. 핸들러가 입력되기 전에 실행되었고 GET DIAGNOSTICS 명령문이 아닌 이전 SQL문에 해당합니다. GET DIAGNOSTICS 명령문이 핸들러 내 첫 번째 명령문인 경우, 첫 번째 진단 영역과 두 번째 진단 영역은 동일한 진단 정보를 포함합니다.

RAISE NOTICE

> 해당 변수들을 원하는 문자열로 포맷으로 구성한 것을 PL/pgSQL의 실행환경에서 표시하는 코드입니다.

 

 

-----

프로시저 삭제

DROP PROCEDURE [프로시저 명] ;

 

 

출처:

http://www.gisdeveloper.co.kr/?p=4573 

https://www.ibm.com/docs/ko/i/7.3?topic=statements-get-diagnostics 

https://goddaehee.tistory.com/163 

https://siahn95.tistory.com/entry/DBMSSQL-%EC%A0%80%EC%9E%A5-%ED%94%84%EB%A1%9C%EC%8B%9C%EC%A0%80Stored-Procedure%EB%9E%80