Tìm Hiểu Về Con Trỏ Trong Sql Server - Tài Liệu Text - 123doc

Có thể bạn quan tâm

Tải bản đầy đủ (.docx) (19 trang)

Trang chủ

Luận Văn - Báo Cáo

Công nghệ thông tin

tìm hiểu về con trỏ trong sql server

Bạn đang xem bản rút gọn của tài liệu. Xem và tải ngay bản đầy đủ của tài liệu tại đây (343.74 KB, 19 trang )

TIỂU LUẬN MÔN HỆ QUẢN TRỊ CƠ SỞ DỮ LIỆUTÌM HIỂU VỀ CON TRỎ TRONG SQL SERVERI.Tìm hiểu chung1.1 Giới thiệu về con trỏKhái niệm Cursor- Là một cấu trúc dữ liệu ánh xạ đến một tập các dòng dữ liệu kết quả của một câu truy vấn (select)- Cho phép duyệt tuần tự qua tập các dòng dữ liệu và đọc giá trị từng dòng- Thể hiện của cursor là 1 biến, nhưng tên biến này không bắt đầu bằng ’@’- Vị trí hiện hành của cursor có thể được dùng như điều kiện trong mệnh đề where của lệnh update hoặc delete: cho phép cập nhật/xoá dữ liệu (dữ liệu thật sự trong CSDL) tương ứng với vị trí hiện hành của cursorMộ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ác vụ 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 không 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ể : 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ậpkếtquả.Page 1TIỂU LUẬN MÔN HỆ QUẢN TRỊ CƠ SỞ DỮ LIỆU Cung cấp các lệnh Transact-SQLtrong 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ả.1.2 Ý nghĩa Truy xuất đến tập bản ghi và thực hiện các thao tác do người lập trình xử lý một số yêu cầu Xử dụng con trỏ để thực hiện trên từng bản ghi .II.Thao tác trên con trỏ2.1 Cú phápLệ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ó thể sử dụng cú pháp chuẩn SQL 92 hoặc cú pháp T_SQL mở rộng− Cú pháp SQL 92 chuẩn:Declare cursor_name [Insensitive] [Scroll] CursorFor select_statement[ For {Read only| Update [of column_name [,…n] ] }]− Cú pháp T_SQL mở rộngDeclare cursor_name Cursor[ Local | Global ][ Forward_only| Scroll] [ Static| Dynamic][ Read_only]For select_statement[ For Update [ of column_name [,…n] ] ]Các thuộc tính được giải nghĩa dưới đây:LocalPage 2TIỂU LUẬN MÔN HỆ QUẢN TRỊ CƠ SỞ DỮ LIỆUChỉ ra phạm vi hạn chế của con trỏ trong thủ tục 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 trữ hoặc bẫy lỗi bị hủy bỏ. cursor cục bộ, chỉ có thể sử dụng trong phạm vi một khối (query batch) hoặc một thủ tục/ hàm. GLobalChỉ 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 trữ nào. Cursor toàn cục, có thể sử dụng trong một thủ tục/hàm hay một query batch bất kỳ hoặc đến khi bị hủy một cách tường minhForward_onlyChỉ 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, cursor chỉ có thể duyệt một chiều từ đầu đến cuốiScrollChỉ 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, có thể duyệt lên xuống cursor tùy ý (duyệt theo đa chiều)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ácbả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.Page 3TIỂU LUẬN MÔN HỆ QUẢN TRỊ CƠ SỞ DỮ LIỆUKeysetChỉ 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. trong thời gian tồn tại, nội dung của cursor có thể thay đổi nếu dữ liệu trong các bảng liên quan có thay đổi. Fast_forwardChỉ đị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ỏ FORWARDONLY và FAST_FORWARD là loại trừ lẫn nhau.Read_onlyNghiê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. chỉ có thể đọc từ cursor, không thể sử dụng cursor để update dữ liệu trong các bảng liên quan (ngược lại với “for update…” )Scroll_locksChỉ 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ớiFAST_FORWARD.OptimisticPage 4TIỂU LUẬN MÔN HỆ QUẢN TRỊ CƠ SỞ DỮ LIỆUChỉ 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 OFColumn_name[,…n] được sử dụng, chỉ các cột nằm trong danh sách đượ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ỏ.Insensitive/ static: Nội dung của cursor không thay đổi trong suốt thời gian tồn tại, trong trường hợp này cursor chỉ là read onlyLưu ý: Tên cursor trong các cách khai báo không bắt đầu bằng ký tự “@”Mặc định khi khai báo cursor nếu không chỉ ra các tùy chọn thì cursor có các tính chất:- Global- Forward_only- Read only hay “for update” tùy thuộc vào câu truy vấn- DynamicPage 5TIỂU LUẬN MÔN HỆ QUẢN TRỊ CƠ SỞ DỮ LIỆU2.2 Các bước tạo một con trỏ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:Bước 1: Mở con trỏ : open tên con trỏBước 2: Nhận về các bản ghi :Fetch tên con trỏBước 3 : Đóng con trỏClose tên con trỏBước 4 :Giải phóng con trỏ Deallocate tên con trỏ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ệnh Open được sử dụng để mở một con trỏ.Cú pháp là:OPEN <Cursor_name>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 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 <Cursor_name>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 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 <Cursor_name>Khi con trỏ không cần thiết thêm nữa, tham chiếu đến nó được huỷ bỏ. LệnhPage 6TIỂU LUẬN MÔN HỆ QUẢN TRỊ CƠ SỞ DỮ LIỆUDeallocate sử dụng để giải phóng tham chiếu tới con trỏ. Cú pháp là:Deallocate<Cursor_name>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 duyệt và nhận về dữ liệu ở chương tiếp.2.3 Truy xuất và duyệ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 thì 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 nlà 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 _STATUSPage 7TIỂU LUẬN MÔN HỆ QUẢN TRỊ CƠ SỞ DỮ LIỆUBiếnnày trảvề một sốnguyênbiễudiễnkết quảcủalệnhtruy xuấtcuốicùngcủacontrỏ.@@CURSOR_ROWSBiến này trả về tổng số hàng hiện tại trong con trỏ đang mở.Hình11.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.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. Thực hiện đoạn lệnh sau ở trong Query Analyzer :DECLARE JobsCursor CURSOR FORWARD_ONLYPage 8TIỂU LUẬN MÔN HỆ QUẢN TRỊ CƠ SỞ DỮ LIỆU FORSELECT * FROM jobs WHERE min_lvl = 75FOR UPDATEOPEN JobsCursorFETCH JobsCursorUPDATE jobsSET max_lvl = 100 WHERE CURRENT OF JobsCursorWHERE CURRENT OF Cursor2SELECT * FROM jobs WHERE min_lvl = 75CLOSE JobsCursorDEALLOCATE JobsCursor.( Kết quả được hiển thị ở hình 2 )Hình 2 : Ví dụ về con trỏ FORWARD_ONLYPage 9TIỂU LUẬN MÔN HỆ QUẢN TRỊ CƠ SỞ DỮ LIỆUCâ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 đó.Tạo contrỏ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ó.Thực hiện đoạn lệnh sau:DECLARE PubInfo Cursor CURSOR READ_ONLY FORSELECT * FROM pub_infoWHERE pub_id between 1000 and 2000OPEN PubInfo CursorFETCH PubInfo CursorDELETE FROM pub_infoWHERE CURRENT OF PubInfo CursorCLOSE PubInfo CursorDEALLOCATE PubInfo CursorLệnh DELETE trả về một lỗi được hiển thị trong hình 3Page 10TIỂU LUẬN MÔN HỆ QUẢN TRỊ CƠ SỞ DỮ LIỆUHình 3:Ví dụ về con trỏ READ_ONLYChú ý :Trước khi lập trình con trỏ ta phải khai báo con trỏ bằng câu lệnh : declare tên con trỏ cursor For [câu lệnh select]2.4 Biến cursorTa có thể khai báo một biến kiểu cursor và gán cho nó tham chiếu đến một cursor đang tồn tạiBiến cursor có thể được xem như là con trỏ cursorBiến cursor là một biến cục bộBiến cursor sau khi gán giá trị được sử dụng như một cursor thông thườngVí dụ :Page 11TIỂU LUẬN MÔN HỆ QUẢN TRỊ CƠ SỞ DỮ LIỆUDeclare @cur_var cursorset @cur_var = my_cur my_cur là một cursor đang tồn tạihoặc:Declare @cur_var cursorset @cur_var = cursor for select_statementKết hợp cursor với stored procedureXây dựng SP tính điểm trung bình và xếp loại cho sinh viên thuộc lớp cho trước. Giả sử có các quan hệ như sau:SinhVien (MaSV, HoTen, DTB, XepLoai, Lop)MonHoc (MaMH, TenMH)KetQua (MaMH, MaSV, LanThi, Diem)Biết rằngĐiểm thi chỉ tính lần thi sau cùngXếp loại: Xuất sắc [9, 10], Giỏi [8, 8.9], Khá [7, 7.9], Trung bình [5.0, 6.9], Yếu[0, 4.9]Kết quả ghi xuống CSDL, đồng thời xuất ra tổng số sinh viên xếp loại giỏi của lớp đó•Phân tích ví dụ:Lớp cần xét có nhiều sinh viên, từng sinh viên cần được xử lý thông qua 3 bước:Tính điểm trung bình cho sinh viên, điểm trung bình phải là điểm của lần thi sau cùng. Có thể tái sử dụng thủ tục XepLoaiSVLopDựa vào điểm trung bình của sinh viên để xác định xếp loạiPage 12TIỂU LUẬN MÔN HỆ QUẢN TRỊ CƠ SỞ DỮ LIỆUCập nhật điểm và xếp loại vào bảng sinh viênMọi sinh viên đều lặp lại 3 bước trên. Từ phân tích trên ta thấy:Cần xử lý nhiều phần tử (các sinh viên)Mỗi phần tử xử lý tương đối phức tạp (truy vấn, tính toán, gọi thủ tục khác, điều kiện rẽ nhánh, cập nhật dữ liệu, …)Cách xử lý các phần tử là như nhau⇒ Sử dụng cursor là thích hợpCursor chứa các sinh viên của lớp cần xét, chỉ cần chứa mã sinh viên là được• Xây dựng thủ tụcCreate procedure XepLoaiSVLop@Lop nvarchar(10), @SoSVGioi int outAsDeclare @DTB floatDeclare @XepLoai nvarchar(20) Declare @MaSV nvarchar(10) Declare cur_SV cursorFor (select MaSV from SinhVien where Lop=@Lop) Open cur_SVFetch Next from cur_SV into @MaSV While @@FETCH_STATUS = 0BeginEndExec XepLoaiSV @MaSV, @DTB output, @XepLoai output Update SinhVien set DTB = @DTB, XepLoai=@XepLoai Where MaSV= @MaSVFetch Next from cur_SV into @MaSVPage 13TIỂU LUẬN MÔN HỆ QUẢN TRỊ CƠ SỞ DỮ LIỆUClose cur_SV Deallocate cur_SVSet @SoSVGioi = (select count(*) from sinhvienwhere lop = @Lop and XepLoai = N’Giỏi’)GoTừ những kiến thức chung về cursor áp dụng thực hiện một số bài toán2.5 Ví dụ minh họaVí dụ 1:Tạo ra con trỏ có tên là cur_sv chứa tập bản ghi về thông tin sinh viên . Hãy duyệt tất cả các bản ghi về thông tin sinh viên đó và hiển thị kết quả ra màn hình . Declare cur_sv cursor For (select * from sinhvien) Mở con trỏOpen cur_sv Đặt giá trị khởi đầu cho con trỏ Fetch first from cur_sv Duyệt tất cả các bản ghi của con trỏWhile (@@Fetch_status=0) Duyệt bản ghi tiếp theo Fetch next from Cur_sv Đóng con trỏClose cur_sv Giải phóng con trỏPage 14TIỂU LUẬN MÔN HỆ QUẢN TRỊ CƠ SỞ DỮ LIỆUDeallocate cur_svVí dụ 2 :Tạo hai biến Masv và hoten , hiển thị thông tin của sinh viên thông qua hai biến của masv và hoten đó .(coi như đã có bảng sinh viên) Declare @masv varchar (10)Declare @hoten nvarchar (50)Declare @cur_sv cursorFor (select masv, hoten from sinhvien )Open cur_sv Fetch next from cur_sv into @masv, @hotenWhile @@ Fetch_status =0Fetch next from cur_sv into @masv, @hotenClose cur_svDeallocate cus_svVí dụ 3 Thêm cột xếp loại rồi cập nhật giá trị cho bảng xếp loại với các thông số tương ứng là: gioi>=8, 7<= kha <8, 5 < = trung bình <7, yếu < 5Thêm cột xếp loạialter table ketquaadd xeploai varchar(20)Thực hiện yêu cầu bài toándeclare @masv varchar(10)Page 15TIỂU LUẬN MÔN HỆ QUẢN TRỊ CƠ SỞ DỮ LIỆUdeclare @madt varchar(10)declare @xeploai varchar(20)declare @diem int Tạo con trỏ declare cur_sv cursorfor select masv, madt,diem from ketqua Mở con trỏopen cur_sv Duyệt bản ghi đầu tiênfetch next from cur_sv into @masv, @madt, @diem Kiểm tra điều kiện xem có bản ghi hay không ?while(@@fetch_status=0)begin Câ lệnh rẽ nhánhif @diem <5set@xeploai='yeu'if @diem>=5 and @diem<7 set@xeploai ='Trung binh'if @diem>=7 and @diem<8set@xeploai='Kha'Page 16TIỂU LUẬN MÔN HỆ QUẢN TRỊ CƠ SỞ DỮ LIỆUif @diem>= 8set@xeploai='gioi' Thực hiện công việc yêu cầu của bài toánupdate ketquasetxeploai=@xeploaiwhere masv=@masv and madt=@madt Duyệt bản ghi tiếp theofetch next from cur_sv into @masv, @madt, @diemendVí dụ 4Bổ sung thêm cột số lượng vào bảng đề tài , viết thủ tục lưu trữ để cập nhật cột số lượng của đề tài bằng số sinh viên tham gia đề tài đó . ( Kết hợp thủ tục vào con trỏ ) Thêm cột số lượng vào bảng đề tàialter table detaiadd soluong int Tạo ra bảng ảo chứa số lượng sinh viên của mỗi đề tài declare @madt varchar(10)declare @soluong intdeclare cur_sosv cursorfor select madt,count(masv) from sinhvien_detai group by madtPage 17TIỂU LUẬN MÔN HỆ QUẢN TRỊ CƠ SỞ DỮ LIỆU Mở con trỏOPEN cur_sosv Duyệt bản ghi đầu tiên fetch next from cur_sosv into @madt,@soluong Kiểm tra điều kiện có bản ghi không ?while(@@fetch_status =0)beginupdate detaisetsoluong=@soluongwhere madt=@madt Trường hợp nếu có đề tài mà chưa có sinh viên nào thực hiệnupdate detaisetsoluong=0where madt not in (select madt from sinhvien_detai) Duyệt các bản ghi tiếp theofetch next from cur_sosv into @madt,@soluongend Đóng con trotclose cur_sosv Giải phóng con trỏdeallocate cur_sosvPage 18TIỂU LUẬN MÔN HỆ QUẢN TRỊ CƠ SỞ DỮ LIỆUTổng kếtTài liệu tham khảoMục lục Page 19

