1. Giới thiệu ngôn ngữ T-SQL

1. Khái niệm

Transaction SQL (T-SQL) là ngôn ngữ phát triển nâng cao của ngôn ngữ SQL chuẩn. Nó là ngôn ngữ dùng để giao tiếp giữa ứng dụng và SQL Server. T-SQL các khả năng của ngôn ngữ định nghĩa dữ liệu - DDL và ngôn ngữ thao tác dữ liệu – DML của SQL chuẩn cộng với một số hàm mở rộng, các store procedure hệ thống và cấu trúc lập trình (như IF, WHILE,…) cho phép lập trình trên SQL Server được linh động hơn.

Trong các chương trước ta đã giới thiệu ngôn ngữ SQL chuẩn và làm quen với các câu lệnh T-SQL dùng để định nghĩa dữ liệu, thao tác dữ liệu như: Tạo CSDL, tạo bảng, tạo View, tạo Index, chèn dữ liệu,.v.v… Trong chương này ta sẽ tìm hiểu thêm về T-SQL.

2. Phát biểu truy vấn dữ liệu nâng cao

a) Mệnh đề TOP

Mệnh đề TOP chỉ định tập hợp các dòng đầu tiên được trả về trong truy vấn. Tập hợp các dòng đó có thể là một con số hoặc theo tỷ lên phần trăm (PERCENT) các dòng dữ liệu. Mệnh đề TOP được sử dụng trong các khối câu lệnh Select, Insert, Update và Delete. Cú pháp:

[ TOP (expression) [PERCENT] [ WITH TIES ]]

Trong đó:

• expression: Là biểu thức trả về giá trị kiểu số.
• PERCENT: Chỉ định số dòng trả về là expression phần trăm trong tập kết quả.
• WITH TIES: TOP ...WITH TIES chỉ được chỉ định trên khối câu lệnh SELECT và có mệnh đề ORDER BY. Chỉ định thêm các dòng từ tập kết quả cơ sở có cùng giá trị với các cột trong mệnh đề ORDER BY xuất hiện như là dòng cuối cùng của TOP n (PERCENT).

Ví dụ 4.1. Sử dụng mệnh đề TOP

Trong câu lệnh Insert

INSERT TOP (2) INTO LOP
SELECT * FROM DMLOP ORDER BY Khoa

Trong câu lệnh Select

INSERT INTO LOP
SELECT TOP (2) WITH TIES * FROM DMLOP ORDER BY Khoa

b) Điều kiện kết nối - JOIN

Trong khối câu lệnh SELECT, ở mệnh đề FROM ta có thể sử dụng phát biểu JOIN để kết nối các bảng có quan hệ với nhau.

Mệnh đề kết nối Join được phân loại như sau:

1. Inner joins (toán tử thường dùng để kết nối thường là các toán tử so sánh = hoặc <>). Inner joins sử dụng một toán tử so sánh để so khớp các dòng từ hai bảng dựa trên các giá trị của các cột so khớp của mỗi bảng. Kết quả trả về của Inner Join là các dòng thỏa mãn điều kiện so khớp.
2. Outer joins. Outer joins có thể là left, right, hoặc full outer join.LEFT JOIN hoặc LEFT OUTER JOIN : Kết quả của left outer join không chỉ bao gồm các dòng thỏa mãn điều kiện so khớp giữa hai bảng mà còn gồm tất cả các dòng của bảng bên trái trong mệnh đề LEFT OUTER. Khi một dòng ở bảng bên trái không có dòng nào của bảng bên phải so khớp đúng thì các giá trị NULL được trả về cho tất cả các cột ở bảng bên phải.RIGHT JOIN or RIGHT OUTER JOIN: Right outer join là nghịch đảo của left outer join. Tất cả các dòng của bảng bên phải được trả về. Các giá trị Null cho bảng bên trái khibất cứ một dòng nào bên phải không có một dòng nào bảng bên trái so khớp đúng.FULL JOIN or FULL OUTER JOIN: full outer join trả về tất cả các dòng trong cả hai bảng bên trái và phải. Bất kỳ một dòng không có dòng so khớp đúng của bảng còn lại thì bảng còn lại nhận các giá trị NULL. Khi có sự so khớp đúng giữa các bảng thì tập kết quả sẽ chứa dữ dữ liệu các bảng cơ sở đó.
3. Cross joins: Trả về tất cả các dòng của bảng bên trái và mỗi dòng bên trái sẽ kết hợp với tất cả các dòng của bảng bên phải. Cross joins còn được gọi là tích Đề các (Cartesian products).

Ví dụ 4.2. Sử dụng Join

- Inner Joins:

SELECT MONHOC.MaMH, MONHOC.TenMH, MONHOC.SDVHT, DIEM.MaSV, DIEM.DiemL1

FROM DIEM INNER JOIN MONHOC

ON DIEM.MaMH = MONHOC.MaMH

- Left Joins:

SELECT MONHOC.MaMH, MONHOC.TenMH, MONHOC.SDVHT, DIEM.MaSV, DIEM.DiemL1

