Cách thành phần trong lập trình T-SQL

Các thành phần này cho phép xử lý các họa động khác nhau mà không thể hoàn thành chỉ trong một câu lệnh đơn. Người dùng có thể nhóm vài câu lệnh lại với nhau và sử dụng một trong các cách sau:

• Batches: Một danh sách một hoặc nhiều các câu lệnh được gửi như một đơn vị truy vấn từ ứng dụng vào server
• Stored Procedures: Một stored procedure là một tập hợp các caua lệnh T-SQL được biên dịch trước và định nghĩa trước trên server.
• Triggers: một trigger là một kiểu store procedure được thực thi khi người dùng xử lý một sự kiện như INSERT,UPDATE, DELETE trên bảng
• Scripts: Là một chuỗi các câu lệnh T-SQL được lưu trữ trong file và sử dụng để nhập liệu thông qua SSMS hoặc sqlcmd
• Variables: biến cho phép người dùng có thể sử dụng như một đầu vào (input) của câu lệnh T-SQL
• Control-of-flow: luồng dideefu khiển để tạo các cấu trúc điều kiện trong T-SQL
• Error Handling: cơ chế xử lý lỗi sử dụng để kiểm soát lỗi và cung cấp thông tin cho người dùng khi bắt gặp lỗi.

T-SQL Batches

T-SQl là một nhóm một hoặc nhiều các câu lệnh được gửi như một đơn vị truy vấn từ ứng dụng vào server. SQL Server biên dịch batch thành một đơn vị thực thi duy nhất, gọi là excution plan (kế hoạch thực thi). Trong excution plan, SQL sẽ thực thi từng câu lệnh một. Mỗi một câu lệnh kết thúc bằng dấu “;”. Điều kiện này là không bắt buộc, nhưng cơ sở để kết thúc câu lệnh mà không có dấu “;” đã cũ (deprecated) và có thể sẽ bỏ đi trong phiên bản mới của SQL Server. Vì vậy, sử dụng dauas ; để kết thúc câu lệnh luôn được khuyến khích.

Khi biên dịch lỗi chẳng hạn như lỗi cú pháp bắt gặp, batch sẽ không được thực thi.

Lôi thời gian chạy (run-time error) như vi phạm ràng buộc hoặc logic thuật toán sẽ có một trong các tình huống sau xảy ra:

• Hầu hết các run-time error sẽ dừng câu lệnh hiện tại và các câu sau trong batch
• Một run-time error xác định như ràng buộc sẽ dùngw chỉ khi câu lệnh tồn tại và các phần lệnh còn tại vẫn được thực thi

Câu lệnh SQL thực thi trước khi xảy ra runtime-error không bị ảnh hưởng. Ngoại lệ duy nhất là khi batch là một transaction và lỗi xảy ra trong transaction khi có nghiệp vụ rollback.

Ví dụ, giải sử rằng có 10 câu lệnh trong batch và 6 câu lệnh gặp lỗi, sau đó các câu lệnh còn lại trong batch sẽ không được thực thi. Nếu batch được biên dịch và caua lệnh thứ 3 chạy thất bại, 2 câu lệnh đầu tiên sẽ được thực thi.

Một số quy tắc cần tuân thủ khi sử dụng batches:

• CREATE FUNCTION, CREATE DEFAULT, CREATE RULE, CREATE TRIGGER, CREATE PROCEDURE, CREATE VIEW hay CREATE SCHEMA không thể được nối với câu lệnh khác trong batch. Câu lệnh CREATE bắt đầu batch và tất cả các câu lệnh còn lại bên tron batch được coi như một phần của định nghĩa câu lệnh CREATE.
• không có thay đổi nào được thực hiện trong bảng và các cột mới tham chiếu trở lại như cũ
• Nếu câu lệnh đầu tiên trong batch là câu lệnh execute, thì caua lệnh excute không yeue cầu, nó chỉ yêu cầu chỉ khi câu lệnh execute không tồn tại trong câu lệnh đầu trong batch.

Ví dụ batch:

USE AdventureWorks2019;
GO
GREATE VIEW dbo.vProduct
AS
SELECT ProductNumber, Name FROM Production.Product;
GO
SELECT * FROM dbo.vProduct;
GO

Vií dụ 2 sử dụng để kết hợp nhiều BATCH trong transaction:

BEGIN TRANSACTION
GO
USE AdventureWorks2019;
GO
CREATE TABLE Company(
Id_Num int IDENTITY(1,1),
Company_Name nvarchar(100))
GO
INSERT Company (Company_Names) ('Company 1')
INSERT Company (Company_Names) ('Company 2')
INSERT Company (Company_Names) ('Company 3')
GO
SELECT Id_Num, Company_Name FROM dbo.Company
ORDER BY Company_Name ASC;
GO
COMMIT;
GO

Trong đoạn code trên, vài batches được kết hợp lại trong một transaction. Câu lệnh BEGIN TRANSACTION và COMMIT xác định việc mở và đóng transaction. CREATE TABLE, BEGIN TRANSACTION, SELECT, COMMIT và USE là các batch câu lệnh đơn. Câu lệnh INSERT tất cả được đóng gói trong một batch.

