CHƯƠNG III: TRUY VẤN NÂNG CAO GV: TUẤN NGUYÊN HOÀI ĐỨC Email: tnhduc@fit.vn 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 Khai báo biến • Cú pháp : • Declare Var_name Datatype • Lưu ý tên biến : Tên biến phải bắt đầu bằng 1 ký tự @ và chỉ 1 mà thôi. • Ví dụ : • Declare @MaSinhVien nvarchar(10) • Declare @TienLuong float • Declare @Sum float, @Count int Chương III : Truy vấn nâng cao 3 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 Lệnh gán •Set TenBien = GiaTri •Set TenBien = TenBien •Set TenBien = BieuThuc •Set TenBien = KetQuaTruyVan Câu truy vấn phải •Ví dụ : trả ra đúng 1 dòng và dòng đó phải có Set @MaLop = ‘TH2001’ đúng 1 cột Set @SoSV = (select count(*) from SinhVien) Set @MaLop = ‘TH’+Year(@NgayTuyenSinh) Chương III : Truy vấn nâng cao 5 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 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 7 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 Cấu trúc điều khiển • Cấu trúc if - Else If logial expression [Begin] Code block [End] [Else if logial expression [Begin] Có thể lặp lại nhiều lần Code block tùy ý.
Mô phỏng cấu trúc [End] case [,…n]] Else [Begin] Code block [End] Chương III : Truy vấn nâng cao 9 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 Cấu trúc điều khiển • Ví dụ Declare @SiSo int Select @SiSo = SiSo From HocPhan Where MaHP= ’HP01’ If @SiSo < 50 Begin Insert into DANGKY(MaSV, MaHP) Values(‘001’, ’HP01’) Print N’Đăng ký thành công’ End Else Print N’Học phần đã đủ SV’ Chương III : Truy vấn nâng cao 11 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 Cấu trúc điều khiển • Ví dụ • SinhVien(MaSV: int, HoTen: nvarchar(30)) • Viết lệnh xác định một mã sinh viên mới theo qui định: mã sinh viên 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 đó • Chẳng hạn : 1,2,3,7 à mã sinh viên mới : 4 Chương III : Truy vấn nâng cao 13 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 Cấu trúc điều khiển • Cấu trúc Case đơn giản CASE input_expression Lệnh Case thường WHEN when_expression làm vế phải của THEN result_expression lệnh gán, giá trị gán sẽ là [ .n ] result_expression [ ELSE else_result_expression ] khi mà END when_expression được thỏa Chương III : Truy vấn nâng cao 15 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 Compujng' WHEN 'mod_cook' THEN 'Modern Cooking' WHEN 'business' THEN 'Business' WHEN 'psychology' THEN 'Psychology' WHEN 'trad_cook' THEN 'Tradijonal Cooking' ELSE 'Not yet categorized' END Chương III : Truy vấn nâng cao 16 Cấu trúc điều khiển • Ví dụ : • NHAN_VIEN(MaNV, HoTen, NgaySinh, CapBac, Phai) Cho biết những nhân viên đến tuổi về hưu (tuổi về hưu của nam là 60, của nữ là 55) Select * From NHAN_VIEN Where datediff(yy, NgaySinh, getdate()) > = Case Phai when ‘Nam’ then 60 when ‘Nu’ then 55 End Chương III : Truy vấn nâng cao 17 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 Cấu trúc điều khiển • Cấu trúc Searched Case • Ví dụ : Set @DanhGiaMucGia = CASE WHEN @Gia IS NULL THEN 'Not yet priced' WHEN price < 10 THEN 'Very Reasonable Title' WHEN price >= 10 and price < 20 THEN 'Coffee Table' ELSE 'Expensive book!' END Chương III : Truy vấn nâng cao 19 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 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 21 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ó enh uyển chuyển, xử lý linh động theo từng jnh huống sử dụng. • Từ đó xuất hiện khái niệm lập trình hướng thủ tục (funcoonal).
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 Thủ tục thường trú • Khái niệm • Việc lập trình trên CSDL cũng không ngoại lệ. Trong môi trường SQL Server, các thủ tục được gọi là Thủ tục thường trú (stored procedure – SP) • Thường trú : Chỉ dịch 1 lần, từ đó lưu trữ bền vững trong CSDL, bền vững tựa như các table. Bất cứ khi nào cần, ta chỉ việc gọi thực hiện. Chương III : Truy vấn nâng cao 23 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ế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 Thủ tục thường trú • Ý nghĩa • Ứng dụng triển khai theo môi trường client – server.
Client gửi lời gọi SP lên server thì chiếm dụng đường truyền ít hơn rất nhiều so với việc gửi khối lệnh tương đương thân hàm à tránh nghẽn đường truyền, giảm trì trệ. • Đóng gói chỉ các thao tác cho phép trên CSDL vào các SP và quy định truy xuất DL phải thông qua SP. Ngoài ra còn có thể phân quyền trên SP. Chương III : Truy vấn nâng cao 25 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 oếp từ các table và view.
Chương III : Truy vấn nâng cao 26 Thủ tục thường trú • Cú pháp Tên của stored procedure Create {proc | procedure} proc_name Parameter DataType [output] [,…n] As Code block SP không có giá Go trị return, giá trị Tên trả ra nếu có thì tham dùng một (hay số một số) tham số (đặt output như tên Thân sủa SP, viết biến) như thế nào là tùy váo từng bài toán Kiểu DL của tham số cụ thể Chương III : Truy vấn nâng cao 27 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.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 Thủ tục thường trú • Ví dụ : • Xây dựng SP enh toán giá trị cho đơn hàng có mã cho trước với quan hệ DonHang như sau : • DonHang(Ma,SoLuong,DonGia,ThueSuat,ChietKha u,ThanhTien) Chương III : Truy vấn nâng cao 29 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 Thủ tục thường trú • Ví dụ Set @DonGia = (select DonGia from DonHang where Ma = @MaDH) Set @TienThue = (select ThueSuat from DonHang where Ma = @MaDH) Set @TienChietKhau = (select ChietKhau from DonHang where Ma = @MaDH) Set @ThanhTien = @DonGia*@SoLuong Set @TienThue = @ThanhTien*@TienThue/100 Set @ThanhTien = @ThanhTien + @TienThue Chương III : Truy vấn nâng cao 31 31 Chương IV : T-SQL Nâng cao 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 Thủ tục thường trú • Ví dụ : • Xây dựng SP \nh điểm trung bình và xếp loại cho sinh viên có mã 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] • Kết quả xuất dạng tham số output, không ghi xuống CSDL.