FROM MONHOC LEFT JOIN DIEM

ON MONHOC.MaMH= DIEM.MaMH

- Right Joins:

SELECT MONHOC.MaMH, MONHOC.TenMH, MONHOC.SDVHT, DIEM.MaSV, DIEM.DiemL1

FROM DIEM Right JOIN MONHOC ON DIEM.MaMH= MONHOC.MaMH

- Full Joins:

SELECT MONHOC.MaMH, MONHOC.TenMH, MONHOC.SDVHT, DIEM.MaSV, DIEM.DiemL1

FROM DIEM Full JOIN MONHOC

ON DIEM.MaMH= MONHOC.MaMH

- Cross Joins:

SELECT MONHOC.MaMH, MONHOC.TenMH, MONHOC.SDVHT, DIEM.MaSV, DIEM.DiemL1

FROM MONHOC CROSS JOIN DIEM

c) Truy vấn Cross tab

Trong một số trường hợp thống kê, ta cần phải xoay bảng kết quả, do đó có các cột được biểu diễn theo chiều ngang và các dòng được biểu diễn theo chiều dọc (được gọi là truy vấn cross tab).

Ví dụ 4.3. Ví dụ ta có một view tính tổng giá trị của một hóa đơn View_Order (OrderID, OrderDate, Month, Year, Total). Ta cần thống kê doanh thu theo từng tháng của các năm.

SELECT Year,



SUM(CASE

Month

WHEN

1

THEN

Total

ELSE

0

END)

AS

Jan,

SUM(CASE

Month

WHEN

2

THEN

Total

ELSE

0

END)

AS

feb,

SUM(CASE

Month

WHEN

3

THEN

Total

ELSE

0

END)

AS

mar,

SUM(CASE

Month

WHEN

4

THEN

Total

ELSE

0

END)

AS

apr,

SUM(CASE

Month

WHEN

5

THEN

Total

ELSE

0

END)

AS

may,

SUM(CASE

Month

WHEN

6

THEN

Total

ELSE

0

END)

AS

jun,

SUM(CASE

Month

WHEN

7

THEN

Total

ELSE

0

END)

AS

jul,

SUM(CASE

Month

WHEN

8

THEN

Total

ELSE

0

END)

AS

aug,

SUM(CASE

Month

WHEN

9

THEN

Total

ELSE

0

END)

AS

sep,

SUM(CASE

Month

WHEN

10

THEN

Total

ELSE

0

END)

AS

oct,

SUM(CASE

Month

WHEN

11

THEN

Total

ELSE

0

END)

AS

nov,

SUM(CASE

Month

WHEN

12

THEN

Total

ELSE

0

END)

AS

dec

FROM View_Order GROUP BY Year

Kết quả:

SQL Server 2005 đưa ra các toán tử đơn giản hơn cho việc tạo truy vấn cross tab, đó là toán tử PIVOT và UNPIVOT trong mệnh đề FROM của khối câu lệnh SELECT.

1. Toán tử PIVOT thực hiện xoay một biểu thức giá trị bảng (table valued expression) thành một bảng khác bằng việc đưa các giá trị duy nhất của một cột thành các cột và thực hiện các hàm thống kê trên các cột còn lại.
2. Toán tử UNPIVOT thực hiện quá trình ngược lại với quá trình thực hiện của toán tử PIVOT, xoay các cột của biểu thức bảng thành giá trị của một cột.

Cú pháp:

FROM { <table_source> } [ ,...n ]

<table_source> ::=

{

<pivoted_table>

| <unpivoted_table> [ ,...n ]

}



<pivoted_table> ::=

table_source PIVOT <pivot_clause> table_alias



<pivot_clause> ::=

( aggregate_function( value_column ) FOR pivot_column

IN ( <column_list> )

)



<unpivoted_table> ::=

table_source UNPIVOT <unpivot_clause> table_alias



<unpivot_clause> ::=

( value_column FOR pivot_column IN (

<column_list> ) )

<column_list> ::=

column_name [ , ... ]

Trong đó:

1. table_sourcePIVOT <pivot_clause> : Chỉ định bảngtable_sourceđược xoay dựa trên cộtpivot_column.table_sourcelà một bảng hoặc biểu thức bảng. Output là mộtbảng chứa tất cả các cột của table_source trừ cột pivot_column và value_column. Các cột của table_source, trừ pivot_column và value_column, được gọi là các cột phân nhóm của toán tử pivot.
2. aggregate_function: Là một hàm thống kê của hệ thống hoặc do người dùng định nghĩa. Hàm COUNT(*) không được phép sử dụng trong trường hợp này.
3. value_column: Là cột giá trị của toán tử PIVOT. Khi sử dụng với toán tử UNPIVOT, value_column không được trùng tên với các cột trong bảng input table_source.
4. FOR pivot_column : Chỉ định trục xoay của toán tử PIVOT. pivot_column là có kiểu chuyển đổi được sang nvarchar(). KHông được là các kiểu image hoặc rowversion.Khi UNPIVOT được sử dụng, pivot_column là tên của cột output được thu hẹp lại từ table_source. Tên cột này không được trùng với một tên nào trong table_source.
5. IN ( column_list ) : Trong mệnh đề PIVOT, danh sách các giá trị trong pivot_column sẽ trở thành tên các cột trong bảng output. Danh sách này không được trùng với bất kỳ tên cột nào tồn tại trong bảng input table_source mà đang được xoay.
6. Trong mệnh đề UNPIVOT, danh sách các cột trong table_source sẽ được thu hẹp lại thành một cột pivot_column.
7. table_alias:Là tên bí danh của bảng output.pivot_table_alias phải được chỉ định.
8. UNPIVOT < unpivot_clause > : Chỉ định bảng input được thu hẹp bằng các cột trong column_list trở thành một cột gọi là pivot_column.

