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

CHƯƠNG VIISTORED PROCEDUREGiảng viên: Phạm Mạnh Cương1I- 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 Procedure31. Bằng Lệnh CREATE PROCEDURE2. Bằng công cụ quản lý Management Studio1- Bằng Lệnh CREATE PROCEDURE:4Create 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 khotrong một tháng5Create Proc spTonKho(@Thang int = 8, @Nam int = 2005)AsBeginSelect * From TonKhoWhere Thang = @Thang And Nam = @NamEndVí dụ: Tạo thủ tục trả về Doanh thu của một năm6Create Proc spDTNam (@Nam Smallint, @DoanhThu Float Output)ASBeginSelect @DoanhThu=Sum(SL*DGBan)From HoaDon hd Inner Join CTHD ct On hd.SoHD=ct.SoHDWhere Year(NgayHD) = @NamEnd1.1 Khai báo tham số hình thức của thủ tục:7Cú 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ậngiá 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ềngiá 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 = 20061.3 Nhận giá trị từ tham số Output9• 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 OutputCreate Proc spDTNam(@Nam int, @DoanhThu Float Output)Ví dụ: Thực hiện thủ tục spDTNamDeclare @DT FloatExec spDTNam 2005, @DT OutputPrint @DTHayDeclare @DT FloatExec spDTNam @DoanhThu = @DT Output, @Nam = 2005Print @DTIII- Các Phát Biểu Điều Khiển:101. Biến cục bộ2. Phát biểu If .. Else3. Phát biểu WHILE4. Phát biểu: RETURN [Biểu thức số nguyên]5. Phát biểu: GOTO 1- Biến cục bộ :11a) 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óilệnh đã khai báo biến.• Giá trị ban đầu của biến là NULLb) Gán giá trị cho biến cục bộ:• Cách 1: SET @VarName = Ví dụ: Declare @Ngay DateTimeset @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òngcuố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.12CREATE PROCEDURE spMatHangCaoNhatThapNhatASBeginDeclare @Dgmax float, @Dgmin floatSELECT @Dgmax = Max(DonGia), @Dgmin = Min(DonGia)FROM MatHangSELECT * FROM MatHangWHERE DonGia = @Dgmax OR DonGia = @DgminORDER BY DonGia DESCEnd2- Phát biểu If .. Else:IF BEGIN ENDELSEBEGIN ENDCreate Proc spTonKho(@Thang int, @Nam int)AsIf @Thang12Begin Print 'Thang sai'EndElseBegin Select * From TonKho Where Thang = @Thang And Nam = @NamEnd133- Phát biểu WHILE :14WHILE BEGIN [CONTINUE][BREAK]ENDVí dụ: Thủ tục tính tổng S = 1 + 3 + + (2N-1)Create Proc spTongLe ( @N int )ASDECLARE @I int, @S intSET @I = 1SET @S = 0WHILE @I [,n]Ví dụ: Gọi thực hiện thủ tục spTongLe( @N int )declare @s intExec @s = spTongLe 9print 'Tong la = ' + Cast(@s As Varchar)Ví dụ: Thủ tục Thêm hóa đơn mới16Create Proc spThemHoaDon(@SoHD int, @MaNV int, @NgayHD DateTime) AsIf Exists(Select * From HoaDon Where SoHD = @SoHD)BeginPrint 'Trung so hoa don'Return 1EndIf Not Exists(Select * From NhanVien Where MaNV = @MaNV)BeginPrint 'Sai Ma nhan vien 'Return 2EndIf @NgayHD > GetDate()BeginPrint 'Ngay lap hoa don lon hon ngay hien hanh 'Return 3EndInsert HoaDon (SoHD, MaNV, NgayHD) Values(@SoHD, @MaNV, @NgayHD)if @@Error 0BeginPrint 'Loi them hoa don moi'Return 4EndReturn 05- Phát biểu: GOTO 17Create Proc spTongLe ( @N int )ASDECLARE @I int, @S int SET @I = 1 SET @S = 0Lap:IF (@I > 2*@N -1) GoTo KetQua Set @S = @S + @I Set @I = @I + 2GoTo LapKetQua:Return @S--Thuc hien thu tucDeclare @KQ intExec @KQ = spTongLe 9Print @kqIV- Sửa Xóa Thủ Tục:181. Đổ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]ASCẢM ƠN19