Khái niệm

Stored Procedure - SP (thủ tục) là một tập các lệnh T -SQL và một số cấu trúc điều khiển, được lưu với một tên và được thực thi như một đơn vị công việc (Single unit of work) . Một thủ tục có thể gọi thực thi hoặc tự động thực khi khi SQL Server khởi động.

Thao tác trên Stored Procedure

Để thuận tiện cho các bạn học tập, mình có tạo sẵn file .sql trong này đã mình tạo cấu trúc bảng và data sẵn để các bạn dễ dàng thực hiện truy vấn, các bạn có thể tải tại đây.

Tạo và thực thi Stored Procedure

Cú pháp tạo:

CREATE PROCEDURE|PROC procedure_name
AS
    sql_statement
GO

Cú pháp thực thi:

EXECUTE|EXEC procedure_name

Ví dụ 1: Hãy xây dựng thủ tục cho phép xuất thông tin của các phòng nghiên cứu.

Cài đặt:

CREATE PROCEDURE DSNV_NGHIENCUU
AS
    SELECT NHANVIEN.*
    FROM NHANVIEN, PHONGBAN
    WHERE TENPHG = 'Nghien cuu' AND PHG = MAPHG
GO

EXECUTE DSNV_NGHIENCUU

Kết quả thực thi:

Ví dụ 2: Hãy xây dựng thủ tục cho phép xuất thông tin của các nhân viên của một phòng bất kì theo yêu cầu người truy vấn.

Cặt đặt:

CREATE PROCEDURE DSNV_PHONG(@TENPHONG VARCHAR(12))
AS
    SELECT NHANVIEN.*
    FROM NHANVIEN, PHONGBAN
    WHERE TENPHG = @TENPHONG AND PHG = MAPHG
GO

EXECUTE DSNV_PHONG 'Nghien cuu'

Kết quả thực thi:

Sửa và xóa Stored Procedure

Cú pháp sửa:

ALTER PROCEDURE|PROC procedure_name
AS
    sql_statement
GO

Cú pháp xóa:

DROP PROCEDURE|PROC procedure_name

Ví dụ 1: Hãy xây dựng thủ tục cho biết số nhân viên của các phòng ban.

Cài đặt:

CREATE PROC SLNV_PHOG
AS
    BEGIN
        SELECT TENPHG, COUNT(MANV) AS SLNV
        FROM NHANVIEN, PHONGBAN
        WHERE PHG = MAPHG
        GROUP BY PHG, TENPHG
    END
GO

EXEC SLNV_PHOG

Kết quả thực thi:

Ví dụ 2: Hãy chỉnh sửa lại thủ tục ở ví dụ 1, hãy cho biết tên phòng và số lượng nhân viên của phòng ban đó, nhưng chỉ xuất những phòng có số lượng nhân viên lớn hơn hoặc bằng 5.

Cài đặt:

ALTER PROC SLNV_PHONG
AS
    BEGIN
        SELECT TENPHG, COUNT(MANV) AS SLNV
        FROM NHANVIEN, PHONGBAN
        WHERE PHG = MAPHG
        GROUP BY PHG, TENPHG
        HAVING COUNT(MANV) >= 5
    END
GO

EXEC SLNV_PHONG

Kết quả thực thi:

Ví dụ 3: Hãy xóa thủ tục vừa được chỉnh sửa ở ví dụ 2.

Cài đặt:

DROP PROC SLNV_PHOG

Kết quả thực thi:

Sử dụng tham số trong Stored Procedure

Input Parameter là tham số nhập, là cầu nối giữa giá trị nhập từ yêu cầu người truy vấn với điều kiện để thực hiện lọc kết quả trong cơ sở dữ liệu thông qua tham số được cài đặt trong thủ tục.

Ví dụ 1: Tham số @TENPHONG trong câu lệnh SQL bên dưới chính là Input Parameter.