Variable (biến) trong T-SQL

Trong SQL Server, biến có thể được khai báo và sử dụng làm tham số hoặc viết các câu truy vấn động, như một tham số truyền từ ứng dụng vào. SQL Server cung cấp câu lệnh để khai báo và gán giá trị cho biến.

DECLARE

Biến được khởi tạo thông qua câu lệnh DECLARE trong thân batch. Các biến này được gán giá trị bằng câu lệnh SELECT hoặc SET. Biến được khởi tạo với giá trị NULL nếu người dùng không cung cấấp giá trị trong thời điểm khai báo.

Cú pháp:

DECLARE {(@local_variable [AS] data_type) } [= value]}

Ví dụ:

USE AdventureWorks2019;
GO
DECLARE @find varchar(30) = 'Man%';
SELECT p.LastName, p.FirstName, ph.PhoneNumber FROM Person.Person AS p 
JOIN Person.PersonPhone AS ph ON p.BusinessEntityID = ph.BusinessEntityID
WHERE LastName LIKE @find;

SET

Câu lệnh SET gán giá trị cho biến cục bộ được khởi tạo bằng DECLARE để xác định giá trị cho biến

Cú pháp:

SET {@local_variable = {expression}}
{@local_variable {+= | -= | *= | /= | %= | &= | ^= | |=}}

Giải thích toán tử:

• += thêm và gán bằng
• -= trừ và gán bằng
• *= nhân và gán bằng
• /= chia và gán bằng
• %= chia lấy dư và gán bằng
• &= bitwise AND và gán sau đó
• ^= bitwise XOR và gán sau đó
• |= Bitwise OR và gán sau đó.

Ví dụ:

DECLARE @myvar char(20);
SET @myvar = 'Hello world';

SELECT

Câu lệnh SELECT chỉ ra biến cục bộ xác định thông qua caua lệnh DECLARE để lấấy biểểu thức ra

Cú pháp:

SELECT {@local_variable {= | += | -= | *= | /= | %= | &= | ^= | != } expresssion}

Ví dụ:

USE AdventureWorks2019
GO
DECLARE @var1 nvarchar(30);
set @var1 = 'company hello';
SET @var1 = (select Name FROM Sales.Store WHERE BusinessEntityID = 292);
SELECT @var1 AS 'Company name';

USE AdventureWorks2019
GO
DECLARE @var1 nvarchar(30);
set @var1 = 'company hello';
select @var1 = Name FROM Sales.Store WHERE BusinessEntityID = 292;
SELECT @var1 AS 'Company name';

2 câu lệnh SET và SELECT nhìn có vẻ tương tự, nhưng có sự khác biệt nhỏ:

• Chỉ có thể gán một biến trong một thời điểm bằng SET, tuy nhiên, SELECT có thể gán giá trị cho nhiều biến cùng lúc.
• SET chỉ có thể gán một biết kiểu dữ liệu cơ bản ghi gán từ câu truy vấn, sẽ gặp lỗi khi dữ liệu là tập hợp hàng và cột. Tuy nhiên SELECT có thể gán nhiều giá trị trả về cho một biến.

Synonyms

Synonyms là một đối tượng database phục vụ cho các mục đích sau:

• Nó gợi ý một tên khác cho các đối tượng database khác nhau, cũng được gọi là base object, nó có thể tồn tại ở remote hoặc local server.
• Nó biểu diễn tầng trừu tượng bảo vệ ứng dụng khách khỏi các sửa đổi được thực hiện đối với vị trí và tên của đối tượng cơ sở.

Ví dụ, xét bảng Department trong AdventureWorks2019 được đặt ở server tên là server1. Để tham chiếu tới bảng từ server 2 có tên là server2, ứng dụng khách sẽ phải sử dụng 4 tên thành phần:
server1.AdventureWorks2019.HumanResources.Department

Nếu vị trí của bảng được sửa đổi, ví dụ sang một server khác, ứng dụng khách cần không chịu ảnh hưởng của thay đổi này. Để giải quyết cả hai vấn đề này, người dùng có thể tạo synonym DeptEmpTable ở Server2 cho bảng Department trên Server1
Từ đó, ứng dụng khách chỉ cần sử dụng duy nhất một tên là DeptEmpTable để tham chiếu tới bảng Department.

Tương tự, nếu vị trí của bảng Department thay đổi, người dùng cần phải chỉnh sửa synonym, DeptEmpTable để trỏ về vị trí mới của bảng Department.
Và lưu ý, không có câu lệnh ALTER Synonym, bạn cần drop synonym và khởi tạo lại với tên cũ nhưng trỏ vào vị trí mới của Department.

Danh sách các database object mà ngời dùng có thể tạo synonyms cho nó:

• Extended stored procedure
• SQL Table-valued function
• SQL stored procedure
• Table (user-definded)
• Replication-filter-procedure
• SQL scalar function
• SQL inline-tabled-valued function
• View

Synonyms và schema

