Session 12

Chương 12

View và Các Con Trỏ (Hướng dẫn thực hành)

Mục đích Hoàn thành chương này bạn có thể:  Tạo ra view sử dụng chương trình Enterprise Manager hoặc cú pháp T-SQL.  Sửa đổi view sử dụng Enterprise Manager.  Đổi tên view sử dụng Enterprise Manager.  Tạo ra các kiểu con trỏ khác nhau.  Thực thi nhiều tác vụ khác nhau với các con trỏ.  Sửa đổi dữ liệu bằng các con trỏ.  Đóng và giải phóng các con trỏ. Các bước thực hành trong chương này đã được mô tả chi tiết và dễ hiểu, để thực hiện được học viên cần nắm được mục đích của chương và hiểu được cách sử dụng các công cụ. Hãy chú ý làm theo các bước sau: Phần I – Hướng dẫn

Thời gian : 1 tiếng 30 phút

12.1Views (hay các khung nhìn) Các view là các bảng dữ liệu ảo được định nghĩa bằng một câu truy vấn. Một view không tồn tại vật lý như các tập hợp dữ liệu lưu trữ trong cơ sở dữ liệu. Tuy nhiên, nó giống như một bảng dữ liệu thực với tập hợp các hàng, các cột dữ liệu. Dữ liệu của View nhận được từ các bảng thực trong cơ sở dữ liệu bằng việc định nghĩa câu truy vấn SQL. 12.1.1 Tạo ra View View có thể được tạo ra bằng cách chọn ra dữ liệu từ nhiều bảng bằng câu lệnh SELECT. Cũng giống như các đối tượng dữ liệu khác, view phải được gán bởi một tên duy nhất trong cơ sở dữ liệu. Câu lệnh CREATE VIEW được sử dụng để tạo ra một view trong trình Query Analyzer. View còn có thể được tạo ra sử dụng chức năng Create View Wizard có trong Enterprise Manager. Chúng ta cùng xem các bước thực hiện khi dùng trình Create View Wizard: 1. Nháy đúp chuột vào cơ sở dữ liệu Northwind. Một danh sách các đổi tượng trong cơ sở dữ liệu Northwind được hiển thị.

2. Nháy chuột vào biểu tường Run a Wizard trên thanh công cụ hoặc có thể nháy chuột vào Tools trên menu và chọn chức năng Wizards từ menu đổ xuống.

3. Mở rộng lựa chọn Database. Một danh sách các chức năng được hiển thị.

4. Chọn chức năng Create View Wizard. Nhấn nút OK .

View và con trỏ

169

Chương trình Create View Wizard xuất hiện.

5. Nhấn nút Next. 6. Chọn cơ sở dữ liệu Northwind từ danh sách Database name và nhấn nút Next. 7. Trong cột Include in View, nhấn chuột đánh dấu các ô sáng bên cạnh các bảng Orders và Order Details để chọn chúng.

8. Nhấn nút Next. 9. Trong cột Select Column, nhấn chuột đánh dấu các ô sáng tương ứng với cột OrderDate của bảng Orders, và các cột OrderID, ProductID và Quantity của bàng Order Details.

10. Nhấn nút Next. Cửa sổ Define Restriction của chương trình xuất hiện. Cửa sổ này có thể được sử dụng để nhập vào các điều kiện ràng buộc dữ liệu. Mệnh đề WHERE của câu lệnh SELECT có thể được đưa ra ở đây.

11. Gõ vào các dòng sau trong cửa sổ Define Restriction: where "Order Details".orderid = Orders.orderid and ProductID between 11 and 20 and CustomerID = 'BONAP'

12. Nhấn nút Next. 13. Gõ vào tên của view “Bonap_Orders_VIEW”. Nhấn nút Next. Lệnh tạo view được hiển thị như hình 12.1.

170

Thiết kế cơ sở dữ liệu và thực hành với SQL Server

Hình 12.1: Create View Wizard

