Thủ tục lưu trữ do người dùng định nghĩa trong SQL

Bạn sẽ sử dụng Microsoft SQL Server làm cơ sở dữ liệu. Khái niệm được đề cập trong hướng dẫn này tương tự cho hầu hết các Hệ quản trị cơ sở dữ liệu quan hệ (RDBMS), nhưng cú pháp có khác nhau, tuy nhiên ý tưởng được dùng ở đây phù hợp cho mọi cơ sở dữ liệu.

Stored Procedure là một khối câu lệnh SQL trong Hệ quản trị cơ sở dữ liệu quan hệ (RDBMS), thường được viết bởi lập trình viên, quản trị viên cơ sở dữ liệu, nhà phân tích dữ liệu, và được lưu lại để tái sử dụng cho nhiều chương trình. Nó cũng có thể có các loại khác nhau tùy theo RDBMS. Tuy nhiên, hai loại Stored Procedure quan trọng nhất có trong bất kỳ RDBMS nào như sau.

• Stored Procedure do người dùng định nghĩa
• Stored Procedure hệ thống

Stored Procedure là mã đã biên dịch sẵn, được lưu trữ hoặc lưu vào bộ nhớ đệm để tái sử dụng. Bạn có thể có mã được lưu đệm để dùng lại, giúp việc bảo trì dễ dàng hơn rất nhiều, tức là không cần thay đổi nhiều lần, đồng thời có thể duy trì tính bảo mật.

Tạo bảng

Bây giờ bạn sẽ tạo một bảng tên là table_Employees như sau.

CREATE TABLE table_Employees
(
    EmployeeId INT PRIMARY KEY NOT NULL,
    EmployeeFirstName VARCHAR(25) NOT NULL,
    EmployeeLastName VARCHAR(25) NOT NULL,
    EmployeeGender VARCHAR(25) NOT NULL,
    EmployeeDepartmentID INT

)
Được hỗ trợ bởi

Bảng chứa thông tin về nhân viên trong một công ty cụ thể và gồm các cột sau:

1. EmployeeId: Khóa chính, là duy nhất và có kiểu số nguyên; giá trị sẽ tự động tăng.
2. EmployeeFirstName: Tên của nhân viên, có kiểu ký tự và không được để trống.
3. EmployeeLast Name: Họ của nhân viên, có kiểu ký tự và không được để trống.
4. EmployeeGender: Giới tính của nhân viên, cũng là kiểu ký tự.
5. EmployeeDepartmentId: Mã phòng ban của nhân viên, kiểu số nguyên.

Bạn có thể viết truy vấn để chèn giá trị vào bảng. Cú pháp truy vấn để thêm giá trị vào bảng là:

    INSERT INTO <TABLE_NAME>(<COLUMNS_OF_TABLE>) VALUES(<CORRESPONDING_VALUES_TO_MATCH_COLUMN>)
Được hỗ trợ bởi

Sau khi chèn các giá trị vào bảng, table_Employees được tạo.

Tạo Stored Procedure do người dùng định nghĩa

Stored Procedure cho phép bạn viết truy vấn một lần, gán cho truy vấn đó một tên để có thể lưu và thực thi nhiều lần khi cần. Sau khi thực thi các truy vấn, bạn có thể thấy một thư mục được tạo tại Programmabilty->Stored Procedure, trong đó có tệp tên dbo.uprocGetEmployees.

Cú pháp tạo Stored Procedure là:

CREATE PROCEDURE <<<procedure_name>>>
AS
BEGIN

   '''Required SQL Queries'''

END
Được hỗ trợ bởi

Bạn có thể tạo Stored Procedure bằng câu lệnh SQL như sau.

• CREATE PROCEDURE procedure_name
• CREATE PROC procedure_name

Ngoài ra, bạn cần đặt tên Stored Procedure theo quy ước khác với "sp_.". Stored Procedure trong ví dụ được tạo với tên: uprocGetEmployees.

Thực thi Stored Procedure

Để thực thi một Stored Procedure, bạn có thể dùng một trong ba cách và chạy như sau.

• EXEC <>
• EXECUTE <>
• <>

Trong chương trình trên, bạn có thể dùng tên thủ tục uprocGetEmployees và chọn để thực thi truy vấn.

Stored Procedure với tham số

Stored Procedure có thể nhận một hoặc nhiều tham số. Bạn có thể dễ dàng hiểu tham số đơn nếu trước tiên hiểu tham số đa.

Cú pháp tạo Stored Procedure với nhiều tham số là:

CREATE PROCEDURE <<procedure_name>> <<procedure_parameter>>
AS
    BEGIN
            <<sql_query>>
    END
Được hỗ trợ bởi

Ví dụ cho thấy có sự thay đổi trong thư mục bên dưới.
EMPLOYEE->Programmability->Stored Procedures->dbo.uprocGetEmployessGenderAndDepartment

Khái niệm ở đây rất giống với hàm trong các ngôn ngữ lập trình phổ biến như Python, Java, v.v. Chương trình trên có tên hàm hay thủ tục là "uprocGetEmployeesGenderAndDepartment", với các tham số @EmployeeGender và @EmployeeDepartmentId được "gọi" bằng cách truyền giá trị cần thiết vào thủ tục của chúng ta; có thể thực hiện theo một trong hai cách và được giải thích bên dưới.

Các tham số trong chương trình này là @EmployeeGender và @EmployeeDepartmentId, được "gọi" bằng cách truyền giá trị cho tham số.

Giá trị có thể được chỉ định theo một trong hai cách. Bạn có thể dùng một trong hai cách để thực thi Stored Procedure với nhiều tham số.

• Chỉ định tên tham số trong truy vấn và truyền các giá trị cần thiết:

• Theo vị trí khớp với tham số của Stored Procedure trong truy vấn của bạn và truyền giá trị cần thiết cho tham số:

Sửa đổi Stored Procedure

Bạn cũng có thể sửa đổi Stored Procedure bằng lệnh ALTER PROCEDURE.

    ALTER PROCEDURE uprocGetEmployeesGenderAndDepartment
@EmployeeGender nvarchar(25),
@EmployeeDepartmentId int
BEGIN
    SELECT EmployeeFirstName,EmployeeGender,EmployeeDepartmentId FROM table_Employees WHERE
    EmployeeGender = @EmployeeGender AND EmployeeDepartmentId = @EmployeeDepartmentId
END
Được hỗ trợ bởi

Xóa Stored Procedure

Stored Procedure có thể được xóa nhanh chóng bằng các lệnh sau:

DROP PROC <> DROP PROCEDURE <>

Tăng cường bảo mật bằng mã hóa

Biểu tượng hình ổ khóa ở hình bên trái bên dưới cho thấy Stored Procedure đã được mã hóa, đảm bảo chỉ những người được cấp quyền mới có thể truy cập.

Kết luận

Bạn vừa hoàn thành phần học các kiến thức cơ bản về User Stored Procedure và các chủ đề được đề cập có tính khái niệm tương tự với bất kỳ RDBMS nào. Bạn có thể học thêm trên DataCamp qua khóa học Intermediate SQL Server. Ngoài ra, hướng dẫn Stored Procedures sau đây cũng sẽ hỗ trợ tốt cho việc học của bạn.