SQL의 저장 프로시저 | 개요

이 블로그에서는 일련의 코드를 저장하고 필요할 때마다 반복적으로 사용할 수 있는 SQL의 저장 프로시저에 대해 설명합니다. 저장 프로시저는 보기와 비슷합니다. 그러나 보기로는 수행할 수 없는 저장 프로시저로 DROP, TRUNCATE, DELETE 등과 같은 일반적인 테이블 작업을 수행할 수 있습니다.

저장 프로시저는 뷰보다 빠르게 실행되도록 미리 컴파일됩니다. 또한 데이터베이스 서버로 전송되는 데이터의 양을 최소화합니다.

목차

• SQL의 저장 프로시저 구문: 매개변수 없음
• SQL의 저장 프로시저 구문: 매개변수 사용
• SSMS의 저장 프로시저 샘플 시나리오
• 두 번째 샘플 시나리오
• 세 번째 샘플 시나리오
• SQL에서 저장된 저장 프로시저의 위치
• 매개변수를 사용하여 SQL에서 저장 프로시저 생성
• 기본값을 사용하여 저장 프로시저 만들기
• 결론

SQL의 저장 프로시저 구문: 매개변수 없음

매개변수 없이 저장 프로시저를 생성하려면 CREATE 함수 로 시작해야 합니다 . 그런 다음 PROC 함수를 추가하고 바로 뒤에 프로시저 이름을 입력한 다음 AS 함수를 추가합니다.

에서 저장 프로시저를 만드는 것은 테이블 및 보기를 만드는 것과 비슷합니다. 그러나 차이점은 데이터를 가져오는 과정에 있습니다.

예를 들어 뷰에서 데이터를 가져오려면 " SELECT * FROM view_name "을 사용합니다. 반면 저장 프로시저의 경우 "실행"을 의미하는 EXEC를 사용하고 뒤에 저장 프로시저 이름을 입력합니다 .

저장 프로시저를 실행하면 추가한 지침이나 명령에 따라 정보도 표시됩니다.

SQL의 저장 프로시저 구문: 매개변수 사용

으로 이동해 보겠습니다 . 프로세스는 내가 시연한 이전 예제와 유사합니다. 유일한 차이점은 AS 함수 앞에 매개변수를 포함하고 바로 뒤에 데이터 유형을 포함해야 한다는 것입니다.

명령에서 저장 프로시저 생성 시 선언한 매개 변수를 활용할 수도 있습니다. 이는 여러 값을 입력하는 시간을 절약하기 위한 것입니다. 예를 들어, customerID1 , customerID2 등을 수동으로 입력하는 대신 @custid 또는 매개변수를 활용하여 긴 코드 집합을 방지했습니다.

매개변수를 사용하거나 포함할 때 @ 기호 를 사용하는 것이 중요합니다 . 하나가 아니라 원하는 만큼 많은 매개변수를 포함할 수도 있습니다. 이것이 매개변수를 사용하여 저장 프로시저를 실행하는 방법입니다.

알 수 있듯이 저장 프로시저를 실행할 때 값이 1 인 매개 변수도 사용했습니다. 이렇게 하면 custid1 의 정보만 가져옵니다 .

SSMS의 저장 프로시저 샘플 시나리오

SQL의 저장 프로시저에 대한 더 많은 예를 살펴보겠습니다. 먼저 아래 강조 표시된 명령을 사용하여 샘플 저장 프로시저를 만들겠습니다.

저장 프로시저를 만들 때 이름 지정 규칙으로 일반적으로 "usp" 또는 "sp"를 사용하여 이것이 저장 프로시저임을 나타냅니다. "usp"가 무엇을 의미하는지 궁금하시다면 단순히 사용자 정의 저장 프로시저를 의미합니다. 

다음으로 저장 프로시저가 실행될 때 처리할 쿼리를 작성하겠습니다.

