맨위로가기

저장 프로시저

"오늘의AI위키"는 AI 기술로 일관성 있고 체계적인 최신 지식을 제공하는 혁신 플랫폼입니다.
"오늘의AI위키"의 AI를 통해 더욱 풍부하고 폭넓은 지식 경험을 누리세요.
목차

1. 개요

저장 프로시저는 데이터베이스에서 실행되는 일련의 SQL 문을 캡슐화한 데이터베이스 객체이다. 사용자 정의 함수(UDF)와 유사하지만, `CALL` 또는 `EXECUTE` 문을 사용하여 호출해야 한다는 차이점이 있다. 저장 프로시저는 SQL/PSM 표준을 통해 SQL 언어에 절차적 요소를 도입했으며, 다양한 프로그래밍 언어로 구현될 수 있다. 저장 프로시저는 성능 향상, 보안 강화, 코드 재사용성 및 유지보수 용이성 등의 장점을 가지지만, 벤더 종속성, 버전 관리의 어려움, 제한적인 디버깅 지원과 같은 단점도 존재한다.

더 읽어볼만한 페이지

  • 데이터 모델링 - 빌딩 정보 모델링
    빌딩 정보 모델링(BIM)은 건축물의 전 생애주기 동안 발생하는 정보를 디지털 모델로 통합 관리하는 프로세스이다.
  • 데이터 모델링 - 관계 논리
    관계 논리는 관계 모델 기반 데이터베이스 언어로서 관계 대수와 논리적으로 동일하며, 튜플 관계 논리와 도메인 관계 논리로 나뉘어 쿼리 최적화에 사용된다.
  • 데이터베이스 관리 시스템 - 트랜잭션 처리
    트랜잭션 처리는 데이터베이스 시스템에서 데이터의 일관성과 무결성을 보장하기 위한 기술이며, ACID 속성을 통해 데이터 정확성을 유지하고 롤백, 데드락 처리 등의 기술을 활용한다.
  • 데이터베이스 관리 시스템 - 데이터베이스 트랜잭션
    데이터베이스 트랜잭션은 데이터베이스의 무결성을 보장하기 위해 ACID 속성을 만족하며 일련의 연산을 묶은 단위로서, 데이터베이스 내에서 여러 쿼리를 실행하고 금융 거래 등에서 중요한 역할을 한다.
  • 데이터베이스 - 지식 베이스
    지식 베이스는 특정 주제 정보를 체계적으로 저장 및 관리하며 규칙 기반 추론으로 새로운 지식 도출에 활용되고, 웹 콘텐츠 관리 및 지식 관리 시스템으로 확장되어 온톨로지를 이용, 인공지능 기술과 결합하여 문제 해결책을 제시하고 경험을 통해 학습하는 시스템이다.
  • 데이터베이스 - 화이트리스트
    화이트리스트는 특정 대상만 허용하고 나머지는 차단하는 접근 제어 목록으로, 정보보안, 무역, 금융 등 다양한 분야에서 활용되지만, 목록 선정 기준의 불명확성, 사회적 문제점 등의 위험성으로 투명하고 엄격한 관리가 필요하다.
저장 프로시저

2. 역사 및 표준

데이터베이스 언어 표준 SQL에서는 SQL/PSM 기준으로 책정되어 있다. 각 벤더(제조사)는 정적, 동적 SQL에 커서 처리 및 제어 구문, 예외 처리 등을 포함한 사양의 확장 언어로 절차를 설명할 수 있는 DBMS를 제공하는 경우가 많다.[1] 또한 C 언어로 작성된 컴파일한 외부 모듈(공유 라이브러리) 및 Java 클래스 라이브러리에서 함수나 클래스 메소드를 호출하는 것으로 실현하는 ‘외부 프로시저’ 기능을 구현하는 것도 있다.[1]

