Bài giảng SQL - Chương 6: Stored Procedure

STORED PROCEDURENội dung chi tiếtGiới thiệuBiếnLệnh IF ELSELệnh CASELệnh WhileMột số lệnh thông dụngTạo SPHiệu chỉnh SPXóa SPXem thông tin về SPVí dụ2Giới thiệuStore 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 ServerSP cho phépChứa nhiều câu lệnh, có thể gọi các SP khácNhận các tham số đầu vào, đầu raTrả về trạng thái thực hiện (thành công hay thất bại) và nguyên nhânMộ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ĩaTemporary stored procedure: SP tạm thời, bắt đầu bằng # 3Giới thiệu (tt)Quá trình tạo SPPhâ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 SPNgược lại tạo SP, tên SP lưu ở sysobjects, văn bản lệnh lưu ở syscommentsThi hành SP (lần đầu hoặc biên dịch lại)Đọc văn bản lệnh ở syscommentsPhân giải tên: liên kết các đối tượng mà SP tham khảo đếnTố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ệnhBiê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 cacheThi hành SP 4Giới thiệu (tt)Ưu điểm khi sử dụng SPCho 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ệuNhanh, giảm lưu lượng mạng5Nội dung chi tiếtGiới thiệuBiếnLệnh IF ELSELệnh CASELệnh WhileMột số lệnh thông dụngTạo SPHiệu chỉnh SPXóa SPXem thông tin về SPVí dụ6BiếnBiế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 DECLAREVí 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 raTên bắt đầu bởi ký hiệu @@ Ví dụ: @@rowcount, @@error 7Nội dung chi tiếtGiới thiệuBiếnLệnh IF ELSELệnh CASELệnh WhileMột số lệnh thông dụngTạo SPHiệu chỉnh SPXóa SPXem thông tin về SPVí dụ8Lệnh IF ELSECú phápVí dụIF END > [ELSE  END>]SELECT * FROM NHANVIENIF @@rowcount > 0 PRINT N'Có dữ liệu‘ELSE PRINT N'Bảng chưa có dữ liệu'9Nội dung chi tiếtGiới thiệuBiếnLệnh IF ELSELệnh CASELệnh WhileMột số lệnh thông dụngTạo SPHiệu chỉnh SPXóa SPXem thông tin về SPVí dụ10Lệnh CASECú phápVí dụCASE WHEN THEN [WHEN THEN ] [ELSE ] ENDSELECT 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'END11Nội dung chi tiếtGiới thiệuBiếnLệnh IF ELSELệnh CASELệnh WhileMột số lệnh thông dụngTạo SPHiệu chỉnh SPXóa SPXem thông tin về SPVí dụ12Lệnh WHILECú phápVí dụWHILE END > [BREAK] END >[CONTINUE] END >DECLARE @Dem int SET @Dem = 0WHILE 1=1BEGIN SET @Dem = @Dem + 1 IF (@dem > 20) BREAK IF @Dem%2!=0 CONTINUE ELSE Print @DemEND Cách 2:DECLARE @Dem int SET @Dem = 0WHILE (@Dem Dùng để xuất kết quả, giá trị ra màn hìnhVí dụ PRINT ‘ABC’ PRINT @Dem PRINT GETDATE() 15Một số lệnh thông dụng (tt)BREAKDùng để thoát khỏi vòng lặpCONTINUEDùng để trở về đầu vòng lặp16Nội dung chi tiếtGiới thiệuBiếnLệnh IF ELSELệnh CASELệnh WhileMột số lệnh thông dụngTạo SPHiệu chỉnh SPXóa SPXem thông tin về SPVí dụ17Tạo SPSử dụng lệnh CREATE PROCEDURE để tạo SP, SP được lưu ở DB hiện hànhNế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 18Tạo SP (tt)Cú phápCREATE PROC [EDURE] [ @ [ = ] [ OUTPUT ] ][ ,...n ] [ WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ] AS 19Tạo SP (tt)Giải thíchTen_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ụcENCRYPTION: 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 20Tạo SP (tt)Ví dụĐể thực thi SPVí dụ EXEC DS_NHANVIENCREATE PROC DS_NHANVIENASSELECT * FROM nhanvienEXEC[UTE] [Danh_sach_tham_so]21Nội dung chi tiếtGiới thiệuBiếnLệnh IF ELSELệnh CASELệnh WhileMột số lệnh thông dụngTạo SPHiệu chỉnh SPXóa SPXem thông tin về SPVí dụ22Hiệu chỉnh SPCú phápVí dụALTER PROC [EDURE] [ @ [ = ] [ OUTPUT ] ][ ,...n ] [ WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ] AS ALTER PROC DS_NHANVIENASSELECT * FROM nhanvien WHERE PHAI= ‘Nam’23Nội dung chi tiếtGiới thiệuBiếnLệnh IF ELSELệnh CASELệnh WhileMột số lệnh thông dụngTạo SPHiệu chỉnh SPXóa SPXem thông tin về SPVí dụ24Xóa SPCú phápVí dụDROP PROC [EDURE] DROP PROC DS_NHANVIEN25Nội dung chi tiếtGiới thiệuBiếnLệnh IF ELSELệnh CASELệnh WhileMột số lệnh thông dụngTạo SPHiệu chỉnh SPXóa SPXem thông tin về SPVí dụ26Xem thông tin về SPStored procedureThông tinsp_help Danh sách các tham số cùng kiểu dữ liệusp_helptext Văn bản lệnh của SP trong trường hợp không mã hóasp_depends Liệt kê các đối tượng mà SP tham khảo đến và các đối tượng tham khảo đến SPsp_stored_proceduresDanh sách các SP trong DB hiện hành27Nội dung chi tiếtGiới thiệuBiếnLệnh IF ELSELệnh CASELệnh WhileMột số lệnh thông dụngTạo SPHiệu chỉnh SPXóa SPXem thông tin về SPVí dụ28Ví dụ 1 – có tham số vào, defaultXem danh sách nhân viên theo phòngThực thi SPCách 1: EXEC DSNV_THEOPHONG 4Cách 2:DECLARE @P intSet @P = 5EXEC DSNV_THEOPHONG @PCREATE PROC DSNV_THEOPHONG@Phong int = 1AS SELECT * FROM NHANVIENWHERE PHG = @Phong29Ví dụ 2 – có tham số vào, raXóa thân nhân theo MANVThực thi SP DECLARE @SoNVXoa intEXEC XOA_THANNHAN_NHANVIEN ‘123’, @SoNVXoa OUTPUTPRINT 'So mau tin bi xoa:' + str(@SoNVXoa,3)CREATE PROC XOA_THANNHAN_THEOMANV@MANV nvarchar(20), @SoNVXoa int OUTPUTASDELETE THANNHANWHERE MA_NVIEN = @MaNVSET @SoNVXoa = @@rowcount30Ví dụ 3 – có recompile , encryptionTăng lương cho nhân viên theo phòng lên @Tyle lầnCREATE PROC TANGLUONG_NHANVIEN@Phong int, @Tyle Decimal(3,1), @So_NV_Tang int OUTPUTWITH RECOMPILE, ENCRYPTION ASUPDATE NHANVIEN SET LUONG = LUONG * @TyleWHERE PHG = @PhongSET @So_NV_Tang = @@rowcount31Ví dụ 4 – có chặn lỗiThêm phòng banCREATE PROC THEM_PHONGBAN @TENPHG NVARCHAR(40), @MAPHG INT, @TRPHG NVARCHAR(20),@NG_NHANCHUC SMALLDATETIME, @Loi int OUTPUT ASBEGIN TRY INSERT PHONGBAN VALUES (@TENPHG, @MAPHG, @TRPHG, @NG_NHANCHUC)END TRYBEGIN CATCH SET @Loi = @@error RAISERROR('Loi them du lieu',10,1) RETURNEND CATCHSET @Loi = @@error32