안녕하세요. SQLER의 코난 김대우입니다.
이번 강좌에서는, 8-2. 저장 프로시저 - 생성과 실행을 진행 하겠습니다.
SQLER에서 진행되는, 챗GPT와 함께 배우는 SQL Server 강좌 목록
TL;DR
다양한 방식으로 저장 프로시저를 생성하는 방법을 설명하며, CREATE PROC 일반 생성, ALTER 구문을 이용한 수정, DROP 구문을 이용한 삭제, sp_helptext 구문을 활용한 생성 구문 조회, 그리고 저장 프로시저 생성 구문 암호화 방법을 진행합니다.
이번 강좌에서는 저장 프로시저를 여러 방식으로 생성합니다.
저장 프로시저 생성 구문
|
CREATE [ OR ALTER ] { PROC | PROCEDURE } <procedure_option> ::= |
늘 말씀드리지만, 모든 옵션을 사용하지 않습니다. 대부분 패턴이 정해져 있으니 걱정 마세요. 예제를 통해 차근차근 설명합니다.
일반적인 저장 프로시저 생성
아래와 같이 CREATE PROC 구문으로 저장 프로시저를 생성합니다.
USE AdventureWorks; GO -- 테이블이 존재하면 삭제 IF OBJECT_ID(N'dbo.Employee', N'U') IS NOT NULL DROP TABLE dbo.Employee; -- employee 테이블 생성 CREATE TABLE Employee ( emp_num INT PRIMARY KEY, emp_login_pwd NVARCHAR(15), emp_name NVARCHAR(10), email NVARCHAR(15), emp_salary BIGINT ); GO -- employee 테이블에 예제 데이터 삽입 INSERT INTO Employee (emp_num, emp_login_pwd, emp_name, email, emp_salary) VALUES (1, N'password1234', N'김대우', N'kdw@example.com', 20000000), (2, N'password!@#$', N'손석구', N'ssk@example.com', 100000000), (3, N'asdfqwer!@#$', N'박은빈', N'peb@example.com', 120000000); GO -- 테이블 직접 조회 SELECT * FROM Employee; GO -- 저장 프로시저 생성 CREATE PROC pEmployeeInfo AS SELECT emp_num, emp_name, email FROM Employee; GO -- 저장 프로시저 실행 EXEC pEmployeeInfo; GO
여기까지는 지난 강좌에서 작업했던 뷰와 유사해 보입니다. 프로시저를 생성하고 실행하기 어렵지 않습니다.
저장 프로시저 수정
대부분의 데이터베이스 개체 수정 구문처럼 ALTER 구문을 사용합니다.
-- 저장 프로시저 수정 ALTER PROC pEmployeeInfo AS SELECT emp_name, email FROM Employee; GO -- 저장 프로시저 실행 EXEC pEmployeeInfo; GO
저장 프로시저를 DROP 하고 재생성할 수도 있지만, 여러 종속성 개체들과 연결성 문제 또는 데이터베이스 사용자에게 허용한(GRANT)한 사용권한 문제가 발생할 수 있습니다. 초기 개발단계가 아니면 주로 ALTER 구문을 이용하게 되실 거에요.
또는 CREATE OR ALTER 구문도 사용할 수도 있습니다.
-- 저장 프로시저 생성 또는 존재할 경우 수정 CREATE OR ALTER PROC pEmployeeInfo AS SELECT emp_name, email FROM Employee; GO
저장 프로시저 삭제
삭제 구문은 DROP을 이용합니다.
-- 저장 프로시저 삭제 DROP PROC pEmployeeInfo; GO
저장 프로시저 생성 구문 조회
저장 프로시저 생성 구문을 조회할 수 있습니다.
-- 저장 프로시저 생성 또는 존재할 경우 수정 CREATE OR ALTER PROC pEmployeeInfo AS SELECT emp_name, email FROM Employee; GO -- 저장 프로시저 생성 구문 조회 EXEC sp_helptext 'dbo.pEmployeeInfo'; GO
이렇게 sp_helptext 구문으로 프로시저 생성 구문을 확인합니다.
저장 프로시저 생성구문 암호화
저장 프로시저의 장점으로 보안성을 높일 수 있다고 말씀드렸습니다. 프로시저 생성 구문을 암호화할 수 있을까요? 가능합니다. 바로 저장 프로시저 생성 구문을 암호화해보겠습니다.
-- 저장 프로시저 생성 구문 암호화 CREATE OR ALTER PROC pEmployeeInfo WITH ENCRYPTION AS SELECT emp_name, email FROM Employee; GO -- 저장 프로시저 생성 구문 조회 EXEC sp_helptext 'dbo.pEmployeeInfo'; GO 결과 The text for object 'dbo.pEmployeeInfo' is encrypted.
이렇게 WITH ENCRYPTION을 이용해 저장 프로시저 구문을 암호화할 수 있습니다.
저장 프로시저 종류
저장 프로시저는 4가지 종류가 있습니다.
1. 사용자 정의 저장 프로시저
지금까지 생성하고 실행했던 CREATE PROC로 생성하는 프로시저를 사용자 정의 저장 프로시저라고 부릅니다. 대부분 줄여서 저장 프로시저라고 부르면, 이 사용자 정의 저장 프로시저라고 생각하면 됩니다.
2. 시스템 저장 프로시저(System stored procedure)
SQLER 강좌를 진행하면서 여러 번 사용한 “sp_”로 시작하는 sp_help, sp_helptext와 같은 프로시저를 시스템 저장 프로시저라고 부릅니다. SQL Server의 기능을 확장하거나, 정보를 볼 필요가 있을 때 사용합니다.
3. 일반 확장 시스템 저장 프로시저(General Extended stored procedure)
SQLER 강좌에서는 아직 다루지 않았습니다, “xp_”로 시작하는 저장 프로시저로 SQL Server의 여러 유지 관리 작업을 위해 SQL Server 인스턴스 외부 프로그램 인터페이스를 제공하는 저장 프로시저입니다. 예를 들어, 아래와 같은 일반 확장 시스템 저장 프로시저가 있습니다.
-- SQL Server의 상세 버전 정보 출력 EXEC xp_msver;
또는, 대표적인 일반 확장 시스템 저장 프로시저로 잘 알려진 - 윈도 OS의 명령 프롬프트 작업을 SQL 쿼리로 수행하는 xp_cmdshell도 있습니다. - 제한적으로 사용합니다.
-- 윈도 OS의 명령 프롬프트 작업을 SQL 쿼리로 수행 EXEC xp_cmdshell 'dir c:\project'; 결과 메시지 15281, 수준 16, 상태 1, 프로시저 xp_cmdshell, 줄 1 [배치 시작 줄 17] SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', search for 'xp_cmdshell' in SQL Server Books Online. -- xp_cmdshell을 수행하려면 다음 단계 진행. 반드시 제한된 용도로만 사용. EXECUTE sp_configure 'show advanced options', 1; RECONFIGURE; GO EXECUTE sp_configure 'xp_cmdshell', 1; RECONFIGURE; GO -- 실행 - C드라이브의 project 폴더 dir 수행 EXEC xp_cmdshell 'dir c:\project'; -- 비활성화 EXECUTE sp_configure 'xp_cmdshell', 0; RECONFIGURE; EXECUTE sp_configure 'show advanced options', 0; RECONFIGURE; GO
확장 저장 프로시저를 직접 개발하고 SQL Server 인스턴스에 등록해 사용할 수도 있습니다. 개발은 C / C++ 언어로 개발합니다. 확장 저장 프로시저는 SQLER의 기본 강좌 범위를 벗어나므로, 진행하지 않습니다. 직접 개발해서 사용도 가능하구나 정도로만 이해하세요.
☑️ 챗GPT 활용: C, C++ 언어에 대해서 알려줘
일반 확장 저장 프로시저 주의사항
신뢰할 수 없는 확장 저장 프로시저를 SQL Server에 등록해 사용하지 마시고, 시스템에서 저장하는 확장 저장 프로시저도 가능한 사용하지 마세요. 확장 저장 프로시저는 DLL로 생성해 SQL Server에 등록해 실행되고, SQL Server가 중지되거나 DLL을 명시적으로 언로드 하기 전까지 SQL Server의 메모리 버퍼 공간에 상주하게 됩니다. 여기까지만 읽어도 등에 땀이 흐르는 분이 계실 겁니다.
만약, 확장 저장 프로시저 DLL에서 오류나 예외가 발생하면, 기본적으로 SQL Server가 핸들(handle) 하지만, SQL Server 인스턴스에 영향을 주거나 메모리 누수(memory leak)와 같은 문제가 발생하게 될 수도 있습니다. 어쩔 수 없이 사용해야 한다면 철저하게 다양한 상황에서 테스트하시고, 제한된 범위 내에서만 사용하세요.
☑️ 챗GPT 활용: DLL(Dynamic Link Library)의 위험성에 대해서 알려줘
☑️ 챗GPT 활용: 메모리 누수(memory leak) 문제에 대해서 알려줘
4. CLR(Common Language Runtime 공용 언어 런타임) 저장 프로시저
SQL Server가 제공하는 T-SQL에서 제공하지 않는 기능을 사용해야 하거나, 프로그래밍 언어의 여러 기능을 활용하는 함수나 루틴을 T-SQL 구문에서 사용할 필요가 있습니다. 하지만, 확장 저장 프로시저는 C / C++로 개발해야 해서 제작이 어렵고, 사용이 위험하며, 제한적입니다.
CLR 함수나 CLR 저장 프로시저는 확장 저장 프로시저의 단점을 제거해 안전하고, C#이나 Visual Basic .NET 언어로 개발해 프로그래밍 언어의 기능을 T-SQL에서 사용 가능한 안전한 확장 기능을 제공합니다. 국내외에서도 SQL Server가 기본 제공하지 않는 암호화 기능과 같은 함수 제작에 CLR 기능이 자주 사용됩니다.
C#에 익숙하다면 생성 자체가 어렵지는 않습니다. CLR 사용자 정의 함수나 CLR 저장 프로시저는 SQLER에서 별도의 포스트로 한번 풀어보도록 하겠습니다.
자 지금까지 간단히 저장프로시저 생성과 종류에 대해서 살펴보았습니다. 여기까지는 뷰와 비슷하다는 느낌이 드실 거에요. 하지만, 이제 저장 프로시저에만 있는 매개변수를 받아 처리하는 부분을 진행하겠습니다.
SQL 강좌 책 구매
강좌가 도움이 되셨다면, 책으로 구매 가능합니다. 책 판매 수익금은 전액 코딩 교육 사회공헌 활동에 기부되며, 아래 링크에서 구매하시면 더 많은 금액이 기부됩니다.
책구매 링크: 챗GPT와 함께하는 마이크로소프트 SQL Server 2022