14. Nhấn nút Finish. 15. Hộp thoại Wizard Complete xuất hiện. Nhấn nút OK. view sẽ được hiển thị ở cửa sổ bên phải. 12.1.2 Sửa đổi View Một view có thể được sửa đổi bằng trình Enterprise Manager. Chúng ta cùng sửa đổi Bonap_Orders_VIEW để nó không hiển thị cột Quantity. Các bước thực thi trong Enterprise Manager như sau:

1. Nhấn chuột phải vào view Bonap_Orders_VIEW trong cơ sở dữ liệu Northwind. Một danh sách các chức năng xuất hiện.

2. Chọn Design View từ danh sách các chức năng. Cửa sổ thiết kế view được hiển thị. 3. Xoá đánh dấu trên ô sáng cạnh cột Quantity. Việc sửa đổi thiết kê được hiển thị như hình 12.2.

View và con trỏ

171

Hình 12.2: Sửa đổi thiết kế của View

4. Nhấn nút save trên thanh công cụ trong cửa sổ thiết kế để ghi lại các thay đổi. 5. Nhấn nút close để thoát khỏi màn hình thiết kế. 12.1.3 Đổi tên View Chúng ta có thể đổi tên của một view trong Enterprise Manager chẳng hạn đổi tên view Bonap_Orders_VIEW thành Bonap_Orders. Các bước thực hiện trong trình Enterprise Manager như sau:

1. Nhấn chuột phải trên view Bonap_Orders_VIEW trong cơ sở dữ liệu Northwind. Một danh sách các chức năng xuất hiện.

2. Chọn chức năng Rename từ menu. 3. Đổi tên view thành Bonap_Orders. Một hộp thoại xuất hiện như hình 12.3.

Hình 12.3: Hộp thoại xác nhận việc đổi tên View

172

Thiết kế cơ sở dữ liệu và thực hành với SQL Server

4. Nhấn nút Yes trong hộp thoại. 5. Nhấn nút OK trên hộp thoại thông báo việc đổi tên đã thành công. View được đổi tên xuất hiện như hình 12.4.

Hình 12.4: Đổi tên view 12.2Các con trỏ Trong một cơ sở dữ liệu quan hệ, một tác vụ thực hiện trên bảng chứa tất cả các hàng. Câu lệnh SELECT trả về các hàng thoả mãn điều kiện đưa ra trong mệnh đề WHERE. Nhưng một số ứng dụng, đặc biệt các ứng dụng tương tác, không thể làm việc với toàn bộ tập hợp các hàng. Những ứng dụng này cần một số kỹ thuật để làm việc với một hàng, hoặc một tập con các hàng. Con trỏ chính là sự mở rộng đối với tập hợp kết quả trả về nhằm cung cấp kỹ thuật để làm việc với một hàng tại một thời điểm. Một con trỏ là một đối tượng cơ sở dữ liệu mà các ứng dụng sử dụng để xử lý dữ liệu theo từng hàng. Sử dụng câu lệnh T-SQL DECLARE CURSOR để định nghĩa các thuộc tính của một con trỏ T-SQL. 12.2.1 Khai báo các con trỏ Chúng ta cùng tạo ra một con trỏ để hiển thị title_id, title, price, và ytd_sales từ bảng titles cho các quyển sách có kiểu là ‘psychology’. Các bước tiến hành trong trình Query Analyzer như sau:

1. Trong Query Analyzer, chọn cơ sở dữ liệu pubs. 2. Thực hiện đoạn lệnh sau: DECLARE TitleCursor CURSOR SCROLL FOR SELECT title_id, title, price, ytd_sales FROM titles

View và con trỏ

173