Giả sử ngời dùng muốn tạo một synonym và có schema mặc định không được sở hữu bởi họ. Trong trường hợp này, họ có thể họ có thể xác định tên synonym với scema mà họ thực sự sở hữu. Ví dụ, người dùng sở hữu schema Resources nhưng Material là schema mặc định, nếu user muốn tạo synonym, họ phải đặt tiền tố cho synonym với schema Resources.

Gán quyền trên Synonyms

chỉ các thành viên của vai trò db_owner hoặc db_ddladmin hoặc chủ sở hữu synonym mới được phép cấp quyền cho một synonym. Người dùng có thể từ chối, cấp hoặc thu hồi tất cả hoặc bất kỳ quyền nào trên một synonym.

Danh sách các quyền:

• DELETE
• INSERT
• TAKE OWNERSHIP
• VIEW DEFINITION
• CONTROL
• EXECUTE
• SELECT
• UPDATE

Làm việc với Synonyms

Người dùng làm việc với synonyms trong trong SQL Server 2019 hoặc T-SQL trong SSMS. Để tạo một synonym trong SSMS dùng object explorer làm theo các yêu cầu:

Để tạo synonym bằng T-SQL, sử dụng cú pháp:

CREATE SYNONYM [schema_name_1.] synonym_name FOR <object>
<object> ::= 
{
server_name.[database_name].[schema_name_2].|database_name.[schema_name_2].|schema_name_2.] object_name}

Ví dụ:

USE AdventureWorks2019;
GO
CREATE SYNONYM MyAddressType
FOR AdventureWorks2019.Person.AddressType;
GO

Program Flow Statements

Các kiểu khách nhau của câu lệnh luồng chương trình được hỗ trợ bởi T-SQL như sau:

T-SQL Control-of-Flow language

Control-of-flow chỉ ra luồng thực thi của câu lệnh SQL, khối lệnh, các hàm người dùng định nghĩa, stored procedures.

Mặc định, câu lệnh T-SQL được thực thi tuần tự. Luồng chương trình cho phép thực thi câu lệnh trong các tình huống cụ thể, có liên quan với nhau và được tạo ra giữa các mã bằng cách sử dụng các cấu trúc tương tự như ngôn ngữ lập trình.

Danh sách các Control-Of-Flow Language keywords:

• RETURN
• THROW
• TRY…CATCH
• WAIT FOR
• WHILE
• BEGIN…END
• BREAK
• CONTINUE
• GOTO label
• IF…ELSE

BEGIN…END

Cú pháp:

BEGIN
{
sql_statement | statement_block
}
END

Ví dụ

USE AdventureWorks2019;
GO
BEGIN TRANSACTION;
GO
IF @@TRANCOUNT = 0 BEGIN
SELECT FirstName, MiddleName
FROM Person.Person WHERE LastName = 'Andy';
ROLLBACK TRANSACTION;
PRINT N'Rolling back the transaction two times would cause an error.';
END;
ROLLBACK TRANSACTION;
PRINT N'Rolled back the transcaction';
GO

IF…ELSE

TRansact-SQL dựa vào từ khóa IF và điều kiện thực thi chỉ khi thỏa mãn và trả về true. Từ khóa ELSE là tùy chọn trong câu lệnh T-SQl đươc thực thi khi điều kiện IF không thỏa mãn và trả về FALSE.

Cú pháp:

IF Boolean_expression {sql_statement | statement_block}
[ELSE
sql_statement | statement_block }
]

Ví dụ:

USE AdventureWorks2019;
GO
DECLARE @ListPrice money;
SET @ListPrice = (SELECT MAX(p.ListPrice) FROM Production.Product AS p
JOIN Production.ProductSubcategory AS s
ON p.ProductSubcategoryID = s.ProductSubcategoryID WHERE s.[Name] = 'Mountain Bikes');
PRINT @ListPrice
IF @ListPrice < 3000
PRINT 'All the products in this category can be purchased for an amount less than 3000'
ELSE
PRINT 'The prices for some products in this category exceed 3000' 

WHILE

Câu lệnh WHILE xác định điều kiện cho việc thực thi khối lệnh lặp lại. Câu lệnh đươc thực thi lặp lại cho đến khi điều khiện xác định trả về true. Câu lệnh thực thi trong vòng lặp WHILE có thể được điều khiển bằng từ khóa BREAK hoặc CONTINUE

Cú pháp:

WHILE Boolean_expression
{sql_statement | statement_block | BREAK | CONTINUE}

Ví dụ:

DECLARE @flag 
int SET @flag = 10 
WHILE (@flag <= 95) 
BEGIN
IF @flag % 2 = 0 
PRINT @flag
SET @flag = @flag + 1
CONTINUE;
print 'continue not work'
END;
GO

CASE WHEN

Cú pháp:

SELECT column_list, CASE column_name WHEN value1 then display1
WHEN value2 then display2
...
ELSE display
END [AS alias]
FROM relative_table [WHERE condition];

Ví dụ

USE AdventureWorks2019;
GO
select Person.Person.*, prefix = case Title 
when 'Mr.' then 'Anh' 
when 'Ms.' then 'Chi' 
else 'Anh/chi' end from Person.Person;