저장 프로시저

Stored procedure

스토어드 프로시저(proc, storp, sproc, StoredProc, StoreProc, sp 또는 SP라고도 함)는 RDBMS(Relational Database Management System)에 액세스하는 애플리케이션에서 사용할 수 있는 서브루틴입니다.이러한 절차는 데이터베이스 데이터 사전에 저장됩니다.

스토어드 프로시저에는 데이터 검증(데이터베이스에 통합) 또는 액세스 제어 메커니즘이 있습니다.게다가 스토어드 프로시저는, 당초 애플리케이션에 실장되어 있던 로직을 통합해 일원화할 수 있습니다.시간과 메모리를 절약하기 위해 여러 SQL 문을 실행해야 하는 광범위하거나 복잡한 처리를 저장 프로시저에 저장할 수 있으며 모든 응용 프로그램이 프로시저를 호출합니다.하나의 저장 프로시저를 다른 저장 프로시저 내에서 실행함으로써 중첩된 저장 프로시저를 사용할 수 있습니다.

저장 프로시저는 결과 세트를 반환할 수 있습니다. 즉,SELECT진술.이러한 결과 세트는 커서, 다른 저장 프로시저, 결과 세트 로케이터 또는 응용 프로그램을 사용하여 처리할 수 있습니다.저장 프로시저에는 데이터 처리를 위한 선언된 변수와 테이블 내의 여러 행을 반복할 수 있는 커서가 포함될 수도 있습니다.stored-procedure flow-control 문에는 일반적으로 다음이 포함됩니다.IF,WHILE,LOOP,REPEAT,그리고.CASE스테이트먼트 등입니다.저장 프로시저는 변수를 선언하는 방법과 위치에 따라 변수를 수신하거나 결과를 반환하거나 변수를 수정하거나 반환할 수 있습니다.

실행

저장 프로시저는 UDF(사용자 정의 함수)와 유사합니다.주요 차이점은 UDF는 SQL 문 내의 다른 표현과 마찬가지로 사용할 수 있지만 스토어드 프로시저는 를 사용하여 호출해야 한다는 것입니다.CALL스테이트먼트.[1] 

콜 프로시저(...)

또는

EXECUTE 프로시저(...)

스토어드 프로시저의 정확하고 올바른 실장은 데이터베이스 시스템마다 다릅니다.대부분의 주요 데이터베이스 벤더는 어떤 형태로든 이를 지원합니다.데이터베이스 시스템에 따라 저장 프로시저는 SQL, Java, C 또는 C++와 같은 다양한 프로그래밍 언어로 구현될 수 있습니다.비 SQL 언어로 작성된 저장 프로시저는 SQL 문 자체를 실행하거나 실행하지 않을 수 있습니다.

스토어드 프로시저의 채택이 증가함에 따라 SQL:1999 및 SQL:2003 표준에서 SQL 언어에 절차 요소가 도입되었습니다.그 결과 SQL은 필수 프로그래밍 언어가 되었습니다.대부분의 데이터베이스 시스템은 SQL/PSM을 초과하는 독점 및 벤더 고유 확장을 제공합니다. SQL/JRT뿐만 아니라 Java 스토어드 프로시저의 표준 사양도 있습니다.

데이터베이스 시스템 구현 언어
큐브리드 자바
IBM DB2 SQL PL(SQL/PSM 표준에 준거) 또는 Java
파이어버드 PSQL(Fyracle은 Oracle의 PL/SQL 일부도 지원)
인포믹스 자바
베이스간 저장 프로시저 및 트리거 언어
Microsoft SQL Server Transact-SQL 및 각종.NET Framework 언어
MySQL, MariaDB SQL/PSM 표준을 엄수하여 스토어드 프로시저를 소유하다
NuoDB SQL 또는 Java
OpenLink Virtuoso VSP([2]Virtuoso SQL Procedures), Java, C 및 기타 프로그래밍 언어를 통해 확장 가능
오라클 PL/SQL 또는 Java
포스트그레스Ql PL/pgSQL은 PL/Perl 또는 PL/PHP 등의 자체 함수 언어 사용 가능
SAP HANA SQLScript 또는 R
SAP ASE 트랜잭션 SQL
어디에서나 SAP SQL 사용 가능 트랜잭션 SQL, Watcom SQL
SQLite 지원되지 않음

