Stored Procedure trong SQL Server

Stored Procedure trong SQL Server: Chỉ dẫn từ ?-Ż về Stored Procedure

  • Trung Nguyen
  • 18/05/2020

  • 22 min read

Stored procedure trong SQL Server được sử dụng để nhóm một hoặc nhiều câu lệnh Transact-SQL thành các nhà cung cấp logic. Stored procedure được lưu trữ dưới dạng các đối tượng được đặt tên trong máy chủ CSDL SQL Server.

Khi bạn gọi một stored procedure lần trước hết, SQL Server sẽ tạo một chiến lược thực thi & lưu trữ nó trong bộ đệm. Trong các lần thực thi kế tiếp của stored procedure, SQL Server sử dụng lại chiến lược để stored procedure có thể thực thi rất nhanh chóng với năng suất đáng tin cậy.

Trong chỉ dẫn này, bạn sẽ tìm tòi mọi thứ bạn cần biết về stored procedure trong SQL Server & chỉ cho bạn cách xây dựng các stored procedure linh động để tối ưu hóa truy cập CSDL.

Chỉ dẫn căn bản về stored procedure trong SQL Server

Trong phần này, bạn sẽ tìm tòi cách làm chủ các stored procedure trong SQL Server bao gồm tạo, thực thi, sửa đổi & xóa các stored procedure.

Tạo stored procedure dễ dàng trong SQL Server

Câu lệnh SELECT sau trả về danh mục các sản phẩm từ bảng products trong CSDL mẫu BikeStores:

SELECT 
    product_name, 
    list_price
FROM 
    production.products
ORDER BY 
    product_name;

Để tạo stored procedure bao bọc truy vấn này, bạn sử dụng câu lệnh CREATE PROCEDURE như sau:

CREATE PROCEDURE uspProductList
AS
BEGIN
    SELECT 
        product_name, 
        list_price
    FROM 
        production.products
    ORDER BY 
        product_name;
END;

Trong cú pháp này:

  • uspProductList là tên của stored procedure.
  • Keyword AS ngăn cách tiêu đề & phần thân của stored procedure.
  • Nếu stored procedure có một câu lệnh, các keyword BEGIN & END bao quanh câu lệnh là tùy chọn. Bên cạnh đó, này là một thực hành tốt khiến cho mã cụ thể hơn.

Note: ngoài keyword CREATE PROCEDURE, bạn có thể sử dụng keyword CREATE PROC để khiến cho câu lệnh ngắn hơn.

Để biên dịch stored procedure này, bạn thực thi nó như một câu lệnh SQL bình bình trong SQL Server Management Studio như trong hình sau:

Nếu mọi thứ đều chuẩn xác, thì bạn sẽ thấy cảnh báo sau:

Commands completed successfully.

Nó có nghĩa là stored procedure đã được biên dịch & lưu trữ thành công vào CSDL.

Bạn có thể tìm ra stored procedure trong Object Explorer, trong Programmability > Stored Procedures như trong hình sau:

Tạo stored procedure đơn giản trong SQL Server

Thỉnh thoảng, bạn cần bấm vào nút Refresh để update thủ công các đối tượng CSDL trong Object Explorer.

Thực thi stored procedure trong SQL Server

Để thực thi stored procedure, bạn sử dụng câu lệnh EXECUTE hoặc EXEC theo sau là tên của stored procedure như sau:

EXECUTE sp_name;

Hoặc là

EXEC sp_name;

Với sp_name là tên của stored procedure mà bạn mong muốn thực thi.

Chẳng hạn, để thực thi stored procedure uspProductList, bạn sử dụng câu lệnh sau:

EXEC uspProductList;

Stored procedure trả về đầu ra sau:

Thực thi stored procedure trong SQL Server

Sửa đổi stored procedure trong SQL Server

Để sửa đổi stored procedure trong SQL Server, bạn sử dụng câu lệnh ALTER PROCEDURE.

Trước hết, stored procedure để xem bài viết của nó bằng cách nhấn chuột phải vào tên stored procedure & chọn list Modify:

Sửa đổi stored procedure trong SQL Server

Thứ hai, biến đổi bài viết của stored procedure mà trong chẳng hạn đó là xếp đặt các sản phẩm theo giá niêm yết thay vì tên sản phẩm:

