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