[MSSQL] CRUD 저장 프로시저(Stored Procedure) 작성법

MSSQL을 사용하다 보면 흔히 접할 수 있는것 중의 하나가 바로 저장 프로시저(Stored Procedure) 입니다. 저장 프로시저는 개발자가 자주 쓰는 쿼리문의 집합으로서 어떠한 동작을 일괄 처리하는 용도로 사용합니다. 프로그램과 DB를 연결하여 데이터를 관리하는 방법은 반복되는 작업이므로 데이터베이스 테이블당 저장 프로시저를 보통 CRUD 4개의 프로시저를 생성하여 관리합니다. 지금부터 저장 프로시저를 작성하는 방법에 대해 알아보도록 하겠습니다.

 

 

1. 테스트 테이블 및 데이터 입력

아래처럼 테스트 테이블을 만들고 데이터를 입력합니다. USER_NUM은 IDENTITY 속성으로 자동 채번되므로 테스트 데이터 입력시 제외합니다. 

--테스트 테이블 생성 IF NOT EXISTS(SELECT TOP 1 * FROM SYS.OBJECTS WHERE NAME='SAMPLE_TEST') BEGIN     CREATE TABLE SAMPLE_TEST     (          USER_NUM       INT IDENTITY(1,1) NOT NULL  --유저 NUM         ,USER_NM         NVARCHAR(20)    --유저명         ,BITRH_YY         NVARCHAR(4)      --출생년도         ,E_MAIL            NVARCHAR(50)    --이메일         PRIMARY KEY(USER_NUM)     ) END GO INSERT INTO SAMPLE_TEST(USER_NM, BITRH_YY, E_MAIL)VALUES('고길동', '1982', 'gildong82@gmail.com') INSERT INTO SAMPLE_TEST(USER_NM, BITRH_YY, E_MAIL)VALUES('둘리', '2000', 'duli00@gmail.com')

 

2. 저장 프로시저(Stored Procedure) 작성 예시

저는 개인적으로 아래와 같은 형태의 구문을 작성합니다. 배포시 발생하는 오류를 줄이기 위해 해당하는 프로젝트에 SQL쿼리를 같이 포함시켜 놓고 배포시 해당하는 프로젝트에 대한 쿼리를 전체 배포합니다.  

IF  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'프로시저명'))   DROP PROCEDURE 프로시저명 GO   CREATE PROCEDURE 프로시저명      @파라미터 인자값                   파라미터 타입 ----WITH ENCRYPTION   AS BEGIN     --프로시저 로직 작성 END GO

 

- 조회 프로시저 작성

IF  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'USP_SAMPLE_TEST_SELECT'))   DROP PROCEDURE USP_SAMPLE_TEST_SELECT GO /*##############################################################   ◆ 프로그램ID :   ◆ 프로그램명 :   ◆ 설        명 :   ---------------------------------------------------------------------------------------------   ◆ 변경일자      생성자(변경자)      변경내역 ---------------------------------------------------------------------------------------------   EXEC  USP_SAMPLE_TEST_SELECT   ###############################################################*/   CREATE PROCEDURE USP_SAMPLE_TEST_SELECT      @USER_NUM                    INT          --유저ID [필수]   ----WITH ENCRYPTION   AS BEGIN     SELECT          USER_NUM                  -유저ID         ,USER_NM                    --유저명         ,BITRH_YY                     --출생년도         ,E_MAIL                        --이메일     FROM  SAMPLE_TEST     WHERE         USER_NUM    = @USER_NUM  --유저ID END GO

 

- 저장 프로시저 작성

IF  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'USP_SAMPLE_TEST_INSERT'))   DROP PROCEDURE USP_SAMPLE_TEST_INSERT GO /*#############################################################   ◆ 프로그램ID :   ◆ 프로그램명 :   ◆ 설      명 :   ---------------------------------------------------------------------------------------------   ◆ 변경일자      생성자(변경자)      변경내역 ---------------------------------------------------------------------------------------------   EXEC  USP_SAMPLE_TEST_INSERT   ##############################################################*/   CREATE PROCEDURE USP_SAMPLE_TEST_INSERT      @USER_NM                   NVARCHAR(20) --유저명       ,@BITRH_YY                    NVARCHAR(4)  --출생년도       ,@E_MAIL                       NVARCHAR(50) --이메일   ----WITH ENCRYPTION   AS BEGIN     INSERT INTO  SAMPLE_TEST     (          USER_NM            --유저명         ,BITRH_YY             --출생년도         ,E_MAIL                --이메일     )     VALUES     (          @USER_NM          --유저명         ,@BITRH_YY           --출생년도         ,@E_MAIL              --이메일     ) END GO

 

- 수정 프로시저 작성

IF  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'USP_SAMPLE_TEST_UPDATE'))   DROP PROCEDURE USP_SAMPLE_TEST_UPDATE GO /*##############################################################   ◆ 프로그램ID :   ◆ 프로그램명 :   ◆ 설      명 :   ---------------------------------------------------------------------------------------------   ◆ 변경일자      생성자(변경자)      변경내역 ---------------------------------------------------------------------------------------------   EXEC  USP_SAMPLE_TEST_UPDATE   ###############################################################*/   CREATE PROCEDURE USP_SAMPLE_TEST_UPDATE      @USER_NUM                  INT          --유저ID [필수]     ,@USER_NM                     NVARCHAR(20) --유저명       ,@BITRH_YY                     NVARCHAR(4)  --출생년도       ,@E_MAIL                        NVARCHAR(50) --이메일   ----WITH ENCRYPTION   AS BEGIN     UPDATE  SAMPLE_TEST     SET          USER_NM     = @USER_NM          --유저명         ,BITRH_YY      = @BITRH_YY          --출생년도         ,E_MAIL         = @E_MAIL             -이메일     WHERE          USER_NUM    = @USER_NUM  --유저ID END GO

 

- 삭제 프로시저 작성

IF  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'USP_SAMPLE_TEST_DELETE'))   DROP PROCEDURE USP_SAMPLE_TEST_DELETE GO /*##############################################################   ◆ 프로그램ID :   ◆ 프로그램명 :   ◆ 설      명 :   ---------------------------------------------------------------------------------------------   ◆ 변경일자      생성자(변경자)      변경내역 ---------------------------------------------------------------------------------------------   EXEC  USP_SAMPLE_TEST_DELETE ###############################################################*/   CREATE PROCEDURE USP_SAMPLE_TEST_DELETE      @USER_NUM                    INT          --유저ID [필수]   ----WITH ENCRYPTION   AS BEGIN     DELETE FROM  SAMPLE_TEST     WHERE          USER_NUM    = @USER_NUM  --유저ID END GO

 

저는 개인적으로 아래와 같은 쿼리작성기를 만들어 사용하고 있습니다. 구글링을 하면 샘플 프로젝트들이 많으니 본인의 스타일에 맞게 개발하여 사용하시면 쿼리 작성시간을 단축시키실 수 있습니다.

쿼리 Maker 화면

 

여기까지 CRUD 저장 프로시저(Stored Procedure) 작성법에 대한 포스팅을 마치도록 하겠습니다.