[mssql]저장프로시저 기본

◎ 저장 프로시저 

1. 저장프로시저란?  

  SQL Server에서 제공되는 프로그래밍기능이라고 할 수 있다. 

  어떠한 동작을 일괄처리하기 위한 용도로 사용된다. 어떤 특정 쿼리를 모듈화시켜 

  필요할 때 마다 호출하여 사용하는 것으로, 데이터베이스 개체에 속한다. 

2. 구문형식 

  CREATE {PROC | PROCEDURE } [schema_name.] procedure_name [; num] 

              [ { @parmeter [type_schema_name.] data_type } 

          [VARYING] [=default] [out [put]] [,...n] 

          [WITH < procedure_option> [,...n] 

          [ FOR REPLICATION] 

  AS 

        <sql_statement> [;] [...n] | <method_specifier> } [;] 

3. 입력 저장프로시저 

  ex)  CREATE PROCEDURE usp_users2 

          @userBirth int, 

              @userHeight int                            /* 매개변수의 선언*/ 

        AS 

          SELECT * FROM userTbl 

            WHERE birthYear > @userBirth AND height > @userHeight 

                                                        /* 저장프로시저 내 실행될 SQL문*/ 

        EXEC usp_users2 1980,180                /* 입력프로시저 실행*/ 

4. 출력 저장프로시저 

  ex)  CREATE PROCEDURE usp_users4 

          @txtValue NCHAR(10), 

          @outValue INT OUTPUT 

        AS 

          Insert into testTbl values(@txtValue); 

          Select@outValue = IDENT_CURRENT('textTbl'); 

        GO                                                    /* 출력 저장프로시저 */ 

        CREATE TABLE testTbl (id int identity, txt nchar(10)); 

                                                                /* 사용할 테이블 만들기 */ 

          /* 출력 저장프로시저 실행 ( 특정 값을 내가 넣을 수 있는 거) */ 

        declare @myvalue int 

        exec usp_users4 '테스트값', @myvalue output 

        print '현재입력된id==>' + cast(@myvalue as char(5));          

                                                                /* 출력프로시저 값 넣기*/ 

5. 저장프로시저 명령어들 

1) 저장프로시저 삭제 : drop procedure usp_users4 

2) 저장된 저장프로시저 확인 

    select o.name, m.definition from sys.sql_modules m join sys.objects o on            m.object_id = o.object_id and o.type = 'p' 

3) 저장프로시저 소스코드 확인 

    execute sp_helptext usp_users2 

6. 저장프로시저 암호화 

  ex)  CREATE PROC usp_Encrypt with encryption 

        AS 

        SELECT substring(name,1,1) + '00' as [이름], birthYear as '출생연도', 

        height as '신장' from userTbl 

        GO 

                          /* 암호화 저장프로시저를 만들어서 소스내용을 보호한다. */ 

        execute usp_encrypt 

                                /* 암호화되어도 실행하는 데는 문제가 없다. */ 

        execute sp_helptext usp_encrypt 

                      /* 하지만 저장프로시저소스는 볼 수 없다.  (다시 확인할 방법도 없다) 

                          즉, 소스가 후에 필요할 경우 저장을 따로 잘해둬야 한다. */ 

7. 임시 저장프로시저 

  ex)  CREATE PROC #usp_temp 

        AS 

        SELECT * from userTbl 

        GO 

                        /* 임시 저장프로시저 생성으로 프로시저이름 앞에 #만 붙이면 된다. 

                          (SQL Server를 재시작하면 사라진다.) 

                          시스템 성능 상에 임시프로시저는 그다지 좋지 않다.  */ 

        exec #usp_temp          /* 임시프로시저 확인*/ 

        exec sp_executesql N'select * from userTbl' 

                  /* 시스템 저장프로시저  (한번만 실행될 것이면, 임시프로시저보다                              시스템저장프로시저를 활용하는 것이 더 시스템성능에 좋다.)*/ 

  

