Web_developing/Oracle 2010/02/17 17:50
저장프로시저
- 저장 프로시저는 하나 이상으로 구성된 Transact-SQL 문을 데이터베이스에 저장한 개체입니다.
저장프로시저 특징
저장프로저의 구성요소
저장 프로시저명, 매개변수들, SQL문, 결과값 반환의 반환값
저장프로시저의 종류
- 저장 프로시저는 하나 이상으로 구성된 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을 사용함
시스템뷰에서 저장프로시저의 텍스트가 나타나지 않음. 암호화된 저장프로시저의 내용은 다시 확인할수 있는 방법이 없음으로 암호화되기전의 저장프로시저를 잘 보관해야함
@ 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문 ]
@ 매개변수 데이터 형식
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
@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
이 글에는 트랙백을 보낼 수 없습니다
0