슬라이드 1

Size: px

Start display at page:

Download "슬라이드 1"

민지 선우
4 years ago
Views:

1 9. 저장프로시저 동일한 Transact-SQL 문을수시로사용한다면저장프로시저로만들어사용해보자. 효율성과성능면에서월등한발전을경험할수있을것이다.

2 2 청춘 청춘이란인생의어느기간을말하는것이아니라마음의상태를말하는것이다. 그것은장밋빛얼굴, 앵두같은입술, 하늘거리는자태가아니고강인한의지, 풍부한상상력, 불타는정열을말한다. 청춘이란인생의깊은샘물에서나오는정신력을뜻하며청춘이란유약함을물리치는용기, 안이함을뿌리치는모험심을의미한다. 때로는이십세의청년보다육십세가된사람에게청춘이있는것이다. 나이가먹는다고해서사람이늙는것은아니다. 이상을잃어버릴때비로서늙는것이다.

3 3 저장프로시저의정의 (What Are Stored Procedures?) 서버에저장된 Transact-SQL 문의명명된컬렉션 반복적인작업을캡슐화 입력매개변수를받아처리하고출력매개변수값을리턴 수행에대한성공과실패를확인하는상태값을리턴 저장프로시저의다섯가지유형 시스템저장프로시저 (System Stored Procedure) 로컬저장프로시저 (Local Stored Procedure) 임시저장프로시저 (Temporary Stored Procedure) 원격저장프로시저 (Remote Stored Procedure) 확장저장프로시저 (Extended Stored Procedure)

4 저장프로시저의장점 (1) (Advantages of Stored Procedures) 저장프로시저를만들고여러곳에서호출하여사용함 모듈화된프로그램작성가능 별도의배포작업이없어도일괄적용됨 실행계획을재사용하여성능을향상시킴 처음수행될때실행계획을만들어메모리에캐싱됨 다음사용부터재사용됨 임의쿼리보다빠르게수행됨 보안메커니즘을제공함 저장프로시저안의개체들에대한권한이없어도됨 네트워크트래픽감소 저장프로시저를수행하는명령문만전달하면됨 4

5 5 저장프로시저의장점 (2) (Advantages of Stored Procedures) 특성뷰프로시저 조회 O O 함수 O 트리거 X 수정 X O X O 매개변수 X O O X 수식 X X O X FROM 절 O X O X

6 6 저장프로시저만들기 (1) (Creating Stored Procedures) 만들기구문 CREATE PROC [ EDURE ] procedure_name [ ; number ] [ data_type } [VARYING] [= default ] [ OUTPUT ] ] [,...n ] [ WITH { RECOMPILE ENCRYPTION RECOMPILE, ENCRYPTION } ] [ FOR REPLICATION ] sql_statement [...n ] i

7 7 CREATE PROCEDURE 문을사용 USE Northwind CREATE PROC dbo.overdueorders SELECT * FROM dbo.orders WHERE RequiredDate < GETDATE() AND ShippedDate IS Null 저장프로시저만들기 (2) (Creating Stored Procedures) 지연된이름확인 (Deferred Name Resolution) 저장프로시저에대한정보얻기 sp_help, sp_depends, sp_rename, sp_helptext?

8 저장프로시저실행 (Stored Procedure Execution) 저장프로시저만으로실행 EXEC OverdueOrders INSERT 문안에서저장프로시저실행 INSERT INTO Customers EXEC EmployeeCustomer 저장프로시저의데이터반환 수행된 SELECT 문의결과 RETURN 값 ( 지정하지않으면 0) 출력 (OUTPUT) 매개변수 프로시저밖에서사용가능한전역커서 8

9 9 RETURN 값사용 (Using RETURN Value) 저장프로시저만들기 CREATE PROC datetime SELECT * FROM Sales WHERE ord_date RETURN(@@Rowcount) 저장프로시저실행 int = Get_Sales_Num ' ', ' ' Counts

