Giáo trình Hệ quản trị cơ sở dữ liệu - Chương 3: Thủ tục nội tại (Store procedure) - Lê Thị Minh Nguyên

8/25/2017 1 Chương 3. Thủ tục nội tại (Store procedure) GV: Lê Thị Minh Nguyện Email: nguyenltm@huflit.edu.vn Nội dung 1. Khái niệm 2. Phân loại 3. Thủ tục với tham số đầu vào 4. Thủ tục có dùng return 5. Thủ tục với tham số đầu ra 6. Bảng tạm trong thủ tục Hệ quản trị Cơ sở dữ liệu 2 1. Khái niệm • Store procedure (thủ tục): là một tập hợp chứa các dòng lệnh, các biến và các cấu trúc điều khiển trong ngôn ngữ Transaction-SQL dùng để thực hiện một hành động nào đó. • Lợi ích của thủ tục • Tối ưu hóa việc phân tích, biên dịch và thực thi câu lệnh. • Thực hiện một yêu cầu bằng một câu lệnh đơn giản hơn thay vì phải sử dụng nhiều dòng lệnh SQL khi thực thi  làm giảm thiểu sự lưu thông trên mạng • Tăng khả năng bảo mật khi cấp phát quyền thông qua thủ tục • Tốc độ xử lý của các thủ tục nội tại rất nhanh. Hệ quản trị Cơ sở dữ liệu 3 2. Phân loại • Thủ tục hệ thống • Bắt đầu bằng chữ sp_ và hầu hết tất cả các thủ tục hệ thống được lưu trữ bên trong CSDL Master. • Thủ tục do người dùng xây dựng Hệ quản trị Cơ sở dữ liệu 4 8/25/2017 2 Tạo thủ tục Hệ quản trị Cơ sở dữ liệu 5 • Tạo mới thủ tục • Cú pháp: CREATE PROC[EDURE] Tên_thủ_tục AS [Declare biến_cục_bộ] các_lệnh Tạo thủ tục Hệ quản trị Cơ sở dữ liệu 6 • Gọi thực hiện thủ tục: Cú pháp: EXEC[UTE] Tên_thủ_tục Ví dụ: EXEC sp_MaxSLHang • Thay đổi nội dung thủ tục Cú pháp: ALTER PROC[EDURE] Tên_thủ_tục AS [Declare biến_cục_bộ] Các_lệnh. 3. Thủ tục với tham số đầu vào Hệ quản trị Cơ sở dữ liệu 7 • Cú pháp: CREATE PROC[EDURE] Tên_thủ_tục @Tên_tham_số kiểu_dữ_liệu [= giá_trị] AS [Declare biến_cục_bộ] các_lệnh 3. Thủ tục với tham số đầu vào (tt) • Tạo thủ tục tên danhsach có tham số truyền vào là tên nhân viên. Liệt kê mã nhân viên, họ lót, tên nhân viên, ngày vào làm, lương: Hệ quản trị Cơ sở dữ liệu 8 CREATE PROC sp_danhsach @ten nvarchar(20) AS SELECT MaNV, HoLot, TenNV, NgayVaoLam, Luong FROM NhanVien WHERE TenNV= @ten • Thực thi: sp_danhsach ‘Linh’ hoặc exec sp_danhsach ‘Linh’ 8/25/2017 3 3. Thủ tục với tham số đầu vào (tt) • Sửa Hệ quản trị Cơ sở dữ liệu 9 ALTER PROC sp_danhsach @ten nvarchar(20) AS IF EXISTS(SELECT * FROM NhanVien WHERE TenNV = @ten) SELECT MaNV, HoLot, TenNV, NgayVaoLam, Luong FROM NhanVien WHERE TenNV= @ten ELSE print ‘không tồn tại nhân viên tên ’ + @ten 3. Thủ tục với tham số đầu vào (tt) Hệ quản trị Cơ sở dữ liệu 10 • Tạo thủ tục tăng lương nhân viên với tham số đầu vào là mức mã nhân viên. Nếu lương nhân viên lớn hơn 1000$ thì tăng 150$, ngược lại tăng 100$ CREATE PROC asc_salary(@idemp int) AS BEGIN DECLARE @salary INT SELECT @salary=Luong FROM NhanVien WHERE MaNV=@idemp If @salary>1000 UPDATE NhanVien SET Luong=Luong+150 WHERE MaNV=@idemp Else UPDATE NhanVien SET Luong=Luong+100 WHERE MaNV=@idemp END 3. Thủ tục với tham số đầu vào (tt) Hệ quản trị Cơ sở dữ liệu 11 • Tạo thủ tục tăng lương nhân viên lên 5 lần, mỗi lần tăng 50$ CREATE PROCEDURE asc_salary(@idemp int) AS BEGIN DECLARE @count int SET @count=1 WHILE @count<=5 BEGIN UPDATE NhanVien SET Luong=Luong+50 WHERE MaNV=@idemp SET @count=@count+1 END END 4. Thủ tục có dùng return • Các thủ tục có thể trả về giá trị số nguyên để thông báo thủ tục thực hiện thành công hay thất bại. SQL Server định nghĩa sẳn tập các giá trị trả về nằm trong khoảng [-99;0]. Giá trị 0 cho biết thủ tục thực hiện thành công, các giá trị còn lại cho biết nguyên nhân lỗi xảy ra Hệ quản trị Cơ sở dữ liệu 12 8/25/2017 4 4. Thủ tục có dùng return (tt) Hệ quản trị Cơ sở dữ liệu 13 CREATE PROC sp_vidu @ten nvarchar(20) AS IF EXISTS (SELECT * FROM NhanVien WHERE TenNV = @ten) RETURN 1 ELSE RETURN 2 Thực thi thủ tục có câu lệnh RETURN: Declare @a int Exec @a = sp_vidu N‘Hà’ if @a=1 print ‘Có tìm thấy Hà’ Else print ‘Không có Hà’ 5. Thủ tục với tham số đầu ra • Cú pháp: CREATE PROC Tên_thủ_tục @Tên_tham_số kiểu_dữ_liệu OUTPUT [,] AS [Declare Biến cục bộ] Các_lệnh Hệ quản trị Cơ sở dữ liệu 14 5. Thủ tục với tham số đầu ra (tt) Hệ quản trị Cơ sở dữ liệu 15 CREATE PROC Chia @sobichia real,@sochia real, @ketqua real OUTPUT AS IF (@sochia =0) Print ‘Lỗi chia cho 0’ ELSE SELECT @ketqua = @sobichia / @sochia Thực thi thủ tục: DECLARE @ketqua real EXEC Chia 100, 2, @ketqua OUT SELECT @ketqua 5. Thủ tục với tham số đầu ra (tt) • Tạo thủ tục với tham số đầu ra là lương nhân viên với tham số đầu vào là mã nhân viên Hệ quản trị Cơ sở dữ liệu 16 CREATE PROC salary (@idemp int, @salary int OUTPUT) AS SELECT @salary=Luong FROM NhanVien WHERE MaNV=@idemp Thực thi: DECLARE @salary int EXEC salary 5, @salary=@salary OUTPUT PRINT ‘Lương nhân viên:’+CAST(@salary AS VARCHAR(10))+’$’ 8/25/2017 5 5. Thủ tục với tham số đầu ra (tt) • Cho biết tổng lương của nhân viên với tham số đầu vào là mã phòng ban Hệ quản trị Cơ sở dữ liệu 17 CREATE PROC sum_salary (@deptid int, @sumsalary int OUTPUT) AS BEGIN If NOT EXISTS (SELECT * FROM NhanVien WHERE MaPB=@deptid) RETURN 1 SELECT @sumsalary=SUM(Luong) FROM NhanVien WHERE MaPB=@deptid RETURN If @sumsalary IS NULL SET @sumsalary=0 END 5. Thủ tục với tham số đầu ra (tt) • Thực thi thủ tục: DECLARE @sumsalary, @result EXEC @result=sum_salary 44,@sumsalary OUTPUT If @result=1 PRINT ‘Không tồn tại phòng ban’ Else PRINT ‘Tổng lương của phòng là:’ + CAST(@sumsalary AS VARCHAR(15))+ ‘$’ Hệ quản trị Cơ sở dữ liệu 18 6. Bảng tạm trong thủ tục Hệ quản trị Cơ sở dữ liệu 19 • Cú pháp: SELECT danh_sách_các_cột INTO #Tên_bảng_tạm FROM Tên_bảng_dữ_liệu (#): tạo ra các bảng tạm cục bộ (##): tạo ra các bảng tạm toàn cục 6. Bảng tạm trong thủ tục Hệ quản trị Cơ sở dữ liệu 20 • Cú pháp: SELECT danh_sách_các_cột INTO #Tên_bảng_tạm FROM Tên_bảng_dữ_liệu (#): tạo ra các bảng tạm cục bộ (##): tạo ra các bảng tạm toàn cục 8/25/2017 6 6. Bảng tạm trong thủ tục (tt) Hệ quản trị Cơ sở dữ liệu 21 CREATE PROC sp_TinhDTCaoNhat @namThang char(7), @TenMH char(50) OUTPUT, @TongTien Money OUTPUT AS Select MH.MaMH, TenMH, Sum(SLXuat*DGXuat) AS TT INTO #DoanhThu From PX, CTPX, MH Where PX.SoPX = CTPX.SoPX And CTPX.MaMH = MH.MaMH And Convert(char(7), ngayxuat, 21) = @namthang Group By MH.MaMH, TenMH Order by SUM(SLXUAT*DGXUAT) DESC Select Top 1 @TenMH=TenMH, @Tongtien = TT From #DoanhThu Tạo thủ tục cho biết một năm tháng bất kỳ cho biết mặt hàng nào bán ra doanh thu cao nhất trong tháng năm đó 6. Bảng tạm trong thủ tục (tt) Hệ quản trị Cơ sở dữ liệu 22 Declare @Ten_MH char(50), @Tong_Tien Money EXEC sp_TinhDTCaoNhat ‘2017-01’, @TenMH = @Ten_MH OUTPUT, @TongTien = @Tong_Tien OUTPUT IF @TenMH IS NULL Print ‘không có dữ liệu tính toán’ ELSE Begin Print Rtrim(@TenMH) + ‘có doanh thu cao nhất’ Print ‘là ’ + CAST(@TongTien AS Varchar(20)) + ‘VND’ End Hệ quản trị Cơ sở dữ liệu 23