정적 SQL과의 비교

오버헤드
스토어드 프로시저 문은 데이터베이스에 직접 저장되므로 소프트웨어 응용 프로그램이 인라인(동적) SQL 쿼리를 데이터베이스로 전송하는 경우 일반적으로 필요한 컴파일 오버헤드의 전부 또는 일부를 제거할 수 있습니다.(단, 대부분의 데이터베이스 시스템에서는 동적 SQL 문이 반복적으로 컴파일되지 않도록 스테이트먼트 캐시 및 기타 메서드를 구현합니다).또한 미리 컴파일된 SQL을 사용하지 않아도 되지만 SQL 문의 모든 인수가 컴파일 시에 제공되는 것은 아니기 때문에 최적의 실행 계획을 작성해야 하는 복잡성을 가중시킵니다.특정 데이터베이스 구현 및 구성에 따라 일반 쿼리 또는 사용자 정의 함수와 비교하여 저장 프로시저에서 혼합된 성능 결과를 볼 수 있습니다.
네트워크 트래픽 회피
스토어드 프로시저의 주요 장점은 데이터베이스 엔진 내에서 직접 실행할 수 있다는 것입니다.프로덕션 시스템에서는 일반적으로 프로시저가 액세스되는 데이터에 직접 액세스할 수 있는 특수 데이터베이스 서버에서 완전히 실행된다는 것을 의미합니다.여기서의 이점은 네트워크 통신 비용을 완전히 피할 수 있다는 것입니다.이는 복잡한 일련의 SQL 문에서 더욱 중요해집니다.
비즈니스 로직 캡슐화
스토어드 프로시저를 사용하면 프로그래머가 비즈니스 로직을 데이터베이스에 API로 삽입할 수 있기 때문에 데이터 관리를 단순화하고 클라이언트 프로그램의 다른 곳에서 로직을 부호화할 필요성을 줄일 수 있습니다.이것에 의해, 클라이언트 프로그램의 장해에 의한 데이터 파손의 가능성이 낮아집니다.데이터베이스 시스템은 저장 프로시저를 사용하여 데이터 무결성과 일관성을 보장할 수 있습니다.
접근권 위임
많은 시스템에서 저장 프로시저는 해당 프로시저를 실행하는 사용자가 직접 가지고 있지 않은 데이터베이스에 대한 액세스 권한을 부여할 수 있습니다.
SQL 주입 공격으로부터 일부 보호
스토어드 프로시저는 주입 공격으로부터 보호할 수 있습니다.공격자가 SQL 명령을 삽입하더라도 저장 프로시저 매개 변수는 데이터로 처리됩니다.또한 일부 DBMS는 파라미터의 유형을 확인합니다.그러나 적절한 예방 조치를 취하지 않는 한 입력을 사용하여 동적 SQL을 생성하는 저장 프로시저는 여전히 SQL 주입에 취약합니다.

기타 용도

시스템에 따라서는 트랜잭션 관리를 제어하기 위해 스토어드 프로시저를 사용할 수 있습니다.또 다른 시스템에서는 스토어드 프로시저가 트랜잭션 내부에서 실행되어 트랜잭션이 효과적으로 투명해집니다.스토어드 프로시저는 데이터베이스 트리거 또는 조건 핸들러에서 호출할 수도 있습니다.예를 들어, 스토어드 프로시저는 특정 테이블상의 삽입이나 테이블내의 특정 필드의 갱신에 의해서 트리거 되어 스토어드 프로시저내의 코드가 실행된다.또한 조건 핸들러로서 스토어드 프로시저를 쓰는 것으로, 데이터베이스 관리자는 스토어드 프로시저를 사용해 에러를 검출해, 감사 정보를 데이터베이스나 파일등의 외부 리소스에 기록하는 것으로, 시스템의 에러를 보다 상세하게 추적할 수 있습니다.

