MSSQL 4.3 · 4부. T-SQL 프로그래밍

매개 변수

입력·출력·기본값을 다루고, RETURN 과 OUTPUT 이 어떻게 다른지 봅니다. 조용히 틀리는 자리 셋이 여기 있습니다.

예상 학습 시간 20분 난이도 중급

개념 설명

값만 바꿔 같은 절차를 부릅니다

4.2 의 P_POST_LIST 는 게시판 번호를 받았습니다. 절차는 고정되어 있고 값만 바뀝니다. 그 값을 받는 자리가 매개 변수이고, 넘기는 방향에 따라 셋으로 나뉩니다.

입력 — 부르는 쪽이 넘깁니다. 기본값을 둘 수 있습니다.
출력(OUTPUT) — 프로시저가 돌려줍니다. 여러 개를 둘 수 있고 형식도 자유입니다.
반환 코드(RETURN) — 정수 하나만 돌려줍니다. 성공했는지 알리는 자리입니다.

입력

기본값과 부르는 방법

SQL

CREATE OR ALTER PROCEDURE Board.P_TEST
    @a int,                          -- 기본값이 없으면 반드시 넘겨야 합니다
    @b int = 0,
    @c nvarchar(20) = N'없음'
AS
BEGIN
    SET NOCOUNT ON;
    SELECT @a AS a, @b AS b, @c AS c;
END
GO
EXEC Board.P_TEST;                          -- (가)
EXEC Board.P_TEST 1;                        -- (나)
EXEC Board.P_TEST 1, , N'값';                 -- (다)
EXEC Board.P_TEST @a = 1, @c = N'값';        -- (라)
EXEC Board.P_TEST 1, default, N'값';          -- (마)

결과 (가) — 필수를 빼면

Procedure or function 'P_TEST' expects parameter '@a', which was not supplied.

결과 (나) — 기본값이 있는 것만 빼면

a	b	c
1	0	없음

결과 (다) — 자리를 비워 두려 하면

메시지 102 Incorrect syntax near ','.

결과 (라)(마) — 이름을 적거나 default 를 적으면

a	b	c
1	0	값

자리 순서로는 가운데를 건너뛸 수 없습니다. 이름을 적거나 default 를 적어 자리를 채워야 합니다. 이름을 적는 편이 낫습니다 — 매개 변수가 하나 늘어도 부르는 쪽이 어긋나지 않고, 문장만 보고 무엇을 넘기는지 알 수 있습니다.

길이를 적지 않은 문자열은 한 자입니다

SQL

CREATE OR ALTER PROCEDURE Board.P_LEN
    @name  nvarchar,          -- 길이를 적지 않았습니다
    @name2 nvarchar(50)
AS
BEGIN
    SET NOCOUNT ON;
    SELECT @name AS 길이없음, LEN(@name) AS 길이1,
           @name2 AS 길이50, LEN(@name2) AS 길이2;
END
GO
EXEC Board.P_LEN @name = N'홍길동입니다', @name2 = N'홍길동입니다';

결과

길이없음	길이1	길이50	길이2
홍	1	홍길동입니다	6

오류 없이 첫 글자만 남았습니다.

길이를 적지 않은 nvarchar 는 nvarchar(1) 입니다. 넘긴 값이 조용히 잘리고, 그 값으로 조회하면 결과가 이상해집니다. 1.3 에서 형식마다 길이를 적으라고 한 것이 매개 변수에도 그대로 적용됩니다.

= default 로 선언하지 마십시오

SQL

CREATE OR ALTER PROCEDURE Board.P_DEF2
    @s nvarchar(20) = default,
    @t nvarchar(20) = NULL,
    @u nvarchar(20) = N''
AS
BEGIN
    SET NOCOUNT ON;
    SELECT ISNULL(@s, N'NULL') AS [= default 로],
           ISNULL(@t, N'NULL') AS [= NULL 로],
           N'[' + ISNULL(@u, N'NULL') + N']' AS [= N'' 로];
END
GO
EXEC Board.P_DEF2;

결과

= default 로	= NULL 로	= N'' 로
NULL	NULL	[]

앞의 둘은 결과가 같습니다. 만들 때 오류도 나지 않습니다.

