본문 바로가기
컴퓨터 일반/데이터베이스

SQL 저장프로시저

by ITPro 2010. 8. 11.
저장프로시저
- 저장 프로시저는 하나 이상으로 구성된 Transact-SQL 문을 데이터베이스에 저장한 개체입니다.

저장프로시저 특징
  • 모듈 프로그래밍
    자주 반복해서 사용하는 T-SQL문을 DB에 저장해 필요한 시점에만 사용함.
    매번 같은 구문을 다시 작성할 필요가 없음
  • 유연한 보완관리
    데이터 조회하는 저장프로시저. 접근권한이 없어도 저장프로시저를 실행할 권한이 있다면 조회가능
  • 네트워크 트래픽 감소
    쿼리전체를 서버로 전송해서 작업하는 것이 아닌 저장 프로시저와 매개변수값만을 전달함으로 데이터량이 작음
  • 빠른실행
    저장프로시저는 실행후 쿼리 실행계획을 메모리에 저장 > 저장된 실행계획 사용 > 구문분석이나 최적화 과정 거치지 않아서 더 빠른 실행을 할수 있고 캐시에 없더라도 구문분석, 표준화등의 작업을 하지 않기에 성능이 빠름

저장프로저의 구성요소
저장 프로시저명, 매개변수들, SQL문, 결과값 반환의 반환값

저장프로시저의 종류
  • 확장프로시저 : C와 같은 언어를 이용해서 구현한 프로시저, master DB에만 추가가능
  • 사용자 정의 저장 프로시저 : T-SQL문을 이용해 저장 프로시저로 구현
  • 시스템 저장 프로시저 : SQL서버관리를 위해 시스템에서 제공해주는 저장 프로시저
    sys의 스키마로 나타남, 데이터베이스 명 필요없이 시스템 저장 프로시저명을 통해서 실행가능

    Sp_who, sp_who2 : 사용자 정보
    Sp_lock : lock 정보
    Sp_help : 지정한 개체 정보
    Sp_helpdb : 지정한 DB정보
    Sp_configure : SQL 서버 설정변경
  • 임시 저장프로시저
    T-SQL 문 또는 일괄처리의 실행계획을 재 사용하지 않던 이전 버전의 방식
    사용자 정의 프로시저와 동일하게 작성하지만, 저장프로시저 명을 #으로 하면 임시저장프로시저가 됨
    SQL Server 2005에서는 T-SQL문과 일괄처리의 실행계획을 재 사용할수 있음으로 임시저장프로시저 사용이 거의없음
  • CLR 저장 프로시저
    T-SQL에서 부족한 프로그래밍 부분을 CLR 저장 프로시저를 통해 T-SQL 저장 프로시저보다는 더 강력한 구조적 프로그래밍이 가능

저장 프로시저의 생성

CREATE PROCEDURE usp_withANumber <- 소문자 : 스키마. 저장 프로시저명
@ EmployeeID INT <- 매개변수, 데이터 형식
AS
SELECT ANumber, AContent, ManagerID
FROM HumanResource.analysisDate
WHERE ManagerID = @ EmployeeID
GO

EXECUTE usp_withANumber

GO
* 임시 저장프로시저의 생성은
CREATE PROCEDURE #usp_withANumber <- 소문자 : 스키마. 저장 프로시저명앞에 샾
* 저장프로시저 생성시, SP_ 접두사는 시스템저장프로시저와 혼란을 줄수 있음으로 사용금지
* 그룹화면 저장프로시저 삭제시 개별적으로 하나씩 삭제할수 없음으로 가급적 사용하지말것
- 그룹화하기 : 같은 저장 프로시저명에 ; 하고 숫자를 매김

CREATE PROCEDURE usp_withANumber;1
AS
[ T-SQL구문 ]
GO

CREATE PROCEDURE usp_withANumber;2
AS
[ T-SQL구문 ]
GO

* 다른 사용자의 접근막기위해서는 CREATE아래쪽에 WITH ENCRYPTION을 사용함
시스템뷰에서 저장프로시저의 텍스트가 나타나지 않음. 암호화된 저장프로시저의 내용은 다시 확인할수 있는 방법이 없음으로 암호화되기전의 저장프로시저를 잘 보관해야함



저장 프로시저의 수정

ALTER PROCEDURE 스키마. 저장프로시저명.
@ 매개변수 데이터 형식
AS
[ 변경된 SQL문 ]



저장 프로시저의 삭제

DROP PROCEDURE 스키마.저장프로시저명;



기본값을 지니는 매개변수의 사용과 output사용

CREATE PROCEDURE usp_withANumber
@EmployeeID INT = 10 <- EmployeeID에 기본값을 지정한 것
@outvalue int output <- outvalue에 output 변수를 쓰겠다는것
@currency_cursor CURSOR VARYING OUTPUT <- output 매개변수로 커서를 사용할때
AS
SELECT @outvalue = ManagerID <- 관리자 ID를 output 매개변수에 설정
FROM HumanResource.analysisDate
WHERE ManagerID = @ EmployeeID
GO

DECLARE @ManagerID INT; <- 저장프로시저 output 매개변수에 반환하는 값저장을 위한 지역변수선언

EXECUTE usp_withANumber 20, @ManagerID output <-반환되는값 조회

GO

반응형

'컴퓨터 일반 > 데이터베이스' 카테고리의 다른 글

오라클 설치시 JAVA 에러가 발생하는 경우  (0) 2012.07.18
MySQL TIP  (0) 2012.07.17
UNIX 계열 mysql 설치  (0) 2010.08.11
mysql 비밀번호 설정  (0) 2010.08.11
mysql 외부 접근 권한 설정 방법  (0) 2010.08.11

바로가기