Chương 11 View và Các Con Trỏ

Chia sẻ: Hoang Minh Minh | Ngày: | Loại File: PDF | Số trang:25

Thêm vào BST

Báo xấu

81
lượt xem 12
download

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

Giới thiệu Trong chương trước, chúng ta đã được học về các chỉ mục. Chúng ta cũng đã hiểu rõ về các kiểu chỉ mục cùng với các đặc trưng hoàn chỉnh và duy nhất. Chúng ta cũng đã có những kiến thức về các tác vụ như xem, xoá các chỉ mục. Các câu truy vấn được sử dụng để truy xuất dữ liệu từ một bảng.

Chủ đề:

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

Lưu

Nội dung Text: Chương 11 View và Các Con Trỏ

Chương 11 View và Các Con Trỏ Mục đích: Hoàn tất chương này, bạn có thể  Định nghĩa view  Hiểu được các ưu điểm của View  Tạo View  Sửa đổi View  Xoá view  Định nghĩa con trỏ và quá trình tạo con trỏ  Hiểu rõ View chỉ mục và View phân tán  Nắm vững quá trình thực thi con trỏ  Nhận về dữ liệu từ con trỏ theo hàng Giới thiệu Trong chương trước, chúng ta đã được học về các chỉ mục. Chúng ta cũng đã hiểu rõ về các kiểu chỉ mục cùng với các đặc trưng hoàn chỉnh và duy nhất. Chúng ta cũng đã có những kiến thức về các tác vụ như xem, xoá các chỉ mục. Các câu truy vấn được sử dụng để truy xuất dữ liệu từ một bảng. Chúng được thực hiện trực tiếp trên dữ liệu thực lưu trữ trong bảng. Thay vì việc truy vấn và xử lý dữ liệu trực tiếp trong các bảng, SQL Server hỗ trợ các bảng ảo để biểu diễn các dữ liệu hiện có theo một cách mới. Các bảng ảo hỗ trợ các chức năng tương tự như các bảng vật lý. Dữ liệu có thể được đọc, thêm mới và sửa đổi trong các bảng giả lập. Các bảng như vậy được gọi là View. Trong cơ sở dữ liệu quan hệ, các tác vụ được thực hiện trên tập hợp các hàng của bảng, trả về bởi lệnh SELECT. Một số ứng dụng như các ứng dụng tương tác và trực tuyến không thể làm việc hiệu quả với toàn bộ tập hợp dữ liệu trả về. Những ứng dụng này cần một kỹ thuật để xử lý dữ liệu trên một hàng hoặc một khối lượng nhỏ các hàng tại một thời điểm. Con trỏ là sự mở rộng của tập hợp kết quả trả về mà cung cấp kỹ thuật này. Trong chương này, chúng ta sẽ nghiên cứu chi tiết về view và các con trỏ. 11.1 View View là một cách thức để xem các dữ liệu từ một hay nhiều bảng trong cơ sở dữ liệu. View là một bảng ảo, bảng ảo được tạo ra như một tập con của các cột hay các hàng từ một hay nhiều bảng khác nhau. Đồng thời, nó cũng có thể bao gồm tất cả các hàng và các cột . Tuy nhiên, một view không tồn tại như một tập hợp của các giá trị dữ liệu lưu trữ trong cơ sở dữ liệu. Các dòng và cột đến từ các bảng tham chiếu trong chuỗi truy vấn. Dữ liệu được hiển thị trực tiếp từ bảng ngay tại thời gian thực thi. Một view có tác dụng như một bộ lọc dữ liệu trên các bảng có địa chỉ trong các truy vấn. Các truy vấn này khai báo một view có thể dựa trên một hay nhiều bảng, hay từ những view khác, từ cơ sở dữ liệu hiện tại hay cơ sở dữ liệu khác. Hình 11.1 mô tả một view đã được tạo ra như thế nào. Xem xét các bảng, bảng A và bảng B và một view được tạo ra từ 2 bảng trên. Tất cả các cột hoặc các cột đã được chọn từ các bảng trên 144 Thiết kế cơ sở dữ liệu và thực hành với SQL Server
đều có thể được gộp trong view. Hình 11.1 chỉ ra một view được tạo ra với cột A và B từ bảng A và cột B1,C1,D1 từ bảng B A B C A B B1 C1 D1 Tabl eA Tabl eB View - Table A & Table B A1 B1 C1 D1 Figure 11.1: View View thường được sử dụng để:  Lọc các dòng từ bảng  Bảo vệ dữ liệu nhạy cảm khỏi những người dùng không được phép  Làm giảm sự phức tạp của cơ sở dữ liệu  Tách các cơ sở dữ liệu khác nhau ở dạng vật lý vào một cơ sở dữ liệu đơn nhất ở dạng logic. 11.1.1 Ưu điểm của View Việc sử dụng view mang lại cho người sử dụng và các nhà phát triển một số thuận lợi sau: Thuận lợi của View đối với người sử dụng cuối  • Dễ dàng để hiểu được kết quả Trong khi tạo ra những view, tên cột có thể được thay đổi sao cho có nghĩa hơn, vì vậy nó làm cho người sử dụng có thể dễ dàng hiểu được cột này biểu diễn cái gì. Việc thay đổi tên cột trong view không tác động đến tên cột trong bảng mà view tham chiếu đến. • Dễ dàng để thu được dữ liệu mong muốn Có nhiều người biết rất ít về SQL, các câu lệnh SQL trở nên khó khăn đối với họ khi họ muốn tạo ra các truy vấn phức tạp từ nhiều bảng khác nhau. Bởi vậy, view được tạo ra 145 Thiết kế cơ sở dữ liệu và thực hành với SQL Server
cho việc truy cập dữ liệu từ nhiều bảng khác nhau, nó giúp người sử dụng dễ dàng trong việc truy cập sở dữ liệu.  Thuận lợi của View đối với những nhà phát triển • Dễ dàng hạn chế việc mất mát dữ liệu Một nhà phát triển có thể muốn giấu những thông tin trong một số cột hoặc một số dòng nào đó. Bằng việc sử dụng view, người sử dụng có thể được cách truy cập linh hoạt tới những dữ liệu mà họ muốn, trong khi vẫn duy trì được bảo mật đối với những dữ liệu khác trong cùng một bảng hoặc trong các bảng khác nhau. Để làm việc này, View được thiết lập ngăn chặn việc truy cập các cột không được phép, các cột này sẽ bị ẩn đối với người sử dụng. • Dễ dàng bảo trì các ứng dụng Chúng ta dễ dàng soát lỗi của View hơn là soát lỗi của nhũng truy vấn. Dò tìm lỗi trong từng bước của mỗi một quá trình trong một View là dễ dàng bởi tất cả các bước đều là một phần của View. 11.1.2 Tạo mới View Câu lệnh T-SQL CREATE VIEW được sử dụng để tạo ra một view. Cú pháp là: CREATE VIEW AS Ví dụ , để tạo một View trong cơ sở dữ liệu pups nó bao gồm chi tiết về những nhân viên cho nhà xuất bản “ New Moon Books”, câu lệnh như sau: CREATE VIEW NewMoon_employee AS Select emp_id, fname, minit, lname, hire_date FROM employee e, publishers p WHERE e.pub_id =p.pub_id AND pub_name=’New Moon Books’ Một View đã được tạo ra, một câu lệnh SELECT có thể được thực thi để xem các hàng của View. SELECT * FROM NewMoon_employee Hình 11.2 hiển thị nội dung của View. 146 View và con trỏ
Figure 11.2: Hiển thị nội dung của một View 11.1.3 Các nguyên tắc khi tạo View Có một số các nguyên tắc phải tuân theo trong khi tạo View  View chỉ có thể được tạo trong cơ sở dữ liệu hiện tại.  Tên một View tương tự như tên một bảng bởi vì nó phải dễ dàng để nhớ và có sự liên kết (không có khoảng trắng giữa các kí tự). Nó phải theo các quy ước đặt tên để đảm bảo sự đồng nhất.  Một View có thể được xây dựng dựa vào các View khác. SQL SERVER cho phép các View được lồng vào nhau tới 32 mức (cấp). Mỗi một View có thể bao gồm tới 1024 cột từ một hay nhiều bảng hoặc từ nhiều View khác.  Những giá trị mặc định, những quy tắc và bẫy lỗi không thể được liên kết với View.  View không thể đánh chỉ mục.  Các bảng tạm thời không thể tham gia vào View  Khai báo View được lưu giữ lại ngay cả khi các bảng liên quan bị loại bỏ.  Câu truy vấn để khai báo View không thể bao gồm các mệnh đề ORDER BY, COMPUTE, COMPUTE BY hoặc từ khóa INTO. 11.1.4 Sửa đổi dữ liệu thông qua View Tương tự như trong các bảng, các câu lệnh INSERT/UPDATE/DELETE có thể được đưa vào trong View. Kết quả thay đổi ở View chính là sự thay đổi dữ liệu trong bảng cơ sở. Một View có thể được sử dụng để thay đổi dữ liệu trong bảng với điều kiện:  View phải bao gồm ít nhất một bảng nằm trong mệnh đề FROM của phần khai báo View, điều đó có nghĩa là View không thể chỉ có một biểu thức.  Không có tất cả các hàm sau (AVG, COUNT, SUM, MIN, MAX, GROUPING) hoặc GROUP BY, UNION, DISTINCT hoặc mệnh đề TOP trong danh sách được chọn. Tuy nhiên, tất cả các hàm đó có thể được sử dụng trong chuỗi truy vấn con được khai báo trong 147 Thiết kế cơ sở dữ liệu và thực hành với SQL Server
mệnh đề FROM với điều kiện là các giá trị nhận được, được sinh ra từ nhóm các hàm trên không bị thay đổi.  View không có các cột nhận được từ trong danh sách chọn. Các cột nhận được này là kết quả của tập hợp các cột chuẩn bởi bất cứ thứ gì hơn là một cột biểu thức đơn giản, ví dụ như việc sử dụng các hàm, hay toán tử thêm hoặc xóa. Câu lệnh INSERT, UPDATE và DELETE bắt buộc phải theo một tiêu chuẩn nhất định trước khi chúng tham chiếu đến View có thể cập nhật. Câu lệnh UPDATE và INSERT có thể tham chiếu đến View (có thể cập nhật) chỉ khi nếu chúng thay đổi dữ liệu chỉ trong một bảng của các bảng cơ sở của View. Câu lệnh DELETE có thể tham chiếu đến View cập nhật được chỉ khi nếu View đó bao gồm chính xác một bảng trong mệnh đề FROM. 11.1.5 Xóa View Sau khi một View được sử dụng xong, nó có thể bị xóa để giải phóng vùng nhớ. Việc xóa một View không tác động đến các bảng cơ sở mà View tham chiếu đến. Câu lệnh xóa một View là: DROP VIEW Ten_view Ten_view chính là tên của một view đã có trong cơ sở dữ liệu. Ví dụ, câu lệnh sau sẽ xóa View có tên NewMoon_employee DROP VIEW NewMoon_employee 11.2 View chỉ mục View chỉ mục đã được giới thiệu trong SQL Server 2000 và chỉ được hỗ trợ trong phiên bản Enterprise Edition (Phiên bản Standard và Personal cho phép bạn tạo chỉ mục trên một View nhưng trình tối ưu hoá truy vấn sẽ không sử dụng view chỉ mục ở trong phần truy vấn). Đến tận gần đây khái niệm của việc đánh chỉ mục một View vẫn còn vô lý, bởi việc đánh chỉ mục dữ liệu ảo (dữ liệu không tồn tại) vẫn còn mơ hồ không rõ ràng. Một View là một bảng ảo, nó không có bất kì dữ liệu thật nào của chính nó. Nó cho phép bạn truy cập đến dữ liệu hiện thời ở trong những bảng cơ sở. Những View chỉ mục tăng cường khả năng thực hiện các truy vấn phức tạp một cách mạnh mẽ. SQL Server 2000 có View chỉ mục, tương tự như Materialized View của Oracle. Những lợi thế của kiểu view này là nó cung cấp một cơ chế tìm kiếm rất nhanh chóng để lấy ra thông tin của một view một cách liên tục. Chỉ mục đầu tiên được tạo ra trong một View chỉ mục phải là một chỉ mục duy nhất loại clustered. Mỗi khi chỉ mục đầu tiên được tạo ra, kéo theo một số chỉ mục loại non-clustered có thể được tạo ra. Tốt nhất chúng ta nên xây dưng View chỉ mục trên dữ liệu không thường xuyên cập nhật, bởi vì việc duy trì view có chi phí cao hơn việc duy trì một chỉ mục của bảng. Nếu view chỉ mục được tạo ra trên những dữ liệu cập nhật thường xuyên thì chi phí để duy trì dữ liệu trong view chỉ mục có thể làm giảm những ưu điểm của việc sử dụng view chỉ mục. Bạn có thể cải thiện khả năng thực thi của những kiểu truy vấn dưới đây bằng việc sử dụng View chỉ mục: • Quá trình tập hợp hoặc kết nối rất nhiều hàng. 148 View và con trỏ
Bạn không thể cải thiện khả năng thực thi của những kiểu truy vấn dưới đây bằng việc sử dụng View chỉ mục: • Các công việc chứa một số lượng lớn các tác vụ cập nhật đối với cơ sở dữ liệu • Những hệ thống OLTP với nhiều tác vụ ghi dữ liệu. • Các câu truy vấn không chứa toán tử tập hợp hoặc kết nối. • Các tập hợp dữ liệu có khoá ở mức yếu tố cao. (Một mức yếu tố cao chỉ ra rằng khoá nắm giữ nhiều giá trị. Trong trường hợp khoá là duy nhất, mỗi khoá đều có một giá trị khác nhau thì mức yếu tố ở giá trị cao nhất có thể). Có một số giới hạn cho việc xây dựng các chỉ mục trên View: Tuỳ chọn SCHEMABINDING phải được sử dụng với view. Tuỳ chọn này kết nối các bảng  được xác định trong định nghĩa view. Mỗi khi các đối tượng cần thiết trong một view được giới hạn, không ai có thể thay đổi nó. Để thay đổi những đối tượng như vậy, đầu tiên bạn phải xoá bỏ khung giới hạn của view. SCHEMABINDING không cho phép bất cứ một tác vụ sửa đổi nào đối với các đối tượng bên dưới, do đó tránh được việc view trở thành “mồ côi". Ví dụ, trong trường hợp một bảng mà một view được tạo ra rồi bị xoá bởi một ai đó. Nếu view là Schema Bound, bạn có thể tránh được những điều rắc rối như vậy. Nếu view tham chiếu đến một chức năng nào đó do người dùng định nghĩa, nó phải sử dụng  tuỳ chọn SCHEMABINDING. View có thể chỉ bao gồm các chức năng do người dùng định nghĩa và các bảng tham chiếu và  nó không thể tham chiếu đến bất kỳ view nào khác. Các đối tượng được tham chiếu trong view phải trong cùng một cơ sở dữ liệu với view.  Các đối tượng được tham chiếu trong view phải sử dụng kiểu tên quy ước gồm hai phần  chẳng hạn như as dbo.Employees (dbo là tên người sở hữu và Employees là đối tượng cơ sở dữ liệu), Jones.UDFName (Jones là tên người sở hữu và UDFName là đối tượng cơ sở dữ liệu). Một view có thể chỉ bao gồm các đối tượng được tạo ra bởi người sở hữu view đó. Ví dụ, một view được tạo bởi người sử dụng dbo có thể chứa các đối tượng của một mình dbo. Bạn phải sử dụng tuỳ chọn ARITHABORT khi tạo chỉ mục.  Ghi chú: Tuỳ chọn arithabort được dùng để huỷ các truy vấn trong trường hợp tràn bộ đệm hoặc gặp lỗi chia cho 0. Tuỳ chọn này giúp cho việc dừng các thao tác số học trong trường hợp có lỗi. Do đó nó có tên là ARITHABORT. Chúng ta cùng tạo một View chỉ mục sử dụng các bảng Customer, Product, Order và Order_Details từ cơ sở dữ liệu Northwind. CREATE VIEW CustOrdProd_view WITH SCHEMABINDING AS SELECT cust.CompanyName, ord.OrderID, ord.OrderDate, orddet.ProductID, prod.ProductName, orddet.Quantity, orddet.UnitPrice FROM dbo.Customers AS cust INNER JOIN dbo.Orders AS ord ON cust.CustomerID = ord.CustomerID INNER JOIN dbo.[Order Details] AS orddet ON ord.OrderID = orddet.OrderID INNER JOIN dbo.Products AS prod 149 Thiết kế cơ sở dữ liệu và thực hành với SQL Server
ON orddet.ProductID = prod.ProductID Trong view này, chúng ta phải sử dụng tuỳ chọn SCHEMABINDING và quy ước đặt tên hai phần cho các đối tượng tham chiếu bởi view. Bạn có thể kiểm tra view bằng một câu lệnh SELECT đơn giản: SET STATISTICS IO ON SET STATISTICS TIME ON SELECT * FROM CustOrdProd_view Kết quả thể hiện trên hình 11.3. Tuỳ chọn statistics time được sử dụng để xem số lượng các thao tác đĩa được sinh ra bởi T-SQL. Tuỳ chọn statistics io được sử dụng để hiển thị số mili giây cần thiết để chuyển đổi, dịch và thực hiện mỗi lệnh. Shows Estimated Execution Plan Hình 11.3 SQL Server 2000 tạo một lược đồ thực thi trước khi thi hành một câu truy vấn. Lược đồ này mô tả các chỉ mục và đường dẫn, SQL Server sẽ sử dụng để lấy các dữ liệu cần thiết cho truy vấn. Hình 1 hiển thị kết quả của việc nhấn nút trên lược đồ thực thi của câu truy vấn. Hình 11.4 hiển thị chi phí thực hiện lược đồ cho truy vấn này. 150 View và con trỏ
Hình 11.4 Cú pháp TSQL ở trên đã tạo ra một view mà nó có thể được đánh chỉ mục. Khi chúng ta tạo chỉ mục, chỉ mục đầu tiên được tạo trên view phải vừa là clustered , vừa có tính năng duy nhất. SET ARITHABORT ON CREATE UNIQUE CLUSTERED INDEX Index_CustOrdProd ON CustOrdProd_view (CompanyName, OrderID, ProductID) Bạn có thể kiểm tra bằng một lệnh SELECT đơn giản: SELECT * FROM CustOrdProd_view Kết quả của view thể hiện trên hình 11.5. 151 Thiết kế cơ sở dữ liệu và thực hành với SQL Server
Hình 11.5 Hình 11.6 hiển thị chi phí thực hiện lược đồ cho truy vấn này. 152 View và con trỏ
Hình 11.6 showplan cho view thông thường và view chỉ mục thể hiện rằng chỉ mục không được sử dụng lúc nào. Tác dụng của một view chỉ mục chỉ rõ ràng trên các bảng mà chứa một số lượng lớn các hàng. Trong các bảng với một số lượng ít các hàng, hiệu suất của một view chỉ mục và view thông thường sẽ gần như tương đương nhau. 11.3 View Phân Tán (Distributed Partitioned views) SQL Server 2000 hỗ trợ đặc tính “Distributed Partitioned Views (DPV)” hay tạm dịch là View phân tán. Tính năng này cho phép phân chia va phân tán dữ liệu theo chiều ngang tới nhiều server và cơ sở dữ liệu và làm cho chúng ta cảm thấy vẫn như một cơ sở dữ liệu thống nhât. DPV cũng được giới thiệu trong SQL Server 7.0, với các giới hạn về việc thực hiện các lệnh Select. Việc phân chia view cũng đã có trong SQL Server 7 nhưng SQL Server 2000 cho phép chúng được phân tán thông qua nhiều máy khác nhau. Các bảng đã phân chia được phân tán qua nhiều server. Vì thế, mỗi server cần truy nhập tới mọi server khác. Do đó, bạn cần cài đặt cầu hình tất cả các server như các server liên kết (linked servers). Ví dụ, xét rằng bạn muốn phân chia các bảng Customers và Orders trên ba server được gọi là Server1, Server2 and Server3. Để liên kết các server này, Server1 cầm được liên kết với Server2 và Server3; Server2 cần được liên kết với Server3 và Server1; và Server3 cần được liên kết với 153 Thiết kế cơ sở dữ liệu và thực hành với SQL Server
Server1 và Server2. Trong SQL Server 2000 bạn có thể thực hiện các tác vụ thêm mới, cập nhật trên các view phân tán. Với tính năng này, dữ liệu được đặt trên các nút riêng biệt và mỗi nút đều biết rằng các nút khác đang sử dụng các server liên kết. Nó sử dụng chức năng Dynamic Partition Elimination để cho phép một câu truy vấn thực hiện trên một server và đặt trên một server khác. Các server không lưu trữ dữ liệu cần thiết sẽ không được truy vấn. Trước khi sử dụng chức năng nay bạn nên: Lên kế hoạch phân chia dữ liệu trên các server khác nhau.  Tạo một view phân chia được với một khoá để chia dữ liệu. Ví dụ, bạn phải chia dữ liệu  trên cột EmpID và lưu trữ các bản ghi của các nhân viên từ 1 đến 1000 trên Server A và 1001 đến 3000 trên Server B vv... Dữ liệu trên mỗi server có các ràng buộc CHECK, các giới hạn dữ liệu trên server, chỉ của các nhân viên này. Thông báo tới mỗi server về các nút khác trên các server liên kết. Server A sẽ có liên kết  với Server B và ngược lại. Tạo một DPV trên mỗi server mà ở đó phản ánh dữ liệu riêng của nó cũng như dữ liệu  biểu diễn trên các server khác. Khi một truy vấn được thực hiện đối với DPV, trình tối ưu hoá thông qua mỗi nút thành viên và dựa trên ràng buộc CHECK của nút thành viên để to xác nhận xem các bảng yêu cầu của nó đã được duyệt. Nếu điều kiện không phù hợp, bảng được duyệt, ngược lại nó sẽ bị bỏ qua. Sau quá trình này, câu truy vấn được gửi lại cho server, từ đó nófrom where it originated ensuring the optimization of all servers. The disadvantage of this view lies in the Schema changes. You have to manage all the nodes that participated in the view and the view itself in case of any change in the schema. 11.3.1 Tạo một View phân chia Trong một view phân chia, dữ liệu được chia theo chiều ngang từ một tập hợp các bảng thành viên thông qua một hoặc nhiều server được hợp nhất lại như một bảng đơn. SQL Server 2000 phân biệt giữa view cục bộ và view phân tán. Trong trường hợp của một view phân chia cục bộ, tất cả các bảng tham chiếu trong view và bản thân view tồn tại trên cùng một phiên bản của SQL Server, trong khi với trường hợp của view phân tán, ít nhất một trong các bảng tham chiếu nằm trên một server ở xa. Bên cạnh đó, SQL Server 2000 còn phân biệt giữa view phân chia có thể cập nhật được và view chỉ đọc. Khái niệm view phân tán được minh hoạ trong hình 11.7. 154 View và con trỏ
MemberTable_3 View1 MemberTable_1 View1 MemberTable_2 View1 Hình 11.7 Bạn phải chia một bảng theo chiều ngang trước khi thực thi một view phân chia. Trong quá trình phân chia theo chiều ngang, bảng gốc được thay thế bởi một số ít các bảng thành viên có cùng số cột như bảng gốc, và mỗi cột có cùng các thuộc tính như kích thước, kiểu dữ liệu. Tất cả các bảng thành viên trong một view phân tán phải được đặt trên một server thành viên riêng biệt. Bạn có thể đặt trùng tên cho các cơ sở dữ liệu thành viên trên mỗi server thành viên. Ví dụ, Server1.TempDatabase, Server2. TempDatabase, Server3. TempDatabase. Trong khi chia một bảng, bạn phải thiết kế các bảng thành viên sao cho mỗi bảng sẽ chứa một phần dữ liệu theo chiều ngang của bảng gốc dựa vào một khoảng giá trị của khoá. Khoảng giá trị trong mỗi bảng thành viên được quy định bởi một ràng buộc CHECK trên một cột phân chia. Các giá trị của khoảng không thể chồng chéo. Ví dụ, một bảng không nên có một một khoảng giá trị từ 1 đến 1000, và một bảng khác với một khoảng từ 500 đến 3000, điều này sẽ tạo ra sự lẫn lộn trong việc quyết định bảng nào chứa giá trị từ 500 đến 1000. Ví dụ, bạn đang chia bảng TEMP thành ba bảng. Ràng buộc CHECK cho các bảng này là: Tạo bảng trên Server1: CREATE TABLE TEMP_1 (ID_no INTEGER PRIMARY KEY CHECK (ID_no BETWEEN 1 AND 500), --- --- ) Tạo bảng trên Server2: CREATE TABLE TEMP_2 (ID_no INTEGER PRIMARY KEY CHECK (ID_no BETWEEN 500 AND 1000), --- --- ) Tạo bảng trên Server3: 155 Thiết kế cơ sở dữ liệu và thực hành với SQL Server
CREATE TABLE TEMP_3 (ID_no INTEGER PRIMARY KEY CHECK (ID_no BETWEEN 1000 AND 2000), --- --- ) Mỗi khi các bảng thành viên được tạo, bạn phải định nghĩa một view phân tán trên mỗi server thành viên. Mỗi view trên các server khác nhau nên để trùng tên để cho phép các câu truy vấn tham chiếu tới các view phân tán, thực hiên trên bất ký server thành viên nào. Thực tế, mỗi server chỉ có một bảng thành viên và một view phân tán nhưng hệ thống thực thi như một phiên bản của bảng gốc trên mỗi server thành viên. Vùng lưu trữ dữ liệu là trong suốt đối với ứng dụng. Để xây dựng các view phân tán: • Bạn phải thêm vào mỗi server thành viên các định nghĩa server liên kết để chúng có thể thực hiện được các truy vấn phân tán trên các server thành viên khác. Bằng cách đó, một view phân tán có thể truy nhập các dữ liệu trên các server khác. • Bạn phải thiết lập tuỳ chọn lazy schema validation, sử dụng sp_serveroption, cho mỗi định nghĩa server liên kết tham chiếu trong view phân tán. • Bạn phải tạo một view phân tán trên mỗi server thành viên. Chúng ta cùng tạo một view phân tán: CREATE VIEW Reservation_View AS SELECT * FROM Server1. TempDatabase.dbo.TEMP_1 UNION ALL SELECT * FROM Server2. TempDatabase.dbo.TEMP_2 UNION ALL SELECT * FROM Server3. TempDatabase.dbo.TEMP_3 Trong đó, TempDatabase– Tên cơ sở dữ liệu dbo – Người sở hữu bảng dữ liệu Bạn phải tạo một view phân tán trên Server2 và Server3. 11.3.2 Các quy tắc cho view phân tán Một view có thể được cập nhật nếu nó là một tập hợp các câu lệnh SELECT mà tập kết quả độc lập của chúng được hợp vào trong một câu lệnh sử dụng mệnh đề UNION ALL. Mỗi lệnh SELECT độc lập trong định nghĩa view nên tham chiếu đến một bảng cơ sở trong SQL Server. Mỗi bảng thành viên tham chiếu trong định nghĩa view phải tuân thủ các quy tắc sau: • View không thể tham chiếu tới bảng thành viên cùng tên nhiều hơn một lần trong định nghĩa của nó. 156 View và con trỏ
• View không thể có các bảng thành viên mà chúng có các chỉ mục được tạo ra trên bất kỳ cột tính toán nào. • Các bảng thành viên phải có tất cả các ràng buộc PRIMARY KEY trên một số giống nhau các cột. Các cột định nghĩa trong danh sách lựa chọn của câu lệnh SELECT của định nghĩa view phải tuân thủ các quy tắc sau. • Bạn phải bao gồm tất cả các cột của mỗi bảng thành viên trong danh sách chọn. • Bạn không thể sử dụng cùng một cột nhiều lần trong danh sách chọn. • Thứ tự các cột trong danh sách chọn là giống nhau. • Các cột trong danh sách chọn của mỗi lệnh SELECT phải cùng kiểu (bao gồm kiểu dữ liệu, độ chính xác, phạm vi, và thứ tự). Ví dụ, định nghĩa view dưới đây không thực hiện được vì cột đầu tiên trong các câu lệnh SELECT khác kiểu. CREATE VIEW Temp AS SELECT CharPrimaryKey, PartNmbr FROM Table_A UNION ALL SELECT IntPrimaryKey, PartNmbr FROM Table_B Việc phân chia các cột trong mỗi bảng thành viên phải tuân theo các quy tắc sau: • Mỗi bảng thành viên có một cột chứa giá trị khoá ràng buộc bởi CHECK. Phạm vi khoá mà ràng buộc CHECK kiểm tra trong mỗi bảng không chồng lên bất kỳ phạm vi của bảng khác. Giá trị chỉ định của cột được phân chia phải ánh xạ tới bảng đơn duy nhât và có thể sử dụng các toán tử như: =, =, BETWEEN, AND, OR. • Mỗi câu lệnh SELECT trong view nên có cột được phân chia mà cùng thứ tự với cột trong lệnh SELECT. Ví dụ, cột phân chia có thể là cột đầu tiên, hoặc cột thứ hai trong danh sách lựa chọn. • Các cột phân chia không nên có giá trị NULL. • Các cột phân chia phải là một phần của khoá chính của bảng. • Các cột tính toán không thể sử dụng cho việc phân chia cột. • Bạn chỉ có thể có một ràng buộc trên cột phân chia. SQL Server loại bỏ tất cả các ràng buộc khi có nhiều hơn một. Bạn có thể sử dụng các lệnh INSERT để thêm dữ liệu vào các bảng thành viên thông qua view đã được phân chia. Các lệnh INSERT phải tuân thủ các quy tắc sau: • Bạn phải bao gồm tất cả các cột trong câu lệnh INSERT. Những cột này có thể có giá trị NULL trong bảng cơ sở hoặc có một ràng buộc DEFAULT. • Bạn không thể chỉ định từ khoá DEFAULT trong mệnh đề VALUES của câu lệnh INSERT. • Bạn phải cung cấp một giá trị, thoả mãn ràng buộc CHECK được định nghĩa trên cột phân chia, trong các câu lệnh INSERT. • Bạn không thể chèn các bản ghi vào một bảng thành viên nếu nó chứa một cột với một thuộc tính đồng nhất. • Bạn không thể chèn các bản ghi vào một bảng thành viên nếu nó chứa một cột kiểu timestamp. 157 Thiết kế cơ sở dữ liệu và thực hành với SQL Server
• Bạn không thể chèn các bản ghi vào một bảng thành viên nếu tồn tại một self-join tới cùng view hoặc một trong các bảng thành viên. Bạn có thể sử dụng các câu lệnh UPDATE để sửa đổi dữ liệu trong một hoặc nhiều bảng thành viên thông qua view phân chia. Các lệnh UPDATE phải tuân thủ các quy tắc sau: • Bạn không thể cập nhật giá trị của một cột có thuộc tính đồng nhất. • Bạn không thể cập nhật giá trị của một PRIMARY KEY nếu cột lưu trữ dữ liệu dạng text, image hoặc ntext. • Bạn không thể cập nhật các bản ghi trong một bảng thành viên nếu nó chứa dữ liệu dạng timestamp. • Bạn không thể cập nhật các bản ghi trong một bảng thành viên nếu tồn tại một self-join tới cùng view hoặc một trong các bảng thành viên. • Bạn không thể chỉ định từ khoá DEFAULT trong mệnh đề SET của câu lẹnh UPDATE. Bạn có thể sử dụng các lệnh DELETE để xoá dữ liệu từ một hoặc nhiều bảng thành viên thông qua view phân chia. Các lệnh DELETE phải tuân thủ các quy tắc sau. • Bạn không thể xoá các bản ghi trong một bảng thành viên nếu tồn tại một self-join tới cùng view hoặc một trong các bảng thành viên. 11.3.3 Các quy tắc cho View phân tán View phân tán (ở xa) có thêm một số điều kiện ngoài những quy tắc được định nghĩa cho view phân tán. Đó là: • Một phiên làm việc phân tán được thực hiện để đảm bảo số phần tử được cập nhật trên tất cả các nút. • Bạn phải thiết lập tuỳ chọn XACT_ABORT ở giá trị ON. Nếu, một lệnh T-SQL trả về một lỗi khi chạy, toàn bộ phiên làm việc được huỷ bỏ và trả lại tình trạng ban đầu nếu XACT_ABORT được đặt là ON. Nếu đặt là OFF, chỉ có lệnh T-SQL bị lỗi được trả lại tình trạng ban đầu còn phiên làm việc vẫn tiếp tục. • Các cột smallmoney và smalldatetime trong các bảng ở xa được ánh xạ thành money và datetime riêgn biệt và các cột tương ứng trong các bảng cục bộ phải có kiểu là money và datetime. • Bất cứ một server đã được liên kết nào trỏ đến cùng một phiên bản của SQL Server (tham khảo loopback linked server) không thể được bao gồm trong các view phân tán. Bạn có thể có một view cập nhật được bao gồm các bảng đã được phân chia, chúng không tuân theo tất cả các quy tắc, cung cấp một bẫy lỗi INSTEAD OF. 11.4 Bẫy lỗi INSTEAD OF Một view thưc hiện cùng chức năng như một bảng. Bạn hoàn toàn có thể chạy các lệnh INSERT, UPDATE, và DELETE trên các view này. Khi dữ liệu thay đổi thông qua view, bạn nên chú ý tờI một số điều dưới đây:  Nếu view của bạn có toán tử join trong định nghĩa của nó, bạn sẽ không thể chèn hoặc xoá dữ liệu trừ khi bạn sử dụng bẫy INSTEAD OF. Bạn có thể cập nhật chỉ những cột mà chúng thuộc về một bảng đơn nếu không sử dụng bẫy INSTEAD OF. 158 View và con trỏ
 Nếu view của bạn bao gồm một bảng đơn với tất cả các trường của nó, bạn có thể chèn dữ liệu thông qua view đó mà không cần sử dụng bẫy INSTEAD OF. Trong một view là một bảng đơn, nếu bạn bao gồm một cột, cột đó không có giá trị ngầm định, vào trong view, bạn phải sử dụng bẫy INSTEAD OF để thực thi thao tác chèn. Trong chương này, chúng ta đã thảo luận về bẫy lỗi discussed INSTEAD OF rất ngắn gọn vì nó sẽ được bàn luận kỹ hơn trong chương tiếp theo. 11.5 Cập nhật View Trong SQL Server 2000, bạn có thể cập nhật view theo hai cách: • Bẫy INSTEAD OF: Bạn có thể cập nhật một view sử dụng bẫy INSTEAD OF. Nó được thực hiện thay cho các lệnh sửa đổi dữ liệu. Bạn có thể cung cấp một tập hợp các thao tác cần thiết để sửa đổi dữ liệu trong bẫy lỗi. Vì vậy, nếu bạn xây dựng một bẫy INSTEAD OF cho một view trên các lệnh sửa đổi dữ liệu chẳng hạn như INSERT, UPDATE, hoặc DELETE thì các view tương ứng có thể được cập nhật thông qua các lệnh đó. • View phân chia: Bạn có thể cập nhật một 'view phân chia' với các hạn chế nhất định. Nếu bạn có một view, không phải là một view phân chia và không có bẫy lỗi INSTEAD OF, thì nó có thể được cập nhật chỉ khi các điều kiện dưới đây được thoả mãn: • Câu lệnh SELECT không nên chứa những hàm kết hợp và các mệnh đề như TOP, GROUP BY, UNION, hoặc DISTINCT. Bạn có thể sử dụng các hàm kết hợp trong mệnh đề FROM của một câu truy vấn con chỉ khi giá trị trả về bởi hàm đó không thay đổi. • Lệnh SELECT không chứa các cột đã được chuyển hoá. • Mệnh đề FROM trong câu lệnh SELECT nên có ít nhất một bảng. Ví dụ, view này là không thể cập nhật được CREATE VIEW NoTable AS SELECT GETDATE() AS CurrentDate, @@LANGUAGE AS CurrentLanguage Các lệnh INSERT, UPDATE, và DELETE cũng phải thoả mãn các điều kiện nhất đinh trước khi chúng tham chiếu đến view. Các lệnh UPDATE và INSERT tham chiếu đến các view chỉ khi chúng sửa đổi dữ liệu trong các bảng cơ sở được bao gồm trong mệnh đề FROM của view. Lệnh DELETE tham chiếu đến một view chỉ khi view bao gồm chính xác một bảng trong mệnh đề FROM. 11.6 Con trỏ Một con trỏ là một đối tượng cơ sở dữ liệu được sử dụng bởi ứng dụng để thao tác với các hàng dữ liệu thay vì các tập hợp dữ liệu. Sử dụng con trỏ, nhiều tácvụ có thể được thực hiện theo từng hàng trên tập kết quả mà có thể cần hoặc ko cần sự có mặt của bảng gốc. Hay nói một cách khác, con trỏ, về mặt khái niệm, trả về một tập hợp kết quả dựa trên các bảng bên trong cơ sở dữ liệu. Với con trỏ chúng ta có thể: 159 Thiết kế cơ sở dữ liệu và thực hành với SQL Server
 Cho phép định vị các hàng chỉ định của tập kết quả.  Nhận về một hàng đơn hoặc tập hợp các hàng từ vị trí hiện tại của tập kết quả.  Hỗ trợ sửa đổi dữ liệu của hàng ở vị trí hiện tại trong tập kết quả.  Hỗ trợ nhiều cấp độ quan sát đối với các thay đổi được tạo ra bởi các người dùng khác trên các dữ liêu của tập kết quả.  Cung cấp các lệnh Transact-SQL trong các script, thủ tục lưu, và bẫy lỗi để truy nhập dữ liệu trong tập kết quả. 11.6.1 Tạo một con trỏ Lệnh DECLARE được sử dụng để tạo một con trỏ. Nó chứa lệnh SELECT để bao gồm các bản ghi từ bảng. Cú pháp là: DECLARE CURSOR [LOCAL | GLOBAL] [FORWARD ONLY | SCROLL] [STATIC | KEYSET | DYNAMIC | FAST_FORWARD] [READ_ONLY | SCROLL_LOCKS | OPTIMISTIC] [TYPE_WARNING] FOR [FOR UPDATE [OF Column_name[,….N]]] Các thuộc tính được giải nghĩa dưới đây:  LOCAL Chỉ ra phạm vi hạn chế của con trỏ trong thủ tục lưu hoặc bẫy lỗi. Hay nói một cách khác, tên của con trỏ là hợp lệ bên trong phạm vi của nó. Con trỏ hoàn toàn được giải phóng khi thủ tục lưu hoặc bẫy lỗi bị hủy bỏ.  GLOBAL Chỉ ra rằng phạm vi của con trỏ là toàn cục. Tên con trỏ có thể được tham chiếu trong bất cú thủ tục lưu nào.  FORWARD_ONLY Chỉ ra rằng con trỏ chỉ có thể được duyệt từ hàng đầu tiên đến hàng cuối cùng, chế độ này chỉ hỗ trợ tuỳ chọn FETCH NEXT để lấy dữ liệu. Ngầm định con trỏ luôn ở chế độ FORWARD ONLY.  SCROLL Chỉ ra rằng tất cả các tuỳ chọn để lấy dữ liệu (FIRST, LAST, PRIOR, NEXT, RELATIVE, ABSOLUTE) đều được hỗ trợ. Nếu SCROLL không được chỉ ra trong khai báo con trỏ, nó chỉ hỗ trợ tuỳ chọn NEXT.  STATIC Định nghĩa một con trỏ mà sẽ tạo ra môt bản sao của dữ liệu để sử dụng. Tất cả các yêu cầu gửi đến con trỏ được trả lời từ bảng tạm thời tempdb. Vì vậy, việc sửa đổi dữ liệu trên các 160 View và con trỏ