= default 는 결국 NULL 이면서 읽는 사람에게는 다른 뜻으로 보입니다. 부를 때 사용하는 default 키워드와 낱말이 겹쳐 더 헷갈립니다. 값을 넘기지 않아도 되는 매개 변수는 = NULL 로 적으십시오. 빈 문자열이 뜻을 가지는 자리라면 = N'' 라고 분명히 적습니다.

출력

OUTPUT 은 양쪽에 적어야 합니다

결과 집합 말고 값 하나를 돌려주고 싶을 때 사용합니다. 건수, 새로 만들어진 번호, 처리 여부 같은 것입니다.

SQL

CREATE OR ALTER PROCEDURE Board.P_COUNT
    @board_num int,
    @cnt       int OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT @cnt = COUNT(*) FROM Board.POSTS WHERE board_num = @board_num;
END
GO
DECLARE @n int = -1;
EXEC Board.P_COUNT @board_num = 2, @cnt = @n OUTPUT;   -- (가)
SELECT @n AS 받은값;
GO
DECLARE @n int = -1;
EXEC Board.P_COUNT @board_num = 2, @cnt = @n;          -- (나) OUTPUT 을 빠뜨렸습니다
SELECT @n AS 받은값;

결과

부른 방법	받은값
(가) OUTPUT 을 적고	315
(나) OUTPUT 을 빠뜨리고	-1

아래쪽은 오류가 나지 않습니다. 초기값이 그대로 남습니다.

선언에 OUTPUT 을 적었어도 부를 때 다시 적어야 합니다. 빠뜨리면 값을 넘기기만 하고 돌려받지 않습니다. 오류가 나지 않으므로 변수에 담긴 것이 이전 값인지 프로시저가 준 값인지 구분되지 않습니다. 4.1 의 SELECT 대입과 같은 모양의 함정입니다.

반환 코드

RETURN 은 정수 하나뿐입니다

RETURN 은 두 가지 일을 합니다. 그 자리에서 프로시저를 끝내고, 정수 하나를 돌려줍니다. 4.2 의 연습에서 관문에 걸렸을 때 사용한 그 문장입니다.

SQL

DECLARE @rc int, @n int;
EXEC @rc = Board.P_COUNT @board_num = 2, @cnt = @n OUTPUT;
SELECT @rc AS 반환코드, @n AS 글수;

결과

반환코드	글수
0	315

RETURN 을 적지 않아도 0 이 돌아옵니다.

정수 말고는 받지 않습니다.

SQL

-- 문자열을 돌려주려 하면
CREATE OR ALTER PROCEDURE Board.P_RET_STR AS BEGIN RETURN N'실패'; END
GO
DECLARE @rc int; EXEC @rc = Board.P_RET_STR;
GO

-- NULL 을 돌려주려 하면
CREATE OR ALTER PROCEDURE Board.P_RET_NULL AS
BEGIN DECLARE @v int = NULL; RETURN @v; END
GO
DECLARE @rc int = -99; EXEC @rc = Board.P_RET_NULL; SELECT @rc AS 반환코드;

오류 — 문자열

메시지 245 Conversion failed when converting the nvarchar value '실패' to data type int.

경고 — NULL

The 'P_RET_NULL' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead. 반환코드 -------- 0

NULL 은 막히지 않고 0 으로 바뀝니다. 성공과 구분되지 않습니다.

무엇을 어디에 담습니까

	RETURN	OUTPUT
개수	하나	여럿
형식	int 만	아무 형식
NULL	0 으로 바뀜	그대로
담는 것	성공했는지	결과 값

값은 OUTPUT 으로, 성공 여부는 RETURN 으로 나누십시오. 건수를 RETURN 으로 돌려주면 건수가 0 일 때와 실패했을 때를 구분할 수 없습니다.

반환 코드로 오류를 알리는 방식은 4.5 이후로 권하지 않습니다. 부르는 쪽이 @rc 를 확인하지 않으면 그냥 지나가기 때문입니다. 실패는 THROW 로 던지는 편이 확실하고, 그것이 4.5 의 주제입니다. RETURN 은 오류가 아닌 갈래(찾지 못함, 이미 처리됨)를 알리는 데 사용하십시오.

선택적 조건

넘기지 않으면 조건을 걸지 않습니다

검색 화면은 조건이 여럿이고 사용자가 일부만 채웁니다. 조건 조합마다 프로시저를 만들 수는 없으므로 이렇게 적게 됩니다.

SQL

CREATE OR ALTER PROCEDURE Board.P_SEARCH
    @board_num int           = NULL,
    @keyword   nvarchar(100) = NULL