10 10 CREATE PROCEDURE dbo.[year to Year DateTime = DateTime = Null IS Null = dateadd(yy,-1,getdate()) IS Null = GetDate() IF Datediff(dd,@BeginDate,@EndDate) > 365 BEGIN RAISERROR('The maximum timespan allowed for this report is one year.', 14, 1) RETURN END 입력매개변수사용 (Input Parameters) 들어오는모든매개변수의값을미리검사 매개변수에대한적절한기본값사용 SELECT O.ShippedDate,O.OrderID,OS.Subtotal, DATENAME(yy,ShippedDate) Year FROM Orders O INNER JOIN [Order Subtotals] OS ON O.OrderID = OS.OrderID WHERE O.ShippedDate

11 11 입력매개변수를사용한저장프로시저실행 매개변수이름으로값전달 EXEC = = 'Maria = 'Alfreds = 'Sales = 'Obere Str. = = = = ' ' 위치로값전달 (Setting Parameter Values) EXEC AddCustomer 'ALFKI2', 'Alfreds Futterkiste', 'Maria Anders', 'Sales Representative', 'Obere Str. 57', 'Berlin', NULL, '12209', 'Germany', ' '

12 12 저장프로시저만들기 CREATE PROCEDURE int OUTPUT 저장프로시저실행 smallint EXECUTE MathTutor 5, OUTPUT SELECT 'The result is: 실행결과 The result is: 30 출력매개변수를사용한값전달 (Return Values Using OUTPUT Parameters)

13 저장프로시저변경및삭제 (Altering and Dropping Stored Procedures) 저장프로시저변경 USE Northwind ALTER PROC dbo.overdueorders SELECT CONVERT(char(8), RequiredDate, 1) RequiredDate, CONVERT(char(8), OrderDate, 1) OrderDate, OrderID, CustomerID, EmployeeID FROM Orders WHERE RequiredDate < GETDATE() AND ShippedDate IS Null ORDER BY RequiredDate 저장프로시저삭제 DROP PROC dbo.overdueorders 13

14 14 일반쿼리문의실행 저장프로시저만들기 실행계획재사용 (1) (Reusing Execution Plans) USE Northwind SELECT * FROM Orders WHERE OrderDate BETWEEN ' ' AND ' ' --SEEK SELECT * FROM Orders WHERE OrderDate BETWEEN ' ' AND ' ' --SCAN CREATE PROC char(08) SELECT * FROM Orders WHERE OrderDate

15 실행계획재사용 (2) (Reusing Execution Plans) 저장프로시저실행 EXEC Get_Orders ' ', ' ' EXEC Get_Orders ' ', ' ' 첫번째경우는 Index Seek 두번째경우도 Index Seek RECOMPILE 사용 EXEC Get_Orders ' ', ' ' EXEC Get_Orders ' ', ' ' WITH RECOMPILE 두번째의경우 Clustered Index Scan 15

16 16 재컴파일을해야하는경우 재컴파일 (Recompiling Stored Procedures) 저장프로시저에대입되는변수의변화폭이심한경우 변수의대입폭은일정하나데이터의분포도가비정형적일경우 재컴파일방법 저장프로시저를수행할때마다재컴파일 저장프로시저만들때 WITH RECOMPILE 사용 현재수행에대해서만재컴파일 WITH RECOMPILE 옵션을사용하여저장프로시저실행 특정개체와관련된저장프로시저재컴파일 sp_recompile object : 개체와관련된저장프로시저가이후처음수행될때재컴파일됨

17 17 저장프로시저작성에대한지침 (Guidelines for Creating Stored Procedures) dbo 사용자가모든저장프로시저를소유하도록함 각각의저장프로시저는단일작업을수행하도록함 만들고테스트하고문제해결하고사용 sp_ 접두사를저장프로시저이름에사용하지않음 모든저장프로시저가동일한연결설정을사용하도록함 임시저장프로시저의사용을최소화함 syscomments 시스템테이블의내용을직접삭제하지않아야함

18 정리

19 저장프로시저그룹화예 1 CREATE PROC usp_getcustomer;1 SELECT CustomerID, City, CompanyName FROM Customers ORDER BY CustomerID CREATE PROC usp_getcustomer;2 SELECT CustomerID, City, CompanyName FROM Customers ORDER BY City 2 -- 프로시저수행 EXEC usp_getcustomer;1 EXEC usp_getcustomer; 프로시저삭제 DROP PROC usp_getcustomer H 19

20 20 OUTPUT 커서매개변수사용예 1 USE pubs CREATE PROCEDURE CURSOR VARYING OUTPUT = CURSOR FOR SELECT * FROM titles WHERE type = 'business' 2 CURSOR EXEC OUTPUT FETCH NEXT WHILE (@@FETCH_STATUS = 0) BEGIN FETCH NEXT END H

21 다음중오류가발생하는부분은 USE Tempdb A 1 IF OBJECT_ID('dbo.usp_Proc1') IS NOT NULL DROP PROC dbo.usp_proc1 IF OBJECT_ID('dbo.usp_Proc2') IS NOT NULL DROP PROC dbo.usp_proc2 IF OBJECT_ID('dbo.

21 21 다음중오류가발생하는부분은 USE Tempdb A 1 IF OBJECT_ID('dbo.usp_Proc1') IS NOT NULL DROP PROC dbo.usp_proc1 IF OBJECT_ID('dbo.usp_Proc2') IS NOT NULL DROP PROC dbo.usp_proc2 IF OBJECT_ID('dbo.Tbl1') IS NOT NULL DROP TABLE dbo.tbl1 2 3 CREATE PROC dbo.usp_proc1 SELECT col1 FROM dbo.tbl1 EXEC dbo.usp_proc1 B 4 5 EXEC dbo.usp_proc1 CREATE PROC dbo.usp_proc2 SELECT col2 FROM dbo.tbl1 C CREATE TABLE Tbl1 (col1 int) INSERT INTO dbo.tbl1(col1) VALUES(1) 6 EXEC dbo.usp_proc2 H

목차 BUG 문법에맞지않는질의문수행시, 에러메시지에질의문의일부만보여주는문제를수정합니다... 3 BUG ROUND, TRUNC 함수에서 DATE 포맷 IW 를추가지원합니다... 5 BUG ROLLUP/CUBE 절을포함하는질의는 SUBQUE

목차 BUG 문법에맞지않는질의문수행시, 에러메시지에질의문의일부만보여주는문제를수정합니다... 3 BUG ROUND, TRUNC 함수에서 DATE 포맷 IW 를추가지원합니다... 5 BUG ROLLUP/CUBE 절을포함하는질의는 SUBQUE ALTIBASE HDB 6.3.1.10.1 Patch Notes 목차 BUG-45710 문법에맞지않는질의문수행시, 에러메시지에질의문의일부만보여주는문제를수정합니다... 3 BUG-45730 ROUND, TRUNC 함수에서 DATE 포맷 IW 를추가지원합니다... 5 BUG-45760 ROLLUP/CUBE 절을포함하는질의는 SUBQUERY REMOVAL 변환을수행하지않도록수정합니다....