Bài giảng SQL Server 2008: Chương 5

Chương 5 - LẬP TRÌNH T-SQL

Lecturer: Email: Website:

Nguyễn Đức Cương - FIT cuongnguyenduc@gmail.com http://www.nguyenduccuong.com

Nội dung

 Tổng quan

 Biến

 Cấu trúc điều khiển

 Thủ tục (store procedures)

 Hàm(function)

Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 2

TỔNG QUAN

1. Sử dụng T-SQL để lập trình các script và sp thực hiện các tác vụ đặc thù trên server. Chương trình bao gồm lệnh sql, biến, cấu trúc điều khiển .

a. Định danh (identifiers): tên các đối tượng

2. Các khái niệm cơ bản:

 Quy tắc:



Tối đa 128 ký tự Bắt đầu từ ký tự a  z, hoặc các ký tự @, # Các định danh có khoảng trắng phải đặt trong []

Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 3

trong CSDL

TỔNG QUAN

 Kiểu dữ liệu: có 2 loại

 System- supplied data type

 User – defined data type



Batch: tập các câu lệnh T-SQL liên tiếp kết thúc bằng lệnh GO

Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 4

Script: tập của 1 hoặc nhiều các bó lệnh được lưu thành một tập tin .SQL

BIẾN (Variable)

Biến: có 2 loại biến: biến cục bộ và biến toàn cục

– Được khai báo bên trong một sp , một batch

–

1. Biến cục bộ (Local variable):

–

Phạm vi hoạt động từ vị trí khai báo đến khi kết thúc sp, batch



Tên bắt đầu bằng ký tự @

Khai báo biến: DECLARE @ VariableName var_type

Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 5

Ví dụ: DECLARE @vEmpID int

BIẾN (Variable)

 Gán giá trị cho biến:

SET @VariableName = expression Hoặc: SELECT{@VariableName=expression} [,…n]

Ví dụ 1:

Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 6

DECLARE @temp_name varchar(20) SELECT @temp_name = companyname FROM customers WHERE customerid = ‘adsff’

BIẾN (Variable)

Ví dụ 2:

DECLARE @temp_city varchar(10)

SET @temp_city = ‘london’

SELECT * FROM Customers

WHERE city = @temp_city

Ví dụ 3:

DECLARE @temp_CustID Char(5), @temp_name

varchar(50)

SET @temp_CustID = ‘ALFKI’

SELECT @temp_name = CompanyName FROM Customers

Where CustomerID = @temp_CustID

PRINT ‘CustomerID is ‘ + @temp_CustID + ‘ and Name is ‘+

@temp_name

Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 7

BIẾN (Variable)

2. Biến toàn cục (Global Variables): Thực chất là các

hàm có sẵn trong SQL Server.

– Không thể gán giá trị cho biến – Biến toàn cục không có kiểu – Tên biến bắt đầu bằng @@

 @@VERSION:

Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 8

 Ví dụ: Select @@ version

BIẾN (Variable)

 @@SERVERNAME: tên sever

 @@ROWCOUNT: trả về số dòng bị ảnh hưởng bởi

Ví dụ: select @@SERVERNAME

lệnh thực thi gần nhất

Ví dụ: Update Employees set LastName = ‘Brooke’ Where LastName =‘Brook’ If(@@rowcount=0)

begin

print ‘Không dòng nào được cập nhật’ return

Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 9

end

BIẾN (Variable)

 @@ERROR: trả về số thứ tự lỗi của lệnh thực thi sau cùng, nếu trả về 0 thì câu lệnh hoàn thành

 @@IDENTITY: trả về số IDENTITY phát sinh sau

Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 10

cùng

Cấu trúc điều khiển

CASE Function: Có 2 dạng

a) Simple CASE function: CASE input_expression

WHEN when_expression THEN

result_expression [ ...n ]

[ELSE else_result_expression ]

END

b) Searched CASE function:

CASE

WHEN Boolean_expression THEN result_expression [ ...n ] [ ELSE else_result_expression ]

Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 11

END

Cấu trúc điều khiển

Ví dụ 1: SELECT Category =

CASE type

WHEN 'popular_comp' THEN 'Popular Computing' WHEN 'mod_cook' THEN 'Modern Cooking' WHEN 'business' THEN 'Business' WHEN 'psychology' THEN 'Psychology' WHEN 'trad_cook' THEN 'Traditional Cooking ELSE 'Not yet categorized‘

