Saturday, July 20, 2019

P2. Hướng dẫn lập trình SQL Server Transact-SQL

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ụ:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 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ụ:
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
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 #.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
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:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 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.
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àmMô 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.



at
Labels: , , , ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Over 50 Ebooks Excel and VBA free Download

1. Statistics and Probability for Engineering Applications With Microsoft Excel by W.J. DeCoursey - PDF Free Download Download Siz...