기능과의 비교

  • 함수는 특정 계산을 수행하기 위해 작성된 하위 프로그램입니다.
  • 스칼라 함수는 하나의 값(또는 NULL)만 반환하는 반면 테이블 함수는 각 행에 하나 이상의 열이 있는 0개 이상의 행으로 구성된 (관계형) 테이블을 반환합니다.
  • 함수는 값을 반환해야 합니다(사용:RETURN키워드) 단, 스토어드 프로시저의 경우 필수사항은 아닙니다.
  • 스토어드 프로시저는RETURN값이 전달되지 않습니다.
  • 함수는 다음에서 사용할 수 있습니다.SELECT데이터 조작을 하지 않는 한, 스테이트먼트를 설정합니다.단, 절차는 에 포함할 수 없습니다.SELECT진술들.
  • 저장 프로시저는 다음 명령을 사용하여 여러 값을 반환할 수 있습니다.OUT매개 변수를 지정하거나 값을 반환하지 않습니다.
  • 저장 프로시저는 쿼리 컴파일 시간을 절약합니다.
  • 저장 프로시저는 데이터베이스 개체입니다.
  • 저장 프로시저는 재료 객체입니다.

작성한 스테이트먼트와의 비교

준비된 문은 일반 문 또는 쿼리를 사용하여 나중에 다른 리터럴 값을 사용할 수 있도록 매개 변수를 지정합니다.스토어드 프로시저와 마찬가지로 효율화를 위해 서버에 저장되며 SQL 주입 공격으로부터 어느 정도 보호합니다.작성된 스테이트먼트는 단순하고 선언적이기는 하지만 일반적으로 절차적 논리를 사용하기 위해 작성되지 않으며 변수에 대해 작동할 수 없습니다.간단한 인터페이스와 클라이언트 측 구현으로 인해 준비된 문장은 DBMS 간에 더 폭넓게 재사용할 수 있습니다.

스마트 계약과의 비교

스마트 컨트랙트는 RDBMS가 아닌 블록체인에 저장된 실행 가능 코드를 가리키는 말로, 퍼블릭 블록체인 네트워크의 실행 결과 컨센서스 메커니즘은 기존 프라이빗 데이터베이스나 연합 데이터베이스와는 원칙적으로 다르지만, 일반적으로 가치감이 있지만 표면적으로는 스토어드 프로시저와 동일한 기능을 수행한다.e 트랜잭션.

단점들

  • 스토어드 프로시저 언어는 벤더마다 다릅니다.데이터베이스 벤더를 변경하려면 일반적으로 기존 저장 프로시저를 다시 작성해야 합니다.
  • 저장 프로시저의 변경은 버전 관리 시스템 내에서 다른 코드보다 추적하기가 어렵습니다.변경 내용은 프로젝트 이력에 저장되는 스크립트로 재현해야 하며, 절차의 차이는 병합 및 추적하기가 더 어려워질 수 있습니다.
  • 스토어드 프로시저의 에러는 애플리케이션 IDE의 컴파일 또는 빌드 스텝의 일부로서 검출할 수 없습니다.스토어드 프로시저가 없어졌거나 잘못 삭제되었을 경우에도 마찬가지입니다.
  • 벤더마다 스토어드 프로시저 언어가 다릅니다.
    • 예를 들어 Postgres의 pgpsql은 Microsoft의 [citation needed]T-SQL보다 더 많은 언어 기능을 가지고 있습니다(특히 확장을 통해).
  • 스토어드 프로시저를 기술하고 디버깅하기 위한 툴 지원은 다른 프로그래밍 언어만큼 좋지 않은 경우가 많지만 벤더와 언어에 따라 다릅니다.
    • 예를 들어 PL/SQL과 T-SQL 모두 전용 IDE와 디버거를 가지고 있습니다.PL/PgSQL은 다양한 IDE에서 디버깅할 수 있습니다.

레퍼런스

  1. ^ "Db2 12 - Application programming and SQL - Calling a stored procedure from your application". www.ibm.com. Retrieved 2022-05-26.
  2. ^ "Chapter 11. SQL Procedure Language Guide". OpenLink documentation. Retrieved 11 September 2019.

외부 링크