ALTER PROCEDURE uspProductList
    AS
    BEGIN
        SELECT 
            product_name, 
            list_price
        FROM 
            production.products
        ORDER BY 
            list_price 
    END;

Thứ ba, bấm vào nút Execute, SQL Server sửa đổi stored procedure & trả về cảnh báo sau:

Commands completed successfully.

Giờ đây, nếu bạn thực thi lại stored procedure, bạn sẽ thấy các biến đổi đã có hiệu lực:

EXEC uspProductList;

Sau đây cho thấy một phần kết quả đầu ra:

Sửa đổi stored procedure trong SQL Server

Xóa stored procedure trong SQL Server

Để xóa stored procedure trong SQL Server, bạn sử dụng câu lệnh DROP PROCEDURE hoặc DROP PROC như sau:

DROP PROCEDURE sp_name;

Hoặc là

DROP PROC sp_name;

Với sp_name là tên của stored procedure mà bạn mong muốn xóa.

Chẳng hạn: để xóa stored procedure uspProductList, bạn thực thi câu lệnh sau:

DROP PROCEDURE uspProductList;

Trong phần này, bạn đã học cách làm chủ các stored procedure trong SQL Server bao gồm tạo, thực thi, sửa đổi & xóa các stored procedure.

Stored procedure có tham số trong SQL Server

Trong phần này, chúng tôi sẽ mở rộng stored procedure cho phép truyền một hoặc nhiều tham số cho nó. Kết quả của stored procedure sẽ biến đổi dựa vào giá trị của các tham số.

Tạo stored procedure có một tham số trong SQL Server

Truy vấn sau đây trả về danh mục sản phẩm từ bảng products trong CSDL mẫu BikeStores:

SELECT
    product_name,
    list_price
FROM 
    production.products
ORDER BY
    list_price;

Bạn có thể tạo stored procedure bao bọc truy vấn này bằng cách dùng câu lệnh CREATE PROCEDURE như sau:

CREATE PROCEDURE uspFindProducts
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM 
        production.products
    ORDER BY
        list_price;
END;

Bên cạnh đó, lần này tất cả chúng ta có thể thêm một tham số vào stored procedure để tìm các sản phẩm có giá niêm yết to hơn giá đầu vào:

ALTER PROCEDURE uspFindProducts
(
    @min_list_price AS DECIMAL
)
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM 
        production.products
    WHERE
        list_price >= @min_list_price
    ORDER BY
        list_price;
END;

Trong chẳng hạn này:

  • Trước hết, chúng tôi đã thêm một tham số có tên là @min_list_price vào stored procedure uspFindProducts. Mỗi tham số phải khởi đầu bằng ký hiệu @. Keyword AS DECIMAL chỉ định kiểu dữ liệu của tham số @min_list_price. Tham số phải được bao quanh bởi cặp dấu ngoặc tròn.
  • Thứ hai, chúng tôi đã sử dụng tham số @min_list_price trong mệnh đề WHERE của câu lệnh SELECT để lọc các sản phẩm có giá niêm yết to hơn hoặc bằng @min_list_price.

Thực thi stored procedure có một tham số trong SQL Server

Để thực thi stored procedure uspFindProducts, bạn truyền một đối số cho nó như sau:

EXEC uspFindProducts 100;

Sau đây cho thấy đầu ra:

Thực thi stored procedure có một tham số trong SQL Server

Stored procedure trả về toàn bộ các sản phẩm có giá niêm yết to hơn hoặc bằng 100.

Nếu bạn biến đổi đối số thành 200, bạn sẽ thu được một tập kết quả khác:

EXEC uspFindProducts 200;

Sau đây cho thấy đầu ra:

Thực thi stored procedure có một tham số trong SQL Server

Tạo stored procedure có nhiều tham số trong SQL Server

Stored procedure có thể có một hoặc nhiều tham số. Các tham số được phân chia bằng dấu phẩy.

Câu lệnh sau sửa đổi stored procedure uspFindProducts bằng cách thêm một tham số được có tên @max_list_price cho nó:

ALTER PROCEDURE uspFindProducts
(
    @min_list_price AS DECIMAL,
    @max_list_price AS DECIMAL
)
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM 
        production.products
    WHERE
        list_price >= @min_list_price AND
        list_price <= @max_list_price
    ORDER BY
        list_price;
