Bài giảng Stored Procedure

1Chương 7: STORED PROCEDURE Giáo viên: Tạ Thúc Nhu Khoa CNTT trường ĐH Lạc Hồng HỆ QUẢN TRỊ CSDL SQL SERVER SQL Server2 I- Khái niệm : • 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. SQL Server3 II-Tạo Stored Procedure 1. Bằng Lệnh CREATE PROCEDURE 2. Bằng công cụ quản lý Management Studio SQL Server4 1- Bằng Lệnh CREATE PROCEDURE: Create Proc [] [With Recompile | Encryption | Recompile , Encryption] As [Begin] [End] 2SQL Server5 Ví dụ: Tạo thủ tục liệt kê sản phẩm tồn kho trong một tháng Create Proc spTonKho(@Thang int = 8, @Nam int = 2005) As Begin Select * From TonKho Where Thang = @Thang And Nam = @Nam End SQL Server6 Ví dụ: Tạo thủ tục trả về Doanh thu của một năm 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 SQL Server7 1.1 Khai báo tham số hình thức của thủ tục: 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ó. SQL Server8 1.2 Thực hiện Stored Procedures có Tham số • 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 3SQL Server9 1.3 Nhận giá trị từ tham số Output • 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 SQL Server10 III- Các Phát Biểu Điều Khiển: 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 6. Phát biểu : Exec(@string_var | [N]'string_expression') SQL Server11 1- Biến cục bộ : 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 @VarName = Ví dụ: Declare @Ngay DateTime set @Ngay ='2005/5/15' print @Ngay • Cách 2: SELECT @VarName = [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. SQL Server12 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. 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 4SQL Server13 2- Phát biểu If .. Else: Create Proc spTonKho(@Thang int, @Nam int) As If @Thang12 Begin Print 'Thang sai' End Else Begin Select * From TonKho Where Thang = @Thang And Nam = @Nam End IF BEGIN END ELSE BEGIN END SQL Server14 Ví dụ: Tạo thủ tục truy vấn tồn kho theo tháng và năm. Set @m = Left(@TN, @i-1 ) Set @y = Right(@TN, Len(@TN)- @i) If (@m 12) Begin Print 'Thang Sai' Return 2 End SELECT * FROM TonKho WHERE Thang = @m And Nam = @y Return 0 Create Proc spTonKho (@TN Varchar(7)) As Declare @i TinyInt, @m TinyInt Declare @y SmallInt Set @TN = Ltrim(RTrim(@TN)) Set @i = CharIndex('/', @TN) If (@i = 0) Begin Print 'Khong co dau / ' Return 1 End SQL Server15 3- Phát biểu WHILE : 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 WHILE BEGIN [CONTINUE] [BREAK] END SQL Server16 4- Phát biểu RETURN [Biểu thức số nguyên]: • 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) 5SQL Server17 Ví dụ: Thủ tục Thêm hóa đơn mới 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 SQL Server18 5- Phát biểu: GOTO --Thuc hien thu tuc Declare @KQ int Exec @KQ = spTongLe 9 Print @kq 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 SQL Server19 6-Phát biểu : Exec(@string_var | [N]'string_expression') Dùng thực hiện phát biểu T-SQL lưu trong biến chuỗi hoặc được tạo bởi một biểu thức chuỗi. Ví dụ: Tạo thủ tục truy vấn một Table, với tên table là tham số Input. Create Proc spMoBang (@TenBang VarChar(30)) Ví dụ: Tạo thủ tục liệt kê N mặt hàng có đơn giá thấp nhất hay cao nhất; chọn thêm hay không chọn thêm các mặt hàng khác có cùng đơn giá với mặt hàng thứ N. Có 3 tham số Input: số nguyên dương N, và 2 cờ hiệu 0 hay 1 cho biết chọn đơn giá thấp nhất hay cao nhất; chọn thêm hay không chọn thêm. SQL Server20 IV- Giao Tác (Transaction) • Transaction là một gói lệnh dùng đảm bảo các lệnh cập nhật dữ liệu trong gói đều được xử lý một cách trọn vẹn. • Nếu có một lệnh nào đó trong Transaction bị lỗi thì sẽ tự động bãi bỏ các lệnh trong Transaction và phục hồi lại toàn bộ dữ liệu đã bị thay đổi bởi các lệnh trong Transaction. 6SQL Server21 1- Các phát biểu đóng gói một Transaction a) Bắt đầu Transaction: BEGIN TRAN[SACTION] [transaction_name] • Nên đặt tên giao tác khi trong giao tác có lồng giao tác khác b) Kết thúc Transaction: • Không phục hồi dữ liệu đã thay đổi: COMMIT TRAN [transaction_name] • Phục hồi dữ liệu đã thay đổi trong Transaction: ROLLBACK TRAN [transaction_name] SQL Server22 Ví dụ: Xây dựng thủ tục dùng: • Thêm một chi tiết hóa đơn gồm số hóa đơn, mã hàng, số lượng, đơn giá bán vào table CTHD • Nếu lệnh chèn bị lỗi thì kết thúc giao tác, trả về giá trị 1 • Ngược lại thì giảm số tồn của mặt hàng đã ghi trong chi tiết hóa đơn đó. • Nếu sau khi giảm mà số tồn < 0 thì bãi bỏ lệnh chèn chi tiết hóa đơn và phục hồi lại số tồn đã thay đổi. SQL Server23 2- Phân đoạn nhóm lệnh trong Transaction: Được sử dụng khi muốn phân chia nhóm lệnh cần bãi bỏ trong Transaction • Đánh dấu đầu phân đoạn: SAVE TRAN • Kết thúc Transaction và phục hồi dữ liệu đã thay đổi bởi nhóm lệnh: ROLLBACK TRAN SQL Server24 Ví dụ: Xây dựng thủ tục dùng: • Thêm một hóa đơn mới gồm số hóa đơn, ngày lập, mã số nhân viên lập hóa đơn và một chi tiết của hóa đơn đó gồm mã hàng, số lượng, đơn giá bán. • Nếu lệnh thêm hóa đơn bị lỗi thì kết thúc giao tác và trả về giá trị 1 • Ngược lại thì thêm chi tiết hóa đơn vào table CTHD đồng thời giảm số tồn của mặt hàng. • Nếu lệnh thêm chi tiết hóa đơn bị lỗi thì kết thúc giao tác và chỉ bỏ lệnh chèn chi tiết hóa đơn và trả về giá trị lỗi là 2 • Nếu sau khi giảm mà số tồn < 0 thì chỉ bỏ lệnh chèn chi tiết hóa đơn và phục hồi lại số tồn đã thay đổi. 7SQL Server25 V- Kiểm soát lỗi thực hiện: • Cú pháp: BEGIN TRY END TRY BEGIN CATCH END CATCH • Khi có lỗi thực hiện một lệnh trong khối TRY thì chương trình sẽ chuyển sang thực hiện các lệnh trong khối CATCH. • Sử dụng các hàm Error… để xuất thông báo lỗi: – Error_Number(): Mã lỗi – Error_Message(): câu thông báo lỗi SQL Server26 Ví dụ sử dung Try Catch Xây dựng thủ tục dùng thêm một hóa đơn mới gồm số hóa đơn, ngày lập, mã số nhân viên lập hóa đơn và một chi tiết của hóa đơn đó gồm mã hàng, số lượng, đơn giá bán. Nếu một trong 2 lệnh chèn bị lỗi thì hủy bỏ cả 2, trả về mã lỗi và thông báo lỗi. SQL Server27 VI- Sửa Xóa Thủ Tục: 1. Đổi Tên TTLT: 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 SQL Server28 VII- XEM THÔNG TIN THỦ TỤC: 1. Table Sys.Procedures: chứa các thủ tục trong CSDL hiện hành Select * From Sys.Procedures 2. Table Sys. Parameters: chứa các tham số của thủ tục Select * From Sys.Parameters Where Object_ID=Object_ID('spTonKho‘) 3. Xem nội dung thủ tục: SP_HELPTEXT ‘spName’ Ví dụ: IF EXISTS (SELECT * FROM Sys.Procudures WHERE Name = 'spTonKho' ) DROP PROC spTonKho