이전 예제의 쿼리는 단순히 dbo.stageOrders 라는 테이블을 제거합니다 . 그런 다음 Sales.SalesOrderHeader 테이블 의 데이터를 사용하여 dbo.stageOrders를 다시 만듭니다 . 

다음 코드를 강조 표시하고 실행 단추를 클릭하여 이 저장 프로시저를 생성해 보겠습니다 .

그러면 다음과 같은 메시지가 표시됩니다.

그런 다음 EXEC usp_TEST 를 실행하여 저장 프로시저를 실행합니다 .

usp_TEST 저장 프로시저를 실행하면 이제 dbo.stageOrders 에 Sales.SalesOrderHeader 의 데이터가 있어야 합니다 . 아래 명령을 실행하여 dbo.stageOrders 의 내용을 확인하겠습니다 .

결과적으로 저장 프로시저에 추가한 명령을 기반으로 dbo.stageOrders 에서 만든 데이터입니다 .

두 번째 샘플 시나리오

다른 예로 넘어 갑시다. 이번에는 dbo.stageOrders 테이블을 삭제하고 Sales.SalesOrderHeader 테이블 에서 가져온 데이터로 동일한 테이블을 다시 만들고 싶지 않습니다 .

대신 " usp_TEST " 저장 프로시저를 실행할 때마다 Sales.SalesOrderHeader 테이블의 데이터가 표시되기를 원합니다 .

이를 수행하려면 ALTER 문을 사용하여 최근에 생성된 저장 프로시저를 변경해야 합니다.

이 예에서는 저장 프로시저를 수정하는 데 사용되는 ALTER 문을 활용했습니다 . 이것을 뷰와 테이블에 적용할 수도 있습니다. 위의 스크린샷에서 코드를 실행하면 저장 프로시저에서 처리할 쿼리가 변경됩니다.

이번에는 이전처럼 테이블을 다시 만들지 않습니다. " usp_TEST "를 실행하면 Sales.SalesOrderHeader 테이블 에서 데이터를 가져옵니다 .

세 번째 샘플 시나리오

다른 예를 들어보겠습니다. 이번에는 SSMS 에서 저장 프로시저를 만드는 첫 번째 예에서 사용한 dbo.stageOrders 테이블을 재사용합니다 . dbo.stageOrders 테이블을 선택하여 이 예를 시작하겠습니다 . 

dbo.stageOrders에서 모든 데이터를 선택한 결과는 다음과 같아야 합니다.

이제 usp_TEST 를 실행하여 dbo.stageOrders 테이블을 비웁니다 . 다음 명령을 추가하여 이를 수행할 수 있습니다.

TRUNCATE TABLE 명령을 추가한 후 usp_TEST를 업데이트해야 합니다 .

그런 다음 명령이 성공적으로 완료되었다는 메시지가 표시되어야 합니다. 그런 다음 usp_TEST를 다시 실행합니다 .

usp_TEST 를 실행하면 Sales.SalesOrderHeader 의 모든 레코드가 표시되고 dbo.stageOrders 테이블 도 비워집니다 . 

dbo.stageOrders 테이블이 비어 있는지 확인하려면 다음 명령을 사용하여 테이블을 선택하고 실행해야 합니다.

위의 코드를 실행하면 dbo.stageOrders 테이블이 이제 비어 있는 것을 볼 수 있습니다. 이는 usp_TEST를 업데이트하는 데 사용한 TRUNCATE TABLE 명령 때문입니다 .

SQL에서 저장된 저장 프로시저의 위치

SQL 의 저장 프로시저가 저장되는 위치를 보려면 왼쪽의 개체 탐색기 패널로 이동하여 작업 중인 데이터베이스 앞의 " + " 아이콘을 클릭합니다.

그런 다음 Programmability를 마우스 오른쪽 버튼으로 클릭 하고 Refresh를 선택 합니다 .

