저장 프로시저 기초

절차를 이름에 담습니다

4.1 의 연습에서 답글 넣기를 여섯 문장으로 적었습니다. 그 문장 묶음은 답글을 달 때마다 필요합니다. 복사해서 여기저기 두면 고칠 일이 생겼을 때 모두 찾아야 합니다.

저장 프로시저는 그 묶음에 이름을 붙여 데이터베이스에 두는 것입니다. 응용 프로그램은 이름을 부르고 값만 넘깁니다.

CREATE OR ALTER PROCEDURE Board.P_POST_LIST
    @board_num int
AS
BEGIN
    SET NOCOUNT ON;

    SELECT TOP (20) num, title, user_num, reg_date, hit_count, depth
    FROM Board.POSTS
    WHERE board_num = @board_num
    ORDER BY group_num DESC, sort_no;
END
GO

-- 부릅니다. 두 가지로 적을 수 있습니다.
EXEC Board.P_POST_LIST 2;
EXEC Board.P_POST_LIST @board_num = 1;

CREATE OR ALTER 는 없으면 만들고 있으면 고칩니다. 배포 스크립트에서 IF EXISTS … DROP … CREATE 를 적을 필요가 없어집니다. 지우고 다시 만들면 권한이 사라지므로 이쪽이 안전하기도 합니다.

매개 변수는 이름을 적어 넘기는 편이 낫습니다. 자리 순서로 넘기면 나중에 매개 변수를 하나 더할 때 부르는 쪽이 조용히 어긋납니다. 매개 변수는 4.3 에서 자세히 다룹니다.

계획을 다시 사용합니다

SQL Server 는 문장을 받으면 실행 계획을 만들어 캐시에 담아 둡니다. 같은 문장이 다시 오면 그것을 다시 사용합니다. 문제는 같은 문장인지 판단하는 기준이 글자라는 데 있습니다.

-- 실습 데이터베이스에서만 하십시오. 운영 서버의 캐시를 비우면
-- 모든 문장이 계획을 다시 만들면서 순간적으로 느려집니다.
DBCC FREEPROCCACHE;
GO
-- 값만 다른 문장 셋을 따로따로 보냅니다.
SELECT TOP (20) num, title FROM Board.POSTS WHERE board_num = 1 ORDER BY group_num DESC, sort_no;
GO
SELECT TOP (20) num, title FROM Board.POSTS WHERE board_num = 2 ORDER BY group_num DESC, sort_no;
GO
SELECT TOP (20) num, title FROM Board.POSTS WHERE board_num = 3 ORDER BY group_num DESC, sort_no;
GO
-- 프로시저로 같은 일을 셋 합니다.
EXEC Board.P_POST_LIST 1;
GO
EXEC Board.P_POST_LIST 2;
GO
EXEC Board.P_POST_LIST 3;
GO
-- 캐시에 무엇이 남았는지 봅니다.
SELECT cp.objtype AS 종류, cp.usecounts AS 사용횟수, cp.size_in_bytes AS 바이트
FROM sys.dm_exec_cached_plans cp
    CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE st.text LIKE '%Board.POSTS%';

같은 일을 하는데 계획이 셋 만들어졌고 172KB 를 차지합니다. 게시판이 셋이라 셋이지만, 값이 회원 번호였다면 회원 수만큼 생깁니다. 그렇게 쌓인 계획은 정작 필요한 계획을 캐시에서 밀어냅니다.

프로시저는 한 칸에서 세 번 사용되었습니다. 문장이 이름으로 고정되어 있고 값만 매개 변수로 오기 때문입니다.

매개 변수를 사용하면 프로시저가 아니어도 같은 효과를 얻습니다 — sp_executesql 이고 4.9 에서 다룹니다. 응용 프로그램의 ADO.NET 도 매개 변수를 넘기면 그렇게 보냅니다. 피해야 할 것은 값을 문장에 이어 붙이는 것이고, 그것은 계획 캐시만의 문제가 아닙니다(4.9 의 SQL 주입).

표 권한 없이 일을 시킵니다

응용 프로그램 계정에 표를 읽고 쓸 권한을 주면 그 계정으로 무엇이든 할 수 있습니다. 연결 문자열이 새는 순간 표가 통째로 나갑니다.

프로시저만 실행할 수 있게 하면 정해진 일만 할 수 있습니다. 3.8 에서 사용한 EXECUTE AS 로 확인합니다.

CREATE USER app_user WITHOUT LOGIN WITH DEFAULT_SCHEMA = Board;

-- 표 권한은 하나도 주지 않습니다. 프로시저 실행 권한만 줍니다.
GRANT EXECUTE ON OBJECT::Board.P_POST_LIST TO app_user;
GO

EXECUTE AS USER = 'app_user';
EXEC Board.P_POST_LIST 1;                        -- 됩니다
SELECT TOP (20) num, title FROM Board.POSTS;   -- 안 됩니다
REVERT;

프로시저 안에서는 읽히는데 밖에서는 막힙니다. 프로시저와 표의 소유자가 같으면 권한 검사를 건너뛰기 때문이고, 이것을 소유권 체인이라고 합니다. 3.8 에서 스키마를 권한 경계로 잡으라고 한 것이 여기서 열매를 맺습니다 — Board 스키마의 표와 프로시저는 소유자가 같습니다.

