Chương 3. Lập trình với cơ sở dữ liệu

10/03/2015 1 CHƢƠNG 3. LẬP TRÌNH VỚI CƠ SỞ DỮ LIỆU TRƢỜNG CAO ĐẲNG CÔNG NGHỆ THÔNG TIN THÀNH PHỐ HỒ CHÍ MINH GV: Lê Thị Minh Nguyện Email: leminhnguyen@itc.edu.vn NỘI DUNG 2  Biến cục bộ  Biến hệ thống  Các câu lệnh truy vấn dữ liệu  Các hàm thường dùng  Cấu trúc điều khiển  Cấu trúc lặp  Biến kiểu dữ liệu cursor 10/03/2015 2 1.Biến cục bộ 3 Biến cục bộ: dùng để lưu trữ các giá trị tạm thời trong quá trình tính toán các xử lý bởi sau khi thoát khỏi chương trình hoặc tắt máy thì giá trị các biến này không còn trong bộ nhớ nữa.  Cú pháp: DECLARE @Tên_biến kiểu_dữ_liệu[,]  Ví dụ: DECLARE @ten_ncc varchar(50), @ngayxh DATETIME Gán giá trị cho biến:  Dùng SET hoặc SELECT cùng với phép gán (= ) 1.Biến cục bộ Ví dụ 1: để gán giá trị ngày 25/3/2002 vào biến ngày xuất hàng DECLARE @ngayxh DATETIME SET @ngayxh = „25-3-2014‟ Ví dụ 2: DECLARE @TongSLDat int SELECT @TongSLDat = SUM(SLDAT) FROM CTDH Ví dụ 3: DECLARE @MinSLDat int, @MaxSLDat int SELECT @MinSLDat = MIN(SLDAT), @MaxSLDat = MAX(SLDAT), FROM CTDH 10/03/2015 3 1.Biến cục bộ Xem giá trị hiện hành của biến Cú pháp: PRINT @Tên_biến | Biểu_thức_chuỗi Ví dụ: DECLARE @MinSLDat int, @MaxSLDat int SELECT @MinSLDat = MIN(SLDAT), @MaxSLDat = MAX(SLDAT), FROM CTHDON PRINT „Số lượng đặt thấp nhất là:‟ PRINT @MinSLDat PRINT „Số lượng đặt cao nhất là:‟ PRINT @MaxSLDat 5 Phạm vi hoạt động của biến  Trong Transaction-SQL phạm vi hoạt động của biến chỉ nằm trong một thủ tục nội tại (stored procedure) hoặc một lô (batch) chứa các câu lệnh mà biến đã được khai báo bên trong đó.  Lô được xem như một nhóm tập hợp của một hoặc nhiều câu lệnh Transaction-SQL sẽ được biên dịch đồng thời cùng lúc tại SQL Server.  Một từ khoá GO chỉ định kết thúc 1 lô 6 1.Biến cục bộ 10/03/2015 4 Do các câu lệnh trong một lô được biên dịch tại SQL Server vì thế khi có ít nhất 1 lệnh bên trong lô có lỗi về cú pháp lúc biên dịch thì hệ thống sẽ không có lệnh nào được thực thi bên trong lô đó. Ví dụ: SELECT * FROM NHACC ORDER BY TenNhaCC INSERT INTO NHACC („C01‟, „Nguyen Van A‟, ‟87 Ly Tu Trong‟,„0903.123456‟) SELECT * FROM VATTU ORDER BY Tenvtu DESC GO 7 (thiếu từ khoá VALUES) thì các lệnh SELECT bên trong lô này không được thực hiện. 1.Biến cục bộ Đối với các lỗi khi thực hiện (run-time) bên trong 1 lô nếu trường hợp các lỗi vi phạm ràng buộc toàn vẹn dữ liệu thì hệ thống SQL Server chỉ ngưng lại câu lệnh gây lỗi và thực hiện tiếp các lệnh bên trong lô đó. Ví dụ: SELECT * FROM NHACC ORDER BY TenNhaCC INSERT INTO NHACC VALUES („C01‟, „Nguyen Van A‟, ‟87 Ly Tu Trong‟,„0903.123456‟) SELECT * FROM VATTU ORDER BY Tenvtu DESC GO (mặc dù vi phạm ràng buộc toàn vẹn trong INSERT (giả sử trùng khoá chính ở cột MaNCC) nhưng các lệnh SELECT bên trong lô này vẫn được thực hiện bình thường. 1.Biến cục bộ 10/03/2015 5 Ví dụ: DECLARE @NgayDH datetime SELECT @NgayDH = MAX(NGAYDH) FROM DONDH GO PRINT “ngay dat hang gan nhat: ” + convert(char(12), @ngaydh) GO HT sẽ báo lỗi vì có thêm từ khoá GO ở giữa 2 lệnh SELECT và PRINT. Bởi vì khi đó các lệnh này được chia làm 2 lô và lô thứ hai sẽ không hiểu biến @ngaydh đã được khai báo trong lô thứ 1. 1.Biến cục bộ 2.Biến hệ thống Các biến hệ thống trong SQL Server luôn bắt đầu bằng 2 chữ @@. Giá trị mà chúng ta đang lưu trữ do hệ thống SQL cung cấp. Người lập trình không can thiệp trực tiếp để gán giá trị vào các biến hệ thống. 10 10/03/2015 6 Tên biến kiểu trả về Dùng để trả về connections số nguyên Tổng số các kết nối vào SQL Server từ khi nó được khởi động Error số nguyên số mã lỗi của câu lệnh thực hiện gần nhất. Khi một lệnh thực hiện thành công thì biến này có giá trị là 0 Language chuỗi Tên ngôn ngữ mà hệ thống SQL đag sử dụng. Mặc định là US_English RowCount số nguyên Tổng số mẩu tin được tác động vào câu lệnh truy vấn gần nhất ServerName chuỗi Tên của máy tính cục bộ được cài đặt trong SQL Server ServiceName chuỗi Tên dịch vụ kèm theo bên dưới SQL Server Fetch_Status số nguyên Trạng thái của việc đọc dữ liệu trong bảng theo cơ chế dòng mẩu tin (cursor). Khi dữ liệu đọc mẩu tin thành công thì biến này có giá trị là 0 Version chuỗi Phiên bản, ngày của phẩm SQL Server và loại CPU 2.Biến hệ thống Ví dụ: SELECT * FROM NHACC SELECT @@ROWCOUNT (Trả về tổng số mẩu tin đang hiện có trong bảng NHACC ) Ví dụ: UPDATE SANPHAM SET PHATRAM = PHANTRAM + 5 WHERE MAVTU like “TV%” SELECT @@ROWCOUNT (Trả về tổng số mẩu tin có MAVTU bắt đầu bằng chữ “TV” trong bảng VATTU) 12 2.Biến hệ thống 10/03/2015 7 3.Các câu lệnh truy vấn dữ liệu Truy vấn cơ bản Truy vấn lồng Hàm kết hợp Gom nhóm Điều kiện trên nhóm Cập nhật dữ liệu 13 3.1.Truy vấn cơ bản Gồm 3 mệnh đề  • Tên các cột cần được hiển thị trong kết quả truy vấn  • Tên các bảng liên quan đến câu truy vấn  • Biểu thức boolean xác định dòng nào sẽ được rút trích • Nối các biểu thức: AND, OR, và NOT • Phép toán:  ,  ,  ,  ,  , , LIKE và BETWEEN 14 SELECT FROM WHERE 10/03/2015 8 Ví dụ 15 SELECT * FROM NHANVIEN WHERE PHG=5 Lấy tất cả các cột của quan hệ kết quả SELECT MANV, HONV, TENLOT + TENNV as HOTEN FROM NHANVIEN WHERE PHG=5 AND PHAI=„Nam‟ SELECT MANV, LUONG*1.1 AS „LUONG10%‟ FROM NHANVIEN WHERE PHG=5 AND PHAI=„Nam‟ Mở rộng Tên bí danh Ví dụ 16 Loại bỏ các dòng trùng nhau - Tốn chi phí - Người dùng muốn thấy LUONG 30000 25000 25000 38000 LUONG 30000 25000 38000 SELECT LUONG FROM NHANVIEN WHERE PHG=5 AND PHAI=„Nam‟ SELECT DISTINCT LUONG FROM NHANVIEN WHERE PHG=5 AND PHAI=„Nam‟ 10/03/2015 9 Ví dụ 17 SELECT MANV, TENNV FROM NHANVIEN WHERE LUONG>=20000 AND LUONG<=30000 BETWEEN SELECT MANV, TENNV FROM NHANVIEN WHERE LUONG BETWEEN 20000 AND 30000 NOT BETWEEN SELECT MANV, TENNV FROM NHANVIEN WHERE LUONG NOT BETWEEN 20000 AND 30000 Ví dụ 18 IN SELECT MANV, TENNV FROM NHANVIEN WHERE PHG = 4 OR PHG=5 SELECT MANV, TENNV FROM NHANVIEN WHERE PHG IN (4,5) NOT IN SELECT MANV, TENNV FROM NHANVIEN WHERE PHG NOT IN (4,5) 10/03/2015 10 Ví dụ 19 SELECT MANV, TENNV FROM NHANVIEN WHERE DCHI LIKE „Nguyen _ _ _ _‟ LIKE SELECT MANV, TENNV FROM NHANVIEN WHERE DCHI LIKE „Nguyen %‟ Chuỗi bất kỳ Ký tự bất kỳ NOT LIKE SELECT MANV, TENNV FROM NHANVIEN WHERE HONV NOT LIKE „Nguyen‟ Ví dụ 20 NULL SELECT MANV, TENNV FROM NHANVIEN WHERE MA_NQL IS NULL SELECT MANV, TENNV FROM NHANVIEN WHERE MA_NQL IS NOT NULL 10/03/2015 11 Ví dụ Dùng để hiển thị kết quả câu truy vấn theo một thứ tự nào đó Cú pháp  ASC: tăng (mặc định)  DESC: giảm 21 SELECT FROM WHERE ORDER BY Ví dụ 22 SELECT MA_NVIEN, SODA FROM PHANCONG ORDER BY MA_NVIEN DESC, SODA SODA 10 30 999887777 999887777 MA_NVIEN 10 30 987987987 987654321 987987987 10 20 987654321 30 987654321 10/03/2015 12 3.2.Truy vấn lồng Chỉ một câu lệnh truy vấn lựa chọn (SELECT) được lồng vào các câu lệnh truy vấn khác nhằm thực hiện các truy vấn tính toán phức tạp. Khi sử dụng đến truy vấn lống chúng ta cần lưu tâm đến một vài yếu tố sau:  Cần mở và đóng ngoặc đơn cho câu lệnh truy vấn con.  Chúng ta chỉ được phép tham chiếu đến tên một cột hoặc một biểu thức sẽ trả về giá trị trong truy vấn con.  Kết quả truy vấn con có thể trả về là một giá trị đơn lẻ hoặc một danh sách các giá trị.  Cấp độ lồng nhau của các truy vấn con bên trong SQL Server là không giới hạn. 23 Mệnh đề WHERE của câu truy vấn cha   So sánh tập hợp thường đi cùng với một số toán tử • = ,, >=, • IN, NOT IN • ALL • ANY hoặc SOME  Kiểm tra sự tồn tại • EXISTS • NOT EXISTS 24 3.2.Truy vấn lồng 10/03/2015 13  Truy vấn con trả về một giá trị đơn: là truy vấn mà kết qủa trả về của nó luôn đảm bảo chỉ là một giá trị đơn.  Ví dụ: để biết được danh sách các đơn đặt hàng gần đây nhất. SELECT MAX(NGAYDH) FROM DONDH Kết quả trả về: 2015-01-25 00:00:00 SELECT * FROM DONDH WHERE NGAYDH = “2015-01-25”  Kết hợp 2 câu truy vấn trên SELECT * FROM DONDH WHERE NGAYDH = (SELECT MAX(NGAYDH) FROM DONDH) 25 3.2.Truy vấn lồng  Truy vấn con trả về danh sách các giá trị: trả về của nó là danh sách các giá trị hay còn gọi là một tập hợp các phần tử. Toán tử IN sẽ được sử dụng để so sánh truy vấn con dạng này  Ví dụ 1: để biết nhà cung cấp nào mà công ty đã đặt hàng trong tháng 01/2015. SELECT MaNCC FROM DONDH WHERE Convert(char(7), NgayDH, 21) = “2015-07”  Kết quả trả về MaNCC C03 C01 26 3.2.Truy vấn lồng SELECT TenNCC, DienThoai FROM NHACC WHERE MaNCC IN(“C01”, “C03”) 10/03/2015 14 Đâu đảm bảo rằng trong tháng 01/2002 công ty chỉ đặt hàng cho 2 nhà cung cấp C01 và C03. Do đó để luôn luôn có được danh sách họ tên các nhà cung cấp mà công ty đã đặt trong tháng 01-2002 chúng ta thực hiện truy vấn con sau: 27 SELECT TenNCC, DienThoai FROM NHACC WHERE MaNCC IN(SELECT MaNCC FROM DONDH WHERE Covert(char(7), NgayDH, 21) = “2015-01”) 3.2.Truy vấn lồng Có 2 loại truy vấn lồng  Lồng phân cấp • Mệnh đề WHERE của truy vấn con không tham chiếu đến thuộc tính của các quan hệ trong mệnh đề FROM ở truy vấn cha • Khi thực hiện, câu truy vấn con sẽ được thực hiện trước  Lồng tương quan • Mệnh đề WHERE của truy vấn con tham chiếu ít nhất một thuộc tính của các quan hệ trong mệnh đề FROM ở truy vấn cha • Khi thực hiện, câu truy vấn con sẽ được thực hiện nhiều lần, mỗi lần tương ứng với một bộ của truy vấn cha 28 3.2.Truy vấn lồng 10/03/2015 15 SELECT TenNCC, DienThoai FROM NHACC WHERE MaNCC IN (SELECT MaNCC FROM DONDH WHERE Convert(char(7), NgayDH, 21) = “2015-01”) 29 3.2.Truy vấn lồng Lồng phân cấp SELECT TenNCC, DienThoai FROM NHACC WHERE EXISTS (SELECT * FROM DONDH WHERE Covert(char(7), NgayDH, 21) = “2007-01” AND NHACC.MaNCC = DONDH.MaNCC) 30 Lồng tương quan 3.2.Truy vấn lồng 10/03/2015 16 Ví dụ  Ví dụ 2: Danh sách các nhà cung cấp nào mà công ty chưa bao giờ đặt hàng: SELECT TenNhaCC, DienThoai FROM NHACC WHERE MaNCC NOT IN (SELECT Distinct MaNCC FROM DONDH)  Hoặc SELECT TenNhaCC, DienThoai FROM NHACC WHERE MaNCC ALL (SELECT Distinct MaNCC FROM DONDH) 31 Lưu ý: IN tương đương =ANY NOT IN tương đượng ALL Ví dụ  Tìm những nhân viên không có thân nhân nào 32 SELECT * FROM NHANVIEN WHERE MANV NOT IN ( SELECT MA_NVIEN FROM THANNHAN ) SELECT * FROM NHANVIEN WHERE NOT EXISTS ( SELECT * FROM THANNHAN WHERE MANV=MA_NVIEN) Lồng phân cấp Lồng tƣơng quan 10/03/2015 17 3.3.Hàm kết hợp COUNT  COUNT(*) đếm số dòng  COUNT() đếm số giá trị khác NULL của thuộc tính  COUNT(DISTINCT ) đếm số giá trị khác nhau và khác NULL của thuộc tính MIN MAX SUM AVG Các hàm kết hợp được đặt ở mệnh đề SELECT 33 34 Ví dụ 10/03/2015 18 Ví dụ Tìm tổng lương, lương cao nhất, lương thấp nhất và lương trung bình của các nhân viên Cho biết số lượng nhân viên của phòng „Nghiên cứu‟ 35 SELECT SUM(LUONG), MAX(LUONG), MIN(LUONG), AVG(LUONG) FROM NHANVIEN SELECT COUNT(*) AS SL_NV FROM NHANVIEN, PHONGBAN WHERE PHG=MAPHG AND TENPHG=‘Nghiên cứu’ 3.4.Gom nhóm Cú pháp Sau khi gom nhóm  Mỗi nhóm các bộ sẽ có cùng giá trị tại các thuộc tính gom nhóm 36 SELECT FROM WHERE GROUP BY 10/03/2015 19 Ví dụ Cho biết số lượng nhân viên của từng phòng ban 37 SELECT PHG, COUNT(*) AS SL_NV FROM NHANVIEN GROUP BY PHG SELECT TENPHG, COUNT(*) AS SL_NV FROM NHANVIEN, PHONGBAN WHERE PHG=MAPHG GROUP BY TENPHG Ví dụ Với mỗi nhân viên cho biết mã số, họ tên, số lượng đề án và tổng thời gian mà họ tham gia 38 SELECT MA_NVIEN, COUNT(*) AS SL_DA, SUM(THOIGIAN) AS TONG_TG FROM PHANCONG GROUP BY MA_NVIEN SELECT MA_NVIEN ,HONV, TENNV, COUNT(*) AS SL_DA, SUM(THOIGIAN) AS TONG_TG FROM PHANCONG, NHANVIEN WHERE MA_NVIEN=MANV GROUP BY MA_NVIEN, HONV, TENNV 10/03/2015 20 3.5.Điều kiện trên nhóm Cú pháp Cho biết những nhân viên tham gia từ 2 đề án trở lên 39 SELECT FROM WHERE GROUP BY HAVING SELECT MA_NVIEN, COUNT(*) as SLDA FROM PHANCONG GROUP BY MA_NVIEN HAVING COUNT(*) >= 2 Ví dụ Cho biết những phòng ban (TENPHG) có lương trung bình của các nhân viên lớn lơn 20000 40 SELECT PHG, AVG(LUONG) AS LUONG_TB FROM NHANVIEN GROUP BY PHG HAVING AVG(LUONG) > 20000 SELECT TENPHG, AVG(LUONG) AS LUONG_TB FROM NHANVIEN, PHONGBAN WHERE PHG=MAPHG GROUP BY TENPHG HAVING AVG(LUONG) > 20000 40 10/03/2015 21 Ví dụ Tìm những phòng ban có lương trung bình cao nhất 41 SELECT PHG, AVG(LUONG) AS LUONG_TB FROM NHANVIEN GROUP BY PHG HAVING MAX(AVG(LUONG)) SELECT PHG, AVG(LUONG) AS LUONG_TB FROM NHANVIEN GROUP BY PHG HAVING AVG(LUONG) >= ALL ( SELECT AVG(LUONG) FROM NHANVIEN GROUP BY PHG) Kết luận 42 SELECT FROM [WHERE ] [GROUP BY ] [HAVING ] [ORDER BY ] 10/03/2015 22 3.6.Cập nhật dữ liệu Thêm (insert) Xóa (delete) Sửa (update) 43 3.6.1.Thêm (insert) Dùng để thêm 1 hay nhiều dòng vào bảng Để thêm dữ liệu  Tên quan hệ  Danh sách các thuộc tính cần thêm dữ liệu  Danh sách các giá trị tương ứng Cú pháp (thêm 1 dòng) 44 INSERT INTO () VALUES () 10/03/2015 23 Ví dụ 45 INSERT INTO NHANVIEN(HONV, TENLOT, TENNV, MANV) VALUES („Le‟, „Van‟, „Tuyen‟, „635635635‟) INSERT INTO NHANVIEN VALUES („Le‟, „Van‟, „Tuyen‟, „635635635‟, ‟12/30/1952‟, ‟98 HV‟, „Nam‟, „37000‟, 4) INSERT INTO NHANVIEN(HONV, TENLOT, TENNV, MANV, DCHI) VALUES („Le‟, „Van‟, „Tuyen‟, „635635635‟, NULL) Nhận xét  Thứ tự các giá trị phải trùng với thứ tự các cột  Có thể thêm giá trị NULL ở những thuộc tính không là khóa chính và NOT NULL  Câu lệnh INSERT sẽ gặp lỗi nếu vi phạm RBTV • Khóa chính • Tham chiếu • NOT NULL - các thuộc tính có ràng buộc NOT NULL bắt buộc phải có giá trị 46 3.6.1.Thêm (insert) 10/03/2015 24 Cú pháp (thêm nhiều dòng) 47 INSERT INTO () 3.6.1.Thêm (insert) Ví dụ 48 CREATE TABLE THONGKE_PB ( TENPHG VARCHAR(20), SL_NV INT, LUONG_TC INT ) INSERT INTO THONGKE_PB(TENPHG, SL_NV, LUONG_TC) SELECT TENPHG, COUNT(MANV), SUM(LUONG) FROM NHANVIEN, PHONGBAN WHERE PHG=MAPHG GROUP BY TENPHG 10/03/2015 25 Dùng để xóa các dòng của bảng Cú pháp: Ví dụ: 49 DELETE FROM [WHERE ] DELETE FROM NHANVIEN WHERE MANV=„345345345‟ DELETE FROM NHANVIEN 3.6.2.Xóa (delete) Ví dụ Xóa đi những nhân viên ở phòng „Nghien cuu‟ 50 DELETE FROM NHANVIEN WHERE PHG IN ( SELECT MAPHG FROM PHONGBAN WHERE TENPHG=„Nghien cuu‟) 10/03/2015 26 Nhận xét  Số lượng số dòng bị xóa phụ thuộc vào điều kiện ở mệnh đề WHERE  Nếu không chỉ định điều kiện ở mệnh đề WHERE, tất cả các dòng trong bảng sẽ bị xóa  Lệnh DELETE có thể gây ra vi phạm RB tham chiếu • Không cho xóa • Xóa luôn những dòng có giá trị đang tham chiếu đến – CASCADE • Đặt NULL cho những giá trị tham chiếu 51 3.6.2.Xóa (delete) 3.6.3. Sửa (update) Dùng để thay đổi giá trị của thuộc tính cho các dòng của bảng Cú pháp: 52 UPDATE SET =, =, [WHERE ] 10/03/2015 27 Ví dụ 53 UPDATE NHANVIEN SET NGSINH=‟08/12/1965‟ WHERE MANV=„333445555‟ UPDATE NHANVIEN SET LUONG=LUONG*1.1 Nhận xét  Những dòng thỏa điều kiện tại mệnh đề WHERE sẽ được cập nhật giá trị mới  Nếu không chỉ định điều kiện ở mệnh đề WHERE, tất cả các dòng trong bảng sẽ bị cập nhật  Lệnh UPDATE có thể gây ra vi phạm RB tham chiếu • Không cho sửa • Sửa luôn những dòng có giá trị đang tham chiếu đến – CASCADE 54 3.6.3. Sửa (update) 10/03/2015 28 4.Các làm thường dùng Các hàm chuyển đổi kiểu dữ liệu Các hàm về ngày 55 4.1.Các hàm chuyển đổi kiểu dữ liệu Hàm CAST: chuyển đổi một biểu thức nào đó sang một kiểu dữ liệu mong muốn. Cú pháp: CAST(Biểu_thức AS kiểu_dữ_liệu) Ví dụ: SELECT MaVTU, TenVT, TyLe = CAST(PHANTRAM AS VARCHAR(3)) + ‘%’ FROM VATTU 56 10/03/2015 29 Hàm CONVERT: chuyển đổi một biểu thức nào đó sang một kiểu dữ liệu bất kỳ mong muốn nhưng có thể theo một định dạng nào đó. Cú pháp: CONVERT (Kiểu_dữ_liệu, Biểu_thức[, định_dạng]) Ví dụ: SELECT SoHD, CONVERT(char(10),NgayHD, 103) AS NGAYHD FROM DONDH 57 4.1.Các hàm chuyển đổi kiểu dữ liệu 58 Bảng chuyển mô tả viết tắt của đơn vị STT Định dạng năm (yyyy) Hiển thị dữ liệu 1 101 Mm/dd/yy 2 102 yy.mm.dd 3 103 Dd/mm/yy 4 104 dd.mm.yy 5 105 dd-mm-yy 6 106 Dd mon yy 7 107 Mon dd, yy 8 108 Hh:mm:ss 9 109 Mon dd yyyy hh:mm:ss 10 110 mm-dd-yy 11 111 Yy/mm/dd 12 112 Yymmdd 13 113 Dd mon yyyy hh:mm:ss 14 114 Hh:mm:ss:mmm 15 21 hoặc 121 Yyyy-mm-dd hh:mi:ss.mmm 16 20 hoặc 120 Yyyy-mm-dd hh:mi:ss 58 58 10/03/2015 30 Hàm STR: chuyển đổi kiểu dữ liệu số sang kiểu dữ liệu chuỗi. Phải đảm bảo đủ khoảng trắng để chứa các ký số khi chuyển sang kiểu dữ liệu chuỗi. Cú pháp: STR(Số_thực, Số_ký_tự[, Số_lẻ]) Ví dụ: SELECT TenVT, SLNhap = STR(SLNhap, 5) + „ ‟ + DVTinh FROM VATTU, CTPNHAP WHERE VATTU.MaVT = CTPNHAP.MaVT 59 4.1.Các hàm chuyển đổi kiểu dữ liệu  Hàm ASCII: ASCII(string) Hàm trả về mã ASCII của ký tự đầu tiên bên trái của chuỗi đối số  Hàm CHAR: CHAR(ascii_code) Hàm trả về ký tự có mã ASCII tương ứng với đối số  Hàm CHARINDEX: CHARINDEX(string1,string2[,start]) Hàm trả về vị trí đầu tiên tính từ vị trí start tại đó chuỗi string1 xuất hiện trong chuỗi string2.  Hàm LEFT: LEFT(string,number) Hàm trích ra number ký tự từ chuỗi string tính từ phía bên trái 60 4.1.Các hàm chuyển đổi kiểu dữ liệu 10/03/2015 31 4.2.Các hàm trên dữ liệu kiểu chuỗi  Hàm LEN: LEN(string) Hàm trả về độ dài của chuỗi string  Hàm LOWER : LOWER(string) Hàm có chức năng chuyển chuỗi string thành chữ thường, kết quả được trả về cho hàm  Hàm UPPER: UPPER(string) Chuyển chuỗi string thành chữ hoa  Hàm LTRIM: LTRIM(string) Cắt bỏ các khoảng trắng thừa bên trái chuỗi string  Hàm NCHAR: NCHAR(code_number) Hàm trả về ký tự UNICODE có mã được chỉ định 61  Hàm REPLACE: REPLACE(string1,string2,string3) Hàm trả về một chuỗi có được bằng cách thay thế các chuỗi string2 trong chuỗi string1 bởi chuỗi string3.  Hàm REVERSE: REVERSE(string) Hàm trả về chuỗi đảo ngược của chuỗi string.  Hàm RIGHT: RIGHT(string, number) Hàm trích ra number ký tự từ chuỗi string tính từ phía bên phải.  Hàm RTRIM: RTRIM(string) Cắt bỏ các khoảng trắng thừa bên phải của chuỗi string. 62 4.2.Các hàm trên dữ liệu kiểu chuỗi 10/03/2015 32  Hàm SPACE: SPACE(number) Hàm trả về một chuỗi với number khoảng trắng.  Hàm SUBSTRING: SUBSTRING(string, m, n) Trích ra từ n ký tự từ chuỗi string bắt đầu từ ký tự thứ m.  Hàm UNICODE : UNICODE(UnicodeString) Hàm trả về mã UNICODE của ký tự đầu tiên bên trái của chuỗi UnicodeString. 63 4.2.Các hàm trên dữ liệu kiểu chuỗi 4.4.Các hàm về ngày  DATEDIFF: trả về 1 số nguyên khoảng cách của hai ngày theo một đơn vị thời gian bất kỳ DATEDIFF(don_vi, ngay1, ngay2)  Đơn vị • DW: trả về ngày trong tuần • DD: trả về ngày • MM: trả về tháng • QQ: trả về quý • YYYY: trả về năm  Ví dụ: SELECT MADH, SONGAY = DATEDIFF(DD,NGAYDH,NGAYGH) FROM DONDH 64 10/03/2015 33  DATENAME: trả về một chuỗi thời gian đại diện của 1 ngày chỉ định theo một đơn vị thời gian bất kỳ DATENAME(Don_vi, ngay)  Ví dụ: SELECT MADH, THU = DATENAME(DW, NGAYDH) FROM DONDH 65 4.4.Các hàm về ngày  GETDATE: trả về ngày giờ hiện hành của hệ thống GETDATE()  DATEPART: trả về 1 số nguyên chỉ định thời gian đại diện của 1 ngày theo một đơn vị thời gian bất kỳ DATEPART(Don_vi, ngày)  Ví dụ: SELECT SODH, THANG = DATEPART(MM, NGAYDH) FROM DONDH 66 4.4.Các hàm về ngày 10/03/2015 34 Cấu trúc Case Cấu trúc IF 67 5.Cấu trúc điều khiển 5.1. Cấu trúc Case  Cho phép kiểm tra điều kiện và xuất thông tin theo từng trường hợp  Cú pháp: 68 CASE WHEN THEN WHEN THEN [ELSE ] END 10/03/2015 35 Ví dụ 1: Cho biết họ tên các nhân viên đã đến tuổi về hưu (nam 60 tuổi, nữ 55 tuổi) 69 SELECT HONV, TENNV FROM NHANVIEN WHERE YEAR(GETDATE()) – YEAR(NGSINH) >= ( CASE PHAI WHEN 'Nam' THEN 60 WHEN 'Nu' THEN 55 END ) 5.1. Cấu trúc Case Ví dụ 2: hiển thị danh sách các sản phẩm có trong bảng SANPHAM theo từng loại hàng SELECT MAVTU, TenVTU, Loai = CASE LEFT(MAVTU,2) When “DD” THEN “Đầu DVD” When “VD” THEN “Đầu VCD” When “TV” THEN “Tivi” When “TL” THEN “Tủ lạnh” When “LO” THEN “Loa thùng” ELSE “chưa phân loại” End, DVTinh FROM VATTU 70 5.1. Cấu trúc Case 10/03/2015 36 Ví dụ 3: Giảm giá bán hàng trong tháng 2-2007 theo quy tắc sau: Nếu số lượng hàng <= 2 thì không giảm giá, Nếu số lượng hàng từ 3 đến 10 thì giảm 10%, Nếu số lượng hàng > 10 thì giảm 20%. UPDATE CTPXUAT SET DGXuat = CASE WHEN SLXUAT <=2 THEN DGXuat WHEN SLXUAT BETWEEN 3 AND 10 THEN DGXuat * 0.9 ELSE DGXuat*0.8 END FROM CTPXUAT, PXUAT WHERE CTPXUAT.SoPX = PXUAT.SoPX AND Convert(char(7), NgayXuat, 21) = “2007-02” 71 5.1. Cấu trúc Case 5.2. Cấu trúc IF Cú pháp IF Biểu_thức_luận_lý Câu_lệnh1|khối_lệnh1 ELSE Câu_lệnh2|khối_lệnh2 72 10/03/2015 37 Ví dụ: Cho biết vật tư nào đã bán ra với số lượng nhiều hơn 4 không? Nếu có thì hiển thị danh sách đó ra, ngược lại thì thông báo chưa bán vật tư nào nhiều hơn 4 IF (SELECT COUNT(*) FROM CTPXUAT WHERE SLXUAT>4)>0 BEGIN Print “Danh sách các hàng hoá bán ra với số lượng lớn hơn 4” SELECT CTPXUAT.MAVT, TENVT, SLXUAT FROM CTPXUAT, VATTU WHERE CTPXUAT.MaVT = VATTU.MaVT AND SLXUAT>4 END ELSE Print “chưa bán hàng nào với số lượng lớn hơn 4” 73 5.2. Cấu trúc IF Cú pháp: 74 WHILE Biểu_thức_luận_lý BEGIN Các_lệnh_lặp END 6.Cấu trúc lặp 10/03/2015 38 Ví dụ 1: Để in ra 10 số nguyên dương bắt đầu từ 100. DECLARE @Songuyen INT SET @Songuyen = 100 WHILE (@Songuyen < 110) BEGIN Print “Số nguyên: ” + convert(char(3), @songuyen) SET @Songuyen = @Songuyen +1 END 75 6.Cấu trúc lặp WHILE Biểu_thức_luận_lý BEGIN Các_lệnh_nhóm_lặp_1 [IF Biểu_thức_lặp_Tiếp CONTINUE ] [IF Biểu_thức_thoát BREAK ] Các_lệnh_nhóm_lặp_2 END Các_lệnh_khác 76 6.Cấu trúc lặp 10/03/2015 39  Từ khoá BREAK lồng vào cấu trúc WHILE để có thể kết thúc việc lặp của các lệnh bên trong vòng lặp DECLARE @Songuyen int SET @Songuyen = 100 WHILE (@Songuyen < 110) BEGIN Print „So nguyen: ‟ + Convert(char(3), @songuyen) IF @Songuyen = 105 Break SET @Songuyen = @Songuyen +1 END 77 6.Cấu trúc lặp Thực hiện giống ví dụ trước, nhưng muốn in sót số nguyên 105. Chúng ta sử dụng cấu trúc lặp WHILE như sau: DECLARE @Songuyen int SET @Songuyen = 99 WHILE (@Songuyen < 110) BEGIN SET @Songuyen = @Songuyen + 1 IF @Songuyen = 105 CONTINUE Print ‘Số nguyên: ’ + Convert(char(3), @songuyen) END 78 6.Cấu trúc lặp 10/03/2015 40 7.Kiểu dữ liệu cursor Giới thiệu Các bước sử dụng kiểu dữ liệu cursor Ví dụ 79 7.1.Giới thiệu  CSDL quan hệ thường làm việc trên dữ liệu của nhiều dòng mẩu tin – còn gọi là các bộ mẩu tin. Lệnh SELECT kết quả luôn trả về nhiều mẩu tin hơn là một mẩu tin.  Tuy nhiên trong thực tế, có những trường hợp cần xử lý vấn đề trên một mẩu tin hoặc trên nhiều mẩu tin cùng thời gian với hình thức tính toán khác nhau. Để đáp ứng được yêu cầu này SQL Server tạo ra một kiểu dữ liệu đó chính là kiểu cursor. 80 10/03/2015 41 7.2.Các bước sử dụng kiểu cursor Biến kiểu cursor: định nghĩa biến kiểu cursor bằng lệnh DECLARE. Mở Cursor : sử dụng lệnh OPEN để mở ra cursor đã định nghĩa trước đó. Đọc và xử lý trên từng dòng dữ liệu bên trong cursor Đóng cursor: bằng lệnh CLOSE và DEALLOCATE. 81 7.2.1. Biến kiểu cursor DECLARE Tên_cursor CURSOR [LOCAL | GLOBAL] [FORWARD_ONLY | SCROLL] [STATIC | DYNAMIC | KEYSET] [READ_ONLY | SCROLL_LOCK] FOR Câu_lệnh SELECT [FOR UPDATE [OF danh_sách_cột_n]]  Trong đó:  Tên cursor: tên của biến kiểu cursor  LOCAL | GLOBAL: phạm vi hoạt động của biến cursor.  FORWARD_ONLY: đọc dữ liệu trong cursor theo chiều đi tới duyệt từ đầu mẫu tin đầu tiên đến mẫu tin cuối cùng. 82 Cú pháp 10/03/2015 42  STATIC: đọc dữ liệu bên trong cursor tĩnh. Khi đó nếu những người dùng khác có thay đổi bên dưới dữ liệu gốc thì các thay đổi đó sẽ không được cập nhật tự động trong dữ liệu của cursor. Bởi vì khi đó dữ liệu trong cursor chính là dữ liệu của bảng tạm đã được hệ thống sao chép và lưu trữ trong CSDL tempdb của hệ thống khi định nghĩa cursor.  DYNAMIC: dùng chỉ định dữ liệu trong cursor là động. Khi đó việc cập nhật dữ liệu trong bảng cơ sở bởi những người dùng khác sẽ được cập nhật tự động trong dữ liệu cursor có kiểu là DYNAMIC. 83 7.2.1. Biến kiểu cursor  KEYSET: hoạt động giống với kiểu DYNAMIC, các thay đổi dữ liệu trên các cột không là khoá chính trong bảng cơ sở bởi những người dùng khác sẽ được cập nhật trong dữ liệu cursor. Tuy nhiên đối với mẩu tin vừa thêm mới hoặc các mẩu tin đã bị huỷ bỏ bởi những người dùng khác sẽ không được hiển thị trong dữ liệu cursor có kiểu là KEYSET.  READ_ONLY: chỉ định dữ liệu trong cursor chỉ đọc nhằm hạn chế việc sửa đổi dữ liệu bên trong cursor. Khi khai báo cursor với kiểu dữ liệu tĩnh (STATIC) thì dữ liệu trong cursor xem như chỉ đọc. 84 7.2.1. Biến kiểu cursor 10/03/2015 43  SCROLL_LOCK: chỉ định hệ thống SQL Server tự động khoá các dòng mẩu tin cần phải thay đổi giá trị hoặc huỷ bỏ bên trong bảng nhằm bảo đảm các hành động cập nhật luôn thành công.  SELECT: dùng để chỉ đến các cột bên trong bảng mà chúng ta cần đọc dữ liệu.  Danh sách các cột cập nhật: chỉ định danh sách tên các cột sẽ được phép thay đổi giá trị trong cursor. 85 7.2.1.Biến kiểu cursor Ví dụ 1: để định nghĩa một biến cursor chứa toàn bộ các dòng dữ liệu bên trong bảng MAT_HANG, các dòng dữ liệu trong cursor cho phép được cập nhật. DECLARE Cur_MAT_HANG CURSOR DYNAMIC FOR SELECT * FROM MATHANG Ví dụ 2: Định nghĩa một biến cursor chứa toàn bộ các dòng dữ liệu bên trong bảng NHACC, các dữ liệu trong cursor chỉ được phép đọc và việc đọc dữ liệu trong cursor chỉ theo một chiều đi tới. DECLARE Cur_NhaCC CURSOR FORWARD_ONLY STATIC READ_ONLY FOR SELECT * FROM NHACC 86 7.2.1. Biến kiểu cursor 10/03/2015 44 7.2.2. Mở Cursor Cú pháp: OPEN Tên_cursor Trong đó: Tên cursor: tên của biến cursor đã được định nghĩa trước đó bằng lệnh DECLARE Ví dụ: Mở các cursor đã định nghĩa ở ví dụ 1 trên. Chúng ta sử dụng lệnh OPEN như sau: OPEN cur_MAT_HANG 87 7.2.3.Đọc và xử lý dữ liệu trong cursor FETCH [Next | Prior | First | Last |Absolute n | Relative n] FROM Tên_cursor [INTO danh_sách_biến]  Trong đó:  Next, Prior, First, Last: dùng để đọc dữ liệu kế tiếp, trước, đầu, sau cùng.  Absolute: dữ liệu chính xác thứ n trong cursor. N>0 chỉ định việc đọc dữ liệu tại dòng thứ n đếm từ dòng đầu tiên, n<0 dùng chỉ định việc đọc dữ liệu tại dòng thứ n được đếm ngược từ dòng cuối trở lên.  Relative: dùng chỉ định việc đọc dữ liệu tại một dòng tương đối so với dòng dữ liệu hiện hành. N là một số nguyên có thể dương có thể âm để chỉ định theo chiều tới hoặc lui so với dòng dữ liệu hiện hành. 88 10/03/2015 45  FETCH: đọc dữ liệu trong cursor được phép di chuyển tới lui, qua lại các dòng mẩu tin bên trong cursor tuỳ thích. 89 7.2.3.Đọc và xử lý dữ liệu trong cursor 7.2.4.Đóng cursor Cú pháp: CLOSE Tên_cursor DEALLOCATE Tên_cursor Trong đó  CLOSE giải phóng các dòng dữ liệu tham chiếu bên trong cursor.  DEALLOCATE giải phóng thật sự biến cursor ra khỏi bộ nhớ 90 10/03/2015 46 7.3.Ví dụ SQL Server cung cấp một biến hệ thống @@FETCH_STATUS dùng để kiểm tra trình trạng đọc dữ liệu thành công hay thất bại. Giá trị trả về 0 khi việc đọc dữ liệu là thành công. Cho lược đồ quan hệ như sau:  MAT_HANG(MaMH, TenMH, DVT, MaNCC)  PNHAP(MaPN, NgayNhap, ThanhTien)  CTPNHAP(MaMH, MaPN, SLNhap, DonGia) 91  Đọc dữ liệu cursor của bảng MAT_HANG chỉ đọc các vật tư là Tivi  -- Khai báo biến cursor DECLARE cur_MatHang CURSOR DYNAMIC FOR SELECT * FROM MAT_HANG WHERE MaMH like „TV%‟ ORDER BY MaMH  -- Mở cursor OPEN cur_MatHang  -- Đọc dữ liệu FETCH NEXT FROM cur_MatHang WHILE @@FETCH_STATUS = 0 BEGIN -- Đọc tiếp dòng kế FETCH NEXT FROM cur_MatHang END  -- Đóng cursor CLOSE cur_MatHang DEALLOCATE cur_MatHang 92 7.3.Ví dụ 10/03/2015 47 Cập nhật dữ liệu cho cột ThanhTien trong bảng PNHAP bằng cách duyệt qua từng phiếu nhập, tính ra trị giá nhập của từng phiếu căn cứ vào số lượng nhập và đơn giá nhập của từng vật tư trong bảng CTPNHAP, sau cùng cập nhật vào cột ThanhTien.  -- Khai báo biến cursor, các biến cục bộ DECLARE @Sopn char(4), @TongTT Money DECLARE cur_Pnhap CURSOR FORWARD_ONLY FOR SELECT SOPN FROM PNHAP  -- Mở cursor OPEN cur_Pnhap 93 7.3.Ví dụ  -- Đọc dữ liệu và cập nhật giá trị FETCH NEXT FROM cur_Pnhap INTO @Sopn WHILE @@FETCH_STATUS = 0 BEGIN SELECT @Tongtt = SUM(SLNhap*dongia) FROM CTPNHAP WHERE MaPN = @SoPN Print „dang cap nhat phieu nhap: ‟ + @SoPN UPDATE PNHAP SET Thanhtien = @TongTT Where Current OF cur_Pnhap// sopn=@SOPN -- dịch con trỏ đến dòng kế tiếp FETCH NEXT FROM cur_Pnhap INTO @Sopn END  -- Đóng cursor CLOSE cur_Pnhap DEALLOCATE cur_Pnhap 94 Dịch chuyển con trỏ cur_Pnhap vào @SoPN 7.3.Ví dụ 10/03/2015 48 DECLARE @Sopn char(4), @TongTT Money DECLARE cur_Pnhap CURSOR FORWARD_ONLY FOR SELECT SOPN FROM PNHAP OPEN cur_Pnhap WHILE 0 = 0 BEGIN FETCH NEXT FROM cur_Pnhap INTO @Sopn IF @@FETCH_STATUS0 BREAK SELECT @Tongtg = SUM(SLNhap*dongia) FROM CTPNHAP WHERE MaPN = @SoPN Print „dang cap nhat phieu nhap: ‟ + @SoPN UPDATE PNHAP SET Thanhtien = @TongTT Where Current OF cur_Pnhap END CLOSE cur_Pnhap DEALLOCATE cur_Pnhap 95 Hoặc 7.3.Ví dụ DECLARE Tên_cursor CURSOR {kiểu đọc | cập nhật dữ liệu} FOR Câu lệnh SELECT --2. Mở cursor OPEN Tên_cursor --3. Đọc dữ liệu và cập nhật giá trị WHILE 0=0 Begin FETCH NEXT FROM [INTO danh_sách_biến] IF @@FETCH_STATUS 0 Break --cập nhật dữ liệu trong cursor End --4. Đóng cursor CLOSE Tên_cursor DEALLOCATE Tên_cursor 96 7.3.Ví dụ 10/03/2015 49 Khi nào cần sử dụng cursor? Khi nào chúng ta cần sử dụng kiểu dữ liệu cursor trong Transaction-SQL để giải quyết các vấn đề:  SQL Server là một hệ quản trị CSDL quan hệ (Relational Database Management System) do đó chúng ta nên chọn giải pháp làm việc trên các bộ mẩu tin.  Khi cần giải quyết vấn đề cập nhật dữ liệu thì luôn ưu tiên chọn các hướng giải quyết trên bộ mẩu tin bởi vì khi đó làm cho các bộ xử lý được nhanh hơn.  Sau cùng là hướng giải quyết theo kiểu cursor là giải pháp sau cùng nhất để chọn lựa khi không còn giải pháp nào tốt hơn 97 Kết luận 98 10/03/2015 50 TRƢỜNG CAO ĐẲNG CÔNG NGHỆ THÔNG TIN THÀNH PHỐ HỒ CHÍ MINH