Hoạt động của toán tử PIVOT:

Toán tử PIVOT thực hiện theo tiến trình sau:

1. Thực hiện GROUP BY dựa vào các cột phân nhóm trên bảng input_table và kết quả là ứng với mỗi nhóm cho một dòng out put trên bảng kết quả.
2. Sinh các giá trị ứng với các cột trong danh sách column list cho mỗi dòng output bằng việc thực thi như sau:Nhóm các dòng được sinh từ việc GROUP BY ở bước trước dựa trên cột pivot_column. Đối với mỗi cột output trong column_list, chọn một nhóm con thỏa mãn điều kiện: pivot_column=CONVERT(<data type of pivot_column>, 'output_column')aggregate_function định giá trị dựa tên cột value_column trong nhóm con này và kết quả được trả về của nó tương ứng là giá trị của cột output_column. Nếu nhóm con là rỗng thì SQL Server sinh giá trị NULL cho cột output_column đó. Nếu hàm thống kê là COUNT thì nó sinh giá trị 0.

Ví dụ 4.5. Ví dụ ta có một view tính tổng giá trị của một hóa đơn View_Order (OrderID, OrderDate, Month, Year, Total). Ta cần thống kê doanh thu theo từng tháng của các năm.

SELECT Year,[1]AS Jan,[2]AS feb, [3]AS mar,[4] AS apr,[5]

AS may,[6] AS jun,[7] AS jul,[8] AS aug,[9] AS sep, [10]AS oct,[11] AS nov,[12] AS dec

FROM

(SELECT Year, Month,Total FROM View_Order) p

PIVOT

(Sum(Total) FOR Month IN

([1], [2], [3], [4], [5], [6], [7], [8], [9],

[10], [11], [12])

)AS pvt

Ví dụ 4.6. Sử dụng PIVOT

USE AdventureWorks GO

SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5

FROM

(SELECT PurchaseOrderID, EmployeeID, VendorID FROM Purchasing.PurchaseOrderHeader) p

PIVOT (

COUNT (PurchaseOrderID)

FOR EmployeeID IN

( [164], [198], [223], [231], [233] )

) AS pvt

ORDER BY VendorID;

Ví dụ 4.7. Sử dụng UNPIVOT

CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int, Emp3 int, Emp4 int, Emp5 int)

GO

INSERT INTO pvt VALUES (1,4,3,5,4,4) INSERT INTO pvt VALUES (2,4,1,5,5,5) INSERT INTO pvt VALUES (3,4,3,5,4,4) INSERT INTO pvt VALUES (4,4,2,5,5,4) INSERT INTO pvt VALUES (5,5,1,5,5,5) GO

--Unpivot the table.

SELECT VendorID, Employee, Orders FROM

(SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5 FROM pvt) p

UNPIVOT

(Orders FOR Employee IN

(Emp1, Emp2, Emp3, Emp4, Emp5)

)AS unpvt

d) UNION và UNION ALL

Toán tử UNION [ALL] dùng để hợp kết quả của hai hoặc nhiều câu truy vấn tương thích với nhau. Hai câu truy vấn tương thích là hai câu có cùng cấu trúc, tức là có cùng số cột và tập các cột tương ứng có cùng kiểu dữ liệu hoặc có các kiểu dữ liệu tương thích nhau. Cú pháp của câu lệnh:

select_statement UNION [ALL] select_statement

Tên của các cột trong phép toán UNION là tên các cột trong tập kết quả của khối câu lệnh SELECT thứ nhất trong UNION.

Theo mặc định phép toán UNION chỉ lấy đại diện cho tập các dòng trùng nhau. Nếu ta sử dụng từ khóa ALL, thì tất cả các dòng được cho vào bảng kết quả và các dòng trùng nhau sẽ không loại bỏ các dòng trùng nhau.

Ví dụ 4.8. Sử dụng UNION

SELECT * from LOP UNION ALL

SELECT * from DMLOP

3. Lập trình cấu trúc trong SQL Server

a) Các toán tử

Toán tử gán: Ký hiệu là dấu ‘=’ được dùng để gán giá trị cho một biến hoặc một cột.

DECLARE @intValue int SELECT @intValue = 1 PRINT @intValue

hoặc

DECLARE @intValue int SET @intValue = 1 PRINT @intValue

