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

Chia sẻ: Bình Yên | Ngày: | Loại File: PPT | Số trang:19

Thêm vào BST

Báo xấu

76
lượt xem 8
download

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

Bài giảng "SQL server 2005 - Chương 7: Stored procedure" cung cấp cho người học các khái niệm về Stored procedure, tạo Stored Procedure bằng lệnh Create Procedure, các phát biểu điều khiển. Mời các bạn cùng tham khảo nội dung chi tiết.

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 SQL server 2005: 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