Bài giảng SQL Server 2005 - Chương VII: Stored Procedure - Phạm Mạnh Cương

CHƯƠNG VII 
STORED PROCEDURE 
Giảng viên : Phạm Mạnh Cương 
1 
I- Khái niệm : 
2 
• Là một chương tr ì nh con được xây dựng bởi những phát biểu của T-SQL 
• Có tham số Input và Output 
• Được lưu trữ trong CSDL với 1 tên phân biệt . 
• Cho phép gọi thực hiện từ các ngôn ngữ lập trình khác . 
• Stored Procedure thường dùng thực hiện truy vấn hay cập nhật dữ liệu theo yêu cầu của người dùng. 
II- Tạo Stored Procedure 
3 
1. Bằng Lệnh CREATE PROCEDURE 
2. Bằng công cụ quản lý Management Studio 
1- Bằng Lệnh CREATE PROCEDURE: 
4 
Create Proc 
[ ] 
[With Recompile | Encryption | Recompile , Encryption] 
As 
[Begin] 
[End] 
Ví dụ : Tạo thủ tục liệt kê sản phẩm tồn kho  trong một tháng 
5 
Create Proc spTonKho(@Thang int = 8, @Nam int = 2005) 
As 
Begin 
Select * From TonKho 
Where Thang = @ Thang And Nam = @Nam 
End 
Ví dụ : Tạo thủ tục trả về Doanh thu của một năm 
6 
Create Proc spDTNam (@Nam Smallint , @ DoanhThu Float Output ) 
AS 
Begin 
Select @ DoanhThu = Sum(SL * DGBan ) 
From HoaDon hd Inner Join CTHD ct On hd.SoHD = ct.SoHD 
Where Year(NgayHD ) = @Nam 
End 
1.1 Khai báo tham số hình thức của thủ tục : 
7 
Cú pháp : @ par_name datatype [= default ][OUTPUT ] [,n] 
Có 2 loại tham số hình thức : Input và Output. 
• Tham số Output dùng liên kết với một biến tại nơi gọi , nhận 
giá trị của biến và trả giá trị về cho biến . 
Ví dụ : 
Create Proc spTonKho (@Thang int = 8, @Nam int = 2005 ) 
Create Proc spDTNam (@Nam int , @ DoanhThu Float Output ) 
• Tham số h ì nh thức nhận giá trị mặc định khi không truyền 
giá trị cho nó . 
1.2 Thực hiện Stored Procedures có Tham số 
8 
• Cách 1 Truyền theo vị trí : 
	EXEC sp_name [OUTPUT] [, n ] 
	 Ví dụ gọi thủ tục : 
	Create Proc spTonKho(@Thang int = 8, @Nam int = 2005) 
Exec spTonKho 9, 2005 
	 Sử dụng giá trị default : Exec spTonKho 9 
• Cách 2 Truyền theo tên : 
	EXEC sp_name @ par_name = [OUTPUT][, n ] 
Ví dụ gọi thủ tục : Create Proc spTonKho(@Thang int = 8, @Nam int = 2005) 
	Exec spTonKho @Nam = 2005, @ Thang = 8 
	 Sử dụng giá trị default : Exec spTonKho @Nam = 2006 