END;

Khi stored procedure được sửa đổi thành công, bạn có thể thực thi nó bằng cách truyền hai đối số vào stored procedure, một cho @min_list_price & một cho @max_list_price như sau:

EXEC uspFindProducts 900, 1000;

Sau đây cho thấy đầu ra:

Xem Thêm  Cách sử dụng liên kết dữ liệu hai chiều trong AngularJS - Ràng buộc 2 chiều trong anglejs

Tạo stored procedure có nhiều tham số trong SQL Server

Sử dụng tên của tham số khi thực thi stored procedure trong SQL Server

Trong trường hợp các stored procedure có nhiều tham số, sẽ tốt hơn & cụ thể hơn khi thực thi cácstored procedure bằng cách dùng tên của các tham số.

Chẳng hạn, câu lệnh sau thực thi stored procedure uspFindProducts bằng cách dùng tên của các tham số @min_list_price & @max_list_price:

EXEC uspFindProducts 
    @min_list_price = 900, 
    @max_list_price = 1000;

Kết quả của stored procedure là như nhau ngoài ra câu lệnh sẽ cụ thể hơn.

Tham số kiểu chuỗi ký tự cho stored procedure trong SQL Server

Câu lệnh sau đây thêm tham số @name kiểu chuỗi ký tự vào stored procedure.

ALTER PROCEDURE uspFindProducts
(
    @min_list_price AS DECIMAL,
    @max_list_price AS DECIMAL,
    @name AS VARCHAR(max)
)
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM 
        production.products
    WHERE
        list_price >= @min_list_price AND
        list_price <= @max_list_price AND
        product_name LIKE '%' + @name + '%'
    ORDER BY
        list_price;
END;

Trong mệnh đề WHERE của câu lệnh SELECT, chúng tôi đã thêm điều kiện sau:

product_name LIKE '%' + @name + '%'

Bằng phương pháp này, stored procedure trả về các sản phẩm có giá niêm yết nằm trong phạm vi giá niêm yết ít nhất & tối đa & tên sản phẩm cũng chứa một đoạn văn bản mà bạn truyền vào.

Khi stored procedure được biến đổi thành công, bạn có thể thực thi nó như sau:

EXEC uspFindProducts 
    @min_list_price = 900, 
    @max_list_price = 1000,
    @name = 'Trek';

Trong câu lệnh này, chúng tôi đã sử dụng stored procedure uspFindProducts để tìm sản phẩm có giá niêm yết nằm trong khoảng 900 & 1.000 & tên của chúng có chứa từ Trek.

Hình ảnh sau đây cho thấy đầu ra:

Tham số kiểu chuỗi ký tự cho stored procedure trong SQL Server

Tạo các tham số tùy chọn cho stored procedure trong SQL Server

Khi bạn thực thì stored procedure uspFindProducts, bạn phải truyền cả ba đối số tương ứng với ba tham số của stored procedure.

SQL Server cho phép bạn chỉ định các giá trị mặc định cho các tham số để khi bạn thực thi stored procedure, bạn có thể bỏ qua các tham số có giá trị mặc định.

Xem stored procedure sau đây:

ALTER PROCEDURE uspFindProducts
(
    @min_list_price AS DECIMAL = 0,
    @max_list_price AS DECIMAL = 999999,
    @name AS VARCHAR(max)
)
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM 
        production.products
    WHERE
        list_price >= @min_list_price AND
        list_price <= @max_list_price AND
        product_name LIKE '%' + @name + '%'
    ORDER BY
        list_price;
END;

Trong stored procedure này, chúng tôi đã gán làm giá trị mặc định cho tham số @min_list_price & 999.999 làm giá trị mặc định cho tham số @max_list_price.

Khi stored procedure được biên dịch, bạn có thể thực thi nó mà không cần truyền các đối số cho tham số @min_list_price & @max_list_price:

EXEC uspFindProducts 
    @name = 'Trek';

Đây là kết quả:

Tạo các tham số tùy chọn cho stored procedure trong SQL Server

Trong trường hợp này, stored procedure đã sử dụng giá trị cho tham số @min_list_price & giá trị 999.999 cho tham số @max_list_price khi nó thực thi truy vấn.

Các tham số @min_list_price & @max_list_price được gọi là tham số tùy chọn (optional parameters).

