안녕하세요. SQLER의 코난 김대우입니다. 
이번 강좌에서는, 8-5. 저장 프로시저 - OUTPUT 파라미터를 진행 하겠습니다.


SQLER에서 진행되는, 챗GPT와 함께 배우는 SQL Server 강좌 목록

 

이번에 진행할 강좌는 저장 프로시저 OUTPUT 파라미터(매개변수)입니다.

 

 

 

TL;DR

저장 프로시저로 SELECT 구문 결과셋을 제공할 수 있고, OUTPUT 파라미터는 결과셋 외에 추가적인 값을 출력합니다. 예제를 통해 출력 결과와 생성 방법을 설명하며, 특히 두 개 이상의 결과셋 처리와 OUTPUT 파라미터 처리 방법을 함께 다룹니다.



저장 프로시저 OUTPUT 파라미터

일반적으로 저장 프로시저나 일반 SQL 쿼리에서 SELECT 구문을 실행해 테이블 형태의 결과셋을 받아 처리할 수 있습니다. OUTPUT 파라미터는 이러한 결과셋에 추가적으로 또는 결과셋 없이 하나의 값을 더 출력하는 저장 프로시저 기능입니다. 먼저 예제를 실행하고 말씀드리겠습니다.


데이터 수정과 OUTPUT 파라미터

USE AdventureWorks;
GO

-- UPDATE 저장 프로시저 생성
CREATE OR ALTER PROC pEmployeeUdt
 @emp_salary BIGINT
, @output INT OUTPUT  -- OUTPUT 파라미터
AS
UPDATE Employee SET emp_salary = @emp_salary
WHERE emp_salary < @emp_salary
SET @output = (SELECT @@ROWCOUNT);  -- OUTPUT 파라미터
GO

-- 테스트 조회하니 2명 정도가 연봉이 120000000 미만임
SELECT * FROM Employee;
GO

-- 직원의 연봉이 입력 파라미터 @emp_salary 보다 작으면 연봉을 * 2 수행
-- 아웃풋 파라미터는 영향을 받은 행의 개수(연봉이 업데이트된 직원수) 리턴
DECLARE @effected_rows int
EXEC pEmployeeUdt 120000000, @effected_rows OUTPUT
SELECT @effected_rows;
GO

 

일반적인 저장 프로시저와 생성도 다르고, 실행도 특이합니다. 저장 프로시저 생성 부분에서 파라미터 지정 시 OUTPUT 키워드를 주는 부분이 다릅니다.


위의 예제는 간단히 연봉이 파라미터보다 낮은 사람은 연봉*2를 하는 로직입니다.


@@ROWCOUNT는 시스템 함수로 테이블에서 영향을 받은(삽입, 수정, 삭제) 행의 수를 리턴합니다. 데이터를 삽입, 수정, 삭제하면 해당 작업이 잘 처리되었는지 확인하고 싶은 경우에 OUTPUT 파라미터를 이용하면 유용합니다.

 

2개 이상의 결과셋 처리

처음 애플리케이션에서 개발할 때 난감할 수 있는 여러 결과셋이 리턴될 경우와 OUTPUT 파라미터 처리입니다.

-- UPDATE 연봉을 절반으로 줄이는 UPDATE 후 데이터 조회
CREATE OR ALTER PROC pEmployeeSalaryUdt
@output INT OUTPUT  -- OUTPUT 파라미터
AS
UPDATE Employee SET emp_salary = emp_salary * 0.5
SELECT * FROM Employee
SET @output = (SELECT @@ROWCOUNT);  -- OUTPUT 파라미터
GO

-- 저장 프로시저 실행
DECLARE @effected_rows INT
EXEC pEmployeeSalaryUdt @effected_rows OUTPUT
SELECT @effected_rows;
GO


저장 프로시저는 간단히, (1) 연봉을 절반으로 줄이는 수정 작업을 하고, (2) 데이터를 조회한 다음 (3) OUTPUT 파라미터로 영향받은 행의 수를 출력하는 프로시저입니다.


SSMS 쿼리 결과탭의 메시지 탭을 보면 이렇게 출력됩니다.

(4개 행이 영향을 받음)

(4개 행이 영향을 받음)

(1개 행 적용됨)


개발 업무를 하면서 이렇게 데이터 수정 후 데이터를 조회하면 이상하게 빈 결과셋이 들어와 난감한 경우가 있습니다. 분명히, SSMS나 쿼리 도구에서 실행하면 결과가 있는데 말이죠. 그 이유는 애플리케이션은 데이터베이스에서 2개의 결과셋과 1개의 OUTPUT 파라미터를 받았기 때문입니다. 


즉, (1번) UPDATE를 수행했지요? 네 개의 행이 수정되었고, 결과셋은 없는 것처럼 보이지만, 1번 결과셋으로 빈 결과셋이 전달됩니다.
(2번) 데이터를 조회했습니다. 두 번째 결과셋으로 이 조회 결과가 출력됩니다.
(3번) OUTPUT 파라미터는 별개로 전달됩니다.


