Skip to main content

Views, Stored Procedures

Views​

1. Tạo view​

Danh sách sản phẩm cùng với tên nhà cung cấp, tên loại sản phẩm
CREATE VIEW v_Products
AS
SELECT
    P.ProductId, P.ProductName, P.Price, P.CategoryId, P.SupplierId,
    C.CategoryName, S.Name AS 'SupplierName'
    FROM Products AS P
    INNER JOIN Categories AS C ON P.CategoryId = C.Id
    INNER JOIN Suppliers AS S ON P.SupplierId = D.Id

2. Sử dụng view​

Lấy danh sách sản phẩm
SELECT * FROM v_Products

3. Xóa view​

Xóa view
DROP VIEW v_Products

4. Sửa view​

Sửa view
ALTER VIEW v_Products
AS
SELECT
    P.ProductId, P.ProductName, P.Price, P.CategoryId, P.SupplierId,
    C.CategoryName, S.Name AS 'SupplierName'
    FROM Products AS P
    INNER JOIN Categories AS C ON P.CategoryId = C.Id
    INNER JOIN Suppliers AS S ON P.SupplierId = D.Id

Các tùy chọn khi tạo view​

WITH SCHEMABINDING​

Với việc sử dụng WITH SCHEMABINDING, view sẽ được ràng buộc với các đối tượng khác trong cơ sở dữ liệu. Nếu bạn thực hiện thay đổi cấu trúc của các đối tượng được ràng buộc (như thay đổi tên cột, tên bảng, ...), bạn sẽ không thể thực hiện được.

Tạo view và ràng buộc với schema
CREATE VIEW v_Products
WITH SCHEMABINDING
AS
SELECT
   P.Id, P.Name, P.Price, P.Discount, P.CategoryId, P.SupplierId
FROM
  Products AS P

WITH ENCRYPTION​

Với việc sử dụng WITH ENCRYPTION, mã nguồn của đối tượng sẽ được mã hóa và không thể đọc hoặc truy cập trực tiếp thông qua các công cụ SQL Server Management Studio (SSMS) hoặc các công cụ khác. Khi một đối tượng được mã hóa, SQL Server sẽ chỉ thực thi đối tượng đó mà không cung cấp truy cập vào mã nguồn.

Tạo view và mã hóa nội dung
CREATE VIEW v_Products
WITH ENCRYPTION
AS
SELECT
   P.Id, P.Name, P.Price, P.Discount, P.CategoryId, P.SupplierId
FROM
  Products AS P

WITH CHECK OPTION​

WITH CHECK OPTION là một cấu hình được sử dụng trong câu lệnh CREATE VIEW để đảm bảo rằng các dòng dự liệu được chọn trong View cũng phải thỏa mãn điều kiện của View. Nếu bạn thêm hoặc cập nhật dữ liệu thông qua View, nó chỉ cho phép các thay đổi đáp ứng điều kiện của View.

Tạo view và ràng buộc với điều kiện
CREATE VIEW v_Products
AS
SELECT
   P.Id, P.Name, P.Price, P.Discount, P.CategoryId, P.SupplierId
FROM
  Products AS P
WHERE
  P.Price > 1000 -- Nếu không có điều kiện WHERE thì không thể INSERT, UPDATE, DELETE được
WITH CHECK OPTION
  1. Truy vấn dữ liệu từ view
SELECT * FROM v_Products
  1. Cập nhật dữ liệu thông qua view
UPDATE v_Products SET Price = 900 WHERE Id = 1
Lưu ý

Lưu ý rằng nếu đơn giá mới được cập nhật không thỏa mãn điều kiện "Price > 1000", câu lệnh UPDATE sẽ không được chấp nhận.

Stored Procedures​

1. Tạo stored procedure​

Tạo stored procedure
CREATE PROCEDURE usp_GetOrders
  @FromDate DATETIME,
  @ToDate DATETIME
AS
BEGIN
  SELECT * FROM Orders WHERE CreatedDate BETWEEN @FromDate AND @ToDate
