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

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.
Nội dung trích xuất từ tài liệu:
Bài giảng SQL server 2005: Chương 7 - Phạm Mạnh Cương CHƯƠNGVIISTORED PROCEDURE Giảngviên:PhạmMạnhCương 1 IKháiniệm:• Là một chương trình con được xây dựng bởi nhữngphá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ìnhkhác.• Stored Procedure thường dùng thực hiện truy vấn haycập nhật dữ liệu theo yêu cầu của người dùng. 2IITạoStoredProcedure 1. Bằng Lệnh CREATE PROCEDURE 2. Bằng công cụ quản lý Management Studio 31BằngLệnhCREATEPROCEDURE: Create Proc [] [With Recompile | Encryption | Recompile , Encryption] As [Begin] [End] 4Vídụ:TạothủtụcliệtkêsảnphẩmtồnkhotrongmộtthángCreate Proc spTonKho(@Thang int = 8, @Nam int = 2005)AsBeginSelect * From TonKhoWhere Thang = @Thang And Nam = @NamEnd 5Vídụ:TạothủtụctrảvềDoanhthucủamộtnămCreate Proc spDTNam (@Nam Smallint, @DoanhThu Float Output)ASBegin Select @DoanhThu=Sum(SL*DGBan) From HoaDon hd Inner Join CTHD ct On hd.SoHD=ct.SoHD Where Year(NgayHD) = @NamEnd 61.1Khaibáothamsốhìnhthứccủathủ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ậ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ó. 71.2ThựchiệnStoredProcedurescóThamsố• 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 81.3Nhậngiátrịtừthamsố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 OutputCreate 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 @DTIIICácPhátBiểuĐiềuKhiể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 101Biếncụcbộ: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à NULLb) 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. 11Vídụ:Tạothủtụcliệtkêdanhsáchmặthàngcóđơngiácaonhấtvàthấpnhấ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 122PhátbiểuIf..Else:IF CreateProcspTonKho(@Thangint, @Namint)BEGIN As If@Thang12END Begin PrintThangsaiELSE EndBEGIN Else Begin Select*FromTonKhoEND WhereThang=@Thang AndNam=@Nam End 133PhátbiểuWHILE: WHIL ...