CREATE PROC DSNV_PHONG(@TENPHONG VARCHAR(12))
AS
    SELECT NHANVIEN.*
    FROM NHANVIEN, PHONGBAN
    WHERE TENPHG = @TENPHONG AND PHG = MAPHG
GO

Output Parameter là tham số xuất chứa giá trị trả về của thủ tục. Thông thường sử dụng Output Parameter khi có nhu cầu sử dụng giá trị trả về của thủ tục cho một hoặc nhiều hành động kế tiếp.

Ví dụ 2: Tham số @LUONGMAX trong câu lệnh SQL bên dưới chính là Output Parameter.

Cài đặt:

CREATE PROC LUONGCAONHAT_PHONG(@TENPHONG VARCHAR(12), @LUONGMAX INT OUTPUT)
AS
    SELECT @LUONGMAX = MAX(LUONG)
    FROM NHANVIEN, PHONGBAN
    WHERE TENPHG = @TENPHONG AND PHG = MAPHG
GO

Thực thi:

DECLARE @LUONGCAONHAT INT
EXEC LUONGCAONHAT_PHONG 'Nghien cuu', @LUONGCAONHAT OUTPUT
PRINT CONVERT(VARCHAR(10), @LUONGCAONHAT)

Kết quả thực thi:

CÓ THỂ BẠN QUAN TÂM

SQL Server - Constraints

SQL Server - Constraints

Trong một CSDL, luôn luôn tồn tại rất nhiều mối liên hệ ảnh hưởng qua lại lẫn nhau giữa các thuộc tính của một quan hệ, giữa các bộ giá trị trong một quan hệ và giữa các thuộc tính của các bộ giá trị...

Giới thiệu SQL Server

Giới thiệu SQL Server

Giới thiệu chung SQL (Structured Query Language, đọc là "sequel") là tập lệnh truy xuất trên cơ dữ liệu (CSDL) quan hệ. Ngôn ngữ SQL, được IBM sử dụng đầu tiên trong hệ quản trị CSDL Sytem R vào...

Lập trình T SQL

Lập trình T SQL

T-SQL (Transact SQL) là ngôn ngữ lập trình cơ sở dữ liệu thủ tục độc quyền của Microsoft dành cho SQL Server. Chúng cũng được sử dụng để viết các thủ tục được lưu trữ, là một đoạn mã nằm trên máy chủ...

SQL Server - Backup / Restore Database

SQL Server - Backup / Restore Database

Mục đích của việc sao lưu và phục hồi dữ liệu Sao lưu và phục hồi dữ liệu là vấn đề rất quan trọng khi quản trị cơ sở dữ liệu. Hoạt động này đảm bảo cho dữ liệu khi xảy ra sự cố, ta có thể khôi phục...

SQL Server - Mệnh đề GROUP BY

SQL Server - Mệnh đề GROUP BY

Chức năng: gom nhóm các dòng dữ liệu theo tiêu chí gom nhóm đặt trong mệnh đề GROUP BY nhằm hỗ trợ và phục vụ các mục đích của câu truy vấn.

SQL Server - User Defined Function

SQL Server - User Defined Function

Khái niệm User Defined Function là những hàm do người dùng tự định nghĩa để đáp ứng một mục tiêu nào đó. Một số hạn chế so với thủ tục là các tham số truyền vào không được mang thuộc tính OUTPUT, ng...

SQL Server - Truy Vấn Lồng

SQL Server - Truy Vấn Lồng

Truy vấn lồng là những câu lệnh mà trong thành phần WHERE có chứa thêm một câu lệnh SELECT khác nữa. Câu lệnh này thường gặp khi dữ liệu cần thiết phải duyệt qua nhiều lần. Đây là một trong những...

Cài Đặt SQL Server 2019

Cài Đặt SQL Server 2019

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ệ...

SQL Server - Lệnh DELETE

SQL Server - Lệnh DELETE

Chức năng: cho phép người dùng xóa một hoặc nhiều dòng dữ liệu trên bảng dữ liệu đã có sẵn trong cơ sở dữ liệu.

ManhDanBlogs