Toán tử số học: Đó là các phép toán cộng (+), trừ (-), nhân (*), chia (/) và chia modul (%) .

12+4=16

12-4=8

12*4=48

12/4=3

15%2=1

• Toán tử so sánh: Đó là các phép toán so sánh giữa hai biểu thức và trả về giá TRUE hoặc FALSE. Đó là các phép so sánh: = (bằng), <> (khác), > (lớn hơn), >= (lớn hơn hoặc bằng), < (nhỏ hơn), <= (nhỏ hơn hoặc bằng).
• Toán tử logic: Kiểm tra điều kiện đúng của hai biểu thức, chúng thường được sử dụng cùng với các toán tử so sánh để trả về giá trị TRUE hoặc FALSE. Các toán tử logic được cho trong bảng 4.1 sau.

Toán tử ghép chuỗi (+): Dùng để ghép hai chuỗi với nhau thành một chuỗi. Toán tử ghép chuỗi được dùng với các kiểu dữ liệu char, varchar, nchar, nvarchar, text, và ntext.

SELECT 'This' + ' is a test.'

Toán tử bit: Thực hiện thao tác với các bit-lavel với các kiểu dữ liệu Integer. Các toán tử đó được cho trong bảng 4.2.

b) Cấu trúc lặp

SQL Server cung cấp hai cấu trúc lặp đó là: cấu trúc WHILE và GOTO.

Cấu trúc lặp WHILE: Câu lệnh WHILE sẽ kiểm tra điều kiện trước khi thực hiện lệnh. Một khối lệnh là một tập các câu lệnh được bao trong cặp từ khóa BEGIN …END. Cú pháp:

WHILE Boolean_expression

{sql_statement| statement_block} [BREAK]

{sql_statement| statement_block} [CONTINUE]

trong đó:

• Boolean_expression: Là biểu thức điều kiện để kiểm tra điều kiện lặp. Vòng lặp sẽ được thực hiện khi biểu thức trả về giá trị True và kết thúc vòng lặp khi trả về giá trị False.
• sql_statement|statement_block:Đó là câu lệnh SQL hoặc khối các câu lệnh SQL sẽ được lặp lại trong câu lệnh While. Khối các câu lệnh SQL được bao trong cặp từ khóa BEGIN … END
• BREAK: Từ khóa dùng để chỉ định dừng việc thực thi vòng lặp hiện tại. Tất cả các câu lệnh sau từ khóa BREAK và trước từ khóa END sẽ bị bỏ qua.
• CONTINUE: Từ khóa dùng để restart lại vòng lặp hiện tại tại ví trí bắt đầu. Tất cả các câu lệnh sau từ khóa CONTINUE và trước từ khóa END sẽ bị bỏ qua.

Ví dụ 4.5. Sử dụng cấu trúc lặp WHILE đơn giản.

Use pubs go

CREATE TABLE WhileLoopTest (

LoopID INT,

LoopValue VARCHAR(32)

) GO

SET NOCOUNT ON

DECLARE @intCounter INT

DECLARE @vchLoopValue VARCHAR(32)



SELECT @intCounter = 1 WHILE (@intCounter <= 100)

BEGIN

SELECT @vchLoopValue = 'Loop Interation #' + CONVERT (VARCHAR(4), @intCounter)

INSERT INTO WhileLoopTest(LoopID, LoopValue) VALUES (@intCounter, @vchLoopValue)

SELECT @intCounter = @intCounter + 1 END

Cấu trúc lặp GOTO: Tương tự như cấu trúc WHILE, GOTO có thể cho phép lặp một chuỗi câu lệnh cho đến khi điều kiện được thỏa mãn.

Chú ý: Câu lệnh GOTO không nhất thiết phải sử dụng trong các vòng lặp mà có thể sử dụng để thoát khỏi vòng lặp khác.

Để sử dụng câu lệnh GOTO, trước hết ta phải định nghĩa một nhãn. Nhãn là một câu lệnh chỉ định vị trí mà câu lệnh GOTO sẽ nhảy đến. Để tạo nhãn ta sử dụng cú pháp sau:

LABLE: Để nhảy đến nhãn trong code ta sử dụng câu lệnh GOTO theo cú pháp sau:

GOTO LABLE

Trong đó: LABLE là nhãn đã được định nghĩa ở trước đó trong code.

Bằng việc sử dụng GOTO, ta có thể nhảy đến một vị trí bất kỳ trong code.

Ví dụ 4.6. Sử dụng cấu trúc lặp GOTO đơn giản.

Use pubs Go

CREATE TABLE GotoLoopTest (

GotoID INT, GotoValue VARCHAR(32)

) GO



SET NOCOUNT ON



DECLARE

@intCounter

INT

DECLARE

@vchLoopValue

VARCHAR(32)



SELECT @intCounter = 0



LOOPSTART:

SELECT @intCounter = @intCounter + 1

SELECT @vchLoopValue = 'Loop Iteration #' + CONVERT(VARCHAR(4), @intCounter)

INSERT INTO GotoLoopTest(GotoID, GotoValue) VALUES (@intCounter, @vchLoopValue)

