Bài giảng Hệ quản trị cơ sở dữ liệu - Chương 3: Truy vấn nâng cao - Hồ Lê Thị Kim Nhung

Nội dung text: Bài giảng Hệ quản trị cơ sở dữ liệu - Chương 3: Truy vấn nâng cao - Hồ Lê Thị Kim Nhung

1. Nội dung 1 Cấu trúc lệnh 2 Thủ tục thường trú 3 Kiểu dữ liệu cursor 4 Hàm người dùng www.themegallery.com
2. Khai báo biến ❖Cú pháp ▪ Declare Var_name Datatype ▪ Lưu ý: Tên biến phải bắt đầu bằng 1 ký tự @ ❖Ví dụ ▪ Declare @MaSinhVien nvarchar(10) ▪ Declare @TienLuong float ▪ Declare @Sum float, @Count int ▪ Declare @temp TABLE (ma int, ten nvarchar(10)) www.themegallery.com
3. Lệnh gán Set @TenBien = GiaTri Set @TenBien = TenBien Set @TenBien = BieuThuc Select @TenBien = (KetQuaTruyVan) ❖Ví dụ : Set @MaLop = ‘TH2001’ Set @SoSV = (select count(*) from SinhVien) Set @MaLop = ‘TH’ + CAST (Year(@NgayTuyenSinh) AS char(4)) www.themegallery.com
4. Lệnh gán ❖Cũng có thể gán giá trị cho biến bằng câu truy vấn thay vì chỉ thị set ❖Ví dụ : NhanVien(MaNV, HoTen, NgaySinh) Declare @Var1 datetime Select @Var1 = NgaySinh from NhanVien where MaNV = 1 If (year(getdate()) – year(@Var1) > 50) www.themegallery.com
5. Cấu trúc điều khiển If logial expression [Begin] Code block [End] [Else if logial expression [Begin] Có thể lặp lại nhiều Code block lần tùy ý. Mô phỏng [End] cấu trúc case [, n]] Else [Begin] Code block [End] www.themegallery.com
6. Cấu trúc điều khiển • Tính tổng các số nguyên chẵn từ 1 đến n • Đếm và in ra số lượng các số nguyên chia hết cho 3 nằm trong đoạn từ 1 đến n • Cho 3 số a, b, c. Tìm số lớn nhất. In giá trị của a, b, c. Xuất thông báo "Số lớn nhất là :" www.themegallery.com
7. Cấu trúc điều khiển ❖Ví dụ NhanVien (MaNV: int, HoTen: nvarchar(30)) Viết lệnh xác định một ma nhan vien mới theo qui định: mã nhan vien tăng dần, nếu có chỗ trống thì mã mới xác định sẽ chèn vào chỗ trống đó Vd: 1,2,3,7 → mã nhan vien mới: 4 www.themegallery.com
8. Cấu trúc điều khiển CASE [input_expression] WHEN when_expression THEN result_expression [ n ] Có thể là giá [ ELSE else_result_expression ] trị hoặc biểu thức điều kiện END www.themegallery.com
9. Cấu trúc điều khiển Select * From NHAN_VIEN Where datediff(yy, NgaySinh, getdate()) > = Case Phai when ‘Nam’ then 60 when ‘Nu’ then 55 End www.themegallery.com
10. Select MaNV, HoTen, ‘Loai’ = Case when CapBac<=3 then ‘Binh Thuong’ when CapBac is null then ‘Chua xep loai’ else ‘Cap Cao’ End From NhanVien www.themegallery.com
11. Bài tập 2 Cho CSDL: SinhVien(MaSV, HoTen, NgaySinh) Tìm sinh viên có MaSV = ‘0912033’ với định dạng như sau: Mã SV : 0912033 Họ tên : Nguyễn Kim Ái Ngày sinh : 20/9/1990 www.themegallery.com
12. Bài tập 4 Cho CSDL: SinhVien(MaSV, HoTen, NgaySinh) DiemThi(MaSV, MaMH, Diem) Kiểm tra MaSV = 0912003 có tồn tại chưa ▪ Nếu chưa tồn tại xuất thông báo [MaSV] chưa tồn tại. ▪ Ngược lại, xuất thông báo [MaSV] sinh viên đã tồn tại. www.themegallery.com
13. Nội dung 1 Cấu trúc lệnh 2 Thủ tục thường trú 3 Kiểu dữ liệu cursor 4 Hàm người dùng www.themegallery.com
14. Thủ tục thường trú Tên thủ tục? Tham số vào? Tham số ra? Giá trị trả về? Yêu cầu xử lí? www.themegallery.com
15. Cú pháp Create {proc | procedure} proc_name Tên của stored Parameter DataType [output] [, n] .Nên bắt đầu với USP As Kiểu DL của Code block tham số Giá trị trả ra nếu có thì dùng một (hay [return [return_value] ] một số) tham số output Go Tên tham số (đặt Thân sủa SP, như tên biến) viết như thế nào là tùy vào từng bài toán cụ thể Chỉ trả về giá trị int www.themegallery.com
16. Ví dụ Viết thủ tục thêm một đăng ký của sinh viên vào một học phần 1. Khai báo đối số Create procedure usp_ThemDangKy @MaSV char(5), @MaHP char(5), @SiSo int = null output As www.themegallery.com
17. Stored-Procedure Stored- procedure Phân tích cú pháp Kiểm tra Kiểm tra Thành tồn tại? tồn tại? công! Thành công! Báo lỗi! Biên dịch khi thực thi www.themegallery.com
18. Scalar input parameters ▪ Named CREATE PROC USP_XemSV @MaSV Char(10) AS BEGIN IF @MaSV is NULL SELECT * FROM SINHVIEN ELSE SELECT * EXEC USP_XemSV ‘0912311’ FROM SINHVIEN WHERE MaSV = @MaSV END www.themegallery.com
19. Table-valued input parameters CREATE PROC USP_THEMHOADON @TEMP AS DSCTDONHANG READONLY, @MADONHANG CHAR(10), @MAKHACHHANG CHAR(10) AS BEGIN Thêm phiếu đặt hàng INSERT PHIEUDATHANG (MADATHANG,NGAYDAT,MAKHACHHANG) VALUES(@MADONHANG, GETDATE(), @MAKHACHHANG) Thêm chi tiết phiếu đặt hàng INSERT CHITIETPHIEUDAT (MACHITIETPD,MASANPHAM,SOLUONG,MADATHANG) SELECT *, @MADONHANG FROM @TEMP END www.themegallery.com
20. Scalar output parameters Thống kê doanh thu của mỗi sản phẩm CREATE PROC USP_ThongKe @MaSP Char(10), @TongSLBan int output, @TongDoanhThu float output AS www.themegallery.com
21. Scalar output parameters Gọi thực thi DECLARE @TongSL int, @TongDT float EXEC USP_ThongKe 'SP00000001', @TongSL output, @TongDT output PRINT CAST(@TongSL AS Char(3)) + Char(13) PRINT @TongDT www.themegallery.com
22. Ví dụ 1. Truyền trị Exec usp_ThemDangKy ‘001’, ’HP01’ 2. Truyền trị có tên biến Exec usp_ThemDangKy @MaHP = ‘HP01’, @MaSV = ‘001’ 3. Truyền trị có tên biến Exec usp_ThemDangKy @MaHP, @MaSV 4. Có output Declare @SiSo int Exec usp_ThemDangKy ‘001’,’HP01’, @SiSo output 5. Nhận lại giá trị từ hàm Declare @SiSo int, @KetQua int Exec @KetQua = usp_ThemDangKy ‘001’,’HP01’, @SiSo output www.themegallery.com
23. Thủ tục thường trú Thư mục chứa thủ tục www.themegallery.com
24. Nội dung 1 Cấu trúc lệnh 2 Thủ tục thường trú 3 Kiểu dữ liệu cursor 4 Hàm người dùng www.themegallery.com
25. Cursor – Khái niệm MaSV Hoten NgaySinh DECLARE @n int = (select COUNT(*) from SinhVien) SV001 Nguyễn Minh Thu 20/1/1990 DECLARE @i int = 0 SV002 Nguyễn Thị Thạch 2/3/1991 WHILE @i < @n BEGIN SV003 Trần Minh Trang 4/3/1990 DECLARE @ma varchar(20), @hoten nvarchar(30) SELECT @ma=ma, @hoten=hoten FROM (SELECT ROW_NUMBER() OVER (ORDER BY ma ASC) AS rownumber, ma, hoten FROM SinhVien) tmp tempCol MaSV Hoten NgaySinh WHERE rownumber = @i PRINT @ma + @hoten 1 SV001 Nguyễn Minh Thu 20/1/1990 Làm gì đó 2 SV002 Nguyễn Thị Thạch 2/3/1991 SET @i = @i + 1 END 3 SV003 Trần Minh Trang 4/3/1990 WHILE CURSOR www.themegallery.com
26. Cursor – khái niệm ❖Vị trí hiện hành của cursor có thể được dùng như điều kiện trong mệnh đề where của lệnh update hoặc delete ▪ Cho phép cập nhật / xoá 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 www.themegallery.com
27. Cursor – Khai báo ▪ Cú pháp mở rộng Declare cursor_name Cursor [ Local | Global ] [ Forward_only| Scroll] [ Static| Dynamic] [ Read_only] For select_statement [ For Update [ of column_name [, n] ] ] www.themegallery.com
28. Cursor – Khai báo ✓Biến cursor – cursor được khai báo như một biến kiểu CURSOR, khi gán giá trị cho biến cursor thông qua lệnh SET thì biến này sẽ trỏ tới đối tượng cursor. VD: DECLARE @cur CURSOR SET @cur = CURSOR FOR SELECT MSSV, TenSV FROM SINHVIEN HOẶC DECLARE @cur CURSOR SET @cur = my_cur www.themegallery.com
29. Cursor – Khai báo ▪ Local: cursor cục bộ, chỉ có thể sử dụng trong phạm vi một khối (query batch) hoặc một thủ tục/ hàm ▪ Global: cursor toàn cục (tồn tại trong suốt connection hoặc đến khi bị hủy tường minh) www.themegallery.com
30. Cursor – Khai báo ❖Mặc định: ▪ Global ▪ Forward_only ▪ For update ▪ Dynamic www.themegallery.com
31. Cursor - Duyệt 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 www.themegallery.com
32. Trình tự sử dụng ❖Khai báo cursor ❖“Mở” cursor bằng lệnh Open Open tên_cursor ❖ Fetch (next, ) cursor để chuyển đến vị trí phù hợp ▪ 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) www.themegallery.com
33. Ví dụ SINHVIEN (MaSV, HoTen, MaKhoa) KHOA (MaKhoa, TenKhoa) Ví dụ 1: 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 www.themegallery.com
34. 4. Fetch lần 2 n While @@fetch_status = 0 Begin update SinhVien set MaSV = MaKhoa +MaSV Where MaKhoa = @MaKhoa Fetch Next From cur_DSKhoa into @MaKhoa End www.themegallery.com
35. Ví dụ 2 Dùng cursor để cập nhật dòng xác định Declare cur_DSKhoa cursor scroll For select MaKhoa, TenKhoa From Khoa Open cur_DSKhoa Fetch Absolute 2 From cur_DSKhoa If (@@fetch_status = 0) Update Khoa Set TenKhoa = ‘aaa’ Where current of cur_DSKhoa Close cur_DSKhoa Deallocate cur_DSKhoa www.themegallery.com
36. Hàm người dùng ❖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à từ đó có thể gọi khi cần ❖Khác stored procedure ▪ Chấp nhận nhiều kiểu giá trị trả về (chỉ một giá trị trả về) ▪ Không chấp nhận tham số output ▪ Khác về cách gọi thực hiện www.themegallery.com
37. Hàm người dùng ❖Loại 1: Giá trị trả về là kiểu dữ liệu cơ sở Create function func_name ( {parameter_name DataType [= default ] } [, n]) Dù không có tham số cũng phải Returns DataType ghi cặp ngoặc rỗng As Dù thân function chỉ có 1 Begin lệnh cũng phải đặt giữa Begin và End Return {value | variable | expression} End www.themegallery.com
38. Hàm người dùng ❖Loại 2: Giá trị trả về là Table có được từ một câu truy vấn Create function func_name ( {parameter_name DataType [= default ] } [, n]) Thân function luôn Returns Table chỉ có một lệnh, không đặt trong cặp As Begin -End Return [ ( ]select_statement [ ) ] Go www.themegallery.com
39. Ví dụ Create function uf_DanhSachLop Returns @DS Table(MaLop varchar(10),SoSV int) As Declare cur_L cursor for Select Ma From Lop Declare @Ma varchar(10) Open cur_L Fetch next from cur_L into @Ma While @@fetch_status=0 Begin . Insert into @DS End Values (@Ma, (select count(*) from Close cur_L SinhVien where Lop=@Ma)) Deallcocate cur_L Fetch next from cur_L into @Ma Return Go www.themegallery.com
40. Hàm người dùng ❖Lưu ý: khi gọi hàm loại 1 (trả về giá trị cơ bản), phải có tên owner của hàm đi kèm Ví dụ dbo.uf_SoLonNhat() www.themegallery.com
41. Hàm người dùng ❖Ngoài các hàm do người dùng định nghĩa, SQL Server còn cung cấp các hàm xây dựng sẵn của hệ thống ❖Các hàm này cung cấp tiện ích như xử lý chuỗi, xử lý thời gian, xử lý số học ❖Sinh viên tìm hiểu thêm về các hàm này trong Books on-line và các tài liệu tham khảo www.themegallery.com
42. Bài tập 1. Viết hàm tính điểm trung bình của sinh viên. 2. Viết hàm tìm mã sinh viên 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 < 5. 4. Viết thủ tục xếp loại cho sinh viên (gọi hàm câu 1). www.themegallery.com