Những thói quen tốt khi viết câu lệnh T-SQL- P5

Chia sẻ: Cong Thanh | Ngày: | Loại File: PDF | Số trang:5

Thêm vào BST

Báo xấu

130
lượt xem 23
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Tham khảo tài liệu 'những thói quen tốt khi viết câu lệnh t-sql- p5', công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Những thói quen tốt khi viết câu lệnh T-SQL- P5

SalesOrderID = @SalesOrderID ' IF @SalesOrderDetailID IS NOT NULL SET @WHERE = @WHERE + 'AND SalesOrderDetailID = @SalesOrderDetailID ' IF @CarrierTrackingNumber IS NOT NULL SET @WHERE = @WHERE + 'AND CarrierTrackingNumber = @CarrierTrackingNumber ' IF LEN(@WHERE) > 0 SET @CMD = @CMD + ' WHERE ' + RIGHT(@WHERE,LEN(@WHERE) - 3) EXEC sp_executesql @CMD , N'@SalesOrderID int ,@SalesOrderDetailID int
,@CarrierTrackingNumber nvarchar(25)' ,@SalesOrderID = @SalesOrderID ,@SalesOrderDetailID = @SalesOrderDetailID ,@CarrierTrackingNumber = @CarrierTrackingNumber Tiếp theo chúng ta sẽ đi sâu vào chi tiết nhằm giúp bạn hiểu rõ phần động và phần thông số hóa của đoạn mã trên. SP này bắt đầu bằng việc gán biến @CMD vào câu lệnh SELECT không có mệnh đề WHERE. Tiếp theo ta gán biến @WHERE cho một chuỗi rỗng. Tiếp đó là bốn câu IF khác nhau. Ba câu IF đầu tiên kiểm tra xem mỗi tham số truyền vào có thỏa mãn điều kiện NOT NULL hay không. Nếu một tham số NOT NULL, ta sẽ gắn điều kiện vào biến @WHERE
đối với tham số ấy. Do ta đã kiểm tra và xác định tham số đó NOT NULL, ta không cần thêm điều kiện IS NULL vào mệnh đề WHERE như đoạn code SP ban đầu ở phần trên. Thay vào đó, tất cả những gì ta cần là thêm điều kiện = @PARM vào biến @WHERE. Câu If cuối cùng xác định xem biến @WHERE có thỏa mãn ít nhất một điều kiện hay không, và nếu có thì nó sẽ nối biến @WHERE với biến @CMD. Lưu ý rằng biến @WHERE là phần động của đoạn code. Nhưng tôi không đặt phần text thực của tham số vào biến @WHERE, thay vào đó chỉ đặt một tham chiếu tới các tham số trong điều kiện WHERE. Vì thế câu lệnh T-SQL động cơ bản chỉ bao gồm câu lệnh SELECT ban đầu và mệnh đề WHERE không còn cần điều kiện IS NULL để ràng buộc dữ liệu nữa. Cuối cùng tôi sử dụng SP “sp_executesql” để thực thi câu lệnh T-SQL động thông số hóa. Để thực hiện
điều này, tôi truyền năm tham số vào SP hệ thống. Tham số đầu tiên là biến T-SQL động @CMD. Tham số thứ hai khai báo tất cả các biến có thể có trong đoạn truy vấn được thông số hóa, cùng với loại dữ liệu của chúng. Với ba tham số cuối cùng, chúng chỉ được truyền vào SP hệ thống giống như chúng được truyền vào SP lưu trữ đa năng trong phần đầu. Như bạn có thể thấy, tôi hoàn toàn không làm đoạn mã SQL động của mình trở nên dễ bị tấn công bằng SQL injection hơn SP ban đầu. Lý do là vì tôi không sử dụng giá trị thực của tham số để chuyển tới biến @WHERE. Tôi chỉ truyền tham số như các biến vào SQL động qua SP hệ thống “sp_executesql”. Bây giờ hãy chạy đoạn code tạo thủ tục lưu trữ đa năng mới viết lại bằng cách chạy câu lệnh sau: EXEC JackOfAllTrades_V2 @SalesOrderID = 43659
Khi chạy thử nghiệm với cơ sở dữ liệu AdventureWorks trên server, tôi nhận được sơ đồ thực thi như sau: Khi so sánh sơ đồ này với sơ đồ ở phần đầu, bạn có thể thấy nó đơn giản hơn và sử dụng thao tác “tìm kiếm chỉ mục cụm” để xử lý SP. Sở dĩ máy chủ SQL có thể sử dụng thao tác này là vì đoạn code SQL động không còn điều kiện “@PARM IS NULL” nữa. Do câu lệnh T-SQL đã được đơn giản hóa nhờ sử dụng SQL động và loại bỏ ràng buộc IS NULL, máy chủ SQL giờ đây có thể đưa ra kế hoạch thực thi tối ưu hơn cho thủ tục lưu trữ đa năng phiên bản V2. Kết lại, thực tế thì hiệu quả chúng ta thu được ở mức nào? Nên nhớ trên đây ta chỉ mới xét những bản ghi từ bảng SalesOrderDetail có SalesOrderID bằng 43659. SP lưu trữ đa năng ban đầu sử dụng thao tác