뇌를 자극하는 SQL Server 2005

Download Report

Transcript 뇌를 자극하는 SQL Server 2005 

뇌를 자극하는
SQL Server 2005
11장.
저장프로시저와
사용자정의 함수
11장. 저장프로시저와 사용자정의 함수
저장프로시저의 개요 (1)
• 개념
 저장 프로시저(Stored Procedure)란 SQL Server에서 제공되는 프로그래밍 기능
 저장 프로시저는 한마디로 쿼리문의 집합으로 어떠한 동작을 일괄 처리하기 위한 용도로
사용
• 구문 형식
CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ]
[ { @parameter [ type_schema_name. ] data_type }
[ VARYING ] [ = default ] [ [ OUT [ PUT ]
] [ ,...n ]
[ WITH <procedure_option> [ ,...n ]
[ FOR REPLICATION ]
AS { <sql_statement> [;][ ...n ] | <method_specifier> }
• 생성 및 사용 예
USE sqlDB;
GO
CREATE PROCEDURE usp_users
AS
SELECT * FROM userTbl;
GO
EXEC usp_users;
뇌를 자극하는 SQL Server 2005
2 / 16
11장. 저장프로시저와 사용자정의 함수
저장프로시저의 개요 (2)
• 수정과 삭제
 수정은 ALTER PROCEDURE
 삭제는 DROP PROCEDURE
• 매개변수의 사용
▫ 구문 형식
 정의
@입력매개변수이름 데이터형식 [ = 디폴트값]
@출력매개변수이름 데이터형식 OUTPUT
 실행
EXECUTE 프로시저이름 [전달값]
EXECUTE 프로시저이름 @변수명 OUTPUT
• 프로그래밍 기능
▫ 프로그래밍 기능을 이용해 유연한 기능으로 확장 가능
• 리터 값의 이용
▫ RERUN 문을 사용해서 성공 및 실패 여부 확인
• 저장프로시저내 오류처리
▫ @@ERROR 함수 및 TRY/CATCH 문을 이용
• 임시 저장프로시저
▫ # 또는 ##을 붙임
▫ TEMPDB에 저장됨
뇌를 자극하는 SQL Server 2005
3 / 16
11장. 저장프로시저와 사용자정의 함수
<실습1> 저장프로시저 사용
• 실습 목표
▫
▫
▫
▫
▫
매개변수가 있는 저장프로시저 생성법을 익힌다.
프로그래밍을 활용한 저장프로시저 생성법을 익힌다.
현재 저장된 저장프로시저의 이름과 내용을 확인하는 방법을 익힌다.
저장 프로시저의 암호화 방식을 익힌다.
임시저장 프로시저를 사용해 본다.
• 사용된 저장프로시저 예
CREATE PROCEDURE usp_users3
@userBirth INT = 1980,
@userHeight INT = 180
AS
SELECT * FROM userTbl
WHERE birthYear > @userBirth AND height > @userHeight;
뇌를 자극하는 SQL Server 2005
4 / 16
11장. 저장프로시저와 사용자정의 함수
저장프로시저의 특징
• SQL Server의 성능을 향상시킬 수 있다.
 동일한 저장 프로시저가 자주 사용될 경우에는 일반 쿼리를 반복해서
실행하는 것보다 SQL Server의 성능이 크게 향상될 수 있다
• 모듈식 프로그래밍이 가능하다.
 저장프로시저를 생성해 놓으면, 언제든지 실행이 가능하다
• 보안을 강화할 수 있다.
 사용자 별로 테이블에 접근 권한을 주지 않고, 저장 프로시저에 접근
권한을 줌으로써 좀 더 보안을 강화한다
• 네트워크 전송량의 감소시킨다.
 저장 프로시저 이름 및 매개변수 등 몇 글자의 텍스트만 전송하면 되
므로 네트워크의 부하를 줄일 수 있다
뇌를 자극하는 SQL Server 2005
5 / 16
11장. 저장프로시저와 사용자정의 함수
저장프로시저의 종류
• 사용자 정의 저장 프로시저
▫ T-SQL 저장 프로시저
 사용자가 직접 CRETE PROCEDURE 문을 이용해서 생성한 프로
시저
▫ CLR 저장 프로시저
 .NET Framework 어셈블리의 클래스에 공용의 정적 메소드로 구현
• 확장 저장프로시저
 C언어등을 이용하여 데이터베이스에서 구현하기 어려운 것들을 구현한
저장 프로시저
• 시스템 저장프로시저
 시스템을 관리하기 위해서 SQL Server가 제공해주는 저장 프로시저로, S
QL Server의 관리와 관련된 작업을 위해서 주로 사용
 주로 ‘sp_’ 접두어로 작성되어 있다. 그러므로, 사용자가 생성한 프로시저
는 ‘sp_’ 접두어를 사용하지 않아야 시스템 저장 프로시저와 혼란을 방지
할 수 있다
뇌를 자극하는 SQL Server 2005
6 / 16
11장. 저장프로시저와 사용자정의 함수
일반 T-SQL의 작동
▫ 일반 T-SQL의 1회 실행시
일반
T-SQL
구문 분석
개체 이름 확인
실행
컴파일 및
실행계획 등록
사용 권한 확인
최적화
실행계획
메모리(캐시)
▫ 일반 T-SQL의 2회 실행시
일반
T-SQL
(동일한 쿼
리)
메모리(캐시)
확인
실행
실행계획
메모리(캐시)
뇌를 자극하는 SQL Server 2005
7 / 16
11장. 저장프로시저와 사용자정의 함수
저장프로시저의 작동 (1)
▫ 저장프로시저 정의시
저장 프로시저 정의
(CREATE
PROCEDURE)
구문 분석
지연된
이름 확인
생성
권한 확인
카탈로그 뷰에
등록
저장 프로시저의
이름 및 텍스트
관련 카탈로그 뷰
- sys.objects
- sys.sql_ modules
뇌를 자극하는 SQL Server 2005
8 / 16
11장. 저장프로시저와 사용자정의 함수
저장프로시저의 작동 (2)
▫ 저장프로시저 1회 실행시
관련 카탈로그 뷰
- sys.objects
- sys.sql_ modules
저장 프로시저의
텍스트
저장 프로시저
1회 실행
(EXEC)
개체 이름 확인
실행
컴파일 및
실행계획 등록
사용 권한 확인
최적화
실행계획
메모리(캐시)
뇌를 자극하는 SQL Server 2005
9 / 16
11장. 저장프로시저와 사용자정의 함수
저장프로시저의 작동 (3)
▫ 저장프로시저 2회 실행시
저장 프로시저
두번째 실행
메모리(캐시)
확인
실행
실행계획
메모리(캐시)
뇌를 자극하는 SQL Server 2005
10 / 16
11장. 저장프로시저와 사용자정의 함수
<실습2> 일반 T-SQL의 처리시간
• 실습 목표
▫ 일반 T-SQL이 처리되는 시간을 확인해서 쿼리의 실행시의 문제점을 파
악한다
<실습3> 저장프로시저의 작동
• 실습 목표
▫ 저장프로시저가 실행시 처리되는 시간을 확인해 본다.
뇌를 자극하는 SQL Server 2005
11 / 16
11장. 저장프로시저와 사용자정의 함수
WITH RECOMPILE 옵션과 문제점
▫ 저장 프로시저의 문제점과 해결책
 처음 수행시에 최적화가 이루어지기 때문에, 두번째 수행시에는 그 최
적화가 더 안좋은 성능을 내더라도 이미 컴파일이 된 저장프로시저를
계속사용하게 된다.
 해결책은 재컴파일하는 방법이 있다.
▫ 재컴파일의 방법




실행시에 ‘WITH RECOMPILE’옵션을 사용
항상 실행시마다 자동으로 재컴파일 되도록 프로시저를 생성
‘sp_recompile 테이블이름’ 시스템 저장 프로시저를 사용
‘DBCC FREEPROCCACHE’를 사용
<실습4> 저장프로시저의 재컴파일
▫ 실습 목표
 저장프로시저의 문제점을 파악하고 이를 해결하기 위한 재컴파일 방
법을 익힌다.
뇌를 자극하는 SQL Server 2005
12 / 16
11장. 저장프로시저와 사용자정의 함수
사용자정의 함수 기본
▫ 개념
 저장프로시저와 조금 비슷해 보이지만, 일반적인 프로그래밍 언어에
서 사용되는 함수와 같이 복잡한 프로그래밍이 가능.
 함수는 RETURN문에 의해서 특정 값을 되돌려 줌
 저장프로시저는 ‘EXEC’에 의해서 실행되지만, 함수는 주로 ‘SELECT’
문에 포함되어 실행됨 (예외도 있음)
<실습5> 사용자 정의함수 정의
▫ 실습 목표
 사용자정의 함수를 정의하는 방법을 익힌다.
 사용자정의 함수를 호출하거나, 수정하는 방법을 익힌다.
뇌를 자극하는 SQL Server 2005
13 / 16
11장. 저장프로시저와 사용자정의 함수
함수의 종류
▫ 시스템 함수
 SQL Server가 제공해 주는 함수
▫ 사용자 정의 스칼라 함수
▫
 Return문에 의해서 하나의 값을 돌려주는 함수
사용자 정의 테이블값 함수 : 리턴하는 값이 테이블인 함수
(1) 인라인 테이블 함수 : 간단히 테이블을 돌려주는 함수로 뷰와 비슷한 역할
CREATE FUNCTION 함수이름(매개변수)
RETURNS TABLE
AS
RETURN (
단일 SELECT 문장;
)
(2) 다중문 테이블 함수 : BEGIN…END로 정의되며 그 내부에 일련의 T-SQL을 이용
해서 반환될 테이블에 행 값을 Insert하는 형식을 가짐
CREATE FUNCTION 함수이름 (매개 변수)
RETURNS @테이블변수 TABLE
( 열이름과 데이터 형식 정의...)
AS
BEGIN
위(헤더)에서 정의한 테이블에 행을 INSERT 시키는 작업들...
RETURN;
END
뇌를 자극하는 SQL Server 2005
14 / 16
11장. 저장프로시저와 사용자정의 함수
그 외 함수와 관련된 사항
• 스키마 바운드 함수
▫ 함수에서 참조하는테이블, 뷰 등이 수정되지 못하도록 설정한 함수
▫ 스키마 바운드 함수의 생성은 옵션에 ‘WITH SCHEMABINDING’ 을 사용
• 테이블 변수
▫ 일반적인 변수의 선언처럼 테이블 변수도 선언해서 사용
▫ 이블 변수의 용도는 주로 임시테이블의 용도와 비슷하게 사용
• 사용자정의 함수의 제약사항
▫ 사용자 정의함수 내부에 TRY…CATCH 문을 사용할 수 없다.
▫ 사용자 정의함수 내부에 CREATE/ALTER/DROP 문을 사용할 수 없다.
▫ 오류가 발생하면 즉시 함수의 실행이 멈추고 값을 반환하지 않는다.
뇌를 자극하는 SQL Server 2005
15 / 16
11장. 저장프로시저와 사용자정의 함수
<실습6> 테이블 함수
• 실습 목표
▫ 테이블 함수의 사용법을 익힌다.
<실습7> 스카마 바운드 함수, 테이블 변수
• 실습 목표
▫ 스카마 바운드 함수의 사용법을 익힌다.
▫ 테이블 변수의 사용법을 익힌다.
뇌를 자극하는 SQL Server 2005
16 / 16