IF (@intCounter <= 1000) BEGIN

GOTO LOOPSTART

END

c) Cấu trúc rẽ nhánh

Cấu trúc IF…ELSE: Cấu trúc IF…ELSE là một khối các câu lệnh dùng để rẽ nhánh dựa trên các tham số được cung cấp. Cú pháp của khối câu lệnh IF như sau:

IF expression BEGIN

sql_statements END

[ELSE

BEGIN

sql_statements END]

Chú ý: Ta có thể sử dụng các cấu trúc IF lồng nhau.

Ví dụ 4.7. Sử dụng cấu trúc rẽ nhánh IF.

Use pubs Go

CREATE PROCEDURE uspCheckNumber @intNumber INT

AS

IF @intNumber < 1 BEGIN

PRINT 'Number is less that 1.' RETURN

END

ELSE IF @intNumber = 1 BEGIN

PRINT 'One' RETURN

END



ELSE IF @intNumber BEGIN

PRINT 'Two' RETURN

END

=

2

ELSE IF @intNumber BEGIN

PRINT 'Three' RETURN

END

=

3

ELSE IF @intNumber BEGIN

PRINT 'Four' RETURN

END

=

4

ELSE IF @intNumber BEGIN

PRINT 'Five' RETURN

END

=

5

ELSE IF @intNumber BEGIN

PRINT 'Six' RETURN

END

=

6

ELSE IF @intNumber BEGIN

PRINT 'Seven' RETURN

END

=

7

ELSE IF @intNumber BEGIN

PRINT 'Eight' RETURN

END

=

8

ELSE IF @intNumber BEGIN

PRINT 'Nine' RETURN

END

=

9

ELSE IF @intNumber BEGIN

PRINT 'Ten' RETURN

END

=

10

ELSE BEGIN


PRINT 'Number is greater than 10.' RETURN

END

Cấu trúc CASE: Cấu trúc này được dùng để đánh giá một biểu thức và trả về một hoặc một số các kết quả dựa vào giá trị của biểu thức. Có 2 kiểu cấu trúc CASE khác nhau như sau:

o Simple CASE: Với cấu trúc này, một biểu thức sẽ được dùng để so sánh với một tập các giá trị để xác định kết quả. Cú pháp như sau:

CASE case_expression

WHEN expression THEN result

