Quick Link
- 1- Transact-SQL là gì
- 2- Tổng quan về Transact-SQL
- 3- Bắt đầu với SQL Server Management Studio
- 4- Các lệnh Transact-SQL cơ bản
- 5- Gán dữ liệu truy vấn vào biến
- 6- Các kiểu dữ liệu đặc biệt trong T-SQL
- 7- Con trỏ (Cursor)
-
- 7.1- Con trỏ là gì?
- 7.2- Khai báo con trỏ
- 7.3- Ví dụ với Con trỏ
- 7.4- Ví dụ sử dụng Con trỏ (Khai báo dạng biến)
- 8- Điều khiển ngoại lệ
- 9- Hàm (Function)
- 10- Thủ tục (Procedure)
- 11- Sử lý giao dịch (Transaction)
- 12- Trigger
Hướng dẫn lập trình SQL Server Transact-SQL
- 1- Transact-SQL là gì
- 2- Tổng quan về Transact-SQL
- 3- Bắt đầu với SQL Server Management Studio
- 4- Các lệnh Transact-SQL cơ bản
- 5- Gán dữ liệu truy vấn vào biến
- 6- Các kiểu dữ liệu đặc biệt trong T-SQL
- 7- Con trỏ (Cursor)
-
- 7.1- Con trỏ là gì?
- 7.2- Khai báo con trỏ
- 7.3- Ví dụ với Con trỏ
- 7.4- Ví dụ sử dụng Con trỏ (Khai báo dạng biến)
- 8- Điều khiển ngoại lệ
- 9- Hàm (Function)
- 10- Thủ tục (Procedure)
- 11- Sử lý giao dịch (Transaction)
- 12- Trigger
-
1- Transact-SQL là gì
-
Transact-SQL (còn gọi là T-SQL) là một ngôn ngữ lập trình database hướng thủ tục độc quyền của Microsoft sử dụng trong SQL Server.
Ngôn ngữ thủ tục được thiết kế để mở rộng khả năng của SQL trong khi có khả năng tích hợp tốt với SQL. Một số tính năng như các biến địa phương và xử lý chuỗi/dữ liệu được thêm vào. Các tính năng này làm cho ngôn ngữ Transact-SQL là Turing-complete (**).
Chúng cũng được sử dụng để viết các thủ tục lưu trữ: Một đoạn code nằm trên máy chủ để quản lý các quy tắc kinh doanh phức tạp mà khó hoặc không thể làm nổi với các thao tác tập hợp thông thường (pure set-based operations). -
Một hệ thống Turing-Complete có nghĩa là một hệ thống trong đó một chương trình có thể được viết ra và sẽ tìm thấy câu trả lời (mặc dù không có sự bảo đảm về thời gian chạy hoặc bộ nhớ).
-
2- Tổng quan về Transact-SQL
-
T-SQL tổ chức theo từng khối lệnh, một khối lệnh có thể lồng bên trong một khối lệnh khác, một khối lệnh bắt đầu bởi BEGIN và kết thúc bởi END, bên trong khối lệnh có nhiều lệnh, và các lệnh ngăn cách nhau bởi dấu chấm phẩy.
-
Cấu trúc khối lệnh:
-
1234
BEGIN
-- Khai báo biến
-- Các câu lệnh T-SQL
END
;
-
3- Bắt đầu với SQL Server Management Studio
-
Trong tài liệu này tôi sẽ hướng dẫn bạn lập trình SQL Server, trên công cụ trực quan SQL Server Management Studio.
-
Đây là hình ảnh SQL Server Management Studio trong khi bạn mới mở nó. Có một số database ví dụ có sẵn khi bạn cài đầy đủ SQLServer.
-
-
Hoặc bạn có thể tạo database learningsql, một database ví dụ được sử dụng trong một vài tài liệu hướng dẫn SQLServer trên o7planning.org.
-
Nhấn phải chuột vào một database, chọn "New Query" để mở ra một cửa sổ làm việc với database này.
-
-
Bạn đã sẵn sàng với lập trình database với SQL Server.
-
-
Dưới đây là một khối lệnh đơn giản, tính tổng 2 số:
-
12345678910111213141516171819202122232425
Begin
-- Khai báo một biến
Declare
@v_Result
Int
;
-- Khai báo một biến có giá trị 50
Declare
@v_a
Int
= 50;
-- Khai báo một biến có giá trị 100
Declare
@v_b
Int
= 100;
-- In ra màn hình Console (Dùng cho lập trình viên).
-- Sử dụng Cast để ép kiểu Int về kiểu chuỗi.
-- Sử dụng toán tử + để nối 2 chuỗi.
Print
'v_a= '
+
Cast
(@v_a
as
varchar
(15));
-- In ra màn hình Console
Print
'v_b= '
+
Cast
(@v_b
as
varchar
(15));
-- Tính tổng
Set
@v_Result = @v_a + @v_b;
-- In ra màn hình Console
Print
'v_Result= '
+
Cast
(@v_Result
as
varchar
(15));
End
;
-
Nhấn biểu tượng để thực thi khối lệnh, và xem kết quả trên SQL Server Management Studio:
-
-
4- Các lệnh Transact-SQL cơ bản
-
Ở đây tôi giới thiệu tổng quan về các lệnh cơ bản của T-SQL. Bạn sẽ hiểu hơn về nó thông qua các ví dụ ở các phần tiếp theo.
-
-
4.1- Câu Lệnh rẽ nhánh If-elsif-else
-
Cú pháp:
-
123456789
IF <điều kiện 1>
Khối lệnh 1;
[
ELSE
IF <điều kiện 2>
Khối lệnh 2;
]
....
[
ELSE
Khối lệnh n + 1;
]
-
Ví dụ:
-
Else_If_Example12345678910111213141516171819202122232425
BEGIN
-- Khai báo một biến
DECLARE
@v_Option
integer
;
DECLARE
@v_Action
varchar
(30);
SET
@v_Option = 2;
IF @v_Option = 1
SET
@v_Action =
'Run'
;
ELSE
IF @v_Option = 2
BEGIN
PRINT
'In block else if @v_Option = 2'
;
SET
@v_Action =
'Backup'
;
END
;
ELSE
IF @v_Option = 3
SET
@v_Action =
'Stop'
;
ELSE
SET
@v_Action =
'Invalid'
;
-- Ghi ra log
PRINT
'@v_Action= '
+ @v_Action;
END
;
-
Kết quả chạy ví dụ:
-
-
4.2- Vòng lặp WHILE
-
Cú pháp:
-
1234
WHILE condition
BEGIN
-- ...statements...
END
;
-
Trong vòng lặp WHILE bạn có thể sử dụng BREAK để thoát ra khỏi vòng lặp.
Sử dụng lệnh CONTINUE để bỏ qua các dòng lệnh trong khối WHILE và ở bên dưới nó, để tiếp tục một vòng lặp mới. -
While_Example11234567891011121314151617181920212223242526272829
BEGIN
-- Khai báo 2 biến x và y.
DECLARE
@x
integer
= 0;
DECLARE
@y
integer
= 10;
-- Bước
DECLARE
@step
integer
= 0;
-- Trong khi @x < @y
WHILE (@x < @y)
BEGIN
SET
@step = @step + 1;
-- Mỗi lần vòng lặp chạy giá trị của x tăng lên 1
SET
@x = @x + 1;
-- Mỗi lần vòng lặp chạy giá trị của y giảm đi 2
SET
@y = @y - 2;
PRINT
'Step ='
+
CAST
(@step
AS
varchar
(10));
PRINT
'@x ='
+
CAST
(@x
AS
varchar
(10)) +
' / @y = '
+
CAST
(@y
AS
varchar
(10));
END
;
-- Ghi ra log
PRINT
'x,y = '
+
CAST
(@x
AS
varchar
(10)) +
', '
+
CAST
(@y
AS
varchar
(10));
END
;
-
Kết quả chạy ví dụ:
-
-
BREAK là lệnh cho phép thoát ra khỏi vòng lặp, dưới đây là ví dụ:
-
While_Example212345678910111213141516171819202122232425262728293031323334
BEGIN
-- Khai báo 2 biến x và y.
DECLARE
@x
integer
= 0;
DECLARE
@y
integer
= 10;
-- Bước
DECLARE
@step
integer
= 0;
-- Trong khi @x < @y
WHILE (@x < @y)
BEGIN
SET
@step = @step + 1;
-- Mỗi lần vòng lặp chạy giá trị của x tăng lên 1
SET
@x = @x + 1;
-- Mỗi lần vòng lặp chạy giá trị của y giảm đi 2
SET
@y = @y - 2;
PRINT
'Step ='
+
CAST
(@step
AS
varchar
(10));
PRINT
'@x ='
+
CAST
(@x
AS
varchar
(10)) +
' / @y = '
+
CAST
(@y
AS
varchar
(10));
-- Nếu @x > 2 thì thoát ra khỏi vòng lặp
-- (Mặc dù điều kiện trong WHILE vẫn đúng).
IF @x > 2
BREAK;
END
;
-- Ghi ra log
PRINT
'x,y = '
+
CAST
(@x
AS
varchar
(10)) +
', '
+
CAST
(@y
AS
varchar
(10));
END
;
-
Kết quả chạy ví dụ:
-
-
Lệnh CONTINUE cho phép bỏ qua các câu lệnh bên dưới nó trong vòng lặp để tiếp tục vòng lặp mới.
-
While_Example31234567891011121314151617181920212223242526272829303132333435
BEGIN
-- Khai báo 2 biến x và y.
DECLARE
@x
integer
= 0;
DECLARE
@y
integer
= 10;
-- Bước
DECLARE
@step
integer
= 0;
-- Trong khi @x < @y
WHILE (@x < @y)
BEGIN
SET
@step = @step + 1;
-- Mỗi lần vòng lặp chạy giá trị của x tăng lên 1
SET
@x = @x + 1;
-- Mỗi lần vòng lặp chạy giá trị của y giảm đi 2
SET
@y = @y - 2;
-- Nếu @x < 3 thì bỏ qua các dòng lệnh bên dưới
-- Tiếp tục vòng lặp mới.
IF @x < 3
CONTINUE
;
-- Nếu @x < 3 các dòng lệnh bên dưới CONTINUE sẽ không được chạy.
PRINT
'Step ='
+
CAST
(@step
AS
varchar
(10));
PRINT
'@x ='
+
CAST
(@x
AS
varchar
(10)) +
' / @y = '
+
CAST
(@y
AS
varchar
(10));
END
;
-- Ghi ra log
PRINT
'x,y = '
+
CAST
(@x
AS
varchar
(10)) +
', '
+
CAST
(@y
AS
varchar
(10));
END
;
-
-
5- Gán dữ liệu truy vấn vào biến
-
Các biến có thể được gán giá trị từ một câu truy vấn. Dưới đây là một ví dụ minh họa:
-
Assign_Value_Example1234567891011121314151617181920212223
BEGIN
-- Khai báo một biến @v_Emp_ID
DECLARE
@v_Emp_ID
integer
= 1;
DECLARE
@v_First_Name
varchar
(30);
DECLARE
@v_Last_Name
varchar
(30);
DECLARE
@v_Dept_ID
integer
;
-- Gán giá trị cho các biến lấy từ câu lệnh Select.
SELECT
@v_First_Name = emp.First_Name,
@v_Last_Name = emp.Last_Name,
@v_Dept_Id = emp.Dept_Id
FROM
Employee Emp
WHERE
Emp.Emp_ID = @v_Emp_Id;
-- In ra các giá trị:
PRINT
'@v_First_Name = '
+ @v_First_Name;
PRINT
'@v_Last_Name = '
+ @v_Last_Name;
PRINT
'@v_Dept_Id = '
+
CAST
(@v_Dept_ID
AS
varchar
(15));
END
;
-
Kết quả chạy ví dụ:
-
-
6- Các kiểu dữ liệu đặc biệt trong T-SQL
-
-
6.1- Kiểu dữ liệu TABLE (Dạng tường minh)
-
T-SQL cho phép bạn khai báo một biến có kiểu dữ liệu TABLE.
-
Cú pháp:
-
123456
-- Định nghĩa một biến có kiểu TABLE.
-- Chú ý: Các giàng buộc cũng có thể tham gia vào khai báo biến kiểu TABLE (Xem trong ví dụ).
Declare
@v_variable_name
TABLE
(
Column1 DataType1,
Column2 DataType2
);
-
Ví dụ:
-
12345678910111213141516
-- Ví dụ khai báo một biến có kiểu TABLE.
Declare
@v_Table
TABLE
(
First_Name
Varchar
(30),
Last_Name
Varchar
(30),
Dept_ID
Integer
,
Salary
Float
);
-- Các giàng buộc cũng có thể tham gia vào trong định nghĩa biến kiểu TABLE:
-- Ví dụ:
Declare
@v_table
TABLE
(
Product_ID
Integer
IDENTITY(1,1)
PRIMARY
KEY
,
Product_Name DataType2
NOT
NULL
Default
(
'Unknown'
),
Price Money
CHECK
(Price < 10.0)
);
-
Ví dụ: Insert dữ liệu vào biến có kiểu TABLE.
-
123456
Insert
Into
@v_Table (First_Name, Last_Name, Dept_ID, Salary)
Select
Emp.First_Name, Emp.Last_Name, Emp.Dept_Id, 1000
From
Employee Emp
Where
Emp.Emp_ID < 4;
-
Bạn cũng có thể Update trên biến có kiểu TABLE:
-
1234
Update
@v_Table
Set
Salary = Salary + 100
Where
Dept_Id = 10;
-
Delete trên biến có kiểu TABLE:
-
1
Delete
From
@v_Table
Where
Dept_ID = 10;
-
Query dữ liệu trên biến có kiểu TABLE:
-
123
Select
*
from
@v_Table
Where
Dept_ID = 10
Order
by
First_Name;
-
Ví dụ:
-
1234567891011121314151617181920212223242526272829303132
BEGIN
DECLARE
@v_Emp_ID
integer
= 1;
-- Khai báo một biến kiểu TABLE.
DECLARE
@v_Table
TABLE
(
First_Name
varchar
(30),
Last_Name
varchar
(30),
Dept_Id
integer
,
Salary
float
DEFAULT
1000
);
-- Sử dụng INSERT INTO để trèn dữ liệu vào @v_Table.
INSERT
INTO
@v_Table (First_name, Last_Name, Dept_ID)
SELECT
emp.First_Name,
emp.Last_Name,
emp.Dept_Id
FROM
Employee Emp
WHERE
Emp.Emp_ID < 4;
-- Update @v_Table
UPDATE
@v_Table
SET
Salary = Salary + 100
WHERE
First_name =
'Susan'
;
-- Duyệt các giá trị trên @v_Table.
SELECT
*
FROM
@v_Table;
END
;
-
Kết quả chạy ví dụ:
-
-
6.2- Kiểu dữ liệu TABLE (Dạng không tường minh)
-
T-SQL cho phép bạn khai báo một biến kiểu TABLE không tường minh. Tên biến bắt đầu bởi #.
-
Table_Example12345678910111213141516171819202122
BEGIN
-- Sử dụng SELECT INTO để trèn dữ liệu vào #v_My_Table.
SELECT
emp.First_Name,
emp.Last_Name,
emp.Dept_Id,
1000 Salary
INTO
#v_My_Table
FROM
Employee Emp
WHERE
Emp.Emp_ID < 4;
-- Update #v_My_Table
UPDATE
#v_My_Table
SET
Salary = Salary + 100
WHERE
First_name =
'Susan'
;
-- Duyệt các giá trị trên #v_My_Table.
SELECT
*
FROM
#v_My_Table;
END
;
-
Kết quả chạy ví dụ:
-
-
-
7- Con trỏ (Cursor)
-
-
7.1- Con trỏ là gì?
-
Cursor là kiểu biến có cấu trúc, cho phép bạn xử lý dữ liệu gồm nhiều dòng. Số dòng phụ thuộc vào câu lệnh truy vấn dữ liệu sau nó. Trong quá trình xử lý, bạn có thể thao tác với cursor thông qua từng dòng dữ liệu. Dòng dữ liệu này được định vị bởi một con trỏ. Với việc dịch chuyển con trỏ, bạn có thể lấy được toàn bộ dữ liệu của một dòng hiện tại.
-
7.2- Khai báo con trỏ
-
Cú pháp:
-
123456789101112131415
-- ISO Syntax
DECLARE
cursor_name [
INSENSITIVE
] [
SCROLL
]
CURSOR
FOR
select_statement
[
FOR
{
READ
ONLY
|
UPDATE
[
OF
column_name [ ,...n ] ] } ]
[;]
-- Transact-SQL Extended Syntax
DECLARE
cursor_name
CURSOR
[
LOCAL
|
GLOBAL
]
[ FORWARD_ONLY |
SCROLL
]
[
STATIC
| KEYSET |
DYNAMIC
| FAST_FORWARD ]
[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
[ TYPE_WARNING ]
FOR
select_statement
[
FOR
UPDATE
[
OF
column_name [ ,...n ] ] ]
[;]
-
7.3- Ví dụ với Con trỏ
-
Cursor_Example123456789101112131415161718192021222324252627282930313233343536373839404142434445
USE learningsql;
BEGIN
--
-- Khai báo biến:
DECLARE
@v_Emp_ID
integer
;
DECLARE
@v_First_Name
varchar
(50);
DECLARE
@v_Last_Name
varchar
(50);
DECLARE
@v_Count
integer
;
-- Khai báo một con trỏ (CURSOR).
DECLARE
My_Cursor
CURSOR
FOR
SELECT
Emp.EMP_ID,
Emp.FIRST_NAME,
Emp.LAST_NAME
FROM
Employee Emp
WHERE
Emp.EMP_ID < 3;
-- Mở Cursor
OPEN
My_Cursor;
-- Di chuyển con trỏ từ đến dòng đầu tiên.
-- Và gán các giá trị cột vào các biến.
FETCH
NEXT
FROM
My_Cursor
INTO
@v_Emp_ID, @v_First_Name, @v_Last_Name;
-- Trong trường hợp còn bản ghi @@FETCH_STATUS = 0.
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT
'First Name = '
+ @v_First_Name+
' / Last Name = '
+ @v_Last_Name;
-- Di chuyển đến bản ghi tiếp theo.
-- Và gán các giá trị cột vào các biến.
FETCH
NEXT
FROM
My_Cursor
INTO
@v_Emp_ID, @v_First_Name, @v_Last_Name;
END
-- Đóng Cursor.
CLOSE
My_Cursor;
DEALLOCATE
My_Cursor;
END
;
-
Kết quả chạy ví dụ:
-
-
7.4- Ví dụ sử dụng Con trỏ (Khai báo dạng biến)
-
Cursor_Example2123456789101112131415161718192021222324252627282930313233343536373839404142434445
USE learningsql;
BEGIN
--
-- Khai báo biến:
DECLARE
@v_Emp_ID
integer
;
DECLARE
@v_First_Name
varchar
(50);
DECLARE
@v_Last_Name
varchar
(50);
-- Khai báo một biến kiểu con trỏ.
DECLARE
@My_Cursor
CURSOR
;
-- Sét câu lệnh truy vấn cho con trỏ.
Set
@My_Cursor =
CURSOR
FOR
SELECT
Emp.EMP_ID,
Emp.FIRST_NAME,
Emp.LAST_NAME
FROM
Employee Emp
WHERE
Emp.EMP_ID < 3;
-- Mở Cursor
OPEN
@My_Cursor;
-- Di chuyển con trỏ đến dòng đầu tiên.
-- Và gán các giá trị cột vào các biến.
FETCH
NEXT
FROM
@My_Cursor
INTO
@v_Emp_ID, @v_First_Name, @v_Last_Name;
-- Trong trường hợp có bản ghi @@FETCH_STATUS = 0.
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT
'First Name = '
+ @v_First_Name+
' / Last Name = '
+ @v_Last_Name;
-- Di chuyển đến bản ghi tiếp theo.
-- Và gán các giá trị cột vào các biến.
FETCH
NEXT
FROM
@My_Cursor
INTO
@v_Emp_ID, @v_First_Name, @v_Last_Name;
END
-- Đóng Cursor.
CLOSE
@My_Cursor;
DEALLOCATE
@My_Cursor;
END
;
-
Kết quả chạy ví dụ:
-
-
-
8- Điều khiển ngoại lệ
-
Khi lập trình T-SQL có thể có những lỗi xuất hiện trong Code của bạn, chẳng hạn như lỗi chia cho 0. Hoặc lỗi khi bạn trèn một bản ghi trùng lặp khóa chính, ... Bạn cần phải sử lý các tình huống này.
-
Hãy xem một ví dụ đơn giản, sử lý lỗi chia cho 0.
-
TryCatch_Example1234567891011121314151617181920212223242526272829303132333435363738394041424344454647
USE learningsql;
BEGIN
--
-- Khai báo biến:
DECLARE
@v_a
float
= 20;
DECLARE
@v_b
float
= 0;
DECLARE
@v_c
float
;
DECLARE
@v_Error_Number
integer
;
-- Sử dụng BEGIN TRY .. END TRY để bẫy lỗi.
-- Nếu lỗi xẩy ra trong khối này
-- nó sẽ nhẩy vào khối BEGIN CATCH .. END CATCH.
BEGIN
TRY
---
PRINT
'@v_a = '
+
CAST
(@v_a
AS
varchar
(15));
PRINT
'@v_b = '
+
CAST
(@v_b
AS
varchar
(15));
--Lỗi chia cho 0 xẩy ra tại đây.
SET
@v_c = @v_a / @v_b;
-- Dòng bên dưới này sẽ không được chạy.
-- Chương trình nhẩy vào khối BEGIN CATCH .. END CATCH
PRINT
'@v_c= '
+
CAST
(@v_c
AS
varchar
(15));
END
TRY
-- BEGIN CATCH .. END CATCH phải được đặt ngay
-- phía sau của khối BEGIN TRY .. END TRY.
BEGIN
CATCH
-- Mã lỗi.
SET
@v_Error_Number = ERROR_NUMBER();
-- In ra mã lỗi:
PRINT
'Error Number: '
+
CAST
(@v_Error_Number
AS
varchar
(15));
-- Nguyên nhân lỗi:
PRINT
'Error Message: '
+ ERROR_MESSAGE();
-- Mức độ nghiêm trọng của lỗi:
PRINT
'Error Severity: '
+
CAST
(ERROR_SEVERITY()
AS
varchar
(15));
-- Mã trạng thái:
PRINT
'Error State: '
+
CAST
(ERROR_STATE()
AS
varchar
(15));
-- Dòng bị lỗi:
PRINT
'Error Line: '
+
CAST
(ERROR_LINE()
AS
varchar
(15));
-- Tên của thủ tục (hoặc function) hoặc trigger, có code gây ra lỗi này.
PRINT
'Error Procedure: '
+ ERROR_PROCEDURE();
END
CATCH;
END
;
-
Kết quả chạy ví dụ:
-
-
Thông tin lỗi:
-
Hàm Mô tả ERROR_NUMBER() Trả về mã lỗi. ERROR_MESSAGE() Trả về văn bản đầy đủ của các thông báo lỗi. Các văn bản bao gồm các giá trị cung cấp cho bất kỳ tham số thay thế chẳng hạn như độ dài, tên đối tượng, hoặc lần. ERROR_SEVERITY() Trả về mức độ nghiêm trọng của lỗi. ERROR_STATE() Trả về trạng thái lỗi ERROR_LINE() Trả về số vị trí dòng code gây lỗi. ERROR_PROCEDURE() Trả về tên của stored procedure hoặc trigger nơi mà lỗi phát ra. -
9- Hàm (Function)
-
Giống như thủ tục, hàm (function) là nhóm các lệnh T-SQL thực hiện chức năng nào đó. Khác với thủ tục, các hàm sẽ trả về một giá trị ngay tại lời gọi của nó.
Hàm cũng có thể được lưu giữ ngay trên database dưới dạng Store procedure. -
Cú pháp tạo Hàm.
-
1234567891011121314151617181920
-- function_name: Tên hàm
-- argument: Tên tham số
-- mode: INPUT, OUTPUT, hoặc không cần viết.
-- datatype: Kiểu dữ liệu của tham số
CREATE
FUNCTION
<function_name>
(
[
@argument1 datatype1 [mode1] ,
@argument2 datatype2 [mode2],
...
]
)
RETURNS
datatype
AS
BEGIN
-- Khai báo biến sử dụng
-- Code nội dung của hàm
-- Trả về giá trị của hàm.
END
;
-
Ví dụ:
-
123456789101112131415
-- Một hàm có tham số:
CREATE
FUNCTION
Sum_Ab(a
Integer
, b
Integer
)
RETURNS
Integer
AS
Begin
return
a + b;
End
;
-- Một hàm không tham số:
CREATE
FUNCTION
Get_Current_Datetime()
RETURNS
Date
AS
Begin
return
CURRENT_TIMESTAMP
;
End
;
-
Hủy Function:
-
1234567
-- Hủy Function
DROP
FUNCTION
<function_name>;
-- Ví dụ:
DROP
FUNCTION
My_Function;
-
Ví dụ tạo một hàm.
-
Đây là một ví dụ tạo một hàm đầu tiên của bạn với SQL Server:
- Tạo một hàm (Function)
- Biên dịch hàm này
- Chạy hàm
-
123456789101112131415161718192021
-- Kiểm tra xem Hàm này đã tồn tại chưa.
-- Nếu đã tồn tại cần phải hủy để có thể tạo mới.
IF OBJECT_ID(N
'dbo.My_Sum'
, N
'FN'
)
IS
NOT
NULL
DROP
FUNCTION
My_Sum;
GO
CREATE
FUNCTION
My_Sum (@p_a
float
, @p_b
float
)
RETURNS
float
AS
BEGIN
-- Khai báo một biến Float
DECLARE
@v_C
float
;
-- Sét giá trị cho biến v_C
SET
@V_C = @p_A + @p_B;
-- Giá trị trả về của hàm.
RETURN
@v_C;
END
;
-
-
Nhấn biểu tượng để biên dịch hàm.
-
Hàm bạn vừa tạo ra ở trên là một hàm đơn giản trả về một giá trị vô hướng (Scalar-value). Bạn có thể nhìn thấy nó đã được tạo ra trên SQLServer Management Studio:
-
-
Bạn có thể test hàm bằng cách nhấn phải chuột vào hàm, chọn:
-
- Script function as -> SELECT to -> New Query Editor Window
-
-
Một cửa sổ test được mở ra, bạn có thể sửa đổi các tham số truyền vào:
-
-
Sửa đổi các giá trị tham số truyền vào và nhấn nút thực thi để test.
-
-
Các hàm có thể tham gia vào trong câu lệnh SELECT.
-
1234567
SELECT
acc.account_id,
acc.cust_id,
acc.avail_balance,
acc.pending_balance,
dbo.MY_SUM(acc.avail_balance, acc.pending_balance) balance
FROM
account acc;
-
Kết quả chạy câu lệnh SQL trên:
-
-
10- Thủ tục (Procedure)
-
Một nhóm các lệnh T-SQL thực hiện chức năng nào đó có thể được gom lại trong một thủ tục (procedure) nhằm làm tăng khả năng xử lý,khả năng sử dụng chung,tăng tính bảo mật và an toàn dữ liệu,tiện ích trong phát triển.
Thủ tục có thể được lưu giữ ngay trong database như một đối tượng của database, sẵn sàng cho việc tái sử dụng. Thủ tục lúc này được gọi là Store procedure. Với các Store procedure, ngay khi lưu giữ Store procedure, chúng đã được biên dịch thành dạng p-code vì thế có thể nâng cao khả năng thực hiện. -
Thủ tục không trả về giá trị trực tiếp như hàm. Tuy nhiên nó có thể có 0 hoặc nhiều tham số đầu ra.
-
Cú pháp tạo một thủ tục:
-
123456789101112131415161718192021222324252627282930313233
-- procedure_name: Tên thủ tục
-- argument: Tên tham số
-- mode: Loại tham số: INPUT hoặc OUTPUT, mặc định là INPUT
-- datatype: Kiểu dữ liệu của tham số
-- Chú ý: Với thủ tục các tham số có thể đặt trong dấu () hoặc không cần thiết.
CREATE
PROCEDURE
<procedure_name>
[
argument1 datatype1 [mode1] ,
argument2 datatype2 [mode2] ,
...
]
AS
BEGIN
-- Khai báo biến sử dụng
-- Nội dung của thủ tục.
END
;
-- Hoặc:
CREATE
PROCEDURE
<procedure_name>
(
[
argument1 datatype1 [mode1] ,
argument2 datatype2 [mode2] ,
...
]
)
AS
BEGIN
-- Khai báo biến sử dụng
-- Nội dung của thủ tục.
END
;
-
Ví dụ:
-
1234567891011121314151617181920212223
-- Ví dụ một thủ tục không tham số.
CREATE
Procedure
Do_Something
AS
Begin
-- Khai báo biến tại đây.
Declare
@v_a
Integer
;
-- Làm gì đó tại đây.
-- ....
End
;
-- Ví dụ một thủ tục có tham số
-- Có một tham số đầu vào và một tham số đầu ra.
CREATE
Procedure
Do_Something (@p_Param1
Varchar
(20),
@v_Param2
Varchar
(50)
OUTPUT
)
AS
Begin
-- Khai báo biến tại đây.
Declare
@v_a
Integer
;
-- Làm gì đó tại đây.
-- ...
End
;
-
Hủy thủ tục:
-
123
-- Hủy một thủ tục:
DROP
PROCEDURE
<Procedure_Name>
-
Các bước thực hiện một thủ tục:
-
-
Ví dụ tạo một thủ tục:
-
Ở đây tôi tạo một thủ tục đơn giản, với tham số truyền vào là @p_Emp_ID và có 3 tham số đầu ra, @v_First_Name, @v_Last_Name, @v_Dep_ID.
-
Get_Employee_Infos12345678910111213141516171819202122232425262728293031323334353637
-- Huỷ procedure Get_Employee_Infos nếu nó đã tồn tại.
-- (Để cho phép tạo lại)
IF OBJECT_ID(N
'dbo.Get_Employee_Infos'
, N
'P'
)
IS
NOT
NULL
DROP
PROCEDURE
Get_Employee_Infos;
GO
-- Thủ tục truyền vào p_Emp_Id
-- Và trả về v_First_Name, v_Last_Name, v_Dept_Id.
CREATE
PROCEDURE
Get_Employee_Infos (@p_Emp_Id
integer
, @v_First_Name
varchar
(50)
OUTPUT
, @v_Last_Name
varchar
(50)
OUTPUT
, @v_Dept_Id
integer
OUTPUT
)
AS
BEGIN
-- Sử dụng lệnh Print để in ra một chuỗi (Dành cho lập trình viên).
-- Sử dụng Cast để ép kiểu số Integer về dạng chuỗi (Varchar).
-- Sử dụng toán tử + để nối hai chuỗi.
PRINT
'Parameter @p_Emp_Id = '
+
CAST
(@p_Emp_ID
AS
varchar
(15));
--
-- Query dữ liệu từ bảng và gán giá trị vào các biến.
--
SELECT
@v_First_Name = Emp.First_Name,
@v_Last_Name = Emp.Last_Name,
@v_Dept_Id = Emp.Dept_Id
FROM
Employee Emp
WHERE
Emp.Emp_Id = @p_Emp_Id;
--
-- Log dành cho người lập trình
--
PRINT
'Found Record!'
;
PRINT
' @v_First_Name= '
+ @v_First_Name;
PRINT
' @v_Last_Name= '
+ @v_Last_Name;
PRINT
' @v_Dept_Id= '
+
CAST
(@v_Dept_Id
AS
varchar
(15));
END
;
-
Nhấn vào biểu tượng để biên dịch thủ tục.
-
-
Thủ tục đã được tạo ra, bạn có thể nhìn thấy trên SQL Server Management Studio:
-
-
Test thủ tục
-
Trong lập trình việc test một thủ tục và dò tìm lỗi là vấn đề rất quan trọng. Nhấn phải chuột vào thủ tục muốn test, chọn:
-
- Script stored Procedure as -> EXECUTE to -> New Query Editor Window
-
-
Kịch bản test được tạo ra (Mặc định) như hình minh họa dưới đây:
-
-
Sét đặt giá trị cho các tham số đầu vào:
-
-
Nhấn nút thực thi thủ tục:
-
-
11- Sử lý giao dịch (Transaction)
-
-
11.1- Tại sao cần sử lý giao dịch
-
Giao dịch (Transaction) là một khái niệm quan trọng trong SQL. Hãy xem một tình huống:
Một giao dịch trong ngân hàng, người A chuyển cho người B một khoản tiền 100$, khi đó trong Database xẩy ra 2 thao tác:- Trừ tiền của người A đi 100$
- Cộng tiền vào cho người B 100$.
-
Xem một ví dụ khác:
-
Khi bạn thêm một sinh viên vào một lớp học bạn cập nhập lại sĩ số của lớp học. Nếu việc trèn thông tin sinh viên không thành công mà sĩ số lại được cộng thêm 1, tính toàn vẹn của dữ liệu bị hỏng.
-
12345678
-- Insert một Sinh viên vào bảng Student.
Insert
into
Student (Studen_Id, Student_Name, Class_ID)
values
(100,
'Tom'
, 1);
-- Cập nhập sĩ số của lớp học.
Update
Class_Table
set
Student_Count = Student_Count + 1
Where
Class_Id = 1;
-
Giao dịch được coi là thành công nếu tất cả các đơn vị lệnh thành công. Ngược lại một trong các đơn vị lệnh bị lỗi, toàn bộ giao dịch cần phải được trở về trạng thái ban đầu.
-
11.2- Khai báo và sử dụng giao dịch (Transaction)
-
Các lệnh liên quan:
- Bắt đầu transaction:
- begin tran / begin transaction
- Hoàn tất transaction:
- commit/ commit tran / commit transaction
- Quay lui transaction:
- rollback / rollback tran / rollback transaction
- Đánh dấu savepoint trong transaction: save transaction tên_savepoint
- Biến @@trancount: cho biết số transaction hiện đang thực hiện (chưa được kết thúc với rollback hay commit) trong connection hiện hành.
- Lệnh rollback tran + tên_savepoint có tác dụng quay lui giao dịch đến vị trí đặt savepoint tương ứng (không có tác dụng kết thúc transaction), các khóa (lock) được đặt khi thực hiện các thao tác nằm trong phần bị rollback sẽ được mở ra.
- Khi khai báo transaction tường minh, phải đảm bảo rằng sau đó nó được rollback hoặc commit tường minh, nếu không, transaction sẽ tiếp tục tồn tại và chiếm giữ tài nguyên, ngăn trở sự thực hiện của các transaction khác.
- Lệnh rollback chỉ có tác dụng quay lui các giao dịch trên CSDL (thêm, xóa, sửa). Các câu lệnh khác, chẳng hạn lệnh gán, sẽ không bị ảnh hưởng bởi lệnh rollback.
- Bắt đầu transaction:
-
Ví dụ:
-
Transaction_Example112345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364
BEGIN
-- Trong ví dụ này các tài khoản Account_ID = 1, 2 thực sự tồn tại trong DB
-- Trong thực tế bạn có thể viết các câu lệnh kiểm tra trước khi bắt đầu giao dịch.
--
-- Tài khoản người A (Đã đảm bảo tồn tại trong DB)
DECLARE
@Account_Id_A
integer
= 1;
-- Tài khoản người B (Đã đảm bảo tồn tại trong DB)
DECLARE
@Account_Id_B
integer
= 2;
-- Số tiền chuyển:
DECLARE
@Amount
float
= 10;
-- Giao dịch tại ngân hàng:
DECLARE
@Execute_Branch_Id
integer
= 1;
-- Ghi ra số Transaction hiện thời.
-- Thực tế lúc này chưa có giao dịch nào.
PRINT
'@@TranCount = '
+
CAST
(@@Trancount
AS
varchar
(5));
PRINT
'Begin transaction'
;
-- Bắt đầu giao dịch
BEGIN
TRAN;
-- Bẫy lỗi.
BEGIN
TRY
--
-- Trừ tiền trong tài khoản người A đi 10$ (Account_ID = 1)
UPDATE
Account
SET
AVAIL_BALANCE = AVAIL_BALANCE - @Amount
WHERE
Account_Id = @Account_Id_A;
--
-- Ghi thông tin thời điểm giao dịch vào bảng Acc_Transaction.
INSERT
INTO
ACC_TRANSACTION (TXN_DATE, FUNDS_AVAIL_DATE, TXN_TYPE_CD,
ACCOUNT_ID, AMOUNT, EXECUTION_BRANCH_ID)
VALUES
(
CURRENT_TIMESTAMP
,
CURRENT_TIMESTAMP
,
'CDT'
,
@Account_Id_A, -@Amount, @Execute_Branch_Id);
--
-- Cộng tiền vào tài khoản người B thêm 10$
UPDATE
Account
SET
AVAIL_BALANCE = AVAIL_BALANCE + @Amount
WHERE
Account_Id = @Account_Id_B;
--
-- Ghi thông tin thời điểm giao dịch vào bảng Acc_Transaction.
INSERT
INTO
ACC_TRANSACTION (TXN_DATE, FUNDS_AVAIL_DATE, TXN_TYPE_CD,
ACCOUNT_ID, AMOUNT, EXECUTION_BRANCH_ID)
VALUES
(
CURRENT_TIMESTAMP
,
CURRENT_TIMESTAMP
,
'CDT'
, @Account_Id_B,
@Amount, @Execute_Branch_Id);
-- Hoàn thành giao dịch
IF @@Trancount > 0
PRINT
'Commit Transaction'
;
COMMIT
TRAN;
END
TRY
-- Nếu có lỗi khối Catch sẽ được chạy.
BEGIN
CATCH
PRINT
'Error: '
+ ERROR_MESSAGE();
PRINT
'Error --> Rollback Transaction'
;
IF @@Trancount > 0
ROLLBACK
TRAN;
END
CATCH;
END
;
-
Kết quả chạy ví dụ:
-
-
-
12- Trigger
-
Tài liệu về Trigger được tách riêng, bạn có thể xem hướng dẫn tại:
-
- TODO
Comments
Post a Comment