저장 프로시저를 사용하면 다음과 같은 장점이 있다.[1]


  • 하나의 요청으로 여러 SQL 문을 실행할 수 있다. (네트워크 부하를 줄일 수 있다.)
  • 미리 구문 분석 및 내부 중간 코드로 변환을 끝내야 하므로 처리 시간이 줄어든다.
  • 데이터베이스 트리거와 결합하여 복잡한 규칙에 의한 데이터의 참조 무결성 유지가 가능하게 된다. 간단히 말하면 응용 프로그램 측 로직 없이도 데이터베이스의 데이터 앞뒤가 맞게 될 수 있다.
  • JAVA 등의 호스트 언어와 SQL 문장이 확실하게 분리되어 소스 코드의 전망이 좋아지고, 웹사이트 등 운용 중에도 저장 프로시저의 교체에 의한 수정이 가능하기 때문에 보수성이 뛰어나다.


저장 프로시저를 많이 사용하면 다음과 같은 단점이 있다.[1]

  • 데이터베이스 제품에 대해 설명하는 구문 규칙이 SQL/PSM 표준과의 호환성이 낮기 때문에 코드 자산으로의 재사용성이 나쁘다.
  • 비즈니스 로직의 일부로 사용하는 경우 업무의 사양 변경 시 외부 응용 프로그램과 함께 저장 프로시저의 정의를 변경할 필요가 있다. 이때 불필요한 수고와 변경 실수에 의한 장애를 발생시킬 가능성이 있다.

3. 구현

저장 프로시저는 사용자 정의 함수(UDF)와 유사하지만, UDF는 SQL 문 내에서 다른 식처럼 사용될 수 있는 반면 저장 프로시저는 `CALL` 문을 사용하여 호출해야 한다는 주요 차이점이 있다.[1]

```

CALL procedure(...)

또는

EXECUTE procedure(...)

```

저장 프로시저의 구현은 데이터베이스 시스템마다 다르다. 대부분의 주요 데이터베이스 공급업체는 어떤 형태로든 저장 프로시저를 지원하며, SQL, 자바, C, C++ 등 다양한 프로그래밍 언어로 구현할 수 있다. SQL 이외의 언어로 작성된 저장 프로시저는 SQL 문을 직접 실행할 수도 있고 그렇지 않을 수도 있다.

SQL:1999 및 SQL:2003 표준의 SQL/PSM 부분에서 SQL 언어에 절차적 요소가 도입되면서, SQL은 명령형 프로그래밍 언어가 되었다. 대부분의 데이터베이스 시스템은 SQL/PSM을 넘어서는 독점적이고 공급업체별 확장을 제공한다. 자바 저장 프로시저에 대한 표준 사양과 SQL/JRT도 존재한다.

3. 1. DBMS별 구현 언어

다양한 DBMS(데이터베이스 관리 시스템)들이 각기 다른 구현 언어를 사용하여 저장 프로시저를 지원한다. 주요 DBMS별 구현 언어는 다음과 같다.[1]