[...n

[ELSE else_result

END

o Searched CASE: Đánh giá tập các biểu thức Boolean để xác định kết quả. Cú pháp của nó như sau:

CASE

WHEN Boolean_expression THEN result

[...n

[ELSE else_result
END

Trong đó:

• case_expression: Biểu thức dùng để SQL Serverđánh giá giá trị trong câu lệnh Simple CASE.
• Expression: Giá trị dùng để so sánh với biểu thứccase_expression nếu đúng thì nó sẽ trả về kết quả.
• Result: Kết quả sẽ được trả về nếu như giá trị biểu thứccase_expression so với Expression là đúng.
• Boolean_expression: SQL Server dùng biểu thức Boolean để rẽ nhánh, nếu biểu thực nhận giá trị True thì sẽ thực hiện kết quả Result.
• else_result: Thực hiện các kết quả sau ELSE.

Ví dụ 4.8. Sử dụng cấu trúc rẽ nhánh CASE dùng trong cả hai trường hợp Simple Case và Searched Case.

Use pubs Go

CREATE PROCEDURE uspCheckNumberCase @chrNumber CHAR(2)

AS

IF (CONVERT(INT, @chrNumber) < 1) OR (CONVERT(INT,

@chrNumber) > 10) BEGIN

SELECT CASE

WHEN CONVERT(INT, @chrNumber) < 1 THEN 'Number is less than 1.'

WHEN CONVERT(INT, @chrNumber) > 10 THEN 'Number is greater than 10.'

END RETURN

END

SELECT CASE CONVERT(INT, @chrNumber) WHEN 1 THEN 'One'

WHEN 2 THEN 'Two' WHEN 3 THEN 'Three' WHEN 4 THEN 'Four' WHEN 5 THEN 'Five' WHEN 6 THEN 'Six' WHEN 7 THEN 'Seven' WHEN 8 THEN 'Eight' WHEN 9 THEN 'Nine' WHEN 10 THEN 'Ten'

END

d) Cấu trúc WAITFOR

Cấu trúc WaitFor được dùng để ngăn việc thực thi một lô, thủ tục, hay một giao dịch cho đến một thời điểm nào đó hoặc sau một khoảng thời gian nào đó. Cú pháp của WAITFOR như sau:

WAITFOR { DELAY 'time' | TIME 'time' }

Trong đó:

1. DELAY: Chỉ định khoảng thời gian phải chờ. Tối đa là 24 giờ.
2. TIME: Chỉ định thời điểm thực thi một lô, thủ tục, hay một giao dịch.

Ví dụ 4.9. Sử dụng cấu trúc WAITFOR để chờ đến lúc 21h30 thì thực hiện xóa bản ghi.

BEGIN

WAITFOR TIME '21:30'

DELETE FROM DMLOP WHERE MALOP='TH6A'

END

Ví dụ 4.10. Xây dựng thủ tục time_delay để chờ trong một khoảng thời gian nào đó và đưa ra thông báo khoảng thời gian đã chờ đó.

CREATE PROCEDURE time_delay @DELAYLENGTH char(9) AS

DECLARE @RETURNINFO varchar(255) BEGIN

WAITFOR DELAY @DELAYLENGTH

SELECT @RETURNINFO = 'A total time of ' + SUBSTRING(@DELAYLENGTH, 1, 2) +

hours, ' + SUBSTRING(@DELAYLENGTH, 4, 2) +

minutes, and ' + SUBSTRING(@DELAYLENGTH, 7, 2) +

seconds ' +

'has elapsed! Your time is up.'; PRINT @RETURNINFO;

END; GO

-- This next statement executes the time_delay procedure. EXEC time_delay '00:05:00'

GO

e) Cấu trúc TRY…CATCH

Trong SQL Server 2005, cấu trúc TRY … CATCH được sử dụng để quản lý lỗi tương tự như các ngôn ngữ lập trình VB.NET, C# và C++. Cú pháp:

BEGIN TRY

{ sql_statement | statement_block } END TRY

BEGIN CATCH

{ sql_statement | statement_block } END CATCH[ ; ]

Hoạt động của cấu trúc TRY… CATCH:

• Cấu trúc TRY…CATCH gồm hai phần: Khối TRY và khối CATCH. Khi một điều kiện lỗi được dò thấy ở một câu lệnh Transact-SQL thuộc khối TRY, điều khiển được chuyển sang khốiCATCH để xử lý. Sau khi khối CATCH điều khiển ngoại lệ, điều khiển được chuyển cho câu lệnh Transact-SQL ngay sau lệnh END CATCH.
• Nếu không lỗi trong khối TRY, điều khiển được chuyển ngay lập tức cho câu lệnh sau END CATCH.

Ví dụ 4.11. Sử dụng cấu trúc TRY … CATCH để điều khiển lỗi.

BEGIN TRY

INSERT INTO [QLDiemSV].[dbo].[DMLOP]([MaLop],[TenLop], [Khoa]) VALUES ('TH6A','Tin học 6A','6')

END TRY BEGIN CATCH

Print ERROR_MESSAGE() END CATCH

Ví dụ 4.11. Xây dựng thủ tục đưa ra thông tin lỗi

USE QLDiemSV; GO

BEGIN TRANSACTION;



BEGIN TRY

-- Generate a constraint violation error. DELETE FROM LOP

WHERE MaLop='TH5A';

END TRY BEGIN CATCH

SELECT

ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() as ErrorState, ERROR_PROCEDURE() as ErrorProcedure, ERROR_LINE() as ErrorLine, ERROR_MESSAGE() as ErrorMessage;



IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;

END CATCH;

IF @@TRANCOUNT > 0 COMMIT TRANSACTION;

GO

f) Functions - Hàm

Hàm được dùng hoặc là định dạng và thao tác dữ liệu hoặc là trả về thông tin cho người sử dụng. Có hai loại hàm: hàm do hệ thống định nghĩa

hoặc hàm do người dùng định nghĩa. Hàm do hệ thống định nghĩa được tạo do Microsoft và được cài đặt khi SQL Server cài đặt. Hàm do người dùng định nghĩa được định nghĩa bởi người sử dụng bằng cách sử dụng câu lệnh CREATE FUNCTION . Đối với loại hàm này ta sẽ thảo luận chúng trong phần tiếp theo của chương.

Các hàm do hệ thống định nghĩa được chia thành các kiểu hàm sau: String functions, Date functions, Mathematical functions, aggregate Functions, System functions,.v.v...

String functions: Là các hàm thao tác với dữ liệu kiểu ký tự. Sau đây là một số hàm thông dụng.

• CHARINDEX(string1, string2, start_position): Tìm vị trí bắt đầu của chuỗi ký tự chỉ định string1 trong chuỗi string2 và bắt đầu tìm ở vị trí start_position trong chuỗi string2.

Ví dụ 4.9. Sử dụng hàm CHARINDEX

SELECT CHARINDEX('test', 'This is a test', 1) Hàm sẽ trả về giá trị 11, vị trí bắt đầu của chuỗi ‘test’ trong chuỗi 'This is a test'.

• LEFT (string, number_of_characters): Trả về chuỗi gồm number_of_characters ký tự tính từ trái sang của chuỗi string.

Ví dụ 4.10. Sử dụng hàm LEFT

SELECT LEFT(‘This is a test’, 4)

Hàm sẽ trả về chuỗi ‘This’

• LEN(string): Xác định độ dài của chuỗi ký tự string.

Ví dụ 4.11. Sử dụng hàm LEN

SELECT LEN(‘This is a test’)

Hàm sẽ trả về giá trị 14

• LOWER(string): Hàm trả về chuỗi ký tự thường.

Ví dụ 4.12. Sử dụng hàm LOWER

SELECT LOWER(‘This is a TEST’)

Hàm sẽ trả về chuỗi ‘this is a test’

• LTRIM(string): Cắt bỏ các ký tự trắng bên trái của chuỗi.

Ví dụ 4.13. Sử dụng hàm LTRIM

SELECT LTRIM(‘ This is a test ’)

Hàm sẽ trả về chuỗi ‘This is a test ’

• RIGHT (string, number_of_characters): trả về chuỗi gồm number_of_characters ký tự tính từ phải sang của chuỗi string.

Ví dụ 4.14. Sử dụng hàm RIGHT

SELECT RIGHT(‘This is a test’, 4)

Hàm sẽ trả về chuỗi ‘test’

• RTRIM(string): Cắt bỏ các ký tự trắng bên phải của chuỗi.

Ví dụ 4.15. Sử dụng hàm RTRIM

SELECT RTRIM(‘ This is a test ’)

Hàm sẽ trả về chuỗi ‘ This is a test’

• SUBSTRING ( expression ,start , length ): Hàm trả về chuỗi con gồm length ký tự của expression tính từ vị trí start.

SELECT x = SUBSTRING('abcdef', 2, 3)

• UPPER(string): Chuyển đổi các ký thường thành chữ hoa.

Ví dụ 4.16. Sử dụng hàm UPPER

SELECT UPPER(‘This is a TEST’)

Hàm sẽ trả về chuỗi ‘THIS IS A TEST’

Chú ý: Cần phải cẩn thận khi sử dụng các hàm, chẳng hạn khi ta sử dụng hàm UPPER trong vế trái của toán tử so sánh. Khi đó nó sẽ bắt SQL Server phải thực hiện trên một bảng để tìm kiếm giá trị. Ta xét hai truy vấn trong ví dụ 4.17 sau:

select au_lname from authors where au_lname = ‘Green’ select au_lname from authors where upper(au_lname) = ‘Green’

Đối với truy vấn thứ hai, sử dụng hàm Upper mất thời gian lâu hơn so với truy vấn thứ nhất.

Date Functions: Là các hàm làm việc với dữ liệu kiểu datetime. Một số hàm làm việc với các kiểu thông tin đặc biệt được gọi là datepart. Trước khi đi vào các hàm, ta xét các ký hiệu của datepart cho trong bảng 4.3.  

Sau đây là một số hàm hay sử dụng:

• DATEADD (datepart, amount, date): Cộng thêm một số amount thời gian thành phần datepart của date.

          Ví dụ 4.17. Sử dụng hàm DATEADD

                   SELECT DATEADD(year, 1, GETDATE()))

          Hàm sẽ trả về ngày hiện tại cộng thêm một năm.