Hiển nhiên, bạn cũng có thể truyền các đối số cho các tham số tùy chọn. Chẳng hạn: câu lệnh sau trả về toàn bộ các sản phẩm có giá niêm yết to hơn hoặc bằng 6.000 & tên chứa từ Trek:

EXEC uspFindProducts 
    @min_list_price = 6000,
    @name = 'Trek';

Tạo các tham số tùy chọn cho stored procedure trong SQL Server

Sử dụng NULL làm giá trị mặc định cho tham số của stored procedure trong SQL Server

Trong stored procedure uspFindProducts, chúng tôi đã sử dụng 999.999 làm giá niêm yết tối đa mặc định. Điều này không ổn vì trong tương lai bạn có thể có những sản phẩm có giá niêm yết to hơn thế.

Một kỹ thuật điển hình để tránh điều đó là sử dụng NULL làm giá trị mặc định cho các tham số:

ALTER PROCEDURE uspFindProducts
(
    @min_list_price AS DECIMAL = 0,
    @max_list_price AS DECIMAL = NULL,
    @name AS VARCHAR(max)
)
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM 
        production.products
    WHERE
        list_price >= @min_list_price AND
        (@max_list_price IS NULL OR list_price <= @max_list_price) AND
        product_name LIKE '%' + @name + '%'
    ORDER BY
        list_price;
END;

Trong mệnh đề WHERE, chúng tôi đã biến đổi điều kiện để giải quyết giá trị NULL cho tham số @max_list_price:

(@max_list_price IS NULL OR list_price <= @max_list_price)

Tips: đây là một biểu thức điều kiện rất hay mà bạn sẽ bắt gặp rất là nhiều trong các dự án thực tiễn khi đi làm.

Câu lệnh sau đây thực thi stored procedure uspFindProducts để tìm sản phẩm có giá niêm yết to hơn hoặc bằng 500 & tên có chứa từ Haro.

EXEC uspFindProducts 
    @min_list_price = 500,
    @name = 'Haro';

Sử dụng NULL làm giá trị mặc định cho tham số của stored procedure trong SQL Server

Trong phần này, bạn đã học cách tạo & thực thi stored procedure với một hoặc nhiều tham số. Bạn cũng từng học cách tạo các tham số tùy chọn & sử dụng NULL làm giá trị mặc định cho các tham số.

Biến trong SQL Server

Trong phần này, bạn sẽ tìm tòi về biến trong SQL Server bao gồm khai báo biến, gán giá trị cho biến & gán các trường giá trị của bản ghi cho biến.

Biến là gì?

Biến là một đối tượng chứa một giá trị duy nhất của một kiểu dữ liệu rõ ràng, chẳng hạn: integer, decimal, date hoặc chuỗi ký tự, ?.?.

Tất cả chúng ta thường sử dụng biến trong các trường hợp sau:

  • Là một bộ đếm vòng lặp để đếm số lần vòng lặp được thực hiện.
  • Để giữ một giá trị được kiểm soát bằng câu lệnh vòng lặp, ví dụ như lệnh WHILE.
  • Để lưu trữ giá trị được trả về bởi một stored procedure hoặc một hàm.

Khai báo biến

Để khai báo một biến, bạn sử dụng câu lệnh DECLARE. Chẳng hạn: câu lệnh sau khai báo một biến có tên @model_year:

DECLARE @model_year SMALLINT;

Câu lệnh DECLARE tạo một biến bằng cách gán cho nó một tên & kiểu dữ liệu. Tên biến phải khởi đầu bằng ký tự @. Trong chẳng hạn này, kiểu dữ liệu của biến @model_yearSMALLINT.

Theo mặc định, khi một biến được khai báo, giá trị của nó được đặt thành NULL.

Giữa tên biến & loại dữ liệu, bạn có thể sử dụng ASkeyword tùy chọn như sau:

DECLARE @model_year AS SMALLINT;

Để khai báo nhiều biến, bạn phân chia các biến bằng dấu phẩy:

DECLARE @model_year SMALLINT, 
        @product_name VARCHAR(MAX);

Gán giá trị cho biến

Để gán giá trị cho một biến, bạn sử dụng câu lệnh SET. Chẳng hạn: câu lệnh sau gán 2018 cho biến @model_year:

SET @model_year = 2018;

Sử dụng biến trong truy vấn

