Excel Easy 1: Hướng dẫn

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:

1

2

3

4

5

6

-- Đị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ụ:

-- 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.

1

2

3

4

5

6

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:

1

2

3

4

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:

1

2

3

Select * from @v_Table

Where Dept_ID = 10

Order by First_Name;

Ví dụ:

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 #.

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:

-- 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ỏ

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 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)

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 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.

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.