• DATEDIFF (datepart, date1, date2): So sánh điểm khác nhau giữa hai ngày bằng việc sử dụng tham số datepart.

          Ví dụ 4.18. Sử dụng hàm DATEDIFF

                  SELECT DATEDIFF(hour, ‘1/1/2008 12:00:00’, ‘1/1/2008 16:00:00’)

           Hàm sẽ trả về giá trị 4. Đây là điểm khác nhau giữa hai ngày, hai ngày chênh nhau 4 giờ.

                   SELECT DATEDIFF(hour, ‘1/1/2008 12:00:00’, ‘1/2/2008 16:00:00’)

           Hàm sẽ trả về giá trị 28. Đây là điểm khác nhau giữa hai ngày, hai ngày chênh nhau 28 giờ.

• DATEPART(datepart, date): Hàm trả về giá trị của thành phần datepart trong date.

          Ví dụ 4.19. Sử dụng hàm DATEPART

                SELECT DATEPART(month, ‘1/1/2008 16:00:00’)

          Hàm sẽ trả về giá trị tháng 1.

• DAY(date): Xác định số ngày của tháng trong dữ liệu ngày giờ date.

          Ví dụ 4.20. Sử dụng hàm DAY

                    SELECT DAY ('7/22/1979 00:04:00')

          Hàm sẽ trả về giá trị ngày là 22.

• GETDATE(): Trả về giá trị ngày hiện tại của hệ thống.
• MONTH(date): Tương tự như hàm DAY, hàm MONTH trả về tháng của dữ liệu ngày giờ.
• YEAR(date): Trả về năm của dữ liệu ngày giờ.

Mathematical Functions: Sau đây ta trình bày một số hàm toán học thông thường.

• ABS(number): Trả về giá trị tuyệt đối của số number.
• CEILING(number): Trả về số nguyên nhỏ nhất lớn hơn hoặc bằng number.
• FLOOR(number): Trả về số nguyên lớn nhất nhỏ hơn hoặc bằng number.
• ROUND(number,precision): Hàm làm tròn sốnumber lấy precision chữ số sau dấu thập phân.
• SQUARE(number): Hàm trả về giá trị bình phương sốnumber.
• SQRT(number):Hàm trả về giá trị căn bậc hai sốnumber.

Aggregate Functions: Các hàm tập hợp thực hiện tính toán trên một tập hợp các giá trị và trả về một giá trị đơn. Ngoại trừ hàm COUNT, hàm tập hợp bỏ qua các giá trị NULL.