Câu lệnh SELECT sau sử dụng biến @model_year trong mệnh đề WHERE để tìm các sản phẩm theo năm của model:

SELECT
    product_name,
    model_year,
    list_price 
FROM 
    production.products
WHERE 
    model_year = @model_year
ORDER BY
    product_name;

Giờ đây, bạn có thể đặt mọi thứ lại với nhau & thực thi khối mã sau để có danh mục các sản phẩm có năm model là 2018:

DECLARE @model_year SMALLINT;

SET @model_year = 2018;

SELECT
    product_name,
    model_year,
    list_price 
FROM 
    production.products
WHERE 
    model_year = @model_year
ORDER BY
    product_name;

Note: để thực thi mã, bạn nhấn vào nút Execute như trong hình sau, hoặc nhấn phím F5:

Sử dụng biến trong truy vấn

Hình ảnh sau đây cho thấy kết quả đầu ra:

Xem Thêm  for-Loop trong R (10 Ví dụ) | Viết, chạy và sử dụng vòng lặp trong RStudio - đơn giản cho vòng lặp r

Sử dụng biến trong truy vấn

Lưu trữ kết quả truy vấn trong biến

Các bước sau đây miêu tả cách lưu trữ kết quả truy vấn trong một biến:

Trước hết, khai báo một biến có tên @product_count với kiểu dữ liệu integer:

DECLARE @product_count INT;

Thứ hai, sử dụng câu lệnh SET để gán tập kết quả của truy vấn cho biến:

SET @product_count = (
    SELECT 
        COUNT(*) 
    FROM 
        production.products 
);

Thứ ba, xuất bài viết của biến @product_count:

SELECT @product_count;

Hoặc bạn có thể sử dụng câu lệnh PRINT để in ra bài viết của một biến:

PRINT @product_count;

Hoặc là

PRINT 'The number of products is ' + CAST(@product_count AS VARCHAR(MAX));

Kết quả như sau:

The number of products is 204

Để ẩn số lượng bản ghi bị tác động, bạn sử dụng câu lệnh sau:

SET NOCOUNT ON;

Select một bản ghi vào biến

Các bước sau minh họa cách khai báo hai biến, gán một bản ghi cho chúng & xuất bài viết của các biến:

Trước hết, khai báo các biến để lưu trữ tên sản phẩm & giá niêm yết:

DECLARE 
    @product_name VARCHAR(MAX),
    @list_price DECIMAL(10,2);

Thứ hai, gán tên cột cho các biến tương ứng:

SELECT 
    @product_name = product_name,
    @list_price = list_price
FROM
    production.products
WHERE
    product_id = 100;

Thứ ba, xuất bài viết của các biến:

SELECT 
    @product_name AS product_name, 
    @list_price AS list_price;

Câu lệnh hoàn chỉnh sẽ như sau:

DECLARE 
    @product_name VARCHAR(MAX),
    @list_price DECIMAL(10,2);
    
SELECT 
    @product_name = product_name,
    @list_price = list_price
FROM
    production.products
WHERE
    product_id = 100;

SELECT 
    @product_name AS product_name, 
    @list_price AS list_price;

Đây là kết quả:

Select một bản ghi vào biến

Cộng dồn các giá trị vào một biến

Stored procedure sau đây có một tham số & trả về danh mục các sản phẩm dưới dạng chuỗi:

CREATE  PROC uspGetProductList
(
    @model_year SMALLINT
) AS 
BEGIN
    DECLARE @product_list VARCHAR(MAX);

    SET @product_list = '';

    SELECT
        @product_list = @product_list + product_name 
                        + CHAR(10)
    FROM 
        production.products
    WHERE
        model_year = @model_year
    ORDER BY 
        product_name;

    PRINT @product_list;
END;

Trong stored procedure này:

  • Trước hết, chúng tôi đã khai báo một biến có tên @product_list với kiểu varchar & gán giá trị của nó thành trống.
  • Thứ hai, chúng tôi đã lấy danh mục tên sản phẩm từ bảng sản phẩm dựa trên tham số @model_year. Trong câu lệnh SELECT chúng tôi cộng dồn tên sản phẩm vào biến @product_list. Note rằng CHAR(10) trả về ký tự xuống dòng.
  • Thứ ba, chúng tôi đã sử dụng câu lệnh PRINT để in ra danh mục tên sản phẩm.