END CAST(title AS varchar(25)) AS 'Shortened Title', price AS Price FROM titles WHERE price IS NOT NULL ORDER BY type, price COMPUTE AVG(price) BY type

Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 12

Cấu trúc điều khiển

Ví dụ 2: SELECT ProductID, Quantity, UnitPrice, [discount%]=

CASE

WHEN Quantity <=5 THEN 0.05 WHEN Quantity BETWEEN 6 and 10 THEN 0.07 WHEN Quantity BETWEEN 11 and 20 THEN 0.09

ELSE 0.1 END

Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 13

FROM [Order Details] ORDER BY Quantity, ProductId

Cấu trúc điều khiển

 IF … ELSE:

IF boolean_expression

{sql_statement | statement_block}

[ELSE boolean_expression

{sql_statement | statement_block}]

 BEGIN …END: Khối lệnh

BEGIN

{sql_statement | statement_ block}

Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 14

END

Cấu trúc điều khiển

Ví dụ: IF ( SELECT COUNT(*) FROM authors WHERE contract =0) >0

BEGIN

PRINT 'These authors do not have contracts on file: ' SELECT au_lname, au_fname, au_id FROM authors WHERE contract=0

END ELSE BEGIN

PRINT 'All authors have contracts on file.'

Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 15

END

Cấu trúc điều khiển

 WHILE: WHILE boolean_expression

Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 16

{sql_statement | statement_block} [BREAK] {sql_statement | statement_block} [CONTINUE]

Cấu trúc điều khiển

Ví dụ 1: DECLARE @Counter INT SET @counter=0 WHILE (@counter<20)

BEGIN

INSERT INTO Pubs VALUES ('last'+CAST(@counter as char(2)), 'First') SET @counter=@counter+1

Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 17

END

Cấu trúc điều khiển

Ví dụ 2

WHILE (SELECT AVG(price) FROM titles) < $30 BEGIN

UPDATE titles SET price = price * 2 IF (SELECT MAX(price) FROM titles) > $50

BREAK

ELSE

CONTINUE

Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 18

END

Cấu trúc điều khiển

 PRINT: In thông tin ra màn hình kết quả của SQL

PRINT ‘any ACII

Text’|@local_variable|@@FUNTION| String_expr  RETURN:

RETURN [integer_expression]

 WAITFOR: SQL tạm dừng

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

Ví dụ:

BEGIN

WAITFOR TIME '22:20' EXECUTE update_all_stats

Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 19

END

Cấu trúc điều khiển

 RAISERROR: Gửi lỗi đến người dùng như một lỗi

hệ thống

RAISERROR({msg_id | msg_str} { , severity ,

state }

[ , argument [ ,...n ] ] )

– Msg_id: mã thông báo, được lưu trong bảng

sysmessage mã thông báo của người dùng được bắt đầu trên 50000

– Msg_str: Nội dung thông báo, tối đa 400 ký tự

Để truyền tham số vào trong thông báo dùng dạng %

Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 20

[ WITH option [ ,...n ] ]

Cấu trúc điều khiển

– D hoặc I: số nguyên

– O: Octal không dấu

–

– Loại ký tự:

–

P : Con trỏ

–

S: chuổi

Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 21

U : số nguyên không dáu

Bài tập

 Bài 1: Khai báo một biến @maso kiểu số nguyên, gán giá trị 7 cho biến @maso, viết câu lệnh lấy ra danh sách các sản phẩm có masp nhỏ hơn giá trị chứa trong biến @maso.

Giải

declare @maso int

set @maso=7

Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 22

select * from products where productid<=@maso

Bài tập

 Bài 2: Khai báo và giá giá trị đại diện cho nơi

Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 23

chuyển hàng của hoá đơn. Kiểm tra xem nơi này có bao nhiêu hoá đơn, giả sử là n. In ra cau thông báo cho biết ‘Có hoá đơn’. Nếu không có hoá đơn nào cả thì in ra câu ‘Không có hoá đơn’. Khi chy đoạn batch thì thay đổi giá trị của biến để kiểm chứng kết quả.

Bài tập

Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 24

GIẢI declare @NoiChuyen int declare @SoHD int set @NoiChuyen=1 set @sohd=(select count(orderid) as sohoadon from orders where ShipVia=@NoiChuyen) if (@sohd)>0 select 'Co '+cast(@sohd as nvarchar(10))+ ' hoa don' else select 'Khong co hoa don'

