Bài giảng Hệ quản trị Cơ sở dữ liệu SQL Server - Bài 8: Thủ tục nội tại

12/8/2018 Microsoft SQL Server 2005 1 Bài 8: Thủ tục nội tại n Nội dung:  Khái niệm  Các thao tác với thủ tục nội tại  Tham số bên trong thủ tục nội tại  Một số vấn đề khác  Giao tác 2/8/2018 Microsoft SQL Server 2005 2 Khái niệm Thủ tục nội tại: n Tập hợp các lệnh, cấu trúc điều khiển n Cho phép truyền các giá trị qua các tham số vào và trả về các giá trị qua tham số đầu ra 2/8/2018 Microsoft SQL Server 2005 3 Các loại thủ tục nội tại n Thủ tục nội tại hệ thống: thực hiện các chức năng quản trị CSDL thường dùng, được đặt trong CSDL Resource (CSDL chỉ để đọc và được ánh xạ qua các CSDL dưới dạng sys, ví dụ: sys.objects, sys.tables, ). Tên thủ tục nội tại hệ thống luôn bắt đầu với tiền tố sp_ n Thủ tục nội tại hệ thống mở rộng: thực hiện các chức năng quản trị mức cao hơn, cũng được lưu trong CSDL Resource, tên bắt đầu với tiền tố xp_ n Thủ tục nội tại do người dùng tự xây dựng 2/8/2018 Microsoft SQL Server 2005 4 Danh sách các thủ tục nội tại hệ thống n Xem trong ngăn System Stored Procedure: 22/8/2018 Microsoft SQL Server 2005 5 Các thao tác với thủ tục nội tại n Tạo mới một thủ tục nội tại n Gọi thực hiện thủ tục nội tại n Xóa thủ tục nội tại n Thay đổi nội dung thủ tục nội tại 2/8/2018 Microsoft SQL Server 2005 6 Tạo mới một thủ tục nội tại n Cú pháp tạo mới một thủ tục nội tại dạng đơn giản Create Proc/Procedure Tên_thủ_tục as [Declare Biến_cục_bộ] Các_lệnh Ví dụ 1: Thủ tục tìm số lượng xuất lớn nhất (trong một lần xuất) của các vật tư: Create proc SLmax As Declare @SLmax int Select @SLmax=max(SlXuat) from CTPXUAT Print @Slmax 2/8/2018 Microsoft SQL Server 2005 7 Gọi thực hiện/Xóa thủ tục nội tại n Để thực hiện thủ tục nội tại, sử dụng cú pháp: Exec Tên_thủ_tục Ví dụ 2: Exec Slmax n Để xóa thủ tục nội tại, sử dụng cú pháp: Drop proc/Procedure Tên_thủ_tục Ví dụ 3: Drop proc Slmax 2/8/2018 Microsoft SQL Server 2005 8 Thay đổi nội dung của thủ tục nội tại n Cú pháp: Alter proc/procedure Tên_thủ_tục as [Declare Biến_cục_bộ] Các_lệnh Ví dụ 4: Alter proc SLmax As Declare @SLmax int Select @Slmax = max(SlXuat) from CTPXUAT Print 'So luong xuat lon nhat: ' + convert(char(6),@Slmax) à Cách tương đương: Xóa đi rồi tạo lại thủ tục mới 32/8/2018 Microsoft SQL Server 2005 9 Ví dụ 5 Tạo thủ tục in ra tên vật tư có số lượng bán cao nhất trong tháng 01/2002? 2/8/2018 Microsoft SQL Server 2005 10 Giải ví dụ 5 Create proc Vattuxuat_max As declare @TenVT nvarchar(100),@SLmax int Select CTPXUAT.MaVTu,TenVTu,sum(SlXuat) as TongSLX into BangSoluongxuat From VATTU inner join CTPXUAT on VATTU.MaVTu=CTPXUAT.MaVTu inner join PXUAT on CTPXUAT.SoPX=PXUAT.SoPX Where convert(char(7),Ngayxuat,21)='2002-01' Group by CTPXUAT.MaVTu,TenVTu 2/8/2018 Microsoft SQL Server 2005 11 Giải ví dụ 5 Select @TenVT=TenVTu,@SLmax=TongSLX From BangSoluongxuat Where TongSLX = (select max(TongSLX) from BangSoluongxuat) Print 'Vat tu duoc xuat nhieu nhat trong thang 01 nam 2002: ' + @TenVT Print 'So luong xuat la: ' + convert(char(6),@SLmax) Drop table Bangsoluongxuat Go Exec Vattuxuat_max -- Thực thi thủ tục 2/8/2018 Microsoft SQL Server 2005 12 Lưu ý n Các thao tác tạo mới, gọi thực hiện, xóa, thay đổi nội dung của thủ tục nội tại có thể tiến hành từ cửa sổ Object Explorer Ví dụ 6: Xóa thủ tục Slmax từ cửa sổ Object Explorer 42/8/2018 Microsoft SQL Server 2005 13 Nhóm thủ tục à Chứa nhiều thủ tục thành phần n Tạo nhóm thủ tục: tạo các thủ tục thành phần với cùng tên và có bổ sung tham số n sau tên thủ tục Ví dụ 7: create proc Nhom;1 as delete from Test1 Go create proc Nhom;2 as delete from Test2 Go Kết quả 2/8/2018 Microsoft SQL Server 2005 14 Nhóm thủ tục n Thực thi nhóm thủ tục: Cần thực thi từng thủ tục thành phần n Sửa nhóm thủ tục: Cần sửa từng thủ tục thành phần n Xóa nhóm thủ tục: Chỉ cần xóa theo tên chung của nhóm thủ tục 2/8/2018 Microsoft SQL Server 2005 15 Tham số trong thủ tục nội tại n Tham số đầu vào - Cú pháp khai báo: các khai báo tham số đầu vào cần đặt trước từ khóa As Create proc Tên_Thủ tục @Tên_tham_số Kiểu_dliệu [=Giá_trị] [,...] As ... Ví dụ 8: Create proc SlDat @pMaVTu char(4),@pSoDh char(4) As ... 2/8/2018 Microsoft SQL Server 2005 16 Tham số trong thủ tục nội tại - Gọi thực thi thủ tục có truyền giá trị cho tham số đầu vào: Exec Tên_thủ_tục Giá_trị [,...] hoặc: Exec Tên_thủ_tục @Tên_tham_số=Giá_trị [,...] Ví dụ 9: Exec SlDat 'DD01', 'D001' hoặc: Exec SlDat @pMaVTu='DD01',@pSoDh= 'D001' 52/8/2018 Microsoft SQL Server 2005 17 Ví dụ về thủ tục có tham số đầu vào n Ví dụ 10: Tạo thủ tục tính trị giá của 1 phiếu xuất với tham số truyền vào là số phiếu xuất với kiểu dữ liệu chuỗi: Create proc TrigiaPX @pSoPx char(4) as Declare @TGia money Select @Tgia = sum(SlXuat*DgXuat) from CTPXUAT where SoPx=@pSoPx Print 'Tri gia phieu xuat '+ cast(@pSoPx as char(4))+ ' la: ' + cast(@TGia as varchar(15)) Go -- Gọi thực thi: Exec TrigiaPX 'X003' 2/8/2018 Microsoft SQL Server 2005 18 Ví dụ về thủ tục có đặt giá trị mặc định cho tham số n Ví dụ 11: Tương tự ví dụ 10 nhưng có đặt giá trị mặc định cho tham số đầu vào @pSoPx là 'X001' Create proc TrigiaPX @pSoPx char(4) = 'X001' as Declare @TGia money Select @Tgia = sum(SlXuat*DgXuat) from CTPXUAT where SoPx=@pSoPx Print 'Tri gia phieu xuat '+ cast(@pSoPx as char(4))+ ' la: ' + cast(@TGia as varchar(15)) Go à Khi gọi thực thi thủ tục mà không truyền tham số Exec TrigiaPX thì thủ tục tự động thực hiện với giá trị mặc định của @pSoPx = 'X001' 2/8/2018 Microsoft SQL Server 2005 19 Tham số trong thủ tục nội tại (tiếp) n Tham số đầu ra - Cú pháp khai báo: Create proc Tên_Thủ_tục @Tên_tham_số Kiểu_dliệu OUTPUT as ... Ví dụ 12: Create proc SlDat @pSldat int output as ... - Gọi thực thi thủ tục: Exec Tên_thủ_tục [@Tên_tham_số=] @Tên_biến_ra output Ví dụ 13: Declare @Sldathang int Exec SlDat @Sldathang output 2/8/2018 Microsoft SQL Server 2005 20 Ví dụ về thủ tục có tham số đầu ra Ví dụ 14: Tạo thủ tục tìm số lượng đặt của vật tư theo đơn đặt hàng: Create proc SlDat @pMaVTu char(4),@pSoDh char(4), @pSlDat int output as If not exists(Select * from CTDONDH where (MaVTu=@pMaVTu) and (SoDh=@pSoDh)) Print 'Kiem tra lai so don dat hang va ma vat tu' Else Select @pSlDat=Sldat from CTDONDH where(MaVTu=@pMaVTu) and (SoDh=@pSoDh) Go 62/8/2018 Microsoft SQL Server 2005 21 Ví dụ về thủ tục có tham số đầu ra -- Gọi thực thi thủ tục Declare @SlDathang int Exec SlDat 'DD01','D001',@SlDathang output Print 'So luong dat hang la: ' +convert(char(10),@SlDathang) 2/8/2018 Microsoft SQL Server 2005 22 Một số vấn đề khác n Xem nội dung của thủ tục nội tại: Exec sp_helptext Tên_thủ_tục n Mã hóa nội dung thủ tục: bổ sung with Encryption Create/Alter proc Tên_thủ_tục with encryption as [Declare Biến_cục_bộ] Các_lệnh Go n Chế độ biên dịch lại khi gọi thực thi thủ tục: bổ sung With Recompile 2/8/2018 Microsoft SQL Server 2005 23 Một số vấn đề khác n Thủ tục lồng nhau: à Có thể thực hiện lời gọi tới một thủ tục nội tại trong một thủ tục nội tại khác à Cấp lồng nhau: tối đa 32 cấp Ví dụ 15: Tạo thủ tục nội tại trả về tổng số lượng đặt hàng và số lượng nhập hàng của một vật tư dựa theo 2 tham số đầu vào là Mã vật tư và Số đơn đặt hàng 2/8/2018 Microsoft SQL Server 2005 24 Giải ví dụ 15 Create proc SlDat_Nhap -- Tạo thủ tục @pMaVTu char(4), @pSoDh char(4), @pSlDat int output, @pSlNhap int output as Exec SlDat @pMaVTu,@pSoDh,@pSlDat output If @pSlDat is null Begin print 'So luong dat hang la 0' return End 72/8/2018 Microsoft SQL Server 2005 25 Giải ví dụ 15 Select @pSlNhap = sum(SlNhap) from CTPNHAP inner join PNHAP on CTPNHAP.SoPn=PNHAP.SoPn where (MaVTu=@pMaVTu)and(SoDh=@pSoDh) If @pSlNhap is Null set @pSlNhap=0 Go 2/8/2018 Microsoft SQL Server 2005 26 Giải ví dụ 15 (tiếp) -- Gọi thực thi Declare @SlDathang int,@SlNhaphang int Exec SlDat_Nhap 'DD01','D001', @SlDathang output, @SLNhaphang output Print 'So luong dat hang DD01 theo don hang D001 la: ' +cast(@SLDathang as char(6)) Print 'So luong da nhap la: ' +cast(@SLNhaphang as char(6)) 2/8/2018 Microsoft SQL Server 2005 27 Một số vấn đề khác (tiếp) n Thủ tục trả về giá trị (là một số nguyên) với Return: - Lệnh return: Return giá_trị_chỉ_định - Gọi thực hiện thủ tục có lệnh return: sử dụng 1 biến cục bộ để nhận giá trị trả về, cú pháp Declare @Biến int Exec @Biến=Tên_thủ_tục[các_tham_số] - Lưu ý: trong trường hợp Biến nhận giá trị trả về là số theo kiểu double, float, decimal thì kết quả trả về vẫn là giá trị được làm tròn thành số nguyên 2/8/2018 Microsoft SQL Server 2005 28 Ví dụ về thủ tục trả về giá trị Ví dụ 16: Xây dựng thủ tục nội tại tính tổng số lượng đặt hàng của một vật tư theo nhà cung cấp đã được chỉ định (tham số truyền vào là mã vật tư và mã nhà cung cấp), quy định thủ tục trả về 1 khi mã vật tư không tồn tại, trả về 2 khi mã nhà cung cấp không tồn tại 82/8/2018 Microsoft SQL Server 2005 29 Giải ví dụ 16 n Tạo thủ tục: Create proc TongSlDat @pMaVTu char(4),@pMaNCC char(4), @pTongSldat int output as If not exists(select * from VATTU where MaVTu=@pMaVTu) return 1 If not exists(select * from NHACC where MaNCC=@pMaNCC) return 2 2/8/2018 Microsoft SQL Server 2005 30 Giải ví dụ 16 (tiếp) n Tạo thủ tục (tiếp): Select @pTongSlDat=sum(SlDat) from DONDH inner join CTDONDH on DONDH.SoDh=CTDONDH.SoDh where (MaVTu=@pMaVTu) and (MaNCC=@pMaNCC) If @pTongSlDat is null set @pTongSlDat=0 Return Go 2/8/2018 Microsoft SQL Server 2005 31 n Gọi thực thi thủ tục: Declare @TSLD int,@Ketqua int Exec @Ketqua=TongSlDat 'DD01','C03', @TSLD output If @Ketqua=1 print 'Ma vat tu khong ton tai' Else If @Ketqua=2 print 'Ma nha cung cap khong ton tai' Else print 'Tong so luong dat la ' +cast(@TSLD as varchar(10)) Go Giải ví dụ 16 (tiếp) 2/8/2018 Microsoft SQL Server 2005 32 n Sử dụng cú pháp: Select Danh_sách_cột into #/##Tên_bảng_tạm From Tên_bảng_dl n Nên xóa các bảng tạm trước khi kết thúc thủ tục n Bài tập: Tìm vật tư có doanh thu bán ra cao nhất trong một năm tháng bất kỳ (dữ liệu truyền vào là năm tháng) Gợi ý: Có thể tạo bảng tạm tính tổng tiền bán cho các vật tư, sắp xếp bảng theo thứ tự giảm dần, lấy ra vật tư đầu tiên Sử dụng bảng tạm trong thủ tục 92/8/2018 Microsoft SQL Server 2005 33 Tham số kiểu cursor bên trong thủ tục n Thường trả về giá trị danh sách các dòng dữ liệu n Các bước thực hiện: - Trong thủ tục: định nghĩa tham số kiểu cursor, mở cursor - Khi gọi thủ tục: đọc từng dòng dữ liệu bên trong cursor, đóng cursor n Cú pháp khai báo và khởi tạo giá trị thông dụng: Create proc Tên_thủ_tục thamsố1 kiểu, thamsố2 kiểu, @Thamsốkiểucontrỏ Cursor Varying output as ... set @Thamsốkiểucontrỏ = cursor --Khởi tạo giá trị For ... Open @Thamsốkiểucontrỏ --Mở cursor ... 2/8/2018 Microsoft SQL Server 2005 34 Thủ tục cập nhật bảng dữ liệu n Trong các thủ tục cập nhật dữ liệu thường sử dụng exists() và raiserror() để kiểm tra dữ liệu trước khi cập nhật (đảm bảo tính toàn vẹn dữ liệu) và thông báo kết quả kiểm tra n Ví dụ 17: if exists(select MaVTu from VATTU where MaVTu=@pMaVTu) Begin set @pErrMsg = 'Ma vat tu da ton tai. Can cap 1 ma khac' raiserror(@pErrMsg,16,1) return End 2/8/2018 Microsoft SQL Server 2005 35 Thủ tục hiển thị dữ liệu n Sử dụng khi cần báo cáo với dữ liệu hiển thị phức tạp n Dữ liệu có thể lấy từ: - Bảng dữ liệu hoặc bảng ảo trong CSDL - Câu lệnh select trực tiếp - Đối tượng trong thủ tục nội tại n Ví dụ 18: Create proc BaocaoTTinVTu @pMaVTu char(4)=null as If not(@pMaVTu=null) select * from VATTU where MaVTu=@pMaVTu Else Begin print 'Ban khong nhap ma vat tu' select * from VATTU End 2/8/2018 Microsoft SQL Server 2005 36 Giao tác n Một nhóm công việc: - Thực hiện tuần tự - Chỉ được ghi nhận khi tất cả các công việc được thực hiện thành công, nếu có 1 công việc thất bại à không được ghi nhận n Tính ACID: - Atomic - Consistency - Isolation - Durability n Phân loại: - Giao tác tường minh - Giao tác không tường minh 10 2/8/2018 Microsoft SQL Server 2005 37 Giao tác không tường minh n Nhóm các câu lệnh đặt trong một lô (batch) n Ví dụ 19: Insert into VATTU values('BL01','Bàn là Phillip','Cái',17) Delete DONDH where SoDH='D001' Update NHACC set TenNCC='Trần Thành Trung' where MaNCC='C01' Go à Lệnh delete không được thực thi nếu các khóa ngoại trong các bảng liên kết với DONDH không đặt chế độ ràng buộc cascade cho hành động Delete 2/8/2018 Microsoft SQL Server 2005 38 Giao tác tường minh n Các hành động cập nhật dữ liệu phải nằm trong cùng một đơn vị xử lý, phải được khai báo trước theo cú pháp: BEGIN TRAN [Tên_giao_tác] n Kết thúc giao tác: - COMMIT TRAN [Tên_giao_tác]: các hành động cập nhật dữ liệu được ghi nhận lại - ROLLBACK TRAN [Tên_giao_tác]: không ghi nhận lại các hành động cập nhật dữ liệu 2/8/2018 Microsoft SQL Server 2005 39 Các ví dụ n Ví dụ 20: Select count(*) as 'Tong vat tu truoc khi them' from VATTU Begin Tran Insert into VATTU values('BL01','Bàn là Phillip','Cái',17) Select count(*) as 'Tong vat tu sau khi them' from VATTU Rollback Tran Select count(*) as 'Tong vat tu hien tai' from VATTU 2/8/2018 Microsoft SQL Server 2005 40 Các ví dụ (tiếp) n Ví dụ 21: Các giao tác lồng nhau Begin Tran Cap1 Insert into VATTU values('BL02','Bàn là Toshiba','Cái',20) Begin Tran Cap2 Insert into VATTU values('QM01','Quạt máy điện cơ','Cái',20) Commit Tran Cap2 Rollback Tran Cap1 à Cả thao tác cập nhật trong Tran Cap2 cũng không được ghi nhận 11 2/8/2018 Microsoft SQL Server 2005 41 Phân vùng trong giao tác n Chia nhỏ các công việc trong giao tác, chỉ định các vùng nhỏ được ghi nhận các hành động cập nhật dữ liệu hoặc không, cú pháp: Save Tran [Tên_vùng] Các_lệnh 2/8/2018 Microsoft SQL Server 2005 42 Phân vùng trong giao tác n Ví dụ 22: Begin Tran Save Tran Dong1 Insert into VATTU values('BL02','Bàn là Toshiba','Cái',20) Save Tran Dong2 Insert into VATTU values('QM01','Quạt máy điện cơ','Cái',2) Rollback Tran Dong2 Commit Tran Dong1 Go