그럼 어떻게 두 개의 결과셋을 애플리케이션에서 받아 처리할 수 있습니까?
예를 들어, Python의 경우 SQLAlchemy를 이용한다면, 이렇게 두 개의 결과셋을 처리할 수 있습니다.

 

connection = engine.raw_connection()
try:
    cursor_obj = connection.cursor()
    cursor_obj.execute("select * from table1; select * from table2")
    results_one = cursor_obj.fetchall()
    cursor_obj.nextset()
    results_two = cursor_obj.fetchall()
    cursor_obj.close()
finally:
    connection.close()

 

SELECT를 두 번 진행합니다. (1개 저장 프로시저 안에서 2번 SELECT 할 경우도 같습니다.) 이렇게 nextset()을 수행해 다음 결과셋에서 작업이 가능합니다.


☑️ 챗GPT 활용: Python SQLAlchemy에 대해서 알려줘

 

SET NOCOUNT ON 사용

또는 저장 프로시저나 SQL 쿼리에서 약간 편법(?)을 이용해 

- 데이터 수정
- 데이터 조회 


작업처럼, 먼저 데이터 수정 작업이 있을 경우에만, 1개 결과셋으로 줄일 수 있습니다.


바로, SET NOCOUNT ON을 이용하는 방법입니다. 이 방법을 이용하면, 결과셋이 비어있는 경우 해당 결과셋을 리턴하지 않고 결과셋이 존재할 경우에만 리턴합니다.

-- UPDATE 연봉을 절반으로 줄이는 UPDATE 후 데이터 조회
CREATE OR ALTER PROC pEmployeeSalaryUdt
@output INT OUTPUT  -- OUTPUT 파라미터
AS
SET NOCOUNT ON  -- Nocount 설정
UPDATE Employee SET emp_salary = emp_salary * 0.5
SELECT * FROM Employee
SET @output = (SELECT @@ROWCOUNT);  -- OUTPUT 파라미터
GO

 

이렇게 수행하면, 첫 번째 결과셋으로 SELECT 구문의 결과가 애플리케이션에 들어옵니다.

 

OUTPUT 파라미터를 애플리케이션에서 받아 처리하는 방법

힘들게 OUTPUT을 사용하는 이유는, 결국 애플리케이션에서 영향받은 행의 숫자 또는 결과셋 외에 추가적으로 전달하기 원하는 값을 제공받기 위해서입니다. 저장 프로시저의 결과셋에 추가적으로 전달되는 OUTPUT 파라미터 값은 어떻게 얻을 수 있을까요?

 

pyodbc - Calling Stored Procedures 에서 좋은 예제를 볼 수 있습니다.

 

저장 프로시저와 OUTPUT 파라미터를 아래처럼 생성합니다.

CREATE PROCEDURE [dbo].[test_for_pyodbc] 
    @param_in nvarchar(max) = N'', 
    @param_out nvarchar(max) OUTPUT
AS
BEGIN
    -- the following statement emits a rowcount as the first "result"
    SELECT 1 AS foo INTO #junk;
    -- since this procedure does not start with
    -- SET NOCOUNT ON;
    -- we will need to use that statement at the beginning of our
    -- anonymous code block in Python

    -- set output parameter
    SELECT @param_out = N'Output parameter value: You said "' + @param_in + N'".';
    
    -- also return a couple of result sets
    SELECT N'SP result set 1, row 1' AS foo
    UNION ALL
    SELECT N'SP result set 1, row 2' AS foo;
    
    SELECT N'SP result set 2, row 1' AS bar
    UNION ALL
    SELECT N'SP result set 2, row 2' AS bar;
END

 

Python에서 pyodbc를 이용할 경우 이렇게 처리해 결과셋과 OUTPUT 파라미터를 모두 받을 수 있습니다.


☑️ 챗GPT 활용: Python에서 pyodbc에 대해서 알려줘

 

sql = """\
SET NOCOUNT ON;
DECLARE @out nvarchar(max);
EXEC [dbo].[test_for_pyodbc] @param_in = ?, @param_out = @out OUTPUT;
SELECT @out AS the_output;
"""
params = ("Burma!", )
crsr.execute(sql, params)
rows = crsr.fetchall()
while True:
    print(rows)
    if crsr.nextset():
        rows = crsr.fetchall()
    else:
        break

 

결과는 다음처럼 출력됩니다. OUTPUT 파라미터는 중간에서 실행되지만, 결과셋이 출력되고 맨 마지막에 OUTPUT 매개변수가 출력됩니다.

 

[('SP result set 1, row 1', ), ('SP result set 1, row 2', )]
[('SP result set 2, row 1', ), ('SP result set 2, row 2', )]
[('Output parameter value: You said "Burma!".', )]

 


영향받은 행의 개수 등을 출력할 때, 꼭 OUTPUT 파라미터를 써야 하나요?

OUTPUT 파라미터의 대표적인 예제인 영향받은 행의 수를 출력하는 경우에 유용하긴 하지만, 이렇게 사용할 수도 있습니다.

 

