Đối với một lập trình viên đã có nhiều kinh nghiệm thường sẽ sử dụng Store procedure để thực hiện thao tác dữ liệu thay vì viết câu lênh trực tiếp (dạng Text). Trong bài này mình sẽ giới thiệu về Store Procedure – một phần khá quan trong trong lập trình với cơ sở dữ liệu
Stored Procedure (Thủ tục lưu trữ) là một đối tượng trong cơ sở dữ liệu bao gồm một tập nhiều câu lệnh SQL được nhóm lại với nhau thành một nhóm với những khả năng sau:
Tạo thủ tục lưu trữ
Thủ tục lưu trữ được tạo bởi câu lệnh CREATE PROCEDURE với cú pháp như sau:
CREATE PROCEDURE Tên_thủ_tục [(Danh_sách_tham_số)] [WITH RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION] AS Các_câu_lệnh_của_thủ_tục
Trong đó: - Tên_thủ_tục: Tên của thủ tục cần tạo. Tên phải tuân theo qui tắc định danh và không được vượt quá 128 ký tự.
- Danh_sách_tham_số: Các tham số của thủ tục được khai báo ngay sau tên thủ tục và nếu thủ tục có nhiều tham số thì các khai báo phân cách nhau bởi dấu phẩy. Khai báo của mỗi một tham số tối thiểu phải bao gồm hai phần: Tên tham số được bắt đầu bởi dấu @, Kiểu dữ liệu của tham số
- RECOMPILE: Thông thường, thủ tục sẽ được phân tích, tối ưu và dịch sẵn ở lần gọi đầu tiên. Nếu tuỳ chọn WITH RECOMPILE được chỉ định, thủ tục sẽ được dịch lại mỗi khi được gọi.
- ENCRYPTION: Thủ tục sẽ được mã hoá nếu tuỳ chọn WITH ENCRYPTION được chỉ định. Nếu thủ tục đã được mã hoá, ta không thể xem được nội dung của thủ tục.
Các_câu_lệnh_của_thủ_tục: Tập hợp các câu lệnh sử dụng trong nội dung thủ tục. Các câu lệnh này có thể đặt trong cặp từ khoá BEGIN…END hoặc có thể không + Để thực thi một thủ tục ta thực hiện: Execute Ten_Store_Procedure Danh_sách_các_tham_số (Hoặc EXEC Ten_Store_Procedure Danh_sách_các_tham_số)
+ Để chỉnh sửa một Stored Procedure có sẵn: sử dụng lệnh Alter Procedure
+ Để xóa một Stored Procedure: sử dụng lệnh Drop Procedure Ten_Store_Procedure
+ Để đổi tên một Stored Procedure ta dùng lệnh: sp_rename ‘tên_sp_cũ’ ‘tên_sp_mới’
+ Để xem nội dung Stored Procedure ta dùng sp_helptext tên_sp
Ví dụ 1: Tạo Store Procedure không có tham số
Trong định nghĩa chúng ta có [(Danh_sách_tham_số)] Nhưng đôi khi Store có thể không có Danh sách các tham số. Bạn tham khảo ví dụ sau
CREATE PROCEDURE spCategories_GetCate_ViDu1 AS BEGIN SELECT c.CategoryID, c.CategoryName, c.Description FROM Categories c END
Trong ví dụ trên mình đã tạo một SP là spCategories_GetCate để truy vấn các trường CategoryID, CategoryName, Description của bảng Categories, Bạn có thể chú ý đến cách đặt tên tuy tên của SP là tùy chọn nhưng Theo mình để cho dễ kiểm soát khi Database của bạn lớn Bạn nên đặt cho mình một quy tắc đặt tên cho SP. Mình thường đặt là spTênBảng_ChứcnăngcủaSP vì khi database của bạn có thể sẽ có rất nhiều bảng và rất nhiều store nêu không theo quy tắc khi cần Alter một SP bạn sẽ khó tìm SP đó. (Trong SQL server có hỗ trợ chức năng Filter – Phải chuột vào Store Procedure/Filter/Filter setting sau đó nhập từ khóa cần Filter)
Ví dụ 2: Tạo Store Procedure có tham số đầu vào.
Trong ví dụ sau ta sẽ tạo 1 SP có tham số đầu vào và truy vấn theo điều kiện của tham số
CREATE PROCEDURE spCategories_GetCate_ViDu2 @CategoryID int AS BEGIN IF @CategoryID>0 SELECT c.CategoryID, c.CategoryName, c.Description FROM Categories c WHERE c.CategoryID=@CategoryID ELSE SELECT c.CategoryID, c.CategoryName, c.Description FROM Categories c ORDER BY c.CategoryID DESC END
Trong ví dụ trên bạn thấy có tham số đầu vào là @CategoryID khi @CategoryID =0 thì sẽ truy vấn All Còn khi @CategoryID >0 thì truy vấn theo @CategoryID
Đây cũng thêm một cách mà mình hay dùng để giảm bớt số lượng SP trong database. (Thường thì bạn sẽ phải viết 2 SP một là Select One – @CategoryID >0, 2 là Select All @CategoryID =0). Bạn thấy rằng trong SP trên mình có sử dụng cấu trúc điều khiển IF. Bạn có thể thấy là SQL cũng có thể lập trình và có thể nó sẽ mang lại nhiều lợi ích khi lập trình trên SQL. Tham khảo thêm bài viết sau: Hướng dẫn xây dựng website
Ví dụ 3: Store Procedure có tham số đầu vào – thực hiện Insert, Delete, Update.
Trong Store Procedure sau mình sẽ minh họa việc tạo một SP để thực hiện Thêm, Sửa, Xóa một bản ghi phụ thuộc vào điều kiện của tham số:
CREATE PROCEDURE spCategories_Edit_ViDu3 @Action int, @CategoryID int, @CategoryName nvarchar(50), @Description nvarchar(400), @Picture image AS BEGIN IF @Action=0 DELETE FROM Categories WHERE CategoryID=@CategoryID ELSE BEGIN IF @CategoryID=0 INSERT INTO Categories( [CategoryName], [Description], [Picture]) VALUES ( @CategoryName, @Description, @Picture ) ELSE UPDATE Categories SET CategoryName = @CategoryName, Description = @Description, Picture = @Picture WHERE CategoryID=@CategoryID END END
Trong ví dụ trên bạn thấy mình sử dụng 1 Store để thực hiện 3 hành động thêm, sửa, xóa một bản ghi trong bảng Categorys. Cũng là một cách mình muốn giới thiệu để giảm bớt số lượng SP trong cơ sở dữ liệu của bạn.
Ví dụ 4: Store Procedure có tham số đầu vào, Tham số đầu ra và trả về giá trị
CREATE PROCEDURE spCategories_Edit_ViDu4 @Action int, @CategoryID int, @CategoryName nvarchar(50), @Description nvarchar(400), @Picture image, @error nvarchar(1000) output AS BEGIN TRAN IF @Action=0 BEGIN DELETE FROM Categories WHERE CategoryID=@CategoryID IF @@ERROR<>0 BEGIN SET @error = N'Lỗi: Chưa xóa được' IF @@TRANCOUNT>0 ROLLBACK tran GOTO Error END ELSE SET @error = N'Đã xóa bản ghi' END ELSE IF @CategoryID=0 BEGIN INSERT INTO Categories( [CategoryName],[Description],[Picture]) VALUES ( @CategoryName,@Description,@Picture ) IF @@ERROR<>0 BEGIN SET @error = N'Lỗi: Lỗi khi thêm dữ liệu' IF @@TRANCOUNT>0 ROLLBACK tran GOTO Error END ELSE SET @error = N'Đã thêm mới bản ghi' END ELSE BEGIN UPDATE Categories SET [CategoryName] = @CategoryName, [Description] = @Description, [Picture] = @Picture WHERE CategoryID=@CategoryID IF @@ERROR<>0 BEGIN SET @error = N'Lỗi: Lỗi khi cập nhật dữ liệu' IF @@TRANCOUNT>0 ROLLBACK tran GOTO Error END ELSE SET @error = N'Đã cập nhật dữ liệu' END RETURN @error -- Select @error as ThongBao COMMIT TRAN Error:
Bạn để ý thấy rằng trong khi thực hiện SP trên thì ở trường hợp nào bạn cũng sẽ nhận được giá trị trả về tương ứng thông báo cho bạn biết trạng thái thực thi. Bạn cũng nên lưu ý sử dụng biên Output trong SP và dùng cách dùng ROLLBACK tran. Khi trong SP của bạn thực hiện nhiều hành động (Có thể là thực hiện Edit dữ liệu nhiều bảng khác nhau trong 1 SP khi đã thực hiện được 1 lệnh nào đó và đến lệnh tiếp theo xảy ra lỗi khi đó nếu bạn không dùng dùng ROLLBACK thì sẽ xảy ra sai dữ liệu mà lại khó tím lỗi). Tham khảo thêm bài viết:Những thói quen tốt khi viết câu lệnh SQL. Trong những bài viết sau của loạt bài hướng dẫn này mình sẽ giới thiệu cách dùng C# để thực thi một Store Procedure
Trên đây mình giới thiệu qua về Store Procedure, hy vọng qua đây bạn đã hiểu hơn về SP và biết cách tạo SP cho Database của bạn. Trong bào viết sau của loạt bài này mình sẽ giới thiệu về Function và Trigger trong SQL server. Tham khảo thêm bài viết Hướng dẫn xây dựng website :: Xây dựng cơ sở dữ liệu – TRIGGER Một số tài liệu cho bạn tham khảo:
Drupal Developer having 9+ year experience, implementation and having strong knowledge of technical specifications, workflow development. Ability to perform effectively and efficiently in team and individually. Always enthusiastic and interseted to study new technologies
Every day, millions of nodes are saved. It happens every time content is created, migrated, or updated. It's probably the most common content management task in Drupal.
Được ra đời với mục đích dành riêng cho máy tính bảng, thiết bị này còn sở hữu pin tự sạc cho thời gian sử dụng lâu hơn.
Ngày 8/11, nhà cung cấp dịch vụ khuyến mãi trực tuyến nổi tiếng thế giới Groupon đã lên tiếng xin lỗi và đề nghị hoàn trả tiền cho những khách hàng mua phải đồng hồ đeo tay rởm từ Groupon Trung Quốc.
Bình luận (0)
Add Comment