Giáo trình Hệ quản trị cơ sở dữ liệu MSSQL 2005 - Chương 4: T-SQL Programming

Chương 4T-SQL PROGRAMINGKHOA CÔNG NGHỆ THÔNG TINI. KHAI BÁO VÀ SỬ DỤNG BIẾN Có 2 loại biến: Cục bộ và toàn cụcBiến cục bộ: Định nghĩa: là biến chỉ sử dụng trong đoạn chương trình khai báo nó như Query Batch, stored procedure/ function , chứa giá trị thuộc một kiểu nhất định. Biến cục bộ được bắt đầu bằng 1 ký hiệu @ Khai báo: Declare , Ví dụ: Declare @MaSinhVien char(10) Declare @Gioitinh bit, @Ngaysinh SmallDateTime Gán giá trị cho biếnCú phápSet @tên_biến = {giá_trị | biến | biểu_thức | Cấu trúc Select }Ví dụ Set @MaLop = ‘TH2001’ Set @SoSV = (select count (*) from SinhVien) Set @MaLop = ‘TH’+Year(@NgayTuyenSinh) Chú ý: Kết quả lệnh Select chỉ cho một giá trị (không thể nhiều bộ giá trị)Gán giá trị của cột tại dòng hiện tại hoặc dòng chỉ định trong Table cho biến. Cú pháp: Select =, =, [Where ] Nếu kết quả của Select cho nhiều dòng thì sẽ gán giá trị của dòng có STT vật lý đầu tiên cho biến. Hiển thị kết quả của biến: Print Ví dụ: Hiển thị dòng ‘Xin chào’ họ tên của sinh viên đầu tiên trong bảng SINHVIEN Declare @ht nVarchar(30) Select @ht=HotenFrom SINHVIEN Print 'Xin chào'+ @ht 2. Biến toàn cục: Định nghĩa: là biến được sử dụng bất kỳ đâu trong hệ thống. Trong SQL biến toàn cục Là các biến hệ thống do SQL Server cung cấp SQL tự cập nhật giá trị cho các biến này, Người sử dụng không thể gán giá trị trực tiếp cho biến này Bản chất là 1 hàm (function) và bắt đầu bằng ký tự @@ Một số biến toàn cục trong SQLTên biếnÝ nghĩa@@ERRORMã số lỗi của câu lệnh T-SQL cuối cùng bị lỗi@@FETCH_STATUSTrạng thái truy nhập con trỏ: 0 nếu trạng thái truy nhật cuối cùng thành công-1 nếu có lỗi@@IDENTITYGiá trị xác định (identity) cuối cùng được thêm vào@@ROWCOUNTSố lượng dòng của kết quả câu lệnh SQL gần đây nhất.@@SERVERNAMETên của Server địa phương@@TRANSCOUNTSố lượng những giao dịch đang được mở@@VERSIONThông tin về phiên bản SQL Server đang dùng@@CURSOR_ROWS Số lượng các dòng dữ liệu của con trỏ mới được mở gần đây nhấtII. CẤU TRÚC LỆNH1. Cấu trúc IF Cú pháp: If Lệnh| Khối_lệnh [Else Lệnh| Khối_lệnh] Khối lệnh là một hoặc nhiều lệnh nằm trong cặp từ khóa beginend Giải thích cấu trúc Ví dụ: Viết lệnh để thêm dữ liệu mới (001,Hồ Thị ,Lan, CT11) vào bảng SINHVIEN. Qui định rằng mỗi lớp chỉ được tối đa 50 sinh viên. Declare @SiSo int Select @SiSo = Count(MaSV) From SINHVIEN Where MaLop= ‘CT11’ If @SiSo then kết_quả 1When then kết_quả 2[...n] [ Else kết_quả_khác] End 2. Cấu trúc WHILE Cú pháp While Lệnh| Khối lệnh [Break] [Continue] Có thể thêm Break và Continue trong khối lệnh của while Break: thoát khỏi vòng While hiện hành Continue : trở lại đầu vòng While, bỏ qua các lệnh sau đó Giải thích:Ví dụ: Viết đoạn chương trình tách tên của sinh viên có mã ‘0001’ trong bảng SINHVIEN Declare @ht varchar(30),@ten varchar(10), @L int, @i int,@j int,@kt varchar(10)set @ht=(select hoten from SINHVIEN Where MaSV='0001')Set @L=LEN(@ht)Set @i=1While @i0 thì KHÔNG thành công con trỏ đang ở vị trí vượt qua mẫu tin cuối cùng của bảng kết quảDeclare cur_Masv cursor For Select MaSV, Malop From SINHVIEN Open cur_Masv declare @Masv varchar(5), @Malop varchar(5)Fetch Next From cur_Masv into @MaSV,@Malop While @@fetch_status = 0 Begin Fetch Next From cur_Masv into @MaSV,@MalopEnd Close cur_Masv Deallocate cur_Masv Update SINHVIEN Set MaSV = @Malop+@MaSV Where MaSV = @MaSVVí du: Cập nhật lại giá trị MaSV = MaLop + MaSV hiện tại cho tất cả sinh viênVí dụ: Cập nhật lại Masv thứ n là aaaDeclare cur_DSSV cursor scroll For select MaSV From SINHVIEN Open cur_DSSV Declare @masv varchar(10)Fetch Absolute 5 From cur_DSSV into @MasvIf (@@fetch_status = 0) update SINHVIEN set masv ='aaa' where current of cur_DSSVDeallocate cur_DSSV Tóm lại các bước sử dụng biến con trỏ trong lập trìnhB1. Định nghĩa Cursor từ một kết quả Select Declare Cursor_name Cursor For select_statement B2. Mở Cursor: OPEN , con trỏ ở vị trị dòng 0B3. Truy cập đến các bản ghi FETCH NEXT| Absolute 5 FROM INTO B4. Kểm tra có thành công không: Nếu @fetch_status = 0 thì xử lý lệnh, quay lại B3 Nếu @fetch_status 0 thì sang B5 B5. Đóng Cursor: CLOSE B6. Xoá tham chiếu của Cursor: DEALLOCATE Bài tập1. Thêm cột SBD trong bảng SV: Hãy điền SBD cho các sinh viên theo từng lớp2. Thêm cột Docdiem trong bảng DIEMTBC, Viết đoạn chương trình cập nhật cho cột Docdiem để đọc điểm TBC ra thành chữ tương ứng.3. Viết đoạn chương trình phân lớp ‘CT11’ ra thành 2 lớp CT11A, CT11B có số lượng nam nữ như nhau.Declare cur_SBD cursor For Select MaSV From SINHVIEN order by Malop Open cur_SBD Declare @i intFetch Next From cur_SBDSet @i=1 While @@fetch_status = 0 Begin Update SINHVIEN Set MaSV = right('1000000'++ltrim(STR(@i)),4)Where Current Of cur_SBDFetch Next From cur_SBDset @i=@i+1End Close cur_SBDDeallocate cur_SDBDeclare cur_phanlop cursor for select masv,Gioitinh,Malop from SINHVIEN where malop='ct11‘ order by gioitinhopen cur_phanlopdeclare @malop varchar(10),@masv varchar(10),@i intfetch next from cur_phanlop set @i=1While @@FETCH_STATUS=0BeginIf @i% 2=0update SINHVIEN set MaLop='CT11A' where current of cur_phanlopElse update SINHVIEN set MaLop='CT11B' where current of cur_phanlopset @i=@i+1fetch next from cur_phanlopendclose cur_phanlopDeallocate cur_phanlopIV. Stored procedure (Thủ tục)1. Khái niệm: Thủ tục lưu trữ là một đối tượng trong CSDL, bao gồm nhiều câu lệnh T-SQL , sẽ được thực thi khi thủ tục lưu trữ được goi.2. Ý nghĩa: –Tính tái sử dụng. –Các lệnh trong stored procedure được tối ưu hóa một lần sau khi biên dịch tiết kiệm thời gian khi thực thi. –Giảm khối lượng thông tin trao đổi khi ứng dụng gửi yêu cầu thực hiện công việc về database server –Hỗ trợ tốt hơn cho việc đảm bảo an toàn (security) cho CSDL3. Tạo thủ tục: Create procedure Danh sách tham số vào ra As [return [giá trị trả về] ] Go Lưu ý: Danh sách tham số vào (ra) [=default] [output] Output: Chỉ định tham số ra (muốn biết giá trị của nó) - Tên tham số đặt theo qui tắc như tên biến cục bộ - Lệnh RETURN được sử dụng để kết thức stored procedure và trả về giá trị là một số, có thể là một biến. Giá trị mặc định là Return 0 - Mỗi lệnh SELECT đặt trong stored procedure sẽ trả về 1 bảng. Declare @SiSo int Select @SiSo = Count(MaSV) From SINHVIEN Where MaLop= ‘CT11’ If @SiSo 25) Alter PROC usp_Inserrt_SinhVien @masv varchar(10), @HoTen nvarchar(30),@Dienthoai varchar(7),@maLop varchar(10) ,Ngaysinh Date@tbloi varchar(30)=null outputAS BEGIN IF(EXISTS(SELECT * FROM SINHVIEN sv WHERE sv.Masv = @Masv)) BEGIN set @tbloi= N'Mã số sinh viên ' + @Masv + N' đã tồn tại' RETURN -1 END IF(NOT EXISTS(SELECT * FROM DMLOP l WHERE l.MaLop = @MaLop)) BEGIN set @tbloi= N'Mã số lớp ' + @MaLop + N' chưa tồn tại' RETURN -1 END INSERT INTO SINHVIEN(Masv, HoTen, Dienthoai, MaLop) VALUES(@Masv, @HoTen, @Dienthoai, @MaLop) RETURN 0 /* procedure tự trả về 0 nếu không RETURN */ END GO DECLARE @kq varchar(30)EXEC usp_Inserrt_SinhVien '002', N'Nguyễn Văn A', '7654343', 'CT11',’09/09/1987’@kq outputPRINT @kq 2.CREATE PROC usp_GETALL_SinhVien @maLop varchar(10) AS BEGIN IF(NOT EXISTS(SELECT * FROM SINHVIEN WHERE Malop = @maLop)) BEGIN PRINT N'Mã số lớp ' + @maLop + N' chưa tồn tại' RETURN -1 END SELECT Masv,Ho,Ten,Gioitinh,Ngaysinh,Dienthoai,Malop FROM SINHVIEN Where Malop = @maLop /*procedure luôn trả về 0 nếu không RETURN*/ END GO 5.Chỉnh sửa thủ tục Thay từ khóa Create trong lệnh tạo thủ tục bằng từ khóa Alter 6. Xóa thủ tục Drop procedure 7. Mã hóa thủ tục Thủ tục sẽ được mã hoá nếu tuỳ chọn WITH ENCRYPTION được chỉ định. Nếu thủ tục đã được mã hoá, ta không thể xem được nội dung của thủ tục.Thêm từ khóa WITH ENCRYPTION Trong lệnh Alter thủ tục8 Biên dịch lại thủ tụcKhi người sử dụng làm thay đổi tới những index của bảng. Stored procedures phải được biên dịch lại (recompiled) để chấp nhận những thay đổi đó. Thêm từ khóa WITH RECOMPILE trong lệnh Alter thủ tục V. Hàm (Function)Hàm là đối tượng cơ sở dữ liệu tương tự như thủ tục. Điểm khác biệt giữa hàm và thủ tục là hàm trả về một giá trị Giá trị trả về có thể là một bảng có được từ một câu truy vấnCú pháp CREATE FUNCTION tên_hàm ([danh_sách_tham_số vào]) RETURNS (kiểu_trả_về_của_hàm) |[Table] AS BEGIN các_câu_lệnh_của_hàm Return {Giá trị | Biến | Biểu thức | Câu lệnh truy vấn} END Bài tập Viết hàm Xeploai theo quy định.Viết hàm hiển thị giới tính Nam, Nữ.Viết hàm đọc điểmViết hàm đọc số có ba chữ số.Viết hàm đọc số có nhiều chữ số.Viết hàm tách tênBài tập 1: Biến vào: DIEM; Biến ra: XL Create Function Xeploai (@Diem numeric(4,1))Returns nvarchar(10)asBegindeclare @xl nvarchar(10)set @xl=casewhen @Diem>=8 then N'Giỏi'when @Diem>=7 then N'Khá'When @Diem>=5 then N'Trung binh'ElseN'Yếu'EndReturn @xlEndVí dụ ứng dụng:Hiển thị danh sách sinh viên gồm: Masv, Họ, Tên, Malop, DTBC, Xếp loạiSelect SINHVIEN.MaSV, HoTen, DTBC, dbo.Xeploai(DTBC) as 'Xếp loại' From SINHVIEN, DIEMTBCWhere SINHVIEN.Masv=DIEMTBC.MasvFunction Tachten(@ht nvarchar(30)) returns nvarchar(10)AsBeginDeclare @ten varchar(10), @L int, @i int,@j int,@kt varchar(10)Set @L=LEN(@ht)Set @i=1While @i0)ROUND(x,n) : Làm tròn tới n sô thập phân.CEILING( x) : Sô nguyên nhỏ nhất nhưng lớn hơn xFLOOR(X) : Sô nguyên lớn nhât nhưng nhỏ hơn x... và các hàm lng giác: SIN, COS, TAN, ASIN, ACOS, ATAN ...2. Các hàm xử lý chuỗiACSII( ch ) : Mã ASCII của ký tự chCHAR( n ) : Ký tự có mã ASCII là nLOWER( str ) : Trả vê chuỗi chữ thườngUPPER(str) : Trả về chuỗi chữ hoaLTRIM(str) : Trả vê chuỗi không có dấu cách bên tráiRTRIM(str) : Trả vê chuoi không có dấu cách bên phảiLEFT(str,n): Lấy n ký tự phía trái của dãy str.RIGHT(str,n): Lây n ký tự phía phải của dãy str.SUBSTRING(str, start, n): Lây n ký tự của dãy str kể từ vị trí start trong dãy.REPLACE(str1, str2, str3): thay thế tất cả str2 trong str1 bằng str3.STUFF(str1, start, n, str2 ): Thay thế n ký tự trong str1 từ vị trí start bằng chuỗi str2.STR( x, len [, Dec]): Chuyển sô x thành chuỗi.3. Hàm xử lý ngày thángGETDATE(): Cho ngày tháng năm hiện tại (Oracle: SYSDATE)DAY(dd): Cho sô thứ tự ngày trong tháng của biểu thức ngày dd.MONTH(dd): Cho sô thứ tự tháng trong năm của biểu thức ngày dd.YEAR(dd): Cho năm của biểu thức ngày dd.Datepart Viết tắtYear yy, yyyyQuarter qq, qMonth mm, mDay of year dy, yDay of Month dd, dWeek of year wk, wwWeekday dwHour hhMinute mi, nSecond ss, sDATEPART(datepart, date): Đưa ra một phần trong ngày date (ss,mm,dd,DATEADD(datepart,number, date): Đưa ra một phần trong date và cộng thêm thêm một số NumberDATEDIFF(datepart, date1, date2): Đưa ra một phần trong date2 và cộng thêm một ngày date14 Hàm chuyển đổi kiểu dữ liệu1. CAST (biểu_thức AS kiểu_dữ_liệu) Chuyển đổi giá trị của biểu thức sang kiểu được chỉ định 2. CONVERT(kiểu_dữ_liệu, biểu_thức [,kiểu_chuyển_đổi]) Hàm có chức năng chuyển đổi giá trị của biểu thức sang kiểu dữ liệu được chỉ định. Tham số kiểu_chuyển_đổi là một giá trị số thường được sử dụng khi chuyển đổi giá trị kiểu ngày sang kiểu chuỗi nhằm qui định khuôn dạng dữ liệu được hiển thị và được qui định như sau: Phần năm 2 chữ số 4 chữ số Khuôn dạng dữ liệu0 hoặc 100 mon dd yyyy hh:mi AM (PM) 1 101 mm/dd/yy 2 102 yy.mm.dd 3 103 dd/mm/yy