Cấu trúc dữ liệu và giải thuật - Chương 5: Lập trình t-sql

Chương 5 - LẬP TRÌNH T-SQL Lecturer: Nguyễn Đức Cương - FIT Email: cuongnguyenduc@gmail.com Website: Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 2 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 3 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 . 2. Các khái niệm cơ bản: a. Định danh (identifiers): tên các đối tượng trong CSDL  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 4 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  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 Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 5 BIẾN (Variable) Biến: có 2 loại biến: biến cục bộ và biến toàn cục 1. Biến cục bộ (Local variable): – Được khai báo bên trong một sp , một batch – 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 Ví dụ: DECLARE @vEmpID int Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 6 BIẾN (Variable)  Gán giá trị cho biến: SET @VariableName = expression Hoặc: SELECT{@VariableName=expression} [,n] Ví dụ 1: DECLARE @temp_name varchar(20) SELECT @temp_name = companyname FROM customers WHERE customerid = ‘adsff’ Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 7 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 8 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:  Ví dụ: Select @@ version Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 9 BIẾN (Variable)  @@SERVERNAME: tên sever Ví dụ: select @@SERVERNAME  @@ROWCOUNT: trả về số dòng bị ảnh hưởng bởi 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 end Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 10 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 cùng Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 11 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 ] END Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 12 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 13 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 FROM [Order Details] ORDER BY Quantity, ProductId Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 14 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} END Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 15 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.' END Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 16 Cấu trúc điều khiển WHILE: WHILE boolean_expression {sql_statement | statement_block} [BREAK] {sql_statement | statement_block} [CONTINUE] Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 17 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 END Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 18 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 END Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 19 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 END Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 20 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 ] ] ) [ WITH option [ ,...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 21 Cấu trúc điều khiển – Loại ký tự: – D hoặc I: số nguyên – O: Octal không dấu – P : Con trỏ – S: chuổi – U : số nguyên không dáu Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 22 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 select * from products where productid<=@maso Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 23 Bài tập  Bài 2: Khai báo và giá giá trị đại diện cho nơi 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ả. Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 24 Bài tập 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' Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 25 Bài tập  Bài 3: Viết một vòng lặp WHILE để nhập 10 mẫu tin tùy ý vào bảng HOCVIEN (MAHV, TENHV, LOP), với bảng dữ liệu như sau:  1 Hoc vien 1 CDTH3  2 Hoc vien 2 CDTH3  . . . ..  10. Hoc vien 10 CDTH3 Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 26 Bài tập 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 Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 27 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 =10% nếu Soluong>=15 Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 28 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 else 0.1 end from "Order Details" o,Products p where o.productid=p.productid Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 29 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 30 Giới thiệu – Lập trình theo module – 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 31 A. STORE PROCEDURE 1. ĐẶC ĐIỂM: – Truyền tham số. – Gọi thủ tục khác. – Trả về các giá trị tham số, chuyển giá trị tham số c ho các thủ tục được gọi. – Trả về giá trị trạng thái thủ tục là thành công hay không thành công Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 32 A. STORE PROCEDURE 2/CÁC LOẠI SP:  System SP: lưu trong CSDL Master (sp) vd: master.dbo.sp_helptext  Ví dụ: Muốn biết tất cả các tiến trình đang thực hiện bởi user nào: sp_who @loginame='sa'  Extended SP: biên dịch thành các file .DLL  Để xem tên file dll tương ứng dùng sp_helptext ESP_name Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 33 A. STORE PROCEDURE 2/CÁC LOẠI SP:  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 đầu bằng # và ##). Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 34 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 [...] Có thể viết tắt lệnh là CREATE PROC. Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 35 A. STORE PROCEDURE 3/Tạo stored Procedures Ví dụ: CREATE PROCEDURE OrderSummary AS SELECT Ord.EmployeeID, SummSales = SUM(OrDet.UnitPrice * OrDet.Quantity) FROM Orders AS Ord JOIN [Order Details] AS OrDet ON (Ord.OrderID = OrDet.OrderID) GROUP BY Ord.EmployeeID Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 36 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 – Liệt kê tất cả các sp trong database : sp_stored_procedures Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 37 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 – Bắt đầu dòng lệnh gọi sp bằng EXECUTE hay EXEC Ví dụ USE Northwind GO Select * from Customers EXEC sp_help Orders Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 38 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 WITH ENCRYPTION trong lệnh tạo sp Cú pháp: CREATE PROCEDURE procedure_name WITH ENCRYPTION AS sql_statement [...n] GO Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 39 A. STORED PROCEDURES 5/ Sửa đổi nội dung sp: Cú pháp ALTER PROCEDURE procedure_name [WITH option] AS sql_statement [...n] GO Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 40 A. STORED PROCEDURES 6/ Xoá sp DROP PROC owner.stored_procedure_name Ví dụ: DROP Proc OrderSummary Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 41 A. STORED PROCEDURES 7/ Sử dụng tham số trong sp a) Input parameter: Dùng để truyền giá trị vào trong sp. Cú pháp : CREATE PROCEDURE procedure_name [@parameter_name data_type] [=default_value] [WITH option] AS sql_statement [...n] Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 42 A. STORED PROCEDURES 7/ Sử dụng tham số trong sp  Thủ tục không tham số CROC PROC Shopper As SELECT cFrirstName, vLastNam, vEmailID FROM cshopper  Thực thi EXEC Shopper Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 43 A. STORED PROCEDURES  Thủ tục có tham số CROC PROC Shopper_city @vCity char(15) As SELECT cFrirstName, vLastNam, vEmailID FROM cshopper  Thực thi EXEC Shopper ‘Woodbridge’ Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 44 A. STORED PROCEDURES 7/ Sử dụng tham số trong sp Truyền tham số  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 45 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] AS sql_statement [...n] Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 46 A. STORED PROCEDURES 7/ Sử dụng tham số trong sp VD : CREATE PROC count_row @NumOfOrders int OUTPUT 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 SELECT @num GO Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 47 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: 1. 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. Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 48 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 WHERE CustomerID = @cus_id Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 49 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) GO Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 50 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 2. 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 Định nghĩa trước các error msg và lưu vào bảng sysmessage Trong các sp, gọi hiển thị các error msg sp_addmessage RAISERROR Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 51 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 ] ] ) [ WITH option [ ,...n ] ] Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 52 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’ GO Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 53 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 DELETE FROM Customers WHERE customerID like @cust_num GO Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 54 A. STORED PROCEDURES 3. Sử dụng @@ERROR 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‘ 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 55 I. STORED PROCEDURES 4. Quản trị sp : • Buộc SQL Server biên dịch lại sp mỗi lần chạy CREATE PROC name WITH RECOMPILE AS sql_statement hay EXEC procedure_name WITH RECOMPILE Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 56 A. STORED PROCEDURES  Bài tập 1: Viết thủ tục Sp_Update_SP có tham số 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  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 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). Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 57 Bài tập 1 create proc Sp_Update_SP @ma int,@giamoi money as update Products set UnitPrice=@giamoi where ProductID=@ma --thực thi Sp_Update_SP 2,200 Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 58 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 --thực thi: sp_dgAVG 2,7,1997 Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 59 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 60 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 – Có thể tạo các function trả về 1 giá trị hoặc trả về 1 table. Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 61 B. FUNCTION  SQL Server cho phép tạo 3 loại function : – 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. – Multi-statement Table-valued : Sử dụng nhiều câu lệnh để trả về một tập row. – Inline Table-valued :Sử dụng một câu lệnh Select để trả về một tập row.  Quyền :  Phải có quyền trên lệnh CREATE FUNTION để tạo, sửa , xoá function.  Phải có quyền EXECUTE để thi hành function. Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 62 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 [ [,] ...n] ] [ AS ] BEGIN function_body RETURN scalar_expression END Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 63 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 64 B. FUNCTION  Có thể dùng function trong mệnh đề Where Select orderid from orders where dbo.OrderNum(7) > 50 and month(orderdate)=7 Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 65 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); END Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 66 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(*) > dbo.Ham_Soluong_Phong('QTA') Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 67 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 END Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 68 B. FUNCTION  Ham cho ket qua la mot bang -- Su dung ham tra ket qua bang nhu la TABLE SELECT * FROM Ham_DS_Phong ('QTA') Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 69 B. FUNCTION Ví dụ: Lấy ra các khách hàng có mã khách hàng tùy ý create function f_SelectCustomer (@customerid int) returns table as return (select * from customers where customerid > @customerid) Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 70 B. FUNCTION -- Su dung ham tra ket qua bang nhu la TABLE SELECT * FROM Ham_DS_Phong ('QTA') Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 71  [Categories] [CategoryID] ,[CategoryName] ,[Description] ,[Picture]  [Orders] ([OrderID],[CustomerID] ,[EmployeeID] ,[OrderDate] ,[RequiredDate] ,[ShippedDate] ,[ShipVia] ,[Freight] ,[ShipName] ,[ShipAddress] ,[ShipCity] ,[ShipRegion] ,[ShipPostalCode] ,[ShipCountry  [Products][ProductID],[ProductName] ,[SupplierID] ,[CategoryID] ,[QuantityPerUnit] ,[UnitPrice] ,[UnitsInStock] ,[UnitsOnOrder] ,[ReorderLevel] ,[Discontinued] Nguyễn Đức Cương – cuongnguyenduc@gmail.com Slide 72 Bài tập  1/ Viết hàm tính độ tuổi trung bình của nhân viên thuộc 1 thành phố.  2/ Viết thủ tục hiển thị danh mục chi tiết các hóa đơn (Mã hóa đơn, ngày lập, tổng tiền) được lập bởi 1 nhân viên đến từ 1 thành phố nào đó.  3/ Viết thủ tục thêm vào 1 hóa đơn (OrderId, Orderdate, CustomerID, EmpID) và chi tiết hóa đơn (OrderId, productId, Price, quanlity) đó