관계형 데이터베이스에서 저장 프로시저 선택이 지원되지 않는 이유는 무엇입니까?

관계형 데이터베이스에서 저장 프로시저 선택이 지원되지 않는 이유는 무엇입니까?

다음과 같은 작업을 수행할 수 없다는 것은 잘 알려진 사실입니다.SELECTOracle 또는 SQL Server(및 대부분의 다른 메인스트림 RDBMS 제품)에 저장된 프로시저를 기반으로 합니다.

일반적으로 저장 프로시저에서 선택할 때 몇 가지 명백한 "문제"가 있으며, 단 두 가지만 고려됩니다.

a) 저장 프로시저의 결과로 발생하는 열이 불확정(실행시간 전에는 알 수 없음)

b) 저장 프로시저의 특성이 불확실하기 때문에 데이터베이스 통계를 작성하고 효율적인 쿼리 계획을 수립하는 데 문제가 발생합니다.

이 기능은 사용자들이 자주 원하는 기능이기 때문에 시간이 지남에 따라 여러 가지 해결책이 개발되었습니다.

http://www.club-oracle.com/threads/select-from-stored-procedure-results.3147/

http://www.sommarskog.se/share_data.html

에는 SQL Server 에는 .OPENROWSEThttps://msdn.microsoft.com/en-us/library/ms190312.aspx 에 가입하거나 거의 모든 것에서 선택할 수 있습니다.

......그러나 DBA는 보안상의 이유로 이 기능을 사용하기를 매우 꺼려하는 경향이 있습니다.

따라서 저장 프로시저에 가입하거나 선택할 수 있도록 허용하는 데 있어 분명한 문제나 성능 고려 사항이 있지만 RDBMS 플랫폼에서 이 기능이 지원되지 않는 근본적인 기술적 이유가 있습니까?

:
처음 피드백에서 좀 더 명확하게...예, 저장 프로시저에서 결과 세트를 반환할 수 있으며, 예, 결과 세트에 가입(또는 선택)하려는 경우 저장 프로시저가 아닌 (테이블 값) 함수를 사용할 수 있습니다. 그러나 이는 저장 프로시저에서 가입/선택과 동일하지 않습니다.완전히 제어할 수 있는 데이터베이스에서 작업하는 경우 TVF를 사용할 수 있습니다.그러나 타사 데이터베이스에서 작업 중인 경우 기존 저장 프로시저를 강제로 호출하거나 시스템 저장 프로시저(https://msdn.microsoft.com/en-us/library/mt604368.aspx) 에 가입하는 경우가 많습니다.

2:
Postgre 여부에 관한 질문에 대하여SQL이 이 작업을 수행할 수 있습니다. 대답도 아니오입니다. Can PostgreSQL은 두 SQL Server 저장 프로시저 간에 조인을 수행합니까?

TL;DR: (표 값) 함수 또는 Postgre의 모든 종류의 함수 중에서 선택할 수 있습니다.SQL. 하지만 저장된 절차에서는 안 됩니다.

여기에 "직관적"이고 다소 데이터베이스에 구애받지 않는 설명이 있습니다. 왜냐하면 저는 SQL과 그 많은 방언들이 유기적으로 발전된 언어/개념이기 때문에 이에 대한 근본적이고 "과학적인" 설명이 있을 수 없다고 생각하기 때문입니다.

절차 vs.함수들, 역사적으로

저장 프로시저 중에서 선택하는 것이 중요하다고 생각하지는 않지만, 다년간의 경험과 현 상태를 받아들이는 것에 치우쳐 있습니다. 프로시저와 기능의 구분이 어떻게 혼란스러울 수 있는지, 그리고 프로시저와 기능이 더 다양하고 강력하기를 바라는지 확실히 알고 있습니다.특히 SQL Server, Sybase 또는 MySQL에서는 프로시저가 임의 수의 결과 세트/업데이트 수를 반환할 수 있지만, 이는 잘 정의된 유형을 반환하는 함수와는 다릅니다.

절차는 (부작용이 있는) 명령형 루틴이고 기능은 부작용이 없는 순수한 루틴이라고 생각합니다. ASELECT진술 그 자체는 또한 (잠금 효과를 제외하고) effects이 없는 "순수한" 것이므로 함수를 a에서 사용할 수 있는 유일한 유형의 루틴으로 생각하는 것이 타당합니다.SELECT진술.

사실, 절차는 임의의 프로그램을 실행할 수 있는 반면, 기능은 행동에 강한 제약이 있는 루틴이라고 생각하세요.

4GL 대 3GL 언어

이것을 바라보는 또 다른 방법은 SQL이 4세대 프로그래밍 언어(4GL)라는 관점입니다. 4GL은 할 수 있는 일에 큰 제약이 있을 때만 합리적으로 작동할 수 있습니다.Common Table Expression은 SQL turing을 완전하게 만들었지만 SQL의 선언적 특성으로 인해 실용적인 일상적인 관점에서 범용 언어가 될 수 없습니다.

저장 프로시저는 이러한 제한을 피할 수 있는 방법입니다.때로는 완전하고 실용적이기를 원합니다.따라서 저장 프로시저는 필수적인 것, 부작용이 있는 것, 거래적인 것 등에 의존합니다.

저장된 기능은 일부 3GL/절차 언어 기능을 순수한 4GL 세계에 도입하는 현명한 방법이며, 그 안에 부작용이 발생하는 것을 금지합니다(판도라의 상자를 열고 완전히 예측할 수 없는 경우를 제외하고).SELECT성명서).

