1 / 38

9. Transact – SQL 고급

9. Transact – SQL 고급. 변수선언 (DECLARE) DBMS 가 가지고 있는 로컬변수를 선언할 때 쓰는 키워드 DECLARE 구문 DECLARE {{@local_variable data_type} | {@cursor_variable_name CURSOR} | {table_type_definition} }[,....n] < table_type_definition > ::=

ryu
Download Presentation

9. Transact – SQL 고급

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 9. Transact – SQL 고급 • 변수선언(DECLARE) • DBMS가 가지고 있는 로컬변수를 선언할 때 쓰는 키워드 • DECLARE 구문 • DECLARE {{@local_variable data_type} | {@cursor_variable_name CURSOR} | {table_type_definition} }[,....n] < table_type_definition > ::= TABLE({<coloume_definition> | < table_constranit >}[,...n]) < column_definition > ::= column_name scalar_data_type [ COLLATE collation_name ] [[ DEFAULT constant_expression ] | IDENTITY[ ( seed, increment)]] [ ROWGUIDCOL ] [ < column_constraint> ]

  2. 9. Transact – SQL 고급(cont) • 변수선언(DECLARE) <cont> • DECLARE 구문(cont) • < column_constraint > ::= {[ NULL | NOT NULL ] |[ PRIMARY KEY | UNIQUE ] | CHECK ( logical_expression ) } < table_constraint > ::= {{ PRIMARY KEY | UNIQUE } ( column_name [,...n]) | CHECK ( serch_condition) } • 일반적으로 DECLARE @local_variable을 사용 • 로컬변수 정의시 ‘@’를 사용, T-SQL에서 ‘@’가 붙은 단어는 변수로 분류. • VARCHAR 타입의 CustomerName 변수를 길이 20으로 선언하는 예 • DECLARE @CustomerName varchar(20)

  3. 9. Transact – SQL 고급(cont) • 변수선언(DECLARE) <cont> • 예제 ( Northwind 데이터베이스 이용) • 선언되 변수는 SET 또는 SELECT를 이용하여 초기화 • 앞의 예 ‘DECLARE @CustomerName varchar(20) ’는 다음과 같이 초기화 • SET @CustomerName = ‘ByungJik Kim’ SELECT @CustomerName = ‘ByungJik Kim’ • myCount라는 변수를 선언하고 Orders테이블의 데이터의 개수를 세는 예 • USE Northwind GO DECLARE @myCount int SET @mycount = 0 SELECT @mycount = count(*) FROM orders SELECT @mycount --결과 ----------- 830 (1 row(s) affected)

  4. 9. Transact – SQL 고급(cont) • 변수선언(DECLARE) <cont> • 예제(cont) • 1998년 5월 5일 Order된 내용이 어떤 제품을 주문했는가를 찾아주는 예. • DECLARE @ordDate smalldatetime SET @ordDate = '1998-05-05' SELECT orderID FROM Orders WHERE OrderDate = @ordDate

  5. 9. Transact – SQL 고급(cont) • SET • 현재 연결 세션의 특정정보를 처리하는 데 사용. • SET문으로 설정하는 SQL Server의 시스템 정보. • 날짜 및 시간 데이터 처리에 대한 설정 • 잠금(LOCK)에 대한 정보 설정 • SQL Server 2000의 기타 기능에 대한 설정 • 쿼리 실행 및 처리에 대한 설정 • SQL-92 기본 설정을 사용하도록 하는 설정 • 통계 정보 표시에 대한 설정 • SQL Server 2000 트랜잭션(Transaction) 처리에 대한 설정

  6. 9. Transact – SQL 고급(cont) • SET (cont) • SQL Server 2000에서 SET문과 함께 설정될 수 있는 옵션들.

  7. 9. Transact – SQL 고급(cont) • SET (cont)

  8. 9. Transact – SQL 고급(cont) • SET (cont)

  9. 9. Transact – SQL 고급(cont) • SET (cont) • SET문으로 설정하는 SQL Server의 시스템 정보 • 예제. • USE northwind GO SELECT orderid, customerid, employeeid from orders • 위 예제를 실행한 결과와. • SET ROWCOUNT 3 GO SELECT Orderid, customerid, employeeid from orders • SET문으로 ROWCOUNT를 제한한 쿼리의 결과 비교. • SET문 사용 후 반드시 이전 상태로 되돌려 주는 것이 좋다. • SET ROWCOUNT 0 GO

  10. 9. Transact – SQL 고급(cont) • SET (cont) • IDENTITY 속성 • 테이블 정의시 특정 컬럼에 IDENTITY 속성을 부, seed(초기값)로부터 increment(증분)씩 증가. • 구문 • IDENTITY[ (seed,increment ) ] • 테이블 정의에서 idcol은 seed가 1이고 매번 데이터가 insert될 때마다 2씩 증가하여 값이 채워지게 하는 예 • USE test GO CREATE TABLE idtest ( idcol int IDENTITY(1,2), IDName varchar(20) ) insert idtest values('첫번째 ID') insert idtest values('두번째 ID') go SELECT * FROM idtest

  11. 9. Transact – SQL 고급(cont) • SET (cont) • IDENTITY 속성 • 결과 • idcol IDName ----------- ---- 1 첫번째 ID 3 두번째 ID • IDENTITY 속성의 열에는 명시적인 값을 사용하여 INSERT할 수 없다. • INSERT나 UPDATE를 IDENTITY 속성에 대해 명시적으로 표현하기 위해서는 SET문을 사용하여 IDENTITY_INSERT를 ON시킨다. • SET IDENTITY_INSERT ON GO INSERT idtest VALUES (2,'테스트 ID')

  12. 9. Transact – SQL 고급(cont) • 일괄처리(GO) • GO 키워드와 함께 쓰인 SQL 구문은 GO 키워드가 쓰인 곳까지 일괄 처리를 수행. • 예제 • 다음의 두 예를 비교. • USE test GO CREATE TABLE batchtest1 ( col1 int ) insert batchtest1 VALUES(1) --valid insert batchtest1 VALUES(2,3) --invalid GO select * from batchtest1 • 결과 col1 ----------- (0 row(s) affected)

  13. 9. Transact – SQL 고급(cont) • 일괄처리 (cont) • 예제(cont) • 위 예제에 이어. 다음을 실행. • insert batchtest1 values (1) go insert batchtest1 values (2,3) go • 결과 • col1 ----------- 1 (1 row(s) affected) • 첫 번째 예제는 두 개의 insert문장을 일괄처리, 두 번쨰 문장이 실패함으로 두 개의 insert 문장 모두 롤백. • 다음 예제는 하나의 문장씩 일괄처리.

  14. 9. Transact – SQL 고급(cont) • 일괄처리 (cont) • 일괄처리의 규칙 • --northwind 데이터베이스로 바꾼다. USE northwind GO -- view를 생성 CREATE VIEW testview AS SELECT customerID, companyName, contactname FROM customers WHERE contacttitle = 'owner' --GO <-잠시 주석처리. --GO 키워드 없이 바로 View를 select한다. SELECT * FROM testview GO • 결과 Server: Msg 156, Level 15, State 1, Procedure testview, Line 8 Incorrect syntax near the keyword 'SELECT'.

  15. 9. Transact – SQL 고급(cont) • 일괄처리 (cont) • 일괄처리의 규칙 (cont) • 위 예제에서 중간의 GO키워드가 없을때는 에러가 난다. • 위 예제로 돌아가 GO 키워드의 주석을 풀고 실행 • 결과 customerID companyName contactname ---------- ---------------------------------------- ANATR Ana Trujillo Emparedados y helados Ana Trujillo ANTON Antonio Moreno Taquería Antonio Moreno ….. (17 row(s) affected)

  16. 9. Transact – SQL 고급(cont) • 일괄처리 (cont) • 일괄처리의 규칙 (cont) • 다음에 나열하는 몇 개의 CREATE문은 일괄처리 부분이 될 수 없다. 대상은 CREATE DEFAULT, CREATE PROCEDURE, CREATE RULE, CREATE TRIGGER 및 CREATE VIEW문이며, 이 문장과 다른 문장은 일괄처리로 묶일 수 없다. • 동일한 일괄처리 내에서 테이블을 변경(ALTER TABLE)한 다음 새 열을 참조할 수 없다. • 일과처리의 첫 문이 EXECUTE 문이면 EXECUTE 키워드는 필요 없다. 저장 프로시져를 수행시킬 때 흔이 일어나며, ‘EXEC SP_NAME’과 ‘SP_NAME’은 똑같이 저장 프로시져를 실행

  17. 9. Transact – SQL 고급(cont) • 일괄처리 (cont) • 일괄처리의 규칙 (cont) • 변수의 선언은 일괄처리의 일부분이 될 수 없음을 보여주는 예 • --경우1 DECLARE @myvar varchar(20) SET @myvar = 'rattc' SELECT CustomerID, CompanyName, ContactName FROM Customers WHERE CustomerID = @myvar GO --결과 CustomerID CompanyName ContactName ---------- ---------------------------------------- RATTC Rattlesnake Canyon Grocery Paula Wilson (1 row(s) affected)

  18. 9. Transact – SQL 고급(cont) • 일괄처리 (cont) • 일괄처리의 규칙 (cont) • 변수의 선언은 일괄처리의 일부분이 될 수 없음을 보여주는 예 • --경우2 DECLARE @myvar varchar(20) GO SET @myvar = 'rattc' SELECT CustomerID, CompanyName, ContactName FROM Customers WHERE CustomerID = @myvar GO --결과 Server: Msg 137, Level 15, State 1, Line 2 Must declare the variable '@myvar'. Server: Msg 137, Level 15, State 1, Line 3 Must declare the variable '@myvar'.

  19. 9. Transact – SQL 고급(cont) • Transact-SQL 제어문 • BEGIN – END 블록 • BEGIN – END 블록내의 T-SQL문은 일괄처리의 대상이 됨. • 예제. • USE Northwind GO DECLARE @condition int DECLARE @cid varchar(20) SET @CID = ? --입력받는 값 SELECT @condition=contacttitle FROM Customers WHERE CustomerID = @cid IF @condition = 'sales representative' BEGIN UPDATE Orders SET Freight = Freight*0.9 WHERE CustomerID = @cid END

  20. 9. Transact – SQL 고급(cont) • Transact-SQL 제어문(cont) • IF문 • IF문이 구문 정의 • IF Boolean_expression { sql_statement | statement_block } [ ELSE { sql_statement | statement_block } ] • SQL Server에서 개체들이 이미 존재하는지의 여부를 알아보고 존재하면 DROP한 뒤 다시 생성하는 예제. • IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA, • TABLES WHERE TABLE_NAME = 'OurtestTable') • DROP TABLE Ourtesttable • GO • CREATE TABLE Ourtesttable • ( • column_definition_list • ... • ) • GO

  21. 9. Transact – SQL 고급(cont) • Transact-SQL 제어문(cont) • IF문 (계속) DECLARE @point INT, @credit NCHAR(1) SET @point = 77 IF @point >= 90 SET @credit = ‘A’ ELSE IF @point >= 80 SET @credit = ‘B’ ELSE IF @point >= 70 SET @credit = ‘C’ ELSE SET @credit = ‘D’ PRINT ‘취득점수==> ‘ + CAST(@point AS NCHAR(3)) PRINT ‘학점 ==>‘ + @credit

  22. 9. Transact – SQL 고급(cont) • Transact-SQL 제어문(cont) • CASE-WHEN 문 • 어떤 조건의 경우를 따져 각기 다른 명령을 수행 • CASE-WHEN문의 구조 • CASE input_expression WHEN when_expression THEN result_expression […n] [ ELSE else_result_expression ] END • Bool표현을 사용하는 구조. • CASE WHEN Boolean_expression THEN result_expression […n] [ ELSE else_result_expression ] END

  23. 9. Transact – SQL 고급(cont) • Transact-SQL 제어문(cont) • CASE 문 DECLARE @point INT, @credit NCHAR(1) SET @point = 77 SET @credit = CASE WHEN (@point >= 90) THEN ‘A’ WHEN (@point >= 90) THEN ‘B’ WHEN (@point >= 90) THEN ‘C’ WHEN (@point >= 90) THEN ‘D’ ELSE ‘F’ END PRINT ‘취득점수==> ‘ + CAST(@point AS NCHAR(3)) PRINT ‘학점 ==>‘ + @credit

  24. 9. Transact – SQL 고급(cont) • Transact-SQL 제어문(cont) • CASE-WHEN 문(cont) • Northwind사의 사원들 급여를 직급에 맞게 조정하는 예. • SELECT EmployeeID, Lastname + '' +Firstname empName,salaryupratio = CASE Title WHEN 'S ales representative' THEN '20% UP' WHEN 'Vice Presidentm, sales' THEN '10% DOWN' WHEN 'Sales manager' THEN '10% UP' ELSE 'not yet defined' END FROM Employees -- 결과 EmployeeID empName salaryupratio ----------- ------------------------------ 1 DavolioNancy not yet defined 2 FullerAndrew not yet defined … (9 row(s) affected)

  25. 9. Transact – SQL 고급(cont) • Transact-SQL 제어문(cont) • CASE-WHEN 문(cont) • Northwind사의 고객 중 얼마만큼의 액수를 구매했느냐에 따라 고객을 Normal, Special, Vip로 구분하는데 사용되는 예. • SELECT o.customerid, contactname,sum(UnitPrice * quantity) totalprice, • 'customercategory' = • case • when sum(UnitPrice * quantity) < 5000 THEN 'Normal' • when sum(UnitPrice * quantity) between 5001 and 50000 THEN 'Special' • when sum(UnitPrice * quantity) > 50001 THEN 'VIP' • end • FROM Orders o join [order details] od • on o.orderid = od.orderid • left outer join customers c • on o.customerid = c.customerid • group by o.customerid, contactname

  26. 9. Transact – SQL 고급(cont) • Transact-SQL 제어문(cont) • WHILE 문 • T-SQL의 루핑(Looping)을 위한 구문 • WHILE문의 구조 • WHILE Boolean_expression { sql_statement | statement_block } [break] { sql_statement | statement_block } [CONTINUE] • 테이블을 생성하고 100건의 데이터를 insert하는 예 • --우선 test 데이터베이스에 Whiletest라는 테이블을 만든다. USE test GO CREATE TABLE whiletest ( testid int, testdate smalldatetime default getdate() ) GO

  27. 9. Transact – SQL 고급(cont) • Transact-SQL 제어문(cont) • WHILE 문 (cont) • 테이블을 생성하고 100건의 데이터를 insert하는 예 (cont) • --100건의 데이터를 INSERT한다. DECLARE @increment int SET @increment = 1 WHILE (@increment <= 100) BEGIN INSERT Whiletest VALUES(@increment, default) SET @increment = @increment + 1 END GO --데이터를 확인 SELECT * FROM whiletest GO

  28. 9. Transact – SQL 고급(cont) • Transact-SQL 제어문(cont) • WHILE 문 (cont) • 1부터 100까지의 합을 구하는 프로그램 DECLARE @i INT DECLARE @hap INT SET @i = 1 SET @hap = 0 WHILE (@i <= 100) BEGIN SET @hap = @hap + @i SET @i = @i + 1 END PRINT @hap

  29. 9. Transact – SQL 고급(cont) • Transact-SQL 제어문(cont) • GOTO 문 …. …. SET @hap = @hap + @i IF (@hap > 1000) GOTO endprint SET @i = @i + 1 END endprint: PRINT ‘합계 = ‘ + CAST(@hap AS NCHAR(10))

  30. 9. Transact – SQL 고급(cont) • Transact-SQL 제어문(cont) • TRY, CATCH 문 • 오류를 처리는데 편리. Visual C++, C#에 있는 기능과 유사 • 구문 형식 • BEGIN TRY 원래 사용하려는 SQL 문장들 END TRY BEGIN CATCH 만약 BEGIN … TRY에서 오류가 발생하면 처리할 일들 END CATCH • USE sqlDB BEGIN TRY INSERT INTO userTbl VALUES(‘AJH’, ‘우주희’, 1988, ‘서울’, 170) PRINT N’정상적으로 입력되었다.’ END TRY BEGIN CATCH PRINT N’오류가 발생했다.’ END CATCH

  31. 9. Transact – SQL 고급(cont) • Transact-SQL 제어문(cont) • BREAK문, CONTINUE문 • RETURN문 • T-SQL의 즉각 종료를 명령하는 구문. • RETURN문에 상태코드를 함께 사용하는 경우의 예. • USE northwind GO CREATE PROCEDURE GetStatusCode @customerID varchar(5) AS IF (SELECT contacttitle FROM customers WHERE customerid = @customerid) = 'owner' RETURN 1 --OWNER이면 1을 RETURN ELSE RETURN 2 --아니면 2를 RETURN GO DECLARE @return_code int EXEC @return_code = getstatuscode 'anton' SELECT @return_code

  32. 커서의 선언(DECLARE) 커서 열기(OPEN) 커서에서 데이터 가져오기(FETCH) WHILE 문으로 모든 행이 처리될 때까지 반복 데이터 처리 커서 닫기(CLOSE) 커서의 해제(DEALLOCATE) 9. Transact – SQL 고급(cont) • 커서[Cursor] • 테이블에서 여러 개의 행을 검색한 결과로 얻은 투플 집합을 한 투플씩 처리하기 위해 사용하는 메커니즘 • 결과 투플 집합에 대한 포인터 역할. • 커서의 처리 순서

  33. 9. Transact – SQL 고급(cont) • 커서 사용의 예 : 고객의 평균 키를 계산 • USE sqlDB GO DECLARE userTbl_cursor CURSOR GLOBAL FOR SELECT height FROM userTbl; OPEN userTbl_cursor; DECLARE @height INT DECLARE @cnt INT DECLARE @totalHeight INT SET @totalHeight = 0 SET @cnt = 0 FETCHNEXT FROM userTbl_cursor INTO @height WHILE @@FETCH_STATUS = 0 BEGIN SET @cnt = @cnt + 1 SET @totalHeight = @totalHeight + @height FETCHNEXT FROM userTbl_cursor INTO @height END CLOSE userTbl_cursor; DEALLOCATE userTbl_cursor;

  34. 9. Transact – SQL 고급(cont) • 커서문의 사용 방법 • DECLARE로 커서를 선언. • OPEN으로 커서를 불러온다. • FETCH로 레코드 집합을 읽는다. • WHILE 또는 IF 등의 제어문으로 필요한 구문을 조건가 함께 실행 • CLOSE로 커서를 닫는다. • DEALLOCATE로 커서 참조를 제거. • 커서 선언문의 구문형식 DECLARE cursor_name CURSOR [LOCAL | GLOBAL ] [ FORWARD_ONLY | SCROLL ] [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] [ TYPE_WARNING ] FORselect_statement [ FOR UPDATE [OF column_name [, . . . N ] ] ]

  35. 9. Transact – SQL 고급(cont) • 커서의 종류 • 커서의 구현 방법에 따른 분류.

  36. 9. Transact – SQL 고급(cont) • 커서문의 사용 방법 (계속) • 커서 OPEN 문의 구조 OPEN { { [ GLOBAL ] cursor_name } | cursor_variable_name } • FETCH 문의 구조 FETCH [ [ NEXT | PRIOR | FIRST | LAST | ABSOLUTE {n | @nvar} | RELATIVE {n | @nvar} ] FROM ] {{ [GLOBAL] cursor_name } | @cursor_variable_name} [ INTO @variable_name [,...n] ] • 커서 CLOSE 문의 구조 CLOSE { { [ GLOBAL ] cursor_name } | cursor_variable_name }

  37. 9. Transact – SQL 고급(cont) • 커서[Cursor] (cont) • FETCH의 옵션. • NEXT : 이 옵션은 FETCH를 이용하여 행을 검색하는데 사용되는 기본 옵션. 결과 집합에서 바로 다음 행을 검색. • ABSOLUTE : 이 옵션은 ABSOLUTE{n}의 형식으로 사용되는데 n의 양수인 경우 결과집합의 맨 처음부터 n번째에 있는 행을 반환한다. 반대로 n이 음수인 경우는 맨 뒤부터 n번째에 있는 행을 반환. • RELATIVE : 이 옵션은 ABSOLUTE와 마찬가지 형식을 취하며, n이 양수인 경우 현재행으로 부터 n번째 뒤로 있는 행을 반환하다. 반대로 n이 음수인 경우는 현재 위치로부터 n번째 앞에 있는 행을 반환.

  38. 9. Transact – SQL 고급(cont) • 커서 사용 예 : • Northwind사에서 물건을 주문한 고객 중 합계가 5000 이상인 고객에게 좀더 빠른 Delivery를 제공하기 위해 Shipping을 DHL_Express를 이용하도록 업데이트 하는 예. • USE northWind GO DECLARE @Orderid int DECLARE Use_DHL_Express_Cursor CURSOR FOR SELECT distinct(o.orderid) orderid from orders o, [order details] od WHERE od.UnitPrice*od.quantity > 5000 AND o.orderid = od.orderid OPEN Use_DHL_Express_Cursor FETCHNEXT FROM Use_DHL_Express_Cursor INTO @Orderid WHILE @@FETCH_STATUS = 0 BEGIN UPDATE Orders SET shipVia = 4 WHERE OrderID = @OrderID FETCHNEXT FROM Use_DHL_Express_Cursor INTO @OrderID END

More Related