Bài giảng 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