[sql] SQL Server의 함수와 저장 프로 시저

함수와 저장 프로 시저를 꽤 오랫동안 배우고 있지만 함수와 저장 프로 시저를 사용해야하는 이유와시기를 모르겠습니다. 그들은 나에게 똑같아 보인다. 어쩌면 나는 그것에 대해 초보자이기 때문이다.

왜 그런지 말해 줄 수 있습니까?

답변

함수는 계산 된 값이며 영구적 인 환경 변경을 수행 할 수 없습니다 SQL Server(예 : 허용 되지 INSERT않거나 UPDATE설명이 허용 되지 않음 ).

함수가 SQL스칼라 값을 리턴하면 명령문 에서 인라인으로 사용 되거나 결과 세트를 리턴하면 결합 될 수 있습니다.

답변을 요약 한 주석에서 주목할 가치가있는 점. @Sean K Anderson 덕분에 :

함수는 컴퓨터 과학 정의에 따라 값을 반환해야하며 매개 변수 (인수)로받은 데이터를 변경할 수 없습니다. 함수는 아무것도 변경할 수 없으며 적어도 하나의 매개 변수가 있어야하며 값을 반환해야합니다. 저장된 proc는 매개 변수를 가질 필요가 없으며 데이터베이스 오브젝트를 변경할 수 있으며 값을 리턴 할 필요가 없습니다.

SQL저장 프로 시저에서 함수 를 호출하는 방법 과 저장 프로 시저 대신 함수를 사용하는 경우

안녕하세요 친구 여러분, 오늘 우리는 저장 프로 시저 사용시기와 기능 사용시기에 대해 논의 할 것입니다. 간단한 팀에서 일부 값을 계산하고 단일 값을 반환하면 필요하지 않습니다.

https://programmingtechtutorial.blogspot.com/2020/01/when-use-storeprocedure-and-when-use.html

답변

SP와 UDF의 차이점은 다음과 같습니다.

+---------------------------------+----------------------------------------+
| Stored Procedure (SP)           | Function (UDF - User Defined           |
|                                 | Function)                              |
+---------------------------------+----------------------------------------+
| SP can return zero , single or  | Function must return a single value    |
| multiple values.                | (which may be a scalar or a table).    |
+---------------------------------+----------------------------------------+
| We can use transaction in SP.   | We can't use transaction in UDF.       |
+---------------------------------+----------------------------------------+
| SP can have input/output        | Only input parameter.                  |
| parameter.                      |                                        |
+---------------------------------+----------------------------------------+
| We can call function from SP.   | We can't call SP from function.        |
+---------------------------------+----------------------------------------+
| We can't use SP in SELECT/      | We can use UDF in SELECT/ WHERE/       |
| WHERE/ HAVING statement.        | HAVING statement.                      |
+---------------------------------+----------------------------------------+
| We can use exception handling   | We can't use Try-Catch block in UDF.   |
| using Try-Catch block in SP.    |                                        |
+---------------------------------+----------------------------------------+

답변

함수와 저장 프로시 저는 별도의 목적으로 사용됩니다. 최상의 비유는 아니지만 함수는 문자 그대로 모든 프로그래밍 언어에서 사용하는 다른 함수로 볼 수 있지만 저장된 procs는 개별 프로그램이나 배치 스크립트와 비슷합니다.

함수에는 일반적으로 출력이 있으며 선택적으로 입력이 있습니다. 그런 다음 출력을 다른 함수 (DATEDIFF, LEN 등의 SQL Server 내장)에 대한 입력으로 사용하거나 SQL 쿼리에 대한 조건 자로 사용할 수 있습니다 (예 : SELECT a, b, dbo.MyFunction(c) FROM table또는) SELECT a, b, c FROM table WHERE a = dbo.MyFunc(c).

저장된 proc는 SQL 쿼리를 트랜잭션에 묶고 외부 세계와 인터페이스하는 데 사용됩니다. ADO.NET 등과 같은 프레임 워크는 함수를 직접 호출 할 수 없지만 저장된 proc을 직접 호출 할 수 있습니다.

함수에는 숨겨진 위험이 있습니다. 잘못 사용되어 다소 불쾌한 성능 문제가 발생할 수 있습니다.

SELECT * FROM dbo.MyTable WHERE col1 = dbo.MyFunction(col2)

MyFunction이 다음과 같이 선언 된 위치 :

CREATE FUNCTION MyFunction (@someValue INTEGER) RETURNS INTEGER
AS
BEGIN
   DECLARE @retval INTEGER

   SELECT localValue
      FROM dbo.localToNationalMapTable
      WHERE nationalValue = @someValue

   RETURN @retval
END

여기서 발생하는 것은 MyFunction 함수가 MyTable 테이블의 모든 행에 대해 호출된다는 것입니다. MyTable에 1000 개의 행이 있으면 데이터베이스에 대한 또 다른 1000 개의 임시 쿼리입니다. 마찬가지로, 열 스펙에 지정된 경우 함수가 호출되면 SELECT에 의해 리턴 된 각 행에 대해 함수가 호출됩니다.

따라서 신중하게 작성 기능이 필요합니다. 함수의 테이블에서 SELECT를 수행하는 경우 상위 스토어드 프로 시저의 JOIN 또는 다른 SQL 구문 (예 : CASE … WHEN … ELSE … 종료).

답변