Câu lệnh sau đây thực thi stored procedure uspGetProductList:

EXEC uspGetProductList 2018

Hình ảnh sau đây cho thấy một phần kết quả đầu ra:

Cộng dồn các giá trị vào một biến

Trong phần này, bạn đã tìm tòi về biến trong SQL Server bao gồm khai báo biến, gán giá trị cho biến & gán các trường của bản ghi cho các biến.

Tham số OUTPUT của stored procedure trong SQL Server

Trong phần này, bạn sẽ tìm tòi cách dùng các tham số  OUTPUT (tham số  đầu ra) trong SQL Server để trả dữ liệu trở lại chương trình gọi.

Tạo tham số OUTPUT cho stored procedure

Để tạo một tham số OUTPUT cho stored procedure, bạn sử dụng cú pháp sau:

parameter_name data_type OUTPUT

Một stored procedure có thể có nhiều tham số OUTPUT. Không những thế, các tham số OUTPUT có thể ở bất kỳ kiểu dữ liệu hợp lệ nào như: số nguyên, ngày tháng & chuỗi.

Chẳng hạn: stored procedure sau đây tìm kiếm các sản phẩm theo năm model & trả về số lượng sản phẩm thông qua tham số OUTPUT là @product_count:

CREATE PROCEDURE uspFindProductByModel 
(
    @model_year SMALLINT,
    @product_count INT OUTPUT
) AS
BEGIN
    SELECT 
        product_name,
        list_price
    FROM
        production.products
    WHERE
        model_year = @model_year;

    SELECT @product_count = @@ROWCOUNT;
END;

Trong stored procedure này:

Trước hết, chúng tôi đã tạo một tham số OUTPUT có tên là @product_count để lưu trữ số lượng sản phẩm được tìm ra:

@product_count INT OUTPUT

Thứ hai, sau câu lệnh SELECT, tất cả chúng ta đã gán số lượng bản ghi được trả về bởi truy vấn (@@ROWCOUNT) cho tham số @product_count.

Note: @@ROWCOUNT là một biến hệ thống trả về số lượng bản ghi được đọc bởi câu lệnh phía trước.

Khi bạn thực hiện câu lệnh CREATE PROCEDURE trên, stored procedure uspFindProductByModel sẽ được biên dịch & lưu trong danh sách CSDL.

Nếu mọi thứ đều ổn, SQL Server sẽ đề ra cảnh báo sau:

Commands completed successfully.

Thực thi stored procedure với các tham số OUTPUT

Để thực thi stored procedure với các tham số OUTPUT, bạn bắt chước các bước sau:

  • Trước hết, khai báo các biến để lưu trữ các giá trị được trả về bởi các tham số OUTPUT.
  • Thứ hai, sử dụng các biến này khi gọi thực thi stored procedure.

Chẳng hạn, câu lệnh sau thực hiện stored procedure uspFindProductByModel:

DECLARE @count INT;

EXEC uspFindProductByModel
    @model_year = 2018,
    @product_count = @count OUTPUT;

SELECT @count AS 'Number of products found';

Hình ảnh sau đây cho thấy đầu ra:

Thực thi stored procedure với các tham số OUTPUT

Trong chẳng hạn này:

Trước hết, chúng tôi khai báo biến @count để lưu trữ giá trị của tham số OUTPUT của stored procedure:

DECLARE @count INT;

Sau đó, thực thi stored procedure uspFindProductByModel & truyền các tham số:

EXEC uspFindProductByModel 
     @model_year = 2018, 
     @product_count = @count OUTPUT;

Trong câu lệnh này, tham số model_year2018 & biến @count sẽ được gán giá trị của tham số OUTPUT @product_count.

Bạn cũng có thể thực thi stored procedure uspFindProductByModel ngắn gọn như sau:


EXEC uspFindProductByModel 2018, @count OUTPUT;

Note: nếu bạn quên keyword OUTPUT sau biến @count thì giá trị của biến @count sẽ là NULL.

Cuối cùng, hiển thị giá trị của biến @count:


SELECT @count AS 'Number of products found';

Trong phần này, bạn đã học cách dùng tham số OUTPUT để truyền dữ liệu từ stored procedure trở lại chương trình gọi.