WHERE type = 'psychology' Con trỏ đã được tạo ra. 12.2.2 Mở các con trỏ Sau khi khai báo con trỏ, chúng ta cần mở nó để truy nhập đến các hàng trong nó. 1. Thực hiện lệnh sau: OPEN TitleCursor 12.2.3 Truy xuất các hàng của con trỏ Lệnh FETCH nhận về các hàng từ con trỏ và hiển thị nó trên màn hình. Lệnh FETCH có rất nhiều tính năng hữu dụng. Ngầm định, một lệnh FETCH không kèm theo tuỳ chọn nào hiển thị các bản ghi ở vị trí con trỏ đến hàng logic hiên tại. Do không có quá trình xử lý nào từ khi con trỏ được mở nên con trỏ này sẽ ở vị trí của bản ghi đầu tiên. 1. Thực hiện lệnh sau: FETCH TitleCursor Kết quả của lệnh FETCH được hiển thị trong hình 12.5.

Hình 12.5: Truy xuất các hàng của con trỏ Bản ghi đầu tiên được hiển thị và hiện tại con trỏ trỏ đến hàng thứ hai. 12.2.4 Truy xuất hàng đầu tiên Bởi vì con trỏ ở hàng thứ hai nên để hiển thị hàng thứ nhất, chúng ta sử dụng tuỳ chọn FIRST của lệnh FETCH. 1. Thực hiện lệnh sau: FETCH FIRST FROM TitleCursor Kết quả được hiển thị trong hình 12.6.

174

Thiết kế cơ sở dữ liệu và thực hành với SQL Server

Hình 12.6: Truy xuất hàng đầu tiên của con trỏ 12.2.5 Truy xuất hàng tiếp theo 1. Thực hiện lệnh sau: FETCH NEXT FROM TitleCursor Kết quả hiển thị trong hình 12.7.

Hình 12.7: Truy xuất hàng tiếp theo của con trỏ 12.2.6 Truy xuất hàng cuối cùng 1. Thực hiện lệnh sau: FETCH LAST FROM TitleCursor Kết quả hiển thị trong hình 12.8.

View và con trỏ

175

Hình 12.8: Truy xuất hàng cuối cùng của con trỏ 12.2.7 Truy xuất chính xác đến một hàng Từ khoá ABSOLUTE được sử dụng để truy xuất đến một hàng xác định bởi số hiệu của vị trí của nó trong con trỏ. 1. Thực hiện lệnh sau: FETCH ABSOLUTE 2 FROM TitleCursor Kết quả hiển thị hàng thứ hai trong con trỏ, xem hình 12.9.

Hình 12.9: Truy xuất một hàng xác định trong con trỏ 12.2.8 Truy xuất đến một hàng liên quan Từ khoá RELATIVE sử dụng cùng lệnh FETCH để chỉ ra một vị trí có quan hệ với vị trí của bản ghi hiện tại. 1. Thực hiện lệnh sau: FETCH RELATIVE 1 FROM TitleCursor Kết quả trong hình 12.10.

176

Thiết kế cơ sở dữ liệu và thực hành với SQL Server

Hình 12.10: Truy xuất hàng liên quan trong con trỏ 12.2.9 Đóng con trỏ và giải phóng nó Khi một con trỏ không được sử dụng đến nữa, nó nên được đóng lại và giải phóng khỏi bộ nhớ. Bây giờ chúng ta đóng và giải phóng con trỏ TitleCursor. 1. Thực hiện đoạn lệnh sau: CLOSE TitleCursor DEALLOCATE TitleCursor 12.2.10 Tạo con trỏ FORWARD_ONLY (Chỉ tiến) Chúng ta khai báo một con trỏ FORWARD_ONLY để thay đổi dữ liệu của chính nó.Con trỏ này bao gồm tất cả các bản ghi của bảng jobs mà có giá trị của trường min_lvl = 75. Sau đó thay đổi giá trị cột max_lvl của bản ghi đầu tiên = 100 1. Thực hiện đoạn lệnh sau ở trong Query Analyzer: DECLARE JobsCursor CURSOR FORWARD_ONLY FOR SELECT * FROM jobs WHERE min_lvl = 75 FOR UPDATE OPEN JobsCursor FETCH JobsCursor UPDATE jobs SET max_lvl = 100 WHERE CURRENT OF JobsCursor WHERE CURRENT OF Cursor2 SELECT * FROM jobs WHERE min_lvl = 75 CLOSE JobsCursor DEALLOCATE JobsCursor Kết quả được hiển thị ở hình 12.11

