Biến cục bộ
Khai báo biến cục bộ
DECLARE @Tên_biến Kiểu_dữ_liệu [, ...]
Ví dụ:
DECLARE @Tongsldat INT, @Hotenncc CHAR(50)
DECLARE @Ngayxh DATETIMEDù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 Sử dụng lệnh SET hoặc SELECT
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
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
DECLARE @Ngaydhgn DATETIME
SELECT @Ngaydhgn=MAX(NGAYDH)
FROM DONDH
GO
PRINT 'Ngày đặt hàng gần nhất: ' + CONVERT(CHAR(12),@Ngaydhgn)
GO Biến hệ thống
Ý nghĩa sử dụng
- Cung cấp các thông tin hệ thống
- 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
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
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 ] 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 (Câu_lệnh_SELECT)
Câu_lệnh1 | Khối_lệnh1
[ELSE
Câu_lệnh2 | Khối_lệnh2]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' Cấu trúc lặp WHILE
WHILE Biểu_thức_luận_lý
BEGIN
Các_lệnh_lặp
ENDDECLARE @Songuyen INT
SET @Songuyen=100
WHILE (@Songuyen<110)
BEGIN
PRINT 'Số nguyên : ' + CONVERT(CHAR(3), @Songuyen)
SET @Songuyen = @Songuyen + 1
ENDKhá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 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
DECLARE cur_Vattu CURSOR
DYNAMIC
FOR
SELECT * FROM VATTU
OPEN cur_Vattu Cú pháp FETCH
- Absolute n: Đọc dòng thứ n trong cursor
- Relative n: Đọc dòng thứ n kể từ vị trí hiện hành
FETCH [NEXT | PRIOR | FIRST | LAST
| ABSOLUTE n | RELATIVE n]
FROM Tên_cursor
[INTO Danh_sách_biến]
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_VattuCá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
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 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
- 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ỗi
- ERROR_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ỗi
- ERROR_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
Sử dụng cấu trúc điều khiển
Cho biết đơn giá xuất trung bình của hàng hóa “Đầu DVD Hitachi 1 đĩa” trong bảng CTPXUAT hiện giờ là bao nhiêu? Nếu lớn hơn 3,800,000 thì in ra “không nên thay đổi giá bán”, ngược lại in ra “đã đến lúc tăng giá bán”
--Cách 1
IF (
SELECT avg(c.dgxuat)
FROM ctpxuat c INNER JOIN vattu v ON v.mavtu = c.mavtu
WHERE v.tenvtu=N'Đầu DVD Hitachi 1 đĩa'
GROUP BY c.mavtu,v.tenvtu
) > =3800000
BEGIN
print 'không nên thay dổi giá bán'
END
ELSE
BEGIN
print 'đã đến lúc tăng giá bán'
END
--Cách 2
SELECT c.mavtu,v.tenvtu,avg(c.dgxuat)AS 'DG trungbinh',(
CASE
WHEN AVG(c.dgxuat)> =3800000
THEN 'không nên thay dổi giá bán'
ELSE
'đã đến lúc tăng giá bán'
END
) AS 'Ghi Chu'
FROM ctpxuat c INNER JOIN vattu v ON v.mavtu = c.mavtu
WHERE v.tenvtu=N'Đầu DVD Hitachi 1 đĩa'
GROUP BY c.mavtu,v.tenvtu
GOSử dụng hàm DATENAME để tính xem có đơn đặt hàng nào đã được lập vào ngày chủ nhật không? Nếu có thì in ra danh sách các đơn đặt hàng đó, ngược lại thì in ra chuỗi “Ngày lập các đơn đặt hàng đều là hợp lệ”
--Cach 1
DECLARE @SoNgay INT
SELECT @SoNgay=COUNT(d.ngaydh)
FROM dondh d
WHERE DATENAME(dw,d.ngaydh)='sunday'
IF @SoNgay>0
BEGIN
SELECT d.sodh, d.ngaydh, d.manhacc
FROM dondh d
WHERE DATENAME(dw,d.ngaydh)='sunday'
END
ELSE
PRINT 'Ngày lập các đơn đặt hàng đều hợp lệ'
GO
--Cach 2
IF EXISTS (SELECT * FROM dondh d WHERE DATENAME(dw,d.ngaydh)='sunday')
SELECT * FROM dondh d WHERE DATENAME(dw,d.ngaydh)='sunday'
ELSE
PRINT N'Các hóa đơn đặt hàng có ngày hợp lệ'
GOHãy cho biết đã có bao nhiêu phiếu nhập hàng cho đơn đặt hàng D001, nếu có thì in ra “Có xx số phiếu nhập hàng cho đơn đặt hàng D001”, ngược lại thì in ra “Chưa có nhập hàng nào cho D001”
DECLARE @SoPhieuDH INT
DECLARE @TenDH VARCHAR(4)
SET @TenDH='D001'
SET @SoPhieuDH=(
SELECT COUNT(p.sopn)
FROM pnhap p
WHERE p.sodh=@TenDH
)
IF @SoPhieuDH>0
PRINT N'Có '+CAST(@SoPhieuDH AS VARCHAR(3))+ N' số phiếu nhập hàng cho dơn đặt hàng D001'
ELSE
PRINT 'Chưa có đơn dặt hàng nào cho D001'
GOHãy cho biết đơn đặt hàng D001 đã có nhập đủ hàng chưa, nếu có thì in ra “Đã nhập đủ hàng cho đơn đặt hàng D001”, ngược lại thì in ra “
Chưa nhập đủ hàng cho đơn đặt hàng D001” Hãy cho biết vật tư TL90 đã có đặt hàng trong tháng 2/2009 chưa, nếu có thì in ra “Đã có đặt hàng với tổng số lượng đặt là xxx”, ngược lại thì in ra “Chưa có đặt hàng”
DECLARE @TongDonDH INT
DECLARE @MaVattu VARCHAR(4)
DECLARE @Thang INT
DECLARE @Nam INT
SET @MaVattu='TL90'
SET @Thang=2
SET @Nam=2009
IF EXISTS(
SELECT *
FROM ctdondh c INNER JOIN dondh d ON d.sodh = c.sodh
WHERE c.mavtu=@MaVattu AND MONTH (d.ngaydh)=@Thang AND YEAR(d.ngaydh)=@Nam
)
BEGIN
SET @TongDonDH=(
SELECT SUM(c.sldat)
FROM ctdondh c INNER JOIN dondh d ON d.sodh = c.sodh
WHERE c.mavtu=@MaVattu AND MONTH (d.ngaydh)=@Thang AND YEAR(d.ngaydh)=@Nam
)
PRINT N'Đã có đặt hàng với tổng số lựong dặt là '+CAST(@TongDonDH AS CHAR(3))
END
ELSE
PRINT N'Chưa có đặt hàng'
GOLiệt kê danh sách các đơn đặt hàng trong bảng DONDH có bổ sung thêm cột hiển thị thứ trong tuần (bằng tiếng Việt) của ngày đặt hàng
SELECT dh.*, 'Thứ' =
(
CASE DATENAME(dw,dh.ngaydh)
WHEN 'MONDAY'
THEN N'Thứ Hai'
WHEN 'TUESDAY'
THEN N'Thứ Ba'
WHEN 'WEDNESDAY'
THEN N'Thứ Tư'
WHEN 'THURSDAY'
THEN N'Thứ Năm'
WHEN 'FRIDAY'
THEN N'Thứ Sáu'
WHEN 'SATURDAY'
THEN N'Thứ Bảy'
ELSE
N'Chủ Nhật'
END
)
FROM dondh AS dhTính tổng các số nguyên từ 1 đến 100
DECLARE @Songuyen INT
DECLARE @Tong INT
SET @Tong = 0
SET @Songuyen = 0
while (@Songuyen <= 100)
BEGIN
SET @Tong = @Tong + @Songuyen
SET @Songuyen = @Songuyen +1
END
print @TongTính tổng chẵn và tổng lẻ của các số nguyên từ 1 đến 100
DECLARE @Songuyen INT
DECLARE @TongChan INT
DECLARE @TongLe INT
SET @TongChan = 0
SET @TongLe = 0
SET @Songuyen = 0
while (@Songuyen <= 100)
BEGIN
IF(@Songuyen % 2 = 0)
BEGIN
SET @TongChan = @TongChan + @Songuyen
END
ELSE
BEGIN
SET @TongLe = @TongLe + @Songuyen
END
SET @Songuyen = @Songuyen +1
END
print @TongChan
print @TongLeDuyệt cursor và xử lý Hiển thị danh sách các thông tin trong bảng DONDH có thêm cột tổng số phiếu nhập
DECLARE DonDH_cur CURSOR
FOR
SELECT dd.sodh, dd.ngaydh, dd.manhacc,COUNT(p.sodh)AS 'tongphieunhap'
FROM DonDH dd LEFT JOIN PNhap p ON p.sodh=dd.sodh
GROUP BY dd.sodh,dd.ngaydh,dd.manhacc
DECLARE @sodh NCHAR(4), @ngaydh datetime, @manhacc NCHAR(3), @slnhap INT
OPEN DonDH_cur
FETCH NEXT FROM DonDH_cur INTO @sodh,@ngaydh,@manhacc,@slnhap
WHILE 0=0
BEGIN
IF @@FETCH_STATUS<>0
BREAK
ELSE
BEGIN
PRINT @sodh+' '+CAST(@ngaydh AS CHAR(6))+' '+@manhacc+' '+CAST(@slnhap AS CHAR)
END
FETCH NEXT FROM DonDH_cur INTO @sodh,@ngaydh,@manhacc,@slnhap
END
CLOSE DonDH_cur
DEALLOCATE DonDH_cur
GO