일부 데이터베이스는 저장된 프로시저가 임의 수의 결과 세트/커서를 반환할 수 있도록 허용한다는 사실은 부작용을 포함한 임의의 동작을 허용하는 특성입니다.원칙적으로 저장된 함수에서도 이러한 특정 동작을 방지할 수 있다고 말한 것은 없지만, 4GL 언어인 SQL의 맥락 내에서 이를 허용한다면 매우 비실용적이고 관리하기 어려울 것입니다.

따라서:

• 프로시저는 프로시저, 모든 함수 및 SQL을 호출할 수 있습니다.
• "순수" 함수는 "순수" 함수와 SQL을 호출할 수 있습니다.
• SQL은 "순수" 함수와 SQL을 호출할 수 있습니다.

그러나:

• "순수" 함수 호출 절차가 "불순수" 함수가 됨(절차와 마찬가지).

그리고:

• SQL에서 프로시저를 호출할 수 없음
• SQL에서 "불순" 함수를 호출할 수 없습니다.

"순수" 테이블 값 함수의 예:

다음은 테이블 값의 "순수" 함수를 사용하는 몇 가지 예입니다.

오라클

CREATE TYPE numbers AS TABLE OF number(10);
/

CREATE OR REPLACE FUNCTION my_function (a number, b number)
RETURN numbers
IS
BEGIN
    return numbers(a, b);
END my_function;
/

그 다음은:

SELECT * FROM TABLE (my_function(1, 2))

SQL 서버

CREATE FUNCTION my_function(@v1 INTEGER, @v2 INTEGER)
RETURNS @out_table TABLE (
    column_value INTEGER
)
AS
BEGIN
    INSERT @out_table
    VALUES (@v1), (@v2)
    RETURN
END

그리고 나서.

SELECT * FROM my_function(1, 2)

PostgreSQL

Postgre에 대해 한마디 하겠습니다.SQL 입니다.

PostgreSQL은 굉장하므로 예외입니다.그것은 또한 이상하고 아마도 50%의 기능을 생산에 사용해서는 안 될 것입니다.그것은 "절차"가 아닌 "기능"만 지원하지만, 그 기능들은 무엇이든 할 수 있습니다.다음을 확인해 보십시오.

CREATE OR REPLACE FUNCTION wow ()
RETURNS SETOF INT
AS $$
BEGIN
    CREATE TABLE boom (i INT);

    RETURN QUERY
    INSERT INTO boom VALUES (1)
    RETURNING *;
END;
$$ LANGUAGE plpgsql;

부작용:

• 테이블이 생성됩니다.
• 레코드가 삽입됩니다.

아직:

SELECT * FROM wow();

수확량

wow
---
1

Microsoft SQL Server: Storeed Procedures and Functions(적어도 스칼라 UDF와 Multi-state TVF)에 대해서만 말하는 것은 다른 구성 요소입니다.

• 저장 프로시저는 미리 구문 분석된 쿼리 배치입니다.임시 쿼리 배치 또는 저장 프로시저에 저장된 쿼리 배치를 실행합니다.예를 들어 에서.에는 NET 에는라는 두 다른 .Text그리고.StoredProcedure할 수는 그냥 함수를 실행할 수는 없습니다.

• 함수, 심지어 TVF(Inline-TVF인 경우 기본적으로 매개변수를 취할 수 있는 보기)도 독립적으로 실행 가능한 코드 조각이 아닙니다.이들은 그 자체로 쿼리가 아니므로 쿼리 내에서 호출해야 합니다.

  또한 저장 프로시저와 달리 함수를 최적화할 수 있으므로 실행 계획을 중심으로 이동할 수 있습니다.쿼리에서 지정한 방법(예: 행당 대 한 번, 캐시된 결과)에 따라 실행 시간과 빈도가 보장되지 않습니다.사실, 이것은 종종 비결정적 결과를 원하지만 모든 행에 대해 단일 값만 반환되는 경우 문제를 야기합니다.이것은 아마도 함수가 데이터베이스의 상태를 변경하는 것과 다른 편리한 일들을 허용하지 않는 주요한 이유일 것입니다. 이러한 일들이 실제로 발생하는지, 어떤 순서로 발생하는지, 또는 얼마나 많이 발생하는지에 대한 제어권이 없기 때문입니다.반면에 저장 프로시저는 실행 계획입니다.

