Bài giảng: Hệ quản trị cơ sở dữ liệu 2 SQL Server - Th.S Nguyễn Trí Nhân

iu thc s" ñưc tr v khi vic so sánh ca biu thc b)ng vi các giá tr so sánh tương ng. • Bi'u th3c k4t qu N: là biu thc s" ñưc tr v khi tt c các trưng hp so sánh ñu không b)ng vi các giá tr ñưa ra. Ví d : Đ hin th danh sách các vt tư có trong bng VATTU theo t%ng loi hàng, có ñm tng s các vt tư theo t%ng loi hàng. Bn s dng lnh SELECT FROM có kt hp biu thc CASE ñơn gin như sau: SELECT LOAI = CASE LEFT(MAVTU, 2) WHEN 'DD' THEN 'Đu DVD' WHEN 'VD' THEN 'Đu VCD' WHEN 'TV' THEN 'Ti vi' WHEN 'TL' THEN 'T laïnh' WHEN 'BI' THEN 'Bia lon' ELSE 'Chưa phân loi' END, MAVTU, TENVTU, DVTINH FROM VATTU ORDER BY LEFT(MAVTU, 2) COMPUTE COUNT(MAVTU) BY LEFT(MAVTU,2) Kt qu truy vn tr v: Bài ging: H qun tr cơ s d liu 2 – SQL Server Bc Cao Đ ng Công ngh Thông tin Ging viên: Th.S Nguy
n Trí Nhân 112 Khoa Công ngh Thông tin Đi hc Phm Văn Đng Cú pháp CASE d ng tìm kim: Trong ñó: • Bi'u th3c logic1, bi'u th3c logic2: là các biu thc lun lý dùng ñ th$c hin các phép so sánh trong biu thc CASE. • Bi'u th3c k4t qu 1, bi'u th3c k4t qu 2: là các biu thc s" ñưc tr v khi mt trong các biu thc lun lý so sánh có kt qu là ñúng. Bài ging: H qun tr cơ s d liu 2 – SQL Server Bc Cao Đ ng Công ngh Thông tin Ging viên: Th.S Nguy
n Trí Nhân 113 Khoa Công ngh Thông tin Đi hc Phm Văn Đng • Bi'u th3c k4t qu N: là biu thc s" ñưc tr v khi tt c các biu thc logic so sánh ñưa ra ñu sai. Ví d : Đ hin th danh sách các vt tư có trong bng VATTU, thông tin b sung thêm chu<i ghi chú, tùy thuc vào giá tr ca ct tD l ph*n trăm giá bán. Bn s dng lnh SELECT FROM có kt hp biu thc CASE tìm kim như sau: SELECT GHICHU = CASE WHEN PHANTRAM < 20 THEN 'Li ít' WHEN PHANTRAM BETWEEN 20 AND 40 THEN 'Li nhiu' ELSE 'R t li' END, MAVTU, TENVTU, DVTINH FROM VATTU ORDER BY PHANTRAM Kt qu truy vn tr v: Th$c t thì nhưng ngưi lp trình trong môi tưng Transaction-SQL thưng s dng biu thc CASE tìm kim bi vì khi ñó các biu thc lun lý mà bn dùng ñ so sánh ñưc phép cha nhiu toán t so sánh khác nhau, trong khi ñó biu thc CASE ñơn gin  ph*n trên ch( cho phép bn th$c hin phép so sánh b)ng (=) trên mt biu thc ñơn gin. Ví d : Đ gim giá bán hàng trong tháng 02/2013 theo quy tc: • Nu s lưng hàng <= 2 thì không gim giá. • Nu s lưng hàng t% 3 ñn 10 thì gim 10%. Bài ging: H qun tr cơ s d liu 2 – SQL Server Bc Cao Đ ng Công ngh Thông tin Ging viên: Th.S Nguy
n Trí Nhân 114 Khoa Công ngh Thông tin Đi hc Phm Văn Đng • Nu s lưng hàng > 10 thì gim 20%. Bn s dng lnh UPDATE SET có kt hp biu thc CASE tìm kim như sau: UPDATE CTPXUAT SET DGXUAT = CASE WHEN SLXUAT <=2 THEN DGXUAT WHEN SLXUAT BETWEEN 3 AND 10 THEN DGXUAT* 0.9 ELSE DGXUAT *0.8 END FROM CTPXUAT CTPX INNER JOIN PXUAT PX ON PX.SOPX = CTPX.SOPX WHERE CONVERT(CHAR(7), NGAYXUAT, 21) = '2013-02' Tóm l i: biu thc CASE có th ñưc phép kt hp s dng trong các câu lnh SELECT, UPDATE SET, DELETE dùng ñ bin lun các trưng hp khác nhau ca các giá tr d liu bên trong mt câu lnh truy vn. 6.4.2. Cấu trúc rẽ nhánh IF... ELSE Vi cu trúc r= nhánh, ngưi lp trình có th ch( mt mt ho#c nhiu câu lnh s" ñưc th$c hin khi giá tr ca mt biu thc lun lý là ñúng ho#c là sai. Cu trúc r" nhánh ñưc phép s dng bên trong mt lô (batch) các lnh ho#c bên trong mt Procdure Store. Cu trúc r" nhánh ñưc phép lng nhiu cp bên trong và cp ñ lng nhau ca các cu trúc r" nhánh là không có gii hn. Cú pháp: Trong ñó: • Bi'u th3c lun lý: thông thưng là mt biu thc so sánh dùng ñ ch( ra mt ñiu kin so sánh nào ñó. • Câu l,nh 1/Kh+i l,nh 1: các lnh s" ñưc th$c hin khi biu thc lun lý so sánh có giá tr là ñúng (True). • Câu l,nh 2/Kh+i l,nh 2: các lnh s" ñưc th$c hin khi biu thc lun lý so sánh có giá tr là sai (False).  Lưu ý: Khi lnh ñưc ñ cp là mt tp hp t% hai câu lnh tr lên, trong các trưng hp này bt buc bn phi s dng các t% khóa BEGIN và END ñ hình thành mt nhóm các câu lnh trong mt khi lnh. Bài ging: H qun tr cơ s d liu 2 – SQL Server Bc Cao Đ ng Công ngh Thông tin Ging viên: Th.S Nguy
n Trí Nhân 115 Khoa Công ngh Thông tin Đi hc Phm Văn Đng Cú pháp: Ví d : Tính xem có vt tư nào ñã bán ra vi s lưng nhiu hơn 4 không ? Nu có thì in ra danh sách các vt tư ñó, ngưc li thì thông báo chưa bán ñưc vt tư nào vi s lưng nhiu hơn 4. Bn s dng cú pháp IF... ELSE như sau: IF (SELECT COUNT(*) FROM CTPXUAT WHERE SLXUAT >4) >0 BEGIN PRINT 'Danh sách cacs hàng hóa bán vi s lưng >4' SELECT CTPX.MAVTU, TENVTU, SLXUAT FROM CTPXUAT CTPX INNER JOIN VATTU VT ON VT.MAVTU = CTPX.MAVTU WHERE SLXUAT >4 END ELSE PRINT 'Chưa bán hàng nào vi s lưng > 4' Kt qu tr v:  Nhn xét: trong ví d trên, s dng biu thc lun lý là mt biu thc so sánh có s dng truy vn SELECT COUNT WHERE dùng ñ ñm các hàng hóa bán ra có s lưng ln hơn 4. Nu kt qu ñm > 0 thì in danh sách các vt tư ñó ra. Ngưc li thông báo chưa bán hàng hóa vi s lưng nhiu hơn 4. Khi lp trình trong Transaction-SQL, thông thưng bn c*n phi kim tra d liu có tn ti bên trong các bng trưc khi th$c hin tip các hành ñng liên quan ñn các dòng d liu ñó. Cú pháp IF có kt hp t% khóa EXISTS dùng ñ kim tra s$ tn ti ca các dòng d liu bên trong bng rt hu hiu. Bài ging: H qun tr cơ s d liu 2 – SQL Server Bc Cao Đ ng Công ngh Thông tin Ging viên: Th.S Nguy
n Trí Nhân 116 Khoa Công ngh Thông tin Đi hc Phm Văn Đng Cú pháp: Trong ñó: • T< khóa EXISTS: dùng ñ kim tra s$ tn ti các dòng d liu trong câu lnh truy vn SELECT sau ñó. Kt qu IF tr v ñúng (True) khi câu lnh SELECT tr v ít nht mt dòng d liu, ngưc li thì tr v sai (False). Ví d : Th$c hin li ví d trên nhưng s dng cú pháp IF EXISTS dùng ñ kim tra xem ñã có hàng hóa nào bán ra vi s lưng nhiu hơn 4 chưa. IF EXISTS (SELECT * FROM CTPXUAT WHERE SLXUAT >4) BEGIN PRINT 'Danh sách các hàng hóa bán vi s lưng >4' SELECT CTPX.MAVTU, TENVTU, SLXUAT FROM CTPXUAT CTPX INNER JOIN VATTU VT ON VT.MAVTU = CTPX.MAVTU WHERE SLXUAT >4 END ELSE PRINT 'Chưa bán hàng nào vi s lưng > 4' Ví d : Đ kim tra xem có phiu nhp hàng nào ñã lp vào ngày ch nht không. Nu có thì in ra danh sách các phiu nhp hàng ñó. Bn s dng cú pháp IF như sau: IF EXISTS (SELECT * FROM PNHAP WHERE DATENAME(DW, NGAYNHAP) = 'Sunday') BEGIN PRINT 'Danh sách các phiu nhp vào ngày ch nht' SELECT * FROM PNHAP WHERE DATENAME(dw, NGAYNHAP) = 'Sunday' END  Nhn xét: trong ví d trên s dng hàm DATENAME vi tham s dw (vit tt ca t% day of week, ngày trong tu*n) dùng ñ tr v ngày trong tu*n ca ngày nhp hàng. Bài ging: H qun tr cơ s d liu 2 – SQL Server Bc Cao Đ ng Công ngh Thông tin Ging viên: Th.S Nguy
n Trí Nhân 117 Khoa Công ngh Thông tin Đi hc Phm Văn Đng 6.4.3. Cấu trúc lặp WHILE Vi cu trúc l#p, ngưi lp trình có th ch( ñnh mt ho#c nhiu câu lnh s" ñưc th$c hin l#p li nhiu l*n trong khi giá tr ca biu thc lun lý so sánh v9n còn ñúng. Ging như cu trúc r" nhánh, cu trúc l#p ñưc phép s dng bên trong mt lô (batch) các lnh ho#c bên trong mt Procdure Store. Gia cu trúc r" nhánh và cu trúc l#p không có th t$ ưu tiên khi chúng lng vào nhau và cp ñ lng nhau là không có gii hn. Th$c t vic s dng cu trúc l#p WHILE b gii hn trong nhiu trưng hp. Bi vì bn thân các lnh truy vn cp nht d liu như là: SELECT, UPDATE SET, DELETE trong Transaction-SQL ñã t$ ñng th$c hin vic l#p t% dòng d liu ñ*u tiên ñn dòng d liu cui cùng bên trong bng. Cu trúc l#p WHILE thông thưng ñưc dùng vi các bin có kiu d liu cursor, cách thc s dng bin kiu d liu cursor s" ñưc hưng d9n trong các ph*n sau. Cú pháp: Trong ñó: • Biu thc lun lý: thông thưng là mt biu thc so sánh ñ ch( các lnh s" ñưc l#p li trong khi mà giá tr ca biu thc v9n còn ñúng. • Các lnh l#p: các câu lnh ñưc th$c hin bên trong vòng l#p. Ví d : Đ in ra 10 s nguyên dương bt ñ*u t% 100. Bn s dng cu trúc l#p WHILE như sau: DECLARE @SoNguyen int SET @SoNguyen = 100 While (@SoNguyen < 110) BEGIN PRINT' S nguyên:' + Convert(char(3), @Songuyen) SET @SoNguyen = @SoNguyen +1 END Kt qu tr v: Bài ging: H qun tr cơ s d liu 2 – SQL Server Bc Cao Đ ng Công ngh Thông tin Ging viên: Th.S Nguy
n Trí Nhân 118 Khoa Công ngh Thông tin Đi hc Phm Văn Đng Bn có th s dng t% khóa BREAK lng vào cu trúc l#p WHILE ñ có th kt thúc vic l#p ca các lnh bên trong vòng l#p mà không c*n xét ñn giá tr tr v ca biu thc lun lý dùng ñ so sánh phía sau t% khóa WHILE phi là sai. Tuy nhiên t% khóa BREAK thưng ñưc s dng kèm theo vi mt biu thc lun lý khác. Ví d : Th$c hin vic l#p ging ví d trên, tuy nhiên mun r)ng vòng l#p s" b kt thúc khi mi in ti s nguyên 105. Bn s dng cu trúc l#p WHILE như sau: DECLARE @SoNguyen int SET @SoNguyen = 100 While (@SoNguyen < 110) BEGIN PRINT N' S nguyên:' + Convert(char(3), @Songuyen) IF @SoNguyen = 105 BREAK SET @SoNguyen = @SoNguyen +1 END PRINT N'Kt thúc vòng lp' Kt qu tr v: Bn cũng có th s dng t% khóa CONTINUE lng vào cu trúc l#p WHILE ñ ch( ñnh các lnh bên trong vòng l#p  phía dưi t% khóa CONTINUE tm thi không th$c hin tip, khi ñó con tr vòng l#p s" nhy v ñ*u dòng l#p ñ kim tra giá tr ca biu thc Bài ging: H qun tr cơ s d liu 2 – SQL Server Bc Cao Đ ng Công ngh Thông tin Ging viên: Th.S Nguy
n Trí Nhân 119 Khoa Công ngh Thông tin Đi hc Phm Văn Đng lun lý so sánh là v9n còn ñúng hay không. Tuy nhiên t% khóa CONTINUE thông thưng ñưc dùng kèm theo vi mt biu thc lun lý khác. Ví d : Th$c hin vic l#p ging các ví d trên, tuy nhiên mun r)ng vòng l#p s" in xót s nguyên 105. Bn s dng cu trúc l#p WHILE như sau: DECLARE @SoNguyen int SET @SoNguyen = 99 While (@SoNguyen < 110) BEGIN SET @SoNguyen = @SoNguyen +1 IF @SoNguyen = 105 CONTINUE PRINT N' S nguyên:' + Convert(char(3), @Songuyen) END PRINT N'Kt thúc vòng lp' Kt qu tr v: Kt qu tr v bn suy lun khi vòng l#p th$c hin ñn giá tr ca bin @Songuyen = 105 thì khi ñó lnh PRINT "S nguyên: "... không ñưc th$c hin và con tr chương trình ñưc quay lên ñ*u vòng l#p ñ kim tra tip biu thc lun lý so sánh. Sơ ñ tóm tt ý nghĩa ca cu trúc lp WHILE kèm vi các t khóa CONTINUE hoc BREAK: Cú pháp: Bài ging: H qun tr cơ s d liu 2 – SQL Server Bc Cao Đ ng Công ngh Thông tin Ging viên: Th.S Nguy
n Trí Nhân 120 Khoa Công ngh Thông tin Đi hc Phm Văn Đng Ví d : Đ tăng t$ ñng tD l ph*n trăm cho các vt tư trong bng VATTU theo quy tc sau: • M<i l*n ch( tăng lên 5% cho các vt tư có giá tr ti ct tD l nh hơn 30%. • L#p li hành ñng tăng trong khi mà giá tr trung bình tD l ph*n trăm ca các vt tư v9n còn thp hơn 40%. Bn s dng các lnh như sau: --In giá tr trung bình trưc khi tăng PRINT 'Trung bình t l phn trăm trưc khi tăng' SELECT AVG(PHANTRAM) FROM VATTU --Bt ñu thc hin vic lp t ñ
ng DECLARE @Lantang int SET @LanTang = 0 While (Select AVG(PhanTram) From VATTU) < 40 BEGIN UPDATE VATTU SET PHANTRAM = PHANTRAM - 5 WHERE PHANTRAM <30 SET @Lantang = @Lantang +1 IF NOT EXISTS (SELECT * FROM VATTU WHERE PHANTRAM <30) BREAK END -- In giá tr trung bình t l sau khi tăng PRINT 'Đã tăng:'+ CONVERT(VARCHAR(7), @LanTang) + 'Ln' PRINT 'Trung bình t l phn trăm sau khi tăng ' SELECT AVG(PHANTRAM) FROM VATTU Bài ging: H qun tr cơ s d liu 2 – SQL Server Bc Cao Đ ng Công ngh Thông tin Ging viên: Th.S Nguy
n Trí Nhân 121 Khoa Công ngh Thông tin Đi hc Phm Văn Đng  Nhn xét: ví d trên khá phc tp, do th bn phi chèn thêm vào các ghi chú (comment) ñ giúp bn thy ñưc t%ng x lý ri rc nh)m dA xem, dA hiu. Trong Transaction-SQL, bn có th chèn các ghi chú trong các câu lnh b)ng hai du tr% liên tip nhau ho#c mun che li mt khi các lnh liên tip nhau thì bn s" s dng c#p ký t$ như bên dưi: Ngoài ra trong vòng l#p bn có th s dng lnh IF EXISTS dùng ñ kim tra trưng hp sau khi ñã tăng ht tt c tD l ph*n trăm các vt tư vi ñu ln hơn 30 mà trung bình tD l ph*n trăm ca các vt tư v9n chưa ln hơn 40 thì bt buc vòng l#p phi ñưc thoát ra ngoài, bi vì nu không thì vòng l#p s" b l#p vô tn không bao gi thoát ra ñưc. 6.5. SỬA, XÓA, ĐỔI TÊN THỦ TỤC (STORE PROCEDURE) 6.5.1. Hủy bỏ Procdure Store: Khi mt Procdure Store không còn c*n s dng na thì bn có th hy b nó ra kh i cơ s d liu. C'n thn khi s dng hành ñng này bi vì bn s" không có cơ hi phc hi li ni dung ca th tc sau khi ñã xóa. Cú pháp lnh DROP PROCEDURE bên dưi cho phép bn có th hy b mt Procdure Store. Cú pháp: DROP PROC[EDURE] Tên_Th(_T;c Trong ñó: • Tên th( t;c: tên Procdure Store ñã ñưc to trưc ñó mà bn mun hy b khi không còn s dng na. Ví d : Đ xóa th tc spud_MaxSLVattu_200201 ñã ñưc to ra trong ví d trưc ñó, bn th$c hin lnh DROP PROCEDURE như sau: DROP PROC spud_MaSLVatTu_200201 6.5.2. Thay đổi nội dung của Procdure Store: Đôi khi ni dung ca các Procdure Store c*n phi thay ñi li ñ cho các hành ñng bên trong Procdure Store th$c hin ñưc ñúng ñn theo các yêu c*u mi. Bn có hai cách th$c hin vic thay ñi ni dung bên trong ca Procdure Store: • Ho#c là dùng lnh xóa b Procdure Store cũ và to li Procdure Store mi vi ni dung ñưc thay ñi mi phù hp theo yêu c*u mi. • Ho#c là dùng lnh ALTER PROCEDURE ñ thay ñi ni dung cú pháp ca lnh ALTER PROCEDURE bên dưi hoàn toàn ging cú pháp ca lnh CREATE PROCEDURE. Cú pháp: Bài ging: H qun tr cơ s d liu 2 – SQL Server Bc Cao Đ ng Công ngh Thông tin Ging viên: Th.S Nguy
n Trí Nhân 122 Khoa Công ngh Thông tin Đi hc Phm Văn Đng Ví d : C*n b sung thêm ph*n kim tra d liu ñã có bán hàng trong tháng 01/2002 trong Procdure Store spud_MaxSLVattu_200201 ñã ñưc to ra  ví d trưc. Bn th$c hin lnh ALTER PROCEDURE như sau: ALTER PROC spud_MaxSLVatTu_200201 as DECLARE @sTenVTu varChar(50), @nMaxSL int if not Exists (Select MaVTu From CTPXuat CTPX INNER JOIN PXUAT PX ON PX.SOPX = CTPX.SOPX WHERE CONVERT(CHAR(7),NGAYXUAT,21) = '2002-01') BEGIN PRINT 'Tháng2002/01 chưa có bán vt tư nào c!' Return END SELECT @sTenVTu = RTrim(TenVTu), @nMaxSL = SLXuat FROM CTPXUAT CTPX INNER JOIN VATTU VT ON VT.MAVTU = CTPX.MAVTU JOIN PXUAT PX ON PX.SOPX = CTPX.SOPX WHERE (CONVERT(CHAR(7), NGAYXUAT, 21) = '2002-01') AND SLXUAT = (SELECT MAX(SLXUAT) FROM CTPXUAT INNER JOIN PXUAT ON PXUAT.SOPX = CTPXUAT.SOPX WHERE CONVERT(CHAR(7), NGAYXUAT, 21) = '2002-01') PRINT @sTenVTu + 'có doanh s bán cao nh t' PRINT 'Vi s lưng là: ' + cast (@nMaxSL as Char(10))  Nhn xét: Trong ví d trên, s dng lnh RETURN dùng ñ thoát ra kh i Procdure Store trong trưng hp khi không có vt tư nào bán ra trong tháng 01/2002. Ý nghĩa ca lnh RETURN dùng ñ thoát kh i mt Procdure Store, các dòng lnh phía sau lnh RETURN s" không ñưc th$c hin sau khi th tc th$c hin lnh RETURN. Bài ging: H qun tr cơ s d liu 2 – SQL Server Bc Cao Đ ng Công ngh Thông tin Ging viên: Th.S Nguy
n Trí Nhân 123 Khoa Công ngh Thông tin Đi hc Phm Văn Đng 6.6. XỬ LÝ LỖI TRONG STORE PROCEDURE (SP) Vì các Procdure Store ngày càng phc tp nên vic kêt hp các ñon lnh kim tra l<i  trong các Procdure Store là rt c*n thit. Chúng ta có th s dng mã tr v ho#c câu lnh RAISERROR ñ 6.6.1. Mã trả về Trong các Procdure Store, các mã tr v ch( có giá tr s nguyên. M#c ñnh, giá tr ca mã là 0, ch( ra r)ng vic th$c hin Procdure Store là thành công. Đ xut ra mã tr v, chúng ta c*n khai báo mt bin và th$c thi mt Procdure Store. Sau ñó chúng ta có th xem giá tr bin ñó. Mã tr v phi tr li giá tr ca chúng vào trong mt bin s. Câu lnh ñ khai báo bin s và s dng nó trong sut quá trình th$c thi th tc là: DECLARE @return_variable_name data_type EXECUTE @return_variable_name = procedure_name Trong ñó, @return_variable_name cha mã tr v bi Procdure Store. Ví d;: Hãy sa ñi th tc Titles_Pub ñ tr v giá tr 0, giá tr m#c ñnh ca SQL Server, nu nó th$c hin thành công thì hin th các hàng. Nu không có hàng nào ñưc tìm thy, nó s" tr v giá tr 1. Câu lnh ALTER PROCEDURE s" như sau: ALTER PROCEDURE Titles_Pub @v_pubid char(4) AS DECLARE @v_return int SELECT @v_return=COUNT(*) FROM titles WHERE pub_id = @v_pubid IF @v_return>0 SELECT * FROM titles WHERE pub_id = @v_pubid ELSE RETURN @v_return+1 Kt qu th$c thi th tc ñưc trình bày trong bng Bài ging: H qun tr cơ s d liu 2 – SQL Server Bc Cao Đ ng Công ngh Thông tin Ging viên: Th.S Nguy
n Trí Nhân 124 Khoa Công ngh Thông tin Đi hc Phm Văn Đng Hình 6.3. S dng mã tr v Bi vì không có tiêu ñ nào ñưc tìm thy ca publisher ID ‘9952’, nên th tc tr li giá tr 1 và thông báo tương ng ñưc hin th. 6.6.2. Câu lệnh RAISERROR Trong mt Procdure Store, chúng ta có th s dng lnh PRINT ñ hin th thông báo l<i do ngưi dùng ñnh nghĩa. Tuy nhiên, các thông báo này ch( là tm thi và ch( hin th cho ngưi s dng. Câu lnh RAISERROR ñưc s dng ñ ghi li nhng l<i này và gán cho chúng các cp ñ .Cú pháp là: RAISERROR ({msg_id | msg_str}{, severity, state} [WITH option[,...n]] Trong ñó, msg_id: Thông báo l<i c th do ngưi s dng ñnh nghĩa trong h thng bng




. msg_str: Thông báo ñ#c bit mà chúng ta ñã ñnh nghĩa, nhiu nht là ñn 255 kí t$. severity: Các cp ñ do ngưi s dng ñnh nghĩa liên quan vi thông báo ñ#c bit. Ngưi s dng có th s dng các cp ñ t% 0 ñn 18. Các cp ñ t% 19 ñn 25 ñưc dành riêng cho các thành viên trong sysadmin, và yêu c*u ñi kèm tùy chn WITH LOG . Các cp ñ t% 20-25 ñưc coi là mc cao. state: Các giá tr t% 1 ñn 127 trình bày tình trng l<i. option: Ch( ra khi nào l<i s" ñưc ghi vào trong nht ký l<i ca server. Ví d, ñon mã dưi ñây hin th th t$ s bt ñ*u t% s 5, theo th t$ gim d*n. Khi giá tr này là 2, mt l<i ñưc ñưa ra và vòng l#p b ñóng. CREATE PROCEDURE Myproc AS DECLARE @v_ctr INT Bài ging: H qun tr cơ s d liu 2 – SQL Server Bc Cao Đ ng Công ngh Thông tin Ging viên: Th.S Nguy
n Trí Nhân 125 Khoa Công ngh Thông tin Đi hc Phm Văn Đng SELECT @v_ctr = 5 WHILE @v_ctr > 0 BEGIN SELECT @v_ctr * @v_ctr SELECT @v_ctr = @v_ctr - 1 IF @v_ctr = 2 BEGIN RAISERROR('Counter has fallen below 3', 1, 2) BREAK END END Kt qu ñ*u ra ca ñon mã trên là: 25 16 9 Bng thông báo s" hin th ra như sau: (1 row(s) affected) (1 row(s) affected) (1 row(s) affected) Msg 50000, Level 1, State 50000 Counter has fallen below 3 6.7. BÀI TẬP THỰC HÀNH 1. Vit báo cáo bng kê chng t% bao gm các thông tin: Mã Ct, S Ct, Ngày Ct, DiAn gii, Mã Đt, Mã Vt, Tên Vt, Đvt, S lưng, Đơn giá, Thành Tin. 2. Vit báo cáo tng hp nhp kho bao gm các thông tin: Mã vt tư, Tên Vt, Đvt, S lưng, Đơn giá, Thành tin (nhóm theo t%ng vt tư). 3. Vit báo cáo s chi tit vt tư bao gm các thông tin: Mã Ct, S Ct, Ngày Ct, DiAn gii, Mã Vt, Tên Vt, Đvt, Sl Nhp, Tin nhp, Sl xut, Tin xut, Sl tn, giá tr tn (lit kê chi tit các l*n nhp xut). 4. Vit báo cáo tng hp nhp – xut – tn bao gm các thông tin: Mã vt tư, Tên vt tư, Đvt, Tn ñ*u, dư ñ*u, Sl Nhp, Tin nhp, Sl Xut, Tin Xut, Tn cui, Dư cui (Nhóm theo t%ng vt tư). Bài ging: H qun tr cơ s d liu 2 – SQL Server Bc Cao Đ ng Công ngh Thông tin Ging viên: Th.S Nguy
n Trí Nhân 126 Khoa Công ngh Thông tin Đi hc Phm Văn Đng Chương 7. CHUYỂN TÁC, BẪY LỖI VÀ KIỂU CON TRỎ TRONG SQL SERVER 7.1. CHUYỂN TÁC (TRANSACTION) 7.1.1. Khái niệm Giao tác trong các loi cơ s d liu quan h ln ñưc s dng trong nhng trưng hp mà các hành ñng cp nht d liu trên nhiu bng khác nhau ñưc th$c hin trong cùng mt ñơn v (unit). Nói mt cách khác thì các hành ñng cp nht d liu trong mt ñơn v s" ñưc ghi nhn li khi tt c các hành ñng con bên trong ñó th$c hin thành công, ngưc li nu có ít nht mt hành ñng nào ñó th$c hin tht bi thì tt c các hành ñng bên trong ñơn v s" b hy b ñ ñm bo tính toàn v!n ca d liu trong các bng. Ví d;: Bn hình dung mt khách hàng có cùng lúc 2 loi tài khon trong ngân hàng. Mt là tài khon thanh toán dùng ñ th$c hin các giao dch thu chi qua li ca khách hàng vi các công ty khác. Hai là tài khon tit kim cá nhân ca khách hàng cho phép khách hàng gi tin tit kim ñ ly tin lãi cui kỳ theo kỳ hn 3 tháng. Gi s sau thi gian 3 tháng, khách hàng ñn ngân hàng ñ nhn s tin lãi t% tài khon tit kim cá nhân. Tuy nhiên khách hàng này mun b phn giao dch tài khon th$c hin t$ ñng chuyn s tin lãi t% tài khon tit kim sang tài khon thanh toán ca mình. Nhn xét thy r)ng trong h thng chương trình ti ngân hàng phi th$c hin hai hành ñng cp nht d liu: mt là ly ra s tin lãi trong tài khon tit kim, hai là np s tin lãi vào tài khon thanh toán. Chuyn gì xy ra nu mt trong hai hành ñng th$c hin không thành công mà hành ñng còn li v9n ñưc ghi li nhn vào cơ s d liu? Bn cùng xem xét như sau: • Trư1ng h)p 1: nu hành ñng rút s tin lãi trong tài khon tit kim th$c hin thành công và hành ñng np s tin lãi ñó vào tài kh an thanh toán th$c hin b tht bi thì xem như khách hàng ñã mt ñi s tin lãi ca tài khon tit kim (khách hàng mt tin). • Trư1ng h)p 2: nu hành ñng rút s tin lãi trong tài kh an tit kim th$c hin tht bi và hành ñng np s tin lãi ñó vào tài khon thanh toán th$c hin thành công thì xem như khách hàng có thêm s tin lãi  c hai tài khon (ngân hàng mt tin). Nhn xét thy r)ng c hai trưng hp nêu trên ñu làm cho h thng vi phm tính toàn v!n d liu và có nh hưng ñn uy tín cht lưng ca ngân hàng. Nhưng nu nh vào khái nim ca giao tác, bn có th quy ñnh c hai hành ñng trên s" ñưc bao bên trong mt ñơn v giao tác nh)m nói r)ng chúng s" ñưc ghi nhn li khi c hai hành ñng con bên trong ñó th$c hin thành công, ngưc li nu trưng hp 1 ho#c trưng hp 2 mô t  ph*n trên có xy ra thì tt c các hành ñng bên trong giao tác s" b hy b (không ghi li các thay ñi d liu). Điu này s" làm cho h thng không vi phm tính toàn v!n d liu. 7.1.2. Cách tạo và ứng dụng 7.1.2.1. Giao tác tường minh: Đ bt ñ*u mt giao tác tưng minh, bn phi s dng câu BEGIN TRAN trong dòng lnh ñ*u tiên ca mt ñơn v x lý. Đ ch( ñnh cho Microsoft SQL Server kt thúc Bài ging: H qun tr cơ s d liu 2 – SQL Server Bc Cao Đ ng Công ngh Thông tin Ging viên: Th.S Nguy
n Trí Nhân 127 Khoa Công ngh Thông tin Đi hc Phm Văn Đng giao tác và ghi nhn li các hành ñng cp nht d liu thì bn phi s dng lnh COMMIT TRAN và ngưc li khi s dng lnh ROLLBACK TRAN dùng ñ ch( ñnh cho Microsoft SQL Server kt thúc giao tác mà không ghi nhn li các hành ñng cp nht d liu trong giao tác. 7.1.2.2. Lệnh chỉ định bắt đầu một giao tác: Như ph*n trên ñã trình bày lnh BEGIN TRAN dùng ñ s dng trong các giao tác tưng minh. M<i giao tác có th ñưc ghép lng các giao tác con bên trong ñó, bn có th ch( ñnh tên cho t%ng giao tác lng nhau nh)m th$c hin dA dàng vic kt thúc ca m<i giao tác. Bin h thng @@TRANCOUNT tr v cp ñ lng hin hành bên trong các giao tác. Cú pháp lnh ch( ñnh bt ñ*u mt giao tác ñưc mô t như bên dưi. Cú pháp: Trong ñó: • Tên giao tác: tên ca giao tác ñưc ch( ñnh rõ ràng, ch( nên s dng tên giao tác khi cp ñ lng nhau ca các giao tác nhiu hơn hai cp. Ví d : S dng lnh BEGIN TRAN ñ ch( ñnh bt ñ*u th$c hin giao tác: thêm vt tư mi vào bng VATTU, tuy nhiên khi kt thúc giao tác bn không lưu li vt tư này. SET ANSI_WARNINGS OFF GO SELECT COUNT(*) AS 'Tng vt tư trưc khi thêm' FROM VATTU BEGIN TRAN INSERT INTO VATTU (MAVTU, TENVTU, DVTINH, PHANTRAM) VALUES('BU01', 'BÀN I PHILIP', 'CÁI', 17) SELECT COUNT(*) AS 'Tng vt tư sau khi thêm trong gt' FROM VATTU ROLLBACK TRAN SELECT COUNT(*) AS 'Tng vt tư hin ti' FROM VATTU SET ANSI_WARNINGS ON Kt qu tr v: Bài ging: H qun tr cơ s d liu 2 – SQL Server Bc Cao Đ ng Công ngh Thông tin Ging viên: Th.S Nguy
n Trí Nhân 128 Khoa Công ngh Thông tin Đi hc Phm Văn Đng Nhn xét thy r)ng trong ví d này, trưc khi th$c hin giao tác, chúng ta có 11 vt tư, sau ñó trong giao tác thêm vào mt vt tư mi. Tuy nhiên cui cùng khi kt thúc giao tác chúng ta không ghi li hành ñng thêm vt tư b)ng lnh ROLLBACK TRAN, do ñó tng s vt tư v9n là 11 vt tư khi kt thúc giao tác. 7.1.2.3. Các lệnh chỉ định kết thúc một giao tác: Theo ví d trên chúng ta có th hiu ý nghĩa ca lnh ROLLBACK TRAN dùng ñ ch( ñnh kt thúc giao tác nhưng không ghi nhn li các hành ñng cp nht d liu bên trong giao tác. Ngoài ra chúng ta c th s dng lnh COMMIT TRAN dùng ñ ch( ñnh kt thúc giao tác nhưng ñng ý ghi nhn li các hành ñng cp nht d liu bên trong giao tác. Cú pháp ca c hai lnh này ñưc mô t như bên dưi. Cú pháp: Ho#c Trong ñó: • Tên giao tác: tên ca giao tác ñưc ñnh nghĩa trưc ñó trong câu lnh BEGIN TRAN. Ví d : To mt bng tm dùng ñ minh ha vic s dng các giao tác lng nhau. Kt thúc giao tác ngoài cùng b)ng lnh ROLLBACK TRAN và không ghi nhn li các hành ñng cp nht d liu ca các giao tác con trưc ñó. Điu này có nghĩa là d liu ca bng tm #TestTran là hoàn toàn trng. CREATE TABLE #TestTran( CotA INT PRIMARY KEY, cotB CHAR(3) ) Bài ging: H qun tr cơ s d liu 2 – SQL Server Bc Cao Đ ng Công ngh Thông tin Ging viên: Th.S Nguy
n Trí Nhân 129 Khoa Công ngh Thông tin Đi hc Phm Văn Đng BEGIN TRAN Cap1 --@@TRANCOUNT = 1 INSERT INTO #TestTran VALUES (1, 'aaa') BEGIN TRAN Cap2 --@@TRANCOUNT = 2 INSERT INTO #TestTran VALUES(2, 'bbb') BEGIN TRAN Cap3 --@@TRANCOUNT = 3 INSERT INTO #TestTran VALUES(3,'CCC') COMMIT TRAN Cap3 --@@TRANCOUNT = 2 GO COMMIT TRAN Cap2 --@@TRANCOUNT = 1 GO ROLLBACK TRAN Cap1 --@@TRANCOUNT = 0 GO SELECT *FROM #TestTran GO DROP TABLE #TestTran GO Nhn xét thy r)ng trong ví d này tên ca các giao tác ñưc s dng trong các lnh ROLLBACK TRAN ho#c COMMIT TRAN ch( ñ giúp cho chúng ta dA ñc và dA thy ñưc cp ñ hin hành ca các giao tác lng nhau, nó hoàn toàn không có mt mi liên h gì gia tên giao tác trong các lnh BEGIN TRAN trưc ñó. Vic s dng ñi tưng Procdure Store ñ cung cp các d liu, các tính toán trên các màn hình nhp liu, báo cáo bên trong ng dng s" làm cho tc ñ các x lý ti nhánh máy ch ñưc nhanh hơn trong các ng dng mô hình khách ch. 7.1.2.4. Phân vùng trong giao tác: Chúng ta có th ch( ñnh vic ñng ý ghi nhn ho#c không ghi nhn li các hành ñng cp nht d liu riêng l= bên trong mt giao tác b)ng cách phân chia thành nhiu vùng nh cho các câu lnh bên trong mt giao tác. B)ng cách này chúng ta chia nh các hành ñng bên trong giao tác ra thành nhiu ph*n, tương ng t%ng ph*n nh chúng ta có th dA dàng ch ñng ñng ý ghi nhn ho#c không ghi nhn li vic cp nht d liu. Cú pháp ca lnh SAVE TRANSACTION cho phép chúng ta có th làm ñưc nhng ñiu như ñã mô t  trên. Cú pháp: Trong ñó: • Tên vùng: dùng ñ ch( ñnh vùng cha các lnh cp nht d liu và tên vùng nên duy nht trong mt giao tác. • Các l,nh: các lnh ñưc phân chia theo vùng bên trong giao tác. Bài ging: H qun tr cơ s d liu 2 – SQL Server Bc Cao Đ ng Công ngh Thông tin Ging viên: Th.S Nguy
n Trí Nhân 130 Khoa Công ngh Thông tin Đi hc Phm Văn Đng Ví d : Như ví d trên, tuy nhiên chúng ta mun phân chia lnh thêm mi m9u tin th nht và th hai trong vùng th nht, lnh thêm mi m9u tin th ba trong mt vùng th hai trong cùng mt giao tác. Kt thúc giao tác th$c hin ghi nhn li các lnh trong vùng th nht nhưng không ghi nhn li các lnh trong vùng th hai (ch( có m9u tin th nht và th hai ñưc ghi li). CREATE TABLE #TestTran( CotA INT PRIMARY KEY, CotB CHAR(3) ) BEGIN TRAN --Vùng th 1 SAVE TRAN Dong_1_2 INSERT INTO #TestTran VALUES(1,'AAA') INSERT INTO #TestTran VALUES(2, 'BBB') --Vùng th 2 SAVE TRAN Dong_3 INSERT INTO #TestTran VALUES (3, 'CCC') ROLLBACK TRAN Dong_1_2 GO SELECT *FROM #TestTran GO DROP TABLE #TestTran GO 7.1.2.5. Kiểm lỗi bên trong giao tác: Thông thưng khi làm vic bên trong giao tác, chúng ta s" không bao gi ch( ñnh rõ ràng vic kt thúc mt giao tác b)ng các lnh c th COMMIT TRAN ho#c ROLLBACK TRAN mà thay vào ñó chúng ta s" kim tra theo mt ñiu kin quy ñnh trưc. Nu ñiu kin này b sai thì bt buc chúng ta s" không ghi nhn các hành ñng cp nht d liu trong giao tác, ngưc li s" ñng ý ghi nhn các hành ñng ñó. Đ làm ñưc ñiu này, thông thưng chúng ta s dng giá tr ca bin h thng @@ERROR trong vic kim tra ñ bit kt qu ca câu lnh th$c hin g*n nht là thành công hay tht bi. Giá tr ca bin h thng @@ERROR tr v b)ng không khi câu lnh g*n nht th$c hin thành công, ngưc li thì tr v giá tr khác không khi câu lnh g*n nht th$c hin có l<i. Ví d : Th$c hin công vic cp phát s chng t% t$ ñng cho các bng DONDH, PNHAP, PXUAT ñm bo r)ng các s này không b trùng lp khi cùng lúc có nhiu ngưi s dng cùng lp các chng t% liên quan. Th$c hin t%ng bưc như sau: Bài ging: H qun tr cơ s d liu 2 – SQL Server Bc Cao Đ ng Công ngh Thông tin Ging viên: Th.S Nguy
n Trí Nhân 131 Khoa Công ngh Thông tin Đi hc Phm Văn Đng Đ*u tiên chúng ta xây d$ng bng CAP_SOCTU dùng lưu tr s chng t% ñưc cp k tip cho các bng, gm có các ct: tên bng (tenbang), s chng t% (soctu), ký t$ ñ*u (kytu). Trong ñó ct tên bng tham gia làm khóa chính. CREATE TABLE CAP_SOCTU ( TENBANG CHAR(20), SOCTU INT, KYTU CHAR(1), PRIMARY KEY (TENBANG) ) K tip ln lưt thêm các dòng d liu vào bng CAP_SOCTU: INSERT INTO CAP_SOCTU VALUES ('DONDH',100,'D') INSERT INTO CAP_SOCTU VALUES ('PNHAP', 100, 'N') INSERT INTO CAP_SOCTU VALUES ('PXUAT', 100, 'X') Sau cùng, chúng ta xây d$ng th tc cp s chng t% t$ ñng ñm bo không trùng lp. Có s dng vic kim tra l<i khi th$c hin các lnh trong giao tác. CREATE PROCEDURE SPUD_CAP_SOCTU_KE @sTenbang char(20), @sSoctuke char(20) output as Declare @nError Int, @nRowCount int, @nSoctuke int, @sChuoitam char(4), @sKytu char(1) Begin Tran -- Tăng s chng t k tip Update Cap_soctu Set Soctu = Soctu +1 Where TenBang = @sTenbang -- Kim tra vic tăng có thành công hay không? Select @nError = @@Error, @nRowcount = @@Rowcount Bài ging: H qun tr cơ s d liu 2 – SQL Server Bc Cao Đ ng Công ngh Thông tin Ging viên: Th.S Nguy
n Trí Nhân 132 Khoa Công ngh Thông tin Đi hc Phm Văn Đng --Nu có li hoc không cp ñưc m u tin nào If (@nError 0 or @nRowCount 1) Begin RollBack Tran Return -999 End -- L y s chng t mà chúng ta ñã tăng thành công Select @nSoctuke = Soctu, @sKytu = Kytu From Cap_soctu Where Tenbang= @sTenBang --Kim tra vic l y d liu có thành công không? Select @nError = @@Error, @nRowCount = @@RowCount --Nu có li hoc không l y ñưc m u tin nào if @nError 0 Or @nRowCount 1 Begin RollBack Tran Return -998 End --Tính s chng t khi không có li nào ht Set @sChuoiTam = LTrim(Str(@nSoctuke)) set @sSoctuke = @sKytu + Replicate('0', 3 - len(@sChuoiTam))+@sChuoiTam Commit Tran Return 0 go G!i thc hin th t"c trên ñ có ñưc s chng t k tip cho bng PXUAT. DECLARE @sSoctu Char(4), @nGttv Int EXEC @nGttv = SPUD_CAP_SOCTU_KE 'PXUAT', @sSoctu output if @nGttv 0 Bài ging: H qun tr cơ s d liu 2 – SQL Server Bc Cao Đ ng Công ngh Thông tin Ging viên: Th.S Nguy
n Trí Nhân 133 Khoa Công ngh Thông tin Đi hc Phm Văn Đng Print N'Có li c p chng t, xem li...' else Print N'S chng t mi là: ' + @sSoctu Kt qu tr v: Tóm l i: vic s dng ñi tưng Procdure Store ñ cung cp các d liu, các tính toán trên các màn hình nhp liu, báo cáo bên trong ng dng s" làm cho tc ñ các x lý ti nhánh máy ch ñưc nhanh hơn trong các ng dng mô hình khách ch. 7.2. BẪY LỖI (TRIGGER) 7.2.1. Khái niệm Trigger là mt trưng hp ñ#c bit ca store procedure, nó s" có hiu l$c khi chúng ta thay ñi d liu trên mt bng d liu c th, ho#c các x lý làm thay ñi d liu ca các lnh: insert, update, delete. Trigger có th cha các lnh truy vn t% các bng khác ho#c bao gm nhng lnh SQL phc tp. Mt s thun li khi s dng trigger: Trigger chy mt cách t$ ñng: chúng ñưc kích hot ngay tc thì khi có s$ thay ñi d liu trên bng d liu. Trigger có th th$c hin cascade khi vic thi hành có nh hưng ñn nhng bng liên quan. Trigger có nhng hiu l$c ít b hn ch hơn so vi ràng buc giá tr nghĩa là có th ràng buc tham chiu ñn nhng ct ca nhng bng d liu khác. Khi trigger ñưc kích hot bi 1 lnh Transact-SQL insert ñ thêm mt b mi vào bng AAA thì b mi này ñưc lưu tm thi vào mt bng tm có tên là inserted có cùng cu trúc vi bng AAA. Khi kt thúc trigger này thì b d liu mi tht s$ lưu xung CSDL. Tương t$ ñi vi lnh delete, các b d liu b xóa s" chuyn tm vào bng tm deleted. 7.2.2. Cách tạo và ứng dụng 7.2.2.1. Lệnh tạo Trigger Create Trigger trigger_name on table_name For [insert, update, delete] As Begin {Khai báo các bin x lý} {Các lnh Transact-SQL} End 7.2.2.2. Lệnh xóa Trigger Drop Trigger trigger_Name Ví d;: To trigger cho thao tác xóa mt ñ*u sách trong bng Muon. CREATE TRIGGER tg_delMuon ON muon Bài ging: H qun tr cơ s d liu 2 – SQL Server Bc Cao Đ ng Công ngh Thông tin Ging viên: Th.S Nguy
n Trí Nhân 134 Khoa Công ngh Thông tin Đi hc Phm Văn Đng FOR delete AS Begin DECLARE @isbn int, @ma_cuonsach smallint SELECT @isbn = isbn, @ma_cuonsach = ma_cuonsach FROM deleted UPDATE cuonsach SET tinhtrang = yes WHERE isbn = @isbn AND ma_cuonsach = @ma_cuonsach End 7.3. KIỂU CON TRỎ (SQL CURSOR) 7.3.1. Khái niệm Nu gii thích mt cách ngn gn thì cursor tương t$ như recordset hay dataset trong các ngôn ng lp trình. Nghĩa là chúng ta trích lc mt s d liu vào b nh, sau ñó có th l*n lưt làm vic vi t%ng bn ghi b)ng cách di chuyn l*n lưt qua t%ng bn ghi (Move Next...). Có 3 loi cursors là Transact-SQL Cursors, API Cursors và Client Cursors. Trong ñó Transact-SQL và API thuc loi Server Cursors nghĩa là cursors ñưc load lên và làm vic bên phía server. Trong khuôn kh bài hc này ta ch( nghiên cu Transact-SQL cursors. 7.3.2. Cách tạo và ứng dụng Transact-SQL cursors ñưc to ra trên server b)ng các câu lnh Transact-SQL và ch yu ñưc dùng trong stored procedure và trigger. Trưc ht hãy xem qua mt ví d v cursor: Vit th tc s dng Cursor ñ in ra l*n lưt danh sách khách hàng, nhà cung cp trong danh mc ñi tưng (th tc ñ#t tên là usp_DmDtPrint). CREATE PROCEDURE [dbo].[usp_DmDtPrint] AS BEGIN SET NOCOUNT ON; DECLARE @_Ma_Dt NVARCHAR(40), @_Ten_Dt NVARCHAR(20)s DECLARE DmDtCursor CURSOR FOR SELECT Ma_Dt, Ten_Dt FROM DmDt OPEN DmDtCursor FETCH NEXT FROM DmDtCursor INTO @_Ma_Dt, @_Ten_Dt WHILE @@FETCH_STATUS = 0 BEGIN Bài ging: H qun tr cơ s d liu 2 – SQL Server Bc Cao Đ ng Công ngh Thông tin Ging viên: Th.S Nguy
n Trí Nhân 135 Khoa Công ngh Thông tin Đi hc Phm Văn Đng PRINT N'Đi tưng: ' + RTRIM(@_Ma_Dt) + ' - ' + LTRIM(@_Ten_Dt) FETCH NEXT FROM DmDtCursor INTO @_Ma_Dt, @_Ten_Dt END CLOSE DmDtCursor DEALLOCATE DmDtCursor END Trong ví d  trên ta s" trích chn Ma_Dt và Ten_Dt t% bng DmDt ca Database QLHTK và np vào DmDtCursor, sau ñó l*n lưt in tên ca các ñi tưng ra màn hình. Ð làm vic vi mt cursor ta c*n theo các bưc sau: - Dùng câu lnh DECLARE CURSOR ñ khai báo mt cursor. Khi khai báo ta cũng phi cho bit câu lnh SELECT s" ñưc th$c hin ñ ly d liu. DECLARE DmDtCursor CURSOR FOR SELECT Ma_Dt, Ten_Dt FROM DmDt - Dùng câu lnh OPEN ñ ñưa d liu lên b nh o (memory). Ðây chính là lúc th$c hin câu lnh SELECT vn ñưc khai báo  trên. OPEN DmDtCursor - Dùng câu lnh FETCH ñ ly t%ng hàng data t% record set. C th là ta phi gi câu lnh FETCH nhiu l*n. FETCH tương t$ như lnh Move trong ADO recordset  ch< nó có th di chuyn ti lui b)ng câu lnh FETCH FIRST, FETCH NEXT, FETCH PRIOR, FETCH LAST, FETCH ABSOLUTE n, FETCH RELATIVE n nhưng khác  ch< là nó ly data b vào trong variable (FETCH...FROM...INTO variable_name). Thông thưng ta FETCH data trưc sau ñó loop cho ti record cui ca Cursor b)ng vòng l#p WHILE b)ng cách kim tra global variable @@FETCH_STATUS (=0 nghĩa là thành công). FETCH NEXT FROM DmDtCursor INTO @_Ma_Dt, @_Ten_Dt - Khi ta di chuyn ti t%ng bn ghi ta có th UPDATE hay DELETE tùy theo nhu c*u (trong thí d này ch( dùng lnh PRINT) PRINT N'Đi tưng: ' + RTRIM(@_Ma_Dt) + ' - ' + LTRIM(@_Ten_Dt) - Dùng câu lnh CLOSE ñ ñóng cursor. Mt s tài nguyên (memory resource) s" ñưc gii phóng nhưng cursor v9n còn ñưc khai báo và có th OPEN tr li. CLOSE DmDtCursor - Dùng câu lnh DEALLOCATE ñ phóng thích hoàn toàn các tài nguyên dành cho cursor (k c tên ca cursor). DEALLOCATE DmDtCursor Bài ging: H qun tr cơ s d liu 2 – SQL Server Bc Cao Đ ng Công ngh Thông tin Ging viên: Th.S Nguy
n Trí Nhân 136 Khoa Công ngh Thông tin Đi hc Phm Văn Đng Lưu ý: Trong ví d  trên trưc khi dùng Cursor chúng ta phi khai báo trưc hai bin @_Ma_Dt và @_Ten_Dt ñ cha các giá tr ly ñưc t% cursor. 7.4. BÀI TẬP THỰC HÀNH 1. Hãy thêm trưng ModifiedAt vào tt c các bng trong Database QLHTK sau ñó vit Trigger ñ lưu li thi gian cp nht vào trưng này. 2. Hãy vit Trigger ñi mã cho tt c các danh mc. Bài ging: H qun tr cơ s d liu 2 – SQL Server Bc Cao Đ ng Công ngh Thông tin Ging viên: Th.S Nguy
n Trí Nhân 137 Khoa Công ngh Thông tin Đi hc Phm Văn Đng Chương 8. QUẢN LÝ BẢO MẬT VÀ NGƯỜI DÙNG TRONG SQL SERVER 8.1. KHÁI NIỆM M<i CSDL có 1 hay nhiu users ñưc ch( ñnh quyn truy xut d liu. Ngưi qun tr có th cp quyn truy xut CSDL b)ng cách to mt tài khon ñăng nhp (login) SQL Server cho User, thêm User vào CSDL và gán quyn cho User trên CSDL ñó. Bao gm các loi quyn: • Quyn truy cp vào SQL Server. • Quyn truy xut CSDL. • Quyn th$c hin trên các ñi tưng ca CSDL. • Quyn x lý d liu. 8.2. TẠO VÀ QUẢN LÝ NGƯỜI DÙNG ĐĂNG NHẬP 8.2.1. Sử dụng Enterprise Manager 8.2.1.1. Tạo tài khoản đăng nhập (Login Acount): (1) M mc Security, click phi mc Login và chn New Login (2) Trên trang General, nhp tên ñăng nhp, ch ñ xác nhn, CSDL m#c ñnh. (Hình 8.1) (Hình 8.2) Chú ý: Đăng nhp vi SQL Server thưng dùng cho vic kt ni quay s và mng peer-to- peer. (3) Trang Server Roles: Chn vai trò qun tr mc Server cho tài khon ñăng nhp Bài ging: H qun tr cơ s d liu 2 – SQL Server Bc Cao Đ ng Công ngh Thông tin Ging viên: Th.S Nguy
n Trí Nhân 138 Khoa Công ngh Thông tin Đi hc Phm Văn Đng System Administrators Security Administrators Server Administrators Setup Administrators Database Creator Disk Administrators Process Administrators Bulk Administrators Đ#c quyn cao nht; cho phép th$c hin mi tác v trên SQL Qun lý các server logins. Cho phép bn ñnh cu hình nhng cài ñ#t server-wide. Cho phép thêm và xóa các linked servers, và truy xut vài SP To và hiu ch(nh databases. Qun lý các files trên ñĩa. Qun lý tin trình ñang chy trong mt th hin ca SQL Server. Th$c hin phát biu BULK INSERT. Chú ý: Bt kỳ Users ca Windows NT thuc nhóm BUILTIN\Administrators ñu có vai trò sysadmin. (4) Trang Database Access: Chn CSDL ñưc phép truy xut và vai trò ca nó trong t%ng CSDL ñưc chn. Fixed Database Role Description Public Vai trò chung cho tt c ngưi dùng. db_owner Quyn cao nht trong database. db_accessadmin Điu khin truy xut, cài ñ#t ho#c xóa user accounts. db_datareader Đc tt c d liu trên database. db_datawriter Thêm, sa, xóa d liu trên các tables ngưi dùng trong database. db_addladmin Thêm, sa, xóa các ñi tưng objects (runs all DDLs). db_securityadmin Qun lý các roles, các thành viên ca role, giy phép trong database. db_backupoperator Cho phép back up database. db_denydatareader T% chi quyn truy vy d liu trong database. db_denydatawriter T% chi quyn thay ñi d liu trong database. Sau khi to login, nó t$ ñng nhp vào tp Users ca m<i database ñưc chn, vi tên User trùng vi tên Login. Bn có th thay ñi thu hi vai trò ca nó trên t%ng CSDL b)ng cách sa ñi thuc tính ca Login, ho#c chuyn ñn tp Users ca database và thay ñi thuc tính ho#c xoá user nào mà bn không mun cho truy xut data ca bn. Các Login ñưc lưu trong table SysLogins ca CSDL Master: If Exists(Select 1 From Master..SysLogins Where Name = 'Login') Các User trong mt CSDL ñưc lưu trong table SysUsers ca CSDL ñó If Exists(Select 1 From SysUsers Where Name = 'User01') Bài ging: H qun tr cơ s d liu 2 – SQL Server Bc Cao Đ ng Công ngh Thông tin Ging viên: Th.S Nguy
n Trí Nhân 139 Khoa Công ngh Thông tin Đi hc Phm Văn Đng 8.2.1.2. Thay đổi thuộc tính cho Login: Bm ñúp vào tên Login ho#c click phi và chn mc Properties 8.2.2. Sử dụng Query Analyzer: 8.2.2.1. Thêm Tài khoản với chế độ xác nhận SQL Server: SP_ADDLOGIN [ @login = ] [, @password = ] [, @defdb = ] [, @deflanguage = ] [, @sid =] [, @encriptopt = 'skip_encription' ] o @defdb: tên CSDL m#c ñnh ñưc m khi ñăng nhp. Nu không ch( ñnh m#c ñnh là CSDL Master. o @deflanguage: Ngôn ng m#c ñnh o @sid: varbinary(16): Mã nhn dng ca h thng. Nu không ch( ñnh, h thng t$ to mt mã s mi. o @encriptopt varchar(20): M#c ñnh Password s" ñưc mã hóa khi lưu trong các table h thng. Ngoi tr% bn gán giá tr skip_encryption cho tham s này. o Th tc tr v giá tr 0 nu thành công, ngưc li tr v giá tr 1. Ví d;: To tài khon tên 'myname', mt kh'u 'mypwd' vào CSDL 'QLDeTai' Sp_AddLogin 'user01', '01', 'QLDeTai' Kt qu tr v: New loigin created 8.2.2.2. Cấp quyền kết nối cho User hay nhóm User của Windows kết nối đến SQL Server: SP_GRANTLOGIN [@loginame =] 'login' 'Login': |\ Ví d;: Thêm tài khon cho user Windows NT [Server4\User01] kt ni ñn SQL Server. EXEC sp_grantlogin ' Server4\User01' Hay EXEC sp_grantlogin [Server4\User01] Chú ý: Ch ñưc thc hi n bi nhng thành viên có vai trò sysadmin và securityadmin. 8.3. CẤP PHÁT QUYỀN CHO NGƯỜI DÙNG 8.3.1. Sử dụng Enterprise Manager 8.3.1.1. Cấp quyền thực thi trên mỗi CSDL: Chn database, trong mc Users bm ñúp vào tên User c*n hiu ch(nh (Login-ID). Click nút Permission ñ ch( ñnh quyn truy cp d liu trên t%ng Table, View. Quyn Bài ging: H qun tr cơ s d liu 2 – SQL Server Bc Cao Đ ng Công ngh Thông tin Ging viên: Th.S Nguy
n Trí Nhân 140 Khoa Công ngh Thông tin Đi hc Phm Văn Đng kim tra RB tham chiu (DRI - Declarative Referential Integrity). Quyn th$c hin các Procdure Store tr. Chú thích: Quyn kim tra RB tham chiu ñưc s dng khi Table A ñưc cp quyn Update hoc Insert. Table A có RB FOREIGN KEY vi table B, mà table B không ñưc cp quyn SELECT. 8.3.1.2. Cấp quyển tạo đối tượng trên CSDL: o Click phi vào tên CSDL, chn Properties o Trong HT Properties, chn trang Permissions o Đánh du chn các phát biu ñưc c*n cp quyn th$c hin cho các User. Xóa User trên m>t CSDL: Click phi vào tên user và chn Delete. T5o User v.i Login ñã có: Click phi vào mc Users và chn New Database User... Chú ý: B n có th thay ñi tên User bng cách xóa và t o l i vi login cũ. (Hình 10.3) 8.3.1.3. Xóa Tài Khoản Đăng Nhập: Click phi vào tên Login và chn Delete Vi cách này s" xóa b các user trong các CSDL ñã chn cho tài khon này. 8.3.2. Sử dụng Query Analyzer 8.3.2.1. Cấp quyền truy xuất CSDL hiện hành cho Login: Sp_GrantDBAccess [ @loginname = ] 'Tênñăngnhp' [ [, @name_in_db = ] 'TênUser' ] Ví d;: Use QLDeTai Go sp_GrantDBAccess 'user01', 'Anh' 8.3.2.2. Cấp quyền thực thi trên CSDL: Bao gm các quyn: Select, Insert, Update, Delete, Reference, Excecute. GRANT ALL | [,] Bài ging: H qun tr cơ s d liu 2 – SQL Server Bc Cao Đ ng Công ngh Thông tin Ging viên: Th.S Nguy
n Trí Nhân 141 Khoa Công ngh Thông tin Đi hc Phm Văn Đng ON [(,..)] | TO [,] [WITH GRANT OPTION] [AS ] o All: Cp tt c các quyn th$c thi Select, Insert, Update, Delete hay Reference trên table hay view; quyn Excecute cho SP. o TO : Khi cp quyn cho nhóm hay user ca Windows NT, phi ch( ñnh: |\. Đ cp quyn cho nhóm cc b Windows NT built-in, dùng t% khóa BUILTIN thay th tên domain ho#c computer name. Quyn th$c thi ñã cp cho role Public ñưc áp dng cho tt c users trong CSDL. Quyn th$c thi ñã cp cho user Guest ñưc s dng cho tt c Users không ñưc phân quyn truy xut trên CSDL. Ví d;: USE QLDeTai Grant All On DeTai To User01 GO GRANT SELECT ON DeTai TO public GO GRANT INSERT, UPDATE, DELETE ON DeTai TO Mary, [ Corporate\BobJ] GO o WITH GRANT OPTION: Cho phép Login cp quyn ñã ch( ñnh trên ñi tưng cho Login khác. o AS {group | role}: Đưc dùng khi quyn th$c thi trên mt ñi tưng ñã cp cho nhóm ho#c role, và mt User ca nhóm ho#c Role mun cp quyn th$c thi cho User khác không là thành viên ca nhóm ho#c Role. Ví d;: table NhatKy ñưc to bi user Lac. Lac cp quyn SELECT table NhatKy cho Role BanBe vi mnh ñ WITH GRANT OPTION ñ các user thành viên ca Role BanBe có th nhưng quyn này cho các user khác không thuc Role BanBe. User Hong, là thành viên ca BanBe, mun nhưng quyn SELECT table NhatKy cho user Khoa, không là thành viên ca role BanBe. /* User Lac */ GRANT SELECT ON NhatKy TO BanBe WITH GRANT OPTION /* User Hong */ GRANT SELECT ON NhatKy TO Khoa AS BanBe Bài ging: H qun tr cơ s d liu 2 – SQL Server Bc Cao Đ ng Công ngh Thông tin Ging viên: Th.S Nguy
n Trí Nhân 142 Khoa Công ngh Thông tin Đi hc Phm Văn Đng 8.3.2.3. Cấp Quyền tạo đối tượng trong CSDL: GRANT ALL | [,] TO [,] Bao gm các lnh: CREATE DATABASE; CREATE DEFAULT; CREATE PROCEDURE; CREATE RULE; CREATE TABLE; CREATE VIEW; BACKUP DATABASE; BACKUP LOG Ví d;: GRANT CREATE DATABASE, CREATE TABLE TO Mary, John, [Corporate\BobJ] 8.3.2.4. Từ Chối quyền thực thi trên CSDL: DENY ALL | [,] [ (column [,...n ]) ] ON { table | view } | ON table | view [ (column [,...n ]) ] | ON stored_procedure TO [,] [Cascade] Ví d;: USE QLDeTai GO GRANT SELECT ON DeTai TO public GO DENY SELECT, INSERT, UPDATE, DELETE ON DeTai TO Mary, John, Tom 8.4. THU HỒI QUYỀN ĐÃ CẤP PHÁT CHO NGƯỜI DÙNG 8.4.1. Xóa quyền truy xuất CSDL hiện hành: Sp_RevokeDBAccess [ @name_in_db = ] 'TênUser' Ví d;: Sp_RevokeDBAccess 'Anh' 8.4.2. Xóa bỏ quyền thực thi đã cấp hoặc từ chối trên CSDL: REVOKE [ GRANT OPTION FOR ] { ALL | permission [,...n ] } [ (column [,...n ]) ] ON { table | view } | ON { table | view } [ (column [,...n ]) ] | ON { stored_procedure | extended_procedure } | ON { user_defined_function } TO | FROM security_account [,...n ] [ CASCADE ] [ AS { group | role } ] Ví d;: Xóa b quyn Select ñã t% chi cho User Mary trên table DeTai. REVOKE SELECT ON DeTai TO Mary Bài ging: H qun tr cơ s d liu 2 – SQL Server Bc Cao Đ ng Công ngh Thông tin Ging viên: Th.S Nguy
n Trí Nhân 143 Khoa Công ngh Thông tin Đi hc Phm Văn Đng 8.4.3. Xóa bỏ quyền tạo đối tượng đã cấp hoặc từ chối trên CSDL: REVOKE { ALL | statement [,...n ] } FROM security_account [,...n ] Ví d;: REVOKE CREATE TABLE FROM Joe, [Corporate\BobJ] Bài ging: H qun tr cơ s d liu 2 – SQL Server Bc Cao Đ ng Công ngh Thông tin Ging viên: Th.S Nguy
n Trí Nhân 144 Khoa Công ngh Thông tin Đi hc Phm Văn Đng TÀI LIỆU THAM KHẢO [1]. Marcilina S. Garcia, Jamie Reding, Edward Whalen, Steve Adrien DeLuca, SQL Server 2000 Administrator’s Companion, Microsoft Press, 2000. [2]. Microsoft SQL Server 2000 System Administration Training kit [3]. Tr*n Nguyên Phong, Bài gi
ng SQL Server , Đi hc khoa hc, Đi hc Hu. TRƯƠNG Đ/I HEC PH/M VĂN ĐGNG KHOA CÔNG NGH THÔNG TIN BÀI GI
NG: H QU N TR CƠ S D LIU 2 SQL SERVER Ging viên: Th.S Nguy
n Trí Nhân Qung Ngãi, 12/2013