Bài tập

 Bài 3: Viết một vòng lặp WHILE để nhập 10 mẫu

 1 Hoc vien 1

tin tùy ý vào bảng HOCVIEN (MAHV, TENHV, LOP), với bảng dữ liệu như sau:

 2 Hoc vien 2

CDTH3

 ….

CDTH3

 10.

…. …. …..

Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 25

Hoc vien 10 CDTH3

Bài tập

Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 26

GIẢI create table HOCVIEN (MAHV int , TENHV nvarchar(12), LOP nvarchar(5)) go declare @i int set @i=1 while(@i<=10) begin insert hocvien values(@i,'Hoc Vien ' + cast(@i as nvarchar(12)), 'CDTH3') set @i=@i+1 end GO

Bài tập

 Bài 5: Viết một câu lệnh SELECT có sử dụng CASE dùng để hiển thị MaSP, TenSp, Soluong, Dongia, Huehong. Trong đó HueHong = Soluong * DonGia*TiLe, TiLe = 5% nếu Soluong <5

= 6% nếu 10>soluong >=5

= 8% NẾU 15>Soluong>=10

Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 27

=10% nếu Soluong>=15

Bài tập

GIẢI select p.ProductID as MaSP,ProductName as TenSp,Quantity as Soluong, p.UnitPrice as Dongia,Huehong =Quantity*p.UnitPrice*case when Quantity<5 then 0.05 when Quantity>=5 and Quantity<10 then 0.06 when Quantity>=10 and Quantity<15 then 0.08

Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 28

else 0.1 end from "Order Details" o,Products p where o.productid=p.productid

LẬP TRÌNH T-SQL

THỦ TỤC VÀ HÀM (STORED PROCEDURES AND FUNCTION)

Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 29

Giới thiệu

–

– Thực thi nhanh hơn

– Giảm lưu lượng trên mạng

– An ninh bảo mật hơn

Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 30

Lập trình theo module

A. STORE PROCEDURE

1. ĐẶC ĐIỂM:

–

– Gọi thủ tục khác.

–

Truyền tham số.

–

Trả về các giá trị tham số, chuyển giá trị tham số c ho các thủ tục được gọi.

Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 31

Trả về giá trị trạng thái thủ tục là thành công hay không thành công

A. STORE PROCEDURE

 System SP: lưu trong CSDL Master (sp)

2/CÁC LOẠI SP:

 Ví dụ: Muốn biết tất cả các tiến trình đang thực

vd: master.dbo.sp_helptext

 Extended SP: biên dịch thành các file .DLL

 Để xem tên file dll tương ứng dùng

hiện bởi user nào: sp_who @loginame='sa'

Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 32

sp_helptext ESP_name