데이터베이스 시스템구현 언어
큐브리드자바
IBM Db2SQL PL (SQL/PSM 표준에 근접) 또는 자바
FirebirdPSQL (Fyracle은 Oracle의 PL/SQL 일부를 지원)
Informix자바
Interbase저장 프로시저 및 트리거 언어
Microsoft SQL ServerTransact-SQL 및 다양한 .NET Framework 언어
MySQL, MariaDB자체 저장 프로시저, SQL/PSM 표준을 엄격히 준수
NuoDBSQL 또는 자바
OpenLink VirtuosoVirtuoso SQL 프로시저(VSP);[2] 또한 자바, C 및 기타 프로그래밍 언어를 통해 확장 가능
OraclePL/SQL 또는 자바
PostgreSQLPL/pgSQL, PL/Tcl, PL/Perl 또는 PL/Python과 같은 자체 함수 언어 사용 가능[3]
SAP HANA[https://help.sap.com/viewer/de2486ee947e43e684d39702027f8a94/2.0.03/en-US/297af2926307446cbbfb1a8f96fec941.html SQLScript] 또는 R
SAP ASETransact-SQL
SAP SQL AnywhereT-SQL, Watcom SQL, 자바, 또는 C/C++
SQLite지원 안함


4. 장점

저장 프로시저는 데이터베이스 언어 표준인 SQL의 SQL/PSM 기준으로 책정되어 있으며, 여러 DBMS에서 확장된 기능을 제공한다. 저장 프로시저를 사용하면 다음과 같은 장점이 있다.[1]


  • 성능 향상: 네트워크 부하를 줄이고 처리 시간을 단축한다.
  • 보안 강화: SQL 주입 공격을 방지하고 접근 권한을 위임할 수 있다.
  • 코드 재사용성 및 유지보수 용이성: 비즈니스 로직을 캡슐화하고 데이터베이스 트리거와 결합하여 데이터 참조무결성을 유지할 수 있다.

4. 1. 성능 향상

저장 프로시저는 하나의 요청으로 여러 SQL 문을 실행할 수 있어 네트워크 부하를 줄일 수 있다. 또한, 미리 구문 분석 및 내부 중간 코드로 변환되어 처리 시간이 단축된다. 저장 프로시저는 데이터베이스 엔진 내에서 직접 실행되므로, 프로덕션 시스템에서 데이터베이스 서버에서 직접 실행되어 네트워크 트래픽을 줄일 수 있다는 장점이 있다.[1]

4. 2. 보안 강화

저장 프로시저는 SQL 주입 공격으로부터 보호하는 데 사용될 수 있다. 저장 프로시저 매개변수는 공격자가 SQL 명령을 삽입하더라도 데이터로 처리된다. 또한 일부 DBMS는 매개변수의 유형을 확인한다. 그러나 입력값을 사용하여 동적 SQL을 생성하는 저장 프로시저는 적절한 예방 조치를 취하지 않으면 여전히 SQL 주입에 취약하다.

많은 시스템에서 저장 프로시저는 해당 프로시저를 실행하는 사용자가 직접 가지고 있지 않은 데이터베이스에 대한 접근 권한을 부여받을 수 있다.

4. 3. 코드 재사용성 및 유지보수 용이성

저장 프로시저는 비즈니스 로직을 캡슐화하여 데이터베이스의 API로 포함할 수 있다. 이를 통해 데이터 관리를 단순화하고 클라이언트 프로그램에서 해당 로직을 별도로 작성할 필요성을 줄인다.[1] 이는 소스 코드의 가독성을 높이고, 운영 중에도 저장 프로시저를 교체하여 수정할 수 있어 유지보수성이 뛰어나다는 장점으로 이어진다.[1] 또한, 데이터베이스 트리거와 결합하여 복잡한 규칙에 따른 데이터의 참조무결성을 유지할 수 있다.[1]

5. 단점

저장 프로시저는 다음과 같은 단점을 가질 수 있다.


  • 코드 재사용성 문제: 데이터베이스 제품별 구문 규칙이 SQL/PSM 표준과 호환성이 낮아 코드 재사용성이 떨어진다.[2]
  • 업무 사양 변경 시 문제: 비즈니스 로직의 일부로 사용될 경우, 업무 사양 변경 시 외부 응용 프로그램과 함께 저장 프로시저 정의를 변경해야 하며, 이 과정에서 오류가 발생할 수 있다.[1]

5. 1. 벤더 종속성

저장 프로시저 언어는 주로 데이터베이스 벤더(제조사)별로 특화되어 있다. 따라서 데이터베이스 벤더를 변경하려면 일반적으로 기존 저장 프로시저를 다시 작성해야 한다.[1] 데이터베이스 제품에 대해 설명하는 구문 규칙이 SQL/PSM 표준과의 호환성이 낮기 때문에 코드 자산으로의 재사용성이 떨어진다.[2] 예를 들어, PostgreSQL의 pgpsql은 Microsoft의 T-SQL보다 더 많은 언어 기능(특히 확장 기능)을 가지고 있다.[3]

5. 2. 버전 관리의 어려움

저장 프로시저는 다른 코드보다 버전 관리 시스템에서 변경 사항을 추적하기 어렵다. 변경 사항을 프로젝트 기록에 포함하려면 스크립트로 재현해야 하며, 프로시저 간 차이점을 병합하고 정확하게 추적하는 것이 더 어려울 수 있다.[1]

5. 3. 제한적인 디버깅 지원

저장 프로시저는 애플리케이션 IDE의 컴파일 또는 빌드 단계에서 오류를 발견하기 어려운 경우가 많다. 저장 프로시저가 없거나 실수로 삭제된 경우에도 마찬가지이다.[1] 저장 프로시저를 작성하고 디버깅하는 도구는 다른 프로그래밍 언어만큼 좋지 않은 경우가 많지만, 이는 벤더와 언어에 따라 다르다.[2] 예를 들어, PL/SQL 및 T-SQL은 모두 전용 IDE와 디버거가 있으며, PL/PgSQL은 다양한 IDE에서 디버깅할 수 있다.[3]

5. 4. 비즈니스 로직 변경의 어려움

저장 프로시저를 비즈니스 로직의 일부로 사용하는 경우, 업무 사양이 변경되면 외부 응용 프로그램과 함께 저장 프로시저의 정의를 변경해야 한다.[1] 이때 불필요한 수고가 발생하고, 변경 실수로 인해 장애가 발생할 가능성이 있다.[1]

6. 다른 개념과의 비교

사용자 정의 함수(UDF)는 SQL 문 내에서 다른 식과 같이 사용할 수 있지만, 저장 프로시저는 `CALL` 문을 사용하여 호출해야 한다.[1] 준비된 명령문은 매개변수를 설정하여 나중에 다른 값을 사용할 수 있도록 하며, 서버에 저장되어 효율성을 높이고 SQL 삽입 공격으로부터 보호한다. 준비된 명령문은 더 간단하고 선언적이지만, 절차적 로직을 사용하지 않으며 변수에 대해 작동할 수 없고, DBMS 간에 더 광범위하게 재사용할 수 있다. 스마트 계약은 관계형 데이터베이스 관리 시스템(RDBMS)과 대조적으로 블록체인에 저장된 실행 가능한 코드이며, 저장 프로시저와 동일한 기능을 수행한다.

6. 1. 사용자 정의 함수 (UDF)

저장 프로시저는 사용자 정의 함수(UDF)와 유사하다. 주요 차이점은 사용자 정의 함수는 SQL 문 내에서 다른 식과 마찬가지로 사용할 수 있지만, 저장 프로시저는 `CALL` 문을 사용하여 호출해야 한다는 것이다.[1]

```

CALL procedure(...)

```

또는

```

EXECUTE procedure(...)

6. 2. 준비된 명령문 (Prepared Statements)

준비된 명령문은 일반적인 명령문이나 쿼리를 가져와 매개변수를 설정하여 나중에 다른 값을 사용할 수 있도록 한다. 저장 프로시저와 마찬가지로 서버에 저장되어 효율성을 높이고 SQL 삽입 공격으로부터 어느 정도 보호 기능을 제공한다. 준비된 명령문은 더 간단하고 선언적이지만, 일반적으로 절차적 로직을 사용하도록 작성되지 않으며 변수에 대해 작동할 수 없다. 간단한 인터페이스와 클라이언트 측 구현으로 인해 준비된 명령문은 DBMS 간에 더 광범위하게 재사용할 수 있다.

6. 3. 스마트 계약 (Smart Contracts)

스마트 계약은 관계형 데이터베이스 관리 시스템(RDBMS)과 대조적으로 블록체인에 저장된 실행 가능한 코드에 적용되는 용어이다. 공개 블록체인 네트워크의 실행 결과 합의 메커니즘은 원칙적으로 기존의 개인 또는 연합 데이터베이스와 다르지만, 저장 프로시저와 동일한 기능을 수행하며, 일반적으로 가치 거래의 의미를 내포한다.

7. 예제 (MySQL)

MySQL은 버전 5.0 이후 표준 SQL 규격 저장 프로시저를 지원한다.[1] 다음은 함수와 프로시저에서 동등한 처리를 하는 예시이다.


  • 함수 예제 1 (DB 개입 없음): 10진수를 n진수로 변환하는 함수를 보여준다.
  • 프로시저 예제 1 (DB 개입 없음): 10진수를 n진수로 변환하는 프로시저를 보여준다.
  • 함수 예제 2 (DB 작업 있음): 서기를 일본력으로 변환하는 함수를 보여준다. 테이블 정의, 데이터, 실행 예시가 포함된다.
  • 프로시저 예제 2 (DB 작업 있음, 비켜서 작업): 서기를 일본력으로 변환하는 프로시저를 보여준다.
  • 프로시저 예제 3 (DB 작업 있음, 커서 작업): 커서를 사용하여 서기를 일본력으로 변환하는 프로시저를 보여준다. 테이블 정의 및 데이터는 "함수 예제2 (DB작업 있음)"와 같다.

7. 1. 함수 예제 1 (DB 개입 없음)

mysql

  • - 10진수를 n진수로 변환하는 함수

DROP FUNCTION IF EXISTS DecToNshin; -- 기존 함수가 존재하면 삭제

DELIMITER // -- 마침 기호를 변경

CREATE FUNCTION DecToNshin (

dec_num INT, -- 10진수 입력값

n_shin TINYINT -- 변환할 진법(n)

)

RETURNS VARCHAR(32) -- n진수로 변환된 결과값 (문자열)

BEGIN

DECLARE ltr CHAR(36) DEFAULT '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'; -- 0-9, A-Z까지의 문자열

DECLARE w_dec_num INT;

DECLARE amari INT;

DECLARE w_DecToNshin VARCHAR(32);

SET w_DecToNshin = '';

SET w_dec_num = dec_num;

  • - n진법으로 변환 (나머지 연산 이용)

WHILE w_dec_num >= n_shin DO

SET amari = MOD(w_dec_num, n_shin); -- 나머지 계산

SET w_DecToNshin = CONCAT(SUBSTR(ltr, amari + 1, 1), w_DecToNshin); -- 나머지에 해당하는 문자를 결과 문자열 앞에 추가

SET w_dec_num = w_dec_num DIV n_shin; -- 몫 계산

END WHILE;

  • - 마지막 몫 처리

SET w_DecToNshin = CONCAT(SUBSTR(ltr, w_dec_num + 1, 1), w_DecToNshin); -- 마지막 몫에 해당하는 문자를 결과 문자열 앞에 추가

RETURN w_DecToNshin; -- 결과 반환

END;

//

DELIMITER ; -- 마침 기호 변경 취소

```

```mysql

SELECT DecToNshin(100,16); -- 100을 16진수로 변환

7. 2. 프로시저 예제 1 (DB 개입 없음)


실행 예시:```mysql

CALL DecToNshin(100, 16, @RSLT); -- 100을 16진수로 변환

SELECT @RSLT; -- 변환된 결과 확인

7. 3. 함수 예제 2 (DB 작업 있음)

다음은 서기를 일본력으로 변환하는 함수 예제이다.

(1) 테이블 정의 및 데이터



create table gengou

(bgn_date date,

end_date date,

gengou_name varchar(4));

insert into gengou values

('1868-01-01','1912-07-30','메이지'),

('1912-07-30','1926-12-25','다이쇼'),

('1926-12-25','1989-01-07','쇼와'),

('1989-01-08','2019-04-30','헤이세이');



(2) 정의



drop function if exists cng_gengou; -- 존재한다면 삭제

delimiter // -- 마침기호 변경

create function cng_gengou

(p_seireki_date date)

returns varchar(30)

  • - 서기→일본력

begin

declare w_rcnt int default 0; -- 행 line 확인

declare w_gengou_name varchar(4) default ''; -- 연호명

declare w_bgn_date date; -- 시작일

declare w_nensuu tinyint default 0; -- 일본력 년

declare w_rslt varchar(30) default ''; -- 결과

select count(*)

into w_rcnt

from gengou

where bgn_date<=p_seireki_date and end_date>=p_seireki_date;

if w_rcnt>1 then

set w_rslt='2행 이상 존재하기 때문에 지원하지 않음';

else

select gengou_name,bgn_date

into w_gengou_name,w_bgn_date

from gengou

where bgn_date<=p_seireki_date and end_date>=p_seireki_date;

if length(w_gengou_name)>0 then

set w_nensuu=year(p_seireki_date)-year(w_bgn_date)+1;

set w_rslt=concat(w_gengou_name,cast(w_nensuu as char(2)));

else

set w_rslt='change unsuccessful';

end if;

end if;

return w_rslt;

end;

//

delimiter ; -- 종결기호 취소



(3) 실행



select cng_gengou('2006-07-19');


7. 4. 프로시저 예제 2 (DB 작업 있음, 비켜서 작업)



'''실행'''

```mysql

CALL cng_gengou('2006-07-19', @rslt); -- 2006년 7월 19일에 대한 변환 실행

SELECT @rslt; -- 결과 출력

7. 5. 프로시저 예제 3 (DB 작업 있음, 커서 작업)

커서를 사용하여 서기를 일본력으로 변환하는 프로시저이다.

(1) 테이블 정의 및 데이터

: "함수 예제2 (DB작업 있음)"와 같다.

(2) 정의

```mysql

drop procedure if exists cng_gengou; -- 존재한다면 삭제

delimiter // -- 마침기호 변경

create procedure cng_gengou

(in p_seireki_date date,

out p_rslt varchar(30))

  • - 서기→일본력

begin

declare w_rcnt int default 0; -- 행 수 확인

declare w_gengou_name varchar(4) default ''; -- 연호이름

declare w_bgn_date date; -- 시작일

declare w_nensuu tinyint default 0; -- 일본력 년

declare eod tinyint;

  • - 커서 선언

declare cr1 cursor for

select gengou_name,bgn_date

from gengou

where bgn_date<=p_seireki_date and end_date>=p_seireki_date;

  • - 예외 선언

declare continue handler for not found set eod=1;

set eod=0;

open cr1;

fetch cr1 into w_gengou_name,w_bgn_date;

while eod=0 do

set w_rcnt=w_rcnt+1;

if w_rcnt>1 then

set p_rslt='2행 이상 존재하기 때문에 지원하지 않음';

else

if length(w_gengou_name)>0 then

set w_nensuu=year(p_seireki_date)-year(w_bgn_date)+1;

set p_rslt=concat(w_gengou_name,cast(w_nensuu as char(2)));

else

set p_rslt='change unsuccessful';

end if;

end if;

fetch cr1 into w_gengou_name,w_bgn_date;

end while;

close cr1;

end;

//

delimiter ; -- 종결기호 취소

```

(3) 실행

```mysql

call cng_gengou('2006-08-10',@rslt);

select @rslt;

8. 추가 정보

저장 프로시저는 트랜잭션 관리를 제어하거나 데이터베이스 트리거 또는 조건 처리기에서 호출될 수 있다. 예를 들어, 저장 프로시저는 특정 테이블에 데이터가 삽입되거나 테이블의 특정 필드가 업데이트될 때 트리거되어 저장 프로시저 내부의 코드가 실행되도록 할 수 있다. 조건 처리기로 저장 프로시저를 작성하면 데이터베이스 관리자가 저장 프로시저를 사용하여 오류를 포착하고, 데이터베이스나 파일과 같은 외부 리소스에 감사 정보를 기록함으로써 시스템 오류를 더 자세하게 추적할 수 있다.[1]

참조

[1] 웹사이트 Db2 12 - Application programming and SQL - Calling a stored procedure from your application https://www.ibm.com/[...] 2022-05-26
[2] 웹사이트 Chapter 11. SQL Procedure Language Guide http://docs.openlink[...] 2019-09-11
[3] 웹사이트 Chapter 42. Procedural Languages https://www.postgres[...] 2023-11-20
[4] 웹사이트 Chapter 11. SQL Procedure Language Guide http://docs.openlink[...] 2019-09-11


본 사이트는 AI가 위키백과와 뉴스 기사,정부 간행물,학술 논문등을 바탕으로 정보를 가공하여 제공하는 백과사전형 서비스입니다.
모든 문서는 AI에 의해 자동 생성되며, CC BY-SA 4.0 라이선스에 따라 이용할 수 있습니다.
하지만, 위키백과나 뉴스 기사 자체에 오류, 부정확한 정보, 또는 가짜 뉴스가 포함될 수 있으며, AI는 이러한 내용을 완벽하게 걸러내지 못할 수 있습니다.
따라서 제공되는 정보에 일부 오류나 편향이 있을 수 있으므로, 중요한 정보는 반드시 다른 출처를 통해 교차 검증하시기 바랍니다.

문의하기 : help@durumis.com