END

2. Sử dụng stored procedure​

Sử dụng stored procedure
EXEC usp_GetOrders '2021-01-01', '2021-01-31'

-- hoặc

EXEC usp_GetOrders @FromDate = '2021-01-01', @ToDate = '2021-01-31'

3. Với OUTPUT parameters​

Tạo stored procedure với OUTPUT parameter
CREATE PROCEDURE usp_GetOrders
  @FromDate DATETIME,
  @ToDate DATETIME,
  @Total INT OUTPUT
AS
BEGIN
  SELECT @Total = COUNT(*) FROM Orders WHERE CreatedDate BETWEEN @FromDate AND @ToDate
END
Sử dụng stored procedure với OUTPUT parameter
DECLARE @Total INT
EXEC usp_GetOrders '2021-01-01', '2021-01-31', @Total OUTPUT
SELECT @Total

4. Xóa stored procedure​

Xóa stored procedure
DROP PROCEDURE usp_GetOrders

5. Sửa stored procedure​

Sửa stored procedure
ALTER PROCEDURE usp_GetOrders
  @FromDate DATETIME,
  @ToDate DATETIME
AS
BEGIN
  SELECT O.*, OD.ProductId, OD.Quantity, OD.Price, OD.Discount
  FROM Orders AS O
    INNER JOIN OrderDetails AS OD ON O.Id = OD.OrderId
  WHERE O.CreatedDate BETWEEN @FromDate AND @ToDate
END

Các tùy chọn khi tạo stored procedure​

WITH ENCRYPTION​

Với việc sử dụng WITH ENCRYPTION, mã nguồn của đối tượng sẽ được mã hóa và không thể đọc hoặc truy cập trực tiếp thông qua các công cụ SQL Server Management Studio (SSMS) hoặc các công cụ khác. Khi một đối tượng được mã hóa, SQL Server sẽ chỉ thực thi đối tượng đó mà không cung cấp truy cập vào mã nguồn.

Tạo stored procedure và mã hóa nội dung
CREATE PROCEDURE usp_GetOrders
WITH ENCRYPTION
  @FromDate DATETIME,
  @ToDate DATETIME
AS
BEGIN
  SELECT O.*, OD.ProductId, OD.Quantity, OD.Price, OD.Discount
  FROM Orders AS O
    INNER JOIN OrderDetails AS OD ON O.Id = OD.OrderId
  WHERE O.CreatedDate BETWEEN @FromDate AND @ToDate
END

WITH RECOMPILE​

Với việc sử dụng WITH RECOMPILE, stored procedure sẽ được biên dịch lại mỗi khi thực thi. Điều này sẽ giúp tăng hiệu suất thực thi của stored procedure.

Tạo stored procedure và biên dịch lại mỗi khi thực thi
CREATE PROCEDURE usp_GetOrders
WITH RECOMPILE
  @FromDate DATETIME,
  @ToDate DATETIME
AS
BEGIN
  SELECT O.*, OD.ProductId, OD.Quantity, OD.Price, OD.Discount
  FROM Orders AS O
    INNER JOIN OrderDetails AS OD ON O.Id = OD.OrderId
  WHERE O.CreatedDate BETWEEN @FromDate AND @ToDate
END

WITH EXECUTE AS​

Với việc sử dụng WITH EXECUTE AS, stored procedure sẽ được thực thi với quyền của người dùng được chỉ định.

Tạo stored procedure và thực thi với quyền của người dùng được chỉ định
CREATE PROCEDURE usp_GetOrders
WITH EXECUTE AS 'dbo'
  @FromDate DATETIME,
  @ToDate DATETIME
AS
BEGIN
  SELECT O.*, OD.ProductId, OD.Quantity, OD.Price, OD.Discount
  FROM Orders AS O
    INNER JOIN OrderDetails AS OD ON O.Id = OD.OrderId
  WHERE O.CreatedDate BETWEEN @FromDate AND @ToDate
END