Bạn đã nghe nhiều về Stored Procedure trong SQL tuy nhiên lại chưa viết cách tạo Stored Procedure SQL. Ngày hôm nay chúng ta sẽ cùng nhau tìm hiểu về cách tạo một Stored Procedure trong SQL chi tiết ngay nhé!
Cách tạo Stored Procedure trong SQL
1. Stored Procedure là gì?
Một stored procedure SQL (viết tắt là SP) là một bộ sưu tập các câu lệnh SQL và các logic lệnh SQL được biên dịch và lưu trữ trong cơ sở dữ liệu. Stored procedures trong SQL cho phép chúng ta tạo các truy vấn SQL để được lưu trữ và thực thi trên máy chủ. Stored procedures có thể cũng được lưu trữ và sử dụng lại.
Mục đích chính của Stored Procedure là ẩn các truy vấn SQL trực tiếp từ code và cải thiện hiệu suất của cơ sở dữ liệu khi thực hiện các lệnh SELECT, UPDATE, DELETE.
Bạn có thể tạo và khởi chạy Stored Procedure sử dụng Object Explorer trong SQL Server hoặc sử dụng SQL Server Management Studio.
2. Tại sao chúng ta sử dụng SET NOCOUNT ON trong một Stored Procedure?
SET NOCOUNT ON là một dòng mã được sử dụng trong SQL để không trả về giá trị cho một số lượng hàng nào đó trong khi thực hiện truy vấn. Nó có nghĩa là không được tính toán.
Và khi bạn SET NOCOUNT OFF thì các câu truy vấn sẽ ảnh hưởng đến giá trị của tất cả các hàng.
3. Có bao nhiêu kiểu Stored Procedure?
Có 2 kiểu có sẵn của Stored Procedure trong SQL Server đó là:
- Người dùng định nghĩa Stored Procedure
- Stored Procedure hệ thống
3.1. Người dùng định nghĩa Stored Procedure
Người dùng định nghĩa Stored Procedure được tạo bởi những nhà phát triển cơ sở dữ liệu hoặc những người quản trị cơ sở dữ liệu. Các SP này chứa nhiều hơn 1 câu lệnh SQL để chọn, cập nhật hoặc xóa các bản ghi từ các bản ghi cơ sở dữ liệu. Người dùng định nghĩa SP là hỗn hợp của các lệnh DDL ( Data Definition Language – Ngôn ngữ định nghĩa dữ liệu) và DML ( Ngôn ngữ thao tác dữ liệu).
Các SP người dùng định nghĩa được phân loại thêm thành 2 kiểu:
- T-SQL stored procedures: T-SQL (Transact SQL – SQL giao dịch) là các SP nhân và trả về các tham số. Các quá trình SP truy vấn như thêm, cập nhật, xóa hoặc không có tham số. Đây là một trong những cách phổ biến nhất để viết SP trong SQL Server.
- CLR stored procedures: CLR (Common Language Runtime) SPs được viết trong một CLR dựa trên ngôn ngữ lập trình như C# hoặc VB. NET và được thực thi bởi .NET Framework.
3.2. Stored Procedure hệ thống
Stored procedures hệ thống được tạo ra và thực thi bởi SQL Server cho các hoạt động quản trị máy chủ. Người phát triển thường không thể tác động được lên SP hệ thống.
3.3. Đăng nhập vào cơ sở dữ liệu SQL Server
Đăng nhập vào cơ sở dữ liệu SQL để chúng ta có thể đạt được những điều sau:
- Cách tạo một truy vấn SELECT dựa trên Stored Procedure mà trả về tất cả các bản ghi
- Cách tạo một tham số dựa trên truy vấn SELECT Stored Procedure mà trả về các bản ghi dựa trên các tham số
- Cách tạo một truy vấn INSERT dựa trên Stored Procedure
- Cách tạo một truy vấn UPDATE dựa trên Stored Procedure
- Cách tạo một truy vấn DELETE dựa trên Stored Procedure
Đăng nhập vào SQL SERVER với tên máy chủ của bạn.
Bạn hãy chuyển sang tên máy chủ của bạn từ hình trên.
Ví dụ: trên máy chủ của mình là “LAPTOP-TSLTAQL8\CHINH”.
Phần Stored Procedure nằm trong mục DataBase >> Một cơ sở dữ liệu của bạn >> Programmability >> Stored Procedures.
SP trống sẽ được tạo như hình sau:
Mẫu trống được đã tạo bởi SQL Server cho một SP sẽ trông như sau. Lệnh CREATE PROCEDURE SQL được sử dụng để tạo một SP, tiếp theo là tên của SP và tham số của nó. Vùng BEGIN và END được sử dụng để định nghĩa truy vấn cho sự hoạt động. Đây là nơi bạn sẽ viết các truy vấn SELECT, UPDATE, INSERT, hoặc DELETE.
4. Làm sao để viết bình luận trong SQL Server?
Bạn có thể bình luận trong SQL Server theo những cách sau:
- Hai dấu “ // ” để tạo bình luận trên 1 dòng
- Bắt đầu với /* …. Và kết thúc với */ cho bình luận nhiều dòng.
5. Quy ước đặt tên cho các Stored Procedure là gì?
Chúng ta phải tuân theo chuẩn quy ước đặt tên.
Với SP người dùng định nghĩa, quy tắc đặt tên được khuyến nghị là thêm một số tiền số vào SP của bạn.
- sp
- stp
- stp_
- udstp
- udstp_
Việc đặt tên theo nguyên tắc là để xác định các đối tượng. Bằng việc thêm các tiền tố này vào tên, chúng ta có thể xác định rõ rành đối tượng đó là một SP.
6. Làm sao để tạo một Stored Procedure để chọn dữ liệu từ một từ một bảng cơ sở dữ liệu sử dụng câu lệnh truy vấn SELECT?
6.1. Tạo một bảng cơ sở dữ liệu
Đầu tiên, chúng ta có thể tạo và thực thi một vài SP, chúng ta cần một bảng cơ sở dữ liệu.
Mình sẽ tạo một bảng cơ sở dữ liệu có tên là “tblMenbers” sử dụng. Như bạn có thể thấy, bảng của mình có 4 cột tại cột đầu tiên có kiểu IDENTITY. Khi bảng được tạo, hãy mở bảng ra và thêm dữ liệu bằng cách nhập thủ công dữ liệu vào bảng.
6.2. Cách để tạo một SELECT SP
Nhấp trên cơ sở dữ liệu của bạn và mở rộng mục “Programmability” và nhấp phải chuột vào “Stored Procedures”. Hoặc nhấn tổ hợp phím CTRL + N để tạo một cửa sổ truy vấn mới. Trong vùng truy vấn giữa BEGIN và END, nhập SELECT của bạn để chọn bản ghi từ bảng. Hãy xem lệnh Select để dưới đây:
Bây giờ bạn hãy nhấn phím F5 để thực thi SP này. Khi đó bạn sẽ nhận được thông báo “ Command (s) completed successfully”. Bây giờ bạn hãy đi đến mục Programmability >> Stored Procedures >> nhấn phải chuột và chọn Refresh.
Bạn có thể thấy hình ảnh như sau: Một SP mới có tên là stpGetAllMembers được tạo ra.
6.3. Thực thi các Stored Procedure
Trong giao diện bên dưới, nhấn phải chuột vào tên của SP và chọn Execute Stored Procedure để thực thi một SP. Từ đó, bạn có thể sửa đổi các SP hiện tại.
Ngoài ra, bạn có thể thực thi một SP từ cửa sổ Query – Truy vấn.
Để chạy Stored Procedure trong SQL Server Management Studio. Hãy chuyển đến cửa sổ Query hoặc nhấn tổ hợp phím Ctrl + N để mở một cửa sổ truy vấn mới cà nhập theo dòng lệnh sau đây:
- Cú pháp: EXEC <stored procedure name>
- Ví dụ: EXEC stpGetAllMembers
Bây giờ, chúng ra chạy Stored Procedure có tên là stpGetAllMembers. Kết quả thu được như sau:
7. Các tham số của một Stored Procedure là gì?
Các tham số trong các SP được sử dụng để truyền giá trị đầu vào và trả về các giá trị tại đầu ra. Có 2 kiểu tham số:
- Tham số đầu vào – Truyền các giá trị cho SP
- Tham số đầu ra – Trả về các giá trị từ một SP.
8. Làm sao để tạo các tham số trong một Stored Procedure truy vấn SELECT mà trả về các bản ghi theo tham số được truyền vào?
Trong các bước trước, chúng ta đã tạo một SP đơn giản mà trả về tất cả các hàng của một bảng. Bây giờ, hãy tạo một SP mới mà sẽ lấy một cái tên của thành phố như một tham số đầu vào và sẽ trả về tất cả các hàng có tên thành phố giống với giá trị đầu vào.
Đây là SP đã được cập nhật với tham số @CityName.
Thực thi nó.
Để chạy SP này bạn hãy nhập lệnh dưới đây vào công cụ truy vấn của SQL – SQL query tool.
EXEC GetMemberByCityName @CityName = ‘mal’
Hoặc sử dụng giao diện dưới đây
Code để thực thi sẽ như sau:
Đầu ra kết quả:
9. Cách để tạo một truy vấn INSERT dựa trên Stored Procedure?
Chúng ta có thể sử dụng truy vấn INSERT INTO để chèn dữ liệu vào một bảng. Lệnh SQL tạo một SP INSERT với 3 tham số như sau:
Nhấp phải chuột vào mục Stored Procedure trong Object Explorer và chọn Refresh.
Truyền giá trị của tham số trong hộp thoại thực thi.
Code dưới đây có thể được sử dụng để thực thi SP.
Đầu ra kết quả:
Trong cửa sổ truy vấn, bạn có thể kiểm tra xem đã có bản ghi mới cho Member Name “ Mahesh Chand” được thêm vào bảng chưa
Bạn cũng có thể chạy SP tương tự trong code sau:
EXEC stpInsertMember @MemberName = ‘Suhana & Ashish Kalla ‘, @MemberCity = ‘Mumbai ‘, @MemberPhone = N’9022592774xxx’
Đầu ra kết quả:
Bạn có thể kiểm tra bản ghi “Suhana & Ashish Kalla” được thêm thành công.
10. Cách để tạo một truy vấn UPDATE dựa trên Stored Procedure?
Hãy tạo một SP mới mà sẽ cập nhật một bảng ghi dưa trên cột Member ID. ID được truyền như một tham số đầu vào. Đây là một SP mới mà sử dụng một lệnh UPDATE … SET … WHERE.
Nhấp phải chuột vào stored procedure trong Object Explorer và chọn Refresh. Bạn sẽ thấy SP mới được tạo ra.
Bây giờ, hãy nhấp phải chuột vào tên của SP và chọn Execute stored procedure…. Hãy cung cấp giá trị đầu vào và thực thi.
Bạn cũng có thể sử dụng SQL Server Management Studio (SSMS).
EXEC stpUpdateMemberByID 17,’Gopal Madhavrai’,’Bikaner’,’90454564xxx’
Các kết quả phải thể hiện bạn đã cập nhật các giá trị.
11. Cách tạo một Stored Procedure để xóa các bản ghi sử dụng truy vấn DELETE?
Hãy tạo một SP mà sẽ xóa các bản ghi. SP mới sử dụng một lệnh DELETE và xóa tất cả các bản ghi mà có liên quan đến Member ID được cung cấp.
Thực thi nó.
Nhấp phải chuột lên Stored Procedure trong Object Explorer và chọn Refresh. Chạy Stored Procedure bằng giao diện.
Bây giờ hãy nhấp chuột phải vào SP một lần nữa và chọn Execute stored procedure. Như bạn có thể thấy trong ảnh, Mình đã truyền @MemberID parameter value = 4.
Hãy chạy DELETE SP một cách thủ công (bằng code)
EXEC stpDeleteMemberByMemberID 2
Kết quả đầu ra:
Bạn có thể thấy trong bản ghi MemberID = 4 đã được xóa thành công.
Vậy là vietgiatrang đã chia sẻ chi tiết cách tạo Stored Procedure trong SQL Server cho việc thêm, cập nhật, xóa các bản ghi. Chúc các bạn thực hiện thành công!
Xem thêm: Cách sửa lỗi realtek hd audio manager win 10 64 bit!