줄 수 있는 권한이 "이 일을 해라" 단위가 됩니다. 목록을 볼 수 있지만 지울 수는 없는 계정, 답글은 달 수 있지만 남의 글은 못 고치는 계정을 만들 수 있습니다. 권한은 6.1 에서 다룹니다.

고칠 곳이 한 군데입니다

3.10 에서 지운 글을 목록에서 빼기로 했습니다. 목록 문장이 응용 프로그램 코드 여기저기에 흩어져 있다면 그 모두에 AND del_date IS NULL 을 더해야 하고, 하나라도 빠뜨리면 지운 글이 그 화면에만 보입니다.

프로시저 안에 있으면 한 줄을 고치고 배포합니다. 응용 프로그램은 다시 배포하지 않습니다. 뷰(3.7)와 같은 이야기이고, 프로시저는 거기에 절차와 매개 변수를 더한 것입니다.

정의는 언제든 볼 수 있습니다.

SELECT definition FROM sys.sql_modules
WHERE object_id = OBJECT_ID('Board.P_POST_LIST');

-- 프로시저 목록
SELECT s.name + '.' + p.name AS 프로시저, p.create_date AS 만든날, p.modify_date AS 고친날
FROM sys.procedures p JOIN sys.schemas s ON s.schema_id = p.schema_id
WHERE p.is_ms_shipped = 0;

정의가 데이터베이스에만 있으면 안 됩니다. 프로시저도 소스이므로 파일로 두고 형상 관리에 넣으십시오. 데이터베이스 프로젝트로 관리하는 방법은 6.4 에서 다룹니다.

SET NOCOUNT ON 을 첫 줄에

문장을 실행할 때마다 SQL Server 는 몇 행이 영향을 받았는지 알리는 메시지를 함께 보냅니다. 프로시저 안에서 문장이 열 개면 메시지도 열 개입니다.

-- 같은 내용의 프로시저 둘. 한쪽에만 SET NOCOUNT ON 이 있습니다.
CREATE OR ALTER PROCEDURE Board.P_COUNT_ON
AS
BEGIN
    UPDATE Board.POSTS SET hit_count = hit_count WHERE board_num = 1;
    SELECT COUNT(*) AS 글수 FROM Board.POSTS WHERE board_num = 1;
END

메시지가 줄어드는 것만이 아닙니다. 이 메시지는 결과와 함께 연결을 타고 나가므로, 문장이 많은 프로시저에서는 오가는 양이 늘어납니다. 응용 프로그램의 데이터 접근 코드가 이 메시지를 결과 집합으로 잘못 읽어 동작이 어긋나는 경우도 있습니다.

프로시저의 첫 줄은 SET NOCOUNT ON 입니다. 예외는 영향받은 행 수를 부르는 쪽이 실제로 사용하는 경우인데, 그때도 @@ROWCOUNT 를 출력 매개 변수로 넘기는 편이 분명합니다(4.3).

만들 때 확인되는 것과 안 되는 것

프로시저를 만들 때 SQL Server 가 무엇을 검사하는지 알아 두어야 합니다. 검사가 고르지 않습니다.

-- 표 이름을 틀리게 적습니다. POSTZ 는 없는 표입니다.
CREATE OR ALTER PROCEDURE Board.P_TYPO
AS
BEGIN
    SET NOCOUNT ON;
    SELECT COUNT(*) FROM Board.POSTZ;
END
GO
-- 만들어집니다. 부를 때 터집니다.
EXEC Board.P_TYPO;

없는 표를 참조해도 만들어집니다. 프로시저가 서로를 부르거나 뒤에 만들 표를 참조하는 경우를 허용하기 위해서입니다. 그런데 열은 다릅니다.

-- 표는 맞고 열 이름을 틀리게 적습니다.
CREATE OR ALTER PROCEDURE Board.P_TYPO2
AS
BEGIN
    SET NOCOUNT ON;
    SELECT titel FROM Board.POSTS;
END

표 이름 오타는 배포될 수 있고 열 이름 오타는 배포될 수 없습니다. 그래서 배포 뒤에 한 번씩 불러 보는 확인이 필요합니다. 오탈자를 미리 찾으려면 sys.sql_expression_dependencies 로 참조하는 이름이 실재하는지 훑는 방법이 있습니다.

이름 규칙 둘

• sp_ 로 시작하지 마십시오. SQL Server 는 그 접두사를 보면 master 를 먼저 찾습니다. 찾는 일이 한 번 더 생기고, 나중에 같은 이름의 시스템 프로시저가 생기면 그쪽이 불립니다. 실습 데이터베이스와 이 사이트의 프로시저가 P_ 로 시작하는 것이 그 까닭입니다.
• 안쪽에서도 두 부분 이름으로 적으십시오. 프로시저 안에서 스키마를 빼면 프로시저가 속한 스키마에서 먼저 찾으므로 Board 안의 표는 우연히 맞습니다. 그러나 Member.USERS 를 참조하는 순간 어긋나고, 3.8 에서 본 대로 계획도 따로 잡힙니다.

직접 해보기

이 단원에서 만든 프로시저를 지웁니다.
DROP PROCEDURE Board.P_POST_LIST, Board.P_POST_READ, Board.P_POST_REPLY, Board.P_TYPO;