Xem Thêm  Tạo cơ sở dữ liệu trong Mysql - sql tạo cơ sở dữ liệu của tôi

Câu lệnh BEGIN … END trong SQL Server

Trong phần này, bạn sẽ tìm tòi cách dùng câu lệnh BEGIN...END trong SQL Server để bọc một tập hợp các câu lệnh Transact-SQL thành một khối câu lệnh.

Khái quát về câu lệnh BEGIN…END trong SQL Server

Câulệnh BEGIN...END trong SQL Server được sử dụng để khái niệm một khối câu lệnh. Một khối câu lệnh bao gồm một tập hợp các câu lệnh SQL thực thi bên nhau.

Nói cách khác, nếu xem mỗi câu lệnh là một câu văn thì câu lệnh BEGIN...END cho phép bạn khái niệm một đoạn văn.

Sau đây minh họa cú pháp của câu lệnh BEGIN...END:

BEGIN
     statement_block
END

Trong cú pháp này, bạn đặt một tập hợp các câu lệnh SQL giữa các keyword BEGIN & END, chẳng hạn:

BEGIN
    SELECT
        product_id,
        product_name
    FROM
        production.products
    WHERE
        list_price > 100000;

    IF @@ROWCOUNT = 0
        PRINT 'No product with price greater than 100000 found';
END

Đầu ra:

Tổng quan về câu lệnh BEGIN...END trong SQL Server

Để xem các cảnh báo được tạo bởi câu lệnh PRINT, trong SQL Server Management Studio, bạn cần bấm vào tab Messages. Theo mặc định, tab Results được bật & tab Messages bị ẩn.

Trong chẳng hạn này:

  • Trước hết, chúng tôi có một khối lệnh khởi đầu bằng keyword BEGIN & chấm dứt bằng keyword END.
  • Thứ hai, bên trong khối lệnh, chúng tôi có một câu lệnh SELECT tìm kiếm các sản phẩm có giá niêm yết to hơn 100.000. Sau đó, chúng tôi có câu lệnh IF để kiểm soát xem truy vấn có trả về bất kỳ sản phẩm nào không & in ra một cảnh báo nếu như không có sản phẩm nào trả về.

Note: @@ROWCOUNT là một biến hệ thống trả về số lượng bản ghi bị tác động bởi câu lệnh phía trước.

Câu lệnh BEGIN... END hạn chế một khối logic của các câu lệnh SQL. Chúng tôi thường sử dụng câu lệnh  BEGIN...END ở đầu & cuối của một stored procedure & function.

Câu lệnh BEGIN...END cũng được sử dụng cho các câu lệnh IF ELSE, câu lệnh WHILE, ?.?., để bao bọc nhiều câu lệnh.

Câu lệnh BEGIN … END lồng nhau trong SQL Server

Khối câu lệnh BEGIN...END có thể lồng nhau trong SQL Server. Nó dễ dàng có nghĩa là bạn có thể đặt một câu lệnh BEGIN...END trong một câu lệnhBEGIN... END khác .

Hãy cân nhắc chẳng hạn sau:

BEGIN
    DECLARE @name VARCHAR(MAX);

    SELECT TOP 1
        @name = product_name
    FROM
        production.products
    ORDER BY
        list_price DESC;
    
    IF @@ROWCOUNT <> 0
    BEGIN
        PRINT 'The most expensive product is ' + @name
    END
    ELSE
    BEGIN
        PRINT 'No product found';
    END;
END

Trong chẳng hạn này, chúng tôi đã sử dụng câu lệnh BEGIN...END để bọc toàn thể khối câu lệnh. Bên trong khối này, chúng tôi cũng sử dụng BEGIN...END cho các câu lệnh IF...ELSE.

Trong phần này, bạn đã tìm tòi về câu lệnh BEGIN...END trong SQL Server để bọc các câu lệnh Transact-SQL thành các khối lệnh.

CURSOR trong SQL Server

Trong phần này, bạn sẽ tìm tòi cách dùng CURSOR trong SQL Server để giải quyết một tập kết quả, mỗi lần một bản ghi.

SQL Server hoạt động dựa vào tập hợp, chẳng hạn, câu lệnh SELECT trả về một tập hợp các bản ghi được gọi là tập kết quả. Bên cạnh đó, thỉnh thoảng, bạn có thể mong muốn giải quyết một tập dữ liệu theo từng bản ghi. Đây là lúc CURSOR được sử dụng.