8. 저장프로시저의 특징 

 1) SQL Server 성능을 향상시킬 수 있다. 

  - 저장프로시저는 처음 시작되면 최적화, 컴파일 등의 과정을 거치고 그 결과가 

      캐시(메모리)에 저장된다. 그 후에는 캐시에 있는 것을 가져 사용하여 실행속도가 

      빨라진다.  반복되는 쿼리의 경우 저장프로시저를 사용하면 SQL Server의 

      성능을 향상시킬 수 있다. 

2) 모듈식 프로그래밍이 가능하다. 

  - 한번 저장으로 언제든지 실행 가능하고,  저장 되어진 쿼리의 관리가 수월해진다. 

      다른 모듈식 언어와 동일한 장점을 갖는다. 

 3) 보안을 강화할 수 있다. 

  - 사용자에게 테이블권한을 주지 않고 저장프로시저 접근권한을 줌으로써 보안을 

      강화할 수 있다. 

 4) 네트워크전송량을 감소시킬 수 있다. 

  - 긴 코드의 쿼리의 경우 서버로 쿼리의 텍스트가 모두 전송되어야한다. 하지만 

      이 쿼리를 서버에 저장프로시저로 생성해 놓았다면, 단지 저장프로시저이름,  

      매개변수 등 몇 글자만 전송되면 된다. 

※ 우리가 만들어쓰는 저장프로시저가 T-SQL 저장프로시저에 속하며, 생성 시 이름  

    앞에 usp_를 붙여 저장프로시저임을 구분 가능하도록 하자. 

※ 확장 저장프로시저라는 것이 있지만, 추후 없어질 것이므로, CLR 저장프로시저라는 

    강력한 기능을 가진 것을 사용하는 것이 좋다.(어려운 것이므로 추후나 공부) 

9. T-SQL과 저장프로시저 비교 

 1)T-SQL 과 저장프로시저의 작동방식 

  ① T-SQL 

    최초에구문분석에서실행까지의여러과정이실행되고, 메모리에 올라간다. 

    같은 구문을 재실행하면 작동시간이 아주 단축된다. 

  ② 저장프로시저 

    Create과정에서 실행하면 구문분석과 시스템테이블에 등록과정,  처음 실행하면 

    구분분석은 필요없고 나머지과정이 실시되고, 두 번째 실행부터 캐시에 것을 사용 

    하게 되어 속도가 빠르다. 

 ⇒ 즉,  두 개의 차이가 크게 없이 느껴질지도 모르나, 변수 즉 검색 등의 조건이 

    바뀌게 되면 T-SQL은 컴파일 과정을 전부 다시 실행하게 되지만, 저장프로시저 

    경우 바뀐 변수부분을 제외하고는 이미 컴파일이 이미 완료된 상태이다. 그러므로 

    실제 다양한 값이 입력되고 조건이 바뀌는 것이 사용되므로 저장프로시저가 효율적 

      이다. 

  

10. 저장프로시저의 단점 

 1) 단점 

  보통 인덱스를 사용하면 결과를 찾는 것이 빨라진다. 하지만, 가져올 데이터 건수가 

  많을 경우 인덱스를 사용하는 것이 오히려 성능을 나쁘게 한다. 

  그러므로 저장프로시저는 최초에 최적으로 한번만 컴파일이 되기 때문에 처음 작은 

  데이터를 사용하면 인덱스를 사용하게 되고, 후에 변수의 값이 커지게 되어도 

  인덱스를 사용하게 되어 성능에 좋지 않게 된다. 반대로도 마찬가지인데 처음에 

  많은 데이터를사용하고, 후에 적은 것을 쓰면 인덱스를 사용하지 않게 되어 

  성능에 좋지 않다. 

 2) 해결 방법 

  이러한 문제를 해결하기 위해 4가지방법을 생각할수있다. 

    ① 실행시에WITH RECOMPILE 를붙인다. 

        (기존에컴파일된상태를알아야한다) 

    ② EXEC sp_recompile 이름 

        EXEC 이름변수 

        (해댱하는 테이블이 이번 한번 다시 컴파일한다.) 

    ③ DBCC FREEPROCCACHE; 

        (캐시를 모두비운다.) 

        (다시 실행시 재컴파일하게 된다). 

    ④ 저장프로시저 사용시 마다 재컴파일되도록 설정한다. 

        (시작부분에 저장프로시저삭제부분을 기재)