" + " 아이콘을 클릭하여 Programmability 폴더 또는 그룹을 확장합니다 . 그런 다음 동일한 단계를 수행하여 저장 프로시저 그룹을 확장합니다. 저장 프로시저 그룹 내에서 dbo.usp_TEST 가 표시되어야 합니다 .

SQL의 특정 저장 프로시저 가 수행하는 명령이나 쿼리를 확인하려면 저장 프로시저를 마우스 오른쪽 단추로 클릭 하고 아래 스크린샷의 단계를 따르십시오.

그런 다음 제공된 명령을 볼 수 있는 다른 탭에서 저장 프로시저를 엽니다. 이것이 dbo.usp_TEST를 열었을 때의 모습입니다.

보시다시피 CREATE 문 앞에 몇 가지 기본 명령이 있습니다. 원하는 경우 간단히 삭제할 수 있습니다. 

이제 SQL의 저장 프로시저 가 수행하는 명령을 확인하는 방법을 알게 되었습니다 .

매개변수를 사용하여 SQL에서 저장 프로시저 생성

다음으로 매개 변수가 있는 저장 프로시저를 만듭니다 . 예를 들어 다음 코드를 사용하여 새 저장 프로시저를 만듭니다.

예제 코드에서는 usp_GetCustomer 라는 저장 프로시저를 만드는 동일한 프로세스를 사용했습니다 . 그런 다음 입력 유형이 INT 인 @CustomerID 매개변수를 추가했습니다 .

저장 프로시저를 만들 때 매개 변수를 추가한 후에는 명령을 실행할 때마다 항상 매개 변수를 제공해야 합니다.

매개 변수를 제공하지 않고 usp_GetCustomer를 실행하면 어떻게 되는지 살펴보겠습니다 .

매개 변수 없이 usp_GetCustomer를 실행하면 오류 메시지가 나타납니다. 매개변수를 사용하여 usp_GetCustomer를 실행하면 다음과 같이 표시됩니다 .

제공된 매개변수를 사용하여 저장 프로시저를 실행할 때 적절한 결과를 얻을 수 있습니다 .

기본값을 사용하여 저장 프로시저 만들기

매개변수가 있는 저장 프로시저를 실행할 때 오류 메시지를 받지 않으려면 기본 매개변수로 사용할 기본값을 설정할 수 있습니다.

예를 들어 usp_GetOrdersByYear 라는 저장 프로시저를 만들 것입니다 .

그런 다음 입력 유형이 " INT "이고 기본값이 2011 인 @OrderYear 매개변수를 추가하겠습니다 .

제공된 매개변수 없이 usp_GetOrdersByYear를 실행하면 연도가 2011 인 레코드가 표시됩니다 .

반면에 주어진 매개변수로 2014를 사용하여 usp_GetOrdersByYear를 실행하면 2014 년 의 레코드 가 표시되어야 합니다 .

이것은 일상적인 데이터 관리 작업에서 저장 프로시저를 활용하는 방법입니다.

SQL 집계 함수의 HAVING 절
ISNULL SQL 함수의 Case 문
OFFSET 및 FETCH를 사용한 SQL 데이터 추출

결론

모든 것을 고려하여 SQL의 저장 프로시저가 무엇이고 그 목적이 무엇인지 배웠습니다. 또한 현재 저장 프로시저에서 변경 또는 업데이트를 수행하는 데 사용되는 ALTER 문에 대해서도 설명했습니다 .

또한 SQL에서 저장 프로시저를 만드는 다양한 방법이 있다는 것과 기본값을 제공하여 저장 프로시저를 실행할 때 오류 수신을 방지하는 방법을 배웠습니다.

가장 중요한 것은 긴 코드 집합을 반복적으로 실행하지 않도록 명령 집합을 저장하는 저장 프로시저를 활용하는 방법을 배웠다는 것입니다. 마지막으로 매개변수를 제공할 때 " @ " 기호를 사용하는 것을 잊지 마십시오 .

모두 제일 좋다,

하피즈