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 #.
- 12345678910111213141516171819202122
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. 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ể truy cập tất cả các dòng dữ liệu.
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ỏ
- 123456789101112131415161718192021
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)
- 123456789101112131415161718192021222324252627282930313233343536373839404142434445
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 xử lý các tình huống này.
Hãy xem một ví dụ đơn giản, xử lý lỗi chia cho 0.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
| 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 các tham số, chẳng hạn như độ dài, tên đối tượng, hoặc thời gian. |
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. |