Bài giảng Cơ sở dữ liệu - Bài 6: Stored Procedure

Nội dung text: Bài giảng Cơ sở dữ liệu - Bài 6: Stored Procedure

1. STORED PROCEDURE
2. Nội dung chi tiết Giới thiệu Biến Lệnh IF ELSE Lệnh CASE Lệnh While Một số lệnh thông dụng Tạo SP Hiệu chỉnh SP Xóa SP Xem thông tin về SP Ví dụ 2
3. Giới thiệu Store procedure (SP) là một tập các câu lệnh SQL (chương trình) được biên dịch và lưu trữ sẵn trên SQL Server SP cho phép - Chứa nhiều câu lệnh, có thể gọi các SP khác - Nhận các tham số đầu vào, đầu ra - Trả về trạng thái thực hiện (thành công hay thất bại) và nguyên nhân Một số loại SP - System stored procedure: SP hệ thống, có tên bắt đầu bằng sp_ - User defined stored procedure: SP do người dùng định nghĩa - Temporary stored procedure: SP tạm thời, bắt đầu bằng # 3
4. Giới thiệu (tt) Quá trình tạo SP - Phân tích cú pháp:  Nếu có lỗi cú pháp thì thông báo lỗi và không tạo SP  Ngược lại tạo SP, tên SP lưu ở sysobjects, văn bản lệnh lưu ở syscomments Thi hành SP (lần đầu hoặc biên dịch lại) - Đọc văn bản lệnh ở syscomments - Phân giải tên: liên kết các đối tượng mà SP tham khảo đến - Tối ưu hóa: tạo kế hoạch thi hành nhanh nhất dựa vào trạng thái của CSDL và cấu trúc câu lệnh - Biên dịch: tạo mã thi hành cho kế hoạch đã được tối ưu hóa và đặt trong vùng procedure cache - Thi hành SP 4
5. Giới thiệu (tt) Ưu điểm khi sử dụng SP - Cho phép các ứng dụng khác nhau dùng chung xử lý, đảm bảo tính nhất quán trong truy xuất và xử lý dữ liệu - Nhanh, giảm lưu lượng mạng 5
6. Nội dung chi tiết Giới thiệu Biến Lệnh IF ELSE Lệnh CASE Lệnh While Một số lệnh thông dụng Tạo SP Hiệu chỉnh SP Xóa SP Xem thông tin về SP Ví dụ 6
7. Biến Biến cục bộ: là một đối tượng cụ thể lưu giữ một giá trị dữ liệu đơn của một kiểu xác định. - Tên của biến cục bộ được bắt đầu bởi ký hiệu @ - Biến cục bộ được khai báo bằng lệnh DECLARE - Ví dụ DECLARE @sl int, @dbname nvarchar(128) - Biến cục bộ không thể có kiểu dữ liệu là text, ntext hoặc image. Biến toàn cục (hệ thống): do SQL tạo ra - Tên bắt đầu bởi ký hiệu @@ - Ví dụ: @@rowcount, @@error 7
8. Nội dung chi tiết Giới thiệu Biến Lệnh IF ELSE Lệnh CASE Lệnh While Một số lệnh thông dụng Tạo SP Hiệu chỉnh SP Xóa SP Xem thông tin về SP Ví dụ 8
9. Lệnh IF ELSE Cú pháp IF END > [ELSE END>] Ví dụ SELECT * FROM NHANVIEN IF @@rowcount > 0 PRINT N'Có dữ liệu‘ ELSE PRINT N'Bảng chưa có dữ liệu' 9
10. Nội dung chi tiết Giới thiệu Biến Lệnh IF ELSE Lệnh CASE Lệnh While Một số lệnh thông dụng Tạo SP Hiệu chỉnh SP Xóa SP Xem thông tin về SP Ví dụ 10
11. Lệnh CASE CASE Cú pháp WHEN THEN [WHEN THEN ] [ELSE ] END Ví dụ SELECT Thu = CASE datepart(w,getdate()) WHEN 1 THEN 'Chu nhat’ WHEN 2 THEN 'Thu hai' WHEN 3 THEN 'Thu ba’ WHEN 4 THEN 'Thu tu' WHEN 5 THEN 'Thu nam’ WHEN 6 THEN 'Thu sau' WHEN 7 THEN 'Thu bay' END 11
12. Nội dung chi tiết Giới thiệu Biến Lệnh IF ELSE Lệnh CASE Lệnh While Một số lệnh thông dụng Tạo SP Hiệu chỉnh SP Xóa SP Xem thông tin về SP Ví dụ 12
13. Lệnh WHILE Cú pháp WHILE END > [BREAK] END >[CONTINUE] END > Ví dụ DECLARE @Dem int Cách 2: SET @Dem = 0 DECLARE @Dem int WHILE 1=1 SET @Dem = 0 BEGIN WHILE (@Dem 20) BREAK IF (@Dem%2=0) Print @Dem IF @Dem%2!=0 CONTINUE SET @Dem = @Dem + 1 ELSE Print @Dem END END 13
14. Nội dung chi tiết Giới thiệu Biến Lệnh IF ELSE Lệnh CASE Lệnh While Một số lệnh thông dụng Tạo SP Hiệu chỉnh SP Xóa SP Xem thông tin về SP Ví dụ 14
15. Một số lệnh thông dụng RETURN [Bieu_thuc_nguyen] - Dùng để thoát khỏi SP - Ví dụ RETURN RETURN 0 RETURN (1) PRINT - Dùng để xuất kết quả, giá trị ra màn hình - Ví dụ PRINT ‘ABC’ PRINT @Dem PRINT GETDATE() 15
16. Một số lệnh thông dụng (tt) BREAK - Dùng để thoát khỏi vòng lặp CONTINUE - Dùng để trở về đầu vòng lặp 16
17. Nội dung chi tiết Giới thiệu Biến Lệnh IF ELSE Lệnh CASE Lệnh While Một số lệnh thông dụng Tạo SP Hiệu chỉnh SP Xóa SP Xem thông tin về SP Ví dụ 17
18. Tạo SP Sử dụng lệnh CREATE PROCEDURE để tạo SP, SP được lưu ở DB hiện hành Nếu trong SP có tạo một bảng tạm, thì bảng tạm chỉ tồn tại khi thực thi SP, bảng tạm sẽ tự động bị xóa khi thi hành xong SP (bảng tạm có tên bắt đầu bằng ký hiệu #, ví dụ: #NHANVIEN) Trong SP không được chứa các câu lệnh: CREATE PROCEDURE, CREATE RULE, CREATE VIEW, CREATE TRIGGER Để thi hành lệnh CREATE PROCEDURE, người dùng phải là thành viên của một trong các role: sysadmin, db_owner, db_ddladmin hoặc được cấp quyền CREATE PROCEDURE 18
19. Tạo SP (tt) Cú pháp CREATE PROC [EDURE] [ @ [ = ] [ OUTPUT ] ] [ , n ] [ WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ] AS 19
20. Tạo SP (tt) Giải thích - Ten_Procedure: tên procedure, các SP tạm cục bộ có ký hiệu # trước tên của SP - @ : tên tham số của procedure - : kiểu dữ liệu của tham số - : giá trị mặc định của tham số - OUTPUT: cho phép tham số nhận giá trị trả về - RECOMPILE: nếu có thêm tùy chọn này thì mỗi lần thi hành SQL Server sẽ biên dịch lại SP và mã của SP không được lưu vào vùng đệm của thủ tục - ENCRYPTION: nếu có thêm tùy chọn này thì văn bản lệnh được mã hóa và lưu trong syscomments 20
21. Tạo SP (tt) Ví dụ CREATE PROC DS_NHANVIEN AS SELECT * FROM nhanvien Để thực thi SP EXEC[UTE] [Danh_sach_tham_so] - Ví dụ EXEC DS_NHANVIEN 21
22. Nội dung chi tiết Giới thiệu Biến Lệnh IF ELSE Lệnh CASE Lệnh While Một số lệnh thông dụng Tạo SP Hiệu chỉnh SP Xóa SP Xem thông tin về SP Ví dụ 22
23. Hiệu chỉnh SP Cú pháp ALTER PROC [EDURE] [ @ [ = ] [ OUTPUT ] ] [ , n ] [ WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ] AS Ví dụ ALTER PROC DS_NHANVIEN AS SELECT * FROM nhanvien WHERE PHAI= ‘Nam’ 23
24. Nội dung chi tiết Giới thiệu Biến Lệnh IF ELSE Lệnh CASE Lệnh While Một số lệnh thông dụng Tạo SP Hiệu chỉnh SP Xóa SP Xem thông tin về SP Ví dụ 24
25. Xóa SP Cú pháp DROP PROC [EDURE] Ví dụ DROP PROC DS_NHANVIEN 25
26. Nội dung chi tiết Giới thiệu Biến Lệnh IF ELSE Lệnh CASE Lệnh While Một số lệnh thông dụng Tạo SP Hiệu chỉnh SP Xóa SP Xem thông tin về SP Ví dụ 26
27. Xem thông tin về SP Stored procedure Thông tin Danh sách các tham số cùng sp_help kiểu dữ liệu Văn bản lệnh của SP trong sp_helptext trường hợp không mã hóa Liệt kê các đối tượng mà SP sp_depends tham khảo đến và các đối tượng tham khảo đến SP Danh sách các SP trong DB sp_stored_procedures hiện hành 27
28. Nội dung chi tiết Giới thiệu Biến Lệnh IF ELSE Lệnh CASE Lệnh While Một số lệnh thông dụng Tạo SP Hiệu chỉnh SP Xóa SP Xem thông tin về SP Ví dụ 28
29. Ví dụ 1 – có tham số vào, default Xem danh sách nhân viên theo phòng CREATE PROC DSNV_THEOPHONG @Phong int = 1 AS SELECT * FROM NHANVIEN WHERE PHG = @Phong Thực thi SP - Cách 1:  EXEC DSNV_THEOPHONG 4 - Cách 2:  DECLARE @P int  Set @P = 5  EXEC DSNV_THEOPHONG @P 29
30. Ví dụ 2 – có tham số vào, ra Xóa thân nhân theo MANV CREATE PROC XOA_THANNHAN_THEOMANV @MANV nvarchar(20), @SoNVXoa int OUTPUT AS DELETE THANNHAN WHERE MA_NVIEN = @MaNV SET @SoNVXoa = @@rowcount Thực thi SP - DECLARE @SoNVXoa int - EXEC XOA_THANNHAN_NHANVIEN ‘123’, @SoNVXoa OUTPUT - PRINT 'So mau tin bi xoa:' + str(@SoNVXoa,3) 30
31. Ví dụ 3 – có recompile , encryption Tăng lương cho nhân viên theo phòng lên @Tyle lần CREATE PROC TANGLUONG_NHANVIEN @Phong int, @Tyle Decimal(3,1), @So_NV_Tang int OUTPUT WITH RECOMPILE, ENCRYPTION AS UPDATE NHANVIEN SET LUONG = LUONG * @Tyle WHERE PHG = @Phong SET @So_NV_Tang = @@rowcount 31
32. Ví dụ 4 – có chặn lỗi Thêm phòng ban CREATE PROC THEM_PHONGBAN @TENPHG NVARCHAR(40), @MAPHG INT, @TRPHG NVARCHAR(20), @NG_NHANCHUC SMALLDATETIME, @Loi int OUTPUT AS BEGIN TRY INSERT PHONGBAN VALUES (@TENPHG, @MAPHG, @TRPHG, @NG_NHANCHUC) END TRY BEGIN CATCH SET @Loi = @@error RAISERROR('Loi them du lieu',10,1) RETURN END CATCH SET @Loi = @@error 32