So sánh thủ tục lưu trữ (stored procedure) và hàm(function) Microsoft SQL Server là một hệ quản trị cơ sở dữ liệu quan hệ được phát triển bởi Microsoft. Là một máy chủ cơ sở dữ liệu, nó là một sản phẩm phần mềm có chức năng chính là lưu trữ và truy xuất dữ liệu theo yêu cầu của các ứng dụng phần mềm khác. Có thể chạy trên cùng một máy tính hoặc trên một máy tính khác trên mạng (bao gồm cả Internet).Trong quá trình thực thi cơ sở dữ liệu, đôi khi chúng ta cần thực hiện liên tục một câu hoặc khối lệnh dài, phức tạp để đáp ứng các nhu cầu quản trị cơ sở dữ liệu vì vậy một câu lệnh hoặc cấu trúc lệnh được sử dụng lặp đi lặp lại, dài dòng một cách liên tục dẫn đến tình trạng có thể nhập sai, logic của dòng lệnh không mạch lạc khó khăn trong việc nhìn lại, thao tác, gặp phải những rắc rối trong quá trình thực hiện dẫn đến mất thời gian trong việc xử lý, chỉnh sửa. Những điều trên cũng làm cho hiệu suất của việc thao tác cũng như hiệu suất của cơ sở dữ liệu giảm đáng kể. Vì thế, một giải pháp hữu ích hơn đã được ra đời, đó chính là việc thực hiện các thủ tục lưu trữ (stored procedure) hoặc sử dụng các hàm (function).Microsoft – một trong những nhà tiên phong trong hệ thống quản trị cơ sở dữ liệu với hệ quản trị cơ sở dữ liệu SQL Server có nhiều chức năng trong việc sử dụng các đối tượng như thủ tục lưu trữ và hàm. Và để có thể hiểu rõ hơn cách sử dụng hai đối tượng này thì bài tiểu luận “Tìm hiểu về store procedure và function trong sql” sẽ giúp giải đáp một phần nào về vấn đề này.Bài viết của em gồm 4 chương:CHƯƠNG I: XÂY DỰNG CƠ SỞ DỮ LIỆUCHƯƠNG II: THỦ TỤC LƯU TRỮ (STORED PROCEDURE)CHƯƠNG III: HÀM (FUNCTION)CHƯƠNG IV: SỰ KHÁC BIỆT GIỮA STORED PROCEDURE VÀ FUNCTION TRONG SQL SERVER
XÂY DỰNG CƠ SỞ DỮ LIỆU
Đặc tả cơ sở dữ liệu
Trường Đại học Ngân hàng TP.HCM đã phát triển một hệ thống cơ sở dữ liệu nhằm quản lý sinh viên hiệu quả Nhà trường tổ chức thành 7 khoa đào tạo, mỗi khoa được phân biệt bằng mã khoa và tên khoa riêng, cùng với số lượng chuyên đề đào tạo, giúp tối ưu hóa quy trình quản lý.
Trường yêu cầu mỗi khóa đào tạo phải có mã, tên, năm bắt đầu và kết thúc để quản lý hiệu quả Sinh viên được cấp mã số, họ tên, ngày sinh và giới tính, cùng với lớp sinh hoạt để thuận tiện trao đổi với giảng viên Các chương trình đào tạo như chính quy, chất lượng cao và quốc tế cũng cần mã và tên riêng để phân loại Mỗi môn học có mã, tên và khoa đào tạo, với yêu cầu sinh viên phải đạt điểm trên 4.0 để qua môn Bộ phận đào tạo sẽ nhập kết quả học tập, số lần thi và điểm số vào hệ thống Mỗi môn học có lịch giảng chi tiết bao gồm mã chương trình, mã lớp học phần, tên học phần, số tín chỉ, địa điểm và số tiết, trong khi lớp học được xác định bằng mã lớp, mã khóa học, mã khoa, chương trình đào tạo và số lượng sinh viên.
Lược đồ quan hệ cơ sở dữ liệu quản lý sinh viên
Nhà trường được tổ chức thành 7 khoa đào tạo, mỗi khoa đều có mã và tên riêng, cùng với số lượng chuyên đề đào tạo khác nhau, nhằm nâng cao hiệu quả quản lý.
MaKhoa TenKhoa SoLuongChuyenDe varchar(10) nvarchar(100) int
MaKhoaHoc TenKhoaHoc NamBatDau NamKetThuc varchar(10) nvarchar(100) Int int
SinhVien: Sinh viên trong trường được cấp mã số sinh viên, họ tên, ngày sinh, giới tính,… sinh viên đều có một lớp sinh hoạt.
MaSV HoTen NgaySin h GioiTinh DiaChi MaLop
(Foreign Key) varchar(10) nvarchar(100) date nvarchar(5) nvarchar(10) varchar(10)
ChuongTrinh: Trường có nhiều chương trình đào tạo khác nhau: chính quy, chất lượng cao, quốc tế cấp song bằng nên cần có mã, tên chương trình
Bảng 1 4: Bảng chương trình đào tạo
MonHoc: Nhà trường có nhiều môn học khác nhau, nên cần có mã môn học, tên môn học, mã khoa đào tạo.
MaMH MaKhoa (Foreign Key) TenMH varchar(20) nvarchar(10) nvarchar(100)
Để đạt tiêu chuẩn qua môn, mỗi sinh viên trong trường cần có điểm trung bình trên 4.0 Bộ phận đào tạo có trách nhiệm nhập thông tin lên hệ thống, bao gồm các môn học mà sinh viên đã hoàn thành, số lần thi và điểm số cuối cùng, nhằm đánh giá chất lượng đào tạo của sinh viên.
SoLanThi Diem varchar(10) varchar(20) int float
LichGiang: Mỗi môn học đều có lịch giảng chi tiết bao gồm mã chương trình, mã khoa, mã môn học, số tín chỉ, địa điểm, số tiết.
SoTinChi DiaDiem SoTiet varchar(10) varchar(10) varchar(20) int nvarchar(100) int
Lop: Lớp học có mã lớp, mã khóa học, mã khoa, chương trình đào tạo, số lượng sinh viên.
SoLuongSV varchar(15) varchar(15) varchar(15) varchar(15) int
Sơ đồ Database Diagrams
Hình 1 1: Sơ đồ database diagrams quản lý sinh viên
Cơ sở dữ liệu quản lý sinh viên
Hình 1 2: Cơ sở dữ liệu bảng khoa
Hình 1 3: Cơ sở dữ liệu bảng khóa học
Hình 1 4: Cơ sở dữ liệu bảng sinh viên
Hình 1 5: Cơ sở dữ liệu bảng chương trình
Hình 1 6: Cơ sở dữ liệu bảng môn học
Hình 1 7: Cơ sở dữ liệu bảng lịch giảng
Hình 1 8: Cơ sở dữ liệu bảng lớp
Hình 1 9: Cơ sở dữ liệu bảng kết quả
THỦ TỤC LƯU TRỮ (STORED PROCEDURE)
Stored procedure là gì?
Thủ tục SQL được lưu trữ (SP) là tập hợp các câu lệnh SQL và logic được biên dịch và lưu trữ trong cơ sở dữ liệu Chúng cho phép tạo và thực thi các truy vấn SQL trên máy chủ, đồng thời có thể được lưu vào bộ nhớ đệm để sử dụng lại Mục đích chính của thủ tục này là ẩn các truy vấn SQL trực tiếp khỏi mã nguồn, giúp cải thiện hiệu suất cho các hoạt động cơ sở dữ liệu như chọn, cập nhật và xóa dữ liệu.
Phân loại thủ tục
Có thể phân loại cho thủ tục thành:
2.1 Thủ tục do người dùng định nghĩa (User-defined Stored Procedure):
Các thủ tục lưu trữ do người dùng định nghĩa được phát triển bởi các nhà phát triển hoặc quản trị viên cơ sở dữ liệu.
- Các SP này chứa thêm một câu lệnh SQL nữa để chọn, cập nhật hoặc xóa bản ghi khỏi bảng cơ sở dữ liệu
- Thủ tục lưu trữ do người dùng định nghĩa có thể nhận các tham số đầu vào và trả về các tham số đầu ra
- Thủ tục lưu trữ do người dùng định nghĩa là hỗn hợp của các lệnh DDL và DML.
Thủ tục do người dùng định nghĩa được phân thêm thành 2 loại:
- T-SQL stored procedures: Các SP T-SQL nhận và trả về các tham số Các
Stored Procedure (SP) này thực hiện các truy vấn Chèn, Cập nhật và Xóa, có hoặc không có tham số, và xuất dữ liệu của các hàng dưới dạng đầu ra Đây là phương pháp phổ biến nhất để viết Stored Procedure trong SQL Server.
- CLR stored procedures: CLR SP được viết bằng ngôn ngữ lập trình dựa trên CLR như C # hoặc VB.NET và được thực thi bởi NET Framework.
2.2 Thủ tục lưu trữ tạm thời (Temporary Stored Procedure):
Thủ tục tạm thời trong CSDL tempdb là một loại thủ tục do người dùng xác định, chia thành hai loại: cục bộ và toàn cục Thủ tục tạm thời cục bộ, với tiền tố (#), chỉ hiển thị cho kết nối người dùng hiện tại và sẽ bị xóa khi kết nối này đóng Ngược lại, thủ tục tạm thời toàn cục, có tiền tố (##), có thể được truy cập bởi bất kỳ người dùng nào sau khi được tạo ra và sẽ bị xóa khi phiên cuối cùng kết thúc.
2.3 Thủ tục lưu trữ hệ thống (System Stored Procedure): Đây là thủ tục được được tạo và thực thi bởi SQL Server cho các hoạt động quản trị của máy chủ, tích hợp sẵn trong SQL Server dưới dạng lưu trữ vật lý trong CSDL master và msdb Trong hệ thống, các thủ tục này mang tiền tố là sp_, thường đóng vai trò hỗ trợ cho các DBA các vấn đề liên quan đến quản trị và an ninh dữ liệu.
2.4 Thủ tục lưu trữ mở rộng (Extended User-Defined Stored Procedure):
Các thủ tục mở rộng cho phép tạo ra quy trình bên ngoài bằng ngôn ngữ lập trình như C Những thủ tục này được triển khai dưới dạng thư viện liên kết động (DLL), mà SQL Server có khả năng tự động tải và khởi động khi cần thiết.
3 Thủ tục lưu trữ hệ thống (System Stored Procedure):
Trong SQL Server, quản trị và truy xuất thông tin có thể được thực hiện thông qua các thủ tục được lưu trữ hệ thống Các thủ tục này được phân loại thành nhiều nhóm khác nhau.
Stored Procedures Được sử dụng để quản lý các cấu hình Active Geo- Replication trong Cơ sở dữ liệu Azure SQL
Stored Procedures trong Catalog được sử dụng để triển khai các chức năng liên quan đến từ điển dữ liệu ODBC, giúp tách biệt các ứng dụng ODBC khỏi những thay đổi đối với các bảng hệ thống cơ bản.
Stored Procedures Được sử dụng để bật, tắt hoặc báo cáo về các đối tượng chứa dữ liệu thay đổi.
Cursor Stored Procedures Được sử dụng để thực hiện chức năng biến con trỏ.
Procedures Được sử dụng để làm việc với bộ thu thập dữ liệu và các thành phần sau: bộ thu thập, mục thu thập và loại bộ sưu tập.
Procedures Được sử dụng để bảo trì chung của SQL Server Database Engine.
Procedures (Transact-SQL) Được sử dụng để thực hiện các hoạt động e-mail từ bên trong một phiên bản của SQL Server.
Stored Procedures Được sử dụng để thiết lập các nhiệm vụ bảo trì cốt lõi được yêu cầu để quản lý hiệu suất cơ sở dữ liệu.
Procedures Được sử dụng để thực hiện và quản lý các truy vấn phân tán.
Stored Procedures Được sử dụng để cấu hình và quản lý các tính năng FILESTREAM và FileTable.
Procedures Được sử dụng để cấu hình firewall của Cơ sở dữ liệu Azure SQL.
Procedures Được sử dụng để triển khai và truy vấn các chỉ mục toàn văn.
Stored Procedures được sử dụng để tạo ra giao diện giữa phiên bản SQL Server và các ứng dụng bên ngoài, phục vụ cho các hoạt động bảo trì khác nhau.
Procedures Được sử dụng để định cấu hình, sửa đổi và giám sát các nhật ký cấu hình vận chuyển.
Procedures Được sử dụng để kích hoạt các đối tượng tự động hóa tiêu chuẩn để sử dụng trong một lô Transact-SQL tiêu chuẩn.
Procedures Được sử dụng để quản lý dựa trên chính sách.
Thêm hoặc xóa một máy tính khỏi nhóm PolyBase scale- out.
SQL) Được sử dụng để điều chỉnh hiệu suất.
Procedures Được sử dụng để quản lý sao chép.
Procedures Dùng để quản lý bảo mật.
Các quy trình được sử dụng để xóa bản sao lưu FILE_SNAPSHOT cùng với tất cả các ảnh chụp nhanh liên quan, hoặc để xóa các ảnh chụp nhanh tệp sao lưu riêng lẻ.
Procedures Được sử dụng để phân tích và cải thiện hiệu suất lập chỉ mục của các chỉ mục không gian.
Stored Procedures Được sử dụng bởi SQL Server Profiler để theo dõi hiệu suất và hoạt động.
Stored Procedures Được sử dụng bởi SQL Server Agent để quản lý các hoạt động theo lịch trình và theo hướng sự kiện.
Procedures Được sử dụng để quản lý cơ sở dữ mở rộng.
Procedures Sử dụng cho bảng thời gian
XML Stored Procedures Được sử dụng để quản lý văn bản XML.
HÀM (FUNCTION)
Function là gì?
Các hàm SQL là chương trình con được sử dụng phổ biến để xử lý và thao tác dữ liệu trong các ứng dụng cơ sở dữ liệu Tất cả hệ thống cơ sở dữ liệu SQL đều cung cấp công cụ DDL và DML nhằm hỗ trợ việc tạo lập và duy trì cơ sở dữ liệu hiệu quả.
DDL chịu trách nhiệm về việc tạo, sửa đổi và bảo trì cơ sở dữ liệu, trong khi DML đảm nhận các nhiệm vụ thao tác dữ liệu, đóng vai trò như giao diện lập trình của cơ sở dữ liệu DML bao gồm các lệnh xử lý và tính toán dữ liệu, tương tự như các ngôn ngữ lập trình khác Ngoài ra, DML hỗ trợ các hàm SQL, là những chương trình nhỏ có thể có hoặc không có tham số đầu vào nhưng chỉ trả về một giá trị.
Phân loại hàm
Có thể phân chia hàm thành 2 loại: hàm dựng sẵn và hàm do người dùng định nghĩa, trong đó:
Các hàm tiêu chuẩn trong hệ thống cơ sở dữ liệu SQL, được định nghĩa bởi ANSI, bao gồm nhiều hàm dựng sẵn chia thành hai loại chính: hàm tổng hợp và hàm vô hướng Ví dụ, hàm ABS() thực hiện các phép tính, hàm GETDATE() lấy giá trị hệ thống hiện tại, và hàm LEFT() dùng để thao tác dữ liệu văn bản.
2.2 Hàm do người dùng định nghĩa
Các hàm do người dùng tạo ra phục vụ cho những mục đích cụ thể và có khả năng trả về một giá trị đơn lẻ hoặc một tập hợp các giá trị.
Hàm vô hướng (Scalar Function) là các hàm do người dùng định nghĩa, trả về một giá trị dữ liệu duy nhất theo kiểu được xác định trong mệnh đề RETURNS Đối với hàm vô hướng nội tuyến, giá trị trả về là kết quả của một câu lệnh đơn, trong khi hàm vô hướng đa lệnh có thể chứa nhiều câu lệnh Transact-SQL để trả về một giá trị duy nhất Kiểu dữ liệu trả về có thể là bất kỳ loại nào ngoại trừ text, ntext, image, cursor và timestamp.
Hàm kiểu bảng (Table-Valued Functions) là các hàm do người dùng định nghĩa, cho phép trả về một kiểu dữ liệu bảng có thể sử dụng như một bảng thông thường trong SQL Đặc biệt, đối với hàm có giá trị bảng nội tuyến, không cần thân hàm, bảng sẽ được tạo ra từ tập hợp kết quả của một câu lệnh SELECT.
Loại Mô tả Ví dụ
Hàm thực hiện phép tính trên một tập giá trị và trả về một giá trị duy nhất.
APPROX_COUNT_DISTINCT, AVG, COUNT, COUNT_BIG, GROUPING, GROUPING_ID, MAX, MIN, SUM, VAR, VARP
Hàm thực hiện trả về thông tin về cài đặt tùy chọn cấu hình hiện tại
Functions Các hàm vô hướng này trả về thông tin về con trỏ
Hàm thực hiện thao tác trên các loại dữ liệu thời gian
GETDATE, SYSDATETIME,GETUTCDATE, DAY, MONTH,YEAR, ISDATE, PARTS, ARTS
Hàm thực hiện các phép tính, thường dựa trên các giá trị đầu vào được cung cấp dưới dạng đối số và trả về một giá trị số
ABS, PI, POWER, RAND, ROUND, FLOOR, CEILING,
Hàm vô hướng trả về thông tin về cơ sở dữ liệu và các đối tượng cơ sở dữ liệu
OBJECT_ID, OBJECT_NAME, OBJECT_SCHEMA_NAME, OBJECTPROPERTY,
String Functions Hàm thực hiện thao tác trên một chuỗi các loại dữ liệu
UPPER, LOWER, LEFT, RIGHT, TRIM, LTRIM, SPACE, LEN, REVERSE, CONCAT, CHOOSE
Hàm trả về các thông tin trong quản lý bảo mật
USER, USER_ID, USER_NAME, IS_MEMBER,
SUSER_SID, IS_SRVROLEMEMBER System
Hàm trả về thông tin thống kê về hệ thống
Bảng 3 1: Các loại hàm hệ thống
SỰ KHÁC BIỆT GIỮA STORED PROCEDURE VÀ
Điểm chung
- Cả stored procedure và function đều là các đối tượng cơ sở dữ liệu chứa một tập các câu lệnh SQL để hoàn thành một tác vụ.
Stored procedure (thủ tục lưu trữ) cho phép tái sử dụng truy vấn SQL nhiều lần, giúp tiết kiệm thời gian và công sức Nếu bạn có một truy vấn mà dự định sử dụng thường xuyên, hãy lưu nó dưới dạng thủ tục lưu trữ và chỉ cần gọi để thực thi Bên cạnh đó, bạn cũng có thể truyền tham số cho thủ tục lưu trữ để tùy chỉnh kết quả.
- Một function (hàm) được biên dịch và thực thi mỗi khi hàm đó được gọi.Hàm phải trả về giá trị
Sự khác biệt cơ bản giữa Stored Procedure và Function
Thủ tục lưu trữ có thể trả về giá trị bằng không, một giá trị hoặc nhiều giá trị, trong khi đó hàm chỉ có khả năng trả về một giá trị duy nhất, mặc dù giá trị này có thể là một bảng.
Các hàm chỉ cho phép tham số đầu vào, trong khi thủ tục lưu trữ có thể nhận cả tham số đầu vào lẫn đầu ra.
- Hàm có thể được gọi từ thủ tục lưu trữ trong khi thủ tục lưu trữ không thể được gọi từ hàm.
Sự khác biệt nâng cao giữa Stored Procedure và Function
Trong câu lệnh SELECT, chỉ có thể sử dụng hàm, trong khi các thủ tục không thể được nhúng Điều này là do thủ tục có khả năng trả về nhiều tập kết quả, do đó không phù hợp để sử dụng trong câu lệnh SELECT.
- Các thủ tục lưu trữ không thể được sử dụng trong các câu lệnh WHERE / HAVING / SELECT trong khi hàm thì có thể.
- Một ngoại lệ có thể được xử lý bằng try-catch trong thủ tục lưu trữ, đối với hàm thì không thể.
- Có thể sử dụng Transactions trong thủ tục lưu trữ, với hàm thì không thể.
Ví dụ sử dụng cả 2 Stored Procedure và Function
VD1: So sánh điểm môn học lần thứ 1 của 2 sinh viên và trả về kết quả sinh viên có điểm cao hơn
- Tạo function để so sánh kết quả
Hình 4 4: Truy vấn thủ tục