-- OUTPUT 방식 없이 UPDATE 후 영향받은 행을 결과셋으로 리턴
CREATE OR ALTER PROC pEmployeeSalaryUdt
AS
SET NOCOUNT ON
UPDATE Employee SET emp_salary = emp_salary * 0.5
SELECT @@ROWCOUNT as Affected_Rows;  -- 일반 결과셋으로 리턴
GO

-- 저장 프로시저 실행
EXEC pEmployeeSalaryUdt

 

이렇게 수행하면 1번 결과셋(OUTPUT 파라미터가 아니라)으로 영향받은 행의 수가 들어와 일반 결과셋 다루듯 처리하면 됩니다. UPDATE가 있지만, SET NOCOUNT ON으로 UPDATE 후 빈 결과셋은 전달되지 않고, 다음 라인의 SELECT 구문으로 영향받은 로우 숫자만 결과셋으로 출력됩니다.


어떤 방법을 사용할지는 선택입니다만, 실제 현업에서 OUTPUT을 이용하는 경우는 거의 보지 못했습니다. 데이터 수정(INSERT / UPDATE / DELETE) 후 영향받은 로우의 숫자 - 결과를 얻고 싶다면 이렇게 결과셋을 얻는 방법을 활용하세요.
 

 

SQL 강좌 책 구매

강좌가 도움이 되셨다면, 책으로 구매 가능합니다. 책 판매 수익금은 전액 코딩 교육 사회공헌 활동에 기부되며, 아래 링크에서 구매하시면 더 많은 금액이 기부됩니다. 

 

책구매 링크: 챗GPT와 함께하는 마이크로소프트 SQL Server 2022 

책구매링크.png

No. Subject Author Date Views
Notice SQL강좌: 챗GPT와 함께 배우는 SQL Server 무료 강좌 목차와 소개 (2023년 9월 업데이트) 코난(김대우) 2023.08.18 38698
Notice Python 무료 강좌 - 기초, 중급, 머신러닝(2023년 6월 업데이트) 코난(김대우) 2021.01.01 20797
2294 SQL강좌: 12-1. 인덱스 생성과 관리 - 인덱스(Index) 소개 file 코난(김대우) 2023.08.18 123
2293 SQL강좌: 11-8. 관계형 데이터베이스 이해 - 데이터베이스 구현 코난(김대우) 2023.08.18 56
2292 SQL강좌: 11-7. 관계형 데이터베이스 이해 - 데이터베이스 물리적 설계 코난(김대우) 2023.08.18 64
2291 SQL강좌: 11-6. 관계형 데이터베이스 이해 - 데이터베이스 논리적 설계 코난(김대우) 2023.08.18 49
2290 SQL강좌: 11-5. 관계형 데이터베이스 이해 - 데이터베이스 설계 요구사항(Requirements) 분석과 개념적 설계 코난(김대우) 2023.08.18 55
2289 SQL강좌: 11-4. 관계형 데이터베이스 이해 - 데이터베이스 설계 목표와 고려사항 코난(김대우) 2023.08.18 49
2288 SQL강좌: 11-3. 관계형 데이터베이스 이해 - 데이터베이스 설계 단계 file 코난(김대우) 2023.08.18 55
2287 SQL강좌: 11-2. 관계형 데이터베이스 이해 - 데이터베이스 생명주기 file 코난(김대우) 2023.08.18 53
2286 SQL강좌: 11-1. 관계형 데이터베이스 이해 - 관계형 모델 코난(김대우) 2023.08.18 73
2285 SQL강좌: 10-4. T-SQL 프로그래밍 - 공통 테이블 식(CTE-Common Table Expression) 코난(김대우) 2023.08.18 101
2284 SQL강좌: 10-3. T-SQL 프로그래밍 - 변수, 조건문(IF-ELSE), 반복문(WHILE), CASE 표현식 코난(김대우) 2023.08.18 92
2283 SQL강좌: 10-2. T-SQL 프로그래밍 - 일괄처리(Batch) 코난(김대우) 2023.08.18 67
2282 SQL강좌: 10-1. T-SQL 프로그래밍 - 소개 코난(김대우) 2023.08.18 69
2281 SQL강좌: 9-3. 커서 - 현업에서 커서 사용 및 주의사항 코난(김대우) 2023.08.18 102
2280 SQL강좌: 9-2. 커서 - 커서 구문 코난(김대우) 2023.08.18 69
2279 SQL강좌: 9-1. 커서(CURSOR) - 커서 소개 코난(김대우) 2023.08.18 97
2278 SQL강좌: 8-7. 저장 프로시저 - 프로시저 캐시와 RECOMPILE 코난(김대우) 2023.08.18 85
2277 SQL강좌: 8-6. 저장 프로시저 - 종속성(Dependency) 확인 코난(김대우) 2023.08.18 101
» SQL강좌: 8-5. 저장 프로시저 - OUTPUT 파라미터 코난(김대우) 2023.08.18 90
2275 SQL강좌: 8-4. 저장 프로시저 - 동적 SQL(Dynamic SQL)과 SQL 주입(Injection) 공격 코난(김대우) 2023.08.18 123





XE Login