bảng cơ sở không ảnh hưởng tới dữ liệu trả về bởi con trỏ, và con trỏ này không cho phép sửa đổi.  KEYSET Chỉ ra thứ tự của các hàng trong con trỏ là cố định khi con trỏ được mở.  DYNAMIC Định nghĩa một con trỏ mà ánh xạ toàn bộ thay đổi đối với các hàng trong tập kết quả mỗi khi con trỏ duyệt.  FAST_FORWARD Chỉ định con trỏ là FORWARD_ONLY và READ_ONLY. FAST_FORWARD không thể được xác định bởi tuỳ chọn SCROLL hoặc FOR_UPDATE. Các con trỏ FORWARD ONLY và FAST_FORWARD là loại trừ lẫn nhau.  READ_ONLY Nghiêm cấm việc cập nhật thông qua con trỏ này. Nó không thể được tham chiếu trong mệnh đề WHERE CURRENT OF trong câu lệnh UPDATE hoặc DELETE.  SCROLL_LOCKS Chỉ ra rằng việc cập nhật hoặc xoá các vị trí thông qua con trỏ được đảm bảo thành công. SQL Server khoá các hàng mà chúng đang được đọc vào trong con trỏ để đảm bảo khả năng sẵn sàng của chúng cho việc sửa đổi sau này. Tuỳ chọn SCROLL_LOCKS không thể được xác định cùng với FAST_FORWARD.  OPTIMISTIC Chỉ ra rằng việc cập nhật hoặc xoá các vị trí thông qua con trỏ không thành công, nếu hàng được cập nhật khi nó đã được đọc vào con trỏ.  TYPE_WARNING Đưa ra một thông điệp cảnh báo gửi đến người dùng nếu con trỏ ngầm chuyển đổi từ một kiểu yêu cầu sang một kiểu khác.  UPDATE [OF Column_name[,..n]] Định nghĩa các cột có thể cập nhật trong con trỏ. Nếu OF Column_name [,…n] được sử dụng, chỉ các cột nằm trong danh sach được phép sửa đổi. Trong số các thuộc tính nêu trên DYNAMIC, STATIC, KEYSET và FORWARD_ONLY định nghĩa các đặc trưng mang tính truy xuất dữ liệu của con trỏ và số còn lại định nghĩa các đặc trưng mang tính chất chức năng của con trỏ. 11.6.2 Các bước tạo một con trỏ 161 Thiết kế cơ sở dữ liệu và thực hành với SQL Server
Một con trỏ có thể tồn tại trong nhiều trạng thái. Các trạng thái khác nhau của con trỏ liên quan đến các khu vực khác nhau nơi mà chúng được tạo ra và thực hiện. Một con trỏ đơn giản được thực thi theo các bước dưới đây: 1. Sau khi con trỏ được tạo, nó phải được mở trước khi các bản ghi được truy xuất từ nó. Lệnhe OPEN được sử dụng để mở một con trỏ. Cú pháp là: OPEN Mỗi khi con trỏ được mở, các bản ghi được truy xuất từ con trỏ để hiển thị chúng trên màn 2. hình. Lệnh FETCH được sử dụng để hiển thị các bản ghi từ con trỏ. Cú pháp là: FETCH Một cách tuỳ ý, một con trỏ có thể được đóng tạm thời khi nó không cần thiết sử dụng lệnh 3. CLOSE. Lệnh này đóng con trỏ đang mở bằng cách giải phóng tập kết quả hiện tại. Mỗi khi con trỏ được đóng, các hàng chỉ có thể được truy xuất sau khi mở lại nó. Cú pháp là: CLOSE Khi con trỏ không cần thiết thêm nữa, tham chiếu đến nó được huỷ bỏ. Lệnh 4. DEALLOCATE sử dụng để giải phóng tham chiếu tới con trỏ. Cú pháp là: DEALLOCATE Mỗi khi con trỏ đựoc tạo và mở, các hàng được truy xuất từ con trỏ. Chúng ta sẽ xem chi tiết về việc duỵệt và nhận về dữ liệu ở chương tiếp. 11.6.3 Truy xuất và Duyệt một con trỏ Khi con trỏ được mở, hàng ở vị trí hiện tại của con trỏ về mặt logic ở trước hàng đầu tiên. Các con trỏ Transact-SQL có thể truy xuất một hàng tại một thời điểm. Các tác vụ để nhận về các hàng từ con trỏ được gọi là fetching. Có rất nhiều thao tác truy xuất: FETCH FIRST: Truy xuất hàng đầu tiên.   FETCH NEXT: Truy xuất hàng tiếp theo hàng truy xuất trước đó.  FETCH PRIOR: Truy xuất hàng trước hàng truy xuất trước đó.  FETCH LAST: Truy xuất hàng cuối cùng.  FETCH ABSOLUTE n: Nếu n là một số nguyên dương, nó sẽ truy xuất n hàng trong con trỏ. Nếu n là một số nguyên âm, n hàng trước hàng cuối cùng trong con trỏ được truy xuất. Nếu n bằng 0, không hàng nào được truy xuất. Ví dụ, FETCH Absolute 2 sẽ hiển thị bản ghi thứ hai của một bảng.  FETCH RELATIVE n: Truy xuất n hàng từ hàng truy xuất trước đó, nếu n là số dương. Nếu n là số âm, n hàng trước hàng truy xuất trước đó được truy xuất. Nếu n bằng 0, hàng hiện tại được nhận về. Trạng thái của mỗi lệnh truy xuất có thể được xác định bởi hai biến toàn cục.  @@FETCH _STATUS Biến này trả về một số nguyên biễu diễn kết quả của lệnh truy xuất cuối cùng của con trỏ. 162 View và con trỏ
 @@CURSOR_ROWS Biến này trả về tổng số hàng hiện tại trong con trỏ đang mở. Hình 11.8 hiển thị một ví dụ về việc sử dụng con trỏ, và các hàng của nó được truy xuất cho đến khi biến bằng 0. Hình 11.8: Ví dụ tạo con trỏ 163 Thiết kế cơ sở dữ liệu và thực hành với SQL Server