CURSOR là gì?

CURSOR (con trỏ) là một đối tượng cho phép duyệt qua các bản ghi của tập kết quả. Nó cho phép bạn giải quyết từng bản ghi riêng rẽ được trả về bởi một truy vấn.

Vòng đời của CURSOR trong SQL Server

Đây là các bước để tạo & sử dụng một CURSOR:

Vòng đời của CURSOR trong SQL Server

Trước hết, khai báo một CURSOR.

DECLARE cursor_name CURSOR
    FOR select_statement;

Để khai báo một CURSOR, bạn chỉ định tên của nó sau keyword DECLARE với kiểu dữ liệu là CURSOR & phân phối một câu lệnh SELECT khái niệm tập kết quả cho CURSOR.

Kế tiếp, mở CURSOR để thực thi câu lệnh SELECT:

OPEN cursor_name;

Sau đó, duyệt từng bản ghi trong CURSOR & mang vào một hoặc nhiều biến:


FETCH NEXT FROM cursor INTO variable_list;

SQL Server phân phối biến hệ thống @@FETCHSTATUS trả về hiện trạng của câu lệnh FETCH. Nếu @@FETCHSTATUS trả về 0, có nghĩa là câu lệnh FETCH đã thành công. Bạn có thể sử dụng câu lệnh WHILE để duyệt toàn bộ các bản ghi từ CURSOR như trong đoạn mã sau:

WHILE @@FETCH_STATUS = 0  
    BEGIN
        FETCH NEXT FROM cursor_name;  
    END;

Sau thời điểm đã duyệt qua toàn bộ bản ghi trong CURSOR, tất cả chúng ta sẽ đóng CURSOR bằng lệnh sau:

CLOSE cursor_name;

Cuối cùng, phân bổ CURSOR để giải phóng nó:

DEALLOCATE cursor_name;

Chẳng hạn CURSOR trong SQL Server

Chúng tôi sẽ sử dụng bảng products trong CSDL mẫu BikeStores để minh họa cho CURSOR trong SQL Server:

Bảng products trong cơ sở dữ liệu mẫu

Trước hết, khai báo hai biến để lưu trữ tên sản phẩm & giá niêm yết & một CURSOR để lưu trữ kết quả của truy vấn lấy tên sản phẩm & giá niêm yết từ bảng production.products:

DECLARE 
    @product_name VARCHAR(MAX), 
    @list_price   DECIMAL;

DECLARE cursor_product CURSOR
FOR SELECT 
        product_name, 
        list_price
    FROM 
        production.products;

Kế tiếp, mở CURSOR:

OPEN cursor_product;

Sau đó, duyệt từng bản ghi từ CURSOR & in ra tên sản phẩm & giá niêm yết:

FETCH NEXT FROM cursor_product INTO 
    @product_name, 
    @list_price;

WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT @product_name + CAST(@list_price AS varchar);
        FETCH NEXT FROM cursor_product INTO 
            @product_name, 
            @list_price;
    END;

Sau đó, đóng CURSOR:

CLOSE cursor_product;

Cuối cùng, phân bổ CURSOR để giải phóng nó.

DEALLOCATE cursor_product;

Đây là đoạn mã hoàn chỉnh:

DECLARE 
    @product_name VARCHAR(MAX), 
    @list_price   DECIMAL;

DECLARE cursor_product CURSOR
FOR SELECT 
        product_name, 
        list_price
    FROM 
        production.products;

OPEN cursor_product;

FETCH NEXT FROM cursor_product INTO 
    @product_name, 
    @list_price;
    
WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT @product_name + CAST(@list_price AS varchar);
        FETCH NEXT FROM cursor_product INTO 
            @product_name, 
            @list_price;
    END;

CLOSE cursor_product;

DEALLOCATE cursor_product;

Đây là đầu ra một phần:

Ví dụ CURSOR trong SQL Server

Trong thực tiễn, bạn sẽ ít khi sử dụng CURSOR để giải quyết tập kết quả vì nó rất chậm.

Trong phần này, bạn đã học cách dùng CURSOR trong SQL Server để giải quyết một tập kết quả, mỗi lần một bản ghi.

Còn tiếp…

Viết một bình luận