SQL Tạo Procedure
Hướng dẫn tạo Procedure và sử dụng Procedure trong SQL Server Database.
- Procedure trong SQL Server là gì?
- Cách tạo thủ tục Create Procedure trong SQL Server.
- Cách Test và Debug thủ tục Procedure trong SQL Server
- Cách sửa thủ tục Modify Procedure trong SQL Server.
- Cách xóa thủ tục Delete Procedure trong SQL Server.
1.Procedure trong SQL Server là gì?
- Thủ tục Procedure trong SQL Server là một chương trình con hay một mô-đun, bao gồm các lệnh nhằm thực hiện một chức năng hoặc một nhiệm vụ cụ thể nào đó của chương trình.
- Thủ tục Procedure chỉ được thực thi khi nó được gọi.
Ưu điểm của Procedure:
-Dể dàng chỉnh sửa và bảo trì: Lập trình viên dễ dàng chỉnh sửa code bên trong thủ tục ở SQL Server mà không cần khởi động lại hay triển khai ứng dụng. Ví dụ, nếu truy vấn T-SQL được viết trong ứng dụng và bạn cần thay đổi logic, bạn phải thay code trong ứng dụng đó và triển khai lại nó. Thủ tục trong SQL Server loại bỏ những khó khăn đó bằng cách lưu trữ code trong database.Do đó, khi muốn thay đổi logic bên trong procedure, bạn chỉ có thể làm việc đó bằng lệnh Alter Procedure đơn giản.
-Có thể tái sử dụng tiết kiệm thời gian lập trình: Một Procedure có thể dùng ở nhiều nơi trong 1 ứng dụng hoặc trong nhiều ứng dụng khác nhau.
-Giảm tải truy cập: Trong các ứng dụng phần mềm nếu không sử dụng Procedure nó sẽ phải thực hiện nhiều câu lệnh SQL và gửi request truy cập nhiều lần đến Database. Khi sử dụng Procredure các lệnh T-SQL sẽ được thực thi 1 lần tại cơ sỡ dữ liệu do đó giảm tải lượt truy cập và request tới Database.
-Bảo mật: Sử dụng Procedure là bảo mật và nó tránh được lỗi SQL injection.
2.Cách tạo Procedure trong SQL Server.
-Sử dụng lệnh CREATE PROCEDURE để tạo mới một thủ tục trong SQL Database.
Cú pháp tạo thủ tục Procedure trong SQL Server như sau:
Cú Pháp
CREATE PROCEDURE Procedure_Name
-- Phần tham số parameter
<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,
<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
SET NOCOUNT ON;
-- Phần Khai báo biến
DECLARE @v_Variable
-- Phần Viết các lệnh SQL cho thủ tục Procedure.
SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
Giả sử chúng ta cần tạo một thủ tục Procedure trong SQL Server để insert thêm thông tin của nhân viên vào cơ sở dữ liệu SQL Server.
Ví dụ các bước tạo thủ tục Procedure để insert dữ liệu của nhân viên vào bảng Employee_t trong SQL Server như sau:
-Bước 1: Tạo mới thủ tục Procedure trong SQL Server.
-Trong cơ sở dữ liệu TestLuu vào thư mục Programmability sau đó chọn thư mục Stored Procedures và sau đó nhấn chuột phải chọn New Stored Procedure… để tạo mới thủ tục.
-Bước 2: Viết code SQL cho thủ tục Insert như sau:
Ví dụ tạo thủ tục trong SQL
CREATE PROCEDURE [dbo].[P_Insert_Employee]
@pv_EMP_ID nvarchar(50),
@pv_EMP_Name nvarchar(255),
@pv_EMP_Age int,
@pv_Result nvarchar(255) out
AS
BEGIN
SET NOCOUNT ON;
DECLARE @pv_Count int = 0;
--Check kiểm tra mã nhân viên EMP_ID
SELECT @pv_Count=count(*) FROM Employee_t WHERE EmployeeID=@pv_EMP_ID;
IF (@pv_Count > 0)
BEGIN
Set @pv_Result ='NG: The EmployeeID was existed in system.';
return;
END
ELSE
BEGIN
INSERT INTO Employee_t(EmployeeID, EmployeeName, EmployeeAge) VALUES(@pv_EMP_ID, @pv_EMP_Name, @pv_EMP_Age);
Set @pv_Result ='OK: Insert Employee Successfully !';
END
END
-Bước 3: Nhấn vào biểu tượng Execute hoặc F5 để biên dịch thủ tục. Trong trường hợp nếu có lỗi code, SQL Server sẽ thông báo cho bạn.
3.Cách Test và Debug thủ thục Procedure trong SQL Server.
- Nhấn chuột phải vào thủ tục P_Insert_Employee sau đó chọn Execute Stored Procedure…
- Nhập giá trị cho các tham số đầu vào.
- Hoặc sử dụng lệnh SQL thực thi chạy (Execute) thủ tục.
Ví dụ
USE [TestLuu]
GO
DECLARE @return_value int,
@pv_Result nvarchar(255)
EXEC @return_value = [dbo].[P_Insert_Employee]
@pv_EMP_ID = N'NV_001',
@pv_EMP_Name = N'Hoàng Văn Lưu',
@pv_EMP_Age = 38,
@pv_Result = @pv_Result OUTPUT
SELECT @pv_Result as N'@pv_Result'
4.Cách sửa thủ tục Procedure trong SQL Database
Để sửa thủ tục Procedure sử dụng lệnh ALTER PROCEDURE.
Hoặc vào thư mục Stored Procedures chọn tên thủ tục mà bạn muốn sửa và sau đó nhấn chuột phải và chọn Modify để sửa thủ tục đó. Ví dụ ở đây tôi chọn thủ tục P_Insert_Employee để sửa như sau (Lưu ý sau khi sửa xong nhấn Execute hoặc F5 để lưu lại thủ tục)
Ví dụ sửa thủ tục trong SQL Database
ALTER PROCEDURE [dbo].[P_Insert_Employee]
@pv_EMP_ID nvarchar(50),
@pv_EMP_Name nvarchar(255),
@pv_EMP_Age int,
@pv_Result nvarchar(255) out
AS
BEGIN
SET NOCOUNT ON;
DECLARE @pv_Count int = 0;
--Check kiểm tra mã nhân viên EMP_ID
SELECT @pv_Count=count(*) FROM Employee_t WHERE EmployeeID=@pv_EMP_ID;
IF (@pv_Count > 0)
BEGIN
Set @pv_Result ='NG: The EmployeeID was existed in system.';
return;
END
ELSE
BEGIN
INSERT INTO Employee_t(EmployeeID, EmployeeName, EmployeeAge) VALUES(@pv_EMP_ID, @pv_EMP_Name, @pv_EMP_Age);
Set @pv_Result ='OK: Insert Employee Successfully !';
END
END
5.Cách Xóa thủ tục Procedure trong SQL Database
Xóa thủ tục Procedre trong SQL bằng lệnh DROP PROCEDURE.
Cú Pháp
DROP PROCEDURE Tên_Procedure;
Ví dụ
DROP PROCEDURE P_Insert_Employee;
-Xóa thủ tục bằng giao diện SQL Studio.