그렇기는 하지만, 그 가치를 위해서는 다음을 사용하지 않고 저장 프로시저에서 선택할 수 있습니다.OPENQUERY/OPENROWSET, SQLCLR이 필요합니다.실제로 T-SQL Functions에 부여된 대부분의 제약은 SQLCLR 코드(예: "no Dynamic SQL")로 극복할 수 있습니다. 그러나 이렇게 해서 SQLCLR 함수가 Query Optimizer가 원하는/기대하는 것에서 실행의 타이밍과 빈도를 변경하는 것으로부터 영향을 받지 않게 됩니다.

저는 당신의 질문이 저장된 절차에 관한 것은 아니라고 생각합니다.테이블 가치 함수의 제한에 관한 것이라고 생각합니다. SQL Server의 관점에서 보면 다음과 같습니다.

• 동적 SQL은 사용할 수 없습니다.
• 테이블이나 데이터베이스는 수정할 수 없습니다.
• 출력 열과 유형을 지정해야 합니다.
• 이런, 당신은 사용할 수도 없습니다.rand()그리고.newid()(directly)

(Oracle의 제한사항은 약간 다릅니다.)

가장 간단한 대답은 데이터베이스가 강력한 쿼리 언어이면서 트랜잭션 데이터베이스의 ACID 속성을 지원하는 환경이라는 것입니다.ACID 속성을 사용하려면 일관된 보기가 필요하므로 기존 테이블을 수정할 수 있으면 다음과 같은 작업이 수행됩니다.

select t.*, (select count(*) from functionThatModifiesT()) -- f() modifies "t"
from t;

.t됩니다에 됩니다.from이 동일한 실제로 SQL Server는 이 질문에 대한 답변을 제공하지만 동일한 조항에서 여러 참조에 대해 동일한 문제가 발생합니다.어떤 의미에서 사용자 정의 기능은 허용되지 않는 것과 같은 방식으로 제한됩니다.

select a = 1, a + 1

의미론을 정의하는 것은 매우 까다롭고 더 중요한 다른 강력한 기능들이 있기 때문에 노력할 가치가 없습니다.

하지만 SQL Server의 마지막 수단은 저장 프로시저가 여러 결과 집합을 "반환"할 수 있는 기능이라고 생각합니다.그것은 단순히 테이블의 세계에서 의미가 없습니다.

편집:

포스트그레스가 사용한create function매우 강력합니다.이 기능을 통해 기본 데이터베이스를 수정할 수 있으므로 흥미로운 트랜잭션 문제가 발생합니다.그러나 열과 해당 유형을 정의해야 합니다.

제 경험은 SQL Server에서만 이루어졌고, 제 사용법에 따라 일화가 있을 뿐입니다.하지만 처음부터 저장 프로시저 중에서 선택함으로써 달성하고자 하는 것은 무엇입니까?당신의 사용 사례는 무엇입니까?

제 경험으로는 저장된 proc는 선택한 것의 결과이지, 애초에 선택의 원천이 아닙니다.저장 프로시저를 만들어 결과 집합을 반환한 다음 (일반적으로 코드로) 해당 결과 집합으로 작업을 수행합니다.또는 저장 프로시저를 호출하여 예를 들어 INSERT 또는 DELETE를 선택하는 것 이외의 작업을 수행할 수도 있습니다.

TSQL에서 쿼리 결과를 캡처하고 SELECT 문을 CTE에 넣거나 보기를 만들어 선택할 수 있는 추가 작업을 수행하려면 다음과 같이 하십시오.

결과 집합을 반환할 수 있고 일반적으로 반환할 수 있는 스토어 절차는 비즈니스 로직을 실행하기 위한 장치로 간주되어야 합니다.원하는 기능을 제공하려면 보기 또는 표 기능을 사용해야 합니다.

Oracle에서는 저장된 기능 중에서 선택할 수 있습니다.타이핑이 강해 일반 서브쿼리로 취급할 수 있습니다.일반적으로 다음을 사용해야 합니다.SELECT FROM TABLE (CAST (function_call(args) AS TABLE_COLL_TYPE))

또한 다른 테이블의 값을 함수에 대한 인수로 사용하여 저장된 함수와 "가입"할 수 있습니다.

select t1.a, t1.b, func.c
from t1, table (function_call (a, b)) as func

언급URL : https://stackoverflow.com/questions/33833265/why-is-selecting-from-stored-procedure-not-supported-in-relational-databases