Hệ quản trị cơ sở dữ liệu - Chương 3: Lập trình cơ sở dữ liệu bằng T - Sql

218 trang vanle 3290

Download

Bạn đang xem 20 trang mẫu của tài liệu "Hệ quản trị cơ sở dữ liệu - Chương 3: Lập trình cơ sở dữ liệu bằng T - Sql", để tải tài liệu gốc về máy bạn click vào nút DOWNLOAD ở trên

Tài liệu đính kèm:

he_quan_tri_co_so_du_lieu_chuong_3_lap_trinh_co_so_du_lieu_b.pdf

Nội dung text: Hệ quản trị cơ sở dữ liệu - Chương 3: Lập trình cơ sở dữ liệu bằng T - Sql

CHƯƠNG 3 LẬP TRÌNH CƠ SỞ DỮ LIỆU BẰNG T-SQL
NỘI DUNG 1. KHAI BÁO VÀ SỬ DỤNG BIẾN 2. CÁC TOÁN TỬ 3. CÁC CẤU TRÚC ĐIỀU KHIỂN 4. CÁC HÀM THÔNG DỤNG 2
NỘI DUNG 5. THỦ TỤC THƯỜNG TRÚ (STORED PROCEDURE) 6. HÀM DO NGƯỜI DÙNG ĐỊNH NGHĨA 7. TRIGGER 8. KIỂU DỮ LIỆU CURSOR 3
1. KHAI BÁO VÀ SỬ DỤNG BIẾN
1. KHAI BÁO VÀ SỬ DỤNG BIẾN . Biến được 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ý. Dữ liệu có thể được truyền đến câu lệnh SQL bằng cách sử dụng tên biến. . Trong T – SQL, biến có thể phân thành 2 loại: • Biến cục bộ • Biến hệ thống 5
1. KHAI BÁO VÀ SỬ DỤNG BIẾN Biến cục bộ . Cũng giống như các ngôn ngữ lập trình, biến cục bộ trong T–SQL được sử dụng trong quá trình viết mã lệnh T–SQL. . Biến cục bộ là biến do người lập trình tạo ra. . Tên biến phải khai báo bắt đầu bằng ký hiệu @ 6
1. KHAI BÁO VÀ SỬ DỤNG BIẾN Khai báo biến cục bộ Cú pháp: DECLARE @Ten_bien Kieu_du_lieu [, ] Ví dụ: Khai báo biến dùng để lưu trữ tên môn học, điểm của sinh viên. DECLARE @Tenmh Varchar(20), @Diem int 7
1. KHAI BÁO VÀ SỬ DỤNG BIẾN Gán giá trị cho biến cục bộ . Dùng lệnh SET hoặc SELECT. Cú pháp: SET @ = | | Hay SELECT @ = | | , @ = | | , 8
1. KHAI BÁO VÀ SỬ DỤNG BIẾN Gán giá trị cho biến cục bộ . Dùng lệnh SET hoặc SELECT. Ví dụ 1: Khai báo biến @ngayxh và gán giá trị ngày hiện tại cho biến vừa tạo DECLARE @ngayxh DATE SET @ngayxh = getdate() 9
1. KHAI BÁO VÀ SỬ DỤNG BIẾN Gán giá trị cho biến cục bộ Ví dụ 2: Gán giá trị cho biến có tên là @Diem_max, dữ liệu gán lấy từ bảng KETQUA DECLARE @Diem_max int SET @Diem_max = ( SELECT MAX(DIEM) FROM KETQUA) Câu truy vấn trả về một giá trị duy nhất (là điểm lớn nhất từ bảng KETQUA) nên phép gán là hợp lệ. 10
1. KHAI BÁO VÀ SỬ DỤNG BIẾN Gán giá trị cho biến cục bộ Ví dụ 3.1: Tính tổng lương của tất cả các nhân viên phòng Nghiên cứu. DECLARE @Tongluong INT SELECT @Tongluong = SUM(luong) FROM nhanvien nv, phongban pb WHERE nv.PHG = pb.MAPHG AND pb.TENPHG = N‘Nghiên cứu’ 11
1. KHAI BÁO VÀ SỬ DỤNG BIẾN Gán giá trị cho biến cục bộ Lưu ý:  Lệnh SET chỉ sử dụng để gán giá trị cho một biến.  Lệnh SELECT có thể sử dụng để gán giá trị cho nhiều biến cùng lúc. 12
1. KHAI BÁO VÀ SỬ DỤNG BIẾN Gán giá trị cho biến cục bộ Lưu ý:  Lệnh SET chỉ sử dụng để gán giá trị cho một biến.  Lệnh SELECT có thể sử dụng để gán giá trị cho nhiều biến cùng lúc. 13
1. KHAI BÁO VÀ SỬ DỤNG BIẾN Khai báo biến cục bộ Lưu ý:  Gán giá trị khởi tạo cho biến lúc khai báo declare @ngay = getdate() date SAI declare @ngay date = getdate() ĐÚNG 14
1. KHAI BÁO VÀ SỬ DỤNG BIẾN Khai báo biến cục bộ Lưu ý:  Khai báo biến cùng kiểu dữ liệu declare @a , @b int SAI declare @a int, @b int ĐÚNG 15
1. KHAI BÁO VÀ SỬ DỤNG BIẾN Khai báo biến cục bộ Lưu ý:  Kiểu dữ liệu text, ntext hoặc image không được chấp nhận khi khai báo biến.  Phạm vi hoạt động của biến chỉ nằm trong một thủ tục hoặc một lô có chứa lệnh khai báo biến. 16
1. KHAI BÁO VÀ SỬ DỤNG BIẾN Xem (in) giá trị hiện hành của biến cục bộ . Dùng lệnh PRINT hoặc SELECT. Cú pháp: PRINT @tên biến| biểu thức chuỗi Hay SELECT @tên biến|số|biểu thức chuỗi 17
1. KHAI BÁO VÀ SỬ DỤNG BIẾN Xem giá trị hiện hành của biến cục bộ Ví dụ 3.1: Tính tổng lương của tất cả các nhân viên phòng Nghiên cứu DECLARE @Tongluong INT SELECT @Tongluong = SUM(luong) FROM nhanvien nv, phongban pb WHERE nv.PHG = pb.MAPHG AND pb.TENPHG = N‘Nghiên cứu’ PRINT ‘Tong luong phong nghien cuu: ’ PRINT @Tongluong 18
1. KHAI BÁO VÀ SỬ DỤNG BIẾN Xem giá trị hiện hành của biến cục bộ Lệnh PRINT được dùng để in ra giá trị của một biến duy nhất hoặc một giá trị cụ thể. Trường hợp muốn in ra nhiều giá trị thì phải dùng nhiều lệnh PRINT tương ứng. Lệnh SELECT có thể in ra giá trị cho nhiều biến cùng lúc. 19
1. KHAI BÁO VÀ SỬ DỤNG BIẾN Xem giá trị hiện hành của biến cục bộ Ví dụ 2: Dùng Print để in DECLARE @MASV CHAR(10), @TUOI INT SET @MASV = ‘300907106’ SET @TUOI = 23 PRINT @MASV PRINT @TUOI 20
1. KHAI BÁO VÀ SỬ DỤNG BIẾN Xem giá trị hiện hành của biến cục bộ Ví dụ 2: Dùng Select để in DECLARE @MASV CHAR(10), @TUOI INT SET @MASV = ‘300907106’ SET @TUOI = 23 SELECT @MASV, @TUOI 21
1. KHAI BÁO VÀ SỬ DỤNG BIẾN In giá trị của biến kết hợp với chuỗi: Nếu biến là kiểu số thì phải chuyển sang kiểu chuỗi bằng cách dùng hàm Convert. Ví dụ 3: DECLARE @MASV CHAR(10), @TUOI INT SET @MASV = ‘300907106’ SET @TUOI = 23 PRINT @MASV + @TUOI SAI 22
1. KHAI BÁO VÀ SỬ DỤNG BIẾN In giá trị của biến kết hợp với chuỗi: Nếu biến là kiểu số thì phải chuyển sang kiểu chuỗi bằng cách dùng hàm Convert. Ví dụ 3: DECLARE @MASV CHAR(10), @TUOI INT SET @MASV = ‘300907106’ SET @TUOI = 23 ĐÚNG PRINT @MASV + CONVERT(CHAR(2), @TUOI) 23
1. KHAI BÁO VÀ SỬ DỤNG BIẾN Phạm vi hoạt động của biến cục bộ . Trong T–SQL phạm vi của biến chỉ thuộc về một thủ tục hoặc một lô (batch). . Các lô được ngăn cách nhau bởi từ khóa GO. 24
1. KHAI BÁO VÀ SỬ DỤNG BIẾN Phạm vi hoạt động của biến cục bộ Ví dụ sau thể hiện 2 lô: Tính tổng lương của tất cả các nhân viên phòng Nghiên cứu DECLARE @Tongluong INT SELECT @Tongluong = SUM(luong) FROM nhanvien nv, phongban pb WHERE nv.PHG = pb.MAPHG AND pb.TENPHG = ‘Nghien cuu’ GO PRINT ‘Tong luong phong nghien cuu: ’ PRINT @Tongluong Lỗi Biến này không còn ý nghĩa GO 25
1. KHAI BÁO VÀ SỬ DỤNG BIẾN Biến hệ thống – Một vài biến thông dụng Kiểu trả Tên biến Ý nghĩa về Phiên bản, ngày sản xuất, HĐH, @@Version Chuỗi CPU cài đặt máy chủ SQL. Tổng số mẫu tin được tác động bởi @@RowCount Số nguyên câu lệnh T-SQL gần nhất. @@ServerName Chuỗi Tên của Server cài đặt SQL Server. Tên của dịch vụ chạy kèm theo SQL @@ServiceName Chuỗi Server 26
1. KHAI BÁO VÀ SỬ DỤNG BIẾN Biến hệ thống – Một vài biến thông dụng Tên biến Kiểu trả về Ý nghĩa Mã lỗi của câu lệnh gần nhất, khi @@Error Số nguyên câu lệnh thực hiện thành công biến này có giá trị 0. Tên ngôn ngữ mà SQL đang @@Language Chuỗi dùng, mặc định là US_English. Trạng thái của việc đọc dữ liệu trong bảng theo từng dòng @@Fetch_Status Số nguyên (Cursor). Khi đọc dữ liệu thành công biến này có giá trị 0. @@Connections Số nguyên Tổng số kết nối vào SQL Server. 27
1. KHAI BÁO VÀ SỬ DỤNG BIẾN Biến hệ thống Đây là biến hệ thống được sử dụng để xem thông tin phiên bản của SQL Server 28
1. KHAI BÁO VÀ SỬ DỤNG BIẾN Biến hệ thống Ví dụ: Câu lệnh SELECT * FROM MONHOC có kết quả là 3 dòng dữ liệu. Dòng lệnh sau để xem giá trị của biến @@Rowcount: PRINT @@Rowcount Kết quả: 3 29
2. CÁC TOÁN TỬ
2. CÁC TOÁN TỬ T–SQL cung cấp cho chúng ta một số toán tử như sau : 1. Toán tử số học: +, -, *, /, % (lấy phần dư) 2. Toán tử nối chuỗi: + 3. Toán tử so sánh: =, >, >=, , != (khác), !> (không lớn hơn), !< (không nhỏ hơn) 4. Toán tử luận lý: And, Or, Not 31
2. CÁC TOÁN TỬ Lưu ý: Đối với Toán tử số học . Các toán hạng sử dụng trong biểu thức phải có một trong các kiểu dữ liệu sau: int, bigint, smallint, tinyint, numeric, decimal, float, real, money và smallmoney. . Toán tử chia lấy phần dư (%) chỉ giới hạn sử dụng các kiểu dữ liệu như: int, bigint, smallint và tinyint. . Thứ tự ưu tiên các toán tử là: *, /, %, +, –. Ví dụ: PRINT 2 + 3 * 4 % 5 4 32
3. CÁC CẤU TRÚC ĐIỀU KHIỀN
3. CÁC CẤU TRÚC ĐIỀU KHIỂN BEGIN END . Một tập lệnh | khối lệnh SQL được thực thi sẽ được đặt trong BEGIN END Cú pháp: BEGIN END 34
3. CÁC CẤU TRÚC ĐIỀU KHIỂN CẤU TRÚC IF ELSE Cú pháp: IF điều kiện 1 lệnh|khối lệnh 1 ELSE IF điều kiện 2 lệnh|khối lệnh 2 ELSE lệnh|khối lệnh n 35
3. CÁC CẤU TRÚC ĐIỀU KHIỂN CẤU TRÚC IF ELSE Lưu ý: . Cú pháp IF được dùng trong 1 lô hay trong 1 thủ tục, hàm hay Trigger. 36
3. CÁC CẤU TRÚC ĐIỀU KHIỂN CẤU TRÚC IF ELSE Ví dụ 3.2: Kiểm tra xem có môn học nào có số tiết lớn hơn 30 hay không. Nếu có thì in ra danh sách những môn học đó gồm (MaMH, TenMH). Nếu không thì in ra thông báo ‘Không có môn học nào có số tiết > 30’. MONHOC (MaMH, TenMH, SoTiet) 37
3. CÁC CẤU TRÚC ĐIỀU KHIỂN CẤU TRÚC IF ELSE Ngoài ra, có thể sử dụng từ khoá EXISTS kết hợp với cấu trúc IF để kiểm tra sự tồn tại các dòng dữ liệu trong bảng một cách hiệu quả hơn. Cú pháp: IF EXISTS (Câu lệnh SELECT) Câu lệnh 1| Khối lệnh 1 [ELSE Câu lệnh 2| Khối lệnh 2] 38
3. CÁC CẤU TRÚC ĐIỀU KHIỂN CẤU TRÚC IF ELSE Lưu ý: . Từ khoá EXISTS: Dùng để kiểm tra sự tồn tại các dòng dữ liệu trong câu lệnh SELECT sau nó. . Kết quả IF trả về: • đúng (TRUE) khi câu lệnh SELECT trả về ít nhất một dòng dữ liệu, • ngược lại trả về sai (FASLE). 39
3. CÁC CẤU TRÚC ĐIỀU KHIỂN CẤU TRÚC IF ELSE Ví dụ 3.2: IFIF( SELECTexists (COUNT(*)select MAMH FROM from MONHOC MONHOC WHERE SOTIET where >SOTIET> 30 )>0 30) BEGIN PRINT ‘Danh sach cac mon hoc co so tiet >30 la:’ SELECT MAMH, TENMH FROM MONHOC WHERE SOTIET > 30 END ELSE PRINT ‘Khong co mon hoc nao co so tiet > 30.’ 40
3. CÁC CẤU TRÚC ĐIỀU KHIỂN CẤU TRÚC IF ELSE Ví dụ 3.3: Kiểm tra và in ra họ tên (HOTEN) những sinh viên có điểm thi lớn hơn 5, nếu không có thì in ra thông báo “Không có sinh viên nào có điểm thi lớn hơn 5”. SINHVIEN (MaSV, HoTen, NgaySinh, Dchi, GioiTinh, MaLop) KETQUA (MaSV, MaMH, LanThi, Diem) 41
3. CÁC CẤU TRÚC ĐIỀU KHIỂN CẤU TRÚC CASE . Trong T–SQL biểu thức CASE vô cùng hữu ích, nó giống như cú pháp SWITCH CASE trong C. . Được dùng để xử lý điều kiện cho cột được chọn trong câu lệnh truy vấn dữ liệu. . Cú pháp của biểu thức CASE có 2 dạng. 42
3. CÁC CẤU TRÚC ĐIỀU KHIỂN CẤU TRÚC CASE Cú pháp 1: CASE biểu thức WHEN gtrị_1 THEN kquả_1 [WHEN gtrị_2 THEN kquả_2 ] [ELSE kquả_n] END 43
3. CÁC CẤU TRÚC ĐIỀU KHIỂN CẤU TRÚC CASE Cú pháp 2: CASE when bt_logic_1 then kquả_1 [when bt_logic_2 then kquả_2 ] [ELSE kquả_n] END 44
3. CÁC CẤU TRÚC ĐIỀU KHIỂN CẤU TRÚC CASE Ví dụ 1: SELECT MASV, DIEM, KETQUA = CASE WHEN DIEM >= 5 THEN 'DAT' ELSE 'CHUA DAT' END FROM SINHVIEN 45
3. CÁC CẤU TRÚC ĐIỀU KHIỂN CẤU TRÚC CASE – Ví dụ 2: SELECT MaSV, TenSV, CASE WHEN dtb = 5.0 AND dtb = 6.5 AND dtb = 8.0 AND dtb = 9.0 AND dtb <= 10 THEN 'Xuat sac' ELSE 'Khong the xep loai' END AS ‘Xep Loai’ FROM SINHVIEN 46
3. CÁC CẤU TRÚC ĐIỀU KHIỂN CẤU TRÚC CASE – Ví dụ 3: UPDATE SINHVIEN SET XepLoai = CASE WHEN dtb = 5.0 AND dtb = 6.5 AND dtb = 8.0 AND dtb = 9.0 AND dtb <= 10 THEN 'Xuat sac' ELSE 'Khong the xep loai' END 47
3. CÁC CẤU TRÚC ĐIỀU KHIỂN CẤU TRÚC LẶP WHILE Cú pháp: WHILE Biểu thức luận lý BEGIN Câu lệnh END 48
3. CÁC CẤU TRÚC ĐIỀU KHIỂN CẤU TRÚC LẶP WHILE Lưu ý: . Cấu trúc lặp được dùng bên trong lô, trong một thủ tục, hàm, trigger. . Break: thoát khỏi vòng lặp . Continue: bỏ qua các lệnh sau nó, trở lại đầu vòng lặp While. 49
3. CÁC CẤU TRÚC ĐIỀU KHIỂN CẤU TRÚC LẶP WHILE Ví dụ: Cho biết kết quả khi chạy đoạn chương trình sau: DECLARE @a int, @b int SET @a = 1 SET @b = @a + 1 WHILE (@a < 10) BEGIN PRINT @a + @b IF(@a + @b = 6) BREAK SET @a = @a + 1 END 50
CÁC HÀM THÔNG DỤNG
4. CÁC HÀM THÔNG DỤNG . Hàm là tập lệnh T–SQL để thực hiện một công việc nào đó. Hàm trong SQL làm việc với dữ liệu, nhóm dữ liệu để trả về một kết quả mong đợi. . T–SQL có các loại hàm sau: • Hàm tập hợp • Các hàm xử lý chuỗi • Các hàm toán học • Các hàm xử lý ngày giờ • Các hàm chuyển đổi kiểu dữ liệu 52
4. CÁC HÀM THÔNG DỤNG HÀM TẬP HỢP Tên hàm Giá trị trả về SUM(col_name) Hàm tính tổng, trả về tổng giá trị của col_name AVG(col_name) Trả tính giá trị trung bình COUNT Đếm số bản ghi trong bảng MAX(col_name) Trả về giá trị lớn nhất MIN(col_name) Trả về giá trị nhỏ nhất 53
4. CÁC HÀM THÔNG DỤNG HÀM XỬ LÝ CHUỖI Tên hàm UPPER (chuỗi dữ liệu) LOWER (chuỗi dữ liệu) LEFT (chuỗi nguồn, số ký tự) RIGHT (chuỗi nguồn, số ký tự) SUBSTRING (chuỗi nguồn, vị trí, số ký tự) 54
4. CÁC HÀM THÔNG DỤNG HÀM XỬ LÝ CHUỖI Tên hàm Giá trị trả về LTRIM(chuỗi dữ liệu) cắt bỏ khoảng trắng ở đầu chuỗi RTRIM(chuỗi dữ liệu) cắt bỏ khoảng trắng ở cuối chuỗi SPACE(N) Trả về là một chuỗi chứa N ký tự trắng REPLICATE(chuỗi lặp,N) Trả về là một chuỗi chứa các ký tự được lặp lại N lần LEN(chuỗi dữ liệu) Trả 55về chiều dài của chuỗi
4. CÁC HÀM THÔNG DỤNG HÀM XỬ LÝ CHUỖI Tên hàm Giá trị trả về REVERSE(chuỗi dữ Đảo ngược một chuỗi cho trước liệu) STUFF(chuỗi Trả về là một chuỗi mới sau khi đã huỷ nguồn, vị trí, chiều bỏ một số ký tự hiện có và thêm vào dài, chuỗi con) một chuỗi con khác tại vị trí vừa huỷ bỏ. PRINT STUFF('1234567',3,4,'ABC') ‘12ABC7’ 56
4. CÁC HÀM THÔNG DỤNG HÀM XỬ LÝ CHUỖI Tên hàm Giá trị trả về REPLACE(chuỗi Cho phép tìm và thay thế (nếu có) một nguồn, chuỗi tìm, chuỗi con trong chuỗi nguồn bằng một chuỗi thay thế) chuỗi khác. PRINT REPLACE('MON HOC THCB', 'THCB', 'CSDL') ‘MON HOC CSDL’ CHAR(số nguyên) Trả về là một ký tự tương ứng trong bảng mã ASCII. ASCII(ký tự) Trả về là một số nguyên tương ứng với ký tự trong57 bảng mã ASCII.
4. CÁC HÀM THÔNG DỤNG HÀM TOÁN HỌC Tên hàm Giá trị trả về ABS(biểu thức số) Lấy trị tuyệt đối của một số PI() Hằng số PI trong toán học POWER(biểu thức Tính luỹ thừa của một số nào đó theo số, số mũ) một số mũ RAND([số nguồn]) Trả về là một số thực ngẫu nhiên. ROUND(biểu thức Làm tròn một số số, Vị trí làm tròn) 58
4. CÁC HÀM THÔNG DỤNG HÀM TOÁN HỌC Tên hàm Giá trị trả về SIGN(biểu thức số) Trả về là một số qui định dấu của một biểu thức số: 1: biểu thức dương, -1: biểu thức âm, 0: biểu thức bằng 0. SQRT(biểu thức số) Tính căn bậc 2 của một số. 59
4. CÁC HÀM THÔNG DỤNG HÀM TOÁN HỌC Tên hàm Giá trị trả về FLOOR(số nguồn) Trả về số nguyên lớn nhất nhỏ hơn hoặc bằng số nguồn. Floor(3.3) 3 CEILING(số nguồn) Trả về số nguyên nhỏ nhất lớn hơn hoặc bằng số nguồn. Ceiling(3.3) 4 60
4. CÁC HÀM THÔNG DỤNG HÀM XỬ LÝ NGÀY GIỜ Các hàm này thường có tham số vào là kiểu dữ liệu ngày giờ và giá trị trả về của chúng có thể là kiểu dữ liệu số, chuỗi hoặc ngày giờ. 61
4. CÁC HÀM THÔNG DỤNG HÀM XỬ LÝ NGÀY GIỜ Bảng mô tả viết tắt của các đơn vị thời gian: Từ viết tắt Ý nghĩa Miền giá trị yy Năm 1900 – 9999 qq Quí 1 – 4 mm Tháng 1 – 12 dd Ngày trong tháng 1 – 31 dy Ngày trong năm 1 – 366 wk Tuần 1 – 53 dw Ngày trong tuần 1 – 7 62
4. CÁC HÀM THÔNG DỤNG HÀM XỬ LÝ NGÀY GIỜ Bảng mô tả viết tắt của các đơn vị thời gian: Từ viết tắt Ý nghĩa Miền giá trị hh Giờ trong ngày 0 – 23 mi Phút trong giờ 0 – 59 ss Giây trong phút 0 – 59 ms Phần trăm mili giây 0 - 999 63
4. CÁC HÀM THÔNG DỤNG HÀM XỬ LÝ NGÀY GIỜ Tên hàm Giá trị trả về DATEADD(đơn Cộng hoặc trừ một ngày chỉ định một đơn vị thời gian, số vị thời gian và trả về một ngày mới theo nguyên, ngày chỉ yêu cầu. định) PRINT DATEADD(dd,14,'03/16/2007') Mar 30 2007 12:00AM PRINT DATEADD(mm,14, '03/16/2007') May 16 2008 12:00AM 64
4. CÁC HÀM THÔNG DỤNG HÀM XỬ LÝ NGÀY GIỜ Tên hàm Giá trị trả về DATEDIFF(đơn Tính khoảng cách (hiệu) của 2 ngày bất vị thời gian, ngày kỳ và trả về là một số nguyên. 1, ngày 2) PRINT DATEDIFF(dd, '12/05/2007' , '02/06/2008') 63. PRINT DATEDIFF(mm, '12/05/2008', '02/06/2008') -10. PRINT DATEDIFF(mm, '12/05/2008', '02/06/2009') 2. 65
4. CÁC HÀM THÔNG DỤNG HÀM XỬ LÝ NGÀY GIỜ Tên hàm Giá trị trả về GETDATE() Lấy ngày giờ hiện tại theo ngày giờ của hệ thống. DATENAME(đơn Trả về một chuỗi thời gian đại diện của vị thời gian, ngày một ngày chỉ định theo một đơn vị thời chỉ định) gian bất kỳ. PRINT DATENAME(dw,'03/13/2013') Wednesday 66
4. CÁC HÀM THÔNG DỤNG HÀM XỬ LÝ NGÀY GIỜ Tên hàm Giá trị trả về DATEPART(đơn vị Trả về một số nguyên thời gian đại thời gian, ngày chỉ diện của một ngày chỉ định theo một định) đơn vị thời gian bất kỳ. PRINT DATEPART(dw,'03/13/2013') 4 DAY(ngày chỉ định) MONTH(ngày chỉ định) YEAR(ngày chỉ định) 67
4. CÁC HÀM THÔNG DỤNG HÀM CHUYỂN ĐỔI KIỂU DỮ LIỆU . Được dùng để chuyển đổi qua lại các kiểu dữ liệu tương thích nhau trong Microsoft SQL Server. . Trong các xử lý, chúng ta thường chuyển đổi các kiểu dữ liệu số hoặc kiểu dữ liệu ngày giờ sang kiểu dữ liệu chuỗi để hiển thị ra màn hình. . Các kiểu dữ liệu image, text, ntext rất hạn chế trong việc chuyển đổi qua lại các kiểu dữ liệu khác. 68
4. CÁC HÀM THÔNG DỤNG HÀM CHUYỂN ĐỔI KIỂU DỮ LIỆU Tên hàm Giá trị trả về CAST (biểu thức AS Chuyển đổi một biểu thức nào đó kiểu dữ liệu) sang một kiểu dữ liệu bất kỳ. PRINT 'Số nguyên tố: ' + CAST(123 AS VARCHAR(3)) Số nguyên tố 123 69
4. CÁC HÀM THÔNG DỤNG HÀM CHUYỂN ĐỔI KIỂU DỮ LIỆU Tên hàm Giá trị trả về CONVERT (kiểu dữ Chuyển đổi một biểu thức nào đó liệu, biểu thức [, định sang một kiểu dữ liệu bất kỳ và có dạng]) thể theo một định dạng nào đó. PRINT CONVERT (VARCHAR(11), GETDATE(), 101) 03/11/2013 70
Định dạng (yy) Định dạng (yyyy) Dạng hiển thị dữ liệu 4. CÁC1 HÀM THÔNG101 DỤNGmm/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 71 hh:mm:ss:mmm 21 hoặc 121 yyyy-mm-dd hh:mi:ss.mmm 20 hoặc 120 yyyy-mm-dd hh:mi:ss
4. CÁC HÀM THÔNG DỤNG HÀM CHUYỂN ĐỔI KIỂU DỮ LIỆU Tên hàm Giá trị trả về STR(số thực, số ký tự Chuyển đổi kiểu dữ liệu số sang [,số lẻ]) kiểu dữ liệu chuỗi. PRINT STR(12.5, 7, 2) ‘_ _ 12.50’ 72
BÀI TẬP 3.1 1. Declare @a int, @b float, @c int 2. Set a = 2 3. Select b = 2.4 4. set c = 2.5 5. print 'a=' + @a + 'b=' + @b + ' c=' + @c 6. If @a > @b 7. select @tmp = @b 8. if @b > @c 9. set @tmp = @c Tìm lỗi 10. if @c > @a 11. set @tmp = @a 12. print 'Số nhỏ nhất là: ' + @tmp 73
BÀI TẬP3.2 Cho CSDL: SinhVien(MaSV, HoTen, NgaySinh) DiemThi(MaSV, MaMH, Diem) Tính điểm trung bình của mỗi sinh viên. Nếu điểm trung bình lớn hơn 5.0 thì “Đậu”, ngược lại “Trượt”. In dưới dạng bảng sau: Ví dụ: MaSV HoTen Điểm TB Kết quả 0912033 Nguyễn Kim Trọng 4.5 Trượt 74
THỦ TỤC THƯỜNG TRÚ (STORE PROCEDURE – SPs)
5. STORED PROCEDURE – SPs Cho CSDL quản lý điểm thi như sau: 76
5. STORED PROCEDURE – SPs Giả sử cần thực hiện một chuỗi các thao tác trên cơ sở dữ liệu: 77
5. STORED PROCEDURE – SPs 78
5. STORED PROCEDURE – SPs Thay vì viết 2 câu lệnh như trên, ta có thể định nghĩa 1 thủ tục với các tham số: @mamh, @tenmh, @sosvht, @malop để nhập dữ liệu cho một môn học bất kỳ do người dùng truyền vào khi gọi thủ tục. 79
5. STORED PROCEDURE – SPs 80
5. STORED PROCEDURE – SPs . Công cụ rất cần thiết cho các hệ quản trị cơ sở dữ liệu . Là “Chương trình con” của SQL SERVER . Chứa các lệnh T-SQL . Có thể gọi thủ tục nội tại ở trong SQL hay trong các ứng dụng được xây dựng bằng VB.NET, C# . Có thể được tạo ra từ công cụ và câu lệnh. . Có thể chứa những câu lệnh thực hiện dữ liệu (DML) hoặc những câu lệnh truy vấn dữ liệu (SELECT). 81
5. STORED PROCEDURE – SPs Đặc tính và những thuận lợi của SPs: . Chấp nhận những tham số vào và trả về những giá trị được chứa trong các tham số ra. . Người dùng có thể chỉ tạo SPs một lần, lưu trữ trong database một lần nhưng trong chương trình có thể gọi nó với số lần bất kỳ. . SPs cho phép thực thi nhanh hơn: một đoạn source code khá lớn thực thi lặp đi lặp lại thì SPs thực hiện sẽ nhanh hơn. . 82
5. STORED PROCEDURE – SPs Có hai loại thủ tục lưu trữ: . Thủ tục hệ thống – System Stored Procedures (thường bắt đầu bằng sp_). . Thủ tục do người sử dụng tự viết gọi là User Stored Procedures. 83
5. STORED PROCEDURE – SPs  Trong SQL Server, thủ tục được lưu trữ trong thư mục như sau: 84
5. STORED PROCEDURE – SPs Một SPs được định nghĩa gồm những thành phần chính sau:  Tên của SPs  Các tham số  Thân của SPs: bao gồm các câu lệnh T-SQL dùng để thực thi SPs 85
5. STORED PROCEDURE – SPs  Tạo SPs bằng SQL Server Management Studio: 86
5. STORED PROCEDURE – SPs 87
5. STORED PROCEDURE – SPs TẠO THỦ TỤC BẰNG T–SQL CREATE PROCEDURE Tên_thủ_tục [(Danh_sách_tham_số)] [WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION] AS [Begin] [Declare biến cục bộ] Các_câu_lệnh_của_thủ_tục [End] 88
5. STORED PROCEDURE – SPs TẠO THỦ TỤC BẰNG T–SQL Trong đó: . Tên_thủ_tục: Là tên thủ tục được tạo mới, phải là duy nhất trong một CSDL và đặt theo quy tắc đặt tên. . Danh_sách_tham_số: Là các tham số dùng để nhận các giá trị bên ngoài đưa vào. Các tham số này được khai báo như sau: @tên_tham_số kiểu_dữ_liệu [độ_dài]. . Số tham số tối đa trong một thủ tục là 255. 89
5. STORED PROCEDURE – SPs TẠO THỦ TỤC BẰNG T–SQL Trong đó : . WITH RECOMPILE: SQL Server sẽ biên dịch lại thủ tục lưu trữ mỗi khi được gọi. . WITH ENCRYPTION: SQL Server sẽ mã hóa thủ tục lưu trữ. Khi thủ tục đã được mã hóa, không thể xem được nội dung của thủ tục. 90
5. STORED PROCEDURE – SPs TẠO THỦ TỤC BẰNG T–SQL Lưu ý: . Trong SQL Server, có thể ghi tắt một số từ khóa mà tên có chiều dài hơn 4 ký tự. Ví dụ: Create Proc . Tên SPs, tên biến trong SQL Server không phân biệt chữ hoa chữ thường. 91
5. STORED PROCEDURE – SPs TẠO THỦ TỤC BẰNG T–SQL Ví dụ 1: CREATE PROCEDURE XINCHAO AS Print N‘Xin chao ban den voi Stored Procedure’ 92
5. STORED PROCEDURE – SPs TẠO THỦ TỤC BẰNG T–SQL Ví dụ 2: Tạo một thủ tục in ra danh sách những môn học (MAMH, TENMH) có số tiết nhiều hơn 45. 93
5. STORED PROCEDURE – SPs TẠO THỦ TỤC BẰNG T–SQL Lưu ý: . CREATE PROCEDURE không chứa những câu lệnh sau: CREATE VIEW, CREATE TRIGGER, CREATE DEFAULT, CREATE PROCEDURE, CREATE RULE. . Số biến hệ thống và số biến cục bộ chỉ bị giới hạn bởi khả năng bộ nhớ. 94
5. STORED PROCEDURE – SPs TẠO THỦ TỤC BẰNG T–SQL Lưu ý: . Có thể có 2100 biến trong Stored Procedure. . Kích thước tối đa cho Stored Procedure là 128 MB. 95
5. STORED PROCEDURE – SPs BIÊN DỊCH VÀ GỌI THỰC THI THỦ TỤC Biên dịch: Chọn toàn bộ mã lệnh tạo Stored Procedure Nhấn F5 Thực thi thủ tục (gọi thủ tục): Exec[ute] tên_thủ_tục Ví dụ: Exec ds_mh Gọi thực thi thủ tục tên ds_mh 96
5. STORED PROCEDURE – SPs HỦY BỎ THỦ TỤC Cú pháp: Drop Proc[edure] tên thủ tục Ví dụ: Drop Proc ds_mh Hủy thủ tục tên ds_mh 97
5. STORED PROCEDURE – SPs THAY ĐỔI NỘI DUNG THỦ TỤC Cú pháp: Alter Proc[edure] tên thủ tục As [Begin] [Declare biến cục bộ] Các lệnh [End] 98
5. STORED PROCEDURE – SPs THAM SỐ TRONG THỦ TỤC . Một SPs có thể không có, có một hay nhiều tham số. . Các tham số chỉ có nghĩa cục bộ trong SPs. . Tên tham số duy nhất, nên đặt gợi nhớ. . Một SPs cho phép tối đa 1024 tham số. 99
5. STORED PROCEDURE – SPs THAM SỐ TRONG THỦ TỤC Có hai loại tham số: . Tham số đầu vào . Tham số đầu ra 100
5. STORED PROCEDURE – SPs THAM SỐ TRONG THỦ TỤC Tham số đầu vào: . Là tham số được dùng để nhận giá trị từ người dùng truyền vào cho thủ tục. 101
5. STORED PROCEDURE – SPs THAM SỐ TRONG THỦ TỤC - Tham số đầu vào: Cú pháp: Create Procedure tên thủ tục @tên tham số đầu vào kdliệu [độ dài][, ] As [Declare biến cục bộ] Các lệnh 102
5. STORED PROCEDURE – SPs THAM SỐ TRONG THỦ TỤC - Tham số đầu vào: Ví dụ 3.4: Tạo thủ tục TIMSV in ra thông tin của sinh viên có MaSV do người dùng truyền vào. CREATE PROC TIMSV @MASV CHAR(10) AS SELECT * FROM SINHVIEN WHERE MASV = @MASV 103
5. STORED PROCEDURE – SPs THAM SỐ TRONG THỦ TỤC - Tham số đầu vào: Ví dụ 3.4: Tạo thủ tục TIMSV in ra thông tin của sinh viên có MaSV do người dùng truyền vào Gọi thực hiện thủ tục: Exec TIMSV ‘SV01’ 104
5. STORED PROCEDURE – SPs THAM SỐ TRONG THỦ TỤC - Tham số đầu vào: Ví dụ 3.5: Tạo thủ tục INBANGDIEM in ra bảng điểm (MAMH, TENMH, DIEM) của sinh viên có MaSV do người dùng truyền vào. 105
5. STORED PROCEDURE – SPs THAM SỐ TRONG THỦ TỤC - Tham số đầu vào: Ví dụ 3.6: Tạo thủ tục THEMMONHOC để thêm mới một môn học. 106
5. STORED PROCEDURE – SPs THAM SỐ TRONG THỦ TỤC Tham số đầu ra: . Nhận kết quả trả về từ thủ tục (giữ lại giá trị của đối số sau khi kết thúc thủ tục) và hiển thị cho người dùng. . Được sử dụng kết hợp với từ khóa OUTPUT hay OUT. 107
5. STORED PROCEDURE – SPs THAM SỐ TRONG THỦ TỤC - Tham số đầu ra: Cú pháp: Create Procedure tên thủ tục @tên tham số kdliệu OUTPUT [ ] As [Declare biến cục bộ] Các lệnh 108
5. STORED PROCEDURE – SPs THAM SỐ TRONG THỦ TỤC - Tham số đầu ra: Lưu ý: . Khi gọi thủ tục có chứa tham số đầu ra phải chỉ tường minh tham số đầu ra với từ khóa OUTPUT hay OUT. 109
5. STORED PROCEDURE – SPs THAM SỐ TRONG THỦ TỤC - Tham số đầu ra: Ví dụ 3.7: Tạo thủ tục trả về điểm một môn học của một sinh viên. . Tham số đầu vào: Mã sinh viên, Mã môn học. . Tham số đầu ra: Điểm của môn học. 110
5. STORED PROCEDURE – SPs THAM SỐ TRONG THỦ TỤC - Tham số đầu ra: Ví dụ 3.7: Tạo thủ tục cho biết điểm một môn học của một sinh viên. CREATE PROC XEMDIEM @MASV CHAR(10), @MAMH CHAR(10), @DIEM INT OUTPUT AS SET @DIEM = (SELECT DIEM FROM KETQUA WHERE MASV = @MASV AND MAMH = @MAMH) 111
5. STORED PROCEDURE – SPs THAM SỐ TRONG THỦ TỤC - Tham số đầu ra: Ví dụ 3.7: Tạo thủ tục cho biết điểm một môn học của một sinh viên. CREATE PROC XEMDIEM @MASV CHAR(10), @MAMH CHAR(10), @DIEM INT OUTPUT AS SELECT @DIEM = DIEM FROM KETQUA WHERE MASV = @MASV AND MAMH = @MAMH 112
5. STORED PROCEDURE – SPs THAM SỐ TRONG THỦ TỤC - Tham số đầu ra: Ví dụ 3.7: Tạo thủ tục cho biết điểm một môn học của một sinh viên. Gọi thực thi thủ tục DECLARE @DIEM_SV INT EXEC XEMDIEM ‘SV01’, ‘M001’, @DIEM_SV OUTPUT PRINT @DIEM_SV 113
5. STORED PROCEDURE – SPs THAM SỐ TRONG THỦ TỤC - Tham số đầu ra: Ví dụ 3.8: Tạo thủ tục nhập vào MASV, xuất ra điểm TB của sinh viên đó và xếp loại SV theo DTB như sau: - DTB = 8.5 : Giỏi 114
5. STORED PROCEDURE – SPs Ví dụ: (Gọi thủ tục) 115
5. STORED PROCEDURE – SPs MỘT SỐ VẤN ĐỀ KHÁC TRONG SPs . Mã hóa nội dung thủ tục . Biên dịch lại thủ tục . Thủ tục lồng nhau . Lệnh return trong SPs 116
5. STORED PROCEDURE – SPs MÃ HÓA NỘI DUNG TRONG SPs Mục đích: Không cho phép người dùng khác xem mã lệnh trong SPs. Create Procedure tên_thủ_tục @tên_tham_số kdliệu [Output] [ ] With Encryption As [Declare biến_cục_bộ] Các lệnh 117
5. STORED PROCEDURE – SPs BIÊN DỊCH LẠI THỦ TỤC TRONG SPs Mục đích: Mỗi lần có người dùng gọi thủ tục thì bản thân nó sẽ biên dịch lại. Create Proc[edure] tên_thủ_tục @tên_tham_số kdliệu [Output] [ ] With Recompile As Cách 1 [Declare biến_cục_bộ] Các lệnh 118
5. STORED PROCEDURE – SPs BIÊN DỊCH LẠI THỦ TỤC TRONG SPs Mục đích: Mỗi lần có người dùng gọi thủ tục thì bản thân nó sẽ biên dịch lại. EXEC tên_thủ_tục [các tham số] With Recompile Lưu ý: Cách này dùng với việc tạo thủ tục không dùng tùy chọn With Recompile. Cách 2 119
5. STORED PROCEDURE – SPs THỦ TỤC LỒNG NHAU . SQL cho phép các thủ tục lồng vào nhau (gọi lẫn nhau). . SQL cho phép lồng tối đa 32 cấp. 120
5. STORED PROCEDURE – SPs THỦ TỤC LỒNG NHAU 121
5. STORED PROCEDURE – SPs LỆNH RETURN TRONG SPs Cú pháp RETURN [số nguyên] Khi đó cách thức gọi thủ tục EXEC @biến = Tên_thủ_tục[các tham số][ ] Lưu ý: Khi gặp câu lệnh RETURN ngay lập tức SPs sẽ kết thúc. 122
LỆNH RETURN TRONG SPs Ví5 . dụSTORED 3.9: Tao PROCEDURE SPs nhap vao –ten SPs mon hoc, tra ve 1 neu co sinh vien hoc mon hoc do, nguoc lai tra ve 0. Create Proc Ktra_SV_MH @tenmh varchar(30) AS If exists (select sv.MaSV, TenSV from SinhVien sv, KetQua kq, MonHoc mh where sv.MaSV = kq.MaSV And kq.MaMH = mh.MaMH And TenMH = @tenmh) Begin return 1 End Else return 0 123
LỆNH RETURN TRONG SPs Lời5 .gọi STORED thủ tục PROCEDURE – SPs declare @kq int, @tenmh varchar(30) set @tenmh = 'Triet hoc' Exec @kq = Ktra_SV_MH @tenmh if @kq = 0 print 'Khong ton tai sinh vien nao hoc mon '+ @tenmh 124
LỆNH RETURN TRONG SPs Ví5 dụ. STORED 3.10: Tạo thủ PROCEDURE tục trả về tuổi của – SPsmột sinh viên 125
LỆNH RETURN TRONG SPs Ví5 dụ. STORED 3.11: Tạo thủPROCEDURE tục KTTUOI của – SPs một SV, nếu tuổi lớn hơn 30 return 1, ngược lại return 0. 126
5. STORED PROCEDURE – SPs SỬ DỤNG GIÁ TRỊ MẶC ĐỊNH CHO THAM SỐ . Trong lệnh gọi thủ tục có tham số đầu vào, nếu không truyền giá trị cho tham số tương ứng thì hệ thống sẽ báo lỗi. . Đôi khi gọi thủ tục, chúng ta muốn bỏ qua giá trị truyền vào cho tham số. Để thực hiện điều này ta sử dụng giá trị mặc định cho tham số theo cú pháp: @Tên tham số Kiểu dữ liệu = Giá trị mặc định 127
5. STORED PROCEDURE – SPs SỬ DỤNG GIÁ TRỊ MẶC ĐỊNH CHO THAM SỐ CREATE PROC TT_SV @MASV CHAR(10)='SV01' AS SELECT * FROM SINHVIEN WHERE MASV = @MASV GO Khi gọi thực hiện thủ tục mà không cung cấp mã sinh viên thì giá trị mặc định sẽ được sử dụng là ‘SV01’ 128
5. STORED PROCEDURE – SPs SỬ DỤNG GIÁ TRỊ MẶC ĐỊNH CHO THAM SỐ Ví dụ 3.12: Tạo thủ tục truyền vào mã lớp sẽ trả về tổng số sinh viên trong lớp đó. Trường hợp không truyền tham số thì trả về giá trị 0. 129
5. STORED PROCEDURE – SPs SỬ DỤNG GIÁ TRỊ MẶC ĐỊNH CHO THAM SỐ CREATE PROC TONGSV @MaLop char(10)='0' AS IF(@MaLop = '0') RETURN 0 ELSE RETURN (SELECT COUNT(MASV) FROM SINHVIEN WHERE MALOP = @MALOP) 130
5. STORED PROCEDURE – SPs SỬ DỤNG GIÁ TRỊ MẶC ĐỊNH CHO THAM SỐ declare @SL int exec @sl = TONGSV 'L001' print @SL declare @SL int exec @SL = TONGSV print @SL 131
BÀI TẬP3.3 Xét các bảng dữ liệu có cấu trúc như sau: Viết SPs nâng điểm của sinh viên có MASV là [input], TenMH [input], LanThi [input], So_diem_duoc_nang [input] 132
HÀM DO NGƯỜI DÙNG ĐỊNH NGHĨA
6. HÀM DO NGƯỜI DÙNG ĐỊNH NGHĨA . Hàm là đối tượng cơ sở dữ liệu tương tự như thủ tục. . Có thể sử dụng hàm như một thành phần của một biểu thức (VD: Trong danh sách chọn của lệnh SELECT) . Hàm có thể do HQT CSDL cung cấp sẵn và người sử dụng có thể định nghĩa các hàm cá nhân. 134
6. HÀM DO NGƯỜI DÙNG ĐỊNH NGHĨA  Giống Stored Procedure . Là mã lệnh có thể tái sử dụng. . Chấp nhận các tham số input. . Dịch một lần và sau đó có thể gọi khi cần.  Khác Stored Procedure . Chấp nhận nhiều kiểu dữ liệu giá trị trả về thông qua tên hàm. . Không chấp nhận tham số output. . Khác về cách gọi thực hiện. 135
6. HÀM DO NGƯỜI DÙNG ĐỊNH NGHĨA . Hàm gồm 2 loại: o Giá trị trả về là kiểu dữ liệu cơ sở (int, varchar, float, datetime, ) Scalar Value Function. o Giá trị trả về là Table có được từ một câu truy vấn Table value Function. 136
6. HÀM DO NGƯỜI DÙNG ĐỊNH NGHĨA 137
6. HÀM DO NGƯỜI DÙNG ĐỊNH NGHĨA ĐỊNH NGHĨA HÀM (hàm vô hướng – Scalar Value Function – Trả về duy nhất một giá trị) CREATE FUNCTION ten_ham ([ds_tham_so]) RETURNS kieu_du_lieu_tra_ve AS BEGIN cau_lenh END 138
6. HÀM DO NGƯỜI DÙNG ĐỊNH NGHĨA Ví dụ 3.13: Tìm số lớn nhất trong 3 số a, b, c 139
6. HÀM DO NGƯỜI DÙNG ĐỊNH NGHĨA Ví dụ 3.14: Viết hàm nhận vào giá trị một ngày và trả về một chuỗi có giá trị là thứ của ngày đó trong tuần. 140
6. HÀM DO NGƯỜI DÙNG ĐỊNH NGHĨA HÀM với giá trị trả về là dữ liệu kiểu BẢNG Cú pháp 1: CREATE FUNCTION ten_ham([ds_tham_so]) RETURNS TABLE AS RETURN cau_lenh_SELECT 141
6. HÀM DO NGƯỜI DÙNG ĐỊNH NGHĨA HÀM với giá trị trả về là dữ liệu kiểu BẢNG Lưu ý: . Kiểu trả về của hàm được chỉ định bởi mệnh đề RETURNS TABLE. . Trong phần thân của hàm chỉ có duy nhất một câu lệnh RETURN xác định giá trị trả về của hàm thông qua duy nhất một câu lệnh SELECT (không sử dụng bất kỳ câu lệnh nào khác, không đặt trong cặp Begin End). 142
6. HÀM DO NGƯỜI DÙNG ĐỊNH NGHĨA GỌI HÀM với giá trị trả về là dữ liệu kiểu BẢNG SELECT ten_cot FROM dbo.Ten_ham(doi_so) 143
6. HÀM DO NGƯỜI DÙNG ĐỊNH NGHĨA Ví dụ: CREATE FUNCTION NOITUYEN(@TUOI INT) RETURNS TABLE AS RETURN (SELECT * FROM SINHVIEN WHERE TUOI > @TUOI) Gọi hàm SELECT * FROM dbo.NOITUYEN(23) 144
6. HÀM DO NGƯỜI DÙNG ĐỊNH NGHĨA HÀM với giá trị trả về là dữ liệu kiểu BẢNG Khi cần sử dụng nhiều câu lệnh (khác câu lệnh SELECT) trong phần thân hàm, sử dụng cú pháp 2 như sau: 145
6. HÀM DO NGƯỜI DÙNG ĐỊNH NGHĨA Cú pháp 2: CREATE FUNCTION ([ds_tham_so]) RETURNS @Ten_bang_tra_ve TABLE (Ten_cot Kieu_du_lieu[, ]) AS BEGIN Cau_lenh Insert into @Ten_bang_tra_ve RETURN END 146
6. HÀM DO NGƯỜI DÙNG ĐỊNH NGHĨA Lưu ý: . Cấu trúc bảng trả về bởi hàm được xác định dựa vào định nghĩa của bảng trong mệnh đề RETURNS. . Biến @Ten_bang_tra_ve trong mệnh đề RETURNS có phạm vi sử dụng trong hàm và được sử dụng như một tên bảng. . Câu lệnh RETURN trong thân hàm không chỉ định giá trị trả về. Giá trị trả về của hàm chính là các dòng dữ liệu trong bảng có tên là @Ten_bang_tra_ve 147
Ví dụ: CREATE FUNCTION Func_TongSV(@lop SMALLINT) RETURNS @bangthongke TABLE( Malop NVARCHAR(5), Tenlop NVARCHAR(50), Tongsosv INT ) AS BEGIN IF @lop = 0 INSERT INTO @bangthongke SELECT lop.Malop, Tenlop, Count(Masv) FROM Lop, Sinhvien WHERE Sinhvien.Malop = Lop.Malop GROUP BY lop.Malop, Tenlop 148
Ví dụ: CREATE FUNCTION Func_TongSV(@lop SMALLINT) RETURNS @bangthongke TABLE( Malop NVARCHAR(5), Tenlop NVARCHAR(50), Tongsosv INT ) AS BEGIN ELSE INSERT INTO @bangthongke SELECT lop.Malop, Tenlop, Count(Masv) FROM Lop, Sinhvien WHERE Sinhvien.Malop = Lop.Malop and lop.malop = @lop GROUP BY lop.Malop, Tenlop RETURN END 149
Ví dụ: Câu lệnh: SELECT * FROM dbo.func_TongSV(2) Kết quả: thống kê tổng số sinh viên lớp có mã là 2. Còn câu lệnh: SELECT * FROM dbo.func_TongSV(0) Cho biết tổng số sinh viên tất cả các lớp. 150
BÀI TẬP 3.4 Cho CSDL: SinhVien(MaSV, HoTen, NgaySinh) DiemThi(MaSV, MaMH, Diem) 1. Viết hàm trả về điểm trung bình của một sinh viên. 2. Viết hàm trả về mã SV có điểm trung bình cao nhất. 3. Viết hàm xuất danh sách các sinh viên có điểm trung bình bé hơn 5 (Thông tin gồm MASV, HOTEN) 151
TRIGGER
7. TRIGGER . Trigger là một kiểu thủ tục được lưu đặc biệt, chúng được tự động gọi khi sửa đổi dữ liệu mà trigger được thiết kế để bảo vệ. . Trigger được thiết kế giúp đảm bảo sự toàn vẹn dữ liệu bằng các ngăn không cho những thay đổi không nhất quán được thực hiện . Ví dụ: Mỗi sinh viên chỉ được đăng ký không quá 20TC/1 học kỳ. 153
Mục tiêu  Cài đặt các RBTV phức tạp  Các quy tắc nghiệp vụ Đặc điểm  Là một thủ tục đặc biệt  Không có tham số  Thuộc duy nhất một bảng Hoạt động  Được kích hoạt tự động thông qua các thao tác (Insert, Update, Delete, )  Dựa trên bảng tạm: Inserted, Deleted. 154
7. TRIGGER CÁC BẢNG TRUNG GIAN . Inserted o Chứa dữ liệu được thêm mới trong hành động Insert/ Update. o Cấu trúc bảng giống với bảng thực sự được cập nhật dữ liệu . Deleted o Chứa dữ liệu bị xóa trong hành động Delete/Update. o Cấu trúc bảng giống với bảng thực sự được cập nhật dữ liệu. 155
7. TRIGGER CÁC BẢNG TRUNG GIAN . Hành động Update trong SQL Server o Xóa dòng dữ liệu cũ o Thêm dòng dữ liệu mới với thông tin đã cập nhật. 156
7. TRIGGER CƠ CHẾ HOẠT ĐỘNG CỦA TRIGGER . Trigger tương ứng với hành động Insert, phát sinh bảng Inserted. . Trigger tương ứng với hành động Delete, phát sinh bảng Deleted. . Trigger tương ứng với hành động Update, phát sinh bảng Inserted và Deleted. 157
7. TRIGGER Một số gợi ý trước khi tạo trigger . Xác định tên của trigger. . Chỉ định table hoặc view gắn trigger. . Chỉ định biến cố Insert, Update, Delete. . Các câu lệnh tương ứng với nhiệm vụ mà trigger sẽ thực hiện. 158
7. TRIGGER TẠO MỚI Cú pháp: CREATE TRIGGER ten_Trigger ON ten_Bang|ten_View FOR|AFTER|InStead of INSERT [, UPDATE, DELETE] AS [DECLARE bien_cuc_bo] Cau_lenh 159
7. TRIGGER XÓA Cú pháp: DROP TRIGGER ten_Trigger 160
7. TRIGGER Lưu ý: . Các lệnh sau không được dùng trong Trigger: o Alter o Create o Drop o Restore, 161
7. TRIGGER AFTER: . Trigger được thực thi sau khi các thao tác insert/update/delete đã thực hiện thành công. . After trigger là trigger mặc định nếu chỉ có chỉ định FOR. . Không thể định nghĩa AFTER Trigger cho View 162
7. TRIGGER Insert Trigger: Khi một hành động thêm (INSERT) dữ liệu vào bảng xảy ra thì Insert trigger trên bảng này sẽ được kích hoạt. Ví dụ 3.15: Tạo trigger kiểm tra khi nhập dữ liệu vào bảng SACH(MASACH,TENSACH,NAMXB,NHAXB, ) thì NAMXB phải lớn hơn 1990. 163
7. TRIGGER Insert Trigger: CREATE TRIGGER KT_NamXB ON SACH FOR INSERT AS IF(SELECT NamXB FROM Inserted) > 1990 Commit Tran cho phép thêm vào ELSE BEGIN Print ‘Nam XB khong hop le’ Rollback Tran –- không cho thêm vào END 164
7. TRIGGER Delete Trigger: Khi xoá - DELETE dữ liệu trên bảng thì Delete Trigger trên bảng đó sẽ được kích hoạt. Ví dụ 3.16: Giả sử có một bảng dữ liệu có tên là GIAODICH lưu trữ thông tin khách hàng giao dịch với ngân hàng như sau: MAKH TENKH NGAYGD K0034 Tran Thanh 12/02/2013 K0036 Nguyen Thu Trang 15/04/2013 K0015 Le Van Khanh 23/05/2013 165
7. TRIGGER Delete Trigger: Ví dụ 3.16: Viết Trigger kiểm tra chỉ cho xóa những khách hàng có thời gian giao dịch trước tháng 4 năm 2013. 166
7. TRIGGER Update Trigger: Khi sửa đổi (UPDATE) dữ liệu trên một bảng thì Update Trigger trên bảng đó sẽ được kích hoạt. Hành động sửa dữ liệu tương ứng với 2 hành động: xoá dữ liệu cũ sau đó thêm dữ liệu mới. 167
7. TRIGGER Update Trigger: Ví dụ 3.17: Xét bảng HANG như sau MAHG TENHG DONGIA A001 X 50000 A002 Y 100000 A003 Z 60000 Viết trigger chỉ cho phép cập nhật đơn giá mới thêm không quá 110% so với đơn giá cũ. 168
7. TRIGGER Update Trigger: CREATE TRIGGER KT_dongia ON HANG FOR UPDATE AS IF(SELECT DONGIA From Inserted)<=1.1*(SELECT DONGIA FROM Deleted) Commit tran ELSE BEGIN Print ‘Don gia khong hop le’ Rollback tran END 169
7. TRIGGER Trigger nhiều hành động: Một trigger khi được tạo có thể khai báo cho nhiều hành động khác nhau: Insert, Delete, Update thay vì phải viết 3 trigger tương ứng với mỗi hành động. 170
7. TRIGGER Trigger nhiều hành động: CREATE TRIGGER KT_SOTIET ON MONHOC FOR INSERT, UPDATE AS IF(SELECT SOTC FROM INSERTED) < 0 ROLLBACK TRAN GO Trigger này sẽ được kích hoạt với một trong 2 hành động là insert và update 171
7. TRIGGER INSTEAD OF Trigger: Đoạn lệnh trong Instead of trigger được thực thi thay cho thao tác insert/update/delete tương ứng. Có nghĩa là nó được kích hoạt trước khi xảy ra sự thay đổi trong cơ sở dữ liệu. Các ràng buộc không được kiểm tra trước khi trigger kích hoạt. Các bảng tạm inserted và deleted vẫn được tạo ra. Thường được dùng để xử lý cập nhật trên View 172
7. TRIGGER INSTEAD OF trigger: thường dùng cho View với các chức năng: Cập nhật nhiều bảng cùng một lúc trong 1 View Tăng điều kiện ràng buộc trên các thuộc tính so với CHECK 173
7. TRIGGER INSTEAD OF trigger VD1: 174
7. TRIGGER INSTEAD OF trigger VD1: CREATE TRIGGER VD1 ON SINHVIEN INSTEAD OF INSERT AS DECLARE @MALOP CHAR(10) SET @MALOP = (SELECT MALOP FROM INSERTED) IF NOT EXISTS(SELECT * FROM LOP WHERE MALOP = @MALOP) INSERT INTO LOP VALUES(@MALOP,'CHUA BIET’,0) GO INSERT INTO SINHVIEN VALUES ('SV001','A',’TN’,'L101') 175
7. TRIGGER INSTEAD OF trigger VD2: View SV_LOP lấy thông tin từ 2 bảng SINHVIEN và LOP CREATE VIEW SV_LOP AS SELECT MASV, TENSV, S.MALOP, TENLOP FROM SINHVIEN S, LOP L WHERE S.Malop = L.MALOP GO 176
7. TRIGGER INSTEAD OF trigger VD2: Muốn thêm 1 dòng vào view SV_LOP, sử dụng câu lệnh sau: LỖI 177
7. TRIGGER CREATE TRIGGER VD2 ON SV_LOP INSTEAD OF INSERT AS IF NOT EXISTS(SELECT * FROM LOP WHERE MALOP =(SELECT MALOP FROM INSERTED)) AND NOT EXISTS(SELECT * FROM SV WHERE MASV =(SELECT MASV FROM INSERTED)) BEGIN INSERT INTO LOP(MALOP, TENLOP) SELECT MALOP, TENLOP FROM INSERTED INSERT INTO SV(MASV, TENSV, MALOP) SELECT MASV, TENSV, MALOP FROM INSERTED END ELSE PRINT N'SINH VIÊN HOẶC LỚP ĐÃ TỒN TẠI' GO 178
7. TRIGGER INSTEAD OF trigger 179
7. TRIGGER INSTEAD OF trigger 180
7. TRIGGER Sử dụng IF UPDATE trong Trigger CREATE TRIGGER SISO_UPDATE ON SINHVIEN FOR UPDATE AS IF UPDATE(MALOP) BEGIN UPDATE LOP SET SISO = SISO + 1 WHERE MALOP = (SELECT MALOP FROM INSERTED) UPDATE LOP SET SISO = SISO - 1 WHERE MALOP = (SELECT MALOP FROM DELETED) END 181
7. TRIGGER QUẢN LÝ TRIGGER 182
7. TRIGGER XEM NỘI DUNG TRIGGER sp_helptrigger tên_trigger 183
7. TRIGGER BÀI TẬP 3.5: Ràng buộc liên bộ Xét LĐQH KETQUA (MaSV, MaMH, LanThi, Diem) Phát biểu: “Sinh viên chỉ được thi tối đa 2 lần cho một môn học” Bảng tầm ảnh hưởng: 184
7. TRIGGER BÀI TẬP 3.6: Ràng buộc liên thuộc tính – liên quan hệ Cho CSDL DATHANG (MaPDH, NgayDH, ) GIAOHANG (MaPGH, MaPDH, NgayGH, ) Phát biểu: “Ngày giao hàng không thể nhỏ hơn ngày đặt hàng tương ứng” Bảng tầm ảnh hưởng: 185
7. TRIGGER BÀI TẬP 3.6: Ràng buộc liên thuộc tính – liên quan hệ Cho CSDL DATHANG (MaPDH, NgayDH, ) GIAOHANG (MaPGH, MaPDH, NgayGH, ) Phát biểu: “Ngày giao hàng không thể nhỏ hơn ngày đặt hàng tương ứng” Yêu cầu: Cài trigger cho thao tác thêm/sửa trên bảng GIAOHANG. 186
7. TRIGGER BÀI TẬP 3.7: Cho CSDL MATHANG (MAHANG, TENHANG, SOLUONG) NHATKYBANHANG (STT, NGAY, NGUOIMUA, MAHANG, SOLUONG, GIABAN) Yêu cầu: Định nghĩa trigger có chức năng tự động giảm số lượng hàng hiện có khi một mặt hàng nào đó được bán (tức là khi câu lệnh INSERT được thực thi trên bảng NHATKYBANHANG). Gọi thực hiện trigger. 187
8. KIỂU DỮ LIỆU CURSOR KIỂU DỮ LIỆU CURSOR hay còn gọi là Kiểu dữ liệu con trỏ. Tại sao phải sử dụng Cursor? Vì các lệnh trong SQL như: SELECT, UPDATE, DELETE, đều thao tác trên nhiều dòng dữ liệu thỏa mãn điều kiện where cùng một lúc mà không thể thao tác trên từng dòng dữ liệu cụ thể 188
8. KIỂU DỮ LIỆU CURSOR Khái niệm:  Cursor là kiểu dữ liệu cơ bản dùng để duyệt qua từng dòng dữ liệu trả về từ câu truy vấn SELECT, giúp ta có thể có những xử lý khác nhau cho từng dòng dữ liệu cụ thể. . Cursor cho phép cập nhật / xóa dữ liệu (dữ liệu thật sự trong CSDL) tương ứng với vị trí hiện hành của cursor. 189
8. KIỂU DỮ LIỆU CURSOR Đặc điểm:  Cho phép thao tác trên từng dòng lệnh trả về từ câu lệnh SELECT. . Do phải duyệt qua từng dòng dữ liệu nên xử lý hơi chậm. 190
8. KIỂU DỮ LIỆU CURSOR Khi sử dụng cursor trong SQL Server cần lưu ý: • Cursor cần phải khai báo và các thuộc tính của nó cũng cần được xác định. • Khi sử dụng đến Cursor thì phải mở cursor • Đọc và xử lý từng dòng lệnh bên trong cursor. • Dữ liệu trong dòng hiện hành có thể được hiệu chỉnh nếu cần thiết. • Tạm thời không dùng cursor thì phải đóng cursor. • Cursor cần phải được giải phóng khi không cần dùng nữa. 191
8. KIỂU DỮ LIỆU CURSOR Định nghĩa biến kiểu cursor Có thể khai báo theo cú pháp chuẩn hoặc cú pháp mở rộng của T-SQL. 192
8. KIỂU DỮ LIỆU CURSOR Cú pháp chuẩn DECLARE Tên_cursor [Insensitive] [Sroll]CURSOR FOR Câu lệnh SELECT [ FOR {Read Only | UPDATE [OF cột 1 [, ,cột n]]}] Tên_Cursor: Chiều dài 128 kí tự 193
8. KIỂU DỮ LIỆU CURSOR VD: DECLARE my_cur CURSOR FOR SELECT MSSV, TenSV FROM SINHVIEN Hoặc, có thể khai báo biến Cursor rồi sau đó mới gán lệnh SELECT vào Cursor DECLARE @cur CURSOR SET @cur = CURSOR FOR SELECT MSSV, TenSV FROM SINHVIEN 194
8. KIỂU DỮ LIỆU CURSOR Cú pháp mở rộng DECLARE Tên_cursor CURSOR [LOCAL | GLOBAL] [FORWARD_ONLY | SCROLL] [STATIC | DYNAMIC] [READ_ONLY] FOR Câu lệnh SELECT [FOR UPDATE [OF cột 1 [, ,cột n]]}] 195
8. KIỂU DỮ LIỆU CURSOR Câu lệnh Select: Là câu lệnh truy vấn để định nghĩa tập kết quả của cursor. Phạm vi: . LoCal: chỉ sử dụng trong phạm vi khai báo . Global: sử dụng chung cho cả kết nối. Mặc định là Local. 196
8. KIỂU DỮ LIỆU CURSOR Di chuyển: . ForWard_Only: chỉ di chuyển một hướng theo chiều đi tới (mặc định). . Scroll: di chuyển tùy ý (tiến lùi giữa các dòng trong Cursor) Xử lý: . Read_Only: chỉ đọc, không thể sử dụng cursor để cập nhật dữ liệu trong các bảng liên quan. 197
8. KIỂU DỮ LIỆU CURSOR Trạng thái: . Insensitive| Static (Cursor tĩnh): dữ liệu trên Cursor không thay đổi mặc dù dữ liệu trong bảng nguồn thay đổi. . Dynamic (Cursor động): dữ liệu trên Cursor sẽ thay đổi khi dữ liệu trong bảng nguồn thay đổi (mặc định). Khi khai báo cursor với thuộc tính tĩnh (STATIC) thì dữ liệu trong cursor xem như là chỉ đọc. 198
8. KIỂU DỮ LIỆU CURSOR . UPDATE [OF cột 1 [, n]: Quy định cột cho phép được update khi dùng cursor. Nếu OF danh sách cột [, n] được chỉ định rõ ràng thì chỉ có các cột được chỉ định mới được cho phép hiệu chỉnh, nếu không có thì tất cả các cột có thể update. 199
8. KIỂU DỮ LIỆU CURSOR Ví dụ: DECLARE my_cur CURSOR GLOBAL SCROLL DYNAMIC FOR SELECT MSSV, TenSV FROM SINHVIEN 200
8. KIỂU DỮ LIỆU CURSOR Mở cursor: Cursor được mở sau khai báo để sử dụng OPEN Tên_cursor|@Biến_cursor 201
8. KIỂU DỮ LIỆU CURSOR Sử dụng cursor: Dùng lệnh Fetch để duyệt tuần tự qua cursor FETCH NEXT | PRIOR | FIRST | LAST | ABSOLUTE n | REALTIVE n ] FROM Tên_cursor|@Biến_cursor [INTO @biến [, n]] 202
8. KIỂU DỮ LIỆU CURSOR Hướng di chuyển: . NEXT: Di chuyển về sau . PRIOR: Di chuyển về trước . FIRST: Di chuyển về đầu . LAST: Di chuyển về cuối . ABSOLUTE n: di chuyển đến mẫu tin thứ n tính từ mẫu tin đầu tiên (Nếu n < 0: tính từ mẫu tin cuối) . RELATIVE n: giống ABSOLUTE nhưng tính từ mẫu tin hiện hành. 203
8. KIỂU DỮ LIỆU CURSOR FETCH FETCH Cursor FETCH RELATIVE 3 FETCH NEXT ABSOLUTE 4 LAST 1 1 1 1 1 2 2 2 2 2 3 3 3 3 3 4 4 4 4 4 5 5 5 5 5 6 6 6 6 6 204
8. KIỂU DỮ LIỆU CURSOR  Mặc định: Fetch Next  Đối với cursor dạng forward_only, chỉ có thể fetch next.  Biến hệ thống @@Fetch_status cho biết lệnh Fetch vừa thực hiện có thành công hay không. 205
8. KIỂU DỮ LIỆU CURSOR Truy xuất từng dòng dữ liệu. Kiểm tra phạm vi con trỏ bằng @@Fetch_status, có giá trị trả về như sau: . 0: Đọc dữ liệu thành công. . -1: Đọc dữ liệu thất bại hoặc dòng vượt quá kết quả gán . -2: Đọc dữ liệu thất bại (dòng dữ liệu truy cập không tồn tại) 206
8. KIỂU DỮ LIỆU CURSOR 207
8. KIỂU DỮ LIỆU CURSOR  Đóng Cursor: Close Tên_cursor|@Biến_cursor Kết thúc hành động của Cursor cho 1 lần mở. Cursor vẫn hiện hữu cho đến khi gặp một lệnh Open khác.  Giải phóng Cursor khỏi bộ nhớ Deallocate cursor_name Giải phóng Cursor ra khỏi bộ nhớ. Sau lệnh này nếu có dòng lệnh nào truy cập đến Tên Cursor đều gây lỗi. 208
8. KIỂU DỮ LIỆU CURSOR Thứ tự các thao tác khi xử lý dữ liệu trên CurSor 1. Định nghĩa biến Cursor 2. Mở Cursor bằng lệnh Open 3. Duyệt và xử lý dữ liệu trên Cursor Fetch (Next, ) . Dùng lệnh Into để đưa giá trị của cursor vào biến . Nếu không có lệnh Into, giá trị của cursor sẽ hiển thị ra màn hình kết quả sau lệnh Fetch. . Có thể sử dụng vị trí hiện tại như là điều kiện cho mệnh đề Where của câu Delete/ Update (nếu cursor không là Read_only) 209
8. KIỂU DỮ LIỆU CURSOR Thứ tự các thao tác khi xử lý dữ liệu trên CurSor 4. Lặp lại việc duyệt và sử dụng cursor, có thể sử dụng biến @@fetch_status để biết đã duyệt qua hết cursor hay chưa. 5. Đóng CurSor bằng lệnh Close Tên_cursor 6. Giải phóng Cursor Deallocate Tên_cursor 210
8. KIỂU DỮ LIỆU CURSOR Ví dụ 1: SINHVIEN (MaSV, HoTen, MaKhoa)  Định nghĩa biến kiểu cursor có tên là cs_sinhvien gồm 2 cột dữ liệu trong bảng sinh viên là MASV, HOTEN.  Mở và xử lý in các dòng dữ liệu. 211
8. KIỂU DỮ LIỆU CURSOR SINHVIEN (MaSV, HoTen, MaKhoa) B1: Định nghĩa một Cursor cs_sinhvien lấy giá trị của hai thuộc tính MASV, HOTEN DECLARE cs_sinhvien CURSOR FOR SELECT MASV, HOTEN FROM SINHVIEN 212
8. KIỂU DỮ LIỆU CURSOR B2: Mở cursor OPEN cs_sinhvien 213
8. KIỂU DỮ LIỆU CURSOR B3: Xử lý dữ liệu trong cursor FETCH NEXT FROM cs_sinhvien WHILE (@@FETCH_STATUS = 0) BEGIN Đọc tiếp các dòng dữ liệu nếu thành công FETCH NEXT FROM cs_sinhvien END 214
8. KIỂU DỮ LIỆU CURSOR B4: Đóng cursor CLOSE MY_CUR B5: Hủy cursor DEALLOCATE MY_CUR 215
8. KIỂU DỮ LIỆU CURSOR Ví dụ 2: SINHVIEN (MaSV, HoTen, MaKhoa) KHOA (MaKhoa, TenKhoa)  Duyệt và đọc giá trị từ cursor  Cập nhật lại giá trị MaSV = MaKhoa + MaSV hiện tại  Áp dụng cho tất cả sinh viên 216
Kết hợp Cursor và Thủ tục/ Trigger Để dễ dàng sử dụng và quản lý cursor, cài đặt cursor lồng bên trong thủ tục/trigger. Như vậy, mỗi lần gọi thủ tục/trigger thì cursor sẽ được mở hoặc tạo mới, khi kết thúc thủ tục/trigger thì cursor sẽ được đóng lại hoặc huỷ đi. Tham khảo bài giảng/ trang 113. 217