Các hàm tập hợp thường sử dụng với mệnh đề GROUP BY trong khối câu lệnh SELECT. Hàm tập hợp được phép dùng như là các biểu thức trong trường hợp:

1. Trong danh sách select của khối câu lệnh SELECT.
2. Trong mệnh đề COMPUTE hoặc COMPUTE BY .
3. Trong mệnh đề HAVING

Sau đây là một số hàm tập hợp hay được sử dụng:

• AVG ([ALL|DISTINCT]expression): Hàm trả về giá trị trung bình của tập các giá trị trong một nhóm.ALL: Áp dụng cho các hàm tập hợp để chỉ định cho tất cả các giá trị. ALL là từ khóa mặc định.DISTINCT:Chỉ định chỉ lấy một thể hiện duy nhất của một giá trị. Nghĩa là trong tập hợp có nhiều phần tử có cùng một giá trị thì chỉ lấy một giá trị đại diện cho nó.

Ví dụ 4.21. Sử dụng hàm AVG

USE pubs

SELECT AVG(advance), SUM(ytd_sales) FROM titles

WHERE type = 'business'

• COUNT({[ALL|DISTINCT]expression]|*}): Hàm trả về kiểu int số các phần tử của một nhóm.

Chú ý. Sử dụng hàm COUNT

1. COUNT(*): Trả về số các phần tử trong một nhóm bao gồm cả giá trị NULL và giá trị duplicates.
2. COUNT(ALL expression): Thực hiện định giá trị cho expression tại mỗi dòng trong nhóm và trả về số các giá trị không NULL.
3. COUNT(DISTINCT expression): Thực hiện định giá trị choexpressiontại mỗi dòng trong nhóm và trả về số các giá trị duy nhất và không NULL.

Ví dụ 4.22. Sử dụng hàm COUNT

USE pubs

GO

SELECT COUNT(DISTINCT city)

FROM authors GO

• COUNT_BIG({[ALL|DISTINCT]expression}|*): Trả về số các phần tử trong một nhóm. Hàm COUNT_BIG làm việc như hàm COUNT. Điểm khác nhau giữa chúng là hàm COUNT trả về giá trị kiểu int còn hàm COUNT_BIG trả về giá trị kiểu bigint.
• MAX ([ALL|DISTINCT]expression): Trả về giá trị lớn nhất trong biểu thức expression.
• MIN ([ALL|DISTINCT]expression): Trả về giá trị lớn nhất trong biểu thức expression.
• SUM([ALL|DISTINCT]expression): Trả về tổng của tất cả các giá trị của biểu thức hoặc tổng các giá trị DISTINCT của biểu thức expression. Hàm SUM chỉ áp dụng cho các cột kiểu số. Các giá trị NULL được bỏ qua.

Ví dụ 4.23. Sử dụng hàm SUM

USE pubs GO

-- Aggregate functions

SELECT type, SUM(price), SUM(advance) FROM titles

WHERE type LIKE '%cook' GROUP BY type

ORDER BY type GO

Ví dụ 4.24. Sử dụng hàm SUM để tính điểm trung bình trong CSDL QLDiemSV  

SELECT DIEM.Masv,(Convert(real, Sum( dbo.fncDiemCN(DiemL1,DiemL2)*MONHOC.SD

VHT))/convert(real,Sum(MONHOC.SDVHT))) AS DTB

FROM DIEM INNER JOIN MONHOC ON DIEM.MAMH=MONHOC.MaMH

GROUP BY DIEM.MaSV

System Functions: Các hàm hệ thống là các hàm lấy thông tin hệ thống về các đối tượng và đã thiết lập trong SQL Server.

• CONVERT (data_type, expression): Chuyển đổi biểu thức expression thành kiểu dữ liệu data_type.

          Ví dụ 4.25. Sử dụng hàm CONVERT

                  SELECT CONVERT(VARCHAR(5), 12345)

          Hàm sẽ trả về chuỗi ‘12345’.

• CAST (expression AS data_type): Chuyển đổi biểu thức expression thành kiểu dữ liệu data_type.

• CURRENT_USER: Trả về người sử dụng hiện tại.

          Ví dụ 4.26. Sử dụng hàm CURRENT_USER

                 SELECT CURRENT_USER

• DATALENGTH(expression): Trả về số byte được sử dụng trong biểu thức expression.

• HOST_NAME(): trả về tên máy tính mà người sử dụng hiện tại đang login.

          Ví dụ 4.27. Sử dụng hàm HOST_NAME()

                      SELECT HOST_NAME()

• SYSTEM_USER: Hàm trả về tên của các User đang login hệ thống.

          Ví dụ 4.28. Sử dụng hàm SYSTEM_USER

                      SELECT SYSTEM_USER

• USER_NAME(): Hàm trả về username khi đưa số user ID.  

          Ví dụ 4.29. Sử dụng hàm USER_NAME()

                     SELECT name FROM sysobjects WHERE USER_NAME(uid) = 'dbo'

Được cập nhật: 17 giờ trước (15:39:22) | Lượt xem: 680