A. STORE PROCEDURE

 User_defined : có thể là local, temporary, remote

 Local sp: được người dùng tạo ra như là một đối tượng của database, dùng thực thi các task trong database, có thể tạo sp ngay trong master db.

 Temporary sp: Có 2 loại: local và global (tên bắt

2/CÁC LOẠI SP:

Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 33

đầu bằng # và ##).

A. STORE PROCEDURE

3/Tạo stored Procedures

– – –

Tạo sp bằng Managerment Studio Tạo bằng wizard Bằng lệnh T_SQL: Trong QA có thể test trước khi thi hành sp.

Cú pháp :

CREATE PROCEDURE procedure_name [WITH option] AS

sql_statement [...]

Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 34

Có thể viết tắt lệnh là CREATE PROC.

A. STORE PROCEDURE

3/Tạo stored Procedures

Ví dụ:

CREATE PROCEDURE OrderSummary AS

SELECT Ord.EmployeeID,

SummSales = SUM(OrDet.UnitPrice *

OrDet.Quantity)

Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 35

FROM Orders AS Ord JOIN [Order Details] AS OrDet ON (Ord.OrderID = OrDet.OrderID) GROUP BY Ord.EmployeeID

A. STORE PROCEDURE

–

Xem nội dung thủ tục :

–

sp_helptext ‘Procedure_name’

Xem thông tin về người tạo :

–

sp_help ‘Procedure_name’

Xem các đối tượng mà các lệnh trong sp tham chiếu đến :

–

sp_depends Procedure_name

Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 36

Liệt kê tất cả các sp trong database : sp_stored_procedures

A. STORED PROCEDURES

4/Thi hành stored Procedures :

– Phải đặt lệnh thi hành sp ở dòng đầu của một query,

hay dòng đầu của một batch.

Ví dụ: sp_help Orders

Select * from Customers

USE Northwind GO Select * from Customers EXEC

sp_help Orders

Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 37

– Bắt đầu dòng lệnh gọi sp bằng EXECUTE hay EXEC Ví dụ

A. STORED PROCEDURES

–

Nếu gọi sp trong db khác, phải viết tên đầy đủ

db_name.owner.sp_name – Mã hoá để ngăn user đọc nội dung mã sp sử dụng

sql_statement [...n]

Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 38

WITH ENCRYPTION trong lệnh tạo sp Cú pháp: CREATE PROCEDURE procedure_name WITH ENCRYPTION AS

A. STORED PROCEDURES

5/ Sửa đổi nội dung sp:

Cú pháp ALTER PROCEDURE procedure_name [WITH option] AS

sql_statement [...n]

Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 39

A. STORED PROCEDURES

6/ Xoá sp

DROP PROC

Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 40

owner.stored_procedure_name Ví dụ: DROP Proc OrderSummary

A. STORED PROCEDURES

7/ Sử dụng tham số trong sp

Input parameter: Dùng để truyền giá trị vào trong sp.

Cú pháp : CREATE PROCEDURE procedure_name [@parameter_name [=default_value]

data_type]

Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 41

[WITH option] AS sql_statement [...n]

A. STORED PROCEDURES

 Thủ tục không tham số CROC PROC Shopper As SELECT cFrirstName, vLastNam, vEmailID FROM cshopper

 Thực thi

7/ Sử dụng tham số trong sp

Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 42

EXEC Shopper

A. STORED PROCEDURES

 Thủ tục có tham số

 Thực thi

CROC PROC Shopper_city @vCity char(15) As SELECT cFrirstName, vLastNam, vEmailID FROM cshopper

Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 43

EXEC Shopper ‘Woodbridge’

A. STORED PROCEDURES

7/ Sử dụng tham số trong sp

 Gán giá trị theo thứ tự:

 Gán giá trị theo tên biến

 EXEC Shopper ‘Woodbridge’

Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 44

Truyền tham số

A. STORED PROCEDURES

7/ Sử dụng tham số trong sp

b/Output parameter: Dùng để trả về giá trị.

Cú pháp :

CREATE PROCEDURE procedure_name

[@parameter_name data_type]

[=default_value] OUTPUT

[WITH option]

Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 45

sql_statement [...n]

A. STORED PROCEDURES

7/ Sử dụng tham số trong sp

VD :

int OUTPUT



CREATE PROC count_row @NumOfOrders AS SELECT @ NumOfOrders = COUNT(*) FROM Orders GO

Thực thi sp có tham số output: phải khai báo một biến để lưu giá trị trả về của tham số output.

DECLARE @num int EXEC count_row @num OUTPUT

Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 46

SELECT @num GO

A. STORED PROCEDURES

III. Quản lý lỗi

SQL Server cung cấp một số công cụ để quản trị

lỗi:

Lệnh RETURN

Thủ tục sp_addmessage

Lệnh RAISERROR

Function @@ERROR:

Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 47

Lệnh RETURN: Được sử dụng để buộc sp kết thúc không điều kiện. Lệnh RETURN có thể kết thúc sp đồng thời trả về mã tình trạng (status code ) phục vụ cho xử lý khác.

A. STORED PROCEDURES



VD: CREATE PROC ListCustomers @cus_id nchar(5) = NULL AS IF @cus_id IS NULL

BEGIN

PRINT ‘Hay nhap vao ma khach hang hop le!.’ PRINT ‘Dinh dang [a-z][a-z][a-z][a-z][a-z]’ RETURN

END

SELECT CustomerID, CompanyName, Phone

FROM Customers

Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 48

WHERE CustomerID = @cus_id

A. STORED PROCEDURES

VD2:

USE Northwind GO

CREATE PROC ListCustomers @cus_id nvarchar(5) = NULL AS

SELECT CustomerID, CompanyName, Phone FROM Customers WHERE CustomerID like @cus_id RETURN (@@rowcount)

Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 49

A. STORED PROCEDURES



Thi hành sp và xem mã tình trạng DECLARE @answer smallint EXEC @answer = ListCustomers ‘B%’ SELECT ‘Tong so khach hang ’, @answer

Sử dụng thủ tục sp_addmessage: Bạn có thể tạo các message thông báo lỗi (error msg) cho chuơng trình của mình bằng cách

Trong các sp, gọi hiển thị Trong các sp, gọi hiển thị các error msg các error msg

Định nghĩa trước các Định nghĩa trước các error msg và lưu vào error msg và lưu vào bảng sysmessage bảng sysmessage

RAISERROR

sp_addmessage

Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 50

A. STORED PROCEDURES



Cú pháp 1:

sp_addmessage

@msgnum = msg_id , @severity = severity , @msgtext = 'msg'



[ , [ @lang = ] 'language' ] [ , [ @with_log = ] 'with_log' ] [ , [ @replace = ] 'replace' ]

Cú pháp 2:

RAISERROR ( msg_id | msg_str , severity ,

state [ , argument [ ,...n ] ] )

Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 51

[ WITH option [ ,...n ] ]

A. STORED PROCEDURES

VD :

Tạo một error message bằng thủ tục sp_addmessage

EXEC sp_addmessage

@msgnum = 50001,

@severity = 10,

@msgtext=‘Khong the xoa. Khach hang co hoa don .’,

@withlog = ‘true’

Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 52

A. STORED PROCEDURES

VD: Hiển thị error message trong một thủ tục xoá khách hàng

CREATE PROC DeleteCust @cust_num nvarchar(5) = null AS IF EXISTS (SELECT customerID FROM Orders WHERE

customerID like @cust_num)

BEGIN

RAISERROR (50001, 10, 1) RETURN

END

Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 53

DELETE FROM Customers WHERE customerID like @cust_num GO

A. STORED PROCEDURES

Sử dụng @@ERROR

WHERE au_id = "172-32-1176“

IF @@ERROR = 547 print “Phát hiện lỗi"

Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 54

Trả về mã lỗi của lệnh sql vừa thi hành. @@ERROR sẽ trả về giá trị 0 nếu thành công. Ngược lại, nếu lệnh sql thi hành không thành công, nó sẽ trả về một giá trị tương ứng với message lỗi trong bảng sysmessages . a) Sử dụng @@ERROR để phát hiện lỗi VD: USE pubs GO UPDATE authors SET au_id = '172 32 1176‘

