저장 프로시저
저장 프로시저
저장 프로시저(stored procedure)는 T-SQL 문장 하나 이상을 묶은 그룹이거나, .NET 프레임워크 CLR(공용 언어 런타임) 메서드에 대한 참조예요. 다른 프로그래밍 언어의 구성과 비슷하게, 입력 파라미터를 받고 출력 파라미터 형태로 호출 프로그램에 여러 값을 돌려줄 수 있어요. 다른 프로시저 호출을 포함한 데이터베이스 작업을 수행하는 프로그래밍 문장을 담을 수 있고, 호출 프로그램에 성공·실패(그리고 실패 이유)를 알려주는 상태 값을 반환할 수 있어요.
저장 프로시저의 이점
서버·클라이언트 네트워크 트래픽 감소
프로시저 안의 명령은 단일 코드 배치로 실행돼요. 그래서 네트워크로는 프로시저 실행 호출 하나만 전송되니까 트래픽이 크게 줄어들어요. 프로시저 없이 코드를 그대로 두면 각 코드 줄이 전부 네트워크를 오가야 해요.
더 강한 보안
여러 사용자와 클라이언트 프로그램이 밑에 있는 데이터베이스 객체에 직접 권한이 없어도 프로시저를 통해 연산을 수행할 수 있어요. 프로시저가 수행되는 작업을 제어하고 밑에 있는 객체를 보호해요. 그래서 객체 하나하나에 권한을 부여할 필요가 없어지고 보안 계층이 단순해져요.
CREATE PROCEDURE에 EXECUTE AS 절을 지정하면 다른 사용자를 가장하거나, 밑에 있는 객체·명령에 직접 권한이 없어도 특정 데이터베이스 활동을 수행하게 해줄 수 있어요. 예를 들어 TRUNCATE TABLE은 부여 가능한 권한이 없어서 테이블에 ALTER 권한이 있어야 실행할 수 있는데, ALTER 권한을 주는 건 과할 수 있죠. TRUNCATE TABLE을 모듈 안에 넣고 그 모듈을 테이블 수정 권한이 있는 사용자로 실행하도록 지정하면, 모듈에 EXECUTE 권한만 주어도 테이블을 비울 수 있게 돼요.
프로시저를 네트워크로 호출하면 실행 호출만 보이기 때문에, 악의적인 사용자가 테이블·객체 이름을 보거나 자신의 T-SQL 문장을 끼워 넣거나 중요 데이터를 찾기 어려워요. 또 파라미터 입력이 리터럴 값(실행 가능한 코드가 아님)으로 취급되므로 SQL 인젝션 공격을 막는 데도 도움이 돼요. 프로시저 소스를 난독화하려면 암호화할 수도 있어요.
코드 재사용
반복되는 데이터베이스 연산은 프로시저로 캡슐화하기 좋은 대상이에요. 같은 코드를 불필요하게 다시 쓰지 않게 하고, 코드 불일치를 줄이며, 필요한 권한이 있는 사용자·애플리케이션이 그 코드에 접근하고 실행할 수 있게 해줘요.
유지보수 용이
클라이언트 애플리케이션이 프로시저를 호출하고 데이터베이스 연산을 데이터 계층에 두면, 밑에 있는 데이터베이스가 바뀌어도 프로시저만 갱신하면 돼요. 애플리케이션 계층은 분리되어 있어서 데이터베이스 레이아웃·관계·프로세스 변경을 몰라도 돼요.
성능 향상
기본적으로 프로시저는 처음 실행될 때 컴파일되고, 이후 실행에서 재사용하는 실행 계획을 만들어요. 쿼리 프로세서가 새 계획을 만들 필요가 없으니 처리 시간이 짧아져요. 다만 프로시저가 참조하는 테이블·데이터가 크게 바뀌면 미리 컴파일된 계획이 오히려 느려질 수 있고, 이때는 프로시저를 재컴파일해서 새 실행 계획을 강제하는 게 나을 수 있어요.
저장 프로시저의 종류
사용자 정의
사용자 정의 데이터베이스나 Resource 데이터베이스를 제외한 모든 시스템 데이터베이스에서 만들 수 있어요. T-SQL로 작성하거나 .NET CLR 메서드 참조로 개발할 수 있어요.
임시
임시 프로시저는 사용자 정의 프로시저의 한 형태로, tempdb에 저장돼요. 이름의 첫 글자가 # 하나면 로컬 임시 프로시저(현재 사용자 연결에만 보이고 연결이 끊기면 삭제), ## 두 개면 전역 임시 프로시저(생성 후 모든 사용자에게 보이고 마지막 세션이 끝나면 삭제)예요.
시스템
시스템 프로시저는 Database Engine에 포함돼 있고, 숨겨진 내부 Resource 데이터베이스에 물리적으로 저장되며 모든 시스템·사용자 데이터베이스의 sys 스키마에 논리적으로 나타나요. msdb 데이터베이스에는 경고·작업 스케줄링에 쓰이는 dbo 스키마의 시스템 프로시저도 있어요. 시스템 프로시저는 이름이 sp_로 시작하니, 사용자 정의 프로시저 이름에는 이 접두사를 쓰지 않는 게 좋아요. SQL Server에서 외부 프로그램과의 인터페이스를 제공하는 확장 프로시저는 xp_ 접두사를 써요.
확장 사용자 정의
확장 프로시저는 C 같은 프로그래밍 언어로 만든 외부 루틴으로, DLL 형태로 SQL Server 인스턴스가 동적으로 로드해서 실행해요. 다만 확장 저장 프로시저는 향후 SQL Server 버전에서 제거될 예정이라 새 개발에서는 쓰지 말고, 더 견고하고 안전한 대안인 CLR 프로시저를 사용하는 걸 권장해요.