1.3 Nhận giá trị từ tham số Output 
9 
• Phải dùng Biến với từ khóa Output trong lời gọi để nhận giá 
trị từ tham số hình thức loại Output 
Create Proc spDTNam (@Nam int , @ DoanhThu Float Output ) 
Ví dụ : Thực hiện thủ tục spDTNam 
Declare @DT Float 
Exec spDTNam 2005, @DT Output 
Print @DT 
Hay 
Declare @DT Float 
Exec spDTNam @DoanhThu = @DT Output, @Nam = 2005 
Print @DT 
III- Các Phát Biểu Điều Khiển : 
10 
1. Biến cục bộ 
2. Phát biểu If .. Else 
3. Phát biểu WHILE 
4. Phát biểu : RETURN [ Biểu thức số nguyên ] 
5. Phát biểu : GOTO 
1- Biến cục bộ : 
11 
a) Khai báo biến cục bộ: DECLARE @VarName data _ type [,... n ] 
Ví dụ : Declare @n TinyInt , @m TinyInt , @y SmallInt 
• Biến cục bộ phải bắt đầu bằng k ý hiệu @ trước tên của nó. 
• Phạm vi biến : Biến chỉ khả dụng bên trong thủ tục , hàm hoặc một gói 
lệnh đ ã khai báo biến. 
• Giá trị ban đầu của biến là NULL 
b) Gán giá trị cho biến cục bộ : 
• Cách 1 : SET @ V arName = 
Ví dụ : Declare @ Ngay DateTime 
set @ Ngay ='2005/5/15' 
print @ Ngay 
• Cách 2 : SELECT @ V arName = [FROM ] 
– Nếu SELECT trả về nhiều d ò ng, th ì biến được gán giá tri từ d ò ng 
cuối cùng trả về bởi Select. 
Ví dụ : Tạo thủ tục liệt kê danh sách mặt hàng có  đơn giá cao nhất và thấp nhất. 
12 
CREATE PROCEDURE spMatHangCaoNhatThapNhat 
AS 
Begin 
Declare @Dgmax float, @Dgmin float 
SELECT @Dgmax = Max(DonGia), @Dgmin = Min(DonGia) 
FROM MatHang 
SELECT * FROM MatHang 
WHERE DonGia = @Dgmax OR DonGia = @Dgmin 
ORDER BY DonGia DESC 
End 
2- Phát biểu If .. Else: 
IF 
BEGIN 
END 
ELSE 
BEGIN 
END 
Create Proc spTonKho(@Thang int , @Nam int ) 
As 
If @ Thang 12 
Begin 
	Print ' Thang sai ' 
End 
Else 
Begin 
	Select * From TonKho 
	Where Thang = @ Thang 
	And Nam = @Nam 
End 
13 
3- Phát biểu WHILE : 
14 
WHILE 
BEGIN 
[CONTINUE] 
[BREAK] 
END 
Ví dụ: Thủ tục tính tổng S = 1 + 3 +  + (2N-1) 
Create Proc spTongLe ( @N int ) 
AS 
DECLARE @I int , @S int 
SET @I = 1 
SET @S = 0 
WHILE @I <= 2*@N -1 
BEGIN 
	Set @S = @S + @I 
	Set @I = @I + 2 
END 
Return @S 
4- Phát biểu RETURN [ Biểu thức số nguyên ]: 
15 
Dùng kết thúc thủ tục và trả về nơi gọi một số nguyên 
• Mặc định là 0 nếu không chỉ định 
• Gọi hàm và nhận giá trị Return của thủ tục : 
EXEC @ var_name = sp_name [@ par_name =] [, n ] 
Ví dụ : Gọi thực hiện thủ tục spTongLe ( @N int ) 
declare @s int 
Exec @s = spTongLe 9 
print 'Tong la = ' + Cast (@s As Varchar) 
Ví dụ: Thủ tục Thêm hóa đơn mới 
16 
Create Proc spThemHoaDon (@SoHD int , @ MaNV int , @ NgayHD DateTime ) 
As 
If Exists(Select * From HoaDon Where SoHD = @ SoHD ) 
Begin 
Print ' Trung so hoa don' 
Return 1 
End 
If Not Exists(Select * From NhanVien Where MaNV = @ MaNV ) 
Begin 
Print 'Sai Ma nhan vien ' 
Return 2 
End 
If @ NgayHD > GetDate () 
Begin 
Print ' Ngay lap hoa don lon hon ngay hien hanh ' 
Return 3 
End 
Insert HoaDon (SoHD, MaNV, NgayHD) Values(@SoHD, @MaNV, @NgayHD) 
if @@Error 0 
Begin 
Print ' Loi them hoa don moi ' 
Return 4 
End 
Return 0 
5- Phát biểu : GOTO 
17 
Create Proc spTongLe ( @N int ) 
AS 
DECLARE @I int, @S int 
	SET @I = 1 
	SET @S = 0 
Lap: 
IF (@I > 2*@N -1) GoTo KetQua 
	Set @S = @S + @I 
	Set @I = @I + 2 
GoTo Lap 
KetQua: 
Return @S 
-- Thuc hien thu tuc 
Declare @KQ int 
Exec @KQ = spTongLe 9 
Print @ kq 
IV- Sửa Xóa Thủ Tục : 
18 
1. Đổi Tên : 
	 sp_Rename , 
2. Xóa Thủ tục được lưu: 
	DROP PROC 
3. Thay đổi nội dung thủ tục lưu trữ: 
ALTER PROC spName 
[ Danh sách tham số ] 
[With Recompile | Encryption | Recompile, Encryption] 
AS 
CẢM ƠN 
19