AS
BEGIN
    SET NOCOUNT ON;
    SELECT COUNT(*) AS 건수
    FROM Board.POSTS
    WHERE (@board_num IS NULL OR board_num = @board_num)
      AND (@keyword   IS NULL OR title LIKE N'%' + @keyword + N'%');
END
GO
EXEC Board.P_SEARCH;                                    -- 507
EXEC Board.P_SEARCH @board_num = 2;                      -- 315
EXEC Board.P_SEARCH @keyword = N'인덱스';                -- 17
EXEC Board.P_SEARCH @board_num = 2, @keyword = N'인덱스';  -- 0

결과 — 잘 돌아갑니다

넘긴 것	건수
없음	507
게시판 2	315
낱말 '인덱스'	17
게시판 2 + '인덱스'	0

인덱스가 든 제목 17건은 모두 공지사항에 있어 마지막이 0 입니다.

결과는 맞습니다. 그런데 인덱스를 사용하지 못합니다.

SQL

-- 같은 조건을 그대로 적었을 때
SELECT COUNT(*) FROM Board.POSTS WHERE board_num = 2;

-- 선택적 조건으로 감쌌을 때
DECLARE @b int = 2;
SELECT COUNT(*) FROM Board.POSTS WHERE (@b IS NULL OR board_num = @b);

계획 — 조건을 그대로

|--Index Seek(OBJECT:([POSTS].[IX_POSTS_list]), SEEK:([POSTS].[board_num]=(2)) ORDERED FORWARD)

계획 — 선택적 조건

|--Clustered Index Scan(OBJECT:([POSTS].[PK_POSTS]), WHERE:([@b] IS NULL OR [POSTS].[board_num]=[@b]))

읽기는 11장과 14장입니다.

계획은 한 번 만들어져 모든 호출에 사용됩니다. 그런데 이 문장은 @board_num 이 NULL 인 호출과 값이 있는 호출에 필요한 계획이 다릅니다. 엔진은 둘 다 감당할 계획을 골라야 하므로 인덱스를 포기하고 전체를 읽는 쪽을 잡습니다.

OPTION (RECOMPILE) 을 붙이면 부를 때마다 그 값에 맞는 계획을 만듭니다.

SQL

CREATE OR ALTER PROCEDURE Board.P_SEARCH2
    @board_num int = NULL
AS
BEGIN
    SET NOCOUNT ON;
    SELECT COUNT(*) AS 건수 FROM Board.POSTS
    WHERE (@board_num IS NULL OR board_num = @board_num)
    OPTION (RECOMPILE);
END

결과 — 읽은 페이지

부른 방법	논리적 읽기
RECOMPILE 없이, 게시판 2	14
RECOMPILE 붙이고, 게시판 2	11
RECOMPILE 붙이고, 조건 없이	2

값을 보고 조건을 지워 버리므로 조건이 없을 때는 2장에 끝납니다.

공짜는 아닙니다. 부를 때마다 계획을 만드므로 4.2 에서 본 계획 재사용을 그 문장에 한해 포기하는 것입니다. 조건 조합이 많고 자주 부르지 않는 검색에 어울리고, 초당 수십 번 도는 문장에는 맞지 않습니다. 계획이 어긋나는 다른 경우들과 함께 5.3 에서 다시 다룹니다.

연습

직접 해보기

1. 찾았는지 알려 주는 상세 프로시저 난이도 하

4.2 의 P_POST_READ 는 없는 번호를 넣어도 빈 결과만 나옵니다. 찾았는지를 출력 매개 변수로 알리고, 반환 코드도 나누어 부르는 쪽이 판단할 수 있게 고쳐 보세요.

