Toán - Tin Học Hệ Quản Trị phần 3

Chia sẻ: Dwqdwqdqwd Dwqdfewf | Ngày: | Loại File: PDF | Số trang:12

Thêm vào BST

Báo xấu

77
lượt xem 7
download

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

Tham khảo tài liệu 'toán - tin học hệ quản trị phần 3', công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả

Chủ đề:

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

Lưu

Nội dung Text: Toán - Tin Học Hệ Quản Trị phần 3

23 Bài gi ng tóm t t H qu n tr cơ s d li u óng gói ch các thao tác cho phép trên CSDL vào các SP và quy nh truy xu t − d li u ph i thông qua SP. Ngoài ra còn có th phân quy n trên SP H tr t t hơn cho vi c m b o an toàn (security) cho CSDL. − SP giúp cho vi c k t xu t báo bi u b ng Crystal Report tr nên ơn gi n và hi u qu hơn r t nhi u so v i vi c k t xu t d li u tr c ti p t các b ng và khung nhìn. 2. Khai báo và s d ng th t c Cú pháp khai báo: Create {proc | procedure} procedure_name {Parameter_name DataType [=default] [output] }[,…n] As { kh i l nh } Go Lưu ý: Tên tham s t theo qui t c như tên bi n c c b . Giá tr tr v c a SP dùng m t (hay m t s ) tham s output. Ví d : − Xây d ng SP cho bi t danh sách sinh viên c a m t l p có mã cho trư c Create proc DS_Lop @MaLop varchar(10) As Select SV.MaSV, SV.HoVaTen, SV.NgaySinh From SinhVien SV where SV.Lop = @MaLop Go − Xây d ng SP tính toán giá tr cho ơn hàng có mã cho trư c v i quan h DonHang như sau: DonHang(Ma, SoLuong, DonGia, ThueSuat, ChietKhau, ThanhTien) Create proc TongTien @MaDH varchar(10) As Declare @ThanhTien float Declare @TienThue float Declare @TienChietKhau float Declare @DonGia float,@SoLuong int Set @SoLuong = (select SoLuong from DonHang where Ma = @MaDH)
24 Bài gi ng tóm t t H qu n tr cơ s d li u Set @DonGia = (select DonGia from DonHang where Ma = @MaDH) Set @TienThue = (select ThueSuat from DonHang where Ma = @MaDH) Set @TienChietKhau = (select ChietKhau from DonHang where Ma = @MaDH) Set @ThanhTien = @DonGia*@SoLuong Set @TienThue = @ThanhTien*@TienThue/100 Set @ThanhTien = @ThanhTien + @TienThue Set @TienChietKhau = @ThanhTien*@TienChietKhau/100 Set @ThanhTien = @ThanhTien - @TienChietKhau Update DonHang set ThanhTien = @ThanhTien where Ma = @MaDH Go − Vi t th t c thêm m t ăng ký c a sinh viên vào m t h c ph n (t ng quát ví d trong ph n If …else) Create procedure usp_ThemDangKy @MaSV char(5), @MaHP char(5), @SiSo int = 0 output As select @SiSo = SiSo from HocPhan where MaHP= @MaHP if @SiSo < 50 Begin insert into DANG_KY(MaSV, MaHP) values(@MaSV, @MaHP) set @SiSo = @SiSo+1 return 1 End return 0 Go - Xâ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) trong ó:
25 Bài gi ng tóm t t H qu n tr cơ s d li u − i m thi ch tính l n thi sau cùng. − X 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 xu t d ng tham s output, không ghi xu ng CSDL. Gi i Create proc XepLoaiSV @MaSV varchar(10), @DTB float out put, @XL nvarchar(20) out put As Set @DTB = (Select avg(Diem) from KetQua Kq Where MaSV = @MaSV and not exists (select * from KetQua Kq1 where Kq1.MaSV = @MaSV and Kq1.MaMH=Kq.MaMH and Kq1.LanThi > Kq.LanThi)) If @DTB >= 9 Set @XL = N’Xu t s c’ Else if @DTB >= 8 Set @XL = N’Gi i’ Else if @DTB >= 7 Set @XL = N’Khá’ Else if @DTB >= 5 Set @XL = N’Trung bình’ Else Set @XL = N’Y u’ Go Cú pháp g i th c hi n th t c: EXEC| EXECUTE { [ @return_status = ] procedure_name { [ @parameter _name = ] value [ OUTPUT ] } [ ,...n ] Lưu ý: o Khi g i th c hi n SP, dùng t khóa Exec và c n truy n tham s v i ki u d li u phù h p và th t chính xác như khai báo trong nh nghĩa SP. o Có th truy n giá tr cho tham s u vào (input) là m t h ng ho c m t bi n ã gán giá tr , không truy n ư c m t bi u th c. nh n ư c giá tr k t qu (thông qua tham s u ra), c n truy n vào m t bi n o và có t khóa output. Ví d : o G i th t c usp_ThemDangKy:
26 Bài gi ng tóm t t H qu n tr cơ s d li u Exec usp_ThemDangKy ‘001’, ’HP01’ ho c Exec usp_ThemDangKy @MaHP = ‘HP01’, @MaSV = ‘001’ o G i th t c usp_ThemDangKy có nh n k t qu u ra: Declare @SiSo int Exec usp_ThemDangKy ‘001’,’HP01’, @SiSo output Print @SiSo o G i th t c usp_ThemDangKy có nh n k t qu u ra và k t qu tr v t th t c: Declare @SiSo int, @KetQua int Exec @KetQua = usp_ThemDangKy ‘001’,’HP01’, @SiSo output o G i th c hi n th t c x p lo i sinh viên: Declare@MaSinhVien varchar(10) Declare@DiemTB varchar(10) Declare@XepLoai varchar(10) Set@MaSinhVien = ‘0712345’ Exec XepLoaiSV @MaSinhVien,@DiemTB out put,@XepLoai out put Exec XepLoaiSV ‘0713478’, @DiemTB out put,@XepLoai out put S a th t c Thay t khóa Create trong l nh t o th t c b ng t khóa Alter. Xóa th t c Drop {procedure|proc} procedure_name Ví d : Drop procedure usp_ThemDangKy 3. Stored procedure h th ng − Là nh ng th t c do SQL Server cung c p s n th c hi n các công vi c: qu n lý CSDL, qu n lý ngư i dùng, c u hình CSDL,… − Các th t c này có tên b t u b ng “sp_” Khi xây d ng th t c, tránh t tên th t c b t u v i “sp_”. IV. Ki u d li u cursor 1. 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).
27 Bài gi ng tóm t t H qu n tr cơ s d li u - 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 cursor. 2. Khai báo và s d ng Cursor Khai báo Cursor 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] Cursor For select_statement [ For {Read only| Update [of column_name [,…n] ] }] − Cú pháp T_SQL m r ng Declare cursor_name Cursor [ Local | Global ] [ Forward_only| Scroll] [ Static| Dynamic] [ Read_only] For select_statement [ For Update [ of column_name [,…n] ] ] Lưu ý: Tên cursor trong các cách khai báo không b t u b ng ký t “@”. Ý nghĩa các tham s tùy ch n trong khai báo: o 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 only. o Dynamic: 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. o Local: 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. o Global: 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 minh. o Forward_only: cursor ch có th duy t m t chi u t u n cu i. o Scroll: có th duy t lên xu ng cursor tùy ý (duy t theo a chi u).
28 Bài gi ng tóm t t H qu n tr cơ s d li u o Read only: 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…” ). Mc 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 - Dynamic Duy t cursor Dùng l nh Fetch duy t tu n t qua cursor theo cú pháp: Fetch [ [Next| Prior| First| Last| Absolute n| Relative n] From ] Tên_cursor [Into Tên_bi n [,…n] ] −M c nh: fetch next. i v i cursor d ng forward_only, ch có th fetch next. − − Bi n h th ng @@fetch_status cho bi t l nh fetch v a th c hi n có thành công hay không, giá tr c a bi n này cơ s bi t ã duy t n cu i cursor hay chưa. Quy trình s d ng Cursor − Khai báo cursor. − “M ” cursor b ng l nh Open Open tên_cursor − Khai báo các bi n t m ch a ph n t hi n hành ( ang ư c x lý) c a cursor: Các bi n t m ph i cùng ki u d li u v i các trư ng tương ng c a ph n t trong cursor. Có n trư ng trong ph n t c a cursor thì ph i có n bi n t m tương ng. − Fetch (next,…) cursor chuy n n v trí phù h p: Có th ưa các giá tr c a dòng hi n hành vào các bi n thông qua m nh into c a l nh fetch. N u không có m nh into, các giá tr c a dòng hi n hành s ư c hi n th ra c a s k t qu (result pane) sau l nh fetch. Có th s d ng v trí hi n t i như là i u ki n cho m nh where c a câu delete/ update (n u cursor không là read_only).
29 Bài gi ng tóm t t H qu n tr cơ s d li u − L p l i vi c duy t và s d ng cursor, có th s d ng bi n @@fetch_status bi t ã duy t qua h t cursor hay chưa. @@FETCH_STATUS = 0 : l y d li u thành công, @@FETCH_STATUS < 0 : không l y ư c d li u. óng cursor b ng l nh Close − Close Tên_cursor Lưu ý: Sau khi óng, v n có th m l i n u cursor chưa b h y. − H y cursor b ng l nh deallocate Deallocate Tên_cursor Ví d : xét hai L QH SINHVIEN (MaSV, HoTen, MaKhoa) KHOA(MaKhoa, TenKhoa) Duy t và c giá tr t cursor C p nh t l i giá tr MaSV = Vi t t t tên Khoa + MaSV hi n t i cho t t c sinh viên: declare cur_DSKhoa cursor for select MaKhoa, TenKhoa from Khoa open cur_DSKhoa declare @MaKhoa int, @TenKhoa varchar(30), @TenTat varchar(5) fetch next from cur_DSKhoa into @MaKhoa, @TenKhoa while @@fetch_status = 0 begin -- xác nh tên t t c a Khoa d a vào @TenKhoa… update SinhVien set MaSV = @TenTat+MaSV Where MaKhoa = @MaKhoa fetch next from cur_DSKhoa into @MaKhoa, @TenKhoa end Close cur_DSKhoa Deallocate cur_DSKhoa Dùng cursor xác nh dòng c p nh t
30 Bài gi ng tóm t t H qu n tr cơ s d li u declare cur_DSKhoa cursor scroll for select MaKhoa, TenKhoa from Khoa open cur_DSKhoa fetch absolute 2 from cur_DSKhoa if (@@fetch_status = 0) update Khoa set TenKhoa = ‘aaa’ where current of cur_DSKhoa Close cur_DSKhoa Deallocate cur_DSKhoa 3. Bi n cursor − Ta 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 i. − Bi n cursor có th ư c xem như là con tr cursor. − Bi 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ư ng. Ví d : Declare @cur_var cursor set @cur_var = my_cur -- my_cur là m t cursor ang t n t i ho c: Declare @cur_var cursor set @cur_var = cursor for select_statement K t h p cursor v i stored procedure Xâ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ùng X 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 :
31 Bài gi ng tóm t t H qu n tr cơ s d li u o 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 XepLoaiSVLop. D a vào i m trung bình c a sinh viên xác nh x p lo i. C p nh t i m và x p lo i vào b ng sinh viên. o M 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 p Cursor 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 c Create procedure XepLoaiSVLop @Lop nvarchar(10), @SoSVGioi int out As Declare @DTB float Declare @XepLoai nvarchar(20) Declare @MaSV nvarchar(10) Declare cur_SV cursor For (select MaSV from SinhVien where Lop=@Lop) Open cur_SV Fetch Next from cur_SV into @MaSV While @@FETCH_STATUS = 0 Begin Exec XepLoaiSV @MaSV, @DTB output, @XepLoai output Update SinhVien set DTB = @DTB, XepLoai=@XepLoai Where MaSV = @MaSV Fetch Next from cur_SV into @MaSV End Close cur_SV Deallocate cur_SV
32 Bài gi ng tóm t t H qu n tr cơ s d li u Set @SoSVGioi = (select count(*) from sinhvien where lop = @Lop and XepLoai = N’Gi i’) Go V. Hàm ngư i dùng (User Defined Functions) 1. Khái ni m hàm ngư i dùng • Gi ng stored procedure: − mã l nh có th tái s d ng. − Ch p nh n các tham s input. − Biên d ch m t l n và t ó có th g i khi c n. • Khác stored procedure: − Ch p nh n nhi u ki u giá tr tr v (ch m t giá tr tr v ). − Không ch p nh n tham s output. − Khác v cách g i th c hi n. • Có th xem hàm ngư i dùng thu c v 3 lo i tùy theo giá tr tr v c a nó: − Giá tr tr v là ki u d li u cơ s (int, varchar, float, datetime…). − Giá tr tr v là Table có ư c t m t câu truy v n. − Giá tr tr v là table mà d li u có ư c nh tích lũy d n sau m t chu i thao tác x lý và insert. 2. Khai báo và s d ng Khai báo hàm ngư i dùng Lo i 1: Giá tr tr v là ki u d li u cơ s Create function func_name ( {parameter_name DataType [= default ] } [,…n]) returns DataType As Begin … Return {value | variable | expression} End Ví d :
33 Bài gi ng tóm t t H qu n tr cơ s d li u Create function SoLonNhat (@a int,@b int,@c int) return int As Begin declare @max int set @max = @a if @b > max set @max = @b if @c > max set @max = @c return @max End Lo i 2: Giá tr tr v là m t b ng có ư c t m t câu truy v n Create function func_name ( {parameter_name DataType [= default ] } [,…n]) returns Table As Return [ ( ]select_statement [ ) ] Go Ví d : Vi t hàm in danh sách các m t hàng c a m t mã ơn hàng cho trư c Create function DanhSachMatHang ( @MaDonHang varchar(10) ) returns Table As Return (Select MH.TenHang,MH.DonGia From ChiTietDH CT, MatHang MH Where CT.MaDH = @MaDonHang and CT.MaMH = MH.MaMH) Go Lo i 3: Giá tr tr v là m t b ng mà d li u có ư c nh tích lũy d n sau m t chu i thao tác x lý và insert.
34 Bài gi ng tóm t t H qu n tr cơ s d li u Create function func_name ( {parameter_name DataType [= default ] } [,…n]) returns TempTab_name Table(Table_definition) As Begin … Return End Go Ví d : Create function DanhSachLop () returns @DS Table(@MaLop varchar(10),@SoSV int) As --các x lý insert d li u vào b ng DS return Go Lưu ý: Trong thân hàm không ư c s d ng các hàm h th ng b t nh (Built-in nondeterministic functions ), bao g m : − GETDATE − GETUTCDATE − NEWID − RAND − TEXTPTR − @@TOTAL_ERRORS, @@CPU_BUSY, @@TOTAL_READ, @@IDLE, @@TOTAL_WRITE, @@CONNECTIONS … S d ng hàm ngư i dùng Các hàm ngư i dùng ư c s d ng trong câu truy v n, trong bi u th c… phù h p ki u d li u tr v c a nó. Ví d : − Select dbo.SoLonNhat(3,5,7) − Select * from DanhSachLop() Lưu ý: − N u dùng giá tr m c nh c a tham s , ph i dùng t khóa default.