I. STORED PROCEDURES

4. Quản trị sp :

CREATE PROC name WITH RECOMPILE AS sql_statement

• Buộc SQL Server biên dịch lại sp mỗi lần chạy

EXEC procedure_name WITH RECOMPILE

Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 55

hay

A. STORED PROCEDURES

 Bài tập 1: Viết thủ tục Sp_Update_SP có tham số

 Bài tập 2:Viết một thủ tục dùng để lấy về tổng số lượng lập hoá đơn và đơn giá trung bình của của một sản phẩm trong một tháng năm nào đó khi biết mã sản phẩm

 Bài tập 3: Viết thủ tục trả về tổng số lượng nhập

dùng để cập nhật dữ liệu mới của một mẫu tin nào đó khi biết Masp trong bảng SanPham

Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 56

hoặc xuất của 1 sản phẩm nào đó (dùng @flag để phân biệt trả về số lượng nhập hoặc xuất).

Bài tập 1

create proc Sp_Update_SP @ma int,@giamoi money

update Products

set UnitPrice=@giamoi

where ProductID=@ma

--thực thi

Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 57

Sp_Update_SP 2,200

Bài tập 2

create proc sp_dgAVG @ma int, @thang int,@nam int as select count(o.orderid) as "tong so luong lap hoa don.", avg(UnitPrice) as "don gia trung binh." from orders o, "Order Details" d where o.orderid=d.orderid and month(OrderDate)=@thang and year(OrderDate)=@nam and ProductID=@ma

Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 58

--thực thi: sp_dgAVG 2,7,1997

Bài tập 3

create proc sp_TongSL @ma int, @flag bit as if @flag=0

select sum(Quantity) as "tong so luong nhap:" from orders o, "Order Details" d where o.orderid=d.orderid and ProductID=@ma and ShipVia=1 --Lọai nhập

else

select sum(Quantity) as "tong so luong nhap:" from orders o, "Order Details" d where o.orderid=d.orderid and ProductID=@ma and ShipVia=2 –Loại Xuất

Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 59

B. FUNCTION

–

1. SYSTEM FUNCTION

–

Các hàm định nghĩa sẵn: avg(), count(), count(*), sum(), max(), min(),...

Các function khác : getdate(), month(), upper(), user_name(),@@rowcount,...

