HỆ QUẢN TRỊ CSDL SQL SERVER - Chương 7: STORED PROCEDURE

Chia sẻ: Nguyễn Mạnh Cường | Ngày: | Loại File: PDF | Số trang:28

Thêm vào BST

Báo xấu

367
lượt xem 105
download

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

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.

Chủ đề:

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

Lưu

Nội dung Text: HỆ QUẢN TRỊ CSDL SQL SERVER - Chương 7: STORED PROCEDURE

HỆ QUẢN TRỊ CSDL SQL SERVER Chương 7: STORED PROCEDURE Giáo viên: Tạ Thúc Nhu Khoa CNTT trường ĐH Lạc Hồng
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 Server 2
II-Tạo Stored Procedure 1. Bằng Lệnh CREATE PROCEDURE 2. Bằng công cụ quản lý Management Studio SQL Server 3
1- Bằng Lệnh CREATE PROCEDURE: Create Proc [] [With Recompile | Encryption | Recompile , Encryption] As [Begin] [End] SQL Server 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 SQL Server 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 SQL Server 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ó. SQL Server 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 SQL Server 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 Print @DT SQL Server 9
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 Server 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. SQL Server 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 SQL Server 12
2- Phát biểu If .. Else: IF Create Proc spTonKho(@Thang int, @Nam int) BEGIN As If @Thang12 END Begin ELSE Print 'Thang sai' BEGIN End Else END Begin Select * From TonKho Where Thang = @Thang And Nam = @Nam End SQL Server 13
Ví dụ: Tạo thủ tục truy vấn tồn kho theo tháng và năm. Create Proc spTonKho (@TN Set @m = Left(@TN, @i-1 ) Varchar(7)) Set @y = Right(@TN, Len(@TN)- @i) As If (@m 12) Declare @i TinyInt, @m TinyInt Begin Declare @y SmallInt Print 'Thang Sai' Set @TN = Ltrim(RTrim(@TN)) Return 2 Set @i = CharIndex('/', @TN) End If (@i = 0) SELECT * FROM TonKho Begin WHERE Thang = @m Print 'Khong co dau / ' And Nam = @y Return 1 Return 0 End SQL Server 14
3- Phát biểu WHILE : Ví dụ: Thủ tục tính tổng S = 1 + 3 + … + (2N-1) WHILE BEGIN Create Proc spTongLe ( @N int ) AS [CONTINUE] DECLARE @I int, @S int [BREAK] SET @I = 1 END SET @S = 0 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) SQL Server 16
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 Server 17
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 SQL Server 18
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 Server 19
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. SQL Server 20