CREATE OR ALTER PROCEDURE Board.P_POST_READ2 @num int, @found bit = 0 OUTPUT AS BEGIN SET NOCOUNT ON; UPDATE Board.POSTS SET hit_count = hit_count + 1 WHERE num = @num; SET @found = CASE WHEN @@ROWCOUNT = 1 THEN 1 ELSE 0 END; -- UPDATE 바로 뒤(4.1) IF @found = 0 RETURN 1; -- 찾지 못함 SELECT num, title, hit_count FROM Board.POSTS WHERE num = @num; RETURN 0; -- 정상 END GO DECLARE @f bit, @rc int; EXEC @rc = Board.P_POST_READ2 @num = 250, @found = @f OUTPUT; -- num title hit_count -- 250 제약 조건 이렇게 쓰는 게 맞나요 251 -- 반환코드 0 · 찾음 1 EXEC @rc = Board.P_POST_READ2 @num = 99999, @found = @f OUTPUT; -- 결과 집합 없음 · 반환코드 1 · 찾음 0 -- 찾지 못한 것은 오류가 아니라 갈래이므로 RETURN 으로 알립니다. -- 부르는 쪽이 @rc 를 보지 않을 수 있으니 @found 도 함께 둡니다. -- EXEC 에서 OUTPUT 을 빠뜨리면 @f 가 이전 값 그대로 남습니다.

2. 검색 프로시저 난이도 중

게시판과 낱말로 글을 찾는 프로시저를 만들어 보세요. 둘 다 선택입니다. 목록 5건과 함께 전체 건수를 출력 매개 변수로 냅니다. 인덱스를 사용하도록 만드십시오.

건수와 목록은 다른 문장입니다. 목록에 TOP 이 있으므로 건수를 따로 세야 합니다. 두 문장 모두에 붙여야 하는 것이 있습니다.

CREATE OR ALTER PROCEDURE Board.P_POST_SEARCH @board_num int = NULL, @keyword nvarchar(100) = NULL, @total int = 0 OUTPUT AS BEGIN SET NOCOUNT ON; SELECT @total = COUNT(*) FROM Board.POSTS WHERE (@board_num IS NULL OR board_num = @board_num) AND (@keyword IS NULL OR title LIKE N'%' + @keyword + N'%') OPTION (RECOMPILE); SELECT TOP (5) num, title FROM Board.POSTS WHERE (@board_num IS NULL OR board_num = @board_num) AND (@keyword IS NULL OR title LIKE N'%' + @keyword + N'%') ORDER BY group_num DESC, sort_no OPTION (RECOMPILE); END GO DECLARE @t int; EXEC Board.P_POST_SEARCH @total = @t OUTPUT; -- 507 EXEC Board.P_POST_SEARCH @board_num = 1, @keyword = N'인덱스', @total = @t OUTPUT; -- 17 -- OPTION (RECOMPILE) 은 문장마다 붙입니다. 프로시저에 한 번 적는 것이 아닙니다. -- 건수와 목록을 한 문장으로 내려고 COUNT(*) OVER () 를 쓸 수도 있지만(2.7), -- TOP 5 만 필요한데 전체를 세게 되어 이 경우에는 손해입니다. 5.6 에서 다시 봅니다. -- @keyword 는 앞에 % 가 붙어 인덱스로 찾을 수 없습니다(3.6 · 5.4). -- 제목 검색을 제대로 하려면 전문 검색이 필요하고 5.8 에서 다룹니다.

이 단원과 4.2 에서 만든 프로시저를 지웁니다.
DROP PROCEDURE Board.P_TEST, Board.P_LEN, Board.P_COUNT, Board.P_RET_STR, Board.P_RET_NULL,
Board.P_SEARCH, Board.P_SEARCH2, Board.P_POST_READ2, Board.P_POST_SEARCH;

요약

기본값이 없는 매개 변수는 반드시 넘겨야 합니다. 자리 순서로는 가운데를 건너뛸 수 없고(102) 이름을 적거나 default 를 적어야 합니다.
이름을 적어 부르십시오. 매개 변수가 늘어도 부르는 쪽이 어긋나지 않습니다.
길이를 적지 않은 nvarchar 는 한 자입니다. 넘긴 값이 조용히 잘립니다.
= default 로 선언하면 결국 NULL 이면서 뜻만 흐려집니다. = NULL 로 적으십시오.
OUTPUT 은 선언과 호출 양쪽에 적어야 합니다. 호출에서 빠뜨리면 오류 없이 값이 돌아오지 않습니다.
RETURN 은 정수 하나뿐입니다. 문자열은 245, NULL 은 조용히 0 이 됩니다.
값은 OUTPUT, 성공 여부는 RETURN 으로 나누십시오. 오류를 알리는 것은 4.5 의 THROW 가 맞습니다.
선택적 조건(@p IS NULL OR 열 = @p)은 인덱스를 사용하지 못합니다. OPTION (RECOMPILE) 을 문장마다 붙이면 살아납니다(14장 → 11장, 조건이 없으면 2장).