Bài giảng Microsoft SQL server: Chương 7 - Phạm Mạnh Cương

Chia sẻ: 5A4F5AFSDG 5A4F5AFSDG | Ngày: | Loại File: PPT | Số trang:19

Thêm vào BST

Báo xấu

52
lượt xem 6
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Chương 7 trang bị cho người học những hiểu biết cơ bản về Stored procedure. Chương này gồm có những nội dung chính sau: Khái niệm Stored procedure, tạo stored procedure bằng lệnh create procedure, tạo stored procedure bằng công cụ quản lý management studio.

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Bài giảng Microsoft SQL server: Chương 7 - 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: • 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. 2
IITạo Stored Procedure 1. Bằng Lệnh CREATE PROCEDURE 2. Bằng công cụ quản lý Management Studio 3
1 Bằng Lệnh CREATE PROCEDURE: Create Proc [] [With Recompile | Encryption | Recompile , Encryption] As [Begin] [End] 4
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 5
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 6
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ó. 7
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 8
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 9 Print @DT
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 10
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. 11
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 12
2 Phát biểu If .. Else: IF Create Proc spTonKho(@Thang int, @Nam int) BEGIN As If @Thang12 END Begin Print 'Thang sai' ELSE End BEGIN Else Begin Select * From TonKho END Where Thang = @Thang And Nam = @Nam End 13
3 Phát biểu WHILE : WHILE Ví dụ: Thủ tục tính tổng S = 1 + 3 + … + (2N1) Create Proc spTongLe ( @N int ) BEGIN AS DECLARE @I int, @S int [CONTINUE] SET @I = 1 SET @S = 0 [BREAK] WHILE @I
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) 15
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 16 Return 0
5 Phát biểu: GOTO Create Proc spTongLe ( @N int ) --Thuc hien thu tuc AS Declare @KQ int DECLARE @I int, @S int Exec @KQ = spTongLe 9 SET @I = 1 Print @kq SET @S = 0 Lap: IF (@I > 2*@N -1) GoTo KetQua Set @S = @S + @I Set @I = @I + 2 GoTo Lap KetQua: Return @S 17
IV Sửa Xóa Thủ Tục: 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ác phát biểu t_sql > 18
CẢM ƠN 19