저장 프로 시저와 사용자 정의 함수의 차이점 :

저장 프로시 저는 Select 문에서 사용할 수 없습니다.
저장 프로시 저는 지연된 이름 확인을 지원합니다.
저장 프로시 저는 일반적으로 비즈니스 로직을 수행하는 데 사용됩니다.
저장 프로시 저는 모든 데이터 유형을 반환 할 수 있습니다.
저장 프로시 저는 사용자 정의 함수보다 많은 수의 입력 매개 변수를 사용할 수 있습니다. 저장 프로시 저는 최대 21,000 개의 입력 매개 변수를 가질 수 있습니다.
저장 프로시 저는 동적 SQL을 실행할 수 있습니다.
저장 프로시 저는 오류 처리를 지원합니다.
비 결정적 기능은 저장 프로 시저에서 사용할 수 있습니다.

Select 문에서 사용자 정의 함수를 사용할 수 있습니다.
사용자 정의 함수는 지연 이름 확인을 지원하지 않습니다.
사용자 정의 함수는 일반적으로 계산에 사용됩니다.
사용자 정의 함수는 값을 반환해야합니다.
사용자 정의 함수는 이미지를 반환 할 수 없습니다.
사용자 정의 함수는 저장 프로 시저보다 적은 수의 입력 매개 변수를 허용합니다. UDF는 최대 1,023 개의 입력 매개 변수를 가질 수 있습니다.
임시 테이블은 사용자 정의 함수에서 사용할 수 없습니다.
사용자 정의 함수는 동적 SQL을 실행할 수 없습니다.
사용자 정의 함수는 오류 처리를 지원하지 않습니다. RAISEERROROR @@ERROR은 UDF에서 허용되지 않습니다.
비 결정적 기능은 UDF에서 사용할 수 없습니다. 예를 들어, GETDATE()UDF에는 사용할 수 없습니다.

답변

다른 SQL 문에서 사용할 값을 계산하고 리턴하려는 경우 사용자 정의 함수를 작성하십시오. 대신 복잡한 SQL 문 세트를 그룹화하는 것이 필요할 때 스토어드 프로 시저를 작성하십시오. 이것들은 결국 두 가지 다른 유스 케이스입니다!

답변

              STORE PROCEDURE                 FUNCTION (USER DEFINED FUNCTION)
 * Procedure can return 0, single or   | * Function can return only single value
   multiple values.                    |
                                       |
 * Procedure can have input, output    | * Function  can have only input
   parameters.                         |   parameters.
                                       |
 * Procedure cannot be called from     | * Functions can be called from
   function.                           |   procedure.
                                       |
 * Procedure allows select as well as  | * Function allows only select statement
   DML statement in it.                |   in it.
                                       |
 * Exception can be handled by         | * Try-catch block cannot be used in a
   try-catch block in a procedure.     |   function.
                                       |
 * We can go for transaction management| * We can't go for transaction
   in procedure.                       |   management in function.
                                       |
 * Procedure cannot be utilized in a   | * Function can be embedded in a select
   select statement                    |   statement.
                                       |
 * Procedure can affect the state      | * Function can not affect the state
   of database means it can perform    |   of database means it can not
   CRUD operation on database.         |   perform CRUD operation on
                                       |   database.
                                       |
 * Procedure can use temporary tables. | * Function can not use
                                       |   temporary tables.
                                       |
 * Procedure can alter the server      | * Function can not alter the
   environment parameters.             |   environment parameters.
                                       |
 * Procedure can use when we want      | * Function can use when we want
   instead is to group a possibly-     |   to compute and return a value
   complex set of SQL statements.      |   for use in other SQL
                                       |   statements.

답변

기본 차이

함수는 값을 반환해야하지만 저장 프로 시저에서는 선택 사항입니다 (프로시 저는 0 또는 n 값을 반환 할 수 있음).

함수는 입력 매개 변수 만 가질 수있는 반면, 프로시 저는 입력 / 출력 매개 변수를 가질 수 있습니다.

함수는 필수 인 하나의 입력 매개 변수를 사용하지만 저장 프로시 저는 o에서 n 개의 입력 매개 변수를 사용할 수 있습니다.

함수는 프로 시저에서 호출 할 수 있지만 프로시 저는 함수에서 호출 할 수 없습니다.

사전 차이

프로시 저는 SELECT 및 DML (INSERT / UPDATE / DELETE) 문을 허용하지만 Function은 SELECT 문만 허용합니다.

SELECT 문에는 프로 시저를 사용할 수 없지만 SELECT 문에는 함수를 임베드 할 수 있습니다.

저장 프로시 저는 WHERE / HAVING / SELECT 섹션의 어느 곳에서나 SQL 문에서 사용할 수 없지만 Function은 가능합니다.

테이블을 반환하는 함수는 다른 행 집합으로 취급 될 수 있습니다. 다른 테이블과 함께 JOIN에서 사용할 수 있습니다.

인라인 함수는 매개 변수를 취하는 뷰로 간주 할 수 있으며 JOIN 및 기타 행 세트 조작에서 사용될 수 있습니다.

프로 시저에서 try-catch 블록으로 예외를 처리 할 수있는 반면 try-catch 블록은 함수에서 사용할 수 없습니다.

트랜잭션 관리는 절차로 진행할 수 있지만 기능은 수행 할 수 없습니다.

출처