Bài giảng Hệ quản trị cơ sở dữ liệu - Chương 3: Truy vấn và nâng cao - Tuấn Nguyên Hoài Đức

Nội dung text: Bài giảng Hệ quản trị cơ sở dữ liệu - Chương 3: Truy vấn và nâng cao - Tuấn Nguyên Hoài Đức

1. Nội dung trình bày • Khai báo biến và gán biến • Cấu trúc điều khiển • Thủ tục thường trú • Kiểu dữ liệu cursor • Hàm người dùng Chương III : Truy vấn nâng cao 2
2. Khai báo biến • Tầm vực biến • Biến cục bộ có ý nghĩa trong một querry batch hay một thủ tục thường trú hoặc một hàm người dùng • Biến hệ thống có ý nghĩa trên cả hệ thống. Tên của chúng bắt đầu bằng @@. Các biến này là read-only. • Ví dụ biến hệ thống : @@fetch_status, @@rowcount, @@trancount Chương III : Truy vấn nâng cao 4
3. 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 Kiểu dữ liệu phải tương ứng. Nếu câu truy vấn trả về nhiều • Ví dụ 1 : dòng thì các biến chỉ nhận giá • SV(MaSV, HoTen, Tuoi) trị từ dòng đầu tiên • Select @Var2 = HoTen, @Var1 = Tuoi from SV where MaSV = 1 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, , >= • Ví dụ 2: or when the subquery is used as an expression. • Declare @hoten nvarchar(40) • set @hoten = (select hoten from sinhvien) • print @hoten Chương III : Truy vấn nâng cao 6
4. Cấu trúc điều khiển • Cấu trúc if - Else If logial expression Có thể chứa các câu truy [Begin] vấn phức tạp tùy ý Code block [End] • Khai báo biến • Các tính toán trên biến Else • Các câu truy vấn phức tạp tùy ý [Begin] • Code block [End] Optional Chương III : Truy vấn nâng cao 8
5. Cấu trúc điều khiển • Ví dụ : • HocPhan (MaHP, TenHP, SiSo) • DangKy (MaSV, MaHP) • Viết lệnh để thêm một đăng ký mới cho sinh viên có mã số 001 vào học phần HP01 (giả sử học phần này đã tồn tại trong bảng HocPhan). Qui định sỉ số lớp cho mỗi học phần không quá 50 sv Chương III : Truy vấn nâng cao 10
6. Cấu trúc điều khiển • Cấu trúc while WHILE Logical_expression [Begin] { sql_statement | statement_block } Ngưng hẳn [ BREAK ] vòng lặp, thoát ra và { sql_statement | statement_block } thực hiện các lệnh kế [ CONTINUE ] tiếp sau [End] vòng lặp Ngưng hẳn lần lặp hiện hành, chuyển sang thực hiện ngay lần lặp kế tiếp Chương III : Truy vấn nâng cao 12
7. Cấu trúc điều khiển • Ví dụ Declare @STT int While exists (select * from SV where MaSV = @STT) set @STT = @STT+1 Insert into SV(MaSV, HoTen) values(@STT, ‘Nguyen Van A’) Chương III : Truy vấn nâng cao 14
8. Cấu trúc điều khiển • Cấu trúc Case đơn giản • Ví dụ : SET @PLoai = CASE @loai WHEN 'pop_comp' THEN 'Popular Compung' WHEN 'mod_cook' THEN 'Modern Cooking' WHEN 'business' THEN 'Business' WHEN 'psychology' THEN 'Psychology' WHEN 'trad_cook' THEN 'Tradional Cooking' ELSE 'Not yet categorized' END Chương III : Truy vấn nâng cao 16
9. Cấu trúc điều khiển • Cấu trúc Searched Case CASE WHEN boolean_expression THEN result_expression [ n ] Lệnh Case [ ELSE else_result_expression ] thường làm vế phải của lệnh END gán, giá trị gán sẽ là result_expressio n khi mà boolean_express ion được thỏa Chương III : Truy vấn nâng cao 18
10. Cấu trúc điều khiển • Ví dụ • Cho biết mã NV, họ tên và loại nhân viên (cấp bậc <=3:bình thường, cấp bậc = null: chưa xếp loại, còn lại: cấp cao) 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 Chương III : Truy vấn nâng cao 20
11. Thủ tục thường trú • Khái niệm • Công việc lập trình luôn đòi hỏi khả năng tái sử dụng mã lệnh. • Không những vậy, các đoạn mã lệnh được tái sử dụng còn phải có nh uyển chuyển, xử lý linh động theo từng nh huống sử dụng. • Từ đó xuất hiện khái niệm lập trình hướng thủ tục (funconal). Các thủ tục được gọi lại và điều khiển thông qua hệ thống tham số Chương III : Truy vấn nâng cao 22
12. Thủ tục thường trú • Ý nghĩa • Tính tái sử dụng, uyển chuyển nhờ hệ thống tham số. • Khi biên dịch SP, SQL Server tối ưu hóa nó sao cho thực thi hiệu quả nhất. Kết quả tối ưu hóa được lưu bền vững. Khi gọi thực thi không cần tối ưu hóa lại à lới gọi thủ tục ết kiệm thời gian và tài nguyên hơn khối lệnh tương đương thân thủ tục. Chương III : Truy vấn nâng cao 24
13. Thủ tục thường trú • Ý nghĩa • SP giúp việc kết xuất báo biểu bằng Crystal Report trở nên đơn giản và hiệu quả hơn rất nhiều so với việc kết xuất trực ếp từ các table và view. Chương III : Truy vấn nâng cao 26
14. Thủ tục thường trú • Ví dụ : Xây dựng SP cho biết danh sách sinh viên của một lớp có mã cho trước Create proc DS_Lop @MaLop varchar(10) As Select SV.MaSV, SV.HoVaTen, SV.NgaySinh From SinhVien SV where SV.Lop = @MaLop Go Sử dụng tham số truyền vào Chương III : Truy vấn nâng cao 28
15. Thủ tục thường trú • Ví dụ Create proc TongTien Giá trị @MaDH varchar(10) = ‘DH001’ default As Declare @ThanhTien float Declare @TienThue float Declare @TienChietKhau float Declare @DonGia float,@SoLuong int Set @SoLuong = (select SoLuong from DonHang where Ma = @MaDH) Chương III : Truy vấn nâng cao 30
16. Thủ tục thường trú • Ví dụ Set @TienChietKhau = @ThanhTien*@TienChietKhau/100 Set @ThanhTien = @ThanhTien - @TienChietKhau Update DonHang set ThanhTien = @ThanhTien where Ma = @MaDH Go Chương III : Truy vấn nâng cao 32
17. Thủ tục thường trú Create proc XepLoaiSV @MaSV varchar(10), @DTB float output, @XL nvarchar(20) output As Set @DTB = Tham số đầu ra (Select avg(Diem) from KetQua Kq Where MaSV = @MaSV and not exists (select * from KetQua Kq1 where Kq1.MaSV = @MaSV and Kq1.MaMH=Kq.MaMH and Kq1.LanThi > Kq.LanThi) Chương III : Truy vấn nâng cao 34
18. Thủ tục thường trú Thư mục chứa thủ tục Chương III : Truy vấn nâng cao 36
19. Thủ tục thường trú • Gọi thực hiện Declare @MaSinhVien varchar(10) Declare @DiemTB varchar(10) Declare @XepLoai varchar(10) Set @MaSinhVien = ‘0112357’ Tham số là Exec XepLoaiSV @MaSinhVien, biến đã gán giá trị ‘0112357’ @DiemTB output,@XepLoai output Exec XepLoaiSV ‘0213478’, @DiemTB output,@XepLoai output Tham số là gía trị hằng Các tham số output Chương III : Truy vấn nâng cao 38
20. Thủ tục thường trú • Bẫy lỗi • Begin Try Chỉ có với • Đoạn code có thể phát sinh lỗi phiên • Bản SQL • End Try Server 2005 • Begin Catch Trở lên • Đoạn code xử lý lỗi phát sinh • • End Catch Error_Severity() Error_Number() Error_Message() Error_State() Chương III : Truy vấn nâng cao 40
21. Thủ tục thường trú • Thủ tục nhận tham số table • Ví dụ • Cho các bảng DL như sau : DonHang(maDH,tenDH,ngayLap,tongTien) CTDonHang(maDH,maSP,SLDat,DonGiaDat, thanhTien) • B1 : Tạo kiểu DL bảng tạm để chứa các CTDonHang từ form truyền xuống. CREATE TYPE CTDHTam AS TABLE (DH varchar(10),SP varchar(10), SL float, DG float, TT float) GO Chương III : Truy vấn nâng cao 42
22. Thủ tục thường trú • Thủ tục nhận tham số table • Ví dụ • B3 : Sử dụng thủ tục vừa viết Declare @CT as CTDHTemp Insert into @CT values( ) Insert EXEC usp_NhapDH ‘DH001’,N’Đơn hàng cty Tiến Thịnh’, ‘2012-9-19’, @CT GO Chương III : Truy vấn nâng cao 44
23. Thủ tục thường trú Chương III : Truy vấn nâng cao 46
24. Cursor • Khái niệm • Là một cấu trúc dữ liệu ánh xạ đến một tập các dòng dữ liệu là kết quả của một câu truy vấn (select) • Cho phép duyệt tuần tự qua tập các dòng dữ liệu và đọc giá trị từng dòng. • thể hiện của cursor là 1 biến, nhưng tên biến này không bắt đầu bằng ‘@’ Chương III : Truy vấn nâng cao 48
25. Cursor • Khai báo : dùng cú pháp chuẩn SQL 92 hoặc T_SQL mở rộng • Cú pháp SQL 92 chuẩn: Declare cur_name [Insensive] [Scroll] Cursor Nội dung cursor không thay For select_statement đổi dù DL thật thay đổi [ For {Read only| Update [of ColName [, n] ] }] Duyệt theo đa chiều Chỉ đọc, không dùng cursor Cho phép dùng cursor cập để cập nhật dữ liệu nhật dữ liệu trên 1 số (hoặc tất cả) cột Chương III : Truy vấn nâng cao 50
26. Cursor Mặc định: • Global • Forward_only • Read only hay “for update” tùy thuộc vào câu truy vấn • Dynamic Chương III : Truy vấn nâng cao 52
27. Cursor • Trình tự sử dụng : • Khai báo cursor • “Mở” cursor bằng lệnh : Open tên_Cursor • Khai báo các biến tạm để chứa phần tử hiện hành (đang được xử lý) của cursor • Các biến tạm phải cùng kiểu dữ liệu với các trường tương ứng của phần tử trong cursor. • Có n trường trong phần tử của cursor thì phải có đủ n biến tạm tương ứng Chương III : Truy vấn nâng cao 54
28. Cursor • Đóng cursor bằng lệnh Close Tên_cursor • Sau khi đóng, vẫn có thể mở lại nếu cursor chưa bị hủy • Hủy cursor bằng lệnh deallocate Deallocate Tên_cursor Chương III : Truy vấn nâng cao 56
29. Cursor declare cur_DSKhoa cursor for select MaKhoa, TenKhoa from Khoa open cur_DSKhoa declare @MaKhoa int, @TenKhoa varchar(30), @TenTat varchar(5) fetch next from cur_DSKhoa into @MaKhoa, @TenKhoa Chương III : Truy vấn nâng cao 58
30. Cursor • Ví dụ 2: dùng cursor để xác định dòng cập nhật 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 Chương III : Truy vấn nâng cao 60
31. Cursor Ví dụ 3 : Kết hợp cursor với SP • Xây dựng SP nh điểm trung bình và xếp loại cho sinh viên thuộc lớp cho trước. Giả sử các quan hệ như sau : • SinhVien(MaSV,HoVaTen,DTB,XepLoai,Lop) • MonHoc(MaMH,TenMH) • KetQua(MaMH,MaSV,LanThi,Diem) • Điểm thi chỉ nh lần thi sau cùng • Xếp loại : Xuất sắc [9,10], Giỏi [8,8.9], Khá [7,7.9], Trung bình [5.0,6.9], Yếu [0,4.9] • Kết quả ghi xuống CSDL, đồng thời xuất ra tổng số sinh viên xếp loại giỏi của lớp đó. Chương III : Truy vấn nâng cao 62
32. Cursor • Phân ch ví dụ : Như vậy ta thấy • Cần xử lý nhiều phần tử (các sinh viên). • Mỗi phần tử xử lý tương đối phức tạp (truy vấn,nh toán, gọi thủ tục khác, điều kiện rẽ nhánh,cập nhật dữ liệu ) • Cách xử lý các phần tử là như nhau • à Sử dụng cursor là thích hợp • Cursor chứa các sinh viên của lớp cần xét, chỉ cần chứa mã sinh viên là được Chương III : Truy vấn nâng cao 64
33. Cursor Fetch next from cur_SV into @MaSV While @@fetch_status=0 Lấy phần tử đầu tiên Begin Exec XepLoaiSV @MaSV,@DTB output,@XepLoai output Update SinhVien set DTB =@DTB,XepLoai=@XepLoai Where MaSV = @MaSV Fetch next from cur_SV into @MaSV Lấy phần tử End tiếp theo Close cur_SV Đóng cursor Giải Deallocate cur_SV phóng bộ Set @SoSVGioi = (slecte count(*) from SinhVien nhớ Where Lop=@Lop and XepLoai = N‘Giỏi’) chiếm bởi Go cursor Chương III : Truy vấn nâng cao 66
34. Hàm người dùng • Hàm người dùng (funcon) cũng giống như SP • 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 SP • Có giá trị trả về (một và chỉ một giá trị trả về) • Không chấp nhận tham số output Chương III : Truy vấn nâng cao 68
35. Hàm người dùng • Có thể xem hàm người dùng thuộc về 3 loại tùy theo giá trị trả về của nó : • Giá trị trả về là kiểu dữ liệu cơ sở (int, varchar, float, dateme ) • Giá trị trả về là Table có được từ một câu truy vấn (thuộc nhánh Table value funcon) • Giá trị trả về là table mà dữ liệu có được nhờ ch lũy dần sau một chuỗi thao tác xử lý và insert (thuộc nhánh Table value funcon). Chương III : Truy vấn nâng cao 70 70
36. Hàm người dùng • Ví dụ Create funcon SoLonNhat (@a int,@b int,@c int) returns int As Begin declare @max int Dù không có tham số cũng phải ghi cặp set @max = @a ngoặc rỗng if @b > max set @max = @b if @c > max set @max = @c return @max End Dù thân function chỉ có 1 lệnh cũng phải đặt giữa Begin và End Chương III : Truy vấn nâng cao 72
37. Hàm người dùng • Ví dụ : Create funcon DanhSachMatHang (@MaDonHang varchar(10)) returns Table As Return (Select MH.TenHang,MH.DonGia From ChiTietDH CT,MatHang MH Where CT.MaDH = @MaDonHang and CT.MaMH = MH.MaMH) Go Chương III : Truy vấn nâng cao 74
38. Hàm người dùng • Ví dụ Create funcon DanhSachLop returns @DS Table(MaLop varchar(10),SoSV int) As Begin 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 values End (@Ma, Close cur_L (select count(*) from SinhVien Deallcocate cur_L where Lop=@Ma)) return Fetch next from cur_L into @Ma End Chương III : Truy vấn nâng cao 76
39. Hàm người dùng • Chỉ thị Cross Apply • Dùng trong câu truy vấn, “kết” table trả về bởi hàm (dạng 2 và 3) với các table khác trong câu truy vấn thông qua các tham số của hàm. • Ví dụ : • Giả sử có hàm trả về @n nhan viên lương cao nhất của một phòng ban có mã cho trước : Create funcon NVLC (@MaPhg varchar(10),@n int) returns table As return Chỉ có với Select top(@n) * from NhanVien phiên Where Phg = @MaPhg Bản SQL Server 2005 Order by Luong Trở lên Chương III : Truy vấn nâng cao 78
40. Hàm người dùng • Các hàm người dùng được sử dụng trong câu truy vấn, trong biểu thức phù hợp kiểu dữ liệu trả về của nó • Ví dụ: • Select dbo.SoLonNhat(87,6,120) • Select * from DanhSachMatHang(‘DH007’) Chương III : Truy vấn nâng cao 80
41. Thư mục chứa hàm người dùng Loại 2, 3 Loại 1 Thư mục chứa hàm hệ thống Chương III : Truy vấn nâng cao 82