SQL Server - Bài 5: Lập trình với CSDL

SQL Server Lập trình với CSDL Biến cục bộ Khai báo biến cục bộ Dùng để lưu trữ các giá trị tạm thời trong quá trình tính toán Biến phải có kiểu dữ liệu Biến muốn sử dụng trong một batch phải khai báo trước Gán giá trị cho biến SET @Biến = Giá_trị SET @a = 5 Select @Biến = Tên_Cột From Tên_Bảng Select @ConLai = TonCuoiKy From TonKho Where MaVTu = ‘VT010’ And NamThang = ‘200402’ Xem giá trị hiện hành của biến Lệnh PRINT 	Print @Biến 	Print @A Khi có kết hợp với chuỗi, phải đổi kiểu dữ liệu sang kiểu chuỗi bằng hàm CAST hay CONVERT 	Print ‘Giá trị của @A ‘ + cast(@A as char(4)) Phạm vi hoạt động của biến Một biến chỉ có phạm vi hoạt động cục bộ Trong một Batch Trong một Stored Procedure hay Trigger Biến hệ thống Ý nghĩa sử dụng Cung cấp các thông tin hệ thống như Phiên bản SQL Server Số dòng dữ liệu vừa được xử lý bởi câu lệnh Mã lỗi Số lượng kết nối Tình trạng cursor … Không cần khai báo Biến do SQL Server định sẵn Tên bắt đầu bởi @@ Một vài biến hệ thống thường dùng @@RowCount Tổng số mẩu tin được tác động của câu lệnh truy vấn gần nhất. @@Error Số mã lỗi của câu lệnh thực hiện gần nhất Khi một câu lệnh thực hiện thành công thì giá trị là 0. @@Fetch_Status Trạng thái của việc đọc dữ liệu trong bảng theo cơ chế từng mẩu tin (cursor). Khi đọc dữ liệu của mẩu tin thành công thì giá trị là 0. Các toán tử Toán tử số học Toán tử nối chuỗi Sử dụng dấu + làm toán tử nối chuỗi SELECT 'Hello' + ' ' + 'The World!' SELECT 'Ngày đặt hàng D007 là: ' 	+ CAST(NGAYDH AS CHAR(11)) 	FROM DONDH 	WHERE SODH='D007' Toán tử so sánh Toán tử luận lý Sử dụng các toán tử thông thường AND, OR, NOT vẫn dùng trong các câu SQL SELECT * FROM VATTU WHERE (DVTINH='Bộ' AND PHANTRAM>10) OR (DVTINH='Cái' AND PHANTRAM>20) Cấu trúc điều khiển Cấu trúc rẽ nhánh IF...ELSE IF Biểu_thức_luận_lý 	Câu_lệnh1 | Khối_lệnh1 [ ELSE 	Câu_lệnh2 | Khối_lệnh2 ] Ví Dụ : IF (SELECT COUNT(*) FROM CTPXUAT WHERE SLXUAT>4) > 0 BEGIN PRINT 'Danh sách các hàng hóa bán với 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 hóa nào với số lượng >4' Cú pháp If Exists IF EXISTS (SELECT * FROM CTPXUAT WHERE SLXUAT>4) BEGIN PRINT 'Danh sách các hàng hóa bán với 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 hóa nào với số lượng >4' IF EXISTS (Câu_lệnh_SELECT) 	Câu_lệnh1 | Khối_lệnh1 [ ELSE 	Câu_lệnh2 | Khối_lệnh2 ] Cấu trúc lặp WHILE DECLARE @Songuyen INT SET @Songuyen=100 WHILE (@Songuyen<110) BEGIN PRINT 'Số nguyên : ' + CONVERT(CHAR(3), @Songuyen) SET @Songuyen = @Songuyen + 1 END WHILE Biểu_thức_luận_lý BEGIN 	Các_lệnh_lặp END Sử dụng biến kiểu dữ liệu cursor Khái niệm về cursor Các lệnh của SQL Server làm việc trên một nhóm nhiều mẩu tin Cursor là cấu trúc giúp làm việc với từng mẩu tin tại một thời điểm Khai báo cursor như một câu lệnh SELECT Có thể di chuyển giữa các mẩu tin trong cursor để làm việc Có thể dùng cursor để cập nhật dữ liệu (Update, Delete) Các bước sử dụng kiểu dữ liệu cursor Định nghĩa biến kiểu cursor bằng lệnh DECLARE Có hai loại cursor: Local, Global Cách di chuyển mẩu tin trong cursor: Forward only, scroll Cách quản lý dữ liệu của cursor: static, dynamic, keyset Sử dụng lệnh OPEN để mở ra cursor đã định nghĩa trước đó Đọc và xử lý trên từng dòng dữ liệu bên trong cursor Sử dụng biến @@Fetch_status Các lệnh Fetch và cấu trúc while Đóng cursor lại bằng lệnh CLOSE và DEALLOCATE Sau khi close, có thể mở lại Deallocate: hủy cursor khỏi bộ nhớ Cú pháp Declare DECLARE Tên_cursor CURSOR [LOCAL | GLOBAL] [FORWARD_ONLY | SCROLL] [STATIC | DYNAMIC | KEYSET] [READ_ONLY | SCROLL_LOCK] FOR Câu_lệnh_SELECT [FOR UPDATE [OF Danh_sách_cột_cập_nhật]] DECLARE cur_Vattu CURSOR DYNAMIC FOR SELECT * FROM VATTU Cú pháp Open OPEN Tên_cursor DECLARE cur_Vattu CURSOR DYNAMIC FOR SELECT * FROM VATTU OPEN cur_Vattu Cú pháp FETCH FETCH [NEXT | PRIOR | FIRST | LAST 	| ABSOLUTE n | RELATIVE n] 	FROM Tên_cursor 	[INTO Danh_sách_biến] Absolute n: Đọc dòng thứ n trong cursor Relative n: Đọc dòng thứ n kể từ vị trí hiện hành Ví dụ hoàn chỉnh --1. Khai báo biến cursor DECLARE cur_Vattu CURSOR KEYSET FOR 	 SELECT * FROM VATTU WHERE MAVTU LIKE 'TV%' ORDER BY MAVTU --2. Mở cursor OPEN cur_Vattu --3. Đọc dữ liệu FETCH NEXT FROM cur_Vattu WHILE @@FETCH_STATUS = 0 BEGIN -- Xử lý dòng mới vừa đọc được 	 -- Thực hiện đọc tiếp các dòng kế FETCH NEXT FROM cur_Vattu END --4. Đóng cursor CLOSE cur_Vattu DEALLOCATE cur_Vattu Các hàm thường dùng Các hàm chuyển đổi kiểu dữ liệu Một hàm của SQL Server có thể sử dụng ở bất cứ đâu thay cho một giá trị cụ thể Đổi một số thành chuỗi STR (Số_thực, Số_ký_tự [, Số_lẻ]) Đổi kiểu dữ liệu CAST (Biểu_thức AS Kiểu_dữ_liệu) Đổi kiểu dữ liệu và định dạng CONVERT (Kiểu_dữ_liệu, Biểu_thức [, Định_dạng]) Một số định dạng chuỗi ngày thông dụng Print convert(nvarchar(50), getdate(), 5) 23-05-09 Print convert(nvarchar(50), getdate(), 105) 23-05-2009 Các hàm ngày giờ Cộng ngày DATEADD (Đơn_vị, Con_số, Ngày_chỉ_định) So sánh hai biến ngày DATEDIFF (Đơn_vị, Ngày1, Ngày2) Lấy tên ngày, tháng, năm DATENAME (Đơn_vị, Ngày) Thời điểm hiện hành GETDATE() Lấy một thành phần ngày, giờ trong biến ngày DATEPART (Đơn_vị, Ngày) Lấy ngày, tháng, năm của biến ngày DAY (Ngày) MONTH (Ngày) YEAR (Ngày) Các hàm toán học Lấy trị tuyệt đối ABS (Biểu_thức_số) Hằng số Pi PI() Luỹ thừa POWER (Biểu_thức_số, Số_mũ) Lấy số ngẫu nhiên RAND ([Số_nguồn]) Làm tròn số ROUND (Biểu_thức_số, Vtrí_làm_tròn) Dấu của kết quả biểu thức : SIGN (Biểu_thức_số) Lấy căn bậc 2 : 	SQRT (Biểu_thức_số) Các hàm xử lý chuỗi Hàm viết hoa, thường UPPER (Chuỗi), LOWER (Chuỗi) Hàm cắt chuỗi LEFT (Chuỗi nguồn, Số_ktự), RIGHT (Chuỗi nguồn, Số_ktự) SUBSTRING (Chuỗi nguồn,Vị_trí,Số_ktự) Hàm cắt khoảng trắng, tạo chuỗi khoảng trắng LTRIM (Chuỗi), RTRIM (Chuỗi), SPACE (N) Hàm tạo chuỗi lặp REPLICATE (Chuỗi_lặp, N) Chiều dài chuỗi LEN (Chuỗi) Đảo chuỗi REVERSE (Chuỗi) Tìm và thay thế chuỗi REPLACE (Chuỗi nguồn, Chuỗi_tìm, Chuỗi_thay_thế) Đổi từ số thành ký tự và ngược lại CHAR (Số) , ASCII(Ký_tự) Xử lý lỗi Khối lệnh TRY…CATCH Ý nghĩa : Thực hiện các lệnh trong khối TRY, nếu gặp lỗi sẽ chuyển qua xử lý bằng các lệnh trong khối CATCH Cú pháp :	BEGIN TRY       	{ các câu lệnh } 	END TRY 	BEGIN CATCH      	 { các câu lệnh} 	END CATCH Các điểm cần lưu ý : TRY và CATCH phải cùng lô xử lý Sau khối TRY phải là khối CATCH Có thể lồng nhiều cấp Khối lệnh TRY…CATCH Ví dụ : BEGIN TRY SELECT * FROM BangKhongTonTai; END TRY BEGIN CATCH SELECT ERROR_NUMBER() as ErrorNumber, ERROR_MESSAGE() as ErrorMessage; END CATCH Một số hàm ERROR thường dùng ERROR_NUMBER() : Trả về mã số của lỗiERROR_MESSAGE() Trả về chuỗi lỗi ERROR_SEVERITY() returns the error severity.ERROR_STATE() returns the error state number.ERROR_LINE() : Trả về dòng gây ra lỗiERROR_PROCEDURE() Trả về tên thủ tục/ trigger gây ra lỗi Thủ tục RAISERROR Ý nghĩa : Trả thông báo lỗi về cho ứng dụng Cú pháp : Raiserror(tbao_loi, muc_do, trang_thai [, cac_tham_so] ) Trong đó: tbao_loi : 	- mã thông báo lỗi do người dùng định nghĩa trước bằng sp_addmessage và được lưu trong sys.messages. Giá trị phải lớn hơn 50000. 	 - chuỗi thông báo lỗi bất kỳ. muc_do : Số có giá trị từ 025 thể hiện mức độ nghiêm trọng của lỗi. trang_thai : Số từ 1-127 để xác định vị trí lỗi khi sử dụng cùng một tbao_loi tại nhiều điểm khác nhau cac_tham_so : Hỗ trợ cho các tbao_loi cần tham số Ví dụ sử dụng RAISERROR … IF @nPhanTram NOT BETWEEN 0 AND 100 BEGIN 	SET @sErrMsg = N‘Tỉ lệ phầm trăm phải nằm trong đoạn [0,100]’ 	RAISERROR(@sErrMsg, 16, 1) 	RETURN END … Msg 50000, Level 16, State 1, Line 6 Tỉ lệ phầm trăm phải nằm trong đoạn [0,100]