View và con trỏ

177

Hình 12.11 : Ví dụ về con trỏ FORWARD_ONLY Câu lệnh FETCH hiển thị nguyên bản bản ghi đầu tiên của con trỏ. Trường Max_lvl đã được thay đổi giá trị ở bản ghi đầu tiên, và sau đó câu lệnh SELECT hiển thị giá trị đã thay đổi đó. 12.2.11 Tạo con trỏ READ_ONLY( chỉ đọc) Bây giờ chúng ta tạo ra một con trỏ READ_ONLY, nó bao gồm các hàng của bảng pub_info, các hàng này phải có giá trị của trường pub_id nằm trong khoảng 1000 đến 2000. Sau đó chúng ta sẽ thử xóa một hàng của con trỏ này và xem kết quả của nó. 1. Thực hiện đoạn lệnh sau: DECLARE PubInfoCursor CURSOR READ_ONLY FOR SELECT * FROM pub_info WHERE pub_id between 1000 and 2000 OPEN PubInfoCursor FETCH PubInfoCursor DELETE FROM pub_info WHERE CURRENT OF PubInfoCursor CLOSE PubInfoCursor DEALLOCATE PubInfoCursor Lệnh DELETE trả về một lỗi được hiển thị trong hình 12.12

178

Thiết kế cơ sở dữ liệu và thực hành với SQL Server

Hình 12.12: Ví dụ về con trỏ READ_ONLY Phần II – Bài tập

Thời gian : 30 phút

Hãy thực hiện các thao tác sau trong Query Analyzer sử dụng cơ sở dữ liệu Northwind

1. Tạo một View có tên là EmpHierarchy dựa trên bảng employees và sử dụng câu lệnh CREATE VIEW. View bao gồm các trường employee ID, first name, last name, và first name của người mà nhân viên đó làm báo cáo cho, 2. Trong View EmpHierarchy, thay đổi last name của bản ghi có trường the employee ID có giá trị là 4 thành ‘Jones’. Sử dụng câu lệnh UPDATE để thay đổi dữ liệu của bảng.

3. Xóa View EmpHierarchy sử dụng câu lệnh DROP VIEW . 4. Tạo con trỏ SCROLL có tên là SuppliersCursor, con trỏ này chứa các bản ghi của các nhà cung cấp từ ‘UK’ và ‘USA’, lấy từ bảng Suppliers .Mở con trỏ, hiển thị bản ghi đầu tiên và bản ghi cuối cùng. Đóng và giải phóng con trỏ khỏi bộ nhớ. Phần III – Bài tập về nhà Thực hiện các thao tác sau trong Enterprise Manager, sử dụng cơ sở dữ liệu pubs:

1. Tạo một View có tên là Sales_View, Nó chứa dữ liệu từ bảng titleauthor, sales và titles. View chứa các trường author ID, title ID, royalty percentage, store ID và số lượng bán được của tác giả có ID là 724-80-9391.

View và con trỏ

179

Thực hiện các thao tác sau trong Query Analyzer, sử dụng cơ sở dữ liệu pubs database:

2. Khai báo một con trỏ có chứa thông tin chi tiết về các nhân viên của nhà xuất bản có ID là 0877. Tăng thời hạn hợp đồng cho tất cả các nhân viên ở những bản ghi lẻ lên 1 năm. Cuối cùng hiển thị tất cả các nhân viên của nhà xuất bản có ID là 0877 .để kiểm tra những thay đổi.

180

Thiết kế cơ sở dữ liệu và thực hành với SQL Server