–

2. USER-DEFINED FUNCTION

–

Có thể sử dụng biến và cấu trúc điều khiển trong function giống như sp.

–

Function có thể dùng trong lệnh sql, còn sp thì không

Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 60

Có thể tạo các function trả về 1 giá trị hoặc trả về 1 table.

B. FUNCTION



–

SQL Server cho phép tạo 3 loại function :

– Multi-statement Table-valued : Sử dụng nhiều câu lệnh để

Scalar: Trả về một giá trị . Function có thể nhận tới 1024 tham số hay không nhận tham số nào.

–

trả về một tập row.

 Quyền :



Inline Table-valued :Sử dụng một câu lệnh Select để trả về một tập row.



Phải có quyền trên lệnh CREATE FUNTION để tạo, sửa , xoá function.

Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 61

Phải có quyền EXECUTE để thi hành function.

B. FUNCTION

3. Tạo scalar function :

Cú pháp :

CREATE FUNCTION [ owner_name. ] function_name

( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } --Tham số truyền vào [ ,...n ] ] )

RETURNS scalar_return_data_type - -Kiểu dl của Giá trị trả về

của fun.

[ WITH < function_option> [ [,] ...n] ] [ AS ]

BEGIN

function_body RETURN scalar_expression

Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 62

END

B. FUNCTION

VD :--Tạo function

CREATE FUNCTION dbo.OrderNum ( @monthOrd tinyint ) RETURNS tinyint AS BEGIN

DECLARE @Ordnum tinyint SELECT @Ordnum = count(orderid) FROM Orders WHERE month(orderdate)= @monthOrd RETURN @Ordnum

END GO --Thi hành function .Chú ý : phải dùng tên đầy đủ

SELECT dbo.OrderNum(7) Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 63

B. FUNCTION



Có thể dùng function trong mệnh đề Where

Select orderid

from orders

where dbo.OrderNum(7) > 50

Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 64

and month(orderdate)=7

B. FUNCTION

Trả về hàm vô hướng – Định nghĩa

 CREATE FUNCTION Ham_Soluong_Phong (

@BienMaPhong CHAR (3) )

RETURNS int AS BEGIN

DECLARE @SL int; SELECT @SL = COUNT (*) FROM NhanVien WHERE MaPhong=@BienMaPhong ; RETURN (@SL);

Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 65

END

B. FUNCTION



Trả về hàm vô hướng – Sử dụng

SELECT dbo.Ham_Soluong_Phong('KDA');

SELECT MaPhong, count (*)

FROM NhanVien

GROUP BY MaPhong

HAVING count(*) >

Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 66

dbo.Ham_Soluong_Phong('QTA')

B. FUNCTION

 Ham cho ket qua la mot bang

CREATE FUNCTION Ham_DS_Phong (@BienMaPhong CHAR (3))

RETURNS @kq TABLE (Manv CHAR(5), HoTen

NCHAR(40), NgaySinh DATETIME)

BEGIN

INSERT INTO @kq SELECT Manv, HoTen, NgaySinh FROM NhanVien WHERE MaPhong=@BienMaPhong RETURN

Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 67

END

B. FUNCTION

 Ham cho ket qua la mot bang

-- Su dung ham tra ket qua bang nhu la TABLE

SELECT *

Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 68

FROM Ham_DS_Phong ('QTA')

B. FUNCTION

Ví dụ: Lấy ra các khách hàng có mã khách hàng tùy ý

Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 69

create function f_SelectCustomer (@customerid int) returns table as return (select * from customers where customerid > @customerid)

B. FUNCTION

-- Su dung ham tra ket qua bang nhu la TABLE

Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 70

SELECT * FROM Ham_DS_Phong ('QTA')

 [Categories] [CategoryID] ,[CategoryName]

 [Orders] ([OrderID],[CustomerID] ,[EmployeeID]

,[Description] ,[Picture]

 [Products][ProductID],[ProductName] ,[SupplierID]

,[OrderDate] ,[RequiredDate] ,[ShippedDate] ,[ShipVia] ,[Freight] ,[ShipName] ,[ShipAddress] ,[ShipCity] ,[ShipRegion] ,[ShipPostalCode] ,[ShipCountry

,[CategoryID] ,[QuantityPerUnit] ,[UnitPrice] ,[UnitsInStock] ,[UnitsOnOrder] ,[ReorderLevel]

Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 71

,[Discontinued]