Tài liệu liên quan

tìm hiều về Đất đai trong ngành sản xuất Nông nghiệp ở Việt Nam
- 17
- 930
- 0
Tìm hiểu về thực trạng trồng một số giống lạc ở xã nghi liên TP vinh nghệ an luận văn tốt nghiệp đại học
- 41
- 691
- 0
tìm hiểu về vai trò hoạt động đầu tư trực tiếp nước ngoài (fdi) đối với sự phát triển của việt nam thời kì 2000 – 2008
- 42
- 577
- 0
Thảo luận: Tìm hiểu về lao động trong nông thôn docx
- 39
- 1
- 0
Tìm Hiểu Về SC - FDMA Trong Truyền Thông Di Động
- 32
- 1
- 2
tìm hiểu về con người và những giá trị văn hóa truyền thống của hàn quốc
- 44
- 1
- 0
Tìm hiểu về côn trùng học entomology
- 31
- 722
- 2
TÌM HIỂU VỀ VAI TRÒ, QUÁ TRÌNH TỔNG HỢP VÀ PHÂN GIẢI CÁC AXITAMIN TRONG CƠ THỂ NGƯỜI VÀ NGUYÊN LIỆU THỰC PHẨM - TIỂU LUẬN HÓA SINH THỰC PHẨM
- 62
- 1
- 0
Tìm hiểu về bảo mật trong Wlan
- 99
- 590
- 1
Giáo trình hướng dẫn tìm hiểu về vai trò quan trọng của cơ sở vật trong sự tồn tại và phát triển của xã hội phần 5 pptx
- 12
- 498
- 0